JezK

Edit File: db-optm.cls.php

<?php
/**
 * The admin optimize tool.
 *
 * @package LiteSpeed
 * @since 1.2.1
 */

namespace LiteSpeed;

defined( 'WPINC' ) || exit();

/**
 * Database optimization utilities for LiteSpeed.
 */
class DB_Optm extends Root {

/**
	 * Whether there are more sites hidden in multisite counts.
	 *
	 * @var bool
	 */
	private static $_hide_more = false;

/**
	 * Supported cleanup types.
	 *
	 * @var string[]
	 */
	private static $types = [
		'revision',
		'orphaned_post_meta',
		'auto_draft',
		'trash_post',
		'spam_comment',
		'trash_comment',
		'trackback-pingback',
		'expired_transient',
		'all_transients',
		'optimize_tables',
	];

/**
	 * Convert tables to InnoDB type identifier.
	 */
	const TYPE_CONV_TB = 'conv_innodb';

/**
	 * Show if there are more sites in hidden.
	 *
	 * @since 3.0
	 * @return bool
	 */
	public static function hide_more() {
		return self::$_hide_more;
	}

/**
	 * Clean/Optimize WP tables.
	 *
	 * @since 1.2.1
	 * @access public
	 * @param string $type             The type to clean.
	 * @param bool   $ignore_multisite If ignoring multisite check.
	 * @return int|string The rows that will be affected, or '-' on unknown.
	 */
	public function db_count( $type, $ignore_multisite = false ) {
		if ( 'all' === $type ) {
			$num = 0;
			foreach ( self::$types as $v ) {
				$num += (int) $this->db_count( $v );
			}
			return $num;
		}

if ( ! $ignore_multisite ) {
			if ( is_multisite() && is_network_admin() ) {
				$num   = 0;
				$blogs = Activation::get_network_ids();
				foreach ( $blogs as $k => $blog_id ) {
					if ( $k > 3 ) {
						self::$_hide_more = true;
						break;
					}

switch_to_blog( $blog_id );
					$num += (int) $this->db_count( $type, true );
					restore_current_blog();
				}
				return $num;
			}
		}

global $wpdb;

switch ( $type ) {
			case 'revision':
            $rev_max = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_MAX );
            $rev_age = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_AGE );

$sql_add = '';
            if ( $rev_age ) {
					$sql_add = $wpdb->prepare( ' AND post_modified < DATE_SUB( NOW(), INTERVAL %d DAY ) ', $rev_age );
				}

$sql = "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_type = 'revision' $sql_add";
            if ( ! $rev_max ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
					return (int) $wpdb->get_var( $sql );
				}

// Has count limit.
            $sql = "SELECT COUNT(*) - %d FROM `$wpdb->posts` WHERE post_type = 'revision' $sql_add GROUP BY post_parent HAVING COUNT(*) > %d";
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
            $res = (array) $wpdb->get_results( $wpdb->prepare( $sql, $rev_max, $rev_max ), ARRAY_N );

Utility::compatibility();
				return array_sum( array_column( $res, 0 ) );

case 'orphaned_post_meta':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				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" );

case 'auto_draft':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_status = 'auto-draft'" );

case 'trash_post':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->posts` WHERE post_status = 'trash'" );

case 'spam_comment':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_approved = 'spam'" );

case 'trash_comment':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_approved = 'trash'" );

case 'trackback-pingback':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$wpdb->comments` WHERE comment_type = 'trackback' OR comment_type = 'pingback'" );

case 'expired_transient':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT COUNT(*) FROM `$wpdb->options` WHERE option_name LIKE %s AND option_value < %d",
						$wpdb->esc_like( '_transient_timeout_' ) . '%',
						time()
					)
				);

case 'all_transients':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT COUNT(*) FROM `$wpdb->options` WHERE option_name LIKE %s",
						$wpdb->esc_like( '_transient_' ) . '%'
					)
				);

case 'optimize_tables':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				return (int) $wpdb->get_var(
					$wpdb->prepare(
						"SELECT COUNT(*) FROM information_schema.tables WHERE TABLE_SCHEMA = %s AND ENGINE <> 'InnoDB' AND DATA_FREE > 0",
						DB_NAME
					)
				);
		}

return '-';
	}

