| 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, removes what each one matches, returns the post-cleanup |
| 14 |
* counts so the UI can refresh. |
| 15 |
* |
| 16 |
* Posts and comments are deleted through wp_delete_post() and |
| 17 |
* wp_delete_comment(), in bounded batches, never by raw DELETE. A raw |
| 18 |
* DELETE skipped every deletion hook, left postmeta and term |
| 19 |
* relationships behind, and held one unbounded lock on wp_posts (#449, |
| 20 |
* #237). Rows WordPress has no deletion API for — expired transients, |
| 21 |
* orphan meta, orphan term relationships — are still raw SQL. |
| 22 |
* |
| 23 |
* All SQL comes from the hardcoded template registry in types(). The |
| 24 |
* only values interpolated are $wpdb->prefix, integers cast with %d (the |
| 25 |
* batch cursor and LIMIT), and the names of registered taxonomies passed |
| 26 |
* through esc_sql(). Never trusts user input directly in SQL. |
| 27 |
* |
| 28 |
* @package XSpeed |
| 29 |
*/ |
| 30 |
|
| 31 |
declare(strict_types=1); |
| 32 |
|
| 33 |
namespace XSpeed; |
| 34 |
|
| 35 |
defined( 'ABSPATH' ) || exit; |
| 36 |
|
| 37 |
final class Database_Cleaner { |
| 38 |
|
| 39 |
public const CRON_HOOK = 'xspeed_database_cleanup'; |
| 40 |
|
| 41 |
/** |
| 42 |
* Where the scheduled clean stopped when its time budget ran out. |
| 43 |
* |
| 44 |
* Shape: { types_hash, type, after_id }. Only cron_tick() reads or writes |
| 45 |
* it. A manual, REST, CLI or MCP clean never does: those delete what the |
| 46 |
* caller just saw, in the same request, and must not leave work behind |
| 47 |
* for a later tick to finish. An MCP clean in particular is authorised by |
| 48 |
* a confirm_token bound to one scan, and a resumed run on a later tick |
| 49 |
* would delete rows that became eligible after that confirmation. |
| 50 |
*/ |
| 51 |
public const CURSOR_OPTION = 'xspeed_db_clean_cursor'; |
| 52 |
|
| 53 |
/** Rows fetched per SELECT for the types deleted one at a time. */ |
| 54 |
private const BATCH = 100; |
| 55 |
|
| 56 |
/** Seconds an interactive clean (REST, CLI, MCP) may spend deleting. */ |
| 57 |
private const BUDGET_INTERACTIVE = 20; |
| 58 |
|
| 59 |
/** Seconds one scheduled tick may spend deleting. */ |
| 60 |
private const BUDGET_CRON = 10; |
| 61 |
|
| 62 |
/** |
| 63 |
* Cleanup types, in the order clean() runs them. Each entry: |
| 64 |
* label — human description |
| 65 |
* scan_sql_tpl — SELECT COUNT(*) of candidates. %1$s is the table |
| 66 |
* prefix, %2$s the post-taxonomy IN list. |
| 67 |
* select_ids_tpl — for types deleted through the WordPress API: one |
| 68 |
* page of candidate ids. %2$d is the id to start |
| 69 |
* after, %3$d the page size. |
| 70 |
* delete — 'post' or 'comment': which API deletes an id. |
| 71 |
* clean_sql_tpl — for raw types: the DELETE for the same set. |
| 72 |
* |
| 73 |
* The slugs are stored in users' `included_types` setting. Renaming one |
| 74 |
* strands that stored value: clean() skips unknown slugs, so a renamed |
| 75 |
* slug would turn a scheduled clean into a silent no-op. Add, never |
| 76 |
* rename. |
| 77 |
* |
| 78 |
* Order matters. The orphan sweeps run after the post and comment |
| 79 |
* deletes, and the resume cursor walks this list. |
| 80 |
* |
| 81 |
* Auto-drafts use a 7-day grace window so in-progress drafts aren't |
| 82 |
* destroyed mid-edit. Expired transients use the option_value |
| 83 |
* column for both `_transient_timeout_*` and `_site_transient_timeout_*`. |
| 84 |
* |
| 85 |
* @return array<string,array<string,string>> |
| 86 |
*/ |
| 87 |
private static function types(): array { |
| 88 |
return array( |
| 89 |
'post_revisions' => array( |
| 90 |
'label' => 'Post Revisions', |
| 91 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sposts WHERE post_type = 'revision'", |
| 92 |
'select_ids_tpl' => "SELECT ID FROM %1\$sposts WHERE post_type = 'revision' AND ID > %2\$d ORDER BY ID ASC LIMIT %3\$d", |
| 93 |
'delete' => 'post', |
| 94 |
), |
| 95 |
'auto_drafts' => array( |
| 96 |
'label' => 'Auto-Drafts (older than 7 days)', |
| 97 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sposts WHERE post_status = 'auto-draft' AND post_modified < DATE_SUB( NOW(), INTERVAL 7 DAY )", |
| 98 |
'select_ids_tpl' => "SELECT ID FROM %1\$sposts WHERE post_status = 'auto-draft' AND post_modified < DATE_SUB( NOW(), INTERVAL 7 DAY ) AND ID > %2\$d ORDER BY ID ASC LIMIT %3\$d", |
| 99 |
'delete' => 'post', |
| 100 |
), |
| 101 |
// A trashed ATTACHMENT (only possible with MEDIA_TRASH on) loses |
| 102 |
// its files from uploads/ here, because wp_delete_post() hands |
| 103 |
// attachments to wp_delete_attachment(). That is what core's own |
| 104 |
// "Empty Trash" does with the same row. The raw DELETE this |
| 105 |
// replaced removed the row and stranded the files. |
| 106 |
'trashed_posts' => array( |
| 107 |
'label' => 'Trashed Posts', |
| 108 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sposts WHERE post_status = 'trash'", |
| 109 |
'select_ids_tpl' => "SELECT ID FROM %1\$sposts WHERE post_status = 'trash' AND ID > %2\$d ORDER BY ID ASC LIMIT %3\$d", |
| 110 |
'delete' => 'post', |
| 111 |
), |
| 112 |
'spam_comments' => array( |
| 113 |
'label' => 'Spam Comments', |
| 114 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$scomments WHERE comment_approved = 'spam'", |
| 115 |
'select_ids_tpl' => "SELECT comment_ID FROM %1\$scomments WHERE comment_approved = 'spam' AND comment_ID > %2\$d ORDER BY comment_ID ASC LIMIT %3\$d", |
| 116 |
'delete' => 'comment', |
| 117 |
), |
| 118 |
'trashed_comments' => array( |
| 119 |
'label' => 'Trashed Comments', |
| 120 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$scomments WHERE comment_approved = 'trash'", |
| 121 |
'select_ids_tpl' => "SELECT comment_ID FROM %1\$scomments WHERE comment_approved = 'trash' AND comment_ID > %2\$d ORDER BY comment_ID ASC LIMIT %3\$d", |
| 122 |
'delete' => 'comment', |
| 123 |
), |
| 124 |
'expired_transients' => array( |
| 125 |
'label' => 'Expired Transients', |
| 126 |
// Scan counts one row per EXPIRED transient — the timeout |
| 127 |
// row — across BOTH the normal (_transient_timeout_*) and the |
| 128 |
// site/network (_site_transient_timeout_*) families. The |
| 129 |
// parentheses around the two LIKEs are required so the AND |
| 130 |
// expiry condition applies to both, not just the second. |
| 131 |
// (FBS-82149 Bug 1: site transients were never matched; |
| 132 |
// Bug 3: scan counts logical transients = 1 per timeout row.) |
| 133 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$soptions WHERE ( option_name LIKE '_transient_timeout_%%' OR option_name LIKE '_site_transient_timeout_%%' ) AND CAST( option_value AS UNSIGNED ) < UNIX_TIMESTAMP()", |
| 134 |
// Clean deletes the timeout row AND its value sibling via a |
| 135 |
// LEFT JOIN so an ORPHAN timeout row (value sibling missing) |
| 136 |
// is still removed — an INNER JOIN silently kept those, so a |
| 137 |
// "clean" never drove the scan count to zero. REPLACE maps |
| 138 |
// both families to their value-key name. (FBS-82149 Bug 2.) |
| 139 |
'clean_sql_tpl' => "DELETE a, b FROM %1\$soptions a LEFT JOIN %1\$soptions b ON b.option_name = REPLACE( REPLACE( a.option_name, '_site_transient_timeout_', '_site_transient_' ), '_transient_timeout_', '_transient_' ) WHERE ( a.option_name LIKE '_transient_timeout_%%' OR a.option_name LIKE '_site_transient_timeout_%%' ) AND CAST( a.option_value AS UNSIGNED ) < UNIX_TIMESTAMP()", |
| 140 |
), |
| 141 |
'orphan_postmeta' => array( |
| 142 |
'label' => 'Orphan Post Meta', |
| 143 |
'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", |
| 144 |
'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", |
| 145 |
), |
| 146 |
'orphan_commentmeta' => array( |
| 147 |
'label' => 'Orphan Comment Meta', |
| 148 |
'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", |
| 149 |
'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", |
| 150 |
), |
| 151 |
// A term relationship's object_id is a post id only when the |
| 152 |
// taxonomy belongs to post types. link_category points at |
| 153 |
// wp_links, and plugins attach taxonomies to users (BuddyPress |
| 154 |
// member types, for one), so "no such post" is not "orphan" for |
| 155 |
// those. The sweep is limited to taxonomies registered for post |
| 156 |
// types only; an unregistered taxonomy is left alone. |
| 157 |
'orphan_term_relationships' => array( |
| 158 |
'label' => 'Orphan Term Relationships', |
| 159 |
'scan_sql_tpl' => "SELECT COUNT(*) FROM %1\$sterm_relationships tr INNER JOIN %1\$sterm_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id LEFT JOIN %1\$sposts p ON p.ID = tr.object_id WHERE p.ID IS NULL AND tt.taxonomy IN ( %2\$s )", |
| 160 |
'clean_sql_tpl' => "DELETE tr FROM %1\$sterm_relationships tr INNER JOIN %1\$sterm_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id LEFT JOIN %1\$sposts p ON p.ID = tr.object_id WHERE p.ID IS NULL AND tt.taxonomy IN ( %2\$s )", |
| 161 |
), |
| 162 |
); |
| 163 |
} |
| 164 |
|
| 165 |
/** |
| 166 |
* Read-only scan. Returns array keyed by type slug: |
| 167 |
* [ slug => [ label => …, count => N ] ] |
| 168 |
* |
| 169 |
* @param \wpdb|null $wpdb_in Injectable for tests; defaults to global. |
| 170 |
*/ |
| 171 |
public static function scan( $wpdb_in = null ): array { |
| 172 |
$wpdb = self::wpdb( $wpdb_in ); |
| 173 |
$out = array(); |
| 174 |
foreach ( self::types() as $key => $spec ) { |
| 175 |
$out[ $key ] = array( |
| 176 |
'label' => $spec['label'], |
| 177 |
'count' => self::count_for( $spec, $wpdb ), |
| 178 |
); |
| 179 |
} |
| 180 |
return $out; |
| 181 |
} |
| 182 |
|
| 183 |
/** |
| 184 |
* Run a type's scan SQL and return its COUNT. Shared by scan() and by |
| 185 |
* clean() (before/after) so the "rows removed" number is always in the |
| 186 |
* same unit the UI shows. (FBS-82149 Bug 3.) |
| 187 |
* |
| 188 |
* @param array{scan_sql_tpl:string} $spec |
| 189 |
* @param \wpdb $wpdb |
| 190 |
*/ |
| 191 |
private static function count_for( array $spec, $wpdb ): int { |
| 192 |
$sql = self::fill( $spec['scan_sql_tpl'], $wpdb ); |
| 193 |
// 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 and esc_sql()'d registered taxonomy names are interpolated. Read-only count; not cached so the user always sees current row counts. |
| 194 |
return (int) $wpdb->get_var( $sql ); |
| 195 |
} |
| 196 |
|
| 197 |
/** |
| 198 |
* Destructive cleanup for the supplied list of type slugs. Anything |
| 199 |
* not in the static types() table is skipped and named in `skipped` — |
| 200 |
* never run arbitrary SQL because someone POSTed an unexpected slug. |
| 201 |
* |
| 202 |
* Types run in registry order, whatever order the caller listed them. |
| 203 |
* Posts and comments go one id at a time through the WordPress API, a |
| 204 |
* page of ids per SELECT, until nothing matches or the time budget is |
| 205 |
* spent. At least one id is always attempted, so a zero budget still |
| 206 |
* makes progress. |
| 207 |
* |
| 208 |
* Options: |
| 209 |
* batch ids per SELECT (default 100) |
| 210 |
* budget_seconds time allowed (default 20, or 10 with resume) |
| 211 |
* resume start from, and save to, the scheduled-clean cursor. |
| 212 |
* Only cron_tick() passes this. |
| 213 |
* |
| 214 |
* Returns: |
| 215 |
* cleaned per type, items removed |
| 216 |
* remaining per requested type, what still matches afterwards |
| 217 |
* partial true when the budget ran out before every type finished |
| 218 |
* skipped requested slugs that are not cleanup types |
| 219 |
* scan the full scan() after the clean, so the UI can refresh |
| 220 |
* |
| 221 |
* @param string[] $types |
| 222 |
* @param \wpdb|null $wpdb_in Injectable for tests; defaults to global. |
| 223 |
* @param array $opts |
| 224 |
*/ |
| 225 |
public static function clean( array $types, $wpdb_in = null, array $opts = array() ): array { |
| 226 |
$wpdb = self::wpdb( $wpdb_in ); |
| 227 |
$registry = self::types(); |
| 228 |
$resume = ! empty( $opts['resume'] ); |
| 229 |
$batch = max( 1, (int) ( $opts['batch'] ?? self::BATCH ) ); |
| 230 |
$budget = (float) ( $opts['budget_seconds'] ?? ( $resume ? self::BUDGET_CRON : self::BUDGET_INTERACTIVE ) ); |
| 231 |
$deadline = microtime( true ) + max( 0.0, $budget ); |
| 232 |
|
| 233 |
$requested = array_values( array_unique( array_map( 'strval', array_filter( $types, 'is_scalar' ) ) ) ); |
| 234 |
$keys = array_values( array_intersect( array_keys( $registry ), $requested ) ); |
| 235 |
$skipped = array_values( array_diff( $requested, $keys ) ); |
| 236 |
|
| 237 |
$start = 0; |
| 238 |
$after = 0; |
| 239 |
$hash = self::types_hash( $keys ); |
| 240 |
if ( $resume ) { |
| 241 |
list( $start, $after ) = self::read_cursor( $hash, $keys ); |
| 242 |
} |
| 243 |
|
| 244 |
$results = array(); |
| 245 |
$total = 0; |
| 246 |
$partial = false; |
| 247 |
$stop_at = null; |
| 248 |
$progress = false; |
| 249 |
|
| 250 |
for ( $i = $start, $n = count( $keys ); $i < $n; $i++ ) { |
| 251 |
$key = $keys[ $i ]; |
| 252 |
$spec = $registry[ $key ]; |
| 253 |
|
| 254 |
if ( $progress && microtime( true ) >= $deadline ) { |
| 255 |
$partial = true; |
| 256 |
$stop_at = array( $key, 0 ); |
| 257 |
break; |
| 258 |
} |
| 259 |
|
| 260 |
if ( isset( $spec['select_ids_tpl'] ) ) { |
| 261 |
list( $removed, $stopped_after ) = self::delete_batched( $spec, $wpdb, $after, $batch, $deadline, $progress ); |
| 262 |
} else { |
| 263 |
$removed = self::delete_raw( $spec, $wpdb ); |
| 264 |
$stopped_after = null; |
| 265 |
$progress = true; |
| 266 |
} |
| 267 |
|
| 268 |
$results[ $key ] = $removed; |
| 269 |
$total += $removed; |
| 270 |
|
| 271 |
if ( null !== $stopped_after ) { |
| 272 |
$partial = true; |
| 273 |
$stop_at = array( $key, $stopped_after ); |
| 274 |
break; |
| 275 |
} |
| 276 |
$after = 0; |
| 277 |
} |
| 278 |
|
| 279 |
if ( $resume ) { |
| 280 |
if ( null !== $stop_at ) { |
| 281 |
self::write_cursor( $hash, $stop_at[0], $stop_at[1] ); |
| 282 |
} else { |
| 283 |
// Last type finished: the cycle wraps, and the next tick |
| 284 |
// starts again from the first type and id 0. Rows that became |
| 285 |
// eligible behind the cursor are picked up then. |
| 286 |
self::reset_cursor(); |
| 287 |
} |
| 288 |
} |
| 289 |
|
| 290 |
if ( $total > 0 && class_exists( '\\XSpeed\\Activity_Log' ) ) { |
| 291 |
Activity_Log::record( |
| 292 |
'database_cleaned', |
| 293 |
sprintf( 'Database cleanup removed %d row%s across %d table(s).', $total, 1 === $total ? '' : 's', count( $results ) ), |
| 294 |
Activity_Log::INFO |
| 295 |
); |
| 296 |
} |
| 297 |
|
| 298 |
$scan = self::scan( $wpdb ); |
| 299 |
$remaining = array(); |
| 300 |
foreach ( $keys as $key ) { |
| 301 |
$remaining[ $key ] = (int) ( $scan[ $key ]['count'] ?? 0 ); |
| 302 |
} |
| 303 |
|
| 304 |
return array( |
| 305 |
'cleaned' => $results, |
| 306 |
'remaining' => $remaining, |
| 307 |
'partial' => $partial, |
| 308 |
'skipped' => $skipped, |
| 309 |
'scan' => $scan, |
| 310 |
); |
| 311 |
} |
| 312 |
|
| 313 |
/** |
| 314 |
* Delete one type's matches through the WordPress API, a page at a time. |
| 315 |
* |
| 316 |
* wp_delete_post( $id, true ) fires before_delete_post / delete_post / |
| 317 |
* deleted_post, removes the post's meta, term relationships, comments and |
| 318 |
* revisions, and moves its attachments and same-type children up to its |
| 319 |
* parent. detach_children() then clears the parent of any other child. For a |
| 320 |
* trashed attachment it also deletes the files. wp_delete_comment( $id, |
| 321 |
* true ) does the same for a comment and its meta. Each fires every |
| 322 |
* other plugin's hooks too, which is the point, and is why the run is |
| 323 |
* bounded by a time budget rather than left to finish. |
| 324 |
* |
| 325 |
* xSpeed's own purge on before_delete_post already ignores revisions, |
| 326 |
* auto-drafts and trashed posts (Cache::post_change_is_cacheable_content), |
| 327 |
* so emptying these does not sweep the page cache per row. |
| 328 |
* |
| 329 |
* The cursor moves past every id attempted, deleted or not. A row a |
| 330 |
* plugin refuses to delete (pre_delete_post) is stepped over rather than |
| 331 |
* fetched forever. |
| 332 |
* |
| 333 |
* @param array $spec Registry entry with select_ids_tpl + delete. |
| 334 |
* @param \wpdb $wpdb |
| 335 |
* @param int $after Start after this id. |
| 336 |
* @param int $batch Ids per SELECT. |
| 337 |
* @param float $deadline microtime() at which to stop. |
| 338 |
* @param bool $progress Set once any id has been attempted. |
| 339 |
* @return array{0:int,1:int|null} Removed count, and the last id attempted |
| 340 |
* when the budget stopped the run (null |
| 341 |
* when the type finished). |
| 342 |
*/ |
| 343 |
private static function delete_batched( array $spec, $wpdb, int $after, int $batch, float $deadline, bool &$progress ): array { |
| 344 |
$removed = 0; |
| 345 |
while ( true ) { |
| 346 |
$sql = sprintf( $spec['select_ids_tpl'], $wpdb->prefix, $after, $batch ); |
| 347 |
// 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 and two integers formatted with %d are interpolated. Candidate ids must be read live. |
| 348 |
$ids = $wpdb->get_col( $sql ); |
| 349 |
if ( ! is_array( $ids ) || array() === $ids ) { |
| 350 |
return array( $removed, null ); |
| 351 |
} |
| 352 |
|
| 353 |
foreach ( $ids as $id ) { |
| 354 |
$id = (int) $id; |
| 355 |
$after = $id; |
| 356 |
if ( 'comment' === $spec['delete'] ) { |
| 357 |
$ok = wp_delete_comment( $id, true ); |
| 358 |
} else { |
| 359 |
$ok = wp_delete_post( $id, true ); |
| 360 |
if ( $ok ) { |
| 361 |
self::detach_children( $id, $wpdb ); |
| 362 |
} |
| 363 |
} |
| 364 |
if ( $ok ) { |
| 365 |
++$removed; |
| 366 |
} |
| 367 |
$progress = true; |
| 368 |
if ( microtime( true ) >= $deadline ) { |
| 369 |
return array( $removed, $after ); |
| 370 |
} |
| 371 |
} |
| 372 |
|
| 373 |
if ( count( $ids ) < $batch ) { |
| 374 |
return array( $removed, null ); |
| 375 |
} |
| 376 |
} |
| 377 |
} |
| 378 |
|
| 379 |
/** |
| 380 |
* Point the children a deleted post left behind at no parent. |
| 381 |
* |
| 382 |
* wp_delete_post() moves the deleted post's attachments, and its children |
| 383 |
* of the same hierarchical post type, up to its own parent. Plugins that |
| 384 |
* own their children (WooCommerce variations) delete them on |
| 385 |
* before_delete_post. A child of any other post type keeps a post_parent |
| 386 |
* naming a post that no longer exists: a course's lessons, for example |
| 387 |
* (#237). |
| 388 |
* |
| 389 |
* Those are set to 0, core's "no parent". They are not deleted: they may |
| 390 |
* be published, and a scheduled cleaner must not remove live content. Not |
| 391 |
* moved to the grandparent either, which core does only within one post |
| 392 |
* type. For a lesson it would name a course the lesson was never under. |
| 393 |
* |
| 394 |
* @param int $id The deleted post. |
| 395 |
* @param \wpdb $wpdb |
| 396 |
* @return int Children detached. |
| 397 |
*/ |
| 398 |
private static function detach_children( int $id, $wpdb ): int { |
| 399 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the children of a post deleted a moment ago; nothing caches that. |
| 400 |
$children = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_parent = %d", $id ) ); |
| 401 |
if ( ! is_array( $children ) || array() === $children ) { |
| 402 |
return 0; |
| 403 |
} |
| 404 |
|
| 405 |
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the same column core's wp_delete_post() rewrites for same-type children; caches are cleared below. |
| 406 |
$wpdb->update( $wpdb->posts, array( 'post_parent' => 0 ), array( 'post_parent' => $id ) ); |
| 407 |
foreach ( $children as $child ) { |
| 408 |
clean_post_cache( (int) $child ); |
| 409 |
} |
| 410 |
|
| 411 |
return count( $children ); |
| 412 |
} |
| 413 |
|
| 414 |
/** |
| 415 |
* Run a raw type's DELETE and report the drop in its scan count, the unit |
| 416 |
* the UI shows. Without before/after, the paired transient DELETE |
| 417 |
* (timeout + value = 2 rows per transient) reported "2" while the button |
| 418 |
* said "(1)". (FBS-82149 Bug 3.) |
| 419 |
* |
| 420 |
* @param array $spec Registry entry with scan_sql_tpl + clean_sql_tpl. |
| 421 |
* @param \wpdb $wpdb |
| 422 |
*/ |
| 423 |
private static function delete_raw( array $spec, $wpdb ): int { |
| 424 |
$before = self::count_for( $spec, $wpdb ); |
| 425 |
if ( 0 === $before ) { |
| 426 |
return 0; |
| 427 |
} |
| 428 |
|
| 429 |
$sql = self::fill( $spec['clean_sql_tpl'], $wpdb ); |
| 430 |
// 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 and esc_sql()'d registered taxonomy names are interpolated. Destructive cleanup explicitly bypasses the object cache. |
| 431 |
$wpdb->query( $sql ); |
| 432 |
|
| 433 |
return max( 0, $before - self::count_for( $spec, $wpdb ) ); |
| 434 |
} |
| 435 |
|
| 436 |
/** Fill a count or raw-delete template: prefix, then the taxonomy list. */ |
| 437 |
private static function fill( string $tpl, $wpdb ): string { |
| 438 |
if ( false === strpos( $tpl, '%2$s' ) ) { |
| 439 |
return sprintf( $tpl, $wpdb->prefix ); |
| 440 |
} |
| 441 |
return sprintf( $tpl, $wpdb->prefix, self::post_taxonomy_list() ); |
| 442 |
} |
| 443 |
|
| 444 |
/** |
| 445 |
* Quoted, escaped names of the taxonomies whose objects are all posts. |
| 446 |
* |
| 447 |
* A taxonomy qualifies only if every object type it is registered for is |
| 448 |
* a registered post type. When none do, the list is '' and the orphan |
| 449 |
* term relationship sweep matches nothing. |
| 450 |
*/ |
| 451 |
private static function post_taxonomy_list(): string { |
| 452 |
$names = array(); |
| 453 |
if ( function_exists( 'get_taxonomies' ) && function_exists( 'post_type_exists' ) ) { |
| 454 |
foreach ( (array) get_taxonomies( array(), 'objects' ) as $name => $taxonomy ) { |
| 455 |
$object_types = is_object( $taxonomy ) && isset( $taxonomy->object_type ) ? (array) $taxonomy->object_type : array(); |
| 456 |
if ( array() === $object_types ) { |
| 457 |
continue; |
| 458 |
} |
| 459 |
foreach ( $object_types as $object_type ) { |
| 460 |
if ( ! post_type_exists( (string) $object_type ) ) { |
| 461 |
continue 2; |
| 462 |
} |
| 463 |
} |
| 464 |
$names[] = "'" . esc_sql( (string) $name ) . "'"; |
| 465 |
} |
| 466 |
} |
| 467 |
return array() === $names ? "''" : implode( ', ', $names ); |
| 468 |
} |
| 469 |
|
| 470 |
/** |
| 471 |
* OPTIMIZE TABLE on every WordPress core table (and any with the |
| 472 |
* site's prefix). Returns the table names + per-table status. |
| 473 |
*/ |
| 474 |
public static function optimize_tables( $wpdb_in = null ): array { |
| 475 |
$wpdb = self::wpdb( $wpdb_in ); |
| 476 |
$prefix = $wpdb->prefix; |
| 477 |
// 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. |
| 478 |
$tables = $wpdb->get_col( "SHOW TABLES LIKE '" . esc_sql( $prefix ) . "%'" ); |
| 479 |
if ( ! is_array( $tables ) ) { |
| 480 |
return array(); |
| 481 |
} |
| 482 |
$out = array(); |
| 483 |
foreach ( $tables as $table ) { |
| 484 |
// 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. |
| 485 |
$ok = $wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table ) . '`' ); |
| 486 |
$out[ $table ] = false !== $ok; |
| 487 |
} |
| 488 |
|
| 489 |
if ( ! empty( $out ) && class_exists( '\\XSpeed\\Activity_Log' ) ) { |
| 490 |
Activity_Log::record( |
| 491 |
'database_optimized', |
| 492 |
sprintf( 'Database tables optimized (%d).', count( $out ) ), |
| 493 |
Activity_Log::INFO |
| 494 |
); |
| 495 |
} |
| 496 |
|
| 497 |
return $out; |
| 498 |
} |
| 499 |
|
| 500 |
/** |
| 501 |
* Register / cancel the cleanup cron schedule. Called from the |
| 502 |
* DatabaseModule's settings-change hook. |
| 503 |
*/ |
| 504 |
public static function apply_schedule( string $schedule, array $types ): void { |
| 505 |
wp_clear_scheduled_hook( self::CRON_HOOK ); |
| 506 |
if ( in_array( $schedule, array( 'hourly', 'daily', 'weekly' ), true ) && ! empty( $types ) ) { |
| 507 |
wp_schedule_event( time() + HOUR_IN_SECONDS, $schedule, self::CRON_HOOK ); |
| 508 |
} |
| 509 |
} |
| 510 |
|
| 511 |
/** |
| 512 |
* Cron handler — pulls the configured included_types from settings |
| 513 |
* and runs clean() on them within one tick's budget, resuming where the |
| 514 |
* previous tick stopped. |
| 515 |
*/ |
| 516 |
public static function cron_tick(): void { |
| 517 |
$opts = Settings_Manager::get( 'database' ); |
| 518 |
$types = is_array( $opts['included_types'] ?? null ) ? $opts['included_types'] : array(); |
| 519 |
if ( empty( $types ) ) { |
| 520 |
return; |
| 521 |
} |
| 522 |
self::clean( $types, null, array( 'resume' => true ) ); |
| 523 |
} |
| 524 |
|
| 525 |
/** |
| 526 |
* Forget where the scheduled clean stopped. Called whenever the module's |
| 527 |
* settings are written, so a cursor never resumes into a category the |
| 528 |
* user has just turned off. |
| 529 |
*/ |
| 530 |
public static function reset_cursor(): void { |
| 531 |
delete_option( self::CURSOR_OPTION ); |
| 532 |
} |
| 533 |
|
| 534 |
/** Identity of a type selection, so a cursor is only honoured for the set it was written for. */ |
| 535 |
private static function types_hash( array $keys ): string { |
| 536 |
sort( $keys ); |
| 537 |
return sha1( implode( ',', $keys ) ); |
| 538 |
} |
| 539 |
|
| 540 |
/** |
| 541 |
* Where to resume: [ index into $keys, id to start after ]. |
| 542 |
* |
| 543 |
* A cursor written for a different selection, or naming a type no longer |
| 544 |
* in it, is discarded and the run starts from the top. |
| 545 |
* |
| 546 |
* @param string $hash types_hash() of $keys. |
| 547 |
* @param string[] $keys Types this run will clean, in registry order. |
| 548 |
* @return array{0:int,1:int} |
| 549 |
*/ |
| 550 |
private static function read_cursor( string $hash, array $keys ): array { |
| 551 |
$stored = get_option( self::CURSOR_OPTION, array() ); |
| 552 |
if ( ! is_array( $stored ) || array() === $stored ) { |
| 553 |
return array( 0, 0 ); |
| 554 |
} |
| 555 |
|
| 556 |
$index = isset( $stored['type'] ) ? array_search( (string) $stored['type'], $keys, true ) : false; |
| 557 |
if ( ( $stored['types_hash'] ?? '' ) !== $hash || false === $index ) { |
| 558 |
self::reset_cursor(); |
| 559 |
return array( 0, 0 ); |
| 560 |
} |
| 561 |
|
| 562 |
return array( (int) $index, max( 0, (int) ( $stored['after_id'] ?? 0 ) ) ); |
| 563 |
} |
| 564 |
|
| 565 |
private static function write_cursor( string $hash, string $type, int $after_id ): void { |
| 566 |
$value = array( |
| 567 |
'types_hash' => $hash, |
| 568 |
'type' => $type, |
| 569 |
'after_id' => $after_id, |
| 570 |
); |
| 571 |
// autoload=no: read once per scheduled tick, never on a page view. |
| 572 |
if ( false === get_option( self::CURSOR_OPTION, false ) ) { |
| 573 |
add_option( self::CURSOR_OPTION, $value, '', 'no' ); |
| 574 |
return; |
| 575 |
} |
| 576 |
update_option( self::CURSOR_OPTION, $value ); |
| 577 |
} |
| 578 |
|
| 579 |
private static function wpdb( $injected ) { |
| 580 |
if ( null !== $injected ) { |
| 581 |
return $injected; |
| 582 |
} |
| 583 |
global $wpdb; |
| 584 |
return $wpdb; |
| 585 |
} |
| 586 |
|
| 587 |
/** |
| 588 |
* Public list of type metadata for the dashboard payload, so the |
| 589 |
* React side doesn't have to hardcode labels. |
| 590 |
* |
| 591 |
* @return array<string,string> |
| 592 |
*/ |
| 593 |
public static function type_labels(): array { |
| 594 |
$out = array(); |
| 595 |
foreach ( self::types() as $key => $spec ) { |
| 596 |
$out[ $key ] = $spec['label']; |
| 597 |
} |
| 598 |
return $out; |
| 599 |
} |
| 600 |
} |
| 601 |
|
