shortcode-variables / includes / db.php

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

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