sureforms / inc / database / base.php

base.php in SureForms – Contact Form Builder, AI Forms, Payment Form, Survey & Quiz 2.8.2, at inc/database/base.php

983 lines 30.7 KB

Raw Download Zip

1	<?php
2	/**
3	* SureForms Database Tables Base Class.
4	*
5	* @link https://sureforms.com
6	* @since 0.0.10
7	* @package SureForms
8	* @author SureForms <https://sureforms.com/>
9	*/
10
11	namespace SRFM\Inc\Database;
12
13	use SRFM\Inc\Helper;
14
15	// Exit if accessed directly.
16	defined( 'ABSPATH' ) \|\| exit;
17
18	/**
19	* SureForms Database Tables Base Class
20	*
21	* @since 0.0.10
22	*/
23	abstract class Base {
24	/**
25	* WordPress Database class instance.
26	*
27	* @var \wpdb
28	* @since 0.0.10
29	*/
30	protected $wpdb;
31
32	/**
33	* Current database table prefix mixed with 'srfm_' as ending.
34	*
35	* @var string
36	* @since 0.0.10
37	*/
38	protected $table_prefix;
39
40	/**
41	* Custom table suffix without any prefix. This needs to be overridden from child class.
42	* Eg: For entries table, suffix will be 'entries' which will be prefixed and finally named as 'wp_srfm_entries'.
43	*
44	* @var string
45	* @since 0.0.10
46	* @override
47	*/
48	protected $table_suffix;
49
50	/**
51	* Version for current custom table. Default is 1.
52	* Unlike semantic versioning [eg: 1.0.0, 1.0.1] we use natural integer like 1, 2, 3... and so on.
53	* Update the table version from child class when any DB upgrade or alteration related changes are made.
54	*
55	* @var int
56	* @since 0.0.13
57	* @override
58	*/
59	protected $table_version = 1;
60
61	/**
62	* Full table name mixed with table prefix and table suffix.
63	*
64	* @var string
65	* @since 0.0.10
66	*/
67	private $table_name;
68
69	/**
70	* Whether or not the current database table is upgradable.
71	* Determines on the basis of the table version.
72	*
73	* @var bool
74	* @since 0.0.13
75	*/
76	private $db_upgradable;
77
78	/**
79	* Current table database result caches.
80	*
81	* @var array<mixed>
82	* @since 0.0.10
83	*/
84	private $caches = [];
85
86	/**
87	* Allowed operators for the database.
88	*
89	* @var array<string>
90	* @since 1.8.0
91	*/
92	private $allowed_where_operators = [ 'LIKE', 'IN', '=', '!=', '>', '<', '>=', '<=' ];
93
94	/**
95	* Init class.
96	*
97	* @since 0.0.10
98	* @return void
99	*/
100	public function __construct() {
101	global $wpdb;
102
103	$this->wpdb = $wpdb;
104	$this->table_prefix = $this->wpdb->prefix . 'srfm_';
105	$this->table_name = $this->table_prefix . $this->table_suffix;
106	}
107
108	/**
109	* Actions to initialize during object unload.
110	*
111	* @since 0.0.13
112	* @return void
113	*/
114	public function __destruct() {
115	/**
116	* Just incase if any developer forgets to stop the db upgrade after starting.
117	* This fallback handling will take care of such scenarios.
118	*/
119	$this->stop_db_upgrade();
120	}
121
122	/**
123	* Returns the current table schema.
124	*
125	* @since 0.0.10
126	* @return array<string,array<mixed>>
127	*/
128	abstract public function get_schema();
129
130	/**
131	* Current table columns definition to create table. These definitions will be used by the create() method.
132	*
133	* @since 0.0.13
134	* @return array<string>
135	*/
136	abstract public function get_columns_definition();
137
138	/**
139	* Any columns that needs to be added if the current table already exists. These definitions will be used by maybe_add_new_columns() method.
140	* Override this from child class if needed.
141	*
142	* @since 0.0.13
143	* @return array<string>
144	* @override
145	*/
146	public function get_new_columns_definition() {
147	return [];
148	}
149
150	/**
151	* Array of columns that needs to be renamed to new column name. It will be used by maybe_rename_columns() method.
152	* Format:
153	* [
154	* [
155	* 'from' => 'old_column_name',
156	* 'to' => 'new_column_name',
157	* 'type' => 'column type definition eg: LONGTEXT', // Optional.
158	* ],
159	* ]
160	*
161	* @since 0.0.13
162	* @return array<array<string,string>>
163	*/
164	public function get_columns_to_rename() {
165	return [];
166	}
167
168	/**
169	* Start the database upgrade process.
170	*
171	* @since 0.0.13
172	* @return void
173	*/
174	public function start_db_upgrade() {
175	$versions = Helper::get_array_value( get_option( 'srfm_database_table_versions', [] ) );
176	$prev_version = ! empty( $versions[ $this->table_suffix ] ) ? absint( $versions[ $this->table_suffix ] ) : false;
177
178	if ( ! $prev_version ) {
179	/**
180	* If we are here then there is the chance that
181	* this site is the new site or fresh setup.
182	*/
183	$this->db_upgradable = true;
184	return;
185	}
186
187	$this->db_upgradable = $this->table_version > $prev_version;
188	}
189
190	/**
191	* Stop the database upgrade process.
192	*
193	* @since 0.0.13
194	* @return bool Returns true on success.
195	*/
196	public function stop_db_upgrade() {
197	if ( ! $this->db_upgradable ) {
198	// Only upgrade when it is needed.
199	return false;
200	}
201
202	$versions = Helper::get_array_value( get_option( 'srfm_database_table_versions', [] ) );
203
204	$versions[ $this->table_suffix ] = $this->table_version;
205
206	update_option( 'srfm_database_table_versions', $versions );
207
208	return true;
209	}
210
211	/**
212	* Check if current table's DB is upgradable or not.
213	*
214	* @since 0.0.13
215	* @return bool True or false depending if DB is upgradable or not.
216	*/
217	public function is_db_upgradable() {
218	return $this->db_upgradable;
219	}
220
221	/**
222	* Returns full table name.
223	*
224	* @since 0.0.10
225	* @return string
226	*/
227	public function get_tablename() {
228	return $this->table_name;
229	}
230
231	/**
232	* Conditionally returns current database charset or collate.
233	*
234	* @since 0.0.10
235	* @return string
236	*/
237	public function get_charset_collate() {
238	$charset_collate = '';
239
240	if ( $this->wpdb->has_cap( 'collation' ) ) {
241	if ( ! empty( $this->wpdb->charset ) ) {
242	$charset_collate = "DEFAULT CHARACTER SET {$this->wpdb->charset}";
243	}
244	if ( ! empty( $this->wpdb->collate ) ) {
245	$charset_collate .= " COLLATE {$this->wpdb->collate}";
246	}
247	}
248
249	return $charset_collate;
250	}
251
252	/**
253	* Create table.
254	*
255	* @param array<string> $columns Array of columns.
256	* @since 0.0.10
257	* @return int\|bool
258	*/
259	public function create( $columns = [] ) {
260	if ( ! $this->db_upgradable ) {
261	// Only upgrade when it is needed.
262	return false;
263	}
264
265	if ( empty( $columns ) ) {
266	return false; // It's better to return a boolean for failure.
267	}
268
269	// Prepare columns list.
270	$columns_list = implode(
271	', ',
272	$columns
273	);
274
275	$wpdb = $this->wpdb;
276
277	// Execute the query.
278	$query = $wpdb->prepare( 'CREATE TABLE IF NOT EXISTS %1s ( %2s ) %3s', $this->get_tablename(), $columns_list, $this->get_charset_collate() ); // phpcs:ignore -- It is okay to use complex placeholder here for the table name, column list and character set because we don't want to quote these variables.
279
280	if ( ! $query ) {
281	// If we are here, then we probably have bad query to work with and prepare method has returned null-ish value.
282	return false;
283	}
284
285	$result = $wpdb->query( $query ); // phpcs:ignore -- We are already using prepare above.
286
287	if ( false === $result ) {
288	// Stop DB alteration if we have any error.
289	$this->db_upgradable = false;
290	}
291
292	return $result;
293	}
294
295	/**
296	* Rename the column of the current table conditionally.
297	*
298	* @param array<array<string,string>> $rename_columns Array of columns to rename.
299	* @since 0.0.13
300	* @return int\|bool Boolean true for CREATE, ALTER, TRUNCATE and DROP queries. Number of rows affected/selected for all other queries. Boolean false on error.
301	*/
302	public function maybe_rename_columns( $rename_columns = [] ) {
303	if ( ! $rename_columns ) {
304	return false;
305	}
306
307	if ( ! $this->db_upgradable ) {
308	// Only upgrade when it is needed.
309	return false;
310	}
311
312	$existing_columns = $this->get_columns();
313
314	if ( ! $existing_columns ) {
315	// Table does not exists or is new table.
316	return false;
317	}
318
319	$wpdb = $this->wpdb;
320
321	$query_parts = [];
322	foreach ( $rename_columns as $column ) {
323	if ( empty( $existing_columns[ $column['from'] ] ) ) {
324	// Bail if column is already renamed or does not exists.
325	continue;
326	}
327
328	$query_part = $wpdb->prepare(
329	'CHANGE %1s %2s %3s', // phpcs:ignore -- It is okay to use complex placeholders as we don't want values to be quoted.
330	$column['from'],
331	$column['to'],
332	! empty( $column['type'] ) ? $column['type'] : $existing_columns[ $column['from'] ]['Type'] // This is column type i.e LONGTEXT, BIGINT etc.
333	);
334
335	if ( is_string( $query_part ) && $query_part ) {
336	$query_parts[] = trim( $query_part );
337	}
338	}
339
340	if ( empty( $query_parts ) ) {
341	// No renaming required.
342	return false;
343	}
344
345	$result = $wpdb->query( $wpdb->prepare( 'ALTER TABLE %1s ', $this->get_tablename() ) . implode( ', ', $query_parts ) . ';' ); // phpcs:ignore -- It is okay to use query directly here.
346
347	if ( false === $result ) {
348	// Stop DB alteration if we have any error.
349	$this->db_upgradable = false;
350	}
351
352	return $result;
353	}
354
355	/**
356	* Adds the new columns to the current table conditionally.
357	*
358	* @param array<string> $new_columns The array of new columns to add. Same as the create method.
359	* @since 0.0.13
360	* @return int\|bool Boolean true for CREATE, ALTER, TRUNCATE and DROP queries. Number of rows affected/selected for all other queries. Boolean false on error.
361	*/
362	public function maybe_add_new_columns( $new_columns = [] ) {
363	if ( ! $new_columns ) {
364	return false;
365	}
366
367	if ( ! $this->db_upgradable ) {
368	// Only upgrade when it is needed.
369	return false;
370	}
371
372	$existing_columns = $this->get_columns();
373
374	if ( ! $existing_columns ) {
375	// Table does not exists or is new table.
376	return false;
377	}
378
379	$existing_indexes = $this->get_indexes();
380
381	$alter_queries = [];
382
383	$wpdb = $this->wpdb;
384
385	// Check and add each column if it does not exist.
386	foreach ( $new_columns as $column_definition ) {
387	preg_match( '/INDEX\s+(.*?)\s+\(/', $column_definition, $index_matches );
388
389	if ( ! empty( $index_matches[1] ) ) {
390	if ( isset( $existing_indexes[ $index_matches[1] ] ) ) {
391	// Move to next element if current index already exists.
392	continue;
393	}
394	// Stack and move to next if we are indexing.
395	$alter_queries[] = $wpdb->prepare( 'ADD %1s', $column_definition ); // phpcs:ignore -- We don't need quote here.
396	continue;
397	}
398
399	preg_match( '/(\w+)\s/', $column_definition, $column_matches );
400	$column_name = $column_matches[1] ?? '';
401
402	// If the column does not exist, add it.
403	if ( ! isset( $existing_columns[ $column_name ] ) ) {
404	$alter_queries[] = $wpdb->prepare( 'ADD COLUMN %1s', $column_definition ); // phpcs:ignore -- We don't need quote here.
405	}
406	}
407
408	if ( $alter_queries ) {
409	$query = $wpdb->prepare(
410	'ALTER TABLE %1s %2s', // phpcs:ignore -- We don't want to quote the value strings for the query.
411	$this->get_tablename(),
412	implode( ', ', $alter_queries )
413	);
414
415	if ( ! $query ) {
416	// If we are here then we probably have bad query and prepare method has returned null.
417	return false;
418	}
419
420	// Execute the query.
421	$result = $wpdb->query( $query ); // phpcs:ignore -- It is okay. We are already using prepare above and we need to do DB query directly here.
422
423	if ( false === $result ) {
424	// Stop DB alteration if we have any error.
425	$this->db_upgradable = false;
426	}
427
428	return $result;
429	}
430
431	return false;
432	}
433
434	/**
435	* Returns an array columns of current table.
436	*
437	* @since 0.0.13
438	* @return array<string,array<string,mixed>>
439	*/
440	public function get_columns() {
441	$wpdb = $this->wpdb;
442
443	$columns = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %1s', $this->get_tablename() ), ARRAY_A ); // phpcs:ignore -- It is okay to use query db directly here.
444
445	if ( empty( $columns ) ) {
446	return [];
447	}
448
449	$_columns = [];
450	if ( is_array( $columns ) ) {
451	foreach ( $columns as $column ) {
452	if ( ! is_string( $column['Field'] ) ) {
453	continue;
454	}
455
456	$_columns[ $column['Field'] ] = $column;
457	}
458	}
459	return $_columns;
460	}
461
462	/**
463	* Returns an array indexes of current table.
464	*
465	* @since 0.0.13
466	* @return array<mixed>
467	*/
468	public function get_indexes() {
469	$wpdb = $this->wpdb;
470
471	$indexes = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %1s', $this->get_tablename() ), ARRAY_A ); // phpcs:ignore -- We don't need quote here so this is fine.
472
473	if ( empty( $indexes ) ) {
474	return [];
475	}
476
477	$_indexes = [];
478	if ( is_array( $indexes ) ) {
479	foreach ( $indexes as $index ) {
480	$_indexes[ $index['Key_name'] ] = $index; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
481	}
482	}
483	return $_indexes;
484	}
485
486	/**
487	* Insert data. Basically, a wrapper method for wpdb::insert.
488	*
489	* @param array<mixed> $data Data to insert (in column => value pairs).
490	* Both `$data` columns and `$data` values should be "raw" (neither should be SQL escaped).
491	* Sending a null value will cause the column to be set to NULL - the corresponding
492	* format is ignored in this case.
493	* @param array<string>\|string\|null $format Optional. An array of formats to be mapped to each of the value in `$data`.
494	* If string, that format will be used for all of the values in `$data`.
495	* A format is one of '%d', '%f', '%s' (integer, float, string).
496	* If omitted, all values in `$data` will be treated as strings unless otherwise
497	* specified in wpdb::$field_types. Default null.
498	* @since 0.0.10
499	* @return int\|false The id of the inserted entry, or false on error.
500	*/
501	public function use_insert( $data, $format = null ) {
502	$prepared_data = $this->prepare_data( $data );
503
504	if ( is_null( $format ) ) {
505	/**
506	* Use formats from schema if not provided explicitly.
507	*
508	* @var array<string>\|string\|null $format Format specifier for the data.
509	*/
510	$format = $prepared_data['format'];
511	}
512
513	$result = $this->wpdb->insert( $this->get_tablename(), $prepared_data['data'], $format );
514
515	// Reset cache so subsequent queries in the same request include the new row.
516	$this->cache_reset();
517
518	return $result ? $this->wpdb->insert_id : false;
519	}
520
521	/**
522	* Update a row data of current table. Basically, a wrapper method for wpdb::update.
523	*
524	* @param array<string,mixed> $data Data to update (in column => value pairs).
525	* Both $data columns and $data values should be "raw" (neither should be SQL escaped).
526	* Sending a null value will cause the column to be set to NULL - the corresponding
527	* format is ignored in this case.
528	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
529	* Multiple clauses will be joined with ANDs.
530	* Both $where columns and $where values should be "raw".
531	* Sending a null value will create an IS NULL comparison - the corresponding
532	* format will be ignored in this case.
533	* @since 0.0.13
534	* @return int\|false The number of rows updated, or false on error.
535	*/
536	public function use_update( $data, $where ) { // phpcs:ignore PSR2.Methods.MethodDeclaration.Underscore -- It is okay. This is our wrapper method.
537	$prepared_data = $this->prepare_data( $data, true );
538
539	/**
540	* Data format specifier.
541	*
542	* @var array<string>\|string\|null $format Format specifier for the data.
543	*/
544	$format = $prepared_data['format'];
545
546	// Reset the cache on update.
547	$this->cache_reset();
548
549	return $this->wpdb->update(
550	$this->get_tablename(),
551	$prepared_data['data'],
552	$where,
553	$format
554	);
555	}
556
557	/**
558	* Delete a row data of current table. Basically, a wrapper method for wpdb::delete.
559	*
560	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
561	* Multiple clauses will be joined with ANDs.
562	* Both $where columns and $where values should be "raw".
563	* Sending a null value will create an IS NULL comparison - the corresponding
564	* format will be ignored in this case.
565	* @param array<string>\|string $where_format Optional. An array of formats to be mapped to each of the values in $where.
566	* If string, that format will be used for all of the items in $where.
567	* A format is one of '%d', '%f', '%s' (integer, float, string).
568	* If omitted, all values in $data will be treated as strings unless otherwise
569	* specified in wpdb::$field_types. Default null.
570	* @since 0.0.13
571	* @return int\|false The number of rows deleted, or false on error.
572	*/
573	public function use_delete( $where, $where_format = null ) {
574	return $this->wpdb->delete( $this->get_tablename(), $where, $where_format );
575	}
576
577	/**
578	* Retrieve results from the database based on the given WHERE clauses and selected columns.
579	*
580	* This method builds a SQL SELECT query with optional WHERE clauses and retrieves the results
581	* from the database. The results are cached to improve performance on subsequent requests.
582	*
583	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
584	* Each key represents a column name, and each value is the value
585	* to match. If the value is an array, it will be used in an IN clause.
586	* Example: ['column1' => 'value1', 'column2' => ['value2', 'value3']].
587	* Default is an empty array.
588	* @param string $columns Optional. A string specifying which columns to select. Defaults to '*' (all columns).
589	* @param array<string> $extra_queries Optional. Array of extra queries to append at the end of main query.
590	* @param bool $decode Optional. Whether to decode the results by datatype. Default is true.
591	* @since 0.0.10
592	* @return array<mixed> An associative array of results where each element represents a row, or an empty array if no results are found.
593	*/
594	public function get_results( $where_clauses = [], $columns = '*', $extra_queries = [], $decode = true ) {
595	$wpdb = $this->wpdb;
596
597	$table_name = $this->get_tablename();
598
599	// Start building the query.
600	$query = "SELECT {$columns} FROM {$table_name}";
601
602	// If there are WHERE clauses, prepare and append them to the query.
603	$query .= $this->prepare_where_clauses( $where_clauses );
604
605	if ( ! empty( $extra_queries ) ) {
606	$query .= ' ' . implode( ' ', array_map( 'trim', $extra_queries ) );
607	}
608
609	// Add a semicolon at the end of the query.
610	$query = rtrim( trim( $query ), ';' ) . ';';
611
612	$cached_results = $this->cache_get( $query );
613	if ( $cached_results ) {
614	// Return the cached data if exists.
615	return Helper::get_array_value( $cached_results );
616	}
617
618	// phpcs:ignore
619	$results = $wpdb->get_results( $query, ARRAY_A );
620
621	if ( $decode && ! empty( $results ) && is_array( $results ) ) {
622	foreach ( $results as &$result ) {
623	$result = $this->decode_by_datatype( $result );
624	}
625	}
626
627	// Execute the query and return results.
628	return Helper::get_array_value( $this->cache_set( $query, $results ) );
629	}
630
631	/**
632	* Retrieves a list of records based on the provided arguments.
633	*
634	* This method fetches results from the database, allowing for various
635	* customization options such as filtering, pagination, and sorting.
636	*
637	* @param array<string,mixed> $args {
638	* Optional. An array of arguments to customize the query.
639	*
640	* @type array $where An associative array of conditions to filter the results.
641	* @type int $limit The maximum number of results to return. Default is 10.
642	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
643	* @type string $orderby The column by which to order the results. Default is 'created_at'.
644	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
645	* }
646	* @param bool $set_limit Whether to set the limit on the query. Default is true.
647	*
648	* @since 1.13.0
649	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
650	*/
651	public function get_records_by_args( $args = [], $set_limit = true ) {
652	$_args = wp_parse_args(
653	$args,
654	[
655	'where' => [],
656	'columns' => '*',
657	'limit' => 10,
658	'offset' => 0,
659	'orderby' => 'created_at',
660	'order' => 'DESC',
661	]
662	);
663	$allowed_orderby = $this->get_allowed_orderby_columns();
664	$orderby = in_array( $_args['orderby'], $allowed_orderby, true ) ? $_args['orderby'] : 'created_at';
665	$order = 'ASC' === strtoupper( Helper::get_string_value( $_args['order'] ) ) ? 'ASC' : 'DESC';
666	$extra_queries = [
667	sprintf( 'ORDER BY `%1$s` %2$s', $orderby, $order ),
668	];
669
670	if ( $set_limit ) {
671	$extra_queries[] = sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) );
672	}
673	return $this->get_results(
674	$_args['where'],
675	$_args['columns'],
676	$extra_queries
677	);
678	}
679
680	/**
681	* Get the total number of rows in the table.
682	*
683	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
684	* @since 0.0.13
685	* @return int The total number of rows in the table.
686	*/
687	public function get_total_count( $where_clauses = [] ) {
688	$wpdb = $this->wpdb;
689
690	$table_name = $this->get_tablename();
691
692	// Start building the query.
693	$query = "SELECT COUNT(*) FROM {$table_name}";
694
695	// If there are WHERE clauses, prepare and append them to the query.
696	$query .= $this->prepare_where_clauses( $where_clauses );
697
698	// Add a semicolon at the end of the query.
699	$query = rtrim( trim( $query ), ';' ) . ';';
700
701	$cached_results = $this->cache_get( $query );
702	if ( $cached_results ) {
703	// Return the cached data if exists.
704	return Helper::get_integer_value( $cached_results );
705	}
706
707	// phpcs:ignore
708	$results = Helper::get_integer_value( $wpdb->get_var( $query ) );
709
710	// Execute the query and return the integer count.
711	return Helper::get_integer_value( $this->cache_set( $query, $results ) );
712	}
713
714	/**
715	* Get the allowed column names for ORDER BY clauses.
716	* Child classes may override this method to restrict orderable columns further.
717	*
718	* @since 2.6.0
719	* @return array<string>
720	*/
721	protected function get_allowed_orderby_columns() {
722	return array_merge( array_keys( $this->get_schema() ), [ 'updated_at' ] );
723	}
724
725	/**
726	* Retrieve a cached value by its key.
727	*
728	* @param string $key The cache key.
729	* @since 0.0.10
730	* @return mixed\|null The cached value if it exists, or null if the key does not exist in the cache.
731	*/
732	protected function cache_get( $key ) {
733	$key = md5( $key );
734	if ( ! isset( $this->caches[ $key ] ) ) {
735	return null;
736	}
737	return $this->caches[ $key ];
738	}
739
740	/**
741	* Store a value in the cache with the specified key.
742	*
743	* @param string $key The cache key.
744	* @param mixed $value The value to store in the cache.
745	* @since 0.0.10
746	* @return mixed The stored value.
747	*/
748	protected function cache_set( $key, $value ) {
749	$key = md5( $key );
750	$this->caches[ $key ] = $value;
751	return $value;
752	}
753
754	/**
755	* Reset the cache by clearing all stored values.
756	*
757	* @since 0.0.10
758	* @return void
759	*/
760	protected function cache_reset() {
761	$this->caches = [];
762	}
763
764	/**
765	* Prepares WHERE clauses for a SQL query based on the provided conditions.
766	*
767	* This method constructs a WHERE statement by iterating through the
768	* specified conditions, appending them with the appropriate SQL syntax.
769	* It supports both single key-value pairs and arrays of conditions.
770	*
771	* @param array<mixed> $where_clauses {
772	* An associative array of conditions to include in the WHERE clause.
773	*
774	* @type string\|array $key The column name or an array of conditions.
775	* @type array $value {
776	* An associative array of comparison data.
777	*
778	* @type string $key The column name for comparison.
779	* @type string $compare The comparison operator (e.g., '=', 'LIKE').
780	* @type mixed $value The value to compare against.
781	* @type string $RELATION Optional. The logical relation ('AND' or 'OR').
782	* }
783	* }
784	*
785	* @since 1.1.1 -- Added support for "IN" compare.
786	* @since 0.0.13
787	* @return string The prepared SQL WHERE clause with placeholders, or an empty string if no clauses were provided.
788	*/
789	protected function prepare_where_clauses( $where_clauses = [] ) {
790	if ( empty( $where_clauses ) ) {
791	return '';
792	}
793
794	$wpdb = $this->wpdb;
795
796	// If there are WHERE clauses, prepare and append them to the query.
797	if ( is_array( $where_clauses ) ) {
798	$groups = [];
799	$values = [];
800	$schema = $this->get_schema();
801
802	foreach ( $where_clauses as $key => $value ) {
803
804	$relation = ! empty( $value['RELATION'] ) ? trim( $value['RELATION'] ) : 'AND';
805	$relation = in_array( strtoupper( $relation ), [ 'AND', 'OR' ], true ) ? strtoupper( $relation ) : 'AND';
806
807	if ( is_int( $key ) ) {
808	$clause_parts = [];
809	foreach ( $value as $_key => $_value ) {
810	if ( is_int( $_key ) ) {
811	// Check if the operator is allowed.
812	if ( ! in_array( $_value['compare'], $this->allowed_where_operators, true ) ) {
813	continue;
814	}
815
816	// Skip if key is not in schema.
817	if ( ! isset( $schema[ $_value['key'] ] ) ) {
818	continue;
819	}
820
821	switch ( $_value['compare'] ) {
822	case 'LIKE':
823	$clause_parts[] = $_value['key'] . ' ' . $_value['compare'] . ' "%%' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) ) . '%%"';
824	$values[] = $_value['value'];
825	break;
826
827	case 'IN':
828	// Based on the number of values and datatype, it will create WHERE clause for $wpdb::prepare method. Eg: for ID with three values column: ID IN (%d, %d, %d).
829	$datatype = $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
830	$clause_parts[] = $_value['key'] . ' ' . $_value['compare'] . ' (' . implode( ', ', array_fill( 0, count( $_value['value'] ), $datatype ) ) . ')';
831	$values = array_merge( $values, $_value['value'] );
832	break;
833
834	default:
835	$clause_parts[] = $_value['key'] . ' ' . $_value['compare'] . ' ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
836	$values[] = $_value['value'];
837	break;
838	}
839	}
840	}
841
842	if ( ! empty( $clause_parts ) ) {
843	$groups[] = '(' . implode( ' ' . $relation . ' ', $clause_parts ) . ')';
844	}
845	continue;
846	}
847
848	if ( ! isset( $schema[ $key ] ) ) {
849	// Skip strictly if current key is not in our schema.
850	continue;
851	}
852
853	$groups[] = '(' . $key . ' = ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $key ]['type'] ) ) . ')';
854	$values[] = $value;
855	}
856
857	if ( empty( $groups ) ) {
858	return '';
859	}
860
861	$where = ' WHERE ' . implode( ' AND ', $groups );
862
863	// Prepare the query with placeholders.
864	// @phpstan-ignore-next-line -- We are already assigning non-literal string above using "get_format_by_datatype" methods.
865	return $wpdb->prepare( $where, ...$values ); // phpcs:ignore -- We are returning prepared sql query here. We are already using necessary placeholders in $where variable.
866	}
867
868	return '';
869	}
870
871	/**
872	* Prepare and format data based on the schema.
873	*
874	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to process.
875	* Missing values will be replaced with default values specified in the schema.
876	* @param bool $skip_defaults Whether or not to skip the defaults values. Pass true if updating the data.
877	* @since 0.0.10
878	* @return array<array<mixed>> An associative array containing:
879	* - 'data': Prepared data with values encoded according to their data types.
880	* - 'format': An array of format specifiers corresponding to the data values.
881	*/
882	protected function prepare_data( $data, $skip_defaults = false ) {
883	$_data = [];
884	$format = [];
885	foreach ( $this->get_schema() as $key => $value ) {
886	// Process defaults.
887	if ( ! isset( $data[ $key ] ) ) {
888	if ( $skip_defaults \|\| ! isset( $value['default'] ) ) {
889	continue;
890	}
891	$data[ $key ] = $value['default'];
892	}
893
894	$format[] = $this->get_format_by_datatype( $value['type'] ); // Format for the WP database methods.
895	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value['type'] );
896	}
897	return [
898	'data' => $_data,
899	'format' => $format,
900	];
901	}
902
903	/**
904	* Get the SQL format specifier based on the provided data type.
905	*
906	* @param string $type The data type for which to get the SQL format specifier.
907	* Possible values: 'string', 'array', 'number', 'boolean'.
908	* @since 0.0.10
909	* @return string The SQL format specifier. One of '%s' for string or array (converted to JSON), '%d' for number or boolean.
910	*/
911	protected function get_format_by_datatype( $type ) {
912	$format = '%s';
913	switch ( $type ) {
914	case 'string':
915	case 'array': // Because array will be converted to json string.
916	$format = '%s';
917	break;
918
919	case 'number':
920	case 'boolean':
921	$format = '%d';
922	break;
923	}
924
925	return $format;
926	}
927
928	/**
929	* Decode data based on the schema data types.
930	*
931	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to decode.
932	* The data will be decoded if the column type in the schema is 'array' (JSON string).
933	* @since 0.0.10
934	* @return array<mixed> An associative array of decoded data based on the schema.
935	*/
936	protected function decode_by_datatype( $data ) {
937	$_data = [];
938	foreach ( $this->get_schema() as $key => $schema ) {
939	if ( ! array_key_exists( $key, $data ) ) {
940	continue;
941	}
942
943	// Lets decode from JSON to Array for the results.
944	$_data[ $key ] = 'array' === $schema['type'] ? Helper::get_array_value( json_decode( Helper::get_string_value( $data[ $key ] ), true ) ) : $data[ $key ];
945	}
946	return $_data;
947	}
948
949	/**
950	* Encode a value based on the specified data type.
951	*
952	* @param mixed $value The value to encode. The encoding will depend on the data type specified.
953	* @param string $type The data type for encoding. Possible values: 'string', 'number', 'boolean', 'array'.
954	* @since 0.0.10
955	* @return mixed The encoded value. The type of the return value depends on the specified type:
956	* - 'string': Encoded as a string.
957	* - 'number': Encoded as an integer.
958	* - 'boolean': Encoded as a boolean.
959	* - 'array': Encoded as a JSON string.
960	* @since 1.8.0 - 'datetime': Returns the value as it is, assuming it is already in SQL DATETIME format.
961	*/
962	protected function encode_by_datatype( $value, $type ) {
963	switch ( $type ) {
964	case 'string':
965	return Helper::get_string_value( $value );
966
967	case 'number':
968	return Helper::get_integer_value( $value );
969
970	case 'boolean':
971	return boolval( $value );
972
973	case 'array':
974	// Lets json_encode array values instead of serializing it.
975	return Helper::encode_json( Helper::get_array_value( $value ) );
976
977	case 'datetime':
978	// For datetime, we will return the value as it is because we are using sql DATETIME format.
979	return $value;
980	}
981	}
982	}
983