sureforms / inc / database / base.php

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

874 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 number of rows inserted, 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	return $this->wpdb->insert( $this->get_tablename(), $prepared_data['data'], $format );
546	}
547
548	/**
549	* Update a row data of current table. Basically, a wrapper method for wpdb::update.
550	*
551	* @param array<string,mixed> $data Data to update (in column => value pairs).
552	* Both $data columns and $data values should be "raw" (neither should be SQL escaped).
553	* Sending a null value will cause the column to be set to NULL - the corresponding
554	* format is ignored in this case.
555	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
556	* Multiple clauses will be joined with ANDs.
557	* Both $where columns and $where values should be "raw".
558	* Sending a null value will create an IS NULL comparison - the corresponding
559	* format will be ignored in this case.
560	* @since 0.0.13
561	* @return int\|false The number of rows updated, or false on error.
562	*/
563	public function use_update( $data, $where ) { // phpcs:ignore PSR2.Methods.MethodDeclaration.Underscore -- It is okay. This is our wrapper method.
564	$prepared_data = $this->prepare_data( $data, true );
565
566	/**
567	* Data format specifier.
568	*
569	* @var array<string>\|string\|null
570	*/
571	$format = $prepared_data['format'];
572
573	return $this->wpdb->update(
574	$this->get_tablename(),
575	$data,
576	$where,
577	$format
578	);
579	}
580
581	/**
582	* Delete a row data of current table. Basically, a wrapper method for wpdb::delete.
583	*
584	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
585	* Multiple clauses will be joined with ANDs.
586	* Both $where columns and $where values should be "raw".
587	* Sending a null value will create an IS NULL comparison - the corresponding
588	* format will be ignored in this case.
589	* @param string[]\|string $where_format Optional. An array of formats to be mapped to each of the values in $where.
590	* If string, that format will be used for all of the items in $where.
591	* A format is one of '%d', '%f', '%s' (integer, float, string).
592	* If omitted, all values in $data will be treated as strings unless otherwise
593	* specified in wpdb::$field_types. Default null.
594	* @since 0.0.13
595	* @return int\|false The number of rows deleted, or false on error.
596	*/
597	public function use_delete( $where, $where_format = null ) {
598	return $this->wpdb->delete( $this->get_tablename(), $where, $where_format );
599	}
600
601	/**
602	* Retrieve results from the database based on the given WHERE clauses and selected columns.
603	*
604	* This method builds a SQL SELECT query with optional WHERE clauses and retrieves the results
605	* from the database. The results are cached to improve performance on subsequent requests.
606	*
607	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
608	* Each key represents a column name, and each value is the value
609	* to match. If the value is an array, it will be used in an IN clause.
610	* Example: ['column1' => 'value1', 'column2' => ['value2', 'value3']].
611	* Default is an empty array.
612	* @param string $columns Optional. A string specifying which columns to select. Defaults to '*' (all columns).
613	* @param array<string> $extra_queries Optional. Array of extra queries to append at the end of main query.
614	* @param boolean $decode Optional. Whether to decode the results by datatype. Default is true.
615	* @since 0.0.10
616	* @return array<mixed> An associative array of results where each element represents a row, or an empty array if no results are found.
617	*/
618	public function get_results( $where_clauses = [], $columns = '*', $extra_queries = [], $decode = true ) {
619	$wpdb = $this->wpdb;
620
621	$table_name = $this->get_tablename();
622
623	// Start building the query.
624	$query = "SELECT {$columns} FROM {$table_name}";
625
626	// If there are WHERE clauses, prepare and append them to the query.
627	$query .= $this->prepare_where_clauses( $where_clauses );
628
629	if ( ! empty( $extra_queries ) ) {
630	$query .= ' ' . implode( ' ', array_map( 'trim', $extra_queries ) );
631	}
632
633	// Add a semicolon at the end of the query.
634	$query = rtrim( trim( $query ), ';' ) . ';';
635
636	$cached_results = $this->cache_get( $query );
637	if ( $cached_results ) {
638	// Return the cached data if exists.
639	return Helper::get_array_value( $cached_results );
640	}
641
642	// phpcs:ignore
643	$results = $wpdb->get_results( $query, ARRAY_A );
644
645	if ( $decode && ! empty( $results ) && is_array( $results ) ) {
646	foreach ( $results as &$result ) {
647	$result = $this->decode_by_datatype( $result );
648	}
649	}
650
651	// Execute the query and return results.
652	return Helper::get_array_value( $this->cache_set( $query, $results ) );
653	}
654
655	/**
656	* Get the total number of rows in the table.
657	*
658	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
659	* @since 0.0.13
660	* @return int The total number of rows in the table.
661	*/
662	public function get_total_count( $where_clauses = [] ) {
663	$wpdb = $this->wpdb;
664
665	$table_name = $this->get_tablename();
666
667	// Start building the query.
668	$query = "SELECT COUNT(*) FROM {$table_name}";
669
670	// If there are WHERE clauses, prepare and append them to the query.
671	$query .= $this->prepare_where_clauses( $where_clauses );
672
673	// Add a semicolon at the end of the query.
674	$query = rtrim( trim( $query ), ';' ) . ';';
675
676	$cached_results = $this->cache_get( $query );
677	if ( $cached_results ) {
678	// Return the cached data if exists.
679	return Helper::get_integer_value( $cached_results );
680	}
681
682	// phpcs:ignore
683	$results = Helper::get_integer_value( $wpdb->get_var( $query ) );
684
685	// Execute the query and return the integer count.
686	return Helper::get_integer_value( $this->cache_set( $query, $results ) );
687	}
688
689	/**
690	* Prepares WHERE clauses for a SQL query based on the provided conditions.
691	*
692	* This method constructs a WHERE statement by iterating through the
693	* specified conditions, appending them with the appropriate SQL syntax.
694	* It supports both single key-value pairs and arrays of conditions.
695	*
696	* @param array<mixed> $where_clauses {
697	* An associative array of conditions to include in the WHERE clause.
698	*
699	* @type string\|array $key The column name or an array of conditions.
700	* @type array $value {
701	* An associative array of comparison data.
702	*
703	* @type string $key The column name for comparison.
704	* @type string $compare The comparison operator (e.g., '=', 'LIKE').
705	* @type mixed $value The value to compare against.
706	* @type string $RELATION Optional. The logical relation ('AND' or 'OR').
707	* }
708	* }
709	*
710	* @since 0.0.13
711	* @return string The prepared SQL WHERE clause with placeholders, or an empty string if no clauses were provided.
712	*/
713	protected function prepare_where_clauses( $where_clauses = [] ) {
714	if ( empty( $where_clauses ) ) {
715	return '';
716	}
717
718	$wpdb = $this->wpdb;
719
720	// If there are WHERE clauses, prepare and append them to the query.
721	if ( is_array( $where_clauses ) ) {
722	$where = '';
723	$values = [];
724	$schema = $this->get_schema();
725
726	foreach ( $where_clauses as $key => $value ) {
727
728	$relation = ! empty( $value['RELATION'] ) ? trim( $value['RELATION'] ) : 'AND';
729
730	if ( is_int( $key ) ) {
731	foreach ( $value as $_key => $_value ) {
732	if ( is_int( $_key ) ) {
733	if ( 'LIKE' === $_value['compare'] ) {
734	$where .= ' ' . $_value['key'] . ' ' . $_value['compare'] . ' "%%' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) ) . '%%" ' . $relation;
735	} else {
736	$where .= ' ' . $_value['key'] . ' ' . $_value['compare'] . ' ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) ) . ' ' . $relation;
737	}
738	$values[] = $_value['value'];
739	}
740	}
741	continue;
742	}
743
744	if ( ! isset( $schema[ $key ] ) ) {
745	// Skip strictly if current key is not in our schema.
746	continue;
747	}
748
749	$where .= ' ' . $key . ' = ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $key ]['type'] ) ) . ' ' . $relation;
750	$values[] = $value;
751	}
752
753	if ( ! $where ) {
754	return '';
755	}
756
757	$where = ' WHERE ' . trim( trim( $where, $relation ) );
758
759	// Prepare the query with placeholders.
760	// @phpstan-ignore-next-line -- We are already assigning non-literal string above using "get_format_by_datatype" methods.
761	return $wpdb->prepare( $where, ...$values ); // phpcs:ignore -- We are returning prepared sql query here. We are already using necessary placeholders in $where variable.
762	}
763
764	return '';
765	}
766
767	/**
768	* Prepare and format data based on the schema.
769	*
770	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to process.
771	* Missing values will be replaced with default values specified in the schema.
772	* @param boolean $skip_defaults Whether or not to skip the defaults values. Pass true if updating the data.
773	* @since 0.0.10
774	* @return array<array<mixed>> An associative array containing:
775	* - 'data': Prepared data with values encoded according to their data types.
776	* - 'format': An array of format specifiers corresponding to the data values.
777	*/
778	protected function prepare_data( $data, $skip_defaults = false ) {
779	$_data = [];
780	$format = [];
781	foreach ( $this->get_schema() as $key => $value ) {
782	// Process defaults.
783	if ( ! isset( $data[ $key ] ) ) {
784	if ( $skip_defaults \|\| ! isset( $value['default'] ) ) {
785	continue;
786	}
787	$data[ $key ] = $value['default'];
788	}
789
790	$format[] = $this->get_format_by_datatype( $value['type'] ); // Format for the WP database methods.
791	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value['type'] );
792	}
793	return [
794	'data' => $_data,
795	'format' => $format,
796	];
797	}
798
799	/**
800	* Get the SQL format specifier based on the provided data type.
801	*
802	* @param string $type The data type for which to get the SQL format specifier.
803	* Possible values: 'string', 'array', 'number', 'boolean'.
804	* @since 0.0.10
805	* @return string The SQL format specifier. One of '%s' for string or array (converted to JSON), '%d' for number or boolean.
806	*/
807	protected function get_format_by_datatype( $type ) {
808	$format = '%s';
809	switch ( $type ) {
810	case 'string':
811	case 'array': // Because array will be converted to json string.
812	$format = '%s';
813	break;
814
815	case 'number':
816	case 'boolean':
817	$format = '%d';
818	break;
819	}
820
821	return $format;
822	}
823
824	/**
825	* Decode data based on the schema data types.
826	*
827	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to decode.
828	* The data will be decoded if the column type in the schema is 'array' (JSON string).
829	* @since 0.0.10
830	* @return array<mixed> An associative array of decoded data based on the schema.
831	*/
832	protected function decode_by_datatype( $data ) {
833	$_data = [];
834	foreach ( $this->get_schema() as $key => $schema ) {
835	if ( ! array_key_exists( $key, $data ) ) {
836	continue;
837	}
838
839	// Lets decode from JSON to Array for the results.
840	$_data[ $key ] = 'array' === $schema['type'] ? Helper::get_array_value( json_decode( Helper::get_string_value( $data[ $key ] ), true ) ) : $data[ $key ];
841	}
842	return $_data;
843	}
844
845	/**
846	* Encode a value based on the specified data type.
847	*
848	* @param mixed $value The value to encode. The encoding will depend on the data type specified.
849	* @param string $type The data type for encoding. Possible values: 'string', 'number', 'boolean', 'array'.
850	* @since 0.0.10
851	* @return mixed The encoded value. The type of the return value depends on the specified type:
852	* - 'string': Encoded as a string.
853	* - 'number': Encoded as an integer.
854	* - 'boolean': Encoded as a boolean.
855	* - 'array': Encoded as a JSON string.
856	*/
857	protected function encode_by_datatype( $value, $type ) {
858	switch ( $type ) {
859	case 'string':
860	return Helper::get_string_value( $value );
861
862	case 'number':
863	return Helper::get_integer_value( $value );
864
865	case 'boolean':
866	return boolval( $value );
867
868	case 'array':
869	// Lets json_encode array values instead of serializing it.
870	return Helper::encode_json( Helper::get_array_value( $value ) );
871	}
872	}
873	}
874