shortcode-variables / includes / db.php

db.php in Snippet Shortcodes 5.0a, at includes/db.php

529 lines 10.1 KB

Raw Download Zip

1	<?php
2
3	defined('ABSPATH') or die('Jog on!');
4
5	/**
6	* Build database table
7	*/
8	function sh_cd_create_database_table() {
9
10	global $wpdb;
11
12	$table_name = $wpdb->prefix . SH_CD_TABLE;
13
14	$charset_collate = $wpdb->get_charset_collate();
15
16	$sql = "CREATE TABLE $table_name (
17	id mediumint(9) NOT NULL AUTO_INCREMENT,
18	slug varchar(100) NOT NULL,
19	previous_slug varchar(100) NOT NULL,
20	editor varchar(10) NULL,
21	data text,
22	disabled bit default 0,
23	multisite bit default 0,
24	UNIQUE KEY id (id)
25	) $charset_collate;";
26
27	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
28
29	dbDelta( $sql );
30	}
31
32	/**
33	* Build database table for multisite
34	*/
35	function sh_cd_create_database_table_multisite() {
36
37	global $wpdb;
38
39	$table_name = $wpdb->base_prefix . SH_CD_TABLE_MULTISITE;
40
41	$charset_collate = $wpdb->get_charset_collate();
42
43	$sql = "CREATE TABLE $table_name (
44	id mediumint(9) NOT NULL AUTO_INCREMENT,
45	slug varchar(100) NOT NULL,
46	data text,
47	site_id int default 0,
48	UNIQUE KEY id (id)
49	) $charset_collate;";
50
51	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
52
53	dbDelta( $sql );
54	}
55
56	/**
57	* Fetch all Shortcodes
58	*
59	* @return bool
60	*/
61	function sh_cd_db_shortcodes_all() {
62
63	global $wpdb;
64
65	return $wpdb->get_results( 'SELECT * FROM ' . $wpdb->prefix . SH_CD_TABLE . ' order by slug asc', ARRAY_A );
66	}
67
68	/**
69	* Fetch all enabled Shortcodes
70	*
71	* @return bool
72	*/
73	function sh_cd_db_shortcodes_all_enabled() {
74
75	global $wpdb;
76
77	return $wpdb->get_results( 'SELECT * FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where disabled = 0 order by slug asc', ARRAY_A );
78	}
79
80	/**
81	* Fetch a count of all shortcodes
82	*
83	* @return bool
84	*/
85	function sh_cd_db_shortcodes_count() {
86
87	global $wpdb;
88
89	return $wpdb->get_var( 'SELECT count(id) FROM ' . $wpdb->prefix . SH_CD_TABLE );
90	}
91
92	/**
93	* Fetch a shortcode by ID (mainly used for quick lookups in admin)
94	*
95	* @param $id
96	*
97	* @return mixed
98	*/
99	function sh_cd_db_shortcodes_by_id( $id ) {
100
101	global $wpdb;
102
103	$sql = $wpdb->prepare('SELECT id, slug, previous_slug, data, disabled, multisite, editor FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where id = %d', $id);
104
105	return $wpdb->get_row( $sql, ARRAY_A );
106	}
107
108	/**
109	* Fetch the content of the shortcode by slug
110	*
111	* @param $slug
112	*
113	* @return null\|string
114	*/
115	function sh_cd_db_shortcodes_by_slug( $slug ) {
116
117	global $wpdb;
118
119	$sqls = [];
120
121	// If multi site functionality is enabled, look there first for the shortcode!
122	if ( true === sh_cd_is_multisite_enabled() ) {
123
124	$sqls[] = $wpdb->prepare('SELECT data FROM ' . $wpdb->base_prefix . SH_CD_TABLE_MULTISITE . ' where slug = %s', $slug);
125	}
126
127	$sqls[] = $wpdb->prepare('SELECT data FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where slug = %s and disabled <> 1', $slug);
128
129	foreach ( $sqls as $sql ) {
130
131	$row = $wpdb->get_var( $sql );
132
133	if ( false === empty( $row ) ) {
134
135	return stripslashes( $row );
136	}
137	}
138
139	return NULL;
140	}
141
142	/**
143	* Get a Shortcode's slug from the slug ID
144	*
145	* @param $id
146	*
147	* @return bool
148	*/
149	function sh_cd_db_shortcodes_get_slug_by_id( $id ) {
150
151	global $wpdb;
152
153	$sql = $wpdb->prepare('SELECT slug FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where id = %d', $id);
154
155	return $wpdb->get_var( $sql );
156	}
157
158	/**
159	* Save / Insert a shortcode
160	*
161	* @param $shortcode
162	*
163	* @return bool
164	*/
165	function sh_cd_db_shortcodes_save( $shortcode, $return_shortcode = false ) {
166
167	if ( false === is_admin() ) {
168	return false;
169	}
170
171	$multi_site_enabled = sh_cd_is_multisite_enabled();
172
173	$shortcode = wp_parse_args( $shortcode, [ 'id' => NULL,
174	'slug' => NULL,
175	'previous_slug' => NULL,
176	'editor' => NULL,
177	'data' => NULL,
178	'disabled' => 0,
179	'multisite' => 0
180	]);
181
182	// We need either a slug or an ID
183	if ( true === empty( $shortcode['slug'] ) && true === empty( $shortcode['id'] ) ) {
184	return false;
185	}
186
187	$shortcode['disabled'] = (int) $shortcode['disabled'];
188	$shortcode['multisite'] = ( true === $multi_site_enabled ) ? (int) $shortcode['multisite'] : 0;
189
190	global $wpdb;
191
192	$result = false;
193
194	// Updating an existing shortcode?
195	if ( false === empty( $shortcode['id'] ) && true === is_numeric( $shortcode['id'] ) ){
196
197	$shortcode['slug'] = sh_cd_slug_generate( $shortcode['slug'], $shortcode['id'] );
198
199	$formats = sh_cd_db_get_formats( $shortcode );
200
201	$result = $wpdb->update(
202	$wpdb->prefix . SH_CD_TABLE,
203	$shortcode,
204	[ 'id' => $shortcode['id'] ],
205	$formats,
206	[ '%d' ]
207	);
208
209	if ( false !== $result ) {
210	do_action( 'sh-cd-shortcode-updated', $shortcode );
211	}
212
213	sh_cd_cache_delete_by_slug_or_key( $shortcode['id'] );
214	sh_cd_cache_delete_by_slug_or_key( $shortcode['previous_slug'] );
215
216	// Adding a new shortcode
217	} else {
218
219	unset( $shortcode['id'] );
220
221	// Ensure slug is santised and unique
222	$shortcode['slug'] = sh_cd_slug_generate( $shortcode['slug'] );
223
224	$formats = sh_cd_db_get_formats( $shortcode );
225
226	$result = $wpdb->insert(
227	$wpdb->prefix . SH_CD_TABLE,
228	$shortcode,
229	$formats
230	);
231
232	if ( false !== $result ) {
233	do_action( 'sh-cd-shortcode-added', $shortcode );
234	}
235
236	// It's an insert, so there should be no cache... however, just a wee sanity check in case
237	// a shortcode with the same slug previously exists.
238	sh_cd_cache_delete_by_slug_or_key( $shortcode['slug'] );
239	}
240
241	if ( false !== $result ) {
242
243	if ( 1 === $shortcode['multisite'] ) {
244	sh_cd_db_shortcodes_multisite_insert( $shortcode['slug'], $shortcode['data'] );
245	} else {
246	sh_cd_db_shortcodes_multisite_delete( $shortcode['slug'] );
247	}
248
249	sh_cd_cache_delete( $shortcode['slug'] );
250
251	if ( true === $return_shortcode ) {
252
253	$shortcode['id'] = $result;
254	return $shortcode;
255	}
256
257	return true;
258	}
259
260	return false;
261	}
262
263
264	/**
265	* Insert a multisite shortcode
266	*
267	* @param $shortcode
268	*
269	* @return bool
270	*/
271	function sh_cd_db_shortcodes_multisite_insert( $slug, $data ) {
272
273	if ( false === is_admin() ) {
274	return false;
275	}
276
277	sh_cd_db_shortcodes_multisite_delete( $slug );
278
279	global $wpdb;
280
281	$shortcode = [
282	'slug' => $slug,
283	'data' => $data,
284	'site_id' => get_current_blog_id()
285	];
286
287	$formats = sh_cd_db_get_formats( $shortcode );
288
289	$result = $wpdb->insert(
290	$wpdb->base_prefix . SH_CD_TABLE_MULTISITE,
291	$shortcode,
292	$formats
293	);
294
295	sh_cd_cache_delete( $slug );
296
297	do_action( 'sh_cd_multisite_changed', $slug );
298
299	return ( false !== $result );
300	}
301
302	/**
303	* Delete a multisite shortcode
304	*
305	* @param $slug
306	*
307	* @return bool
308	*/
309	function sh_cd_db_shortcodes_multisite_delete( $slug ) {
310
311	global $wpdb;
312
313	$result = $wpdb->delete( $wpdb->base_prefix . SH_CD_TABLE_MULTISITE, [ 'slug' => $slug ], [ '%s' ] );
314
315	sh_cd_cache_delete( $slug );
316
317	do_action( 'sh_cd_multisite_changed', $slug );
318
319	return ( false !== $result );
320	}
321
322	/**
323	* Fetch all multisite slugs
324
325	* @return null\|array
326	*/
327	function sh_cd_db_shortcodes_multisite_slugs() {
328
329	global $wpdb;
330
331	return $wpdb->get_results( 'SELECT slug FROM ' . $wpdb->base_prefix . SH_CD_TABLE_MULTISITE, ARRAY_A );
332	}
333
334
335	/**
336	* Update a shortcode's status
337	*
338	* @param $shortcode
339	*
340	* @return bool
341	*/
342	function sh_cd_db_shortcodes_update_status( $id, $status ) {
343
344	if ( false === is_admin() ) {
345	return false;
346	}
347
348	global $wpdb;
349
350	$result = $wpdb->update(
351	$wpdb->prefix . SH_CD_TABLE,
352	[ 'disabled' => $status ],
353	[ 'id' => $id ],
354	[ '%d' ],
355	[ '%d' ]
356	);
357
358	sh_cd_cache_delete_by_slug_or_key( $id );
359
360	return ( false !== $result );
361	}
362
363	/**
364	* Update a shortcode's multisite
365	*
366	* @param $shortcode
367	*
368	* @return bool
369	*/
370	function sh_cd_db_shortcodes_update_multisite( $id, $multisite ) {
371
372	if ( false === is_admin() ) {
373	return false;
374	}
375
376	global $wpdb;
377
378	$result = $wpdb->update(
379	$wpdb->prefix . SH_CD_TABLE,
380	[ 'multisite' => $multisite ],
381	[ 'id' => $id ],
382	[ '%d' ],
383	[ '%d' ]
384	);
385
386	$shortcode = sh_cd_db_shortcodes_by_id( $id );
387
388	if ( false === empty( $shortcode ) ) {
389
390	if ( 1 === (int) $multisite ) {
391	sh_cd_db_shortcodes_multisite_insert( $shortcode['slug'], $shortcode['data'] );
392	} else {
393	sh_cd_db_shortcodes_multisite_delete( $shortcode['slug'] );
394	}
395
396	}
397
398	sh_cd_cache_delete_by_slug_or_key( $id );
399
400	return ( false !== $result );
401	}
402
403	/**
404	* Update a shortcode's content
405	*
406	* @param $shortcode
407	*
408	* @return bool
409	*/
410	function sh_cd_db_shortcodes_update_content( $id, $data ) {
411
412	if ( false === is_admin() ) {
413	return false;
414	}
415
416	global $wpdb;
417
418	$result = $wpdb->update(
419	$wpdb->prefix . SH_CD_TABLE,
420	[ 'data' => $data ],
421	[ 'id' => $id ],
422	[ '%s' ],
423	[ '%d' ]
424	);
425
426	$shortcode = sh_cd_db_shortcodes_by_id( $id );
427
428	if ( false === empty( $shortcode ) ) {
429
430	if ( 1 === (int) $shortcode[ 'multisite' ] ) {
431	sh_cd_db_shortcodes_multisite_insert( $shortcode['slug'], $shortcode['data'] );
432	} else {
433	sh_cd_db_shortcodes_multisite_delete( $shortcode['slug'] );
434	}
435
436	}
437	sh_cd_cache_delete_by_slug_or_key( $id );
438
439	return ( false !== $result );
440	}
441
442
443	/**
444	* Delete a shortcode
445	*
446	* @param $id
447	*
448	* @return bool
449	*/
450	function sh_cd_db_shortcodes_delete( $id ) {
451
452	if ( false === is_admin() \|\| false === is_numeric( $id ) ) {
453	return false;
454	}
455
456	global $wpdb;
457
458	// Clear cached version
459	sh_cd_cache_delete_by_slug_or_key( $id );
460
461	$slug = sh_cd_db_shortcodes_get_slug_by_id( $id );
462	$result = $wpdb->delete( $wpdb->prefix . SH_CD_TABLE, [ 'id' => $id ], [ '%d' ] );
463
464	if ( false !== $result ) {
465	do_action( 'sh-cd-shortcode-updated', $id, $slug );
466	}
467
468	return ( false !== $result );
469	}
470
471	/**
472	* For a given key value array, look up and return the expected MySQL data formats.
473	*
474	* @param $data
475	*
476	* @return array
477	*/
478	function sh_cd_db_get_formats( $data ) {
479
480	$lookup = [
481	'id' => '%d',
482	'slug' => '%s',
483	'previous_slug' => '%s',
484	'data' => '%s',
485	'disabled' => '%d',
486	'multisite' => '%d',
487	'site_id' => '%d',
488	'editor' => '%s'
489	];
490
491	$formats = [];
492
493	foreach ( $data as $key => $value ) {
494
495	if ( false === empty( $lookup[ $key ] ) ) {
496	$formats[] = $lookup[ $key ];
497	}
498	}
499
500	return $formats;
501	}
502
503	/**
504	* Check if the slug already exists
505	*
506	* @param $slug
507	* @param $existing_id
508	*
509	* @return bool
510	*/
511	function sh_cd_slug_is_unique( $slug, $existing_id = NULL ) {
512
513	if ( true === empty( $slug ) ) {
514	return false;
515	}
516
517	global $wpdb;
518
519	$sql = $wpdb->prepare( 'SELECT count(slug) FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where slug = %s', $slug );
520
521	if ( false === empty( $existing_id ) ) {
522	$sql .= $wpdb->prepare( ' and id <> %d', $existing_id );
523	}
524
525	$row = $wpdb->get_var( $sql );
526
527	return ( empty( $row ) );
528	}
529