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 3.2.7, at includes/class-wp-optimize-database-information.php

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