| 1 |
<?php |
| 2 |
/** |
| 3 |
* Database_Cleaner — read-first scan + destructive cleanup for common |
| 4 |
* WordPress bloat: revisions, auto-drafts, trashed posts/comments, |
| 5 |
* spam, expired transients, orphan metadata, and OPTIMIZE TABLE. |
| 6 |
* |
| 7 |
* Two-phase API on purpose: |
| 8 |
* - scan() is read-only — returns counts + rough size estimates so |
| 9 |
* the dashboard can show "this is what cleanup would remove" |
| 10 |
* BEFORE the user clicks Clean. Cheap enough to run every panel |
| 11 |
* mount. |
| 12 |
* - clean( $types ) is destructive — accepts a whitelist of type |
| 13 |
* keys, runs the DELETE / OPTIMIZE for each, returns the |
| 14 |
* post-cleanup counts so the UI can refresh. |
| 15 |
* |
| 16 |
* All queries go through $wpdb->prepare or are string-literal SQL on |
| 17 |
* static table-name interpolations (sanitized via $wpdb->prefix). |
| 18 |
* Never trusts user input directly in SQL. |
| 19 |
* |
| 20 |
* @package XSpeed |
| 21 |
*/ |
| 22 |
|
| 23 |
declare(strict_types=1); |
| 24 |
|
| 25 |
namespace XSpeed; |
| 26 |
|
| 27 |
defined( 'ABSPATH' ) || exit; |
| 28 |
|
| 29 |
final class Database_Cleaner { |
| 30 |
|
| 31 |
public const CRON_HOOK = 'xspeed_database_cleanup'; |
| 32 |
|
| 33 |
/** |
| 34 |
* Cleanup types. Each entry: |
| 35 |
* label — human description |
| 36 |
* scan_sql_tpl — SELECT for counting candidates (sprintf |
| 37 |
* placeholder for table prefix) |
| 38 |
* clean_sql_tpl — DELETE for the same set |
| 39 |
* |
| 40 |
* Auto-drafts use a 7-day grace window so in-progress drafts aren't |
| 41 |
* destroyed mid-edit. Expired transients use the option_value |
| 42 |
* column for both `_transient_timeout_*` and `_site_transient_timeout_*`. |
| 43 |
* |
| 44 |
* @return array<string,array{label:string,scan_sql_tpl:string,clean_sql_tpl:string}> |
| 45 |
*/ |
| 46 |
private static function types(): array { |
| 47 |
return array( |
| 48 |
'post_revisions' => array( |
| 49 |
'label' => 'Post Revisions', |
| 50 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sposts WHERE post_type = 'revision'", |
| 51 |
'clean_sql_tpl' => "DELETE FROM %1\$sposts WHERE post_type = 'revision'", |
| 52 |
), |
| 53 |
'auto_drafts' => array( |
| 54 |
'label' => 'Auto-Drafts (older than 7 days)', |
| 55 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sposts WHERE post_status = 'auto-draft' AND post_modified < DATE_SUB( NOW(), INTERVAL 7 DAY )", |
| 56 |
'clean_sql_tpl' => "DELETE FROM %1\$sposts WHERE post_status = 'auto-draft' AND post_modified < DATE_SUB( NOW(), INTERVAL 7 DAY )", |
| 57 |
), |
| 58 |
'trashed_posts' => array( |
| 59 |
'label' => 'Trashed Posts', |
| 60 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sposts WHERE post_status = 'trash'", |
| 61 |
'clean_sql_tpl' => "DELETE FROM %1\$sposts WHERE post_status = 'trash'", |
| 62 |
), |
| 63 |
'spam_comments' => array( |
| 64 |
'label' => 'Spam Comments', |
| 65 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$scomments WHERE comment_approved = 'spam'", |
| 66 |
'clean_sql_tpl' => "DELETE FROM %1\$scomments WHERE comment_approved = 'spam'", |
| 67 |
), |
| 68 |
'trashed_comments' => array( |
| 69 |
'label' => 'Trashed Comments', |
| 70 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$scomments WHERE comment_approved = 'trash'", |
| 71 |
'clean_sql_tpl' => "DELETE FROM %1\$scomments WHERE comment_approved = 'trash'", |
| 72 |
), |
| 73 |
'expired_transients' => array( |
| 74 |
'label' => 'Expired Transients', |
| 75 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$soptions WHERE option_name LIKE '_transient_timeout_%%' AND CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP()", |
| 76 |
'clean_sql_tpl' => "DELETE a, b FROM %1\$soptions a INNER JOIN %1\$soptions b ON b.option_name = REPLACE(a.option_name, '_transient_timeout_', '_transient_') WHERE a.option_name LIKE '_transient_timeout_%%' AND CAST(a.option_value AS UNSIGNED) < UNIX_TIMESTAMP()", |
| 77 |
), |
| 78 |
'orphan_postmeta' => array( |
| 79 |
'label' => 'Orphan Post Meta', |
| 80 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$spostmeta pm LEFT JOIN %1\$sposts p ON p.ID = pm.post_id WHERE p.ID IS NULL", |
| 81 |
'clean_sql_tpl' => "DELETE pm FROM %1\$spostmeta pm LEFT JOIN %1\$sposts p ON p.ID = pm.post_id WHERE p.ID IS NULL", |
| 82 |
), |
| 83 |
'orphan_commentmeta' => array( |
| 84 |
'label' => 'Orphan Comment Meta', |
| 85 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$scommentmeta cm LEFT JOIN %1\$scomments c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL", |
| 86 |
'clean_sql_tpl' => "DELETE cm FROM %1\$scommentmeta cm LEFT JOIN %1\$scomments c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL", |
| 87 |
), |
| 88 |
); |
| 89 |
} |
| 90 |
|
| 91 |
/** |
| 92 |
* Read-only scan. Returns array keyed by type slug: |
| 93 |
* [ slug => [ label => …, count => N ] ] |
| 94 |
* |
| 95 |
* @param \wpdb|null $wpdb_in Injectable for tests; defaults to global. |
| 96 |
*/ |
| 97 |
public static function scan( $wpdb_in = null ): array { |
| 98 |
$wpdb = self::wpdb( $wpdb_in ); |
| 99 |
$out = array(); |
| 100 |
foreach ( self::types() as $key => $spec ) { |
| 101 |
$sql = sprintf( $spec['scan_sql_tpl'], $wpdb->prefix ); |
| 102 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL is built from a hardcoded template registry (types()); only $wpdb->prefix is interpolated. Read-only scan called from a manage_options REST endpoint; results aren't cached on purpose so the user always sees current row counts. |
| 103 |
$count = (int) $wpdb->get_var( $sql ); |
| 104 |
$out[ $key ] = array( |
| 105 |
'label' => $spec['label'], |
| 106 |
'count' => $count, |
| 107 |
); |
| 108 |
} |
| 109 |
return $out; |
| 110 |
} |
| 111 |
|
| 112 |
/** |
| 113 |
* Destructive cleanup for the supplied list of type slugs. Anything |
| 114 |
* not in the static types() table is silently skipped — never run |
| 115 |
* arbitrary SQL because someone POSTed an unexpected slug. |
| 116 |
* |
| 117 |
* Returns the new scan() shape so the UI can refresh in one |
| 118 |
* round trip, plus a per-type `affected` count of rows removed. |
| 119 |
* |
| 120 |
* @param string[] $types |
| 121 |
*/ |
| 122 |
public static function clean( array $types, $wpdb_in = null ): array { |
| 123 |
$wpdb = self::wpdb( $wpdb_in ); |
| 124 |
$registry = self::types(); |
| 125 |
$results = array(); |
| 126 |
$total = 0; |
| 127 |
|
| 128 |
foreach ( $types as $key ) { |
| 129 |
if ( ! isset( $registry[ $key ] ) ) { |
| 130 |
continue; |
| 131 |
} |
| 132 |
$sql = sprintf( $registry[ $key ]['clean_sql_tpl'], $wpdb->prefix ); |
| 133 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL built from a hardcoded template registry (types()); only $wpdb->prefix is interpolated. Destructive cleanup explicitly bypasses the object cache. |
| 134 |
$affected = (int) $wpdb->query( $sql ); |
| 135 |
if ( $affected < 0 ) { |
| 136 |
$affected = 0; // wpdb returns -1 on error. |
| 137 |
} |
| 138 |
$results[ $key ] = $affected; |
| 139 |
$total += $affected; |
| 140 |
} |
| 141 |
|
| 142 |
if ( $total > 0 && class_exists( '\\XSpeed\\Activity_Log' ) ) { |
| 143 |
Activity_Log::record( |
| 144 |
'database_cleaned', |
| 145 |
sprintf( 'Database cleanup removed %d row%s across %d table(s).', $total, 1 === $total ? '' : 's', count( $results ) ), |
| 146 |
Activity_Log::INFO |
| 147 |
); |
| 148 |
} |
| 149 |
|
| 150 |
return array( |
| 151 |
'cleaned' => $results, |
| 152 |
'scan' => self::scan( $wpdb_in ), |
| 153 |
); |
| 154 |
} |
| 155 |
|
| 156 |
/** |
| 157 |
* OPTIMIZE TABLE on every WordPress core table (and any with the |
| 158 |
* site's prefix). Returns the table names + per-table status. |
| 159 |
*/ |
| 160 |
public static function optimize_tables( $wpdb_in = null ): array { |
| 161 |
$wpdb = self::wpdb( $wpdb_in ); |
| 162 |
$prefix = $wpdb->prefix; |
| 163 |
// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prefix from $wpdb, not user input. SHOW TABLES is metadata, not cached. |
| 164 |
$tables = $wpdb->get_col( "SHOW TABLES LIKE '" . esc_sql( $prefix ) . "%'" ); |
| 165 |
if ( ! is_array( $tables ) ) { |
| 166 |
return array(); |
| 167 |
} |
| 168 |
$out = array(); |
| 169 |
foreach ( $tables as $table ) { |
| 170 |
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name comes from SHOW TABLES on our prefix; safe to interpolate. OPTIMIZE TABLE is a maintenance DDL, not cacheable. |
| 171 |
$ok = $wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table ) . '`' ); |
| 172 |
$out[ $table ] = false !== $ok; |
| 173 |
} |
| 174 |
|
| 175 |
if ( ! empty( $out ) && class_exists( '\\XSpeed\\Activity_Log' ) ) { |
| 176 |
Activity_Log::record( |
| 177 |
'database_optimized', |
| 178 |
sprintf( 'Database tables optimized (%d).', count( $out ) ), |
| 179 |
Activity_Log::INFO |
| 180 |
); |
| 181 |
} |
| 182 |
|
| 183 |
return $out; |
| 184 |
} |
| 185 |
|
| 186 |
/** |
| 187 |
* Register / cancel the cleanup cron schedule. Called from the |
| 188 |
* DatabaseModule's settings-change hook. |
| 189 |
*/ |
| 190 |
public static function apply_schedule( string $schedule, array $types ): void { |
| 191 |
wp_clear_scheduled_hook( self::CRON_HOOK ); |
| 192 |
if ( in_array( $schedule, array( 'hourly', 'daily', 'weekly' ), true ) && ! empty( $types ) ) { |
| 193 |
wp_schedule_event( time() + HOUR_IN_SECONDS, $schedule, self::CRON_HOOK ); |
| 194 |
} |
| 195 |
} |
| 196 |
|
| 197 |
/** |
| 198 |
* Cron handler — pulls the configured included_types from settings |
| 199 |
* and runs clean() on them. Idempotent; if nothing's eligible, the |
| 200 |
* underlying DELETE runs but affects 0 rows. |
| 201 |
*/ |
| 202 |
public static function cron_tick(): void { |
| 203 |
$opts = Settings_Manager::get( 'database' ); |
| 204 |
$types = is_array( $opts['included_types'] ?? null ) ? $opts['included_types'] : array(); |
| 205 |
if ( empty( $types ) ) { |
| 206 |
return; |
| 207 |
} |
| 208 |
self::clean( $types ); |
| 209 |
} |
| 210 |
|
| 211 |
private static function wpdb( $injected ) { |
| 212 |
if ( null !== $injected ) { |
| 213 |
return $injected; |
| 214 |
} |
| 215 |
global $wpdb; |
| 216 |
return $wpdb; |
| 217 |
} |
| 218 |
|
| 219 |
/** |
| 220 |
* Public list of type metadata for the dashboard payload, so the |
| 221 |
* React side doesn't have to hardcode labels. |
| 222 |
* |
| 223 |
* @return array<string,string> |
| 224 |
*/ |
| 225 |
public static function type_labels(): array { |
| 226 |
$out = array(); |
| 227 |
foreach ( self::types() as $key => $spec ) { |
| 228 |
$out[ $key ] = $spec['label']; |
| 229 |
} |
| 230 |
return $out; |
| 231 |
} |
| 232 |
} |
| 233 |
|
