wp-optimize / includes / class-wp-optimize-database-information.php

class-wp-optimize-database-information.php in WP-Optimize – Cache, Compress images, Minify & Clean database to boost page speed & performance 4.6.0, at includes/class-wp-optimize-database-information.php

740 lines 19.1 KB

Raw Download Zip

1	<?php
2
3	if (!defined('ABSPATH')) die('No direct access allowed');
4
5	class WP_Optimize_Database_Information {
6
7	const UNKNOWN_DB = 'unknown';
8	const MARIA_DB = 'MariaDB';
9	const PERCONA_DB = 'Percona';
10	// for some reason coding standard parser give error here WordPress.DB.RestrictedFunctions.mysql_mysql_db
11	const MYSQL_DB = 'MysqlDB';
12
13	const MYISAM_ENGINE = 'MyISAM';
14	const MEMORY_ENGINE = 'Memory';
15	const INNODB_ENGINE = 'InnoDB';
16	const ARCHIVE_ENGINE = 'ARCHIVE';
17	const CSV_ENGINE = 'CSV';
18	const NDB_ENGINE = 'NDB';
19	const ARIA_ENGINE = 'Aria'; // MariaDB
20	const VIEW = 'VIEW';
21
22	/**
23	* Returns singleton instance object
24	*
25	* @return WP_Optimize_Database_Information Returns `WP_Optimize_Database_Information` object
26	*/
27	public static function instance() {
28	static $_instance = null;
29	if (null === $_instance) {
30	$_instance = new self();
31	}
32	return $_instance;
33	}
34
35	/**
36	* Returns server type MySQL or MariaDB if mysql database or Unknown if not mysql.
37	*
38	* @return string
39	*/
40	public function get_server_type() {
41	global $wpdb;
42	static $server_type = null;
43
44	if (!$wpdb->is_mysql) return self::UNKNOWN_DB;
45
46	if (null !== $server_type) return $server_type;
47
48	$server_type = self::MYSQL_DB;
49
50	$variables = $wpdb->get_results('SHOW SESSION VARIABLES LIKE "version%"');
51
52	if (!empty($variables)) {
53	foreach ($variables as $variable) {
54	if (preg_match('/mariadb/i', $variable->Value)) {
55	$server_type = self::MARIA_DB;
56	}
57	if (preg_match('/percona/i', $variable->Value)) {
58	$server_type = self::PERCONA_DB;
59	}
60	}
61	}
62
63	return $server_type;
64	}
65
66	/**
67	* Returns database server version
68	*
69	* @return string\|bool
70	*/
71	public function get_version() {
72	$version = $this->get_option_value('version');
73
74	if (!empty($version)) {
75	if (preg_match('/^(\d+)(\.\d+)+/', $version, $match)) {
76	return $match[0];
77	}
78	}
79
80	return false;
81	}
82
83	/**
84	* Return table type by $table_name.
85	*
86	* @param string $table_name Database table name.
87	* @return string\|boolean - returns false upon failure
88	*/
89	public function get_table_type($table_name) {
90	$table_info = $this->get_table_status($table_name);
91
92	if ($table_info) {
93	if (!$table_info->Engine && $this->is_view($table_name)) return self::VIEW;
94
95	return $table_info->Engine;
96	}
97
98	return false;
99	}
100
101	/**
102	* Returns information about database table.
103	*
104	* @param string $table_name
105	* @param bool $update if true, then force request to database and don't use cached values.
106	* @return mixed
107	*/
108	public function get_table_status($table_name, $update = false) {
109	$tables_info = $this->get_show_table_status($update, $table_name);
110
111	foreach ($tables_info as $table_info) {
112	if ($table_name === $table_info->Name) return $table_info;
113	}
114
115	return false;
116	}
117
118	/**
119	* Returns result for query SHOW TABLE STATUS.
120	*
121	* @param bool $update refresh or no cached data
122	* @return array\|object\|stdClass[]\|null
123	*/
124	public function get_show_table_status($update = false, $table_name = '') {
125	global $wpdb;
126	static $tables_info = array();
127	static $fetched_all_tables = false;
128
129	if ($fetched_all_tables && !$update) {
130	return $tables_info;
131	}
132
133	// If a table name is provided, and the whole record hasn't been fetched yet, only fetch the information for the current table.
134	// This allows for a big performance gain when using WP-CLI or doing single optimizations.
135	if ($table_name && !$fetched_all_tables) {
136	$tables_info = $wpdb->get_results($wpdb->prepare("SHOW TABLE STATUS LIKE %s", $table_name));
137	} else {
138	if ($update \|\| empty($tables_info) \|\| !is_array($tables_info) \|\| !$fetched_all_tables) {
139	$tables_info = $wpdb->get_results('SHOW TABLE STATUS');
140	$fetched_all_tables = true;
141	foreach ($tables_info as $i => $table) {
142	$rows_count = get_transient('wpo_' . $table->Name . '_count');
143	if (false === $rows_count) break;
144	$tables_info[$i]->Rows = $rows_count;
145	}
146	}
147	}
148
149	// If option innodb_file_per_table is disabled then Data_free column will have summary overhead value for all table.
150	if (!empty($tables_info)) {
151	foreach ($tables_info as $i => $table) {
152	if (self::INNODB_ENGINE == $table->Engine && !$this->is_option_enabled('innodb_file_per_table')) {
153	$tables_info[$i]->Data_free = 0;
154	}
155	}
156	}
157
158	return $tables_info;
159	}
160
161	/**
162	* Whether a table exists
163	*
164	* @param string $table_name Name of the table
165	* @param bool $use_default_prefix Whether to use default prefix
166	* @return boolean
167	*/
168	public function table_exists($table_name, $use_default_prefix = true) {
169	global $wpdb;
170	return null !== $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($use_default_prefix ? $wpdb->prefix.$table_name : $table_name)));
171	}
172
173	/**
174	* Returns result for query SHOW FULL TABLES as associative array [table_name] => table_type.
175	*
176	* @return array
177	*/
178	public function get_show_full_tables() {
179	global $wpdb;
180
181	static $tables_info = array();
182
183	if (empty($tables_info) \|\| !is_array($tables_info)) {
184	$_tables_info = $wpdb->get_results('SHOW FULL TABLES', ARRAY_N);
185
186	if (!empty($_tables_info)) {
187	foreach ($_tables_info as $row) {
188	$tables_info[$row[0]] = $row[1];
189	}
190	}
191	}
192
193	return $tables_info;
194	}
195
196	/**
197	* Checks if table is a VIEW.
198	*
199	* @param string $table_name
200	* @return bool
201	*/
202	public function is_view($table_name) {
203	$tables_info = $this->get_show_full_tables();
204
205	if (!array_key_exists($table_name, $tables_info)) return false;
206
207	return ('VIEW' === $tables_info[$table_name]);
208	}
209
210	/**
211	* Returns true if DDL supported.
212	*
213	* @return bool
214	*/
215	public function has_online_ddl() {
216	if (self::MYSQL_DB === $this->get_server_type()) {
217	if (version_compare($this->get_version(), '5.7', '>=')) {
218	return true;
219	} else {
220	return false;
221	}
222	} elseif (self::MARIA_DB === $this->get_server_type()) {
223	if (version_compare($this->get_version(), '10.0.0', '>=')) {
224	return true;
225	} else {
226	return false;
227	}
228	}
229
230	return false;
231	}
232
233	/**
234	* Returns database option variable
235	*
236	* @param string $option_name Name of database option.
237	* @return mixed\|null
238	*/
239	public function get_option_value($option_name) {
240	global $wpdb;
241	static $options = array();
242
243	if (array_key_exists($option_name, $options)) return $options[$option_name];
244
245	$option = $wpdb->get_row(
246	$wpdb->prepare('SHOW SESSION VARIABLES LIKE %s', $option_name)
247	);
248
249	if (!empty($option)) {
250	$options[$option_name] = $option->Value;
251	return $option->Value;
252	}
253
254	return null;
255	}
256
257	/**
258	* Returns true if database option $option_name
259	*
260	* @param string $option_name Name of database option name.
261	* @return bool
262	*/
263	public function is_option_enabled($option_name) {
264	$option_value = $this->get_option_value($option_name);
265
266	return 'ON' === strtoupper($option_value);
267	}
268
269	/**
270	* Returns true if table $table_name is optimizable
271	*
272	* @param string $table_name Name of database table
273	* @return bool
274	*/
275	public function is_table_optimizable($table_name) {
276	$server_type = $this->get_server_type();
277	$server_version = $this->get_version();
278	$table_type = $this->get_table_type($table_name);
279
280	// return true if table is MyISAM.
281	if (self::MYISAM_ENGINE === $table_type) return true;
282
283	// return true if table is Archive or Aria.
284	if (self::ARCHIVE_ENGINE === $table_type \|\| self::ARIA_ENGINE === $table_type) return true;
285
286	// if InnoDB then check if we can optimize.
287	if (self::INNODB_ENGINE === $table_type) {
288	// check for MysqlDB.
289	if (self::MYSQL_DB === $server_type && $this->has_online_ddl()) {
290	return true;
291	}
292
293	// check for MariaDB.
294	if (self::MARIA_DB === $server_type) {
295	// if innodb_file_per_table enabled or version not older than 10.1.1 and innodb_defragment enabled.
296	if ($this->is_option_enabled('innodb_file_per_table') \|\| (version_compare($server_version, '10.1.1', '>=') && $this->is_option_enabled('innodb_defragment'))) {
297	return true;
298	}
299	}
300	}
301
302	// otherwise return false.
303	return false;
304	}
305
306	/**
307	* Returns true if table type is supported for optimization.
308	*
309	* @param string $table_name Name of database table
310	* @return bool
311	*/
312	public function is_table_type_optimize_supported($table_name) {
313	$table_type = $this->get_table_type($table_name);
314
315	$supported_table_types = array(
316	self::MYISAM_ENGINE,
317	self::INNODB_ENGINE,
318	self::ARCHIVE_ENGINE,
319	self::ARIA_ENGINE,
320	);
321
322	return in_array($table_type, $supported_table_types);
323	}
324
325	/**
326	* Returns true if table type is supported for repair.
327	*
328	* @param string $table_name
329	* @return bool
330	*/
331	public function is_table_type_repair_supported($table_name) {
332	$table_type = $this->get_table_type($table_name);
333
334	$supported_table_types = array(
335	self::MYISAM_ENGINE,
336	self::ARCHIVE_ENGINE,
337	self::CSV_ENGINE,
338	);
339
340	return in_array($table_type, $supported_table_types);
341	}
342
343	/**
344	* Run CHECK TABLE query and returns statuses for single or list of tables.
345	*
346	* @param array\|string $table
347	* @return array
348	*/
349	public function check_table($table) {
350	global $wpdb;
351
352	if (is_array($table)) {
353	$table = join('`,`', $table);
354	}
355
356	$result = array();
357
358	if (empty($table)) return $result;
359
360	$query_result = $wpdb->get_results('CHECK TABLE `'. esc_sql($table).'`;');
361
362	if (empty($query_result)) return $result;
363
364	foreach ($query_result as $row) {
365	$table_name_parts = explode('.', rtrim($row->Table, ' .'));
366	$table_name = array_pop($table_name_parts);
367
368	if (!array_key_exists($table_name, $result)) {
369	$result[$table_name] = array(
370	'status' => '',
371	'corrupted' => false,
372	);
373	}
374
375	if ('error' === $row->Msg_type) {
376	$result[$table_name]['status'] = $row->Msg_type;
377
378	if (preg_match('/corrupt/i', $row->Msg_text)) {
379	$result[$table_name]['corrupted'] = true;
380	} else {
381	$result[$table_name]['message'] = $row->Msg_text;
382	}
383	}
384
385	if ('status' === $row->Msg_type) {
386	$result[$table_name]['status'] = $row->Msg_text;
387	}
388	}
389
390	return $result;
391	}
392
393	/**
394	* Check all supported for repair tables and return statuses for them.
395	*
396	* @return array
397	*/
398	public function check_all_tables() {
399	static $result = null;
400
401	if (null !== $result) return $result;
402
403	$tables = $this->get_show_table_status();
404	$supported_tables = array();
405
406	foreach ($tables as $table) {
407	if ('' === $table->Engine \|\| $this->is_table_type_repair_supported($table->Name)) {
408	$supported_tables[] = $table->Name;
409	}
410	}
411
412	$result = $this->check_table($supported_tables);
413
414	return $result;
415	}
416
417	/**
418	* Returns true if table needing repair.
419	*
420	* @param string $table_name Database table name.
421	*/
422	public function is_table_needing_repair($table_name) {
423	$table_statuses = $this->check_all_tables();
424
425	if (!$this->is_table_type_repair_supported($table_name)) return false;
426
427	return (array_key_exists($table_name, $table_statuses) && $table_statuses[$table_name]['corrupted']);
428	}
429
430	/**
431	* Check if any of plugins from $plugin_names is installed.
432	*
433	* @param array $plugin_names
434	* @return bool
435	*/
436	public function is_any_plugin_installed($plugin_names) {
437
438	if (empty($plugin_names)) {
439	return false;
440	}
441
442	// is WordPress core table or using by any of installed plugins then return true.
443	foreach ($plugin_names as $plugin_name) {
444	// $plugin_name is a translatable string, so we compare it with a translated string
445	if (__('WordPress core', 'wp-optimize') === $plugin_name \|\| in_array($plugin_name, $this->get_all_installed_plugins())) {
446	return true;
447	}
448	}
449
450	return false;
451	}
452
453	/**
454	* Get blog_id
455	*
456	* @param string $table_name
457	* @return int
458	*/
459	public function get_table_blog_id($table_name) {
460	global $wpdb;
461
462	if (is_multisite()) {
463	$blogs_ids = wp_list_pluck(WP_Optimize()->get_sites(), 'blog_id');
464
465	// if match with base_prefix_(number)_
466	if (preg_match('/^'.$wpdb->base_prefix.'(\d+)_/', $table_name, $match)) {
467	// check if matched number in available sites.
468	if (in_array($match[1], $blogs_ids)) return absint($match[1]);
469	}
470	}
471
472	return 1;
473	}
474
475	/**
476	* Get information about relations between tables and plugins. [ 'table' => ['plugin1', 'plugin2', ...], ... ].
477	*
478	* @return array
479	*/
480	private function get_all_plugin_tables_relationship() {
481	static $plugin_tables = array();
482
483	if (!empty($plugin_tables)) return $plugin_tables;
484
485	$wp_core_tables = array(
486	'blogs',
487	'blog_versions',
488	'commentmeta',
489	'comments',
490	'links',
491	'options',
492	'postmeta',
493	'posts',
494	'registration_log',
495	'signups',
496	'term_relationships',
497	'term_taxonomy',
498	'termmeta',
499	'terms',
500	'usermeta',
501	'users',
502	'site',
503	'sitemeta',
504	);
505
506	$plugin_tables_json_file = $this->get_plugin_json_file_path();
507	$fallback_plugin_tables_json_file = WPO_PLUGIN_MAIN_PATH.'plugin.json';
508
509	if (is_file($plugin_tables_json_file) && is_readable($plugin_tables_json_file)) {
510	// get data from plugin.json file.
511	$plugin_tables = json_decode(file_get_contents($plugin_tables_json_file), true);
512	}
513
514	// Fallback to the bundled version if the list is empty
515	if (empty($plugin_tables)) {
516	if (is_file($fallback_plugin_tables_json_file) && is_readable($fallback_plugin_tables_json_file)) {
517	// get data from the bundled plugin.json file.
518	$plugin_tables = json_decode(file_get_contents($fallback_plugin_tables_json_file), true);
519	}
520	}
521
522	foreach ($wp_core_tables as $table) {
523	$plugin_tables[$table][] = __('WordPress core', 'wp-optimize');
524	}
525
526	// add WP-Optimize tables.
527	$wpo = 'wp-optimize';
528	$plugin_tables['tm_taskmeta'] = $plugin_tables['tm_taskmeta'] ?? array();
529	$plugin_tables['tm_tasks'] = $plugin_tables['tm_tasks'] ?? array();
530
531	if (!in_array($wpo, $plugin_tables['tm_taskmeta'], true)) {
532	$plugin_tables['tm_taskmeta'][] = $wpo;
533	}
534
535	if (!in_array($wpo, $plugin_tables['tm_tasks'], true)) {
536	$plugin_tables['tm_tasks'][] = $wpo;
537	}
538
539	return $plugin_tables;
540	}
541
542	/**
543	* Try to get plugin name by table name and return it or return false if plugin is not defined.
544	*
545	* @param string $table
546	* @return array\|bool - array with plugin slugs or false.
547	*/
548	public function get_table_plugin($table) {
549	global $wpdb;
550
551	$plugins = array();
552	$original_table = $table;
553	// delete table prefix.
554	$table = preg_replace('/^'.$wpdb->prefix.'([0-9]+_)?/', '', $table);
555	$plugins_tables = $this->get_all_plugin_tables_relationship();
556
557	// If a direct match exists.
558	if (array_key_exists($table, $plugins_tables)) {
559	$plugins = $plugins_tables[$table];
560	}
561
562	// if has pro_quiz in name then try to match with learndash tables.
563	if (false !== stripos($original_table, 'pro_quiz')) {
564	$match_learndash_tables_plugin = $this->match_learndash_tables_plugin($original_table);
565
566	if (!empty($match_learndash_tables_plugin)) {
567	$plugins = array_merge($plugins, $match_learndash_tables_plugin);
568	}
569	}
570
571	if (!empty($plugins)) {
572	return array_unique($plugins);
573	}
574
575	return false;
576	}
577
578	/**
579	* Get the path where the updated plugin.json is stored
580	*
581	* @return string
582	*/
583	private function get_plugin_json_file_path() {
584	$uploads_dir = wp_upload_dir(null, false);
585	$filtered_path = apply_filters('wpo_get_plugin_json_file_path', trailingslashit($uploads_dir['basedir']).'wpo/wpo-plugins-tables-list.json');
586	return is_string($filtered_path) ? $filtered_path : '';
587	}
588
589	/**
590	* Get all installed plugin slugs.
591	*
592	* @return array
593	*/
594	public function get_all_installed_plugins() {
595	static $installed_plugins;
596
597	if (is_array($installed_plugins)) return $installed_plugins;
598
599	$installed_plugins = array();
600
601	if (!function_exists('get_plugins')) include_once(ABSPATH.'wp-admin/includes/plugin.php');
602
603	$plugins = get_plugins();
604
605	foreach ($plugins as $plugin_file => $plugin_data) {
606	if ('' !== $plugin_data['TextDomain']) {
607	$installed_plugins[] = $plugin_data['TextDomain'];
608	} else {
609	$plugin_file_parts = explode('/', $plugin_file);
610	$installed_plugins[]= $plugin_file_parts[0];
611	}
612	}
613
614	return $installed_plugins;
615	}
616
617	/**
618	* Check current plugin status installed/not installed and active/inactive.
619	*
620	* @param string $plugin
621	* @return array - ['installed' => true\|false, 'active' => true\|false]
622	*/
623	public function get_plugin_status($plugin) {
624
625	if (!function_exists('get_plugins')) include_once(ABSPATH.'wp-admin/includes/plugin.php');
626	$plugins = get_plugins();
627
628	// return true for wp-optimize without checking.
629	if ('wp-optimize' === $plugin) {
630	return array(
631	'installed' => true,
632	'active' => true,
633	);
634	}
635
636	$installed = false;
637	$active = false;
638
639	foreach ($plugins as $plugin_file => $plugin_data) {
640	$plugin_file_parts = explode('/', $plugin_file);
641	$plugin_slug = $plugin_file_parts[0] ?? '';
642
643	if ($plugin === $plugin_slug) {
644	$installed = true;
645	$active = is_plugin_active($plugin_file);
646	}
647	}
648
649	return array(
650	'installed' => $installed,
651	'active' => $active,
652	);
653	}
654
655	/**
656	* Update list in plugin.json, if necessary
657	*
658	* @return void
659	*/
660	public function update_plugin_json() {
661	// Add the possibility to turn this off.
662	if (!apply_filters('wpo_update_plugin_json', true)) return;
663
664	$update_request = wp_remote_get('https://plugins.svn.wordpress.org/wp-optimize/trunk/plugin.json', array('timeout' => 3000));
665	if (200 !== wp_remote_retrieve_response_code($update_request)) return;
666	$json_content = wp_remote_retrieve_body($update_request);
667	if (json_decode($json_content)) {
668	// phpcs:disable
669	// WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents -- WP_Filesystem not available this early
670	// Generic.PHP.NoSilencedErrors.Discouraged -- suppress PHP warning in case of failure
671	if (false === @file_put_contents($this->get_plugin_json_file_path(), $json_content)) {
672	error_log("WP-Optimize: wpo-plugins-tables-list.json couldn't be updated");
673	return;
674	}
675	// phpcs:enable
676	$this->change_plugin_json_permissions();
677	}
678	}
679
680	/**
681	* Set permissions to 640 for plugin.json
682	*
683	* @return void
684	*/
685	public function change_plugin_json_permissions() {
686	$plugin_json_file_path = $this->get_plugin_json_file_path();
687	// phpcs:disable
688	// Generic.PHP.NoSilencedErrors.Discouraged -- suppress PHP warning in case of failure
689	if (is_file($plugin_json_file_path)) {
690	@chmod($plugin_json_file_path, 0640);
691	}
692	// phpcs:enable
693	}
694
695	/**
696	* Cache all table rows count
697	*/
698	public function wpo_update_record_count() {
699	global $wpdb;
700	$tables_info = $wpdb->get_results('SHOW TABLE STATUS');
701	foreach ($tables_info as $table) {
702	$rows_count = $wpdb->get_var("SELECT COUNT(*) FROM `" . esc_sql($table->Name) . "`");
703	set_transient('wpo_' . $table->Name . '_count', $rows_count, 246060);
704	}
705	}
706
707	/**
708	* Check if the give table name belongs to a LearnDash plugin table and return the plugin slug.
709	*
710	* @param string $table_name The original table name with prefix to check if it belongs to LearnDash plugin.
711	* @return array
712	*/
713	private function match_learndash_tables_plugin($table_name) {
714
715	$table_names = array(
716	'pro_quiz_category',
717	'pro_quiz_form',
718	'pro_quiz_lock',
719	'pro_quiz_prerequisite',
720	'pro_quiz_question',
721	'pro_quiz_statistic',
722	'pro_quiz_statistic_ref',
723	'pro_quiz_template',
724	'pro_quiz_toplist',
725	);
726
727	$learndash_slug = 'sfwd-lms';
728
729	foreach ($table_names as $learndash_table) {
730	$table_suffix = substr($table_name, -strlen($learndash_table));
731
732	if ($table_suffix === $learndash_table) {
733	return array($learndash_slug);
734	}
735	}
736
737	return array();
738	}
739	}
740