opcache-manager / includes / system / class-database.php

class-database.php in OPcache Manager trunk, at includes/system/class-database.php

255 lines 6.9 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 OPcacheManager\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->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->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->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->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->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->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->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	* @access protected
200	*/
201	protected static function drop( $table_name ) {
202	global $wpdb;
203	$sql = 'DROP TABLE IF EXISTS ' . $wpdb->prefix . $table_name;
204	// phpcs:ignore
205	$wpdb->query( $sql );
206	}
207
208	/**
209	* Count the number of records in a table.
210	*
211	* @param string $table_name The table to count.
212	* @return integer Count of records.
213	* @since 1.0.0
214	*/
215	public function count_lines( $table_name ) {
216	$result = -1;
217	global $wpdb;
218	$sql = 'SELECT COUNT(*) as CNT FROM `' . $wpdb->prefix . $table_name . '`;';
219	// phpcs:ignore
220	$cnt = $wpdb->get_results( $sql, ARRAY_A );
221	if ( count( $cnt ) > 0 ) {
222	if ( array_key_exists( 'CNT', $cnt[0] ) ) {
223	$result = $cnt[0]['CNT'];
224	}
225	}
226	return $result;
227	}
228
229	/**
230	* Performs a safe add column.
231	*
232	* @param string $table Table name.
233	* @param string $column Column name.
234	* @param string $alter Query to perform.
235	* @return boolean True if the operation was successful, false otherwise.
236	* @since 1.0.0
237	*/
238	public function safe_add_column( $table, $column, $alter ) {
239	global $wpdb;
240	$result = false;
241	if ( ! $wpdb->get_var( "SHOW COLUMNS FROM `" . $table . "` LIKE '" . $column . "'" ) ) {
242	try {
243	// phpcs:ignore
244	$wpdb->query( $alter );
245	$result = true;
246	}
247	catch ( \Exception $ex ) {
248	$result = false;
249	}
250	}
251	return $result;
252	}
253
254	}
255