xspeed / includes / class-score-store.php

class-score-store.php in xSpeed Cache: AI-Powered Performance Hub with MCP, Caching & CDN 1.3.1, at includes/class-score-store.php

440 lines 14.7 KB

Raw Download Zip

1	<?php
2	/**
3	* Score history storage — the plugin's own table.
4	*
5	* Runs used to live in the `xspeed_score_history` option: a capped array,
6	* rewritten whole on every append. That is fine for twenty rows and wrong for
7	* a history you want to keep, because an option cannot be queried — you cannot
8	* ask for "GTmetrix runs in March" or "the last run before this deploy"
9	* without loading and filtering the lot in PHP.
10	*
11	* A table gives us the thing the feature is actually for: a record of how the
12	* site's score moved over time, owned by the plugin, surviving whatever
13	* happens to a Hub connection.
14	*
15	* The option remains the source of truth until this table exists and has been
16	* migrated into — see maybe_install(). Nothing reads the option afterwards.
17	*
18	* @package XSpeed
19	*/
20
21	declare(strict_types=1);
22
23	namespace XSpeed;
24
25	defined( 'ABSPATH' ) \|\| exit;
26
27	final class Score_Store {
28
29	/**
30	* Bumped whenever the schema changes. Stored per-site so dbDelta only
31	* runs when it has something to do, rather than on every admin request.
32	*/
33	private const SCHEMA_VERSION = 3;
34
35	private const VERSION_OPTION = 'xspeed_score_schema';
36
37	/**
38	* Is a database available?
39	*
40	* $wpdb is absent in unit tests and during very early boot. Every method
41	* here checks rather than assuming, so a missing database degrades to
42	* "no history" instead of a fatal — the score panel is not worth taking a
43	* request down for.
44	*/
45	private static function has_db(): bool {
46	global $wpdb;
47	return isset( $wpdb ) && is_object( $wpdb );
48	}
49
50	/** Prefixed table name. Empty string when there is no database. */
51	public static function table(): string {
52	global $wpdb;
53	return self::has_db() ? $wpdb->prefix . 'xspeed_scores' : '';
54	}
55
56	/**
57	* Create or upgrade the table, and migrate the old option once.
58	*
59	* Safe to call repeatedly: it returns immediately unless the stored
60	* schema version is behind. Called on activation AND on admin_init,
61	* because activation does not fire for a site added to a multisite
62	* network later, nor after a plugin update that ships a new schema.
63	*/
64	public static function maybe_install(): void {
65	if ( ! self::has_db() ) {
66	return;
67	}
68	if ( (int) get_option( self::VERSION_OPTION, 0 ) === self::SCHEMA_VERSION ) {
69	return;
70	}
71
72	global $wpdb;
73	$table = self::table();
74	$collate = $wpdb->get_charset_collate();
75
76	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
77
78	/*
79	* Nullable metrics throughout, deliberately. A failed audit is not a
80	* score of zero, and a metric the provider did not report is not a
81	* perfect one — the panel renders "—" for null and a real number for
82	* 0, and coercing here would make a failure look like a catastrophe
83	* on the score and a triumph on the timings.
84	*
85	* `ran_at` is the RUN's own timestamp, not when we stored it, so the
86	* ordering stays honest when a Hub result arrives minutes late.
87	*/
88	$sql = "CREATE TABLE {$table} (
89	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
90	provider varchar(20) NOT NULL DEFAULT '',
91	source varchar(20) NOT NULL DEFAULT 'local',
92	ran_at bigint(20) unsigned NOT NULL DEFAULT 0,
93	url text NOT NULL,
94	strategy varchar(20) NOT NULL DEFAULT '',
95	ok tinyint(1) NOT NULL DEFAULT 1,
96	score smallint(5) DEFAULT NULL,
97	lcp float DEFAULT NULL,
98	fcp float DEFAULT NULL,
99	cls float DEFAULT NULL,
100	tbt float DEFAULT NULL,
101	si float DEFAULT NULL,
102	ttfb float DEFAULT NULL,
103	report_url text NULL,
104	error text NULL,
105	remote_id varchar(64) NOT NULL DEFAULT '',
106	opportunities longtext NULL,
107	PRIMARY KEY (id),
108	KEY ran_at (ran_at),
109	KEY provider_ran_at (provider, ran_at),
110	KEY remote_id (remote_id)
111	) {$collate};";
112
113	dbDelta( $sql );
114
115	self::migrate_option_history();
116
117	update_option( self::VERSION_OPTION, self::SCHEMA_VERSION, false );
118	}
119
120	/**
121	* Move the old option-based history into the table, once.
122	*
123	* The option is left in place rather than deleted: if an upgrade goes
124	* wrong, the user's history is still there to recover, and a second run
125	* of this method is a no-op because the table is only empty the first
126	* time.
127	*/
128	private static function migrate_option_history(): void {
129	global $wpdb;
130
131	$table = self::table();
132	// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- one-time migration, no cache to invalidate.
133	$existing = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
134	if ( $existing > 0 ) {
135	return;
136	}
137
138	$legacy = get_option( Score::HISTORY_OPTION, array() );
139	if ( ! is_array( $legacy ) \|\| empty( $legacy ) ) {
140	return;
141	}
142
143	foreach ( array_reverse( $legacy ) as $row ) {
144	if ( is_array( $row ) ) {
145	self::insert( $row, 'local' );
146	}
147	}
148	}
149
150	/**
151	* Store one run.
152	*
153	* @param array<string,mixed> $row A parsed run, in the shape \XSpeed\Score produces.
154	* @param string $source 'local' (this site called the provider) or 'hub'.
155	* @return int Inserted row id, or 0 on failure.
156	*/
157	public static function insert( array $row, string $source = 'local' ): int {
158	global $wpdb;
159
160	if ( ! self::has_db() ) {
161	return 0;
162	}
163
164	$metrics = isset( $row['metrics'] ) && is_array( $row['metrics'] ) ? $row['metrics'] : array();
165
166	$data = array(
167	'provider' => isset( $row['provider'] ) ? substr( (string) $row['provider'], 0, 20 ) : '',
168	'source' => 'hub' === $source ? 'hub' : 'local',
169	'ran_at' => isset( $row['ts'] ) ? (int) $row['ts'] : time(),
170	'url' => substr( (string) self::url( $row['url'] ?? null ), 0, 2048 ),
171	'strategy' => isset( $row['strategy'] ) ? substr( (string) $row['strategy'], 0, 20 ) : '',
172	'ok' => empty( $row['ok'] ) ? 0 : 1,
173	'score' => self::num( $row['score'] ?? null ),
174	'lcp' => self::num( $metrics['lcp'] ?? null ),
175	'fcp' => self::num( $metrics['fcp'] ?? null ),
176	'cls' => self::num( $metrics['cls'] ?? null ),
177	'tbt' => self::num( $metrics['tbt'] ?? null ),
178	'si' => self::num( $metrics['si'] ?? null ),
179	'ttfb' => self::num( $metrics['ttfb'] ?? null ),
180	// The report link is rendered as an href and, for a Hub-run test,
181	// arrives over the network. esc_url_raw() strips anything that
182	// isn't an allowed scheme, so a `javascript:` or `data:` value
183	// can never reach the panel's link. Empty result => null, never
184	// a half-sanitized string.
185	'report_url' => self::url( $row['report_url'] ?? null ),
186	// Bounded: this is shown to the user and the column is TEXT, so
187	// an unbounded remote string would be both a storage and a
188	// rendering problem.
189	'error' => isset( $row['error'] ) && '' !== $row['error']
190	? substr( sanitize_text_field( (string) $row['error'] ), 0, 500 )
191	: null,
192	// The Hub's run id. A STABLE identity for a remote run — dedupe
193	// keyed on a timestamp let one test in twice when a retry and the
194	// original arrived milliseconds apart.
195	'remote_id' => isset( $row['remote_id'] ) ? substr( (string) $row['remote_id'], 0, 64 ) : '',
196	// What the report said to fix. JSON in a longtext: it belongs to
197	// one run, is read whenever that run is read, and written once.
198	'opportunities' => self::encode_opportunities( $row['opportunities'] ?? null ),
199	);
200
201	// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- own table, no core API for it.
202	$ok = $wpdb->insert( self::table(), $data );
203
204	return $ok ? (int) $wpdb->insert_id : 0;
205	}
206
207	/**
208	* Runs, newest first, in the array shape the REST layer already returns.
209	*
210	* @param int $limit Rows to return (1-500).
211	* @return array<int,array<string,mixed>>
212	*/
213	public static function history( int $limit = 20 ): array {
214	global $wpdb;
215
216	if ( ! self::has_db() ) {
217	return array();
218	}
219
220	$limit = max( 1, min( 500, $limit ) );
221	$table = self::table();
222
223	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table; $limit is clamped above.
224	$rows = $wpdb->get_results(
225	$wpdb->prepare( "SELECT * FROM {$table} ORDER BY ran_at DESC, id DESC LIMIT %d", $limit ),
226	ARRAY_A
227	);
228
229	if ( ! is_array( $rows ) ) {
230	return array();
231	}
232
233	return array_map( array( self::class, 'to_run' ), $rows );
234	}
235
236	/**
237	* The most recent SUCCESSFUL run, straight from the table.
238	*
239	* Score::latest() used to scan the capped history window for the first
240	* `ok` row, which meant a run of failures could push the last real audit
241	* out of the window and leave the site reporting no score at all — the
242	* exact outcome the optimize report exists to prevent, and most likely on
243	* the sites with no PSI key, since the shared quota refuses often.
244	* Asking the store for the newest `ok` row cannot be crowded out.
245	*
246	* @return array<string,mixed>\|null
247	*/
248	public static function latest_ok(): ?array {
249	global $wpdb;
250
251	if ( ! self::has_db() ) {
252	return null;
253	}
254
255	$table = self::table();
256
257	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table, no user input in the query.
258	$row = $wpdb->get_row( "SELECT * FROM {$table} WHERE ok = 1 ORDER BY ran_at DESC, id DESC LIMIT 1", ARRAY_A );
259
260	return is_array( $row ) ? self::to_run( $row ) : null;
261	}
262
263	/**
264	* Whether a run with this provider + timestamp is already stored.
265	*
266	* The Hub is polled repeatedly while a test runs, and the finished result
267	* comes back on every poll after it completes — without this, one test
268	* would be recorded several times.
269	*/
270	/** Whether a run with this remote (Hub) id is already stored. */
271	public static function exists_remote( string $remote_id ): bool {
272	global $wpdb;
273	if ( ! self::has_db() \|\| '' === $remote_id ) {
274	return false;
275	}
276	$table = self::table();
277	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table.
278	return (bool) $wpdb->get_var(
279	$wpdb->prepare( "SELECT id FROM {$table} WHERE remote_id = %s LIMIT 1", $remote_id )
280	);
281	}
282
283	public static function exists( string $provider, int $ran_at ): bool {
284	global $wpdb;
285	if ( ! self::has_db() ) {
286	return false;
287	}
288	$table = self::table();
289
290	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table.
291	return (bool) $wpdb->get_var(
292	$wpdb->prepare( "SELECT id FROM {$table} WHERE provider = %s AND ran_at = %d LIMIT 1", $provider, $ran_at )
293	);
294	}
295
296	/** Remove every stored run. */
297	public static function clear(): void {
298	global $wpdb;
299	if ( ! self::has_db() ) {
300	return;
301	}
302	$table = self::table();
303	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table.
304	$wpdb->query( "TRUNCATE TABLE {$table}" );
305	}
306
307	/** Drop the table — uninstall only. */
308	public static function drop(): void {
309	global $wpdb;
310	if ( ! self::has_db() ) {
311	return;
312	}
313	$table = self::table();
314	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table, uninstall.
315	$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
316	delete_option( self::VERSION_OPTION );
317	}
318
319	/**
320	* A DB row back into the run shape the panel expects.
321	*
322	* @param array<string,mixed> $r Raw row.
323	* @return array<string,mixed>
324	*/
325	private static function to_run( array $r ): array {
326	return array(
327	'provider' => (string) ( $r['provider'] ?? '' ),
328	'source' => (string) ( $r['source'] ?? 'local' ),
329	'ts' => (int) ( $r['ran_at'] ?? 0 ),
330	'url' => (string) ( $r['url'] ?? '' ),
331	'strategy' => (string) ( $r['strategy'] ?? '' ),
332	'ok' => ! empty( $r['ok'] ),
333	'score' => self::num( $r['score'] ?? null ),
334	'metrics' => array(
335	'lcp' => self::num( $r['lcp'] ?? null ),
336	'fcp' => self::num( $r['fcp'] ?? null ),
337	'cls' => self::num( $r['cls'] ?? null ),
338	'tbt' => self::num( $r['tbt'] ?? null ),
339	'si' => self::num( $r['si'] ?? null ),
340	'ttfb' => self::num( $r['ttfb'] ?? null ),
341	),
342	'report_url' => isset( $r['report_url'] ) && '' !== $r['report_url'] ? (string) $r['report_url'] : null,
343	'remote_id' => isset( $r['remote_id'] ) ? (string) $r['remote_id'] : '',
344	'opportunities' => self::decode_opportunities( $r['opportunities'] ?? null ),
345	'error' => isset( $r['error'] ) && '' !== $r['error'] ? (string) $r['error'] : '',
346	);
347	}
348
349	/**
350	* A number, or null.
351	*
352	* NEVER casts null to 0. "We have no measurement" and "the value is zero"
353	* are different facts, and the whole history is built on telling them
354	* apart.
355	*
356	* @param mixed $value Raw value.
357	*/
358	/**
359	* A safe, storable URL — or null.
360	*
361	* Applied to everything that will be rendered as a link, including
362	* values that came back from the Hub. esc_url_raw() enforces the scheme
363	* allow-list, which is what stops `javascript:` reaching an href.
364	*
365	* @param mixed $value Raw value.
366	*/
367	/**
368	* Validate and encode the audit list for storage.
369	*
370	* Comes over the network from the Hub, so it is treated as untrusted:
371	* every row must have an id and a title, both bounded, and the list is
372	* capped. Returns null rather than an empty array, so "none" and "not
373	* recorded" are the same absent value in the column.
374	*
375	* @param mixed $value Raw value.
376	*/
377	private static function encode_opportunities( $value ): ?string {
378	if ( ! is_array( $value ) \|\| empty( $value ) ) {
379	return null;
380	}
381
382	$clean = array();
383	foreach ( $value as $row ) {
384	if ( ! is_array( $row ) ) {
385	continue;
386	}
387	$id = isset( $row['id'] ) ? substr( sanitize_text_field( (string) $row['id'] ), 0, 64 ) : '';
388	$title = isset( $row['title'] ) ? substr( sanitize_text_field( (string) $row['title'] ), 0, 160 ) : '';
389	if ( '' === $id \|\| '' === $title ) {
390	continue;
391	}
392	$metrics = array();
393	if ( isset( $row['metrics'] ) && is_array( $row['metrics'] ) ) {
394	foreach ( array_slice( $row['metrics'], 0, 6 ) as $m ) {
395	$metrics[] = substr( sanitize_text_field( (string) $m ), 0, 8 );
396	}
397	}
398	$clean[] = array(
399	'id' => $id,
400	'title' => $title,
401	'metrics' => $metrics,
402	);
403	if ( count( $clean ) >= 15 ) {
404	break;
405	}
406	}
407
408	return empty( $clean ) ? null : (string) wp_json_encode( $clean );
409	}
410
411	/**
412	* The stored audit list, back as an array.
413	*
414	* @param mixed $value Raw column value.
415	* @return array<int,array<string,mixed>>\|null
416	*/
417	private static function decode_opportunities( $value ): ?array {
418	if ( ! is_string( $value ) \|\| '' === $value ) {
419	return null;
420	}
421	$decoded = json_decode( $value, true );
422	return is_array( $decoded ) && ! empty( $decoded ) ? $decoded : null;
423	}
424
425	private static function url( $value ): ?string {
426	if ( ! is_string( $value ) \|\| '' === $value ) {
427	return null;
428	}
429	$clean = esc_url_raw( $value );
430	return '' === $clean ? null : $clean;
431	}
432
433	private static function num( $value ): ?float {
434	if ( null === $value \|\| '' === $value ) {
435	return null;
436	}
437	return is_numeric( $value ) ? (float) $value : null;
438	}
439	}
440