shortcode-variables / includes / db.php

db.php in Snippet Shortcodes 3.5.1, at includes/db.php

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