/**
	 * Clean/Optimize WP tables.
	 *
	 * @since 1.2.1
	 * @since 3.0 changed to private
	 * @access private
	 * @param string $type Cleanup type.
	 * @return string Status message.
	 */
	private function _db_clean( $type ) {
		if ( 'all' === $type ) {
			foreach ( self::$types as $v ) {
				$this->_db_clean( $v );
			}
			return __( 'Clean all successfully.', 'litespeed-cache' );
		}

global $wpdb;

switch ( $type ) {
			case 'revision':
            $rev_max = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_MAX );
            $rev_age = (int) $this->conf( Base::O_DB_OPTM_REVISIONS_AGE );

$postmeta = "`$wpdb->postmeta`";
            $posts    = "`$wpdb->posts`";

$sql_postmeta_join = function ( $table ) use ( $postmeta, $posts ) {
					return "
						$postmeta
						CROSS JOIN $table
						ON $posts.ID = $postmeta.post_id
					";
				};

$sql_where = "WHERE $posts.post_type = 'revision'";

// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$sql_add = $rev_age ? $wpdb->prepare( ' AND ' . $posts . '.post_modified < DATE_SUB( NOW(), INTERVAL %d DAY )', $rev_age ) : '';

if ( ! $rev_max ) {
					$sql_where    = "$sql_where $sql_add";
					$sql_postmeta = $sql_postmeta_join( $posts );
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$wpdb->query( "DELETE $postmeta FROM $sql_postmeta $sql_where" );
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$wpdb->query( "DELETE FROM $posts $sql_where" );
				} else {
					// Has count limit.
					$sql = "
						SELECT COUNT(*) - %d
						AS del_max, post_parent
						FROM $posts
						WHERE post_type = 'revision'
						$sql_add
						GROUP BY post_parent
						HAVING COUNT(*) > %d
					";
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
					$res          = (array) $wpdb->get_results( $wpdb->prepare( $sql, $rev_max, $rev_max ) );
					$sql_where    = "
						$sql_where
						AND post_parent = %d
						ORDER BY ID
						LIMIT %d
					";
					$sql_postmeta = $sql_postmeta_join( "(SELECT ID FROM $posts $sql_where) AS $posts" );
					foreach ( $res as $v ) {
						$args = [ (int) $v->post_parent, (int) $v->del_max ];
						// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
						$wpdb->query( $wpdb->prepare( "DELETE $postmeta FROM $sql_postmeta", $args ) );
						// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
						$wpdb->query( $wpdb->prepare( "DELETE FROM $posts $sql_where", $args ) );
					}
				}

return __( 'Clean post revisions successfully.', 'litespeed-cache' );

case 'orphaned_post_meta':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->query( "DELETE a FROM `$wpdb->postmeta` a LEFT JOIN `$wpdb->posts` b ON b.ID=a.post_id WHERE b.ID IS NULL" );
				return __( 'Clean orphaned post meta successfully.', 'litespeed-cache' );

case 'auto_draft':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->query( "DELETE FROM `$wpdb->posts` WHERE post_status = 'auto-draft'" );
				return __( 'Clean auto drafts successfully.', 'litespeed-cache' );

case 'trash_post':
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query( "DELETE FROM `$wpdb->posts` WHERE post_status = 'trash'" );
				return __( 'Clean trashed posts and pages successfully.', 'litespeed-cache' );

case 'spam_comment':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->query( "DELETE FROM `$wpdb->comments` WHERE comment_approved = 'spam'" );
				return __( 'Clean spam comments successfully.', 'litespeed-cache' );

case 'trash_comment':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->query( "DELETE FROM `$wpdb->comments` WHERE comment_approved = 'trash'" );
				return __( 'Clean trashed comments successfully.', 'litespeed-cache' );

case 'trackback-pingback':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->query( "DELETE FROM `$wpdb->comments` WHERE comment_type = 'trackback' OR comment_type = 'pingback'" );
				return __( 'Clean trackbacks and pingbacks successfully.', 'litespeed-cache' );

case 'expired_transient':
			$keys_to_delete = [];
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$transients = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT option_name FROM `$wpdb->options` WHERE option_name LIKE %s AND option_value < %d",
					$wpdb->esc_like( '_transient_timeout_' ) . '%',
					time()
				),
			);
			foreach ( $transients as $transient ) {
				$keys_to_delete[] = $transient->option_name;
				$keys_to_delete[] = str_replace( '_transient_timeout_', '_transient_', $transient->option_name );
			}

if ( ! empty( $keys_to_delete ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $keys_to_delete ), '%s' )  );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$wpdb->query(
					$wpdb->prepare(
						// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						"DELETE FROM `$wpdb->options` WHERE option_name IN ( $placeholders )",
						$keys_to_delete
					)
				);
			}
				return __( 'Clean expired transients successfully.', 'litespeed-cache' );

case 'all_transients':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->query(
				$wpdb->prepare(
					"DELETE FROM `$wpdb->options` WHERE option_name LIKE %s",
					$wpdb->esc_like( '_transient_' ) . '%'
				)
			);
				return __( 'Clean all transients successfully.', 'litespeed-cache' );

