imagify / inc / classes / class-imagify-abstract-db.php

class-imagify-abstract-db.php in Imagify Image Optimization: Optimize Images | Compress & Convert to WebP/AVIF 2.3.0, at inc/classes/class-imagify-abstract-db.php

859 lines 19.8 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Imagify DB base class.
5	*
6	* @since 1.5
7	* @source https://gist.github.com/pippinsplugins/e220a7f0f0f2fbe64608
8	*/
9	abstract class Imagify_Abstract_DB extends Imagify_Abstract_DB_Deprecated implements \Imagify\DB\DBInterface {
10	/**
11	* Class version.
12	*
13	* @var string
14	*/
15	const VERSION = '1.3';
16
17	/**
18	* Suffix used in the name of the options that store the table versions.
19	*
20	* @var string
21	* @since 1.7
22	*/
23	const TABLE_VERSION_OPTION_SUFFIX = '_db_version';
24
25	/**
26	* The suffix used in the name of the database table (so, without the wpdb prefix).
27	*
28	* @var string
29	* @since 1.7
30	* @access protected
31	*/
32	protected $table;
33
34	/**
35	* The version of our database table.
36	*
37	* @var int
38	* @since 1.5
39	* @since 1.7 Not public anymore, now an integer.
40	* @access protected
41	*/
42	protected $table_version;
43
44	/**
45	* Tell if the table is the same for each site of a Multisite.
46	*
47	* @var bool
48	* @since 1.7
49	* @access protected
50	*/
51	protected $table_is_global;
52
53	/**
54	* The name of the primary column.
55	*
56	* @var string
57	* @since 1.5
58	* @since 1.7 Not public anymore.
59	* @access protected
60	*/
61	protected $primary_key;
62
63	/**
64	* The name of our database table.
65	*
66	* @var string
67	* @since 1.5
68	* @since 1.7 Not public anymore.
69	* @access protected
70	*/
71	protected $table_name = '';
72
73	/**
74	* Tell if the table has been created.
75	*
76	* @var bool
77	* @since 1.7
78	* @access protected
79	*/
80	protected $table_created = false;
81
82	/**
83	* Stores the list of columns that must be (un)serialized.
84	*
85	* @var array
86	* @since 1.7
87	* @access protected
88	*/
89	protected $to_serialize;
90
91	/**
92	* Get things started.
93	*
94	* @since 1.5
95	* @access protected
96	*/
97	protected function __construct() {
98	global $wpdb;
99
100	$prefix = $this->table_is_global ? $wpdb->base_prefix : $wpdb->prefix;
101
102	$this->table_name = $prefix . $this->table;
103
104	if ( ! $this->table_is_up_to_date() ) {
105	/**
106	* The option doesn't exist or is not up-to-date: we must upgrade the table before declaring it ready.
107	* See self::maybe_upgrade_table() for the upgrade.
108	*/
109	return;
110	}
111
112	$this->set_table_ready();
113	}
114
115	/**
116	* Init:
117	* - Launch hooks.
118	*
119	* @since 1.7
120	* @access public
121	* @author Grégory Viguier
122	*/
123	public function init() {
124	add_action( 'admin_init', [ $this, 'maybe_upgrade_table' ] );
125	}
126
127	/**
128	* Tell if we can work with the tables.
129	*
130	* @since 1.7
131	* @access public
132	* @author Grégory Viguier
133	*
134	* @return bool
135	*/
136	public function can_operate() {
137	return $this->table_created;
138	}
139
140
141	/** ----------------------------------------------------------------------------------------- */
142	/** TABLE SPECIFICS ========================================================================= */
143	/** ----------------------------------------------------------------------------------------- */
144
145	/**
146	* Get the column placeholders.
147	*
148	* @since 1.5
149	* @access public
150	*
151	* @return array
152	*/
153	abstract public function get_columns();
154
155	/**
156	* Default column values.
157	*
158	* @since 1.5
159	* @access public
160	*
161	* @return array
162	*/
163	abstract public function get_column_defaults();
164
165	/**
166	* Get the query to create the table fields.
167	*
168	* @since 1.7
169	* @access protected
170	* @author Grégory Viguier
171	*
172	* @return string
173	*/
174	abstract protected function get_table_schema();
175
176
177	/** ----------------------------------------------------------------------------------------- */
178	/** QUERIES ================================================================================= */
179	/** ----------------------------------------------------------------------------------------- */
180
181	/**
182	* Tell if the table is empty or not.
183	*
184	* @since 1.7
185	* @access public
186	* @author Grégory Viguier
187	*
188	* @return bool True if the table contains at least one row.
189	*/
190	public function has_items() {
191	global $wpdb;
192
193	$column = esc_sql( $this->primary_key );
194
195	return (bool) $wpdb->get_var( "SELECT $column FROM $this->table_name LIMIT 1;" ); // WPCS: unprepared SQL ok.
196	}
197
198	/**
199	* Retrieve a row by the primary key.
200	*
201	* @since 1.5
202	* @access public
203	*
204	* @param string $row_id A primary key.
205	* @return array
206	*/
207	public function get( $row_id ) {
208	if ( $row_id <= 0 ) {
209	return [];
210	}
211
212	return $this->get_by( $this->primary_key, $row_id );
213	}
214
215	/**
216	* Retrieve a row by a specific column / value.
217	*
218	* @since 1.5
219	* @access public
220	*
221	* @param string $column_where A column name.
222	* @param mixed $column_value A value.
223	* @return array
224	*/
225	public function get_by( $column_where, $column_value ) {
226	global $wpdb;
227
228	$placeholder = $this->get_placeholder( $column_where );
229	$column_where = esc_sql( $column_where );
230
231	$result = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $this->table_name WHERE $column_where = $placeholder LIMIT 1;", $column_value ), ARRAY_A ); // WPCS: unprepared SQL ok, PreparedSQLPlaceholders replacement count ok.
232
233	return (array) $this->cast_row( $result );
234	}
235
236	/**
237	* Retrieve a row by the specified column / values.
238	*
239	* @since 1.7
240	* @access public
241	* @author Grégory Viguier
242	*
243	* @param string $column_where A column name.
244	* @param array $column_values An array of values.
245	* @return array
246	*/
247	public function get_in( $column_where, $column_values ) {
248	global $wpdb;
249
250	$column_where = esc_sql( $column_where );
251	$column_values = Imagify_DB::prepare_values_list( $column_values );
252
253	$result = $wpdb->get_row( "SELECT * FROM $this->table_name WHERE $column_where IN ( $column_values ) LIMIT 1;", ARRAY_A ); // WPCS: unprepared SQL ok.
254
255	return (array) $this->cast_row( $result );
256	}
257
258	/**
259	* Retrieve a var by the primary key.
260	* Previously named get_column().
261	*
262	* @since 1.7
263	* @access public
264	* @author Grégory Viguier
265	*
266	* @param string $column_select A column name.
267	* @param string $row_id A primary key.
268	* @return mixed
269	*/
270	public function get_var( $column_select, $row_id ) {
271	if ( $row_id <= 0 ) {
272	return false;
273	}
274
275	return $this->get_var_by( $column_select, $this->primary_key, $row_id );
276	}
277
278	/**
279	* Retrieve a var by the specified column / value.
280	* Previously named get_column_by().
281	*
282	* @since 1.7
283	* @access public
284	* @author Grégory Viguier
285	*
286	* @param string $column_select A column name.
287	* @param string $column_where A column name.
288	* @param string $column_value A value.
289	* @return mixed
290	*/
291	public function get_var_by( $column_select, $column_where, $column_value ) {
292	global $wpdb;
293
294	$placeholder = $this->get_placeholder( $column_where );
295	$column = esc_sql( $column_select );
296	$column_where = esc_sql( $column_where );
297
298	$result = $wpdb->get_var( $wpdb->prepare( "SELECT $column FROM $this->table_name WHERE $column_where = $placeholder LIMIT 1;", $column_value ) ); // WPCS: unprepared SQL ok, PreparedSQLPlaceholders replacement count ok.
299
300	return $this->cast( $result, $column_select );
301	}
302
303	/**
304	* Retrieve a var by the specified column / values.
305	* Previously named get_column_in().
306	*
307	* @since 1.7
308	* @access public
309	* @author Grégory Viguier
310	*
311	* @param string $column_select A column name.
312	* @param string $column_where A column name.
313	* @param array $column_values An array of values.
314	* @return mixed
315	*/
316	public function get_var_in( $column_select, $column_where, $column_values ) {
317	global $wpdb;
318
319	$column = esc_sql( $column_select );
320	$column_where = esc_sql( $column_where );
321	$column_values = Imagify_DB::prepare_values_list( $column_values );
322
323	$result = $wpdb->get_var( "SELECT $column FROM $this->table_name WHERE $column_where IN ( $column_values ) LIMIT 1;" ); // WPCS: unprepared SQL ok.
324
325	return $this->cast( $result, $column_select );
326	}
327
328	/**
329	* Retrieve values by the specified column / values.
330	*
331	* @since 1.7
332	* @access public
333	* @author Grégory Viguier
334	*
335	* @param string $column_select A column name.
336	* @param string $column_where A column name.
337	* @param array $column_values An array of values.
338	* @return array
339	*/
340	public function get_column_in( $column_select, $column_where, $column_values ) {
341	global $wpdb;
342
343	$column = esc_sql( $column_select );
344	$column_where = esc_sql( $column_where );
345	$column_values = Imagify_DB::prepare_values_list( $column_values );
346
347	$result = $wpdb->get_col( "SELECT $column FROM $this->table_name WHERE $column_where IN ( $column_values );" ); // WPCS: unprepared SQL ok.
348
349	return $this->cast_col( $result, $column_select );
350	}
351
352	/**
353	* Retrieve values by the specified column / values.
354	*
355	* @since 1.7
356	* @access public
357	* @author Grégory Viguier
358	*
359	* @param string $column_select A column name.
360	* @param string $column_where A column name.
361	* @param array $column_values An array of values.
362	* @return array
363	*/
364	public function get_column_not_in( $column_select, $column_where, $column_values ) {
365	global $wpdb;
366
367	$column = esc_sql( $column_select );
368	$column_where = esc_sql( $column_where );
369	$column_values = Imagify_DB::prepare_values_list( $column_values );
370
371	$result = $wpdb->get_col( "SELECT $column FROM $this->table_name WHERE $column_where NOT IN ( $column_values );" ); // WPCS: unprepared SQL ok.
372
373	return $this->cast_col( $result, $column_select );
374	}
375
376	/**
377	* Insert a new row.
378	*
379	* @since 1.5
380	* @access public
381	*
382	* @param string $data New data.
383	* @return int The ID.
384	*/
385	public function insert( $data ) {
386	global $wpdb;
387
388	// Initialise column format array.
389	$column_formats = $this->get_columns();
390
391	// Set default values.
392	$data = wp_parse_args( $data, $this->get_column_defaults() );
393
394	// Force fields to lower case.
395	$data = array_change_key_case( $data );
396
397	// White list columns.
398	$data = array_intersect_key( $data, $column_formats );
399
400	// Maybe serialize some values.
401	$data = $this->serialize_columns( $data );
402
403	// Reorder $column_formats to match the order of columns given in $data.
404	$column_formats = array_merge( $data, $column_formats );
405
406	$wpdb->insert( $this->table_name, $data, $column_formats );
407
408	return (int) $wpdb->insert_id;
409	}
410
411	/**
412	* Update a row.
413	*
414	* @since 1.5
415	* @access public
416	*
417	* @param int $row_id A primary key.
418	* @param array $data New data.
419	* @param string $where A column name.
420	* @return bool
421	*/
422	public function update( $row_id, $data = [], $where = '' ) {
423	global $wpdb;
424
425	if ( $row_id <= 0 ) {
426	return false;
427	}
428
429	if ( ! $this->get( $row_id ) ) {
430	$this->insert( $data );
431	return true;
432	}
433
434	if ( empty( $where ) ) {
435	$where = $this->primary_key;
436	}
437
438	// Initialise column format array.
439	$column_formats = $this->get_columns();
440
441	// Force fields to lower case.
442	$data = array_change_key_case( $data );
443
444	// White list columns.
445	$data = array_intersect_key( $data, $column_formats );
446
447	// Maybe serialize some values.
448	$data = $this->serialize_columns( $data );
449
450	// Reorder $column_formats to match the order of columns given in $data.
451	$column_formats = array_merge( $data, $column_formats );
452
453	return (bool) $wpdb->update( $this->table_name, $data, [ $where => $row_id ], $column_formats, $this->get_placeholder( $where ) );
454	}
455
456	/**
457	* Delete a row identified by the primary key.
458	*
459	* @since 1.5
460	* @access public
461	*
462	* @param string $row_id A primary key.
463	* @return bool
464	*/
465	public function delete( $row_id = 0 ) {
466	global $wpdb;
467
468	if ( $row_id <= 0 ) {
469	return false;
470	}
471
472	$placeholder = $this->get_placeholder( $this->primary_key );
473
474	return (bool) $wpdb->query( $wpdb->prepare( "DELETE FROM $this->table_name WHERE $this->primary_key = $placeholder", $row_id ) ); // WPCS: unprepared SQL ok, PreparedSQLPlaceholders replacement count ok.
475	}
476
477
478	/** ----------------------------------------------------------------------------------------- */
479	/** TABLE CREATION ========================================================================== */
480	/** ----------------------------------------------------------------------------------------- */
481
482	/**
483	* Maybe create/upgrade the table in the database.
484	*
485	* @since 1.7
486	* @access public
487	* @author Grégory Viguier
488	*/
489	public function maybe_upgrade_table() {
490	global $wpdb;
491
492	if ( $this->table_is_up_to_date() ) {
493	// The table has the right version.
494	$this->set_table_ready();
495	return;
496	}
497
498	// Create the table.
499	$this->create_table();
500	}
501
502	/**
503	* Create/Upgrade the table in the database.
504	*
505	* @since 1.7
506	* @access public
507	* @author Grégory Viguier
508	*/
509	public function create_table() {
510	if ( ! Imagify_DB::create_table( $this->get_table_name(), $this->get_table_schema() ) ) {
511	// Failure.
512	$this->set_table_not_ready();
513	$this->delete_db_version();
514	return;
515	}
516
517	// Table successfully created/upgraded.
518	$this->set_table_ready();
519	$this->update_db_version();
520	}
521
522	/**
523	* Set various properties to tell the table is ready to be used.
524	*
525	* @since 1.7
526	* @access public
527	* @author Grégory Viguier
528	*/
529	protected function set_table_ready() {
530	global $wpdb;
531
532	$this->table_created = true;
533	$wpdb->{$this->table} = $this->table_name;
534
535	if ( $this->table_is_global ) {
536	$wpdb->global_tables[] = $this->table;
537	} else {
538	$wpdb->tables[] = $this->table;
539	}
540	}
541
542	/**
543	* Unset various properties to tell the table is NOT ready to be used.
544	*
545	* @since 1.7
546	* @access public
547	* @author Grégory Viguier
548	*/
549	protected function set_table_not_ready() {
550	global $wpdb;
551
552	$this->table_created = false;
553	unset( $wpdb->{$this->table} );
554
555	if ( $this->table_is_global ) {
556	$wpdb->global_tables = array_diff( $wpdb->global_tables, [ $this->table ] );
557	} else {
558	$wpdb->tables = array_diff( $wpdb->tables, [ $this->table ] );
559	}
560	}
561
562
563	/** ----------------------------------------------------------------------------------------- */
564	/** TABLE VERSION =========================================================================== */
565	/** ----------------------------------------------------------------------------------------- */
566
567	/**
568	* Get the table version.
569	*
570	* @since 1.7
571	* @access public
572	* @author Grégory Viguier
573	*
574	* @return int
575	*/
576	public function get_table_version() {
577	return $this->table_version;
578	}
579
580	/**
581	* Tell if the table is up-to-date (we don't "downgrade" the tables).
582	*
583	* @since 1.7
584	* @access public
585	* @author Grégory Viguier
586	*
587	* @return bool
588	*/
589	public function table_is_up_to_date() {
590	return $this->get_db_version() >= $this->get_table_version();
591	}
592
593	/**
594	* Get the table version stored in DB.
595	*
596	* @since 1.7
597	* @access public
598	* @author Grégory Viguier
599	*
600	* @return int\|bool The version. False if not set yet.
601	*/
602	public function get_db_version() {
603	$option_name = $this->table . self::TABLE_VERSION_OPTION_SUFFIX;
604
605	if ( $this->table_is_global && is_multisite() ) {
606	return get_site_option( $option_name );
607	}
608
609	return get_option( $option_name );
610	}
611
612	/**
613	* Update the table version stored in DB.
614	*
615	* @since 1.7
616	* @access protected
617	* @author Grégory Viguier
618	*/
619	protected function update_db_version() {
620	$option_name = $this->table . self::TABLE_VERSION_OPTION_SUFFIX;
621
622	if ( $this->table_is_global && is_multisite() ) {
623	update_site_option( $option_name, $this->get_table_version() );
624	} else {
625	update_option( $option_name, $this->get_table_version() );
626	}
627	}
628
629	/**
630	* Delete the table version stored in DB.
631	*
632	* @since 1.7
633	* @access protected
634	* @author Grégory Viguier
635	*/
636	protected function delete_db_version() {
637	$option_name = $this->table . self::TABLE_VERSION_OPTION_SUFFIX;
638
639	if ( $this->table_is_global && is_multisite() ) {
640	delete_site_option( $option_name );
641	} else {
642	delete_option( $option_name );
643	}
644	}
645
646
647	/** ----------------------------------------------------------------------------------------- */
648	/** TOOLS =================================================================================== */
649	/** ----------------------------------------------------------------------------------------- */
650
651	/**
652	* Get the table name.
653	*
654	* @since 1.7
655	* @access public
656	* @author Grégory Viguier
657	*
658	* @return string
659	*/
660	public function get_table_name() {
661	return $this->table_name;
662	}
663
664	/**
665	* Tell if the table is the same for each site of a Multisite.
666	*
667	* @since 1.7
668	* @access public
669	* @author Grégory Viguier
670	*
671	* @return bool
672	*/
673	public function is_table_global() {
674	return $this->table_is_global;
675	}
676
677	/**
678	* Get the primary column name.
679	*
680	* @since 1.7
681	* @access public
682	* @author Grégory Viguier
683	*
684	* @return string
685	*/
686	public function get_primary_key() {
687	return $this->primary_key;
688	}
689
690	/**
691	* Get the formats related to the given columns.
692	*
693	* @since 1.7
694	* @access public
695	* @author Grégory Viguier
696	*
697	* @param array $columns An array of column names (as keys).
698	* @return array
699	*/
700	public function get_column_formats( $columns ) {
701	if ( ! is_array( $columns ) ) {
702	$columns = array_flip( (array) $columns );
703	}
704
705	// White list columns.
706	return array_intersect_key( $this->get_columns(), $columns );
707	}
708
709	/**
710	* Get the placeholder corresponding to the given key.
711	*
712	* @since 1.7
713	* @access public
714	* @author Grégory Viguier
715	*
716	* @param string $key The key.
717	* @return string
718	*/
719	public function get_placeholder( $key ) {
720	$columns = $this->get_columns();
721	return isset( $columns[ $key ] ) ? $columns[ $key ] : '%s';
722	}
723
724	/**
725	* Tell if the column value must be (un)serialized.
726	*
727	* @since 1.7
728	* @access public
729	* @author Grégory Viguier
730	*
731	* @param string $key The key.
732	* @return bool
733	*/
734	public function is_column_serialized( $key ) {
735	$columns = $this->get_column_defaults();
736	return isset( $columns[ $key ] ) && is_array( $columns[ $key ] );
737	}
738
739	/**
740	* Cast a value.
741	*
742	* @since 1.7
743	* @access public
744	* @author Grégory Viguier
745	*
746	* @param mixed $value The value to cast.
747	* @param string $key The corresponding key.
748	* @return mixed
749	*/
750	public function cast( $value, $key ) {
751	if ( null === $value \|\| is_bool( $value ) ) {
752	return $value;
753	}
754
755	$placeholder = $this->get_placeholder( $key );
756
757	if ( '%d' === $placeholder ) {
758	return (int) $value;
759	}
760
761	if ( '%f' === $placeholder ) {
762	return (float) $value;
763	}
764
765	if ( $value && $this->is_column_serialized( $key ) ) {
766	return maybe_unserialize( $value );
767	}
768
769	return $value;
770	}
771
772	/**
773	* Cast a column.
774	*
775	* @since 1.7
776	* @access public
777	* @author Grégory Viguier
778	*
779	* @param array $values The values to cast.
780	* @param string $column The corresponding column name.
781	* @return array
782	*/
783	public function cast_col( $values, $column ) {
784	if ( ! $values ) {
785	return $values;
786	}
787
788	foreach ( $values as $i => $value ) {
789	$values[ $i ] = $this->cast( $value, $column );
790	}
791
792	return $values;
793	}
794
795	/**
796	* Cast a row.
797	*
798	* @since 1.7
799	* @access public
800	* @author Grégory Viguier
801	*
802	* @param array\|object $row_fields A row from the DB.
803	* @return array\|object
804	*/
805	public function cast_row( $row_fields ) {
806	if ( ! $row_fields ) {
807	return $row_fields;
808	}
809
810	if ( is_array( $row_fields ) ) {
811	foreach ( $row_fields as $field => $value ) {
812	$row_fields[ $field ] = $this->cast( $value, $field );
813	}
814	} elseif ( is_object( $row_fields ) ) {
815	foreach ( $row_fields as $field => $value ) {
816	$row_fields->$field = $this->cast( $value, $field );
817	}
818	}
819
820	return $row_fields;
821	}
822
823	/**
824	* Serialize columns that need to be.
825	*
826	* @since 1.7
827	* @access public
828	* @author Grégory Viguier
829	*
830	* @param array $data An array of values.
831	* @return array
832	*/
833	public function serialize_columns( $data ) {
834	if ( ! isset( $this->to_serialize ) ) {
835	$this->to_serialize = array_filter( $this->get_column_defaults(), 'is_array' );
836	}
837
838	if ( ! $this->to_serialize ) {
839	return $data;
840	}
841
842	$serialized_data = array_intersect_key( $data, $this->to_serialize );
843
844	if ( ! $serialized_data ) {
845	return $data;
846	}
847
848	$serialized_data = array_map(
849	function ( $value ) {
850	// Try not to store empty serialized arrays.
851	return [] === $value ? null : maybe_serialize( $value );
852	},
853	$serialized_data
854	);
855
856	return array_merge( $data, $serialized_data );
857	}
858	}
859