get_counts(); $s = \QWSpeedCache\Plugin::instance()->get_all_settings(); $sched = $s['database_cleanup_schedule'] ?? ( ! empty( $s['database_cleanup'] ) ? 'weekly' : 'off' ); ?>

*/ public function get_counts() { global $wpdb; $counts = array( 'revisions' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'" ), 'autodrafts' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'" ), 'spam' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'" ), 'trash_posts' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'" ), '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_commentmeta' => (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_relationships' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->term_relationships} tr LEFT JOIN {$wpdb->posts} p ON p.ID = tr.object_id WHERE p.ID IS NULL" ), 'action_scheduler' => 0, 'wc_sessions' => 0, 'table_overhead' => 0, ); $as = $wpdb->prefix . 'actionscheduler_actions'; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $as ) ) === $as ) { $counts['action_scheduler'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$as} WHERE status IN ('complete','failed','canceled') AND scheduled_date_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)" ); } $sess = $wpdb->prefix . 'woocommerce_sessions'; if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $sess ) ) === $sess ) { $counts['wc_sessions'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sess} WHERE session_expiry > 0 AND session_expiry < UNIX_TIMESTAMP()" ); } $row = $wpdb->get_row( $wpdb->prepare( 'SELECT SUM(data_free) AS overhead FROM information_schema.TABLES WHERE table_schema = %s AND table_name LIKE %s', DB_NAME, $wpdb->esc_like( $wpdb->prefix ) . '%' ), \ARRAY_A ); if ( is_array( $row ) && isset( $row['overhead'] ) ) { $counts['table_overhead'] = (int) $row['overhead']; } return $counts; } /** * Build backup payload for selected types. * * @param string[] $types Types. * @return array */ public function build_backup( array $types ) { global $wpdb; $data = array( 'generated' => gmdate( 'c' ), 'items' => array() ); if ( in_array( 'revisions', $types, true ) ) { $data['items']['revisions'] = $wpdb->get_results( "SELECT ID, post_title, post_date FROM {$wpdb->posts} WHERE post_type = 'revision' LIMIT 500", \ARRAY_A ); } if ( in_array( 'autodrafts', $types, true ) ) { $data['items']['autodrafts'] = $wpdb->get_results( "SELECT ID, post_title, post_date FROM {$wpdb->posts} WHERE post_status = 'auto-draft' LIMIT 500", \ARRAY_A ); } if ( in_array( 'spam', $types, true ) ) { $data['items']['spam'] = $wpdb->get_results( "SELECT comment_ID, comment_author, comment_date FROM {$wpdb->comments} WHERE comment_approved = 'spam' LIMIT 500", \ARRAY_A ); } if ( in_array( 'trash_posts', $types, true ) ) { $data['items']['trash_posts'] = $wpdb->get_results( "SELECT ID, post_title, post_date FROM {$wpdb->posts} WHERE post_status = 'trash' LIMIT 500", \ARRAY_A ); } return $data; } public function download_backup() { check_admin_referer( 'qwsc_db_backup' ); if ( ! current_user_can( 'manage_options' ) ) { wp_die( esc_html__( 'Unauthorized', 'qw-optimize' ), '', array( 'response' => 403 ) ); } $types = array( 'revisions', 'autodrafts', 'spam', 'trash_posts' ); $payload = wp_json_encode( $this->build_backup( $types ) ); nocache_headers(); header( 'Content-Type: application/json; charset=utf-8' ); header( 'Content-Disposition: attachment; filename=qwsc-db-backup-' . gmdate( 'Ymd-His' ) . '.json' ); echo $payload; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped exit; } public function optimize_tables() { check_admin_referer( 'qwsc_db_optimize' ); if ( ! current_user_can( 'manage_options' ) ) { wp_die( esc_html__( 'Unauthorized', 'qw-optimize' ), '', array( 'response' => 403 ) ); } global $wpdb; $tables = array( $wpdb->posts, $wpdb->postmeta, $wpdb->comments, $wpdb->commentmeta, $wpdb->options, $wpdb->terms, $wpdb->term_taxonomy, $wpdb->termmeta ); foreach ( $tables as $table ) { // Table names come from $wpdb->* properties only (not user input). // OPTIMIZE TABLE is DDL — MySQL does not allow prepared placeholders for identifiers. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- safe $wpdb table names; DDL cannot use prepare(). $wpdb->query( "OPTIMIZE TABLE `{$table}`" ); } wp_safe_redirect( add_query_arg( array( 'page' => 'qw-speed-cache-database', 'optimized' => '1' ), admin_url( 'admin.php' ) ) ); exit; } public function run_cleanup() { check_admin_referer( 'qwsc_db_cleanup' ); if ( ! current_user_can( 'manage_options' ) ) { wp_die( esc_html__( 'Unauthorized', 'qw-optimize' ), '', array( 'response' => 403 ) ); } $types = isset( $_POST['clean'] ) ? array_map( 'sanitize_text_field', (array) wp_unslash( $_POST['clean'] ) ) : array(); if ( ! empty( $_POST['qwsc_backup_first'] ) ) { $payload = wp_json_encode( $this->build_backup( $types ) ); nocache_headers(); header( 'Content-Type: application/json; charset=utf-8' ); header( 'Content-Disposition: attachment; filename=qwsc-db-backup-' . gmdate( 'Ymd-His' ) . '.json' ); echo $payload; // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped exit; } $this->clean( $types ); wp_safe_redirect( add_query_arg( array( 'page' => 'qw-speed-cache-database', 'cleaned' => '1' ), admin_url( 'admin.php' ) ) ); exit; } public function scheduled() { $this->clean( array( 'revisions', 'autodrafts', 'spam', 'transients' ) ); } /** * @param string[] $types Types. */ private function clean( $types ) { global $wpdb; if ( in_array( 'revisions', $types, true ) ) { $ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision' LIMIT 500" ); foreach ( $ids as $id ) { wp_delete_post_revision( (int) $id ); } } if ( in_array( 'autodrafts', $types, true ) ) { $ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'auto-draft' LIMIT 500" ); foreach ( $ids as $id ) { wp_delete_post( (int) $id, true ); } } if ( in_array( 'spam', $types, true ) ) { $ids = $wpdb->get_col( "SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved = 'spam' LIMIT 500" ); foreach ( $ids as $id ) { wp_delete_comment( (int) $id, true ); } } if ( in_array( 'trash_posts', $types, true ) ) { $ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'trash' LIMIT 200" ); foreach ( $ids as $id ) { wp_delete_post( (int) $id, true ); } } if ( in_array( 'transients', $types, true ) ) { $timeouts = $wpdb->get_col( "SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP() LIMIT 500" ); foreach ( $timeouts as $name ) { $key = str_replace( '_transient_timeout_', '', $name ); delete_option( $name ); delete_option( '_transient_' . $key ); } } if ( in_array( 'orphaned_postmeta', $types, true ) ) { $wpdb->query( "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL LIMIT 1000" ); } if ( in_array( 'orphaned_commentmeta', $types, true ) ) { $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 LIMIT 1000" ); } if ( in_array( 'orphaned_relationships', $types, true ) ) { $wpdb->query( "DELETE tr FROM {$wpdb->term_relationships} tr LEFT JOIN {$wpdb->posts} p ON p.ID = tr.object_id WHERE p.ID IS NULL LIMIT 1000" ); } if ( in_array( 'action_scheduler', $types, true ) ) { $as = $wpdb->prefix . 'actionscheduler_actions'; if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $as ) ) === $as ) { $wpdb->query( "DELETE FROM {$as} WHERE status IN ('complete','failed','canceled') AND scheduled_date_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY) LIMIT 1000" ); } } if ( in_array( 'wc_sessions', $types, true ) ) { $sess = $wpdb->prefix . 'woocommerce_sessions'; if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $sess ) ) === $sess ) { $wpdb->query( "DELETE FROM {$sess} WHERE session_expiry > 0 AND session_expiry < UNIX_TIMESTAMP() LIMIT 1000" ); } } } /** * Human-readable table overhead for admin. * * @return string */ public function overhead_label() { $c = $this->get_counts(); $bytes = (int) ( $c['table_overhead'] ?? 0 ); if ( $bytes <= 0 ) { return '0'; } return size_format( $bytes ); } }