suredonation / inc / database / base.php

base.php in SureDonation – Donation Forms, Fundraising Campaigns & Donor Management 1.2.0, at inc/database/base.php

601 lines 14.5 KB

Raw Download Zip

1	<?php
2	/**
3	* SureDonation Database Tables Base Class.
4	*
5	* @package SureDonation
6	*/
7
8	namespace SureDonation\Inc\Database;
9
10	use SureDonation\Inc\Helper;
11
12	// Exit if accessed directly.
13	defined( 'ABSPATH' ) \|\| exit;
14
15	/**
16	* SureDonation Database Tables Base Class
17	*
18	* Provides basic database operations using $wpdb methods.
19	* All queries in child classes use $wpdb->prepare() with explicit placeholders.
20	*
21	* @since 0.0.1
22	*/
23	abstract class Base {
24	/**
25	* Option key for database table versions within consolidated options.
26	*
27	* @since 0.0.1
28	*/
29	public const VERSION_OPTION_KEY = 'database_table_versions';
30
31	/**
32	* WordPress Database class instance.
33	*
34	* @var \wpdb
35	* @since 0.0.1
36	*/
37	protected $wpdb;
38
39	/**
40	* Current database table prefix mixed with 'suredonation_' as ending.
41	*
42	* @var string
43	* @since 0.0.1
44	*/
45	protected $table_prefix;
46
47	/**
48	* Custom table suffix without any prefix. This needs to be overridden from child class.
49	*
50	* @var string
51	* @since 0.0.1
52	*/
53	protected $table_suffix;
54
55	/**
56	* Version for current custom table.
57	*
58	* @var int
59	* @since 0.0.1
60	*/
61	protected $table_version = 1;
62
63	/**
64	* Full table name mixed with table prefix and table suffix.
65	*
66	* @var string
67	* @since 0.0.1
68	*/
69	private $table_name;
70
71	/**
72	* Whether or not the current database table is upgradable.
73	*
74	* @var bool
75	* @since 0.0.1
76	*/
77	private $db_upgradable;
78
79	/**
80	* Current table database result caches.
81	*
82	* @var array<mixed>
83	* @since 0.0.1
84	*/
85	private $caches = [];
86
87	/**
88	* Init class.
89	*
90	* @return void
91	* @since 0.0.1
92	*/
93	public function __construct() {
94	global $wpdb;
95
96	$this->wpdb = $wpdb;
97	$this->table_prefix = $this->wpdb->prefix . 'suredonation_';
98	$this->table_name = $this->table_prefix . $this->table_suffix;
99	}
100
101	/**
102	* Actions to initialize during object unload.
103	*
104	* @return void
105	* @since 0.0.1
106	*/
107	public function __destruct() {
108	$this->stop_db_upgrade();
109	}
110
111	/**
112	* Returns the current table schema.
113	*
114	* @return array<string,array<mixed>>
115	* @since 0.0.1
116	*/
117	abstract public function get_schema();
118
119	/**
120	* Current table columns definition to create table.
121	*
122	* @return array<string>
123	* @since 0.0.1
124	*/
125	abstract public function get_columns_definition();
126
127	/**
128	* Columns to add if the table already exists. Override in child class.
129	* Each entry is a bare SQL fragment: "column_name TYPE [constraints] [AFTER other_col]"
130	* or "INDEX index_name (column)" for indexes.
131	*
132	* @return array<string>
133	* @since 1.0.0
134	*/
135	public function get_new_columns_definition() {
136	return [];
137	}
138
139	/**
140	* Start the database upgrade process.
141	*
142	* @return void
143	* @since 0.0.1
144	*/
145	public function start_db_upgrade() {
146	$versions = Helper::get_suredonation_option( self::VERSION_OPTION_KEY, [] );
147	$versions = is_array( $versions ) ? $versions : [];
148	$prev_version = ! empty( $versions[ $this->table_suffix ] ) ? absint( $versions[ $this->table_suffix ] ) : false;
149
150	if ( ! $prev_version ) {
151	$this->db_upgradable = true;
152	return;
153	}
154
155	$this->db_upgradable = $this->table_version > $prev_version;
156	}
157
158	/**
159	* Stop the database upgrade process.
160	*
161	* @return bool Returns true on success.
162	* @since 0.0.1
163	*/
164	public function stop_db_upgrade() {
165	if ( ! $this->db_upgradable ) {
166	return false;
167	}
168
169	$versions = Helper::get_suredonation_option( self::VERSION_OPTION_KEY, [] );
170	$versions = is_array( $versions ) ? $versions : [];
171
172	$versions[ $this->table_suffix ] = $this->table_version;
173
174	Helper::update_suredonation_option( self::VERSION_OPTION_KEY, $versions );
175
176	return true;
177	}
178
179	/**
180	* Check if current table's DB is upgradable or not.
181	*
182	* @return bool True or false depending if DB is upgradable or not.
183	* @since 0.0.1
184	*/
185	public function is_db_upgradable() {
186	return $this->db_upgradable;
187	}
188
189	/**
190	* Returns full table name.
191	*
192	* @return string
193	* @since 0.0.1
194	*/
195	public function get_tablename() {
196	return $this->table_name;
197	}
198
199	/**
200	* Conditionally returns current database charset or collate.
201	*
202	* @return string
203	* @since 0.0.1
204	*/
205	public function get_charset_collate() {
206	$charset_collate = '';
207
208	if ( $this->wpdb->has_cap( 'collation' ) ) {
209	if ( ! empty( $this->wpdb->charset ) ) {
210	$charset_collate = "DEFAULT CHARACTER SET {$this->wpdb->charset}";
211	}
212	if ( ! empty( $this->wpdb->collate ) ) {
213	$charset_collate .= " COLLATE {$this->wpdb->collate}";
214	}
215	}
216
217	return $charset_collate;
218	}
219
220	/**
221	* Create table.
222	*
223	* @param array<string> $columns Array of columns.
224	* @return int\|bool
225	* @since 0.0.1
226	*/
227	public function create( $columns = [] ) {
228	if ( ! $this->db_upgradable ) {
229	return false;
230	}
231
232	if ( empty( $columns ) ) {
233	return false;
234	}
235
236	$columns_list = implode( ', ', $columns );
237	$wpdb = $this->wpdb;
238
239	// `$wpdb->prepare()` cannot be used here: it escapes single quotes in
240	// the interpolated column-definition string, turning literal `DEFAULT ''`
241	// into `DEFAULT \'\'`, which MySQL rejects with a syntax error. The
242	// table name, column list, and charset are all hardcoded DDL (not user
243	// input), so direct concatenation is safe.
244	$query = sprintf(
245	'CREATE TABLE IF NOT EXISTS `%s` ( %s ) %s',
246	esc_sql( $this->get_tablename() ),
247	$columns_list,
248	$this->get_charset_collate()
249	);
250
251	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
252	$result = $wpdb->query( $query );
253
254	if ( false === $result ) {
255	$this->db_upgradable = false;
256	}
257
258	return $result;
259	}
260
261	/**
262	* Add new columns to an existing table conditionally.
263	*
264	* Checks existing columns/indexes and only adds missing ones via ALTER TABLE.
265	*
266	* @param array<string> $new_columns Column definitions to add.
267	* @return int\|bool Result of ALTER query, or false.
268	* @since 1.0.0
269	*/
270	public function maybe_add_new_columns( $new_columns = [] ) {
271	if ( ! $new_columns ) {
272	return false;
273	}
274
275	if ( ! $this->db_upgradable ) {
276	return false;
277	}
278
279	$existing_columns = $this->get_columns();
280
281	if ( ! $existing_columns ) {
282	// Table does not exist or is new.
283	return false;
284	}
285
286	$existing_indexes = $this->get_indexes();
287	$alter_queries = [];
288	$wpdb = $this->wpdb;
289
290	foreach ( $new_columns as $column_definition ) {
291	// Check if this is an INDEX definition.
292	preg_match( '/INDEX\s+(.*?)\s+\(/', $column_definition, $index_matches );
293
294	if ( ! empty( $index_matches[1] ) ) {
295	if ( isset( $existing_indexes[ $index_matches[1] ] ) ) {
296	continue; // Index already exists.
297	}
298	// Index definitions come from get_new_columns() — hardcoded DDL, not user input.
299	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Hardcoded DDL fragment from get_new_columns().
300	$alter_queries[] = 'ADD ' . $column_definition;
301	continue;
302	}
303
304	// Extract column name from definition.
305	preg_match( '/(\w+)\s/', $column_definition, $column_matches );
306	$column_name = $column_matches[1] ?? '';
307
308	if ( ! isset( $existing_columns[ $column_name ] ) ) {
309	// Column definitions come from get_new_columns() — hardcoded DDL, not user input.
310	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Hardcoded DDL fragment from get_new_columns().
311	$alter_queries[] = 'ADD COLUMN ' . $column_definition;
312	}
313	}
314
315	if ( ! $alter_queries ) {
316	return false;
317	}
318
319	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- ALTER TABLE with prepared table name and hardcoded DDL fragments.
320	$result = $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ', $this->get_tablename() ) . implode( ', ', $alter_queries ) . ';' );
321
322	if ( false === $result ) {
323	$this->db_upgradable = false;
324	}
325
326	return $result;
327	}
328
329	/**
330	* Returns an array columns of current table.
331	*
332	* @return array<string,array<string,mixed>>
333	* @since 0.0.1
334	*/
335	public function get_columns() {
336	$wpdb = $this->wpdb;
337
338	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
339	$columns = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $this->get_tablename() ), ARRAY_A );
340
341	if ( empty( $columns ) ) {
342	return [];
343	}
344
345	$_columns = [];
346	if ( is_array( $columns ) ) {
347	foreach ( $columns as $column ) {
348	if ( ! is_string( $column['Field'] ) ) {
349	continue;
350	}
351
352	$_columns[ $column['Field'] ] = $column;
353	}
354	}
355	return $_columns;
356	}
357
358	/**
359	* Returns an array indexes of current table.
360	*
361	* @return array<mixed>
362	* @since 0.0.1
363	*/
364	public function get_indexes() {
365	$wpdb = $this->wpdb;
366
367	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
368	$indexes = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i', $this->get_tablename() ), ARRAY_A );
369
370	if ( empty( $indexes ) ) {
371	return [];
372	}
373
374	$_indexes = [];
375	if ( is_array( $indexes ) ) {
376	foreach ( $indexes as $index ) {
377	$_indexes[ $index['Key_name'] ] = $index; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
378	}
379	}
380	return $_indexes;
381	}
382
383	/**
384	* Insert data. Wrapper method for wpdb::insert.
385	*
386	* @param array<mixed> $data Data to insert.
387	* @param array<string>\|string\|null $format Optional format specifiers.
388	* @return int\|false The id of the inserted entry, or false on error.
389	* @since 0.0.1
390	*/
391	public function use_insert( $data, $format = null ) {
392	$prepared_data = $this->prepare_data( $data );
393
394	if ( is_null( $format ) ) {
395	$format = $prepared_data['format'];
396	}
397
398	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
399	$result = $this->wpdb->insert( $this->get_tablename(), $prepared_data['data'], $format ); // @phpstan-ignore argument.type
400	return $result ? $this->wpdb->insert_id : false;
401	}
402
403	/**
404	* Update a row data of current table. Wrapper method for wpdb::update.
405	*
406	* @param array<string,mixed> $data Data to update.
407	* @param array<string,mixed> $where WHERE clauses.
408	* @return int\|false The number of rows updated, or false on error.
409	* @since 0.0.1
410	*/
411	public function use_update( $data, $where ) {
412	$prepared_data = $this->prepare_data( $data, true );
413	$format = $prepared_data['format'];
414
415	$this->cache_reset();
416
417	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
418	return $this->wpdb->update(
419	$this->get_tablename(),
420	$prepared_data['data'],
421	$where,
422	$format // @phpstan-ignore argument.type
423	);
424	}
425
426	/**
427	* Delete a row data of current table. Wrapper method for wpdb::delete.
428	*
429	* @param array<string,mixed> $where WHERE clauses.
430	* @param array<string>\|string $where_format Optional format specifiers.
431	* @return int\|false The number of rows deleted, or false on error.
432	* @since 0.0.1
433	*/
434	public function use_delete( $where, $where_format = null ) {
435	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
436	return $this->wpdb->delete( $this->get_tablename(), $where, $where_format );
437	}
438
439	/**
440	* Retrieve a cached value by its key.
441	*
442	* @param string $key The cache key.
443	* @return mixed\|null The cached value or null.
444	* @since 0.0.1
445	*/
446	protected function cache_get( $key ) {
447	$key = md5( $key );
448	if ( ! isset( $this->caches[ $key ] ) ) {
449	return null;
450	}
451	return $this->caches[ $key ];
452	}
453
454	/**
455	* Store a value in the cache.
456	*
457	* @param string $key The cache key.
458	* @param mixed $value The value to store.
459	* @return mixed The stored value.
460	* @since 0.0.1
461	*/
462	protected function cache_set( $key, $value ) {
463	$key = md5( $key );
464	$this->caches[ $key ] = $value;
465	return $value;
466	}
467
468	/**
469	* Reset the cache.
470	*
471	* @return void
472	* @since 0.0.1
473	*/
474	protected function cache_reset() {
475	$this->caches = [];
476	}
477
478	/**
479	* Prepare and format data based on the schema.
480	*
481	* @param array<mixed> $data Data to prepare.
482	* @param bool $skip_defaults Whether to skip defaults.
483	* @return array<array<mixed>> Prepared data with format specifiers.
484	* @since 0.0.1
485	*/
486	protected function prepare_data( $data, $skip_defaults = false ) {
487	$_data = [];
488	$format = [];
489
490	foreach ( $this->get_schema() as $key => $value ) {
491	if ( ! isset( $data[ $key ] ) ) {
492	if ( $skip_defaults \|\| ! isset( $value['default'] ) ) {
493	continue;
494	}
495	$data[ $key ] = $value['default'];
496	}
497
498	$value_type = isset( $value['type'] ) && is_string( $value['type'] ) ? $value['type'] : 'string';
499	$format[] = $this->get_format_by_datatype( $value_type );
500	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value_type );
501	}
502
503	return [
504	'data' => $_data,
505	'format' => $format,
506	];
507	}
508
509	/**
510	* Get the SQL format specifier based on the provided data type.
511	*
512	* @param string $type The data type.
513	* @return string The SQL format specifier.
514	* @since 0.0.1
515	*/
516	protected function get_format_by_datatype( $type ) {
517	$format = '%s';
518
519	switch ( $type ) {
520	case 'string':
521	case 'array':
522	case 'datetime':
523	$format = '%s';
524	break;
525
526	case 'number':
527	case 'boolean':
528	$format = '%d';
529	break;
530
531	case 'decimal':
532	$format = '%f';
533	break;
534	}
535
536	return $format;
537	}
538
539	/**
540	* Decode data based on the schema data types.
541	*
542	* @param array<mixed> $data Data to decode.
543	* @return array<mixed> Decoded data.
544	* @since 0.0.1
545	*/
546	protected function decode_by_datatype( $data ) {
547	$_data = [];
548
549	foreach ( $this->get_schema() as $key => $schema ) {
550	if ( ! array_key_exists( $key, $data ) ) {
551	continue;
552	}
553
554	$value = $data[ $key ];
555	if ( isset( $schema['type'] ) && 'array' === $schema['type'] ) {
556	$json_string = is_scalar( $value ) ? (string) $value : '';
557	$_data[ $key ] = json_decode( $json_string, true );
558	if ( ! is_array( $_data[ $key ] ) ) {
559	$_data[ $key ] = [];
560	}
561	} else {
562	$_data[ $key ] = $value;
563	}
564	}
565
566	return $_data;
567	}
568
569	/**
570	* Encode a value based on the specified data type.
571	*
572	* @param mixed $value The value to encode.
573	* @param string $type The data type.
574	* @return mixed The encoded value.
575	* @since 0.0.1
576	*/
577	protected function encode_by_datatype( $value, $type ) {
578	switch ( $type ) {
579	case 'string':
580	return is_scalar( $value ) ? (string) $value : '';
581
582	case 'number':
583	return is_numeric( $value ) ? (int) $value : 0;
584
585	case 'boolean':
586	return (bool) $value;
587
588	case 'array':
589	return wp_json_encode( is_array( $value ) ? $value : [] );
590
591	case 'datetime':
592	return $value;
593
594	case 'decimal':
595	return is_numeric( $value ) ? (float) $value : 0.0;
596	}
597
598	return $value;
599	}
600	}
601