xspeed / includes / class-database-cleaner.php

class-database-cleaner.php in xSpeed Cache: AI-Powered Performance Hub with MCP, Caching & CDN 1.3.0, at includes/class-database-cleaner.php

266 lines 11.0 KB

Raw Download Zip

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 counts one row per EXPIRED transient — the timeout
76	// row — across BOTH the normal (_transient_timeout_*) and the
77	// site/network (_site_transient_timeout_*) families. The
78	// parentheses around the two LIKEs are required so the AND
79	// expiry condition applies to both, not just the second.
80	// (FBS-82149 Bug 1: site transients were never matched;
81	// Bug 3: scan counts logical transients = 1 per timeout row.)
82	'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()",
83	// Clean deletes the timeout row AND its value sibling via a
84	// LEFT JOIN so an ORPHAN timeout row (value sibling missing)
85	// is still removed — an INNER JOIN silently kept those, so a
86	// "clean" never drove the scan count to zero. REPLACE maps
87	// both families to their value-key name. (FBS-82149 Bug 2.)
88	'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()",
89	),
90	'orphan_postmeta' => array(
91	'label' => 'Orphan Post Meta',
92	'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",
93	'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",
94	),
95	'orphan_commentmeta' => array(
96	'label' => 'Orphan Comment Meta',
97	'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",
98	'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",
99	),
100	);
101	}
102
103	/**
104	* Read-only scan. Returns array keyed by type slug:
105	* [ slug => [ label => …, count => N ] ]
106	*
107	* @param \wpdb\|null $wpdb_in Injectable for tests; defaults to global.
108	*/
109	public static function scan( $wpdb_in = null ): array {
110	$wpdb = self::wpdb( $wpdb_in );
111	$out = array();
112	foreach ( self::types() as $key => $spec ) {
113	$out[ $key ] = array(
114	'label' => $spec['label'],
115	'count' => self::count_for( $spec, $wpdb ),
116	);
117	}
118	return $out;
119	}
120
121	/**
122	* Run a type's scan SQL and return its COUNT. Shared by scan() and by
123	* clean() (before/after) so the "rows removed" number is always in the
124	* same unit the UI shows. (FBS-82149 Bug 3.)
125	*
126	* @param array{scan_sql_tpl:string} $spec
127	* @param \wpdb $wpdb
128	*/
129	private static function count_for( array $spec, $wpdb ): int {
130	$sql = sprintf( $spec['scan_sql_tpl'], $wpdb->prefix );
131	// 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 count; not cached so the user always sees current row counts.
132	return (int) $wpdb->get_var( $sql );
133	}
134
135	/**
136	* Destructive cleanup for the supplied list of type slugs. Anything
137	* not in the static types() table is silently skipped — never run
138	* arbitrary SQL because someone POSTed an unexpected slug.
139	*
140	* Returns the new scan() shape so the UI can refresh in one
141	* round trip, plus a per-type `affected` count of rows removed.
142	*
143	* @param string[] $types
144	*/
145	public static function clean( array $types, $wpdb_in = null ): array {
146	$wpdb = self::wpdb( $wpdb_in );
147	$registry = self::types();
148	$results = array();
149	$total = 0;
150
151	foreach ( $types as $key ) {
152	if ( ! isset( $registry[ $key ] ) ) {
153	continue;
154	}
155	// Report `affected` in the SAME unit the scan counts + the UI
156	// button shows: the reduction in the scan count (logical items),
157	// not raw rows deleted. Without this the paired transient DELETE
158	// (timeout + value = 2 rows per transient) reported "2" while the
159	// button said "(1)". Measure before/after so every type — paired
160	// or single-row — reports a consistent, user-meaningful count.
161	// (FBS-82149 Bug 3.)
162	$before = self::count_for( $registry[ $key ], $wpdb );
163
164	$sql = sprintf( $registry[ $key ]['clean_sql_tpl'], $wpdb->prefix );
165	// 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.
166	$wpdb->query( $sql );
167
168	$after = self::count_for( $registry[ $key ], $wpdb );
169	$affected = max( 0, $before - $after );
170
171	$results[ $key ] = $affected;
172	$total += $affected;
173	}
174
175	if ( $total > 0 && class_exists( '\\XSpeed\\Activity_Log' ) ) {
176	Activity_Log::record(
177	'database_cleaned',
178	sprintf( 'Database cleanup removed %d row%s across %d table(s).', $total, 1 === $total ? '' : 's', count( $results ) ),
179	Activity_Log::INFO
180	);
181	}
182
183	return array(
184	'cleaned' => $results,
185	'scan' => self::scan( $wpdb_in ),
186	);
187	}
188
189	/**
190	* OPTIMIZE TABLE on every WordPress core table (and any with the
191	* site's prefix). Returns the table names + per-table status.
192	*/
193	public static function optimize_tables( $wpdb_in = null ): array {
194	$wpdb = self::wpdb( $wpdb_in );
195	$prefix = $wpdb->prefix;
196	// 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.
197	$tables = $wpdb->get_col( "SHOW TABLES LIKE '" . esc_sql( $prefix ) . "%'" );
198	if ( ! is_array( $tables ) ) {
199	return array();
200	}
201	$out = array();
202	foreach ( $tables as $table ) {
203	// 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.
204	$ok = $wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table ) . '`' );
205	$out[ $table ] = false !== $ok;
206	}
207
208	if ( ! empty( $out ) && class_exists( '\\XSpeed\\Activity_Log' ) ) {
209	Activity_Log::record(
210	'database_optimized',
211	sprintf( 'Database tables optimized (%d).', count( $out ) ),
212	Activity_Log::INFO
213	);
214	}
215
216	return $out;
217	}
218
219	/**
220	* Register / cancel the cleanup cron schedule. Called from the
221	* DatabaseModule's settings-change hook.
222	*/
223	public static function apply_schedule( string $schedule, array $types ): void {
224	wp_clear_scheduled_hook( self::CRON_HOOK );
225	if ( in_array( $schedule, array( 'hourly', 'daily', 'weekly' ), true ) && ! empty( $types ) ) {
226	wp_schedule_event( time() + HOUR_IN_SECONDS, $schedule, self::CRON_HOOK );
227	}
228	}
229
230	/**
231	* Cron handler — pulls the configured included_types from settings
232	* and runs clean() on them. Idempotent; if nothing's eligible, the
233	* underlying DELETE runs but affects 0 rows.
234	*/
235	public static function cron_tick(): void {
236	$opts = Settings_Manager::get( 'database' );
237	$types = is_array( $opts['included_types'] ?? null ) ? $opts['included_types'] : array();
238	if ( empty( $types ) ) {
239	return;
240	}
241	self::clean( $types );
242	}
243
244	private static function wpdb( $injected ) {
245	if ( null !== $injected ) {
246	return $injected;
247	}
248	global $wpdb;
249	return $wpdb;
250	}
251
252	/**
253	* Public list of type metadata for the dashboard payload, so the
254	* React side doesn't have to hardcode labels.
255	*
256	* @return array<string,string>
257	*/
258	public static function type_labels(): array {
259	$out = array();
260	foreach ( self::types() as $key => $spec ) {
261	$out[ $key ] = $spec['label'];
262	}
263	return $out;
264	}
265	}
266