sureforms / inc / database / base.php

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

446 lines 12.1 KB

Raw Download Zip

1	<?php
2	/**
3	* SureForms Database Tables Base Class.
4	*
5	* @link https://sureforms.com
6	* @since 0.0.10
7	* @package SureForms
8	* @author SureForms <https://sureforms.com/>
9	*/
10
11	namespace SRFM\Inc\Database;
12
13	use SRFM\Inc\Helper;
14
15	// Exit if accessed directly.
16	defined( 'ABSPATH' ) \|\| exit;
17
18	/**
19	* SureForms Database Tables Base Class
20	*
21	* @since 0.0.10
22	*/
23	abstract class Base {
24
25	/**
26	* WordPress Database class instance.
27	*
28	* @var \wpdb
29	* @since 0.0.10
30	*/
31	protected $wpdb;
32
33	/**
34	* Current database table prefix mixed with 'srfm_' as ending.
35	*
36	* @var string
37	* @since 0.0.10
38	*/
39	protected $table_prefix;
40
41	/**
42	* Custom table suffix without any prefix. This needs to be overridden from child class.
43	* Eg: For entries table, suffix will be 'entries' which will be prefixed and finally named as 'wp_srfm_entries'.
44	*
45	* @var string
46	* @since 0.0.10
47	* @override
48	*/
49	protected $table_suffix;
50
51	/**
52	* Full table name mixed with table prefix and table suffix.
53	*
54	* @var string
55	* @since 0.0.10
56	*/
57	private $table_name;
58
59	/**
60	* Current table database result caches.
61	*
62	* @var array<mixed>
63	* @since 0.0.10
64	*/
65	private $caches = [];
66
67	/**
68	* Init class.
69	*
70	* @since 0.0.10
71	* @return void
72	*/
73	public function __construct() {
74	global $wpdb;
75
76	$this->wpdb = $wpdb;
77	$this->table_prefix = $this->wpdb->prefix . 'srfm_';
78	$this->table_name = $this->table_prefix . $this->table_suffix;
79	}
80
81	/**
82	* Returns the current table schema.
83	*
84	* @since 0.0.10
85	* @return array<string,array<mixed>>
86	*/
87	abstract public function get_schema();
88
89	/**
90	* Returns full table name.
91	*
92	* @since 0.0.10
93	* @return string
94	*/
95	public function get_tablename() {
96	return $this->table_name;
97	}
98
99	/**
100	* Retrieve a cached value by its key.
101	*
102	* @param string $key The cache key.
103	* @since 0.0.10
104	* @return mixed\|null The cached value if it exists, or null if the key does not exist in the cache.
105	*/
106	protected function cache_get( $key ) {
107	$key = md5( $key );
108	if ( ! isset( $this->caches[ $key ] ) ) {
109	return null;
110	}
111	return $this->caches[ $key ];
112	}
113
114	/**
115	* Store a value in the cache with the specified key.
116	*
117	* @param string $key The cache key.
118	* @param mixed $value The value to store in the cache.
119	* @since 0.0.10
120	* @return mixed The stored value.
121	*/
122	protected function cache_set( $key, $value ) {
123	$key = md5( $key );
124	$this->caches[ $key ] = $value;
125	return $value;
126	}
127
128	/**
129	* Reset the cache by clearing all stored values.
130	*
131	* @since 0.0.10
132	* @return void
133	*/
134	protected function cache_reset() {
135	$this->caches = [];
136	}
137
138	/**
139	* Conditionally returns current database charset or collate.
140	*
141	* @since 0.0.10
142	* @return string
143	*/
144	public function get_charset_collate() {
145	$charset_collate = '';
146
147	if ( $this->wpdb->has_cap( 'collation' ) ) {
148	if ( ! empty( $this->wpdb->charset ) ) {
149	$charset_collate = "DEFAULT CHARACTER SET {$this->wpdb->charset}";
150	}
151	if ( ! empty( $this->wpdb->collate ) ) {
152	$charset_collate .= " COLLATE {$this->wpdb->collate}";
153	}
154	}
155
156	return $charset_collate;
157	}
158
159	/**
160	* Create table.
161	*
162	* @param array<string> $columns Array of columns.
163	* @since 0.0.10
164	* @return int\|bool
165	*/
166	public function create( $columns = [] ) {
167	if ( empty( $columns ) ) {
168	return false; // It's better to return a boolean for failure.
169	}
170
171	// Prepare columns list.
172	$columns_list = implode(
173	', ',
174	$columns
175	);
176
177	// Execute the query.
178	// phpcs:ignore
179	return $this->wpdb->query( "CREATE TABLE IF NOT EXISTS {$this->get_tablename()} ( {$columns_list} ) {$this->get_charset_collate()}" );
180	}
181
182	/**
183	* Drop or delete current table.
184	*
185	* @since 0.0.10
186	* @return int\|bool
187	*/
188	public function drop() {
189	$wpdb = $this->wpdb;
190
191	// Escape table name.
192	$table_name = $wpdb->esc_like( $this->get_tablename() );
193
194	// Prepare the SQL query.
195	$query = $wpdb->prepare(
196	'DROP TABLE IF EXISTS %s',
197	$table_name
198	);
199
200	if ( ! $query ) {
201	return false;
202	}
203
204	// Execute the query.
205	// phpcs:ignore
206	return $wpdb->query( $query );
207	}
208
209	/**
210	* Check if current table exists.
211	*
212	* @since 0.0.10
213	* @return boolean
214	*/
215	public function exists() {
216	global $wpdb;
217
218	// Escape table name.
219	$table_name = $wpdb->esc_like( $this->get_tablename() );
220
221	// Prepare the SQL query to check if the table exists.
222	$query = $wpdb->prepare(
223	'SHOW TABLES LIKE %s',
224	$table_name
225	);
226
227	// Check if the table exists.
228	// phpcs:ignore
229	if ( $wpdb->get_var( $query ) === $table_name ) {
230	return true;
231	}
232
233	return false;
234	}
235
236	/**
237	* Insert data. Basically, a wrapper method for wpdb::insert.
238	*
239	* @param array<mixed> $data Data to insert (in column => value pairs).
240	* Both `$data` columns and `$data` values should be "raw" (neither should be SQL escaped).
241	* Sending a null value will cause the column to be set to NULL - the corresponding
242	* format is ignored in this case.
243	* @param string[]\|string $format Optional. An array of formats to be mapped to each of the value in `$data`.
244	* If string, that format will be used for all of the values in `$data`.
245	* A format is one of '%d', '%f', '%s' (integer, float, string).
246	* If omitted, all values in `$data` will be treated as strings unless otherwise
247	* specified in wpdb::$field_types. Default null.
248	* @since 0.0.10
249	* @return int\|false The number of rows inserted, or false on error.
250	*/
251	public function insert( $data, $format = null ) {
252	$prepared_data = $this->prepare_data( $data );
253
254	if ( is_null( $format ) ) {
255	$format = $prepared_data['format'];
256	}
257
258	// @phpstan-ignore-next-line
259	return $this->wpdb->insert( $this->get_tablename(), $prepared_data['data'], $format );
260	}
261
262	/**
263	* Retrieve results from the database based on the given WHERE clauses and selected columns.
264	*
265	* This method builds a SQL SELECT query with optional WHERE clauses and retrieves the results
266	* from the database. The results are cached to improve performance on subsequent requests.
267	*
268	* @param array<mixed> $where_clauses Optional. An associative array of WHERE clauses for the SQL query.
269	* Each key represents a column name, and each value is the value
270	* to match. If the value is an array, it will be used in an IN clause.
271	* Example: ['column1' => 'value1', 'column2' => ['value2', 'value3']].
272	* Default is an empty array.
273	* @param string $columns Optional. A string specifying which columns to select. Defaults to '*' (all columns).
274	* @since 0.0.10
275	* @return array<mixed> An associative array of results where each element represents a row, or an empty array if no results are found.
276	*/
277	public function get_results( $where_clauses = [], $columns = '*' ) {
278	$wpdb = $this->wpdb;
279
280	$table_name = $this->get_tablename();
281
282	// Start building the query.
283	$query = "SELECT {$columns} FROM {$table_name}";
284
285	// If there are WHERE clauses, prepare and append them to the query.
286	if ( is_array( $where_clauses ) && ! empty( $where_clauses ) ) {
287	// Start constructing WHERE clause.
288	$where_clause = [];
289	$values = [];
290
291	// Current table schema.
292	$schema = $this->get_schema();
293
294	foreach ( $where_clauses as $key => $value ) {
295	if ( ! isset( $schema[ $key ] ) ) {
296	// Skip strictly if current key is not in our schema.
297	continue;
298	}
299
300	// @phpstan-ignore-next-line
301	$where_clause[] = $key . ' = ' . $this->get_format_by_datatype( $schema[ $key ]['type'] );
302	$values[] = $value;
303	}
304
305	if ( ! empty( $where_clause ) ) {
306	// Combine the WHERE clauses into a single string.
307	$query .= ' WHERE ' . implode( ' AND ', $where_clause );
308	}
309
310	// Prepare the query with placeholders.
311	// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
312	// @phpstan-ignore-next-line
313	$query = $wpdb->prepare( $query, ...$values );
314	// phpcs:enable
315	}
316
317	// Add a semicolon (optional, not necessary in practice).
318	$query .= ';';
319
320	$cached_results = $this->cache_get( $query );
321	if ( $cached_results ) {
322	// Return the cached data if exists.
323	return Helper::get_array_value( $cached_results );
324	}
325
326	// phpcs:ignore
327	$results = $wpdb->get_results( $query, ARRAY_A );
328
329	if ( ! empty( $results ) && is_array( $results ) ) {
330	foreach ( $results as &$result ) {
331	$result = $this->decode_by_datatype( $result );
332	}
333	}
334
335	// Execute the query and return results.
336	return Helper::get_array_value( $this->cache_set( $query, $results ) );
337	}
338
339	/**
340	* Prepare and format data based on the schema.
341	*
342	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to process.
343	* Missing values will be replaced with default values specified in the schema.
344	* @since 0.0.10
345	* @return array<array<mixed>> An associative array containing:
346	* - 'data': Prepared data with values encoded according to their data types.
347	* - 'format': An array of format specifiers corresponding to the data values.
348	*/
349	protected function prepare_data( $data ) {
350	$_data = [];
351	$format = [];
352	foreach ( $this->get_schema() as $key => $value ) {
353	// Process defaults.
354	if ( ! isset( $data[ $key ] ) ) {
355	if ( ! isset( $value['default'] ) ) {
356	continue;
357	}
358	$data[ $key ] = $value['default'];
359	}
360
361	$format[] = $this->get_format_by_datatype( $value['type'] ); // Format for the WP database methods.
362	$_data[ $key ] = $this->encode_by_datatype( $data[ $key ], $value['type'] );
363	}
364	return [
365	'data' => $_data,
366	'format' => $format,
367	];
368	}
369
370	/**
371	* Get the SQL format specifier based on the provided data type.
372	*
373	* @param string $type The data type for which to get the SQL format specifier.
374	* Possible values: 'string', 'array', 'number', 'boolean'.
375	* @since 0.0.10
376	* @return string The SQL format specifier. One of '%s' for string or array (converted to JSON), '%d' for number or boolean.
377	*/
378	protected function get_format_by_datatype( $type ) {
379	$format = '%s';
380	switch ( $type ) {
381	case 'string':
382	case 'array': // Because array will be converted to json string.
383	$format = '%s';
384	break;
385
386	case 'number':
387	case 'boolean':
388	$format = '%d';
389	break;
390	}
391
392	return $format;
393	}
394
395	/**
396	* Decode data based on the schema data types.
397	*
398	* @param array<mixed> $data An associative array of data where the key is the column name and the value is the data to decode.
399	* The data will be decoded if the column type in the schema is 'array' (JSON string).
400	* @since 0.0.10
401	* @return array<mixed> An associative array of decoded data based on the schema.
402	*/
403	protected function decode_by_datatype( $data ) {
404	$_data = [];
405	foreach ( $this->get_schema() as $key => $schema ) {
406	// Process defaults.
407	if ( ! isset( $data[ $key ] ) ) {
408	continue;
409	}
410
411	// Lets decode from JSON to Array for the results.
412	$_data[ $key ] = 'array' === $schema['type'] ? json_decode( Helper::get_string_value( $data[ $key ] ), true ) : $data[ $key ];
413	}
414	return $_data;
415	}
416
417	/**
418	* Encode a value based on the specified data type.
419	*
420	* @param mixed $value The value to encode. The encoding will depend on the data type specified.
421	* @param string $type The data type for encoding. Possible values: 'string', 'number', 'boolean', 'array'.
422	* @since 0.0.10
423	* @return mixed The encoded value. The type of the return value depends on the specified type:
424	* - 'string': Encoded as a string.
425	* - 'number': Encoded as an integer.
426	* - 'boolean': Encoded as a boolean.
427	* - 'array': Encoded as a JSON string.
428	*/
429	protected function encode_by_datatype( $value, $type ) {
430	switch ( $type ) {
431	case 'string':
432	return Helper::get_string_value( $value );
433
434	case 'number':
435	return Helper::get_integer_value( $value );
436
437	case 'boolean':
438	return boolval( $value );
439
440	case 'array':
441	// Lets json_encode array values instead of serializing it.
442	return wp_json_encode( Helper::get_array_value( $value ) );
443	}
444	}
445	}
446