sureforms / inc / database / base.php

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

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