case 'optimize_tables':
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$result = (array) $wpdb->get_results(
				$wpdb->prepare(
					"SELECT table_name, DATA_FREE FROM information_schema.tables WHERE TABLE_SCHEMA = %s AND ENGINE <> 'InnoDB' AND DATA_FREE > 0",
					DB_NAME
				)
			);
			if ( $result ) {
				foreach ( $result as $row ) {
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$wpdb->query( 'OPTIMIZE TABLE ' . esc_sql( $row->table_name ) );
				}
			}
				return __( 'Optimized all tables.', 'litespeed-cache' );
		}
	}

/**
	 * Get all MyISAM tables.
	 *
	 * @since 3.0
	 * @access public
	 * @return array
	 */
	public function list_myisam() {
		global $wpdb;

$like = $wpdb->esc_like( $wpdb->prefix ) . '%';

// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		return (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT TABLE_NAME as table_name, ENGINE as engine
				 FROM information_schema.tables
				 WHERE TABLE_SCHEMA = %s AND ENGINE = 'myisam' AND TABLE_NAME LIKE %s",
				DB_NAME,
				$like
			)
		);
	}

/**
	 * Convert tables to InnoDB.
	 *
	 * @since 3.0
	 * @access private
	 * @return void
	 */
	private function _conv_innodb() {
		global $wpdb;

$tb_param = isset( $_GET['litespeed_tb'] ) ? sanitize_text_field( wp_unslash( $_GET['litespeed_tb'] ) ) : ''; // phpcs:ignore WordPress.Security.NonceVerification.Recommended

if ( ! $tb_param ) {
			Admin_Display::error( 'No table to convert or invalid nonce' );
			return;
		}

$tb    = false;
		$list  = $this->list_myisam();
		$names = wp_list_pluck( $list, 'table_name' );

if ( in_array( $tb_param, $names, true ) ) {
			$tb = $tb_param;
		}

if ( ! $tb ) {
			Admin_Display::error( 'No existing table' );
			return;
		}

// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$wpdb->query( 'ALTER TABLE ' . esc_sql( DB_NAME ) . '.' . esc_sql( $tb ) . ' ENGINE = InnoDB' );

Debug2::debug( "[DB] Converted $tb to InnoDB" );

$msg = __( 'Converted to InnoDB successfully.', 'litespeed-cache' );
		Admin_Display::success( $msg );
	}

/**
	 * Count all autoload size.
	 *
	 * @since 3.0
	 * @access public
	 * @return object Summary with size, entries, and toplist.
	 */
	public function autoload_summary() {
		global $wpdb;

$autoload_values = function_exists( 'wp_autoload_values_to_autoload' ) ? wp_autoload_values_to_autoload() : [ 'yes', 'on', 'auto-on', 'auto' ];
		$placeholders    = implode( ',', array_fill( 0, count( $autoload_values ), '%s' ) );

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$summary = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT SUM(LENGTH(option_value)) AS autoload_size, COUNT(*) AS autload_entries
				 FROM `$wpdb->options`
				 WHERE autoload IN ($placeholders)",
				$autoload_values
			)
		);

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$summary->autoload_toplist = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT option_name, LENGTH(option_value) AS option_value_length, autoload
				 FROM `$wpdb->options`
				 WHERE autoload IN ($placeholders)
				 ORDER BY option_value_length DESC
				 LIMIT 20",
				$autoload_values
			)
		);

return $summary;
	}

/**
	 * Handle all request actions from main cls.
	 *
	 * @since 3.0
	 * @access public
	 * @return void
	 */
	public function handler() {
		$type = Router::verify_type();

switch ($type) {
			case self::TYPE_CONV_TB:
			$this->_conv_innodb();
				break;

default:
				if ( 'all' === $type || in_array( $type, self::$types, true ) ) {
					if ( is_multisite() && is_network_admin() ) {
						$blogs = Activation::get_network_ids();
						foreach ( $blogs as $blog_id ) {
							switch_to_blog( $blog_id );
							$msg = $this->_db_clean( $type );
							restore_current_blog();
						}
					} else {
						$msg = $this->_db_clean( $type );
					}
					Admin_Display::success( $msg );
				}
				break;
		}

Admin::redirect();
	}

/**
	 * Clean DB via WP-CLI.
	 *
	 * @since 7.0
	 * @access public
	 * @param string $args Cleanup type.
	 * @return string|false
	 */
	public function handler_clean_db_cli( $args ) {
		if ( defined( 'WP_CLI' ) && constant('WP_CLI') ) {
			return $this->_db_clean( $args );
		}

return false;
	}
}