sureforms / inc / database / base.php

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

995 lines 31.2 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. A failed ALTER leaves the table
425	// version un-bumped, so it retries on every request — expose the underlying
426	// error so persistent failures are diagnosable (hook for logging/monitoring).
427	$this->db_upgradable = false;
428
429	/**
430	* Fires when a SureForms DB schema-upgrade query fails.
431	*
432	* @since 2.11.0
433	* @param string $last_error The DB error message ( $wpdb->last_error ).
434	* @param string $query The ALTER query that failed.
435	* @param string $table The table being altered.
436	*/
437	do_action( 'srfm_db_upgrade_query_failed', $this->wpdb->last_error, $query, $this->get_tablename() );
438	}
439
440	return $result;
441	}
442
443	return false;
444	}
445
446	/**
447	* Returns an array columns of current table.
448	*
449	* @since 0.0.13
450	* @return array<string,array<string,mixed>>
451	*/
452	public function get_columns() {
453	$wpdb = $this->wpdb;
454
455	$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.
456
457	if ( empty( $columns ) ) {
458	return [];
459	}
460
461	$_columns = [];
462	if ( is_array( $columns ) ) {
463	foreach ( $columns as $column ) {
464	if ( ! is_string( $column['Field'] ) ) {
465	continue;
466	}
467
468	$_columns[ $column['Field'] ] = $column;
469	}
470	}
471	return $_columns;
472	}
473
474	/**
475	* Returns an array indexes of current table.
476	*
477	* @since 0.0.13
478	* @return array<mixed>
479	*/
480	public function get_indexes() {
481	$wpdb = $this->wpdb;
482
483	$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.
484
485	if ( empty( $indexes ) ) {
486	return [];
487	}
488
489	$_indexes = [];
490	if ( is_array( $indexes ) ) {
491	foreach ( $indexes as $index ) {
492	$_indexes[ $index['Key_name'] ] = $index; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
493	}
494	}
495	return $_indexes;
496	}
497
498	/**
499	* Insert data. Basically, a wrapper method for wpdb::insert.
500	*
501	* @param array<mixed> $data Data to insert (in column => value pairs).
502	* Both `$data` columns and `$data` values should be "raw" (neither should be SQL escaped).
503	* Sending a null value will cause the column to be set to NULL - the corresponding
504	* format is ignored in this case.
505	* @param array<string>\|string\|null $format Optional. An array of formats to be mapped to each of the value in `$data`.
506	* If string, that format will be used for all of the values in `$data`.
507	* A format is one of '%d', '%f', '%s' (integer, float, string).
508	* If omitted, all values in `$data` will be treated as strings unless otherwise
509	* specified in wpdb::$field_types. Default null.
510	* @since 0.0.10
511	* @return int\|false The id of the inserted entry, or false on error.
512	*/
513	public function use_insert( $data, $format = null ) {
514	$prepared_data = $this->prepare_data( $data );
515
516	if ( is_null( $format ) ) {
517	/**
518	* Use formats from schema if not provided explicitly.
519	*
520	* @var array<string>\|string\|null $format Format specifier for the data.
521	*/
522	$format = $prepared_data['format'];
523	}
524
525	$result = $this->wpdb->insert( $this->get_tablename(), $prepared_data['data'], $format );
526
527	// Reset cache so subsequent queries in the same request include the new row.
528	$this->cache_reset();
529
530	return $result ? $this->wpdb->insert_id : false;
531	}
532
533	/**
534	* Update a row data of current table. Basically, a wrapper method for wpdb::update.
535	*
536	* @param array<string,mixed> $data Data to update (in column => value pairs).
537	* Both $data columns and $data values should be "raw" (neither should be SQL escaped).
538	* Sending a null value will cause the column to be set to NULL - the corresponding
539	* format is ignored in this case.
540	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
541	* Multiple clauses will be joined with ANDs.
542	* Both $where columns and $where values should be "raw".
543	* Sending a null value will create an IS NULL comparison - the corresponding
544	* format will be ignored in this case.
545	* @since 0.0.13
546	* @return int\|false The number of rows updated, or false on error.
547	*/
548	public function use_update( $data, $where ) { // phpcs:ignore PSR2.Methods.MethodDeclaration.Underscore -- It is okay. This is our wrapper method.
549	$prepared_data = $this->prepare_data( $data, true );
550
551	/**
552	* Data format specifier.
553	*
554	* @var array<string>\|string\|null $format Format specifier for the data.
555	*/
556	$format = $prepared_data['format'];
557
558	// Reset the cache on update.
559	$this->cache_reset();
560
561	return $this->wpdb->update(
562	$this->get_tablename(),
563	$prepared_data['data'],
564	$where,
565	$format
566	);
567	}
568
569	/**
570	* Delete a row data of current table. Basically, a wrapper method for wpdb::delete.
571	*
572	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
573	* Multiple clauses will be joined with ANDs.
574	* Both $where columns and $where values should be "raw".
575	* Sending a null value will create an IS NULL comparison - the corresponding
576	* format will be ignored in this case.
577	* @param array<string>\|string $where_format Optional. An array of formats to be mapped to each of the values in $where.
578	* If string, that format will be used for all of the items in $where.
579	* A format is one of '%d', '%f', '%s' (integer, float, string).
580	* If omitted, all values in $data will be treated as strings unless otherwise
581	* specified in wpdb::$field_types. Default null.
582	* @since 0.0.13
583	* @return int\|false The number of rows deleted, or false on error.
584	*/
585	public function use_delete( $where, $where_format = null ) {
586	return $this->wpdb->delete( $this->get_tablename(), $where, $where_format );
587	}
588
589	/**
590	* Retrieve results from the database based on the given WHERE clauses and selected columns.
591	*
592	* This method builds a SQL SELECT query with optional WHERE clauses and retrieves the results
593	* from the database. The results are cached to improve performance on subsequent requests.
594	*
595	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
596	* Each key represents a column name, and each value is the value
597	* to match. If the value is an array, it will be used in an IN clause.
598	* Example: ['column1' => 'value1', 'column2' => ['value2', 'value3']].
599	* Default is an empty array.
600	* @param string $columns Optional. A string specifying which columns to select. Defaults to '*' (all columns).
601	* @param array<string> $extra_queries Optional. Array of extra queries to append at the end of main query.
602	* @param bool $decode Optional. Whether to decode the results by datatype. Default is true.
603	* @since 0.0.10
604	* @return array<mixed> An associative array of results where each element represents a row, or an empty array if no results are found.
605	*/
606	public function get_results( $where_clauses = [], $columns = '*', $extra_queries = [], $decode = true ) {
607	$wpdb = $this->wpdb;
608
609	$table_name = $this->get_tablename();
610
611	// Start building the query.
612	$query = "SELECT {$columns} FROM {$table_name}";
613
614	// If there are WHERE clauses, prepare and append them to the query.
615	$query .= $this->prepare_where_clauses( $where_clauses );
616
617	if ( ! empty( $extra_queries ) ) {
618	$query .= ' ' . implode( ' ', array_map( 'trim', $extra_queries ) );
619	}
620
621	// Add a semicolon at the end of the query.
622	$query = rtrim( trim( $query ), ';' ) . ';';
623
624	$cached_results = $this->cache_get( $query );
625	if ( $cached_results ) {
626	// Return the cached data if exists.
627	return Helper::get_array_value( $cached_results );
628	}
629
630	// phpcs:ignore
631	$results = $wpdb->get_results( $query, ARRAY_A );
632
633	if ( $decode && ! empty( $results ) && is_array( $results ) ) {
634	foreach ( $results as &$result ) {
635	$result = $this->decode_by_datatype( $result );
636	}
637	}
638
639	// Execute the query and return results.
640	return Helper::get_array_value( $this->cache_set( $query, $results ) );
641	}
642
643	/**
644	* Retrieves a list of records based on the provided arguments.
645	*
646	* This method fetches results from the database, allowing for various
647	* customization options such as filtering, pagination, and sorting.
648	*
649	* @param array<string,mixed> $args {
650	* Optional. An array of arguments to customize the query.
651	*
652	* @type array $where An associative array of conditions to filter the results.
653	* @type int $limit The maximum number of results to return. Default is 10.
654	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
655	* @type string $orderby The column by which to order the results. Default is 'created_at'.
656	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
657	* }
658	* @param bool $set_limit Whether to set the limit on the query. Default is true.
659	*
660	* @since 1.13.0
661	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
662	*/
663	public function get_records_by_args( $args = [], $set_limit = true ) {
664	$_args = wp_parse_args(
665	$args,
666	[
667	'where' => [],
668	'columns' => '*',
669	'limit' => 10,
670	'offset' => 0,
671	'orderby' => 'created_at',
672	'order' => 'DESC',
673	]
674	);
675	$allowed_orderby = $this->get_allowed_orderby_columns();
676	$orderby = in_array( $_args['orderby'], $allowed_orderby, true ) ? $_args['orderby'] : 'created_at';
677	$order = 'ASC' === strtoupper( Helper::get_string_value( $_args['order'] ) ) ? 'ASC' : 'DESC';
678	$extra_queries = [
679	sprintf( 'ORDER BY `%1$s` %2$s', $orderby, $order ),
680	];
681
682	if ( $set_limit ) {
683	$extra_queries[] = sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) );
684	}
685	return $this->get_results(
686	$_args['where'],
687	$_args['columns'],
688	$extra_queries
689	);
690	}
691
692	/**
693	* Get the total number of rows in the table.
694	*
695	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
696	* @since 0.0.13
697	* @return int The total number of rows in the table.
698	*/
699	public function get_total_count( $where_clauses = [] ) {
700	$wpdb = $this->wpdb;
701
702	$table_name = $this->get_tablename();
703
704	// Start building the query.
705	$query = "SELECT COUNT(*) FROM {$table_name}";
706
707	// If there are WHERE clauses, prepare and append them to the query.
708	$query .= $this->prepare_where_clauses( $where_clauses );
709
710	// Add a semicolon at the end of the query.
711	$query = rtrim( trim( $query ), ';' ) . ';';
712
713	$cached_results = $this->cache_get( $query );
714	if ( $cached_results ) {
715	// Return the cached data if exists.
716	return Helper::get_integer_value( $cached_results );
717	}
718
719	// phpcs:ignore
720	$results = Helper::get_integer_value( $wpdb->get_var( $query ) );
721
722	// Execute the query and return the integer count.
723	return Helper::get_integer_value( $this->cache_set( $query, $results ) );
724	}
725
726	/**
727	* Get the allowed column names for ORDER BY clauses.
728	* Child classes may override this method to restrict orderable columns further.
729	*
730	* @since 2.6.0
731	* @return array<string>
732	*/
733	protected function get_allowed_orderby_columns() {
734	return array_merge( array_keys( $this->get_schema() ), [ 'updated_at' ] );
735	}
736
737	/**
738	* Retrieve a cached value by its key.
739	*
740	* @param string $key The cache key.
741	* @since 0.0.10
742	* @return mixed\|null The cached value if it exists, or null if the key does not exist in the cache.
743	*/
744	protected function cache_get( $key ) {
745	$key = md5( $key );
746	if ( ! isset( $this->caches[ $key ] ) ) {
747	return null;
748	}
749	return $this->caches[ $key ];
750	}
751
752	/**
753	* Store a value in the cache with the specified key.
754	*
755	* @param string $key The cache key.
756	* @param mixed $value The value to store in the cache.
757	* @since 0.0.10
758	* @return mixed The stored value.
759	*/
760	protected function cache_set( $key, $value ) {
761	$key = md5( $key );
762	$this->caches[ $key ] = $value;
763	return $value;
764	}
765
766	/**
767	* Reset the cache by clearing all stored values.
768	*
769	* @since 0.0.10
770	* @return void
771	*/
772	protected function cache_reset() {
773	$this->caches = [];
774	}
775
776	/**
777	* Prepares WHERE clauses for a SQL query based on the provided conditions.
778	*
779	* This method constructs a WHERE statement by iterating through the
780	* specified conditions, appending them with the appropriate SQL syntax.
781	* It supports both single key-value pairs and arrays of conditions.
782	*
783	* @param array<mixed> $where_clauses {
784	* An associative array of conditions to include in the WHERE clause.
785	*
786	* @type string\|array $key The column name or an array of conditions.
787	* @type array $value {
788	* An associative array of comparison data.
789	*
790	* @type string $key The column name for comparison.
791	* @type string $compare The comparison operator (e.g., '=', 'LIKE').
792	* @type mixed $value The value to compare against.
793	* @type string $RELATION Optional. The logical relation ('AND' or 'OR').
794	* }
795	* }
796	*
797	* @since 1.1.1 -- Added support for "IN" compare.
798	* @since 0.0.13
799	* @return string The prepared SQL WHERE clause with placeholders, or an empty string if no clauses were provided.
800	*/
801	protected function prepare_where_clauses( $where_clauses = [] ) {
802	if ( empty( $where_clauses ) ) {
803	return '';
804	}
805
806	$wpdb = $this->wpdb;
807
808	// If there are WHERE clauses, prepare and append them to the query.
809	if ( is_array( $where_clauses ) ) {
810	$groups = [];
811	$values = [];
812	$schema = $this->get_schema();
813
814	foreach ( $where_clauses as $key => $value ) {
815
816	$relation = ! empty( $value['RELATION'] ) ? trim( $value['RELATION'] ) : 'AND';
817	$relation = in_array( strtoupper( $relation ), [ 'AND', 'OR' ], true ) ? strtoupper( $relation ) : 'AND';
818
819	if ( is_int( $key ) ) {
820	$clause_parts = [];
821	foreach ( $value as $_key => $_value ) {
822	if ( is_int( $_key ) ) {
823	// Check if the operator is allowed.
824	if ( ! in_array( $_value['compare'], $this->allowed_where_operators, true ) ) {
825	continue;
826	}
827
828	// Skip if key is not in schema.
829	if ( ! isset( $schema[ $_value['key'] ] ) ) {
830	continue;
831	}
832
833	switch ( $_value['compare'] ) {
834	case 'LIKE':
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	case 'IN':
840	// 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).
841	$datatype = $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
842	$clause_parts[] = $_value['key'] . ' ' . $_value['compare'] . ' (' . implode( ', ', array_fill( 0, count( $_value['value'] ), $datatype ) ) . ')';
843	$values = array_merge( $values, $_value['value'] );
844	break;
845
846	default:
847	$clause_parts[] = $_value['key'] . ' ' . $_value['compare'] . ' ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
848	$values[] = $_value['value'];
849	break;
850	}
851	}
852	}
853
854	if ( ! empty( $clause_parts ) ) {
855	$groups[] = '(' . implode( ' ' . $relation . ' ', $clause_parts ) . ')';
856	}
857	continue;
858	}
859
860	if ( ! isset( $schema[ $key ] ) ) {
861	// Skip strictly if current key is not in our schema.
862	continue;
863	}
864
865	$groups[] = '(' . $key . ' = ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $key ]['type'] ) ) . ')';
866	$values[] = $value;
867	}
868
869	if ( empty( $groups ) ) {
870	return '';
871	}
872
873	$where = ' WHERE ' . implode( ' AND ', $groups );
874
875	// Prepare the query with placeholders.
876	// @phpstan-ignore-next-line -- We are already assigning non-literal string above using "get_format_by_datatype" methods.
877	return $wpdb->prepare( $where, ...$values ); // phpcs:ignore -- We are returning prepared sql query here. We are already using necessary placeholders in $where variable.
878	}
879
880	return '';
881	}
882
883	/**
884	* Prepare and format data based on the schema.
885	*
886	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to process.
887	* Missing values will be replaced with default values specified in the schema.
888	* @param bool $skip_defaults Whether or not to skip the defaults values. Pass true if updating the data.
889	* @since 0.0.10
890	* @return array<array<mixed>> An associative array containing:
891	* - 'data': Prepared data with values encoded according to their data types.
892	* - 'format': An array of format specifiers corresponding to the data values.
893	*/
894	protected function prepare_data( $data, $skip_defaults = false ) {
895	$_data = [];
896	$format = [];
897	foreach ( $this->get_schema() as $key => $value ) {
898	// Process defaults.
899	if ( ! isset( $data[ $key ] ) ) {
900	if ( $skip_defaults \|\| ! isset( $value['default'] ) ) {
901	continue;
902	}
903	$data[ $key ] = $value['default'];
904	}
905
906	$format[] = $this->get_format_by_datatype( $value['type'] ); // Format for the WP database methods.
907	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value['type'] );
908	}
909	return [
910	'data' => $_data,
911	'format' => $format,
912	];
913	}
914
915	/**
916	* Get the SQL format specifier based on the provided data type.
917	*
918	* @param string $type The data type for which to get the SQL format specifier.
919	* Possible values: 'string', 'array', 'number', 'boolean'.
920	* @since 0.0.10
921	* @return string The SQL format specifier. One of '%s' for string or array (converted to JSON), '%d' for number or boolean.
922	*/
923	protected function get_format_by_datatype( $type ) {
924	$format = '%s';
925	switch ( $type ) {
926	case 'string':
927	case 'array': // Because array will be converted to json string.
928	$format = '%s';
929	break;
930
931	case 'number':
932	case 'boolean':
933	$format = '%d';
934	break;
935	}
936
937	return $format;
938	}
939
940	/**
941	* Decode data based on the schema data types.
942	*
943	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to decode.
944	* The data will be decoded if the column type in the schema is 'array' (JSON string).
945	* @since 0.0.10
946	* @return array<mixed> An associative array of decoded data based on the schema.
947	*/
948	protected function decode_by_datatype( $data ) {
949	$_data = [];
950	foreach ( $this->get_schema() as $key => $schema ) {
951	if ( ! array_key_exists( $key, $data ) ) {
952	continue;
953	}
954
955	// Lets decode from JSON to Array for the results.
956	$_data[ $key ] = 'array' === $schema['type'] ? Helper::get_array_value( json_decode( Helper::get_string_value( $data[ $key ] ), true ) ) : $data[ $key ];
957	}
958	return $_data;
959	}
960
961	/**
962	* Encode a value based on the specified data type.
963	*
964	* @param mixed $value The value to encode. The encoding will depend on the data type specified.
965	* @param string $type The data type for encoding. Possible values: 'string', 'number', 'boolean', 'array'.
966	* @since 0.0.10
967	* @return mixed The encoded value. The type of the return value depends on the specified type:
968	* - 'string': Encoded as a string.
969	* - 'number': Encoded as an integer.
970	* - 'boolean': Encoded as a boolean.
971	* - 'array': Encoded as a JSON string.
972	* @since 1.8.0 - 'datetime': Returns the value as it is, assuming it is already in SQL DATETIME format.
973	*/
974	protected function encode_by_datatype( $value, $type ) {
975	switch ( $type ) {
976	case 'string':
977	return Helper::get_string_value( $value );
978
979	case 'number':
980	return Helper::get_integer_value( $value );
981
982	case 'boolean':
983	return boolval( $value );
984
985	case 'array':
986	// Lets json_encode array values instead of serializing it.
987	return Helper::encode_json( Helper::get_array_value( $value ) );
988
989	case 'datetime':
990	// For datetime, we will return the value as it is because we are using sql DATETIME format.
991	return $value;
992	}
993	}
994	}
995