sureforms / inc / database / base.php

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

1,310 lines 43.9 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	return $this->wpdb->delete( $this->get_tablename(), $where, $where_format );
794	}
795
796	/**
797	* Retrieve results from the database based on the given WHERE clauses and selected columns.
798	*
799	* This method builds a SQL SELECT query with optional WHERE clauses and retrieves the results
800	* from the database. The results are cached to improve performance on subsequent requests.
801	*
802	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
803	* Each key represents a column name, and each value is the value
804	* to match. If the value is an array, it will be used in an IN clause.
805	* Example: ['column1' => 'value1', 'column2' => ['value2', 'value3']].
806	* Default is an empty array.
807	* @param string $columns Optional. A string specifying which columns to select. Defaults to '*' (all columns).
808	* @param array<string> $extra_queries Optional. Array of extra queries to append at the end of main query.
809	* @param bool $decode Optional. Whether to decode the results by datatype. Default is true.
810	* @since 0.0.10
811	* @return array<mixed> An associative array of results where each element represents a row, or an empty array if no results are found.
812	*/
813	public function get_results( $where_clauses = [], $columns = '*', $extra_queries = [], $decode = true ) {
814	$wpdb = $this->wpdb;
815
816	$table_name = $this->get_tablename();
817
818	// Start building the query.
819	$query = "SELECT {$columns} FROM {$table_name}";
820
821	// If there are WHERE clauses, prepare and append them to the query.
822	$query .= $this->prepare_where_clauses( $where_clauses );
823
824	if ( ! empty( $extra_queries ) ) {
825	$query .= ' ' . implode( ' ', array_map( 'trim', $extra_queries ) );
826	}
827
828	// Add a semicolon at the end of the query.
829	$query = rtrim( trim( $query ), ';' ) . ';';
830
831	$cached_results = $this->cache_get( $query );
832	if ( null !== $cached_results ) {
833	// Return the cached data if exists. Tested against null rather than
834	// truthiness: an empty result set is a real answer, and re-running the
835	// query for it means every no-match lookup runs once per caller.
836	return Helper::get_array_value( $cached_results );
837	}
838
839	// phpcs:ignore
840	$results = $wpdb->get_results( $query, ARRAY_A );
841
842	if ( $decode && ! empty( $results ) && is_array( $results ) ) {
843	foreach ( $results as &$result ) {
844	$result = $this->decode_by_datatype( $result );
845	}
846	}
847
848	// Execute the query and return results.
849	return Helper::get_array_value( $this->cache_set( $query, $results ) );
850	}
851
852	/**
853	* Retrieves a list of records based on the provided arguments.
854	*
855	* This method fetches results from the database, allowing for various
856	* customization options such as filtering, pagination, and sorting.
857	*
858	* @param array<string,mixed> $args {
859	* Optional. An array of arguments to customize the query.
860	*
861	* @type array $where An associative array of conditions to filter the results.
862	* @type int $limit The maximum number of results to return. Default is 10.
863	* @type int $offset The number of records to skip before starting to collect results. Default is 0.
864	* @type string $orderby The column by which to order the results. Default is 'created_at'.
865	* @type string $order The direction of the order (ASC or DESC). Default is 'DESC'.
866	* }
867	* @param bool $set_limit Whether to set the limit on the query. Default is true.
868	*
869	* @since 1.13.0
870	* @return array<mixed> The results of the query, typically an array of objects or associative arrays.
871	*/
872	public function get_records_by_args( $args = [], $set_limit = true ) {
873	$_args = wp_parse_args(
874	$args,
875	[
876	'where' => [],
877	'columns' => '*',
878	'limit' => 10,
879	'offset' => 0,
880	'orderby' => 'created_at',
881	'order' => 'DESC',
882	]
883	);
884	$allowed_orderby = $this->get_allowed_orderby_columns();
885	$orderby = in_array( $_args['orderby'], $allowed_orderby, true ) ? $_args['orderby'] : 'created_at';
886	$order = 'ASC' === strtoupper( Helper::get_string_value( $_args['order'] ) ) ? 'ASC' : 'DESC';
887	$extra_queries = [
888	sprintf( 'ORDER BY `%1$s` %2$s', $orderby, $order ),
889	];
890
891	if ( $set_limit ) {
892	$extra_queries[] = sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) );
893	}
894	return $this->get_results(
895	$_args['where'],
896	$_args['columns'],
897	$extra_queries
898	);
899	}
900
901	/**
902	* Get the total number of rows in the table.
903	*
904	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
905	* @since 0.0.13
906	* @return int The total number of rows in the table.
907	*/
908	public function get_total_count( $where_clauses = [] ) {
909	$wpdb = $this->wpdb;
910
911	$table_name = $this->get_tablename();
912
913	// Start building the query.
914	$query = "SELECT COUNT(*) FROM {$table_name}";
915
916	// If there are WHERE clauses, prepare and append them to the query.
917	$query .= $this->prepare_where_clauses( $where_clauses );
918
919	// Add a semicolon at the end of the query.
920	$query = rtrim( trim( $query ), ';' ) . ';';
921
922	$cached_results = $this->cache_get( $query );
923	if ( null !== $cached_results ) {
924	// Return the cached data if exists. Tested against null rather than
925	// truthiness: a count of zero is a real answer, and the editor exclusion
926	// makes zero the common case rather than the exception.
927	return Helper::get_integer_value( $cached_results );
928	}
929
930	// phpcs:ignore
931	$results = Helper::get_integer_value( $wpdb->get_var( $query ) );
932
933	// Execute the query and return the integer count.
934	return Helper::get_integer_value( $this->cache_set( $query, $results ) );
935	}
936
937	/**
938	* The signature this plugin stamps on tables it owns on this site.
939	*
940	* A random per-site token, generated once and stored in options. Embedded in
941	* the table's MySQL comment at creation time; the comment survives RENAME, so a
942	* table that moved under a different prefix still carries it, while an unrelated
943	* install sharing the same database carries a different one.
944	*
945	* @since 2.12.6
946	* @return string
947	*/
948	protected function get_owner_signature() {
949	$token = get_option( 'srfm_db_owner_token' );
950
951	if ( ! is_string( $token ) \|\| '' === $token ) {
952	$token = wp_generate_password( 20, false );
953	update_option( 'srfm_db_owner_token', $token, false );
954	}
955
956	return 'srfm-owner:' . $token;
957	}
958
959	/**
960	* Whether a table carries every column this table's schema declares.
961	*
962	* Guards adoption: a same-named table from an unrelated source should never be
963	* renamed into place just because its name matches.
964	*
965	* @param string $table Full table name to inspect.
966	* @since 2.12.6
967	* @return bool
968	*/
969	protected function has_expected_columns( $table ) {
970	$wpdb = $this->wpdb;
971
972	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema lookup; a cached answer would defeat the check.
973	$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.
974
975	if ( ! empty( $wpdb->last_error ) \|\| ! is_array( $columns ) ) {
976	return false;
977	}
978
979	foreach ( array_keys( $this->get_schema() ) as $column ) {
980	if ( ! in_array( $column, $columns, true ) ) {
981	return false;
982	}
983	}
984
985	return true;
986	}
987
988	/**
989	* Get the allowed column names for ORDER BY clauses.
990	* Child classes may override this method to restrict orderable columns further.
991	*
992	* @since 2.6.0
993	* @return array<string>
994	*/
995	protected function get_allowed_orderby_columns() {
996	return array_merge( array_keys( $this->get_schema() ), [ 'updated_at' ] );
997	}
998
999	/**
1000	* Retrieve a cached value by its key.
1001	*
1002	* @param string $key The cache key.
1003	* @since 0.0.10
1004	* @return mixed\|null The cached value if it exists, or null if the key does not exist in the cache.
1005	*/
1006	protected function cache_get( $key ) {
1007	$key = md5( $key );
1008	if ( ! isset( $this->caches[ $key ] ) ) {
1009	return null;
1010	}
1011	return $this->caches[ $key ];
1012	}
1013
1014	/**
1015	* Store a value in the cache with the specified key.
1016	*
1017	* @param string $key The cache key.
1018	* @param mixed $value The value to store in the cache.
1019	* @since 0.0.10
1020	* @return mixed The stored value.
1021	*/
1022	protected function cache_set( $key, $value ) {
1023	$key = md5( $key );
1024	$this->caches[ $key ] = $value;
1025	return $value;
1026	}
1027
1028	/**
1029	* Reset the cache by clearing all stored values.
1030	*
1031	* @since 0.0.10
1032	* @return void
1033	*/
1034	protected function cache_reset() {
1035	$this->caches = [];
1036	}
1037
1038	/**
1039	* Prepares WHERE clauses for a SQL query based on the provided conditions.
1040	*
1041	* This method constructs a WHERE statement by iterating through the
1042	* specified conditions, appending them with the appropriate SQL syntax.
1043	* It supports both single key-value pairs and arrays of conditions.
1044	*
1045	* @param array<mixed> $where_clauses {
1046	* An associative array of conditions to include in the WHERE clause.
1047	*
1048	* @type string\|array $key The column name or an array of conditions.
1049	* @type array $value {
1050	* An associative array of comparison data.
1051	*
1052	* @type string $key The column name for comparison.
1053	* @type string $compare The comparison operator (e.g., '=', 'LIKE').
1054	* @type mixed $value The value to compare against.
1055	* @type string $RELATION Optional. The logical relation ('AND' or 'OR').
1056	* }
1057	* }
1058	*
1059	* @since 2.12.7 -- Added support for "NOT IN" compare.
1060	* @since 1.1.1 -- Added support for "IN" compare.
1061	* @since 0.0.13
1062	* @return string The prepared SQL WHERE clause with placeholders, or an empty string if no clauses were provided.
1063	*/
1064	protected function prepare_where_clauses( $where_clauses = [] ) {
1065	if ( empty( $where_clauses ) ) {
1066	return '';
1067	}
1068
1069	$wpdb = $this->wpdb;
1070
1071	// If there are WHERE clauses, prepare and append them to the query.
1072	if ( is_array( $where_clauses ) ) {
1073	$groups = [];
1074	$values = [];
1075	$schema = $this->get_schema();
1076
1077	foreach ( $where_clauses as $key => $value ) {
1078
1079	$relation = ! empty( $value['RELATION'] ) ? trim( $value['RELATION'] ) : 'AND';
1080	$relation = in_array( strtoupper( $relation ), [ 'AND', 'OR' ], true ) ? strtoupper( $relation ) : 'AND';
1081
1082	if ( is_int( $key ) ) {
1083	$clause_parts = [];
1084	foreach ( $value as $_key => $_value ) {
1085	if ( is_int( $_key ) ) {
1086	// Normalised before the allowlist test. Payments'
1087	// builder upper-cases and trims, this one compared
1088	// strictly -- so a caller writing 'not in' was honoured
1089	// by one and silently dropped by the other. A dropped
1090	// condition used to be harmless; now that NOT IN is the
1091	// exclusion primitive, dropping it disables the
1092	// exclusion without a word.
1093	$compare = strtoupper( trim( Helper::get_string_value( $_value['compare'] ) ) );
1094
1095	// Check if the operator is allowed.
1096	if ( ! in_array( $compare, $this->allowed_where_operators, true ) ) {
1097	continue;
1098	}
1099
1100	// Skip if key is not in schema.
1101	if ( ! isset( $schema[ $_value['key'] ] ) ) {
1102	continue;
1103	}
1104
1105	switch ( $compare ) {
1106	case 'LIKE':
1107	// Single quotes to match WP core. Under a MySQL session with
1108	// ANSI_QUOTES set (not in WP's incompatible_modes list, which
1109	// only names the compound ANSI mode) a double-quoted pattern
1110	// parses as an identifier and the query hard-fails, taking out
1111	// both the listing and its COUNT(*).
1112	$clause_parts[] = $_value['key'] . ' ' . $compare . " '%%" . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) ) . "%%'";
1113	$values[] = $_value['value'];
1114	break;
1115
1116	case 'IN':
1117	case 'NOT IN':
1118	// A scalar is a caller bug, not an empty set, and it must
1119	// surface. 'NOT IN' with value 5 -- a plausible typo for
1120	// [ 5 ] -- would otherwise drop the condition and exclude
1121	// nobody, with no error and a green test suite, while the
1122	// same typo on 'IN' fails closed. On a primitive whose only
1123	// job is scoping data, that asymmetry is a hazard.
1124	if ( ! is_array( $_value['value'] ) ) {
1125	_doing_it_wrong(
1126	__METHOD__,
1127	esc_html( "{$compare} requires an array value, received " . gettype( $_value['value'] ) . '.' ),
1128	'2.12.7'
1129	);
1130	break;
1131	}
1132
1133	// An empty list cannot be interpolated: "col IN ()" is a syntax
1134	// error that fails the whole query, listing and COUNT alike.
1135	// An empty IN matches nothing, so '1 = 0' says that in any
1136	// relation. An empty NOT IN excludes nothing, but a literal
1137	// would be '1 = 1', and that makes an enclosing OR group
1138	// unconditionally true. Dropping the condition means the same
1139	// thing under AND and stays fail-closed under OR.
1140	if ( [] === $_value['value'] ) {
1141	if ( 'IN' === $compare ) {
1142	$clause_parts[] = '1 = 0';
1143	}
1144	break;
1145	}
1146
1147	// 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).
1148	$datatype = $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
1149	$clause_parts[] = $_value['key'] . ' ' . $compare . ' (' . implode( ', ', array_fill( 0, count( $_value['value'] ), $datatype ) ) . ')';
1150	$values = array_merge( $values, $_value['value'] );
1151	break;
1152
1153	default:
1154	$clause_parts[] = $_value['key'] . ' ' . $compare . ' ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $_value['key'] ]['type'] ) );
1155	$values[] = $_value['value'];
1156	break;
1157	}
1158	}
1159	}
1160
1161	if ( ! empty( $clause_parts ) ) {
1162	$groups[] = '(' . implode( ' ' . $relation . ' ', $clause_parts ) . ')';
1163	}
1164	continue;
1165	}
1166
1167	if ( ! isset( $schema[ $key ] ) ) {
1168	// Skip strictly if current key is not in our schema.
1169	continue;
1170	}
1171
1172	$groups[] = '(' . $key . ' = ' . $this->get_format_by_datatype( Helper::get_string_value( $schema[ $key ]['type'] ) ) . ')';
1173	$values[] = $value;
1174	}
1175
1176	if ( empty( $groups ) ) {
1177	return '';
1178	}
1179
1180	$where = ' WHERE ' . implode( ' AND ', $groups );
1181
1182	if ( [] === $values ) {
1183	// Every branch that builds a placeholder also pushes a value, so an
1184	// empty list here means the only conditions were constant ones. There
1185	// is nothing for prepare() to fill, and calling it with no placeholder
1186	// trips _doing_it_wrong.
1187	return $where;
1188	}
1189
1190	// Prepare the query with placeholders.
1191	// @phpstan-ignore-next-line -- We are already assigning non-literal string above using "get_format_by_datatype" methods.
1192	return $wpdb->prepare( $where, ...$values ); // phpcs:ignore -- We are returning prepared sql query here. We are already using necessary placeholders in $where variable.
1193	}
1194
1195	return '';
1196	}
1197
1198	/**
1199	* Prepare and format data based on the schema.
1200	*
1201	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to process.
1202	* Missing values will be replaced with default values specified in the schema.
1203	* @param bool $skip_defaults Whether or not to skip the defaults values. Pass true if updating the data.
1204	* @since 0.0.10
1205	* @return array<array<mixed>> An associative array containing:
1206	* - 'data': Prepared data with values encoded according to their data types.
1207	* - 'format': An array of format specifiers corresponding to the data values.
1208	*/
1209	protected function prepare_data( $data, $skip_defaults = false ) {
1210	$_data = [];
1211	$format = [];
1212	foreach ( $this->get_schema() as $key => $value ) {
1213	// Process defaults.
1214	if ( ! isset( $data[ $key ] ) ) {
1215	if ( $skip_defaults \|\| ! isset( $value['default'] ) ) {
1216	continue;
1217	}
1218	$data[ $key ] = $value['default'];
1219	}
1220
1221	$format[] = $this->get_format_by_datatype( $value['type'] ); // Format for the WP database methods.
1222	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value['type'] );
1223	}
1224	return [
1225	'data' => $_data,
1226	'format' => $format,
1227	];
1228	}
1229
1230	/**
1231	* Get the SQL format specifier based on the provided data type.
1232	*
1233	* @param string $type The data type for which to get the SQL format specifier.
1234	* Possible values: 'string', 'array', 'number', 'boolean'.
1235	* @since 0.0.10
1236	* @return string The SQL format specifier. One of '%s' for string or array (converted to JSON), '%d' for number or boolean.
1237	*/
1238	protected function get_format_by_datatype( $type ) {
1239	$format = '%s';
1240	switch ( $type ) {
1241	case 'string':
1242	case 'array': // Because array will be converted to json string.
1243	$format = '%s';
1244	break;
1245
1246	case 'number':
1247	case 'boolean':
1248	$format = '%d';
1249	break;
1250	}
1251
1252	return $format;
1253	}
1254
1255	/**
1256	* Decode data based on the schema data types.
1257	*
1258	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to decode.
1259	* The data will be decoded if the column type in the schema is 'array' (JSON string).
1260	* @since 0.0.10
1261	* @return array<mixed> An associative array of decoded data based on the schema.
1262	*/
1263	protected function decode_by_datatype( $data ) {
1264	$_data = [];
1265	foreach ( $this->get_schema() as $key => $schema ) {
1266	if ( ! array_key_exists( $key, $data ) ) {
1267	continue;
1268	}
1269
1270	// Lets decode from JSON to Array for the results.
1271	$_data[ $key ] = 'array' === $schema['type'] ? Helper::get_array_value( json_decode( Helper::get_string_value( $data[ $key ] ), true ) ) : $data[ $key ];
1272	}
1273	return $_data;
1274	}
1275
1276	/**
1277	* Encode a value based on the specified data type.
1278	*
1279	* @param mixed $value The value to encode. The encoding will depend on the data type specified.
1280	* @param string $type The data type for encoding. Possible values: 'string', 'number', 'boolean', 'array'.
1281	* @since 0.0.10
1282	* @return mixed The encoded value. The type of the return value depends on the specified type:
1283	* - 'string': Encoded as a string.
1284	* - 'number': Encoded as an integer.
1285	* - 'boolean': Encoded as a boolean.
1286	* - 'array': Encoded as a JSON string.
1287	* @since 1.8.0 - 'datetime': Returns the value as it is, assuming it is already in SQL DATETIME format.
1288	*/
1289	protected function encode_by_datatype( $value, $type ) {
1290	switch ( $type ) {
1291	case 'string':
1292	return Helper::get_string_value( $value );
1293
1294	case 'number':
1295	return Helper::get_integer_value( $value );
1296
1297	case 'boolean':
1298	return boolval( $value );
1299
1300	case 'array':
1301	// Lets json_encode array values instead of serializing it.
1302	return Helper::encode_json( Helper::get_array_value( $value ) );
1303
1304	case 'datetime':
1305	// For datetime, we will return the value as it is because we are using sql DATETIME format.
1306	return $value;
1307	}
1308	}
1309	}
1310