shortcode-variables / includes / db.php

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

513 lines 9.7 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, $return_shortcode = false ) {
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, [ 'id' => NULL,
173	'slug' => NULL,
174	'previous_slug' => NULL,
175	'data' => NULL,
176	'disabled' => 0,
177	'multisite' => 0
178	]);
179
180	// We need either a slug or an ID
181	if ( true === empty( $shortcode['slug'] ) && true === empty( $shortcode['id'] ) ) {
182	return false;
183	}
184
185	$shortcode['disabled'] = (int) $shortcode['disabled'];
186	$shortcode['multisite'] = ( true === $multi_site_enabled ) ? (int) $shortcode['multisite'] : 0;
187
188	global $wpdb;
189
190	$result = false;
191
192	// Updating an existing shortcode?
193	if ( false === empty( $shortcode['id'] ) && true === is_numeric( $shortcode['id'] ) ){
194
195	$shortcode['slug'] = sh_cd_slug_generate( $shortcode['slug'], $shortcode['id'] );
196
197	$formats = sh_cd_db_get_formats( $shortcode );
198
199	$result = $wpdb->update(
200	$wpdb->prefix . SH_CD_TABLE,
201	$shortcode,
202	[ 'id' => $shortcode['id'] ],
203	$formats,
204	[ '%d' ]
205	);
206
207	sh_cd_cache_delete_by_slug_or_key( $shortcode['id'] );
208	sh_cd_cache_delete_by_slug_or_key( $shortcode['previous_slug'] );
209
210	// Adding a new shortcode
211	} else {
212
213	unset( $shortcode['id'] );
214
215	// Ensure slug is santised and unique
216	$shortcode['slug'] = sh_cd_slug_generate( $shortcode['slug'] );
217
218	$formats = sh_cd_db_get_formats( $shortcode );
219
220	$result = $wpdb->insert(
221	$wpdb->prefix . SH_CD_TABLE,
222	$shortcode,
223	$formats
224	);
225
226	// It's an insert, so there should be no cache... however, just a wee sanity check in case
227	// a shortcode with the same slug previously exists.
228	sh_cd_cache_delete_by_slug_or_key( $shortcode['slug'] );
229	}
230
231	if ( false !== $result ) {
232
233	if ( 1 === $shortcode['multisite'] ) {
234	sh_cd_db_shortcodes_multisite_insert( $shortcode['slug'], $shortcode['data'] );
235	} else {
236	sh_cd_db_shortcodes_multisite_delete( $shortcode['slug'] );
237	}
238
239	sh_cd_cache_delete( $shortcode['slug'] );
240
241	if ( true === $return_shortcode ) {
242
243	$shortcode['id'] = $result;
244	return $shortcode;
245	}
246
247	return true;
248	}
249
250	return false;
251	}
252
253
254	/**
255	* Insert a multisite shortcode
256	*
257	* @param $shortcode
258	*
259	* @return bool
260	*/
261	function sh_cd_db_shortcodes_multisite_insert( $slug, $data ) {
262
263	if ( false === is_admin() ) {
264	return false;
265	}
266
267	sh_cd_db_shortcodes_multisite_delete( $slug );
268
269	global $wpdb;
270
271	$shortcode = [
272	'slug' => $slug,
273	'data' => $data,
274	'site_id' => get_current_blog_id()
275	];
276
277	$formats = sh_cd_db_get_formats( $shortcode );
278
279	$result = $wpdb->insert(
280	$wpdb->base_prefix . SH_CD_TABLE_MULTISITE,
281	$shortcode,
282	$formats
283	);
284
285	sh_cd_cache_delete( $slug );
286
287	do_action( 'sh_cd_multisite_changed', $slug );
288
289	return ( false !== $result );
290	}
291
292	/**
293	* Delete a multisite shortcode
294	*
295	* @param $slug
296	*
297	* @return bool
298	*/
299	function sh_cd_db_shortcodes_multisite_delete( $slug ) {
300
301	global $wpdb;
302
303	$result = $wpdb->delete( $wpdb->base_prefix . SH_CD_TABLE_MULTISITE, [ 'slug' => $slug ], [ '%s' ] );
304
305	sh_cd_cache_delete( $slug );
306
307	do_action( 'sh_cd_multisite_changed', $slug );
308
309	return ( false !== $result );
310	}
311
312	/**
313	* Fetch all multisite slugs
314
315	* @return null\|array
316	*/
317	function sh_cd_db_shortcodes_multisite_slugs() {
318
319	global $wpdb;
320
321	return $wpdb->get_results( 'SELECT slug FROM ' . $wpdb->base_prefix . SH_CD_TABLE_MULTISITE, ARRAY_A );
322	}
323
324
325	/**
326	* Update a shortcode's status
327	*
328	* @param $shortcode
329	*
330	* @return bool
331	*/
332	function sh_cd_db_shortcodes_update_status( $id, $status ) {
333
334	if ( false === is_admin() ) {
335	return false;
336	}
337
338	global $wpdb;
339
340	$result = $wpdb->update(
341	$wpdb->prefix . SH_CD_TABLE,
342	[ 'disabled' => $status ],
343	[ 'id' => $id ],
344	[ '%d' ],
345	[ '%d' ]
346	);
347
348	sh_cd_cache_delete_by_slug_or_key( $id );
349
350	return ( false !== $result );
351	}
352
353	/**
354	* Update a shortcode's multisite
355	*
356	* @param $shortcode
357	*
358	* @return bool
359	*/
360	function sh_cd_db_shortcodes_update_multisite( $id, $multisite ) {
361
362	if ( false === is_admin() ) {
363	return false;
364	}
365
366	global $wpdb;
367
368	$result = $wpdb->update(
369	$wpdb->prefix . SH_CD_TABLE,
370	[ 'multisite' => $multisite ],
371	[ 'id' => $id ],
372	[ '%d' ],
373	[ '%d' ]
374	);
375
376	$shortcode = sh_cd_db_shortcodes_by_id( $id );
377
378	if ( false === empty( $shortcode ) ) {
379
380	if ( 1 === (int) $multisite ) {
381	sh_cd_db_shortcodes_multisite_insert( $shortcode['slug'], $shortcode['data'] );
382	} else {
383	sh_cd_db_shortcodes_multisite_delete( $shortcode['slug'] );
384	}
385
386	}
387
388	sh_cd_cache_delete_by_slug_or_key( $id );
389
390	return ( false !== $result );
391	}
392
393	/**
394	* Update a shortcode's content
395	*
396	* @param $shortcode
397	*
398	* @return bool
399	*/
400	function sh_cd_db_shortcodes_update_content( $id, $data ) {
401
402	if ( false === is_admin() ) {
403	return false;
404	}
405
406	global $wpdb;
407
408	$result = $wpdb->update(
409	$wpdb->prefix . SH_CD_TABLE,
410	[ 'data' => $data ],
411	[ 'id' => $id ],
412	[ '%s' ],
413	[ '%d' ]
414	);
415
416	$shortcode = sh_cd_db_shortcodes_by_id( $id );
417
418	if ( false === empty( $shortcode ) ) {
419
420	if ( 1 === (int) $shortcode[ 'multisite' ] ) {
421	sh_cd_db_shortcodes_multisite_insert( $shortcode['slug'], $shortcode['data'] );
422	} else {
423	sh_cd_db_shortcodes_multisite_delete( $shortcode['slug'] );
424	}
425
426	}
427	sh_cd_cache_delete_by_slug_or_key( $id );
428
429	return ( false !== $result );
430	}
431
432
433	/**
434	* Delete a shortcode
435	*
436	* @param $id
437	*
438	* @return bool
439	*/
440	function sh_cd_db_shortcodes_delete( $id ) {
441
442	if ( false === is_admin() \|\| false === is_numeric( $id ) ) {
443	return false;
444	}
445
446	global $wpdb;
447
448	// Clear cached version
449	sh_cd_cache_delete_by_slug_or_key( $id );
450
451	$result = $wpdb->delete( $wpdb->prefix . SH_CD_TABLE, [ 'id' => $id ], [ '%d' ] );
452
453	return ( false !== $result );
454	}
455
456	/**
457	* For a given key value array, look up and return the expected MySQL data formats.
458	*
459	* @param $data
460	*
461	* @return array
462	*/
463	function sh_cd_db_get_formats( $data ) {
464
465	$lookup = [
466	'id' => '%d',
467	'slug' => '%s',
468	'previous_slug' => '%s',
469	'data' => '%s',
470	'disabled' => '%d',
471	'multisite' => '%d',
472	'site_id' => '%d'
473	];
474
475	$formats = [];
476
477	foreach ( $data as $key => $value ) {
478
479	if ( false === empty( $lookup[ $key ] ) ) {
480	$formats[] = $lookup[ $key ];
481	}
482	}
483
484	return $formats;
485	}
486
487	/**
488	* Check if the slug already exists
489	*
490	* @param $slug
491	* @param $existing_id
492	*
493	* @return bool
494	*/
495	function sh_cd_slug_is_unique( $slug, $existing_id = NULL ) {
496
497	if ( true === empty( $slug ) ) {
498	return false;
499	}
500
501	global $wpdb;
502
503	$sql = $wpdb->prepare( 'SELECT count(slug) FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where slug = %s', $slug );
504
505	if ( false === empty( $existing_id ) ) {
506	$sql .= $wpdb->prepare( ' and id <> %d', $existing_id );
507	}
508
509	$row = $wpdb->get_var( $sql );
510
511	return ( empty( $row ) );
512	}
513