opcache-manager / includes / features / class-schema.php

class-schema.php in OPcache Manager 3.3.0, at includes/features/class-schema.php

379 lines 14.0 KB

Raw Download Zip

1	<?php
2	/**
3	* OPcache Manager schema
4	*
5	* Handles all schema operations.
6	*
7	* @package Features
8	* @author Pierre Lannoy <https://pierre.lannoy.fr/>.
9	* @since 1.0.0
10	*/
11
12	namespace OPcacheManager\Plugin\Feature;
13
14	use OPcacheManager\System\OPcache;
15	use OPcacheManager\System\Option;
16	use OPcacheManager\System\Database;
17	use OPcacheManager\System\Cache;
18
19	/**
20	* Define the schema functionality.
21	*
22	* Handles all schema operations.
23	*
24	* @package Features
25	* @author Pierre Lannoy <https://pierre.lannoy.fr/>.
26	* @since 1.0.0
27	*/
28	class Schema {
29
30	/**
31	* Statistics table name.
32	*
33	* @since 1.0.0
34	* @var string $statistics The statistics table name.
35	*/
36	private static $statistics = OPCM_PRODUCT_ABBREVIATION . '_statistics';
37
38	/**
39	* Initialize the class and set its properties.
40	*
41	* @since 1.0.0
42	*/
43	public function __construct() {
44	}
45
46	/**
47	* Effectively write a record in the database.
48	*
49	* @param array $record The record to write.
50	* @since 1.0.0
51	**/
52	public function write_statistics_record_to_database( $record ) {
53	$field_insert = [];
54	$value_insert = [];
55	$value_update = [];
56	foreach ( $record as $k => $v ) {
57	$field_insert[] = '`' . $k . '`';
58	$value_insert[] = "'" . $v . "'";
59	$value_update[] = '`' . $k . '`=' . "'" . $v . "'";
60	}
61	if ( count( $field_insert ) > 0 ) {
62	global $wpdb;
63	$sql = 'INSERT INTO `' . $wpdb->base_prefix . self::$statistics . '` ';
64	$sql .= '(' . implode( ',', $field_insert ) . ') ';
65	$sql .= 'VALUES (' . implode( ',', $value_insert ) . ') ';
66	$sql .= 'ON DUPLICATE KEY UPDATE ' . implode( ',', $value_update ) . ';';
67	// phpcs:ignore
68	$wpdb->query( $sql );
69	}
70	$this->purge();
71	}
72
73	/**
74	* Initialize the schema.
75	*
76	* @since 1.0.0
77	*/
78	public function initialize() {
79	global $wpdb;
80	try {
81	$this->create_table();
82	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( sprintf( 'Table "%s" created.', $wpdb->base_prefix . self::$statistics ) );
83	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->info( 'Schema installed.' );
84	} catch ( \Throwable $e ) {
85	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->alert( sprintf( 'Unable to create "%s" table: %s', $wpdb->base_prefix . self::$statistics, $e->getMessage() ), [ 'code' => $e->getCode() ] );
86	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->alert( 'Schema not installed.', [ 'code' => $e->getCode() ] );
87	}
88	}
89
90	/**
91	* Finalize the schema.
92	*
93	* @since 1.0.0
94	*/
95	public function finalize() {
96	global $wpdb;
97	$sql = 'DROP TABLE IF EXISTS ' . $wpdb->base_prefix . self::$statistics;
98	// phpcs:ignore
99	$wpdb->query( $sql );
100	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( sprintf( 'Table "%s" removed.', $wpdb->base_prefix . self::$statistics ) );
101	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( 'Schema destroyed.' );
102	}
103
104	/**
105	* Update the schema.
106	*
107	* @since 1.0.0
108	*/
109	public function update() {
110	global $wpdb;
111	try {
112	$this->create_table();
113	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( sprintf( 'Table "%s" updated.', $wpdb->base_prefix . self::$statistics ) );
114	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->info( 'Schema updated.' );
115	} catch ( \Throwable $e ) {
116	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->alert( sprintf( 'Unable to update "%s" table: %s', $wpdb->base_prefix . self::$statistics, $e->getMessage() ), [ 'code' => $e->getCode() ] );
117	}
118	}
119
120	/**
121	* Purge old records.
122	*
123	* @since 1.0.0
124	*/
125	private function purge() {
126	$days = (int) Option::network_get( 'history' );
127	if ( ! is_numeric( $days ) \|\| 21 > $days ) {
128	$days = 21;
129	Option::network_set( 'history', $days );
130	}
131	$database = new Database();
132	$count = $database->purge( self::$statistics, 'timestamp', 24 * $days );
133	if ( 0 === $count ) {
134	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( 'No old records to delete.' );
135	} elseif ( 1 === $count ) {
136	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( '1 old record deleted.' );
137	Cache::delete_global( 'data/oldestdate' );
138	} else {
139	\DecaLog\Engine::eventsLogger( OPCM_SLUG )->debug( sprintf( '%1$s old records deleted.', $count ) );
140	Cache::delete_global( 'data/oldestdate' );
141	}
142
143	}
144
145	/**
146	* Create the table.
147	*
148	* @since 1.0.0
149	*/
150	private function create_table() {
151	global $wpdb;
152	$charset_collate = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci';
153	$sql = 'CREATE TABLE IF NOT EXISTS ' . $wpdb->base_prefix . self::$statistics;
154	$sql .= " (`timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',";
155	$sql .= " `status` enum('" . implode( "','", OPcache::$status ) . "') NOT NULL DEFAULT 'disabled',";
156	$sql .= " `reset` enum('" . implode( "','", OPcache::$resets ) . "') NOT NULL DEFAULT 'none',";
157	$sql .= " `mem_total` int(11) UNSIGNED NOT NULL DEFAULT '0',";
158	$sql .= " `mem_used` int(11) UNSIGNED NOT NULL DEFAULT '0',";
159	$sql .= " `mem_wasted` int(11) UNSIGNED NOT NULL DEFAULT '0',";
160	$sql .= " `key_total` int(11) UNSIGNED NOT NULL DEFAULT '0',";
161	$sql .= " `key_used` int(11) UNSIGNED NOT NULL DEFAULT '0',";
162	$sql .= " `buf_total` int(11) UNSIGNED NOT NULL DEFAULT '0',";
163	$sql .= " `buf_used` int(11) UNSIGNED NOT NULL DEFAULT '0',";
164	$sql .= " `hit` int(11) UNSIGNED NOT NULL DEFAULT '0',";
165	$sql .= " `miss` int(11) UNSIGNED NOT NULL DEFAULT '0',";
166	$sql .= " `strings` int(11) UNSIGNED NOT NULL DEFAULT '0',";
167	$sql .= " `scripts` int(11) UNSIGNED NOT NULL DEFAULT '0',";
168	$sql .= " PRIMARY KEY (`timestamp`)";
169	$sql .= ") $charset_collate;";
170	// phpcs:ignore
171	$wpdb->query( $sql );
172	}
173
174	/**
175	* Get an empty record.
176	*
177	* @return array An empty, ready to use, record.
178	* @since 1.0.0
179	*/
180	public function init_record() {
181	$datetime = new \DateTime();
182	$record = [
183	'timestamp' => $datetime->format( 'Y-m-d H:i:s' ),
184	'status' => 'disabled',
185	'reset' => 'none',
186	'mem_total' => 0,
187	'mem_used' => 0,
188	'mem_wasted' => 0,
189	'key_used' => 0,
190	'buf_total' => 0,
191	'buf_used' => 0,
192	'hit' => 0,
193	'miss' => 0,
194	'strings' => 0,
195	'scripts' => 0,
196	];
197	return $record;
198	}
199
200	/**
201	* Get "where" clause of a query.
202	*
203	* @param array $filters Optional. An array of filters.
204	* @return string The "where" clause.
205	* @since 1.0.0
206	*/
207	private static function get_where_clause( $filters = [] ) {
208	$result = '';
209	if ( 0 < count( $filters ) ) {
210	$w = [];
211	foreach ( $filters as $key => $filter ) {
212	if ( is_array( $filter ) ) {
213	$w[] = '`' . $key . '` IN (' . implode( ',', $filter ) . ')';
214	} else {
215	$w[] = '`' . $key . '`="' . $filter . '"';
216	}
217	}
218	$result = 'WHERE (' . implode( ' AND ', $w ) . ')';
219	}
220	return $result;
221	}
222
223	/**
224	* Get the oldest date.
225	*
226	* @return string The oldest timestamp in the statistics table.
227	* @since 1.0.0
228	*/
229	public static function get_oldest_date() {
230	$result = Cache::get_global( 'data/oldestdate' );
231	if ( $result ) {
232	return $result;
233	}
234	global $wpdb;
235	$sql = 'SELECT * FROM ' . $wpdb->base_prefix . self::$statistics . ' ORDER BY `timestamp` ASC LIMIT 1';
236	// phpcs:ignore
237	$result = $wpdb->get_results( $sql, ARRAY_A );
238	if ( is_array( $result ) && 0 < count( $result ) && array_key_exists( 'timestamp', $result[0] ) ) {
239	Cache::set_global( 'data/oldestdate', $result[0]['timestamp'], 'infinite' );
240	return $result[0]['timestamp'];
241	}
242	return '';
243	}
244
245	/**
246	* Get the standard KPIs.
247	*
248	* @param array $filter The filter of the query.
249	* @param boolean $cache Has the query to be cached.
250	* @param string $extra_field Optional. The extra field to filter.
251	* @param array $extras Optional. The extra values to match.
252	* @param boolean $not Optional. Exclude extra filter.
253	* @return array The standard KPIs.
254	* @since 1.0.0
255	*/
256	public static function get_std_kpi( $filter, $cache = true, $extra_field = '', $extras = [], $not = false ) {
257	// phpcs:ignore
258	$id = Cache::id( __FUNCTION__ . serialize( $filter ) . $extra_field . serialize( $extras ) . ( $not ? 'no' : 'yes') );
259	$result = Cache::get_global( $id );
260	if ( $result ) {
261	return $result;
262	}
263	$where_extra = '';
264	if ( 0 < count( $extras ) && '' !== $extra_field ) {
265	$where_extra = ' AND ' . $extra_field . ( $not ? ' NOT' : '' ) . " IN ( '" . implode( "', '", $extras ) . "' )";
266	}
267	global $wpdb;
268	$sql = 'SELECT count(*) as records, sum(hit) as sum_hit, avg(hit) as avg_hit, avg(miss) as avg_miss, avg(mem_total) as avg_mem_total, avg(mem_used) as avg_mem_used, avg(mem_wasted) as avg_mem_wasted, avg(key_total) as avg_key_total, avg(key_used) as avg_key_used, avg(buf_total) as avg_buf_total, avg(buf_used) as avg_buf_used, avg(strings) as avg_strings, min(strings) as min_strings, max(strings) as max_strings, avg(scripts) as avg_scripts, min(scripts) as min_scripts, max(scripts) as max_scripts FROM ' . $wpdb->base_prefix . self::$statistics . ' WHERE (' . implode( ' AND ', $filter ) . ') ' . $where_extra;
269	// phpcs:ignore
270	$result = $wpdb->get_results( $sql, ARRAY_A );
271	if ( is_array( $result ) && 1 === count( $result ) ) {
272	Cache::set_global( $id, $result[0], $cache ? 'infinite' : 'ephemeral' );
273	return $result[0];
274	}
275	return [];
276	}
277
278	/**
279	* Get a time series.
280	*
281	* @param array $filter The filter of the query.
282	* @param boolean $cache Has the query to be cached.
283	* @param string $extra_field Optional. The extra field to filter.
284	* @param array $extras Optional. The extra values to match.
285	* @param boolean $not Optional. Exclude extra filter.
286	* @param integer $limit Optional. The number of results to return.
287	* @return array The time series.
288	* @since 1.0.0
289	*/
290	public static function get_time_series( $filter, $cache = true, $extra_field = '', $extras = [], $not = false, $limit = 0 ) {
291	$data = self::get_list( $filter, $cache, $extra_field, $extras, $not, 'ORDER BY timestamp ASC', $limit );
292	$result = [];
293	foreach ( $data as $datum ) {
294	$result[ $datum['timestamp'] ] = $datum;
295	}
296	return $result;
297	}
298
299	/**
300	* Get the standard KPIs.
301	*
302	* @param array $filter The filter of the query.
303	* @param boolean $cache Has the query to be cached.
304	* @param string $extra_field Optional. The extra field to filter.
305	* @param array $extras Optional. The extra values to match.
306	* @param boolean $not Optional. Exclude extra filter.
307	* @param string $order Optional. The sort order of results.
308	* @param integer $limit Optional. The number of results to return.
309	* @return array The standard KPIs.
310	* @since 1.0.0
311	*/
312	public static function get_list( $filter, $cache = true, $extra_field = '', $extras = [], $not = false, $order = '', $limit = 0 ) {
313	// phpcs:ignore
314	$id = Cache::id( __FUNCTION__ . serialize( $filter ) . $extra_field . serialize( $extras ) . ( $not ? 'no' : 'yes') . $order . (string) $limit);
315	$result = Cache::get_global( $id );
316	if ( $result ) {
317	return $result;
318	}
319	$where_extra = '';
320	if ( 0 < count( $extras ) && '' !== $extra_field ) {
321	$where_extra = ' AND ' . $extra_field . ( $not ? ' NOT' : '' ) . " IN ( '" . implode( "', '", $extras ) . "' )";
322	}
323	global $wpdb;
324	$sql = 'SELECT * FROM ' . $wpdb->base_prefix . self::$statistics . ' WHERE (' . implode( ' AND ', $filter ) . ') ' . $where_extra . ' ' . $order . ( $limit > 0 ? 'LIMIT ' . $limit : '' ) . ';';
325	// phpcs:ignore
326	$result = $wpdb->get_results( $sql, ARRAY_A );
327	if ( is_array( $result ) && 0 < count( $result ) ) {
328	Cache::set_global( $id, $result, $cache ? 'infinite' : 'ephemeral' );
329	return $result;
330	}
331	return [];
332	}
333
334	/**
335	* Get the standard KPIs.
336	*
337	* @param string $group The group of the query.
338	* @param array $count The sub-groups of the query.
339	* @param array $filter The filter of the query.
340	* @param boolean $cache Has the query to be cached.
341	* @param string $extra_field Optional. The extra field to filter.
342	* @param array $extras Optional. The extra values to match.
343	* @param boolean $not Optional. Exclude extra filter.
344	* @param string $order Optional. The sort order of results.
345	* @param integer $limit Optional. The number of results to return.
346	* @return array The standard KPIs.
347	* @since 1.0.0
348	*/
349	public static function get_grouped_list( $group, $count, $filter, $cache = true, $extra_field = '', $extras = [], $not = false, $order = '', $limit = 0 ) {
350	// phpcs:ignore
351	$id = Cache::id( __FUNCTION__ . $group . serialize( $count ) . serialize( $filter ) . $extra_field . serialize( $extras ) . ( $not ? 'no' : 'yes') . $order . (string) $limit);
352	$result = Cache::get_global( $id );
353	if ( $result ) {
354	return $result;
355	}
356	$where_extra = '';
357	if ( 0 < count( $extras ) && '' !== $extra_field ) {
358	$where_extra = ' AND ' . $extra_field . ( $not ? ' NOT' : '' ) . " IN ( '" . implode( "', '", $extras ) . "' )";
359	}
360	$cnt = [];
361	foreach ( $count as $c ) {
362	$cnt[] = 'count(distinct(' . $c . ')) as cnt_' . $c;
363	}
364	$c = implode( ', ', $cnt );
365	if ( 0 < strlen( $c ) ) {
366	$c = $c . ', ';
367	}
368	global $wpdb;
369	$sql = 'SELECT , ' . ( '' !== $group ? $group . ', ' : '' ) . $c . 'count() as records, sum(hit) as sum_hit, avg(hit) as avg_hit, avg(miss) as avg_miss, avg(mem_total) as avg_mem_total, avg(mem_used) as avg_mem_used, avg(mem_wasted) as avg_mem_wasted, avg(key_total) as avg_key_total, avg(key_used) as avg_key_used, avg(buf_total) as avg_buf_total, avg(buf_used) as avg_buf_used, avg(strings) as avg_strings, min(strings) as min_strings, max(strings) as max_strings, avg(scripts) as avg_scripts, min(scripts) as min_scripts, max(scripts) as max_scripts FROM ';
370	$sql .= $wpdb->base_prefix . self::$statistics . ' WHERE (' . implode( ' AND ', $filter ) . ') ' . $where_extra . ' GROUP BY ' . $group . ' ' . $order . ( $limit > 0 ? 'LIMIT ' . $limit : '') .';';
371	// phpcs:ignore
372	$result = $wpdb->get_results( $sql, ARRAY_A );
373	if ( is_array( $result ) && 0 < count( $result ) ) {
374	Cache::set_global( $id, $result, $cache ? 'infinite' : 'ephemeral' );
375	return $result;
376	}
377	return [];
378	}
379	}