'button', 'id' => 'database-cleanup-submit', 'name' => '', 'input_class' => 'pwp-btn pwp-btn-ghost', 'label' => __( 'Check the database', 'adminease' ), 'field_description' => __( 'Shows what can be cleaned, with a count for each, and lets you choose. Make a backup first, deleted rows cannot be restored.', 'adminease' ), 'attributes' => [ 'role' => 'button', ], ]; return $fields; } /** * Adds the Database section to the Performance sidebar. * * @param array $subgroups The existing sub-section map. * * @return array */ public function adminease_settings_subgroups( array $subgroups ): array { if( ! isset( $subgroups['performance'] ) ) { return $subgroups; } $subgroups['performance']['database'] = [ 'title' => __( 'Database', 'adminease' ), 'icon' => '', 'field_ids' => [ 'database-cleanup-submit' ], ]; return $subgroups; } /** * Prints the container the script fills with the checklist, under the button. * * @param array $field The field that was just rendered. * * @return void */ public function adminease_after_field_render( array $field ): void { if( 'database-cleanup-submit' !== ( $field['id'] ?? '' ) || ! current_user_can( 'manage_options' ) ) { return; } echo '
'; } /** * Loads the panel script on the AdminEase screen. * * @param string $hook The current admin page hook. * * @return void */ public function admin_enqueue_scripts( string $hook ): void { if( 'toplevel_page_adminease' !== $hook || ! current_user_can( 'manage_options' ) ) { return; } wp_enqueue_script( ADMINEASE_NAME . 'DatabaseCleanup', ADMINEASE_PLUGIN_URL . 'assets/js/AdminEaseDatabaseCleanup.js', [ 'jquery', ADMINEASE_NAME ], filemtime( ADMINEASE_DIR . 'assets/js/AdminEaseDatabaseCleanup.js' ), true ); wp_localize_script( ADMINEASE_NAME . 'DatabaseCleanup', ADMINEASE_NAME . 'DatabaseCleanupAjaxObj', [ 'ajaxUrl' => admin_url( 'admin-ajax.php' ), 'security' => wp_create_nonce( 'adminease_database_cleanup_nonce' ), 'kinds' => $this->get_kinds(), 'i18n' => [ 'checking' => __( 'Checking...', 'adminease' ), /* translators: %d: number of items deleted so far */ 'cleaning' => __( 'Cleaning... %d', 'adminease' ), 'optimizing' => __( 'Optimizing...', 'adminease' ), 'clean' => __( 'Clean selected', 'adminease' ), 'optimize' => __( 'Optimize tables', 'adminease' ), 'recheck' => __( 'Check again', 'adminease' ), 'nothing' => __( 'Nothing selected.', 'adminease' ), /* translators: %s: the database size */ 'size' => __( 'Database size: %s. It may not shrink until you click Optimize tables.', 'adminease' ), 'confirm' => __( 'This will delete the selected items:', 'adminease' ), 'confirmOpt' => __( 'Optimizing rebuilds every table. It can take minutes on a large database, locks tables while it runs and needs free disk space. Do it at a quiet time. Continue?', 'adminease' ), 'stuck' => __( 'Some items could not be deleted, something is blocking them.', 'adminease' ), 'sure' => __( 'This cannot be undone. Continue?', 'adminease' ), /* translators: 1: number of items deleted, 2: database size before, 3: database size after */ 'done' => __( 'Deleted %1$s items. Database size: %2$s before, %3$s after.', 'adminease' ), /* translators: 1: database size before, 2: database size after */ 'optimized' => __( 'Tables optimized. Database size: %1$s before, %2$s after.', 'adminease' ), 'error' => __( 'An error occurred. Refresh the page and try again.', 'adminease' ), ], ] ); } /** * Lists what can be cleaned. Each kind says where its rows are, which ones count, and how they are deleted. * * Keys: label, desc, default (ticked at first), from (FROM clause, the table aliased as m), id (the id column), * where (SQL condition), extra (extra column to select), delete (post, comment, rows or transients), * table and column (for rows). * * @return array */ private function get_categories(): array { global $wpdb; $draft_cutoff = gmdate( 'Y-m-d H:i:s', time() - self::AUTO_DRAFT_DAYS * DAY_IN_SECONDS + (int) ( get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ) ); $categories = [ 'transients' => [ 'label' => __( 'Expired transients', 'adminease' ), 'desc' => __( 'Temporary cached values that have run out. WordPress makes new ones when needed.', 'adminease' ), 'default' => true, 'from' => "{$wpdb->options} m", 'id' => 'm.option_id', 'extra' => 'm.option_name', 'where' => $wpdb->prepare( '(m.option_name LIKE %s OR m.option_name LIKE %s) AND CAST(m.option_value AS UNSIGNED) < %d', $wpdb->esc_like( '_transient_timeout_' ) . '%', $wpdb->esc_like( '_site_transient_timeout_' ) . '%', time() ), 'delete' => 'transients', ], 'auto_drafts' => [ 'label' => __( 'Old auto-drafts', 'adminease' ), 'desc' => __( 'Empty drafts WordPress creates when you open the editor, left for more than a week.', 'adminease' ), 'default' => true, 'from' => "{$wpdb->posts} m", 'id' => 'm.ID', 'where' => $wpdb->prepare( "m.post_status = 'auto-draft' AND m.post_date < %s", $draft_cutoff ), 'delete' => 'post', ], 'trashed_posts' => [ 'label' => __( 'Everything in the trash', 'adminease' ), 'desc' => __( 'Trashed posts, pages, products and any other post type. Files of trashed media are deleted too. Empties the trash now instead of waiting.', 'adminease' ), 'default' => false, 'from' => "{$wpdb->posts} m", 'id' => 'm.ID', 'where' => "m.post_status = 'trash'", 'delete' => 'post', ], 'spam_comments' => [ 'label' => __( 'Spam and trashed comments', 'adminease' ), 'desc' => __( 'Comments marked as spam or moved to the trash.', 'adminease' ), 'default' => false, 'from' => "{$wpdb->comments} m", 'id' => 'm.comment_ID', 'where' => "m.comment_approved IN ('spam', 'trash')", 'delete' => 'comment', ], 'oembed' => [ 'label' => __( 'Embed caches', 'adminease' ), 'desc' => __( 'Saved copies of embedded videos and tweets. They are fetched again when a page needs them.', 'adminease' ), 'default' => true, 'from' => "{$wpdb->postmeta} m", 'id' => 'm.meta_id', 'where' => $wpdb->prepare( 'm.meta_key LIKE %s', $wpdb->esc_like( '_oembed_' ) . '%' ), 'delete' => 'rows', 'table' => $wpdb->postmeta, 'column' => 'meta_id', ], ]; if( is_multisite() && is_main_site() && is_super_admin() ) { $categories['network_transients'] = [ 'label' => __( 'Expired network transients', 'adminease' ), 'desc' => __( 'Temporary cached values stored for the whole network that have run out.', 'adminease' ), 'default' => true, 'from' => "{$wpdb->sitemeta} m", 'id' => 'm.meta_id', 'extra' => 'm.meta_key', 'where' => $wpdb->prepare( 'm.site_id = %d AND m.meta_key LIKE %s AND CAST(m.meta_value AS UNSIGNED) < %d', get_current_network_id(), $wpdb->esc_like( '_site_transient_timeout_' ) . '%', time() ), 'delete' => 'network_transients', ]; } if( class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' ) && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ) { $categories['trashed_orders'] = [ 'label' => __( 'Trashed WooCommerce orders', 'adminease' ), 'desc' => __( 'Orders in the trash that WooCommerce keeps in its own order tables. They are deleted for good.', 'adminease' ), 'default' => false, 'from' => "{$wpdb->prefix}wc_orders m", 'id' => 'm.id', 'where' => "m.status = 'trash'", 'delete' => 'order', ]; } $orphans = [ 'orphan_postmeta' => [ __( 'Orphaned post meta', 'adminease' ), $wpdb->postmeta, 'meta_id', $wpdb->posts, 'ID', 'post_id' ], 'orphan_commentmeta' => [ __( 'Orphaned comment meta', 'adminease' ), $wpdb->commentmeta, 'meta_id', $wpdb->comments, 'comment_ID', 'comment_id' ], 'orphan_termmeta' => [ __( 'Orphaned term meta', 'adminease' ), $wpdb->termmeta, 'meta_id', $wpdb->terms, 'term_id', 'term_id' ], 'orphan_usermeta' => [ __( 'Orphaned user meta', 'adminease' ), $wpdb->usermeta, 'umeta_id', $wpdb->users, 'ID', 'user_id' ], ]; if( is_multisite() && ! ( is_main_site() && is_super_admin() ) ) { unset( $orphans['orphan_usermeta'] ); } foreach( $orphans as $key => list( $label, $table, $id, $parent, $parent_id, $column ) ) { $categories[ $key ] = [ 'label' => $label, 'desc' => __( 'Rows left behind by something that was deleted. Rows pointing at id 0 are kept, some plugins store global values there.', 'adminease' ), 'default' => true, 'from' => "{$table} m LEFT JOIN {$parent} p ON p.{$parent_id} = m.{$column}", 'id' => "m.{$id}", 'where' => "m.{$column} > 0 AND p.{$parent_id} IS NULL", 'delete' => 'rows', 'table' => $table, 'column' => $id, ]; } return $categories; } /** * Counts one kind, reports the size, cleans one batch, or optimizes tables. * * @return void */ public function ajax_database_cleanup(): void { if( ! isset( $_POST['security'] ) || ! wp_verify_nonce( sanitize_key( wp_unslash( $_POST['security'] ) ), 'adminease_database_cleanup_nonce' ) ) { wp_send_json_error( new WP_Error( 'security_error', esc_html__( 'Security check failed. Refresh the page and try again.', 'adminease' ) ), 403 ); } if( ! current_user_can( 'manage_options' ) ) { wp_send_json_error( new WP_Error( 'permissions', esc_html__( 'You do not have sufficient permissions to perform this action', 'adminease' ) ), 403 ); } $mode = isset( $_POST['mode'] ) ? sanitize_key( wp_unslash( $_POST['mode'] ) ) : 'count'; if( 'optimize' === $mode ) { wp_send_json_success( $this->optimize_batch( isset( $_POST['offset'] ) ? absint( $_POST['offset'] ) : 0 ) ); } if( 'clean' === $mode ) { $types = isset( $_POST['types'] ) ? array_map( 'sanitize_key', (array) wp_unslash( $_POST['types'] ) ) : []; wp_send_json_success( $this->process_batch( $types ) ); } if( 'size' === $mode ) { wp_send_json_success( [ 'size' => size_format( $this->get_database_size() ) ] ); } $type = isset( $_POST['type'] ) ? sanitize_key( wp_unslash( $_POST['type'] ) ) : ''; wp_send_json_success( $this->count_kind( $type ) ); } /** * Lists the kinds for the script, without counts. * * @return array */ private function get_kinds(): array { $kinds = []; foreach( $this->get_categories() as $key => $category ) { $kinds[] = [ 'key' => $key, 'label' => $category['label'], 'desc' => $category['desc'], 'default' => $category['default'], ]; } return $kinds; } /** * Counts one kind, one request each so a slow count on a huge table cannot stall the rest. * A count stops after COUNT_CAP matches, but a table with no matches is still read in full. * * @param string $key The kind. * * @return array { * @type int $count Rows found, at most COUNT_CAP. * @type bool $capped True when the count stopped at the cap. * } */ public function count_kind( string $key ): array { global $wpdb; $categories = $this->get_categories(); if( ! isset( $categories[ $key ] ) ) { return [ 'count' => 0, 'capped' => false, ]; } $category = $categories[ $key ]; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared $count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT 1 FROM {$category['from']} WHERE {$category['where']} LIMIT " . self::COUNT_CAP . ') t' ); return [ 'count' => $count, 'capped' => $count >= self::COUNT_CAP, ]; } /** * Deletes the chosen kinds until the time budget is spent. * * Re-entrant: deleted rows drop out of every query, so the next call simply carries on with what is left. * * @param string[] $types The kinds to clean. Unknown ones are ignored. * * @return array { * @type int $deleted Items deleted by this call. * @type bool $done True when nothing is left to delete. * } */ public function process_batch( array $types ): array { /** * Filters the seconds one database cleanup request may run. * * @param float $seconds The time budget. */ $budget = (float) apply_filters( 'adminease_database_cleanup_time_budget', self::BATCH_TIME_BUDGET ); $start = microtime( true ); $deleted = 0; foreach( array_intersect_key( $this->get_categories(), array_flip( $types ) ) as $category ) { $cursor = 0; do { list( $selected, $removed ) = $this->delete_batch( $category, $cursor ); $deleted += $removed; if( $selected && ( microtime( true ) - $start ) >= $budget ) { return [ 'deleted' => $deleted, 'done' => false, ]; } } while( $selected ); } return [ 'deleted' => $deleted, 'done' => true, 'size' => size_format( $this->get_database_size() ), ]; } /** * Deletes one batch of one kind. * * @param array $category The kind, from get_categories(). * @param int $cursor The last id handled, updated in place. * * @return int[] Rows selected, items deleted. */ private function delete_batch( array $category, int &$cursor ): array { global $wpdb; $extra = isset( $category['extra'] ) ? ", {$category['extra']} AS extra" : ''; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared $rows = $wpdb->get_results( "SELECT {$category['id']} AS id{$extra} FROM {$category['from']} WHERE {$category['where']} AND {$category['id']} > " . (int) $cursor . " ORDER BY {$category['id']} ASC LIMIT " . self::ROWS_PER_QUERY ); if( empty( $rows ) ) { return [ 0, 0 ]; } $ids = array_map( 'intval', wp_list_pluck( $rows, 'id' ) ); $cursor = (int) end( $ids ); $deleted = 0; switch( $category['delete'] ) { case 'post': foreach( $ids as $id ) { wp_delete_post( $id, true ); if( null === get_post( $id ) ) { ++$deleted; } } break; case 'comment': foreach( $ids as $id ) { wp_delete_comment( $id, true ); if( ! get_comment( $id ) ) { ++$deleted; } } break; case 'network_transients': $names = []; foreach( $rows as $row ) { $names[] = $row->extra; $names[] = preg_replace( '/^_site_transient_timeout_/', '_site_transient_', $row->extra ); } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $affected = (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->sitemeta} WHERE site_id = %d AND meta_key IN (" . implode( ', ', array_fill( 0, count( $names ), '%s' ) ) . ')', array_merge( [ get_current_network_id() ], $names ) ) ); foreach( $names as $name ) { wp_cache_delete( get_current_network_id() . ':' . $name, 'site-options' ); } $deleted = min( count( $ids ), $affected ); break; case 'order': foreach( $ids as $id ) { $order = wc_get_order( $id ); if( $order ) { $order->delete( true ); } if( ! wc_get_order( $id ) ) { ++$deleted; } } break; case 'transients': $names = []; foreach( $rows as $row ) { $names[] = $row->extra; $names[] = preg_replace( '/^(_(?:site_)?transient_)timeout_/', '$1', $row->extra ); } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $affected = (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->options} WHERE option_name IN (" . implode( ', ', array_fill( 0, count( $names ), '%s' ) ) . ')', $names ) ); foreach( $names as $name ) { wp_cache_delete( $name, 'options' ); } wp_cache_delete( 'alloptions', 'options' ); $deleted = min( count( $ids ), $affected ); break; default: // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $deleted = (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$category['table']} WHERE {$category['column']} IN (" . implode( ', ', array_fill( 0, count( $ids ), '%d' ) ) . ')', $ids ) ); } return [ count( $ids ), $deleted ]; } /** * Optimizes this site's tables one after another until the time budget is spent. * * @param int $offset The index of the first table to optimize. * * @return array { * @type int $offset The index to continue from. * @type bool $done True when every table has been optimized. * @type string $size The database size, formatted. Only set once done. * } */ public function optimize_batch( int $offset ): array { global $wpdb; $budget = (float) apply_filters( 'adminease_database_cleanup_time_budget', self::BATCH_TIME_BUDGET ); $start = microtime( true ); $tables = $this->get_tables(); for( $i = $offset; $i < count( $tables ); $i++ ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $tables[ $i ] ) . '`' ); if( ( microtime( true ) - $start ) >= $budget && $i + 1 < count( $tables ) ) { return [ 'offset' => $i + 1, 'done' => false, ]; } } return [ 'offset' => count( $tables ), 'done' => true, 'size' => size_format( $this->get_database_size() ), ]; } /** * Lists WordPress's own tables for this site. * * @return string[] */ private function get_tables(): array { global $wpdb; return array_values( $wpdb->tables( is_multisite() ? 'blog' : 'all' ) ); } /** * Returns the size in bytes of WordPress's own tables for this site. * * @return int */ private function get_database_size(): int { global $wpdb; $suppress = $wpdb->suppress_errors( true ); $wpdb->query( 'SET SESSION information_schema_stats_expiry = 0' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->suppress_errors( $suppress ); $tables = $this->get_tables(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT SUM( data_length + index_length ) FROM information_schema.TABLES WHERE table_schema = DATABASE() AND table_name IN (' . implode( ', ', array_fill( 0, count( $tables ), '%s' ) ) . ')', $tables ) ); } }