db-optm.cls.php — LiteSpeed Cache 7.8.1

litespeed-cache / src / db-optm.cls.php

litespeed-cache / src Last commit date

db-optm.cls.php

492 lines

1	<?php
2	/**
3	* The admin optimize tool.
4	*
5	* @package LiteSpeed
6	* @since 1.2.1
7	*/
8
9	namespace LiteSpeed;
10
11	defined( 'WPINC' ) \|\| exit();
12
13	/**
14	* Database optimization utilities for LiteSpeed.
15	*/
16	class DB_Optm extends Root {
17
18	/**
19	* Whether there are more sites hidden in multisite counts.
20	*
21	* @var bool
22	*/
23	private static $_hide_more = false;
24
25	/**
26	* Supported cleanup types.
27	*
28	* @var string[]
29	*/
30	private static $types = [
31	'revision',
32	'orphaned_post_meta',
33	'auto_draft',
34	'trash_post',
35	'spam_comment',
36	'trash_comment',
37	'trackback-pingback',
38	'expired_transient',
39	'all_transients',
40	'optimize_tables',
41	];
42
43	/**
44	* Convert tables to InnoDB type identifier.
45	*/
46	const TYPE_CONV_TB = 'conv_innodb';
47
48	/**
49	* Show if there are more sites in hidden.
50	*
51	* @since 3.0
52	* @return bool
53	*/
54	public static function hide_more() {
55	return self::$_hide_more;
56	}
57
58	/**
59	* Clean/Optimize WP tables.
60	*
61	* @since 1.2.1
62	* @access public
63	* @param string $type The type to clean.
64	* @param bool $ignore_multisite If ignoring multisite check.
65	* @return int\|string The rows that will be affected, or '-' on unknown.
66	*/
67	public function db_count( $type, $ignore_multisite = false ) {
68	if ( 'all' === $type ) {
69	$num = 0;
70	foreach ( self::$types as $v ) {
71	$num += (int) $this->db_count( $v );
72	}
73	return $num;
74	}
75
76	if ( ! $ignore_multisite ) {
77	if ( is_multisite() && is_network_admin() ) {
78	$num = 0;
79	$blogs = Activation::get_network_ids();
80	foreach ( $blogs as $k => $blog_id ) {
81	if ( $k > 3 ) {
82	self::$_hide_more = true;
83	break;
84	}
85
86	switch_to_blog( $blog_id );
87	$num += (int) $this->db_count( $type, true );
88	restore_current_blog();
89	}
90	return $num;
91	}
92	}
93
94	global $wpdb;
95
96	switch ( $type ) {
97	case 'revision':
98	$rev_max = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_MAX );
99	$rev_age = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_AGE );
100
101	$sql_add = '';
102	if ( $rev_age ) {
103	$sql_add = $wpdb->prepare( ' AND post_modified < DATE_SUB( NOW(), INTERVAL %d DAY ) ', $rev_age );
104	}
105
106	$sql = "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_type = 'revision' $sql_add";
107	if ( ! $rev_max ) {
108	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
109	return (int) $wpdb->get_var( $sql );
110	}
111
112	// Has count limit.
113	$sql = "SELECT COUNT() - %d FROM `$wpdb->posts` WHERE post_type = 'revision' $sql_add GROUP BY post_parent HAVING COUNT() > %d";
114	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
115	$res = (array) $wpdb->get_results( $wpdb->prepare( $sql, $rev_max, $rev_max ), ARRAY_N );
116
117	Utility::compatibility();
118	return array_sum( array_column( $res, 0 ) );
119
120	case 'orphaned_post_meta':
121	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
122	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->postmeta` a LEFT JOIN `$wpdb->posts` b ON b.ID=a.post_id WHERE b.ID IS NULL" );
123
124	case 'auto_draft':
125	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
126	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_status = 'auto-draft'" );
127
128	case 'trash_post':
129	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
130	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_status = 'trash'" );
131
132	case 'spam_comment':
133	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
134	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_approved = 'spam'" );
135
136	case 'trash_comment':
137	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
138	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_approved = 'trash'" );
139
140	case 'trackback-pingback':
141	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
142	return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_type = 'trackback' OR comment_type = 'pingback'" );
143
144	case 'expired_transient':
145	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
146	return (int) $wpdb->get_var(
147	$wpdb->prepare(
148	"SELECT COUNT(*) FROM `$wpdb->options` WHERE option_name LIKE %s AND option_value < %d",
149	$wpdb->esc_like( '_transient_timeout_' ) . '%',
150	time()
151	)
152	);
153
154	case 'all_transients':
155	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
156	return (int) $wpdb->get_var(
157	$wpdb->prepare(
158	"SELECT COUNT(*) FROM `$wpdb->options` WHERE option_name LIKE %s",
159	$wpdb->esc_like( '_transient_' ) . '%'
160	)
161	);
162
163	case 'optimize_tables':
164	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
165	return (int) $wpdb->get_var(
166	$wpdb->prepare(
167	"SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA = %s AND ENGINE <> 'InnoDB' AND DATA_FREE > 0",
168	DB_NAME
169	)
170	);
171	}
172
173	return '-';
174	}
175
176	/**
177	* Clean/Optimize WP tables.
178	*
179	* @since 1.2.1
180	* @since 3.0 changed to private
181	* @access private
182	* @param string $type Cleanup type.
183	* @return string Status message.
184	*/
185	private function _db_clean( $type ) {
186	if ( 'all' === $type ) {
187	foreach ( self::$types as $v ) {
188	$this->_db_clean( $v );
189	}
190	return __( 'Clean all successfully.', 'litespeed-cache' );
191	}
192
193	global $wpdb;
194
195	switch ( $type ) {
196	case 'revision':
197	$rev_max = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_MAX );
198	$rev_age = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_AGE );
199
200	$postmeta = "`$wpdb->postmeta`";
201	$posts = "`$wpdb->posts`";
202
203	$sql_postmeta_join = function ( $table ) use ( $postmeta, $posts ) {
204	return "
205	$postmeta
206	CROSS JOIN $table
207	ON $posts.ID = $postmeta.post_id
208	";
209	};
210
211	$sql_where = "WHERE $posts.post_type = 'revision'";
212
213	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
214	$sql_add = $rev_age ? $wpdb->prepare( ' AND ' . $posts . '.post_modified < DATE_SUB( NOW(), INTERVAL %d DAY )', $rev_age ) : '';
215
216	if ( ! $rev_max ) {
217	$sql_where = "$sql_where $sql_add";
218	$sql_postmeta = $sql_postmeta_join( $posts );
219	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
220	$wpdb->query( "DELETE $postmeta FROM $sql_postmeta $sql_where" );
221	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
222	$wpdb->query( "DELETE FROM $posts $sql_where" );
223	} else {
224	// Has count limit.
225	$sql = "
226	SELECT COUNT(*) - %d
227	AS del_max, post_parent
228	FROM $posts
229	WHERE post_type = 'revision'
230	$sql_add
231	GROUP BY post_parent
232	HAVING COUNT(*) > %d
233	";
234	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
235	$res = (array) $wpdb->get_results( $wpdb->prepare( $sql, $rev_max, $rev_max ) );
236	$sql_where = "
237	$sql_where
238	AND post_parent = %d
239	ORDER BY ID
240	LIMIT %d
241	";
242	$sql_postmeta = $sql_postmeta_join( "(SELECT ID FROM $posts $sql_where) AS $posts" );
243	foreach ( $res as $v ) {
244	$args = [ (int) $v->post_parent, (int) $v->del_max ];
245	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
246	$wpdb->query( $wpdb->prepare( "DELETE $postmeta FROM $sql_postmeta", $args ) );
247	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
248	$wpdb->query( $wpdb->prepare( "DELETE FROM $posts $sql_where", $args ) );
249	}
250	}
251
252	return __( 'Clean post revisions successfully.', 'litespeed-cache' );
253
254	case 'orphaned_post_meta':
255	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
256	$wpdb->query( "DELETE a FROM `$wpdb->postmeta` a LEFT JOIN `$wpdb->posts` b ON b.ID=a.post_id WHERE b.ID IS NULL" );
257	return __( 'Clean orphaned post meta successfully.', 'litespeed-cache' );
258
259	case 'auto_draft':
260	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
261	$wpdb->query( "DELETE FROM `$wpdb->posts` WHERE post_status = 'auto-draft'" );
262	return __( 'Clean auto drafts successfully.', 'litespeed-cache' );
263
264	case 'trash_post':
265	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
266	$wpdb->query( "DELETE FROM `$wpdb->posts` WHERE post_status = 'trash'" );
267	return __( 'Clean trashed posts and pages successfully.', 'litespeed-cache' );
268
269	case 'spam_comment':
270	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
271	$wpdb->query( "DELETE FROM `$wpdb->comments` WHERE comment_approved = 'spam'" );
272	return __( 'Clean spam comments successfully.', 'litespeed-cache' );
273
274	case 'trash_comment':
275	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
276	$wpdb->query( "DELETE FROM `$wpdb->comments` WHERE comment_approved = 'trash'" );
277	return __( 'Clean trashed comments successfully.', 'litespeed-cache' );
278
279	case 'trackback-pingback':
280	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
281	$wpdb->query( "DELETE FROM `$wpdb->comments` WHERE comment_type = 'trackback' OR comment_type = 'pingback'" );
282	return __( 'Clean trackbacks and pingbacks successfully.', 'litespeed-cache' );
283
284	case 'expired_transient':
285	$keys_to_delete = [];
286	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
287	$transients = $wpdb->get_results(
288	$wpdb->prepare(
289	"SELECT option_name FROM `$wpdb->options` WHERE option_name LIKE %s AND option_value < %d",
290	$wpdb->esc_like( '_transient_timeout_' ) . '%',
291	time()
292	),
293	);
294	foreach ( $transients as $transient ) {
295	$keys_to_delete[] = $transient->option_name;
296	$keys_to_delete[] = str_replace( '_transient_timeout_', '_transient_', $transient->option_name );
297	}
298
299	if ( ! empty( $keys_to_delete ) ) {
300	$placeholders = implode( ',', array_fill( 0, count( $keys_to_delete ), '%s' ) );
301	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
302	$wpdb->query(
303	$wpdb->prepare(
304	// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
305	"DELETE FROM `$wpdb->options` WHERE option_name IN ( $placeholders )",
306	$keys_to_delete
307	)
308	);
309	}
310	return __( 'Clean expired transients successfully.', 'litespeed-cache' );
311
312	case 'all_transients':
313	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
314	$wpdb->query(
315	$wpdb->prepare(
316	"DELETE FROM `$wpdb->options` WHERE option_name LIKE %s",
317	$wpdb->esc_like( '_transient_' ) . '%'
318	)
319	);
320	return __( 'Clean all transients successfully.', 'litespeed-cache' );
321
322	case 'optimize_tables':
323	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
324	$result = (array) $wpdb->get_results(
325	$wpdb->prepare(
326	"SELECT table_name, DATA_FREE FROM information_schema.tables WHERE TABLE_SCHEMA = %s AND ENGINE <> 'InnoDB' AND DATA_FREE > 0",
327	DB_NAME
328	)
329	);
330	if ( $result ) {
331	foreach ( $result as $row ) {
332	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
333	$wpdb->query( 'OPTIMIZE TABLE ' . esc_sql( $row->table_name ) );
334	}
335	}
336	return __( 'Optimized all tables.', 'litespeed-cache' );
337	}
338	}
339
340	/**
341	* Get all MyISAM tables.
342	*
343	* @since 3.0
344	* @access public
345	* @return array
346	*/
347	public function list_myisam() {
348	global $wpdb;
349
350	$like = $wpdb->esc_like( $wpdb->prefix ) . '%';
351
352	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
353	return (array) $wpdb->get_results(
354	$wpdb->prepare(
355	"SELECT TABLE_NAME as table_name, ENGINE as engine
356	FROM information_schema.tables
357	WHERE TABLE_SCHEMA = %s AND ENGINE = 'myisam' AND TABLE_NAME LIKE %s",
358	DB_NAME,
359	$like
360	)
361	);
362	}
363
364	/**
365	* Convert tables to InnoDB.
366	*
367	* @since 3.0
368	* @access private
369	* @return void
370	*/
371	private function _conv_innodb() {
372	global $wpdb;
373
374	$tb_param = isset( $_GET['litespeed_tb'] ) ? sanitize_text_field( wp_unslash( $_GET['litespeed_tb'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification.Recommended
375
376	if ( ! $tb_param ) {
377	Admin_Display::error( 'No table to convert or invalid nonce' );
378	return;
379	}
380
381	$tb = false;
382	$list = $this->list_myisam();
383	$names = wp_list_pluck( $list, 'table_name' );
384
385	if ( in_array( $tb_param, $names, true ) ) {
386	$tb = $tb_param;
387	}
388
389	if ( ! $tb ) {
390	Admin_Display::error( 'No existing table' );
391	return;
392	}
393
394	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
395	$wpdb->query( 'ALTER TABLE ' . esc_sql( DB_NAME ) . '.' . esc_sql( $tb ) . ' ENGINE = InnoDB' );
396
397	Debug2::debug( "[DB] Converted $tb to InnoDB" );
398
399	$msg = __( 'Converted to InnoDB successfully.', 'litespeed-cache' );
400	Admin_Display::success( $msg );
401	}
402
403	/**
404	* Count all autoload size.
405	*
406	* @since 3.0
407	* @access public
408	* @return object Summary with size, entries, and toplist.
409	*/
410	public function autoload_summary() {
411	global $wpdb;
412
413	$autoload_values = function_exists( 'wp_autoload_values_to_autoload' ) ? wp_autoload_values_to_autoload() : [ 'yes', 'on', 'auto-on', 'auto' ];
414	$placeholders = implode( ',', array_fill( 0, count( $autoload_values ), '%s' ) );
415
416	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
417	$summary = $wpdb->get_row(
418	$wpdb->prepare(
419	"SELECT SUM(LENGTH(option_value)) AS autoload_size, COUNT(*) AS autload_entries
420	FROM `$wpdb->options`
421	WHERE autoload IN ($placeholders)",
422	$autoload_values
423	)
424	);
425
426	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
427	$summary->autoload_toplist = $wpdb->get_results(
428	$wpdb->prepare(
429	"SELECT option_name, LENGTH(option_value) AS option_value_length, autoload
430	FROM `$wpdb->options`
431	WHERE autoload IN ($placeholders)
432	ORDER BY option_value_length DESC
433	LIMIT 20",
434	$autoload_values
435	)
436	);
437
438	return $summary;
439	}
440
441	/**
442	* Handle all request actions from main cls.
443	*
444	* @since 3.0
445	* @access public
446	* @return void
447	*/
448	public function handler() {
449	$type = Router::verify_type();
450
451	switch ($type) {
452	case self::TYPE_CONV_TB:
453	$this->_conv_innodb();
454	break;
455
456	default:
457	if ( 'all' === $type \|\| in_array( $type, self::$types, true ) ) {
458	if ( is_multisite() && is_network_admin() ) {
459	$blogs = Activation::get_network_ids();
460	foreach ( $blogs as $blog_id ) {
461	switch_to_blog( $blog_id );
462	$msg = $this->_db_clean( $type );
463	restore_current_blog();
464	}
465	} else {
466	$msg = $this->_db_clean( $type );
467	}
468	Admin_Display::success( $msg );
469	}
470	break;
471	}
472
473	Admin::redirect();
474	}
475
476	/**
477	* Clean DB via WP-CLI.
478	*
479	* @since 7.0
480	* @access public
481	* @param string $args Cleanup type.
482	* @return string\|false
483	*/
484	public function handler_clean_db_cli( $args ) {
485	if ( defined( 'WP_CLI' ) && constant('WP_CLI') ) {
486	return $this->_db_clean( $args );
487	}
488
489	return false;
490	}
491	}
492