sureforms / inc / database / base.php

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

1,315 lines 44.0 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', 'NOT 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	* Whether this table currently exists in the database.
233	*
234	* Deliberately `SHOW TABLES LIKE` rather than the existing get_columns():
235	* `SHOW COLUMNS FROM <missing table>` is a MySQL error, so it pollutes
236	* $wpdb->last_error, prints under WP_DEBUG_DISPLAY, and cannot tell "the table
237	* is gone" apart from "SHOW is denied". This returns a clean empty set instead.
238	*
239	* esc_like() matters because $wpdb->prefix contains `_`, which is a LIKE
240	* wildcard — without it `wp_srfm_entries` would also match `wpXsrfm_entries`.
241	* The comparison is against the real, unescaped name so the match stays exact.
242	*
243	* Fails safe: any DB-level error reports the table as present. A false "your
244	* database needs updating" on a transient connection blip is worse than a
245	* missed one, because the notice it drives asks the user to alter their schema.
246	*
247	* @since 2.12.6
248	* @return bool True when the table exists, or when existence cannot be determined.
249	*/
250	public function table_exists() {
251	$wpdb = $this->wpdb;
252	$table = $this->get_tablename();
253
254	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema lookup; the caller owns caching, and a cached answer here would defeat the check.
255	$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
256
257	if ( ! empty( $wpdb->last_error ) ) {
258	return true;
259	}
260
261	return $found === $table;
262	}
263
264	/**
265	* A table holding this table's data under a different prefix, if there is one.
266	*
267	* Changing `$table_prefix` — a manual edit, a restored dump from a site with a
268	* different prefix, or a security plugin that renames tables and misses the ones
269	* it does not know about — leaves our data behind under the old name while the
270	* plugin looks for the new one. Creating a fresh empty table there would strand
271	* every stored entry, so look for the old one first and adopt it instead.
272	*
273	* Refuses to guess. Returns '' unless exactly one credible candidate exists, and
274	* only when that candidate carries every column this table's schema declares —
275	* an unrelated table that merely ends in the same words is never touched.
276	*
277	* On multisite, other blogs' tables are legitimate and belong to those blogs.
278	* Anything matching the `{base_prefix}{digits}_` pattern, or the base prefix
279	* itself, is excluded so a subsite can never adopt another subsite's data.
280	*
281	* @since 2.12.6
282	* @return string Full table name to adopt, or '' when there is nothing safe to adopt.
283	*/
284	public function find_adoptable_table() {
285	$wpdb = $this->wpdb;
286	$correct = $this->get_tablename();
287	$needle = 'srfm_' . $this->table_suffix;
288
289	// Wildcard on the left only: the name must end at the suffix, so a
290	// deliberate copy such as `wp_srfm_entries_backup` is never a candidate.
291	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema lookup; a cached answer would defeat the check.
292	$found = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', '%' . $wpdb->esc_like( $needle ) ) );
293
294	if ( ! empty( $wpdb->last_error ) \|\| ! is_array( $found ) ) {
295	return '';
296	}
297
298	$base = $wpdb->base_prefix;
299	$blog_table = '/^' . preg_quote( $base, '/' ) . '\d+_' . preg_quote( $needle, '/' ) . '$/';
300	$candidates = [];
301
302	foreach ( $found as $table ) {
303	$table = (string) $table;
304
305	// The table we are looking for, another blog's table, or the network's
306	// main-site table — none of these are ours to rename.
307	if ( $table === $correct \|\| $base . $needle === $table \|\| preg_match( $blog_table, $table ) ) {
308	continue;
309	}
310
311	$candidates[] = $table;
312	}
313
314	// More than one and we cannot tell which holds the real data. Refuse rather
315	// than pick, and let the caller fall back to creating an empty table.
316	if ( 1 !== count( $candidates ) ) {
317	return '';
318	}
319
320	return $this->has_expected_columns( $candidates[0] ) ? $candidates[0] : '';
321	}
322
323	/**
324	* Rename a differently-prefixed table into this table's expected name.
325	*
326	* RENAME rather than create-and-copy: it is atomic, needs no second copy of the
327	* data, and cannot half-succeed and leave rows in two places.
328	*
329	* @param string $from Full name of the table to adopt.
330	* @since 2.12.6
331	* @return bool True when the table is in place afterwards.
332	*/
333	public function adopt_table( $from ) {
334	$wpdb = $this->wpdb;
335	$to = $this->get_tablename();
336
337	if ( empty( $from ) \|\| $from === $to ) {
338	return false;
339	}
340
341	// Never rename over an existing table; the one already in place wins.
342	if ( $this->table_exists() ) {
343	return true;
344	}
345
346	$query = $wpdb->prepare( 'RENAME TABLE %1s TO %2s', str_replace( '`', '', $from ), str_replace( '`', '', $to ) ); // phpcs:ignore -- Same complex-placeholder pattern as create(): identifiers must not be quoted, and both names come from SHOW TABLES / $wpdb->prefix.
347
348	if ( ! $query ) {
349	// prepare() returned nothing usable; do not fall through to a raw query.
350	return false;
351	}
352
353	$wpdb->query( $query ); // phpcs:ignore -- We are already using prepare above, and one-off DDL has nothing to cache.
354
355	if ( ! empty( $wpdb->last_error ) ) {
356	/** This action is documented in inc/database/base.php */
357	do_action( 'srfm_db_upgrade_query_failed', $wpdb->last_error, 'RENAME TABLE', $to );
358	}
359
360	return $this->table_exists();
361	}
362
363	/**
364	* Stamp this site's owner signature onto a table's MySQL comment.
365	*
366	* Best-effort: a host that refuses ALTER simply leaves the table unstamped,
367	* which later reads as "ownership unproven" — the safe direction.
368	*
369	* @param string $table Full table name; defaults to this table's own name.
370	* @since 2.12.6
371	* @return void
372	*/
373	public function stamp_owner_signature( $table = '' ) {
374	$wpdb = $this->wpdb;
375	$table = '' === $table ? $this->get_tablename() : $table;
376
377	$query = $wpdb->prepare( 'ALTER TABLE %1s COMMENT = %s', str_replace( '`', '', $table ), $this->get_owner_signature() ); // phpcs:ignore -- Identifier must not be quoted; the comment value is a bound, quoted string.
378
379	if ( ! $query ) {
380	return;
381	}
382
383	$wpdb->query( $query ); // phpcs:ignore -- Prepared above; one-off DDL with nothing to cache.
384	}
385
386	/**
387	* Whether a table carries this site's owner signature.
388	*
389	* Gates adoption: on shared hosting a different install's identically-named,
390	* same-schema table can be the only candidate, and renaming it in would destroy
391	* that site's data. Deny by default — anything but an exact signature match
392	* (including a read error, an empty comment, or a legacy table stamped before
393	* this plugin wrote signatures) returns false.
394	*
395	* @param string $table Full table name to inspect.
396	* @since 2.12.6
397	* @return bool
398	*/
399	public function table_belongs_to_site( $table ) {
400	$wpdb = $this->wpdb;
401	$bare = str_replace( '`', '', (string) $table );
402
403	if ( '' === $bare ) {
404	return false;
405	}
406
407	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema lookup; a cached answer would defeat the check.
408	$comment = $wpdb->get_var( $wpdb->prepare( 'SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $bare ) );
409
410	if ( ! empty( $wpdb->last_error ) \|\| ! is_string( $comment ) \|\| '' === $comment ) {
411	return false;
412	}
413
414	return hash_equals( $this->get_owner_signature(), $comment );
415	}
416
417	/**
418	* Conditionally returns current database charset or collate.
419	*
420	* @since 0.0.10
421	* @return string
422	*/
423	public function get_charset_collate() {
424	$charset_collate = '';
425
426	if ( $this->wpdb->has_cap( 'collation' ) ) {
427	if ( ! empty( $this->wpdb->charset ) ) {
428	$charset_collate = "DEFAULT CHARACTER SET {$this->wpdb->charset}";
429	}
430	if ( ! empty( $this->wpdb->collate ) ) {
431	$charset_collate .= " COLLATE {$this->wpdb->collate}";
432	}
433	}
434
435	return $charset_collate;
436	}
437
438	/**
439	* Create table.
440	*
441	* @param array<string> $columns Array of columns.
442	* @since 0.0.10
443	* @return int\|bool
444	*/
445	public function create( $columns = [] ) {
446	if ( ! $this->db_upgradable ) {
447	// Only upgrade when it is needed.
448	return false;
449	}
450
451	if ( empty( $columns ) ) {
452	return false; // It's better to return a boolean for failure.
453	}
454
455	// Prepare columns list.
456	$columns_list = implode(
457	', ',
458	$columns
459	);
460
461	$wpdb = $this->wpdb;
462
463	// Execute the query.
464	$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.
465
466	if ( ! $query ) {
467	// If we are here, then we probably have bad query to work with and prepare method has returned null-ish value.
468	return false;
469	}
470
471	$result = $wpdb->query( $query ); // phpcs:ignore -- We are already using prepare above.
472
473	if ( false === $result ) {
474	// Stop DB alteration if we have any error.
475	$this->db_upgradable = false;
476
477	/**
478	* Fires when a table could not be created.
479	*
480	* Column changes have announced their failures since 2.11.0 but table
481	* creation never did — so the one failure that leaves a site with no
482	* table at all, a host denying CREATE TABLE, was the only silent one.
483	* Same signature as the ALTER case so one listener can handle both.
484	*
485	* @param string $last_error The database error.
486	* @param string $query The query that failed.
487	* @param string $table_name The table it was for.
488	* @since 2.12.6
489	*/
490	do_action( 'srfm_db_upgrade_query_failed', $wpdb->last_error, $query, $this->get_tablename() );
491	}
492
493	if ( false !== $result ) {
494	// Stamp our own table so a future adoption can prove it belongs to this
495	// site before renaming it in. See stamp_owner_signature().
496	$this->stamp_owner_signature();
497	}
498
499	return $result;
500	}
501
502	/**
503	* Rename the column of the current table conditionally.
504	*
505	* @param array<array<string,string>> $rename_columns Array of columns to rename.
506	* @since 0.0.13
507	* @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.
508	*/
509	public function maybe_rename_columns( $rename_columns = [] ) {
510	if ( ! $rename_columns ) {
511	return false;
512	}
513
514	if ( ! $this->db_upgradable ) {
515	// Only upgrade when it is needed.
516	return false;
517	}
518
519	$existing_columns = $this->get_columns();
520
521	if ( ! $existing_columns ) {
522	// Table does not exists or is new table.
523	return false;
524	}
525
526	$wpdb = $this->wpdb;
527
528	$query_parts = [];
529	foreach ( $rename_columns as $column ) {
530	if ( empty( $existing_columns[ $column['from'] ] ) ) {
531	// Bail if column is already renamed or does not exists.
532	continue;
533	}
534
535	$query_part = $wpdb->prepare(
536	'CHANGE %1s %2s %3s', // phpcs:ignore -- It is okay to use complex placeholders as we don't want values to be quoted.
537	$column['from'],
538	$column['to'],
539	! empty( $column['type'] ) ? $column['type'] : $existing_columns[ $column['from'] ]['Type'] // This is column type i.e LONGTEXT, BIGINT etc.
540	);
541
542	if ( is_string( $query_part ) && $query_part ) {
543	$query_parts[] = trim( $query_part );
544	}
545	}
546
547	if ( empty( $query_parts ) ) {
548	// No renaming required.
549	return false;
550	}
551
552	$result = $wpdb->query( $wpdb->prepare( 'ALTER TABLE %1s ', $this->get_tablename() ) . implode( ', ', $query_parts ) . ';' ); // phpcs:ignore -- It is okay to use query directly here.
553
554	if ( false === $result ) {
555	// Stop DB alteration if we have any error.
556	$this->db_upgradable = false;
557	}
558
559	return $result;
560	}
561
562	/**
563	* Adds the new columns to the current table conditionally.
564	*
565	* @param array<string> $new_columns The array of new columns to add. Same as the create method.
566	* @since 0.0.13
567	* @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.
568	*/
569	public function maybe_add_new_columns( $new_columns = [] ) {
570	if ( ! $new_columns ) {
571	return false;
572	}
573
574	if ( ! $this->db_upgradable ) {
575	// Only upgrade when it is needed.
576	return false;
577	}
578
579	$existing_columns = $this->get_columns();
580
581	if ( ! $existing_columns ) {
582	// Table does not exists or is new table.
583	return false;
584	}
585
586	$existing_indexes = $this->get_indexes();
587
588	$alter_queries = [];
589
590	$wpdb = $this->wpdb;
591
592	// Check and add each column if it does not exist.
593	foreach ( $new_columns as $column_definition ) {
594	preg_match( '/INDEX\s+(.*?)\s+\(/', $column_definition, $index_matches );
595
596	if ( ! empty( $index_matches[1] ) ) {
597	if ( isset( $existing_indexes[ $index_matches[1] ] ) ) {
598	// Move to next element if current index already exists.
599	continue;
600	}
601	// Stack and move to next if we are indexing.
602	$alter_queries[] = $wpdb->prepare( 'ADD %1s', $column_definition ); // phpcs:ignore -- We don't need quote here.
603	continue;
604	}
605
606	preg_match( '/(\w+)\s/', $column_definition, $column_matches );
607	$column_name = $column_matches[1] ?? '';
608
609	// If the column does not exist, add it.
610	if ( ! isset( $existing_columns[ $column_name ] ) ) {
611	$alter_queries[] = $wpdb->prepare( 'ADD COLUMN %1s', $column_definition ); // phpcs:ignore -- We don't need quote here.
612	}
613	}
614
615	if ( $alter_queries ) {
616	$query = $wpdb->prepare(
617	'ALTER TABLE %1s %2s', // phpcs:ignore -- We don't want to quote the value strings for the query.
618	$this->get_tablename(),
619	implode( ', ', $alter_queries )
620	);
621
622	if ( ! $query ) {
623	// If we are here then we probably have bad query and prepare method has returned null.
624	return false;
625	}
626
627	// Execute the query.
628	$result = $wpdb->query( $query ); // phpcs:ignore -- It is okay. We are already using prepare above and we need to do DB query directly here.
629
630	if ( false === $result ) {
631	// Stop DB alteration if we have any error. A failed ALTER leaves the table
632	// version un-bumped, so it retries on every request — expose the underlying
633	// error so persistent failures are diagnosable (hook for logging/monitoring).
634	$this->db_upgradable = false;
635
636	/**
637	* Fires when a SureForms DB schema-upgrade query fails.
638	*
639	* @since 2.11.0
640	* @param string $last_error The DB error message ( $wpdb->last_error ).
641	* @param string $query The ALTER query that failed.
642	* @param string $table The table being altered.
643	*/
644	do_action( 'srfm_db_upgrade_query_failed', $this->wpdb->last_error, $query, $this->get_tablename() );
645	}
646
647	return $result;
648	}
649
650	return false;
651	}
652
653	/**
654	* Returns an array columns of current table.
655	*
656	* @since 0.0.13
657	* @return array<string,array<string,mixed>>
658	*/
659	public function get_columns() {
660	$wpdb = $this->wpdb;
661
662	$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.
663
664	if ( empty( $columns ) ) {
665	return [];
666	}
667
668	$_columns = [];
669	if ( is_array( $columns ) ) {
670	foreach ( $columns as $column ) {
671	if ( ! is_string( $column['Field'] ) ) {
672	continue;
673	}
674
675	$_columns[ $column['Field'] ] = $column;
676	}
677	}
678	return $_columns;
679	}
680
681	/**
682	* Returns an array indexes of current table.
683	*
684	* @since 0.0.13
685	* @return array<mixed>
686	*/
687	public function get_indexes() {
688	$wpdb = $this->wpdb;
689
690	$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.
691
692	if ( empty( $indexes ) ) {
693	return [];
694	}
695
696	$_indexes = [];
697	if ( is_array( $indexes ) ) {
698	foreach ( $indexes as $index ) {
699	$_indexes[ $index['Key_name'] ] = $index; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
700	}
701	}
702	return $_indexes;
703	}
704
705	/**
706	* Insert data. Basically, a wrapper method for wpdb::insert.
707	*
708	* @param array<mixed> $data Data to insert (in column => value pairs).
709	* Both `$data` columns and `$data` values should be "raw" (neither should be SQL escaped).
710	* Sending a null value will cause the column to be set to NULL - the corresponding
711	* format is ignored in this case.
712	* @param array<string>\|string\|null $format Optional. An array of formats to be mapped to each of the value in `$data`.
713	* If string, that format will be used for all of the values in `$data`.
714	* A format is one of '%d', '%f', '%s' (integer, float, string).
715	* If omitted, all values in `$data` will be treated as strings unless otherwise
716	* specified in wpdb::$field_types. Default null.
717	* @since 0.0.10
718	* @return int\|false The id of the inserted entry, or false on error.
719	*/
720	public function use_insert( $data, $format = null ) {
721	$prepared_data = $this->prepare_data( $data );
722
723	if ( is_null( $format ) ) {
724	/**
725	* Use formats from schema if not provided explicitly.
726	*
727	* @var array<string>\|string\|null $format Format specifier for the data.
728	*/
729	$format = $prepared_data['format'];
730	}
731
732	$result = $this->wpdb->insert( $this->get_tablename(), $prepared_data['data'], $format );
733
734	// Reset cache so subsequent queries in the same request include the new row.
735	$this->cache_reset();
736
737	return $result ? $this->wpdb->insert_id : false;
738	}
739
740	/**
741	* Update a row data of current table. Basically, a wrapper method for wpdb::update.
742	*
743	* @param array<string,mixed> $data Data to update (in column => value pairs).
744	* Both $data columns and $data values should be "raw" (neither should be SQL escaped).
745	* Sending a null value will cause the column to be set to NULL - the corresponding
746	* format is ignored in this case.
747	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
748	* Multiple clauses will be joined with ANDs.
749	* Both $where columns and $where values should be "raw".
750	* Sending a null value will create an IS NULL comparison - the corresponding
751	* format will be ignored in this case.
752	* @since 0.0.13
753	* @return int\|false The number of rows updated, or false on error.
754	*/
755	public function use_update( $data, $where ) { // phpcs:ignore PSR2.Methods.MethodDeclaration.Underscore -- It is okay. This is our wrapper method.
756	$prepared_data = $this->prepare_data( $data, true );
757
758	/**
759	* Data format specifier.
760	*
761	* @var array<string>\|string\|null $format Format specifier for the data.
762	*/
763	$format = $prepared_data['format'];
764
765	// Reset the cache on update.
766	$this->cache_reset();
767
768	return $this->wpdb->update(
769	$this->get_tablename(),
770	$prepared_data['data'],
771	$where,
772	$format
773	);
774	}
775
776	/**
777	* Delete a row data of current table. Basically, a wrapper method for wpdb::delete.
778	*
779	* @param array<string,mixed> $where A named array of WHERE clauses (in column => value pairs).
780	* Multiple clauses will be joined with ANDs.
781	* Both $where columns and $where values should be "raw".
782	* Sending a null value will create an IS NULL comparison - the corresponding
783	* format will be ignored in this case.
784	* @param array<string>\|string $where_format Optional. An array of formats to be mapped to each of the values in $where.
785	* If string, that format will be used for all of the items in $where.
786	* A format is one of '%d', '%f', '%s' (integer, float, string).
787	* If omitted, all values in $data will be treated as strings unless otherwise
788	* specified in wpdb::$field_types. Default null.
789	* @since 0.0.13
790	* @return int\|false The number of rows deleted, or false on error.
791	*/
792	public function use_delete( $where, $where_format = null ) {
793	$result = $this->wpdb->delete( $this->get_tablename(), $where, $where_format );
794
795	// Reset cache so subsequent queries in the same request exclude the deleted row.
796	$this->cache_reset();
797
798	return $result;
799	}
800
801	/**
802	* Retrieve results from the database based on the given WHERE clauses and selected columns.
803	*
804	* This method builds a SQL SELECT query with optional WHERE clauses and retrieves the results
805	* from the database. The results are cached to improve performance on subsequent requests.
806	*
807	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
808	* Each key represents a column name, and each value is the value
809	* to match. If the value is an array, it will be used in an IN clause.
810	* Example: ['column1' => 'value1', 'column2' => ['value2', 'value3']].
811	* Default is an empty array.
812	* @param string $columns Optional. A string specifying which columns to select. Defaults to '*' (all columns).
813	* @param array<string> $extra_queries Optional. Array of extra queries to append at the end of main query.
814	* @param bool $decode Optional. Whether to decode the results by datatype. Default is true.
815	* @since 0.0.10
816	* @return array<mixed> An associative array of results where each element represents a row, or an empty array if no results are found.
817	*/
818	public function get_results( $where_clauses = [], $columns = '*', $extra_queries = [], $decode = true ) {
819	$wpdb = $this->wpdb;
820
821	$table_name = $this->get_tablename();
822
823	// Start building the query.
824	$query = "SELECT {$columns} FROM {$table_name}";
825
826	// If there are WHERE clauses, prepare and append them to the query.
827	$query .= $this->prepare_where_clauses( $where_clauses );
828
829	if ( ! empty( $extra_queries ) ) {
830	$query .= ' ' . implode( ' ', array_map( 'trim', $extra_queries ) );
831	}
832
833	// Add a semicolon at the end of the query.
834	$query = rtrim( trim( $query ), ';' ) . ';';
835
836	$cached_results = $this->cache_get( $query );
837	if ( null !== $cached_results ) {
838	// Return the cached data if exists. Tested against null rather than
839	// truthiness: an empty result set is a real answer, and re-running the
840	// query for it means every no-match lookup runs once per caller.
841	return Helper::get_array_value( $cached_results );
842	}
843
844	// phpcs:ignore
845	$results = $wpdb->get_results( $query, ARRAY_A );
846
847	if ( $decode && ! empty( $results ) && is_array( $results ) ) {
848	foreach ( $results as &$result ) {
849	$result = $this->decode_by_datatype( $result );
850	}
851	}
852
853	// Execute the query and return results.
854	return Helper::get_array_value( $this->cache_set( $query, $results ) );
855	}
856
857	/**
858	* Retrieves a list of records based on the provided arguments.
859	*
860	* This method fetches results from the database, allowing for various
861	* customization options such as filtering, pagination, and sorting.
862	*
863	* @param array<string,mixed> $args {
864	* Optional. An array of arguments to customize the query.
865	*
866	* @type array $where An associative array of conditions to filter the results.
867	* @type int $limit The maximum number of results to return. Default is 10.
868	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
869	* @type string $orderby The column by which to order the results. Default is 'created_at'.
870	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
871	* }
872	* @param bool $set_limit Whether to set the limit on the query. Default is true.
873	*
874	* @since 1.13.0
875	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
876	*/
877	public function get_records_by_args( $args = [], $set_limit = true ) {
878	$_args = wp_parse_args(
879	$args,
880	[
881	'where' => [],
882	'columns' => '*',
883	'limit' => 10,
884	'offset' => 0,
885	'orderby' => 'created_at',
886	'order' => 'DESC',
887	]
888	);
889	$allowed_orderby = $this->get_allowed_orderby_columns();
890	$orderby = in_array( $_args['orderby'], $allowed_orderby, true ) ? $_args['orderby'] : 'created_at';
891	$order = 'ASC' === strtoupper( Helper::get_string_value( $_args['order'] ) ) ? 'ASC' : 'DESC';
892	$extra_queries = [
893	sprintf( 'ORDER BY `%1$s` %2$s', $orderby, $order ),
894	];
895
896	if ( $set_limit ) {
897	$extra_queries[] = sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) );
898	}
899	return $this->get_results(
900	$_args['where'],
901	$_args['columns'],
902	$extra_queries
903	);
904	}
905
906	/**
907	* Get the total number of rows in the table.
908	*
909	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
910	* @since 0.0.13
911	* @return int The total number of rows in the table.
912	*/
913	public function get_total_count( $where_clauses = [] ) {
914	$wpdb = $this->wpdb;
915
916	$table_name = $this->get_tablename();
917
918	// Start building the query.
919	$query = "SELECT COUNT(*) FROM {$table_name}";
920
921	// If there are WHERE clauses, prepare and append them to the query.
922	$query .= $this->prepare_where_clauses( $where_clauses );
923
924	// Add a semicolon at the end of the query.
925	$query = rtrim( trim( $query ), ';' ) . ';';
926
927	$cached_results = $this->cache_get( $query );
928	if ( null !== $cached_results ) {
929	// Return the cached data if exists. Tested against null rather than
930	// truthiness: a count of zero is a real answer, and the editor exclusion
931	// makes zero the common case rather than the exception.
932	return Helper::get_integer_value( $cached_results );
933	}
934
935	// phpcs:ignore
936	$results = Helper::get_integer_value( $wpdb->get_var( $query ) );
937
938	// Execute the query and return the integer count.
939	return Helper::get_integer_value( $this->cache_set( $query, $results ) );
940	}
941
942	/**
943	* The signature this plugin stamps on tables it owns on this site.
944	*
945	* A random per-site token, generated once and stored in options. Embedded in
946	* the table's MySQL comment at creation time; the comment survives RENAME, so a
947	* table that moved under a different prefix still carries it, while an unrelated
948	* install sharing the same database carries a different one.
949	*
950	* @since 2.12.6
951	* @return string
952	*/
953	protected function get_owner_signature() {
954	$token = get_option( 'srfm_db_owner_token' );
955
956	if ( ! is_string( $token ) \|\| '' === $token ) {
957	$token = wp_generate_password( 20, false );
958	update_option( 'srfm_db_owner_token', $token, false );
959	}
960
961	return 'srfm-owner:' . $token;
962	}
963
964	/**
965	* Whether a table carries every column this table's schema declares.
966	*
967	* Guards adoption: a same-named table from an unrelated source should never be
968	* renamed into place just because its name matches.
969	*
970	* @param string $table Full table name to inspect.
971	* @since 2.12.6
972	* @return bool
973	*/
974	protected function has_expected_columns( $table ) {
975	$wpdb = $this->wpdb;
976
977	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema lookup; a cached answer would defeat the check.
978	$columns = $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %1s', str_replace( '`', '', $table ) ) ); // phpcs:ignore -- Same complex-placeholder pattern as create(): an identifier must not be quoted, and the name comes from SHOW TABLES on this connection.
979
980	if ( ! empty( $wpdb->last_error ) \|\| ! is_array( $columns ) ) {
981	return false;
982	}
983
984	foreach ( array_keys( $this->get_schema() ) as $column ) {
985	if ( ! in_array( $column, $columns, true ) ) {
986	return false;
987	}
988	}
989
990	return true;
991	}
992
993	/**
994	* Get the allowed column names for ORDER BY clauses.
995	* Child classes may override this method to restrict orderable columns further.
996	*
997	* @since 2.6.0
998	* @return array<string>
999	*/
1000	protected function get_allowed_orderby_columns() {
1001	return array_merge( array_keys( $this->get_schema() ), [ 'updated_at' ] );
1002	}
1003
1004	/**
1005	* Retrieve a cached value by its key.
1006	*
1007	* @param string $key The cache key.
1008	* @since 0.0.10
1009	* @return mixed\|null The cached value if it exists, or null if the key does not exist in the cache.
1010	*/
1011	protected function cache_get( $key ) {
1012	$key = md5( $key );
1013	if ( ! isset( $this->caches[ $key ] ) ) {
1014	return null;
1015	}
1016	return $this->caches[ $key ];
1017	}
1018
1019	/**
1020	* Store a value in the cache with the specified key.
1021	*
1022	* @param string $key The cache key.
1023	* @param mixed $value The value to store in the cache.
1024	* @since 0.0.10
1025	* @return mixed The stored value.
1026	*/
1027	protected function cache_set( $key, $value ) {
1028	$key = md5( $key );
1029	$this->caches[ $key ] = $value;
1030	return $value;
1031	}
1032
1033	/**
1034	* Reset the cache by clearing all stored values.
1035	*
1036	* @since 0.0.10
1037	* @return void
1038	*/
1039	protected function cache_reset() {
1040	$this->caches = [];
1041	}
1042
1043	/**
1044	* Prepares WHERE clauses for a SQL query based on the provided conditions.
1045	*
1046	* This method constructs a WHERE statement by iterating through the
1047	* specified conditions, appending them with the appropriate SQL syntax.
1048	* It supports both single key-value pairs and arrays of conditions.
1049	*
1050	* @param array<mixed> $where_clauses {
1051	* An associative array of conditions to include in the WHERE clause.
1052	*
1053	* @type string\|array $key The column name or an array of conditions.
1054	* @type array $value {
1055	* An associative array of comparison data.
1056	*
1057	* @type string $key The column name for comparison.
1058	* @type string $compare The comparison operator (e.g., '=', 'LIKE').
1059	* @type mixed $value The value to compare against.
1060	* @type string $RELATION Optional. The logical relation ('AND' or 'OR').
1061	* }
1062	* }
1063	*
1064	* @since 2.12.7 -- Added support for "NOT IN" compare.
1065	* @since 1.1.1 -- Added support for "IN" compare.
1066	* @since 0.0.13
1067	* @return string The prepared SQL WHERE clause with placeholders, or an empty string if no clauses were provided.
1068	*/
1069	protected function prepare_where_clauses( $where_clauses = [] ) {
1070	if ( empty( $where_clauses ) ) {
1071	return '';
1072	}
1073
1074	$wpdb = $this->wpdb;
1075
1076	// If there are WHERE clauses, prepare and append them to the query.
1077	if ( is_array( $where_clauses ) ) {
1078	$groups = [];
1079	$values = [];
1080	$schema = $this->get_schema();
1081
1082	foreach ( $where_clauses as $key => $value ) {
1083
1084	$relation = ! empty( $value['RELATION'] ) ? trim( $value['RELATION'] ) : 'AND';
1085	$relation = in_array( strtoupper( $relation ), [ 'AND', 'OR' ], true ) ? strtoupper( $relation ) : 'AND';
1086
1087	if ( is_int( $key ) ) {
1088	$clause_parts = [];
1089	foreach ( $value as $_key => $_value ) {
1090	if ( is_int( $_key ) ) {
1091	// Normalised before the allowlist test. Payments'
1092	// builder upper-cases and trims, this one compared
1093	// strictly -- so a caller writing 'not in' was honoured
1094	// by one and silently dropped by the other. A dropped
1095	// condition used to be harmless; now that NOT IN is the
1096	// exclusion primitive, dropping it disables the
1097	// exclusion without a word.
1098	$compare = strtoupper( trim( Helper::get_string_value( $_value['compare'] ) ) );
1099
1100	// Check if the operator is allowed.
1101	if ( ! in_array( $compare, $this->allowed_where_operators, true ) ) {
1102	continue;
1103	}
1104
1105	// Skip if key is not in schema.
1106	if ( ! isset( $schema[ $_value['key'] ] ) ) {
1107	continue;
1108	}
1109
1110	switch ( $compare ) {
1111	case 'LIKE':
1112	// Single quotes to match WP core. Under a MySQL session with
1113	// ANSI_QUOTES set (not in WP's incompatible_modes list, which
1114	// only names the compound ANSI mode) a double-quoted pattern
1115	// parses as an identifier and the query hard-fails, taking out
1116	// both the listing and its COUNT(*).
1117	$clause_parts[] = $_value['key'] . ' ' . $compare . " '%%" . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) ) . "%%'";
1118	$values[] = $_value['value'];
1119	break;
1120
1121	case 'IN':
1122	case 'NOT IN':
1123	// A scalar is a caller bug, not an empty set, and it must
1124	// surface. 'NOT IN' with value 5 -- a plausible typo for
1125	// [ 5 ] -- would otherwise drop the condition and exclude
1126	// nobody, with no error and a green test suite, while the
1127	// same typo on 'IN' fails closed. On a primitive whose only
1128	// job is scoping data, that asymmetry is a hazard.
1129	if ( ! is_array( $_value['value'] ) ) {
1130	_doing_it_wrong(
1131	__METHOD__,
1132	esc_html( "{$compare} requires an array value, received " . gettype( $_value['value'] ) . '.' ),
1133	'2.12.7'
1134	);
1135	break;
1136	}
1137
1138	// An empty list cannot be interpolated: "col IN ()" is a syntax
1139	// error that fails the whole query, listing and COUNT alike.
1140	// An empty IN matches nothing, so '1 = 0' says that in any
1141	// relation. An empty NOT IN excludes nothing, but a literal
1142	// would be '1 = 1', and that makes an enclosing OR group
1143	// unconditionally true. Dropping the condition means the same
1144	// thing under AND and stays fail-closed under OR.
1145	if ( [] === $_value['value'] ) {
1146	if ( 'IN' === $compare ) {
1147	$clause_parts[] = '1 = 0';
1148	}
1149	break;
1150	}
1151
1152	// 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).
1153	$datatype = $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
1154	$clause_parts[] = $_value['key'] . ' ' . $compare . ' (' . implode( ', ', array_fill( 0, count( $_value['value'] ), $datatype ) ) . ')';
1155	$values = array_merge( $values, $_value['value'] );
1156	break;
1157
1158	default:
1159	$clause_parts[] = $_value['key'] . ' ' . $compare . ' ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
1160	$values[] = $_value['value'];
1161	break;
1162	}
1163	}
1164	}
1165
1166	if ( ! empty( $clause_parts ) ) {
1167	$groups[] = '(' . implode( ' ' . $relation . ' ', $clause_parts ) . ')';
1168	}
1169	continue;
1170	}
1171
1172	if ( ! isset( $schema[ $key ] ) ) {
1173	// Skip strictly if current key is not in our schema.
1174	continue;
1175	}
1176
1177	$groups[] = '(' . $key . ' = ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $key ]['type'] ) ) . ')';
1178	$values[] = $value;
1179	}
1180
1181	if ( empty( $groups ) ) {
1182	return '';
1183	}
1184
1185	$where = ' WHERE ' . implode( ' AND ', $groups );
1186
1187	if ( [] === $values ) {
1188	// Every branch that builds a placeholder also pushes a value, so an
1189	// empty list here means the only conditions were constant ones. There
1190	// is nothing for prepare() to fill, and calling it with no placeholder
1191	// trips _doing_it_wrong.
1192	return $where;
1193	}
1194
1195	// Prepare the query with placeholders.
1196	// @phpstan-ignore-next-line -- We are already assigning non-literal string above using "get_format_by_datatype" methods.
1197	return $wpdb->prepare( $where, ...$values ); // phpcs:ignore -- We are returning prepared sql query here. We are already using necessary placeholders in $where variable.
1198	}
1199
1200	return '';
1201	}
1202
1203	/**
1204	* Prepare and format data based on the schema.
1205	*
1206	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to process.
1207	* Missing values will be replaced with default values specified in the schema.
1208	* @param bool $skip_defaults Whether or not to skip the defaults values. Pass true if updating the data.
1209	* @since 0.0.10
1210	* @return array<array<mixed>> An associative array containing:
1211	* - 'data': Prepared data with values encoded according to their data types.
1212	* - 'format': An array of format specifiers corresponding to the data values.
1213	*/
1214	protected function prepare_data( $data, $skip_defaults = false ) {
1215	$_data = [];
1216	$format = [];
1217	foreach ( $this->get_schema() as $key => $value ) {
1218	// Process defaults.
1219	if ( ! isset( $data[ $key ] ) ) {
1220	if ( $skip_defaults \|\| ! isset( $value['default'] ) ) {
1221	continue;
1222	}
1223	$data[ $key ] = $value['default'];
1224	}
1225
1226	$format[] = $this->get_format_by_datatype( $value['type'] ); // Format for the WP database methods.
1227	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value['type'] );
1228	}
1229	return [
1230	'data' => $_data,
1231	'format' => $format,
1232	];
1233	}
1234
1235	/**
1236	* Get the SQL format specifier based on the provided data type.
1237	*
1238	* @param string $type The data type for which to get the SQL format specifier.
1239	* Possible values: 'string', 'array', 'number', 'boolean'.
1240	* @since 0.0.10
1241	* @return string The SQL format specifier. One of '%s' for string or array (converted to JSON), '%d' for number or boolean.
1242	*/
1243	protected function get_format_by_datatype( $type ) {
1244	$format = '%s';
1245	switch ( $type ) {
1246	case 'string':
1247	case 'array': // Because array will be converted to json string.
1248	$format = '%s';
1249	break;
1250
1251	case 'number':
1252	case 'boolean':
1253	$format = '%d';
1254	break;
1255	}
1256
1257	return $format;
1258	}
1259
1260	/**
1261	* Decode data based on the schema data types.
1262	*
1263	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to decode.
1264	* The data will be decoded if the column type in the schema is 'array' (JSON string).
1265	* @since 0.0.10
1266	* @return array<mixed> An associative array of decoded data based on the schema.
1267	*/
1268	protected function decode_by_datatype( $data ) {
1269	$_data = [];
1270	foreach ( $this->get_schema() as $key => $schema ) {
1271	if ( ! array_key_exists( $key, $data ) ) {
1272	continue;
1273	}
1274
1275	// Lets decode from JSON to Array for the results.
1276	$_data[ $key ] = 'array' === $schema['type'] ? Helper::get_array_value( json_decode( Helper::get_string_value( $data[ $key ] ), true ) ) : $data[ $key ];
1277	}
1278	return $_data;
1279	}
1280
1281	/**
1282	* Encode a value based on the specified data type.
1283	*
1284	* @param mixed $value The value to encode. The encoding will depend on the data type specified.
1285	* @param string $type The data type for encoding. Possible values: 'string', 'number', 'boolean', 'array'.
1286	* @since 0.0.10
1287	* @return mixed The encoded value. The type of the return value depends on the specified type:
1288	* - 'string': Encoded as a string.
1289	* - 'number': Encoded as an integer.
1290	* - 'boolean': Encoded as a boolean.
1291	* - 'array': Encoded as a JSON string.
1292	* @since 1.8.0 - 'datetime': Returns the value as it is, assuming it is already in SQL DATETIME format.
1293	*/
1294	protected function encode_by_datatype( $value, $type ) {
1295	switch ( $type ) {
1296	case 'string':
1297	return Helper::get_string_value( $value );
1298
1299	case 'number':
1300	return Helper::get_integer_value( $value );
1301
1302	case 'boolean':
1303	return boolval( $value );
1304
1305	case 'array':
1306	// Lets json_encode array values instead of serializing it.
1307	return Helper::encode_json( Helper::get_array_value( $value ) );
1308
1309	case 'datetime':
1310	// For datetime, we will return the value as it is because we are using sql DATETIME format.
1311	return $value;
1312	}
1313	}
1314	}
1315