prefix . 'actionscheduler_actions'; return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table; } /** * Build fast, index-friendly SQL WHERE clause on wp_comments (alias: c) for a specific comment category. * Always protects WooCommerce order_note, webhook_delivery, and action_log entries. */ private function build_comment_type_clause( $type, $protect_reviews = false ) { global $wpdb; $review_guard = $protect_reviews ? " AND c.comment_type != 'review' AND NOT EXISTS (SELECT 1 FROM {$wpdb->commentmeta} rm WHERE rm.comment_id = c.comment_ID AND rm.meta_key = 'rating' AND rm.meta_value > '0')" : ''; switch ( $type ) { case 'approved': return "(c.comment_approved = '1' AND c.comment_type NOT IN ('pingback', 'trackback', 'order_note', 'webhook_delivery', 'action_log'){$review_guard})"; case 'unapproved': return "(c.comment_approved = '0' AND c.comment_type NOT IN ('pingback', 'trackback', 'order_note', 'webhook_delivery', 'action_log'){$review_guard})"; case 'spam': return "(c.comment_approved = 'spam' AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log'))"; case 'trash': case 'trashed': return "(c.comment_approved IN ('trash', 'post-trashed') AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log'))"; case 'pingbacks': return "(c.comment_type = 'pingback')"; case 'trackbacks': return "(c.comment_type = 'trackback')"; default: return ''; } } /** * Query live counts for all 6 comment categories in a single pass over wp_comments. */ private function get_comment_breakdown( $protect_reviews = false ) { global $wpdb; $row = $wpdb->get_row( "SELECT COALESCE(SUM(CASE WHEN c.comment_type = 'pingback' THEN 1 ELSE 0 END), 0) AS pingbacks, COALESCE(SUM(CASE WHEN c.comment_type = 'trackback' THEN 1 ELSE 0 END), 0) AS trackbacks, COALESCE(SUM(CASE WHEN c.comment_approved = 'spam' THEN 1 ELSE 0 END), 0) AS spam_comments, COALESCE(SUM(CASE WHEN c.comment_approved IN ('trash', 'post-trashed') THEN 1 ELSE 0 END), 0) AS trashed_comments, COALESCE(SUM(CASE WHEN c.comment_approved = '1' AND c.comment_type NOT IN ('pingback', 'trackback') THEN 1 ELSE 0 END), 0) AS approved_comments, COALESCE(SUM(CASE WHEN c.comment_approved = '0' AND c.comment_type NOT IN ('pingback', 'trackback') THEN 1 ELSE 0 END), 0) AS unapproved_comments, COALESCE(SUM(CASE WHEN c.comment_type = 'review' AND c.comment_approved IN ('1', '0') THEN 1 ELSE 0 END), 0) AS wc_reviews_type FROM {$wpdb->comments} c WHERE c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')", ARRAY_A ); $rated_reviews = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT cm.comment_id) FROM {$wpdb->commentmeta} cm INNER JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE cm.meta_key = 'rating' AND cm.meta_value > '0' AND c.comment_approved IN ('1', '0')" ); $wc_reviews = max( (int) ( $row['wc_reviews_type'] ?? 0 ), $rated_reviews ); $approved = (int) ( $row['approved_comments'] ?? 0 ); $unapproved = (int) ( $row['unapproved_comments'] ?? 0 ); if ( $protect_reviews && $wc_reviews > 0 ) { $approved = (int) $wpdb->get_var( 'SELECT COUNT(c.comment_ID) FROM ' . $wpdb->comments . ' c WHERE ' . $this->build_comment_type_clause( 'approved', true ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $unapproved = (int) $wpdb->get_var( 'SELECT COUNT(c.comment_ID) FROM ' . $wpdb->comments . ' c WHERE ' . $this->build_comment_type_clause( 'unapproved', true ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared } return [ 'approved_comments' => $approved, 'unapproved_comments' => $unapproved, 'spam_comments' => (int) ( $row['spam_comments'] ?? 0 ), 'trashed_comments' => (int) ( $row['trashed_comments'] ?? 0 ), 'pingbacks' => (int) ( $row['pingbacks'] ?? 0 ), 'trackbacks' => (int) ( $row['trackbacks'] ?? 0 ), 'wc_reviews_count' => $wc_reviews, ]; } /** * Synchronize wp_posts.comment_count with actual approved comments (excluding WooCommerce order notes). */ private function recount_post_comments() { global $wpdb; $remaining_approved = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = '1' AND comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')" ); if ( 0 === $remaining_approved ) { // Fast path when all approved comments have been wiped $wpdb->query( "UPDATE {$wpdb->posts} SET comment_count = 0 WHERE comment_count > 0" ); return; } $wpdb->query( "UPDATE {$wpdb->posts} p LEFT JOIN ( SELECT comment_post_ID, COUNT(*) AS cnt FROM {$wpdb->comments} WHERE comment_approved = '1' AND comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log') GROUP BY comment_post_ID ) c ON p.ID = c.comment_post_ID SET p.comment_count = COALESCE(c.cnt, 0) WHERE p.comment_count != COALESCE(c.cnt, 0)" ); } public function get_stats() { check_ajax_referer( 'aio-settings-nonce', 'security' ); if ( ! current_user_can( 'manage_options' ) ) { wp_send_json_error( __( 'Unauthorized', 'aio-tools' ) ); } global $wpdb; $protect_reviews = ! empty( $_POST['protect_reviews'] ); $comm_stats = $this->get_comment_breakdown( $protect_reviews ); $revisions = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'" ); $auto_drafts = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'" ); $cutoff_gmt = $this->get_trash_cutoff_gmt(); $trashed_posts = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash' AND post_type NOT IN ('shop_order', 'shop_order_placehold', 'shop_subscription') AND post_modified_gmt < %s", $cutoff_gmt ) ); $spam_comments = $comm_stats['spam_comments']; $trashed_comments = $comm_stats['trashed_comments']; $expired_transients = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP()" ); $orphaned_postmeta = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ); $orphaned_commmeta = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ); $orphaned_meta = $orphaned_postmeta + $orphaned_commmeta; $action_scheduler = 0; if ( $this->has_action_scheduler_table() ) { $as_table = $wpdb->prefix . 'actionscheduler_actions'; $as_cutoff = gmdate( 'Y-m-d H:i:s', time() - ( 7 * DAY_IN_SECONDS ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $action_scheduler = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$as_table} WHERE status IN ('complete', 'failed', 'canceled') AND scheduled_date_gmt < %s", $as_cutoff ) ); } $autoload_bytes = (int) $wpdb->get_var( "SELECT COALESCE(SUM(LENGTH(option_value)), 0) FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')" ); $total = $revisions + $auto_drafts + $trashed_posts + $spam_comments + $trashed_comments + $expired_transients + $orphaned_meta + $action_scheduler; wp_send_json_success( [ 'revisions' => $revisions, 'auto_drafts' => $auto_drafts, 'trashed_posts' => $trashed_posts, 'approved_comments' => $comm_stats['approved_comments'], 'unapproved_comments' => $comm_stats['unapproved_comments'], 'spam_comments' => $spam_comments, 'trashed_comments' => $trashed_comments, 'pingbacks' => $comm_stats['pingbacks'], 'trackbacks' => $comm_stats['trackbacks'], 'wc_reviews_count' => $comm_stats['wc_reviews_count'], 'expired_transients' => $expired_transients, 'orphaned_postmeta' => $orphaned_postmeta, 'orphaned_commentmeta' => $orphaned_commmeta, 'orphaned_meta' => $orphaned_meta, 'action_scheduler' => $action_scheduler, 'autoload_kb' => round( $autoload_bytes / 1024, 1 ), 'total' => $total, ] ); } private function flush_db_caches( $groups = [] ) { if ( function_exists( 'wp_cache_delete' ) ) { wp_cache_delete( 'comments-0', 'counts' ); } if ( function_exists( 'wp_cache_flush_group' ) && function_exists( 'wp_cache_supports' ) && wp_cache_supports( 'flush_group' ) ) { foreach ( $groups as $grp ) { wp_cache_flush_group( $grp ); } } elseif ( function_exists( 'wp_cache_flush' ) ) { wp_cache_flush(); } } private function clean_expired_transients_sql() { global $wpdb; $wpdb->query( "DELETE a, b FROM {$wpdb->options} a INNER JOIN {$wpdb->options} b ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12)) WHERE a.option_name LIKE '\_transient\_%' AND a.option_name NOT LIKE '\_transient\_timeout\_%' AND b.option_value < UNIX_TIMESTAMP()" ); $wpdb->query( "DELETE FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP()" ); } private function clean_action_scheduler_sql() { global $wpdb; if ( ! $this->has_action_scheduler_table() ) { return; } $as_actions = $wpdb->prefix . 'actionscheduler_actions'; $as_logs = $wpdb->prefix . 'actionscheduler_logs'; $as_cutoff = gmdate( 'Y-m-d H:i:s', time() - ( 7 * DAY_IN_SECONDS ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query( $wpdb->prepare( "DELETE FROM {$as_actions} WHERE status IN ('complete', 'failed', 'canceled') AND scheduled_date_gmt < %s", $as_cutoff ) ); if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $as_logs ) ) === $as_logs ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query( "DELETE lg FROM {$as_logs} lg LEFT JOIN {$as_actions} ac ON ac.action_id = lg.action_id WHERE ac.action_id IS NULL" ); } } /** * High-Speed Chunked Bulk Delete Comments AJAX Handler. * Deletes up to 5,000 comments per request via PRIMARY KEY / indexed comment_id to prevent InnoDB lock exhaustion and HTTP timeouts on 100k-1M+ comments. */ public function bulk_delete_comments_ajax() { check_ajax_referer( 'aio-settings-nonce', 'security' ); if ( ! current_user_can( 'manage_options' ) ) { wp_send_json_error( __( 'Unauthorized', 'aio-tools' ) ); } if ( function_exists( 'set_time_limit' ) ) { @set_time_limit( 120 ); // phpcs:ignore WordPress.PHP.NoSilencedErrors.Discouraged } if ( function_exists( 'wp_suspend_cache_invalidation' ) ) { wp_suspend_cache_invalidation( true ); } $raw_types = isset( $_POST['types'] ) ? wp_unslash( $_POST['types'] ) : []; if ( is_string( $raw_types ) ) { $raw_types = explode( ',', $raw_types ); } if ( ! is_array( $raw_types ) ) { $raw_types = []; } $allowed_types = [ 'approved', 'unapproved', 'spam', 'trash', 'pingbacks', 'trackbacks' ]; $protect_reviews = ! empty( $_POST['protect_reviews'] ); $batch_size = isset( $_POST['batch_size'] ) ? max( 1000, min( 10000, absint( $_POST['batch_size'] ) ) ) : 5000; $clauses = []; $selected_types = []; foreach ( $raw_types as $t ) { $clean_t = sanitize_key( (string) $t ); if ( in_array( $clean_t, $allowed_types, true ) && ! in_array( $clean_t, $selected_types, true ) ) { $clause = $this->build_comment_type_clause( $clean_t, $protect_reviews ); if ( '' !== $clause ) { $selected_types[] = $clean_t; $clauses[] = $clause; } } } if ( empty( $clauses ) ) { wp_send_json_error( __( 'Please select at least one comment category to delete.', 'aio-tools' ) ); } global $wpdb; $where_sql = '(' . implode( ' OR ', $clauses ) . ") AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')"; // Fetch a chunk of matching comment_IDs directly from wp_comments (no slow multi-table JOINs) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $raw_ids = $wpdb->get_col( "SELECT c.comment_ID FROM {$wpdb->comments} c WHERE {$where_sql} LIMIT {$batch_size}" ); $clean_ids = []; if ( is_array( $raw_ids ) ) { foreach ( $raw_ids as $cid ) { $id_int = (int) $cid; if ( $id_int > 0 ) { $clean_ids[] = $id_int; } } } $deleted_in_batch = 0; if ( ! empty( $clean_ids ) ) { // Delete in sub-chunks of 1,000 IDs by indexed comment_id & PRIMARY KEY comment_ID foreach ( array_chunk( $clean_ids, 1000 ) as $id_chunk ) { $id_list = implode( ',', $id_chunk ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query( "DELETE FROM {$wpdb->commentmeta} WHERE comment_id IN ({$id_list})" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $res = $wpdb->query( "DELETE FROM {$wpdb->comments} WHERE comment_ID IN ({$id_list})" ); if ( false !== $res ) { $deleted_in_batch += (int) $res; } } } // Check how many matching comments remain for the progress bar & next batch loop // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $remaining = (int) $wpdb->get_var( "SELECT COUNT(c.comment_ID) FROM {$wpdb->comments} c WHERE {$where_sql}" ); $is_done = ( 0 === $remaining || empty( $clean_ids ) ); if ( function_exists( 'wp_suspend_cache_invalidation' ) ) { wp_suspend_cache_invalidation( false ); } if ( $is_done ) { // Finalize: recount wp_posts.comment_count and flush all comment/post/count caches $this->recount_post_comments(); $this->flush_db_caches( [ 'counts', 'comments', 'comment_meta', 'posts' ] ); if ( array_intersect( $selected_types, [ 'approved', 'pingbacks', 'trackbacks' ] ) && class_exists( 'AIO_Page_Cache' ) && method_exists( 'AIO_Page_Cache', 'clear_all' ) ) { AIO_Page_Cache::clear_all(); } } else { // Flush comment count cache after every batch so wp-admin/edit-comments.php stays accurate mid-run $this->flush_db_caches( [ 'counts', 'comments' ] ); } $total_deleted = isset( $_POST['deleted_so_far'] ) ? absint( $_POST['deleted_so_far'] ) + $deleted_in_batch : $deleted_in_batch; /* translators: %d: number of comments permanently deleted */ $msg = sprintf( __( 'Permanently deleted %d comments and synchronized post comment counts!', 'aio-tools' ), $total_deleted ); wp_send_json_success( [ 'done' => $is_done, 'deleted' => $deleted_in_batch, 'deleted_total' => $total_deleted, 'remaining' => $remaining, 'message' => $msg, ] ); } public function clean_db() { check_ajax_referer( 'aio-settings-nonce', 'security' ); if ( ! current_user_can( 'manage_options' ) ) { wp_send_json_error( __( 'Unauthorized', 'aio-tools' ) ); } $type = isset( $_POST['type'] ) ? sanitize_key( wp_unslash( $_POST['type'] ) ) : ''; if ( empty( $type ) ) { wp_send_json_error( __( 'Invalid cleanup type.', 'aio-tools' ) ); } global $wpdb; $cutoff_gmt = $this->get_trash_cutoff_gmt(); switch ( $type ) { case 'revisions': $wpdb->query( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'" ); $this->flush_db_caches( [ 'posts', 'post_meta', 'term_relationships' ] ); break; case 'auto_drafts': $wpdb->query( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_status = 'auto-draft'" ); $this->flush_db_caches( [ 'posts', 'post_meta', 'term_relationships' ] ); break; case 'trashed_posts': $wpdb->query( $wpdb->prepare( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_status = 'trash' AND a.post_type NOT IN ('shop_order', 'shop_order_placehold', 'shop_subscription') AND a.post_modified_gmt < %s", $cutoff_gmt ) ); $this->flush_db_caches( [ 'posts', 'post_meta', 'term_relationships' ] ); break; case 'spam_comments': $wpdb->query( "DELETE c, cm FROM {$wpdb->comments} c LEFT JOIN {$wpdb->commentmeta} cm ON (c.comment_ID = cm.comment_id) WHERE c.comment_approved = 'spam' AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')" ); $this->flush_db_caches( [ 'counts', 'comments', 'comment_meta' ] ); break; case 'trashed_comments': $wpdb->query( "DELETE c, cm FROM {$wpdb->comments} c LEFT JOIN {$wpdb->commentmeta} cm ON (c.comment_ID = cm.comment_id) WHERE c.comment_approved IN ('trash', 'post-trashed') AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')" ); $this->flush_db_caches( [ 'counts', 'comments', 'comment_meta' ] ); break; case 'expired_transients': $this->clean_expired_transients_sql(); $this->flush_db_caches( [ 'options', 'transient' ] ); break; case 'orphaned_postmeta': $wpdb->query( "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ); $this->flush_db_caches( [ 'post_meta' ] ); break; case 'orphaned_commentmeta': $wpdb->query( "DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ); $this->flush_db_caches( [ 'comment_meta' ] ); break; case 'orphaned_meta': $wpdb->query( "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ); $wpdb->query( "DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ); $this->flush_db_caches( [ 'post_meta', 'comment_meta' ] ); break; case 'action_scheduler': $this->clean_action_scheduler_sql(); break; case 'optimize_tables': $like_prefix = $wpdb->esc_like( $wpdb->prefix ) . '%'; $tables = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like_prefix ) ); $optimized = 0; if ( is_array( $tables ) ) { foreach ( array_slice( $tables, 0, 80 ) as $tbl ) { $safe_tbl = preg_replace( '/[^A-Za-z0-9_]/', '', (string) $tbl ); if ( $safe_tbl !== '' ) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $wpdb->query( "OPTIMIZE TABLE `{$safe_tbl}`" ); $optimized++; } } } /* translators: %d: number of database tables optimized */ wp_send_json_success( [ 'message' => sprintf( __( 'Optimized %d database tables successfully!', 'aio-tools' ), $optimized ) ] ); break; case 'all': $wpdb->query( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_type = 'revision' OR a.post_status = 'auto-draft'" ); $wpdb->query( $wpdb->prepare( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_status = 'trash' AND a.post_type NOT IN ('shop_order', 'shop_order_placehold', 'shop_subscription') AND a.post_modified_gmt < %s", $cutoff_gmt ) ); $wpdb->query( "DELETE c, cm FROM {$wpdb->comments} c LEFT JOIN {$wpdb->commentmeta} cm ON (c.comment_ID = cm.comment_id) WHERE c.comment_approved IN ('spam', 'trash', 'post-trashed') AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')" ); $wpdb->query( "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ); $wpdb->query( "DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL" ); $this->clean_expired_transients_sql(); $this->clean_action_scheduler_sql(); $this->flush_db_caches( [ 'counts', 'posts', 'post_meta', 'term_relationships', 'comments', 'comment_meta', 'options', 'transient' ] ); break; default: wp_send_json_error( __( 'Unknown cleanup type.', 'aio-tools' ) ); } wp_send_json_success( [ 'message' => __( 'Cleaned up successfully!', 'aio-tools' ) ] ); } /** * Check whether an option must keep autoload enabled to protect WordPress core operation. */ private function is_protected_autoload_option( $option_name ) { global $wpdb; $core_protected = [ 'siteurl', 'home', 'blogname', 'blogdescription', 'users_can_register', 'admin_email', 'start_of_week', 'use_balanceTags', 'use_smilies', 'require_name_email', 'comments_notify', 'posts_per_rss', 'rss_use_excerpt', 'permalink_structure', 'rewrite_rules', 'hack_file', 'blog_charset', 'active_plugins', 'category_base', 'tag_base', 'template', 'stylesheet', 'WPLANG', 'cron', 'aio_options', 'aio_db_version', 'aio_encryption_key', $wpdb->prefix . 'user_roles', ]; if ( in_array( $option_name, $core_protected, true ) ) { return true; } if ( strpos( $option_name, 'theme_mods_' ) === 0 || strpos( $option_name, 'widget_' ) === 0 ) { return true; } return false; } /** * Top 20 Autoloaded Options Bloat Inspector. */ public function get_autoload_options_ajax() { check_ajax_referer( 'aio-settings-nonce', 'security' ); if ( ! current_user_can( 'manage_options' ) ) { wp_send_json_error( __( 'Unauthorized', 'aio-tools' ) ); } global $wpdb; $rows = $wpdb->get_results( "SELECT option_name, autoload, LENGTH(option_value) AS byte_size FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto', 'auto-on') ORDER BY byte_size DESC LIMIT 20", ARRAY_A ); $total_bytes = (int) $wpdb->get_var( "SELECT COALESCE(SUM(LENGTH(option_value)), 0) FROM {$wpdb->options} WHERE autoload IN ('yes', 'on', 'auto', 'auto-on')" ); $list = []; if ( is_array( $rows ) ) { foreach ( $rows as $r ) { $name = (string) $r['option_name']; $list[] = [ 'name' => $name, 'option_name' => $name, 'autoload' => (string) $r['autoload'], 'size_kb' => round( ( (int) $r['byte_size'] ) / 1024, 2 ), 'protected' => $this->is_protected_autoload_option( $name ), ]; } } wp_send_json_success( [ 'total_kb' => round( $total_bytes / 1024, 1 ), 'items' => $list, 'options' => $list, ] ); } /** * Toggle autoload off for a non-protected bloated option. */ public function toggle_option_autoload_ajax() { check_ajax_referer( 'aio-settings-nonce', 'security' ); if ( ! current_user_can( 'manage_options' ) ) { wp_send_json_error( __( 'Unauthorized', 'aio-tools' ) ); } $option_name = isset( $_POST['option_name'] ) ? sanitize_text_field( wp_unslash( $_POST['option_name'] ) ) : ''; if ( $option_name === '' || $this->is_protected_autoload_option( $option_name ) ) { wp_send_json_error( __( 'This core option is protected and must remain autoloaded.', 'aio-tools' ) ); } global $wpdb; if ( function_exists( 'wp_set_option_autoload' ) ) { wp_set_option_autoload( $option_name, false ); } else { $wpdb->update( $wpdb->options, [ 'autoload' => 'no' ], [ 'option_name' => $option_name ], [ '%s' ], [ '%s' ] ); } wp_cache_delete( 'alloptions', 'options' ); wp_cache_delete( $option_name, 'options' ); /* translators: %s: option_name */ wp_send_json_success( [ 'message' => sprintf( __( 'Disabled autoload for "%s" successfully!', 'aio-tools' ), $option_name ) ] ); } /** * Safe Weekly Auto-Cleaner (never deletes active WooCommerce orders or recently trashed posts). */ public function process_auto_cleaner() { global $wpdb; $cutoff_gmt = $this->get_trash_cutoff_gmt(); // Clean Post Revisions & Auto Drafts safely via INNER/LEFT JOIN on exact post IDs $wpdb->query( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_type = 'revision' OR a.post_status = 'auto-draft'" ); // Clean Trashed Posts older than EMPTY_TRASH_DAYS (excluding WooCommerce orders/subscriptions) $wpdb->query( $wpdb->prepare( "DELETE a, b, c FROM {$wpdb->posts} a LEFT JOIN {$wpdb->term_relationships} b ON (a.ID = b.object_id) LEFT JOIN {$wpdb->postmeta} c ON (a.ID = c.post_id) WHERE a.post_status = 'trash' AND a.post_type NOT IN ('shop_order', 'shop_order_placehold', 'shop_subscription') AND a.post_modified_gmt < %s", $cutoff_gmt ) ); // Clean Spam & Trashed Comments along with their commentmeta $wpdb->query( "DELETE c, cm FROM {$wpdb->comments} c LEFT JOIN {$wpdb->commentmeta} cm ON (c.comment_ID = cm.comment_id) WHERE c.comment_approved IN ('spam', 'trash', 'post-trashed') AND c.comment_type NOT IN ('order_note', 'webhook_delivery', 'action_log')" ); // Clean Expired Transients & old Action Scheduler entries $this->clean_expired_transients_sql(); $this->clean_action_scheduler_sql(); $this->flush_db_caches( [ 'counts', 'posts', 'post_meta', 'term_relationships', 'comments', 'comment_meta', 'options', 'transient' ] ); } } new AIO_DB_Cleaner();