charitable / includes / abstracts / abstract-class-charitable-db.php

abstract-class-charitable-db.php in Charitable – Donation & Fundraising Platform (Donation Forms, Recurring Donations & Fundraising Campaigns) 1.8.12.3, at includes/abstracts/abstract-class-charitable-db.php

461 lines 14.3 KB

Raw Download Zip

1	<?php
2	/**
3	* Charitable DB base class.
4	*
5	* Credit: This is based on Easy Digital Downloads' EDD_DB class. Major props to Pippin Williamson.
6	* https://github.com/easydigitaldownloads/Easy-Digital-Downloads/blob/master/includes/class-edd-db.php
7	*
8	* @package Charitable/Classes/Charitable_DB
9	* @author David Bisset
10	* @copyright Copyright (c) 2023, WP Charitable LLC
11	* @license http://opensource.org/licenses/gpl-2.0.php GNU Public License
12	* @since 1.0.0
13	* @version 1.8.9.1
14	*/
15
16	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
17
18	// Exit if accessed directly.
19	if ( ! defined( 'ABSPATH' ) ) {
20	exit;
21	}
22
23	if ( ! class_exists( 'Charitable_DB' ) ) :
24
25	/**
26	* Charitable_DB
27	*
28	* @abstract
29	* @since 1.0.0
30	*/
31	abstract class Charitable_DB {
32
33	/**
34	* The name of our database table
35	*
36	* @since 1.0.0
37	*
38	* @var string
39	*/
40	public $table_name;
41
42	/**
43	* The version of our database table
44	*
45	* @since 1.0.0
46	*
47	* @var string
48	*/
49	public $version;
50
51	/**
52	* The name of the primary column
53	*
54	* @since 1.0.0
55	*
56	* @var string
57	*/
58	public $primary_key;
59
60	/**
61	* Get things started
62	*
63	* @since 1.0.0
64	*/
65	public function __construct() {}
66
67	/**
68	* Whitelist of columns
69	*
70	* @since 1.0.0
71	*
72	* @return array
73	*/
74	abstract public function get_columns();
75
76	/**
77	* Default column values
78	*
79	* @since 1.0.0
80	*
81	* @return array
82	*/
83	abstract public function get_column_defaults();
84
85	/**
86	* Return the format for the given column.
87	*
88	* @since 1.0.0
89	* @version 1.8.9.1
90	*
91	* @param string $column The column to get the format for.
92	* @return %s, %d or %f
93	*/
94	public function get_column_format( $column ) {
95	$columns = $this->get_columns();
96	$format = isset( $columns[ $column ] ) ? $columns[ $column ] : false;
97
98	// If the column isn't found, throw an exception.
99	if ( false === $format ) {
100	throw new Exception( sprintf( 'Invalid column passed %s', esc_html( $column ) ) );
101	}
102
103	// If the column format isn't valid, throw an exception.
104	if ( ! in_array( $format, array( '%s', '%d', '%f' ) ) ) {
105	throw new Exception( sprintf( 'Invalid column format for column %s. Format returned %s', esc_html( $column ), esc_html( $format ) ) );
106	}
107
108	return $format;
109	}
110
111	/**
112	* Validate that a column name exists in the table schema.
113	*
114	* @since 1.8.8.6
115	*
116	* @param string $column The column name to validate.
117	* @return string The validated column name.
118	* @throws Exception If column is invalid.
119	*/
120	protected function validate_column_name( $column ) {
121	$columns = $this->get_columns();
122	if ( ! isset( $columns[ $column ] ) ) {
123	throw new Exception( sprintf( 'Invalid column passed %s', esc_html( $column ) ) );
124	}
125	return $column;
126	}
127
128	/**
129	* Retrieve a row by the primary key
130	*
131	* @since 1.0.0
132	*
133	* @return object
134	*/
135	public function get( $row_id ) {
136	global $wpdb;
137
138	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
139	// Direct database queries are expected in this database abstraction class.
140	// $this->table_name and $this->primary_key are class properties set at initialization and validated.
141	return $wpdb->get_row(
142	$wpdb->prepare(
143	"SELECT * FROM $this->table_name WHERE $this->primary_key = %d LIMIT 1;",
144	$row_id
145	)
146	);
147	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
148	}
149
150	/**
151	* Retrieve a row by a specific column / value
152	*
153	* @since 1.0.0
154	* @return object
155	*/
156	public function get_by( $column, $row_id ) {
157	global $wpdb;
158
159	// Validate column name before use.
160	$column = $this->validate_column_name( $column );
161
162	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
163	// Direct database queries are expected in this database abstraction class.
164	// $column is validated above via validate_column_name(), $this->table_name is a class property set at initialization.
165	return $wpdb->get_row(
166	$wpdb->prepare(
167	"SELECT * FROM $this->table_name WHERE $column = {$this->get_column_format($column)} LIMIT 1;",
168	$row_id
169	)
170	);
171	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
172	}
173
174	/**
175	* Retrieve a specific column's value by the primary key.
176	*
177	* @since 1.0.0
178	*
179	* @return string
180	*/
181	public function get_column( $column, $row_id ) {
182	global $wpdb;
183
184	// Validate column name before use.
185	$column = $this->validate_column_name( $column );
186
187	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
188	// Direct database queries are expected in this database abstraction class.
189	// $column is validated above via validate_column_name(), $this->table_name and $this->primary_key are class properties set at initialization.
190	return $wpdb->get_var(
191	$wpdb->prepare(
192	"SELECT $column FROM $this->table_name WHERE $this->primary_key = %d LIMIT 1;",
193	$row_id
194	)
195	);
196	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
197	}
198
199	/**
200	* Retrieve a specific column's value by the the specified column / value
201	*
202	* @since 1.0.0
203	*
204	* @param string $column The column with the value we are retrieving.
205	* @param string $column_where The column that we will be querying by.
206	* @param mixed $column_value The value we are searching for in the column set by $column_where.
207	* @return mixed
208	*/
209	public function get_column_by( $column, $column_where, $column_value ) {
210	global $wpdb;
211
212	// Validate column names before use.
213	$column = $this->validate_column_name( $column );
214	$column_where = $this->validate_column_name( $column_where );
215
216	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
217	// Direct database queries are expected in this database abstraction class.
218	// $column and $column_where are validated above via validate_column_name(), $this->table_name is a class property set at initialization.
219	return $wpdb->get_var(
220	$wpdb->prepare(
221	"SELECT $column FROM $this->table_name WHERE $column_where = {$this->get_column_format($column_where)} LIMIT 1;",
222	$column_value
223	)
224	);
225	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
226	}
227
228	/**
229	* Retrieve all values in a specific column by the specified column / value.
230	*
231	* @since 1.5.11
232	*
233	* @param string $column The column with the value we are retrieving.
234	* @param string $column_where The column that we will be querying by.
235	* @param mixed $column_value The value we are searching for in the column set by $column_where.
236	* @return array
237	*/
238	public function get_column_all_by( $column, $column_where, $column_value ) {
239	global $wpdb;
240
241	// Validate column names before use.
242	$column = $this->validate_column_name( $column );
243	$column_where = $this->validate_column_name( $column_where );
244
245	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
246	// Direct database queries are expected in this database abstraction class.
247	// $column and $column_where are validated above via validate_column_name(), $this->table_name is a class property set at initialization.
248	return $wpdb->get_col(
249	$wpdb->prepare(
250	"SELECT $column FROM $this->table_name WHERE $column_where = {$this->get_column_format($column_where)};",
251	$column_value
252	)
253	);
254	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
255	}
256
257	/**
258	* Count all rows.
259	*
260	* @since 1.0.0
261	*
262	* @return int
263	*/
264	public function count_all() {
265	global $wpdb;
266
267	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
268	// Direct database queries are expected in this database abstraction class.
269	// $this->table_name is a class property set at initialization.
270	return $wpdb->get_var(
271	"SELECT COUNT( * ) FROM $this->table_name;"
272	);
273	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
274	}
275
276	/**
277	* Count all rows that certain criteria.
278	*
279	* @since 1.0.0
280	*
281	* @return int
282	*/
283	public function count_by( $column, $column_value ) {
284	global $wpdb;
285
286	// Validate column name before use.
287	$column = $this->validate_column_name( $column );
288
289	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
290	// Direct database queries are expected in this database abstraction class.
291	// $column is validated above via validate_column_name(), $this->table_name is a class property set at initialization.
292	return $wpdb->get_var(
293	$wpdb->prepare(
294	"SELECT COUNT( * ) FROM $this->table_name WHERE $column = {$this->get_column_format($column)};",
295	$column_value
296	)
297	);
298	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
299	}
300
301	/**
302	* Insert a new row
303	*
304	* @since 1.0.0
305	* @version 1.8.8.6
306	*
307	* @return int
308	*/
309	public function insert( $data, $type = '' ) {
310	global $wpdb;
311
312	/* Set default values */
313	$data = wp_parse_args( $data, $this->get_column_defaults() );
314
315	do_action( 'charitable_pre_insert_' . $type, $data );
316
317	/* Initialise column format array */
318	$column_formats = $this->get_columns();
319
320	/* Force fields to lower case */
321	$data = array_change_key_case( $data );
322
323	/* White list columns */
324	$data = array_intersect_key( $data, $column_formats );
325
326	/* Reorder $column_formats to match the order of columns given in $data */
327	$data_keys = array_keys( $data );
328	$column_formats = array_merge( array_flip( $data_keys ), $column_formats );
329
330	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
331	// Direct database queries are expected in this database abstraction class.
332	// $this->table_name is a class property set at initialization, and $data/$column_formats are sanitized above.
333	$inserted = $wpdb->insert( $this->table_name, $data, $column_formats );
334	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
335
336	/* If the insert failed, return 0 */
337	if ( false === $inserted ) {
338	return 0;
339	}
340
341	do_action( 'charitable_post_insert_' . $type, $wpdb->insert_id, $data );
342
343	return $wpdb->insert_id;
344	}
345
346	/**
347	* Update a row.
348	*
349	* @since 1.0.0
350	* @version 1.8.8.6
351	*
352	* @global WPDB $wpdb
353	* @param int $row_id The record to update.
354	* @param array $data Data to update.
355	* @param string $where Column used in where argument.
356	* @return boolean
357	*/
358	public function update( $row_id, $data = array(), $where = '' ) {
359	global $wpdb;
360
361	// Row ID must be positive integer.
362	$row_id = absint( $row_id );
363
364	if ( empty( $row_id ) ) {
365	return false;
366	}
367
368	if ( empty( $where ) ) {
369	$where = $this->primary_key;
370	}
371
372	/* Initialise column format array. */
373	$column_formats = $this->get_columns();
374
375	/* Force fields to lower case */
376	$data = array_change_key_case( $data );
377
378	/* White list columns. */
379	$data = array_intersect_key( $data, $column_formats );
380
381	/* Reorder $column_formats to match the order of columns given in $data. */
382	$data_keys = array_keys( $data );
383	$column_formats = array_merge( array_flip( $data_keys ), $column_formats );
384
385	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
386	// Direct database queries are expected in this database abstraction class.
387	// $this->table_name is a class property set at initialization, $where is validated (defaults to primary_key or validated column),
388	// $row_id is sanitized via absint(), and $data/$column_formats are sanitized above.
389	$updated = $wpdb->update( $this->table_name, $data, array( $where => $row_id ), $column_formats );
390	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
391
392	if ( false === $updated ) {
393	return false;
394	}
395
396	return true;
397	}
398
399	/**
400	* Delete a row identified by the primary key
401	*
402	* @since 1.0.0
403	*
404	* @param int $row_id
405	* @return boolean
406	*/
407	public function delete( $row_id = 0 ) {
408	// Row ID must be positive integer
409	$row_id = absint( $row_id );
410
411	if ( empty( $row_id ) ) {
412	return false;
413	}
414
415	return $this->delete_by( $this->primary_key, $row_id );
416	}
417
418	/**
419	* Delete a row identified by a specific column.
420	*
421	* @since 1.2.0
422	*
423	* @global WPDB $wpdb
424	* @param string $column The column to search for the row ID.
425	* @param mixed $row_id The row ID to be removed.
426	* @return boolean
427	*/
428	public function delete_by( $column, $row_id = 0 ) {
429	global $wpdb;
430
431	// Validate column name before use.
432	$column = $this->validate_column_name( $column );
433
434	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
435	// Direct database queries are expected in this database abstraction class.
436	// $column is validated above via validate_column_name(), $this->table_name is a class property set at initialization.
437	$result = $wpdb->query( $wpdb->prepare( "DELETE FROM $this->table_name WHERE $column = {$this->get_column_format($column)}", $row_id ) );
438	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
439
440	return false !== $result;
441	}
442
443	/**
444	* Create the table.
445	*
446	* @since 1.0.0
447	*
448	* @param string $sql The SQL statement to execute.
449	* @return void
450	*/
451	protected function _create_table( $sql ) {
452	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
453
454	dbDelta( $sql );
455
456	update_option( $this->table_name . '_db_version', $this->version );
457	}
458	}
459
460	endif;
461