decalog / includes / system / class-database.php

class-database.php in DecaLog 4.4.0, at includes/system/class-database.php

293 lines 8.1 KB

Raw Download Zip

1	<?php
2	/**
3	* Database handling
4	*
5	* Handles all database operations.
6	*
7	* @package System
8	* @author Pierre Lannoy <https://pierre.lannoy.fr/>.
9	* @since 1.0.0
10	*/
11
12	namespace Decalog\System;
13
14	/**
15	* Define the database functionality.
16	*
17	* Handles all database operations.
18	*
19	* @package System
20	* @author Pierre Lannoy <https://pierre.lannoy.fr/>.
21	* @since 1.0.0
22	*/
23	class Database {
24
25	/**
26	* Initializes the class and set its properties.
27	*
28	* @since 1.0.0
29	*/
30	public function __construct() {
31	}
32
33	/**
34	* Update table with current value line.
35	*
36	* @param string $table_name The table to update.
37	* @param array $value The values to update or insert in the table.
38	* @return integer The insert id if anny.
39	* @since 1.0.0
40	*/
41	public function insert_line( $table_name, $value ) {
42	global $wpdb;
43	// phpcs:ignore
44	if ( $wpdb->insert( $wpdb->base_prefix . $table_name, $value ) ) {
45	return $wpdb->insert_id;
46	}
47	return 0;
48	}
49
50	/**
51	* Update table with current value lines.
52	*
53	* @param string $table_name The table to update.
54	* @param array $lines The array of value lines to update or insert in the table.
55	* @return array The list of insert ids if anny.
56	* @since 1.0.0
57	*/
58	public function insert_lines( $table_name, $lines ) {
59	global $wpdb;
60	$result = [];
61	foreach ( $lines as $line ) {
62	// phpcs:ignore
63	if ( $wpdb->insert( $wpdb->base_prefix . $table_name, $line ) ) {
64	$id = $wpdb->insert_id;
65	if ( 0 !== $id ) {
66	$result[] = $id;
67	}
68	}
69	}
70	return $result;
71	}
72
73	/**
74	* Delete some lines in a table.
75	*
76	* @param string $table_name The table to update.
77	* @param string $field_name The name of the field containing ids.
78	* @param array $value The list of id to delete.
79	* @param string $sep Optional. Separator.
80	* @return int\|false The number of rows deleted, or false on error.
81	* @since 1.0.0
82	*/
83	public function delete_lines( $table_name, $field_name, $value, $sep = '' ) {
84	global $wpdb;
85	$table_name = $wpdb->base_prefix . $table_name;
86	$sql = 'DELETE FROM ' . $table_name . ' WHERE ' . $field_name . ' IN (' . $sep . implode( $sep . ',' . $sep, $value ) . $sep . ')';
87	// phpcs:ignore
88	return $wpdb->query( $sql );
89	}
90
91	/**
92	* Load some lines from a table.
93	*
94	* @param string $table_name The table to load.
95	* @param string $field_name The name of the field containing ids.
96	* @param array $value The list of id to load.
97	* @param string $sep Optional. Separator.
98	* @return array The loaded lines.
99	* @since 1.0.0
100	*/
101	public function load_lines( $table_name, $field_name, $value, $sep = '' ) {
102	global $wpdb;
103	$table_name = $wpdb->base_prefix . $table_name;
104	$sql = 'SELECT * FROM ' . $table_name . ' WHERE ' . $field_name . ' IN (' . $sep . implode( $sep . ',' . $sep, $value ) . $sep . ')';
105	// phpcs:ignore
106	return $wpdb->get_results( $sql, ARRAY_A );
107	}
108
109	/**
110	* Update table with current value line.
111	*
112	* @param string $table_name The table name.
113	* @param array $value The values to update or insert in the table.
114	* @since 1.0.0
115	*/
116	public function insert_update( $table_name, $value ) {
117	$field_insert = [];
118	$value_insert = [];
119	$value_update = [];
120	foreach ( $value as $k => $v ) {
121	$field_insert[] = '`' . $k . '`';
122	$value_insert[] = "'" . $v . "'";
123	$value_update[] = '`' . $k . '`=' . "'" . $v . "'";
124	}
125	if ( count( $field_insert ) > 0 ) {
126	global $wpdb;
127	$sql = 'INSERT INTO `' . $wpdb->base_prefix . $table_name . '` ';
128	$sql .= '(' . implode( ',', $field_insert ) . ') ';
129	$sql .= 'VALUES (' . implode( ',', $value_insert ) . ') ';
130	$sql .= 'ON DUPLICATE KEY UPDATE ' . implode( ',', $value_update ) . ';';
131	// phpcs:ignore
132	$wpdb->query( $sql );
133	}
134	}
135
136	/**
137	* Insert in a table with current value line.
138	*
139	* @param string $table_name The table name.
140	* @param array $value The values to update or insert in the table.
141	* @since 1.0.0
142	*/
143	public function insert_ignore( $table_name, $value ) {
144	$field_insert = [];
145	$value_insert = [];
146	foreach ( $value as $k => $v ) {
147	$field_insert[] = '`' . $k . '`';
148	$value_insert[] = "'" . $v . "'";
149	}
150	if ( count( $field_insert ) > 0 ) {
151	global $wpdb;
152	$sql = 'INSERT IGNORE INTO `' . $wpdb->base_prefix . $table_name . '` ';
153	$sql .= '(' . implode( ',', $field_insert ) . ') ';
154	$sql .= 'VALUES (' . implode( ',', $value_insert ) . ');';
155	// phpcs:ignore
156	$wpdb->query( $sql );
157	}
158	}
159
160	/**
161	* Delete some old lines in a table.
162	*
163	* @param string $table_name The table to update.
164	* @param string $field_name The name of the field containing ids.
165	* @param integer $limit The number of lines to delete.
166	* @return int\|false The number of rows deleted, or false on error.
167	* @since 1.0.0
168	*/
169	public function rotate( $table_name, $field_name, $limit ) {
170	global $wpdb;
171	$table_name = $wpdb->base_prefix . $table_name;
172	$sql = 'DELETE FROM ' . $table_name . ' ORDER BY ' . $field_name . ' ASC LIMIT ' . $limit;
173	// phpcs:ignore
174	return $wpdb->query( $sql );
175	}
176
177	/**
178	* Delete some old lines in a table.
179	*
180	* @param string $table_name The table to update.
181	* @param string $field_name The name of the field containing timestamp.
182	* @param integer $interval The number of hours of age to delete.
183	* @return int\|false The number of rows deleted, or false on error.
184	* @since 1.0.0
185	*/
186	public function purge( $table_name, $field_name, $interval ) {
187	global $wpdb;
188	$table_name = $wpdb->base_prefix . $table_name;
189	$sql = 'DELETE FROM ' . $table_name . ' WHERE (' . $field_name . ' < NOW() - INTERVAL ' . $interval . ' HOUR);';
190	// phpcs:ignore
191	return $wpdb->query( $sql );
192	}
193
194	/**
195	* Drop a table.
196	*
197	* @param string $table_name The table to drop.
198	* @since 1.0.0
199	*/
200	protected static function drop( $table_name ) {
201	global $wpdb;
202	$sql = 'DROP TABLE IF EXISTS ' . $wpdb->base_prefix . $table_name;
203	// phpcs:ignore
204	$wpdb->query( $sql );
205	}
206
207	/**
208	* Count the number of records in a table.
209	*
210	* @param string $table_name The table to count.
211	* @return integer Count of records.
212	* @since 1.0.0
213	*/
214	public function count_lines( $table_name ) {
215	return $this->count_filtered_lines( $table_name );
216	}
217
218	/**
219	* Count the number of records in a filtered table.
220	*
221	* @param string $table_name The table to count.
222	* @param array $filters The filters to apply.
223	* @return integer Count of records.
224	* @since 3.0.0
225	*/
226	public function count_filtered_lines( $table_name, $filters = [] ) {
227	global $wpdb;
228	$result = -1;
229	if ( 0 !== strpos( $table_name, $wpdb->base_prefix ) ) {
230	$table_name = $wpdb->base_prefix . $table_name;
231	}
232	$where_clause = '';
233	if ( count( $filters ) > 0 ) {
234	$wheres = [];
235	foreach ( $filters as $key => $filter ) {
236	if ( is_array( $filter ) ) {
237	$w = [];
238	foreach ( $filter as $f ) {
239	if ( is_numeric( $f ) ) {
240	$w[] = $f;
241	} elseif ( is_string( $f ) ) {
242	$w[] = "'" . $f . "'";
243	}
244	}
245	$wheres[] = '`' . $key . '` IN (' . implode( ',', $w ) . ')';
246	} elseif ( is_numeric( $filter ) ) {
247	$wheres[] = '`' . $key . '` = ' . $filter;
248	} elseif ( is_string( $filter ) ) {
249	$wheres[] = '`' . $key . '` = \'' . $filter . '\'';
250	}
251	}
252	if ( count( $wheres ) > 0 ) {
253	$where_clause = ' WHERE ' . implode( ' AND ', $wheres );
254	}
255	}
256	$sql = 'SELECT COUNT(*) as CNT FROM `' . $table_name . '`' . $where_clause . ';';
257	// phpcs:ignore
258	$cnt = $wpdb->get_results( $sql, ARRAY_A );
259	if ( count( $cnt ) > 0 ) {
260	if ( array_key_exists( 'CNT', $cnt[0] ) ) {
261	$result = $cnt[0]['CNT'];
262	}
263	}
264	return $result;
265	}
266
267	/**
268	* Performs a safe add column.
269	*
270	* @param string $table Table name.
271	* @param string $column Column name.
272	* @param string $alter Query to perform.
273	* @return boolean True if the operation was successful, false otherwise.
274	* @since 1.0.0
275	*/
276	public function safe_add_column( $table, $column, $alter ) {
277	global $wpdb;
278	$result = false;
279	if ( ! $wpdb->get_var( "SHOW COLUMNS FROM `" . $table . "` LIKE '" . $column . "'" ) ) {
280	try {
281	// phpcs:ignore
282	$wpdb->query( $alter );
283	$result = true;
284	}
285	catch ( \Exception $ex ) {
286	$result = false;
287	}
288	}
289	return $result;
290	}
291
292	}
293