xspeed / includes / class-score-store.php

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

413 lines 13.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	* Whether a run with this provider + timestamp is already stored.
238	*
239	* The Hub is polled repeatedly while a test runs, and the finished result
240	* comes back on every poll after it completes — without this, one test
241	* would be recorded several times.
242	*/
243	/** Whether a run with this remote (Hub) id is already stored. */
244	public static function exists_remote( string $remote_id ): bool {
245	global $wpdb;
246	if ( ! self::has_db() \|\| '' === $remote_id ) {
247	return false;
248	}
249	$table = self::table();
250	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table.
251	return (bool) $wpdb->get_var(
252	$wpdb->prepare( "SELECT id FROM {$table} WHERE remote_id = %s LIMIT 1", $remote_id )
253	);
254	}
255
256	public static function exists( string $provider, int $ran_at ): bool {
257	global $wpdb;
258	if ( ! self::has_db() ) {
259	return false;
260	}
261	$table = self::table();
262
263	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table.
264	return (bool) $wpdb->get_var(
265	$wpdb->prepare( "SELECT id FROM {$table} WHERE provider = %s AND ran_at = %d LIMIT 1", $provider, $ran_at )
266	);
267	}
268
269	/** Remove every stored run. */
270	public static function clear(): void {
271	global $wpdb;
272	if ( ! self::has_db() ) {
273	return;
274	}
275	$table = self::table();
276	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table.
277	$wpdb->query( "TRUNCATE TABLE {$table}" );
278	}
279
280	/** Drop the table — uninstall only. */
281	public static function drop(): void {
282	global $wpdb;
283	if ( ! self::has_db() ) {
284	return;
285	}
286	$table = self::table();
287	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table, uninstall.
288	$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
289	delete_option( self::VERSION_OPTION );
290	}
291
292	/**
293	* A DB row back into the run shape the panel expects.
294	*
295	* @param array<string,mixed> $r Raw row.
296	* @return array<string,mixed>
297	*/
298	private static function to_run( array $r ): array {
299	return array(
300	'provider' => (string) ( $r['provider'] ?? '' ),
301	'source' => (string) ( $r['source'] ?? 'local' ),
302	'ts' => (int) ( $r['ran_at'] ?? 0 ),
303	'url' => (string) ( $r['url'] ?? '' ),
304	'strategy' => (string) ( $r['strategy'] ?? '' ),
305	'ok' => ! empty( $r['ok'] ),
306	'score' => self::num( $r['score'] ?? null ),
307	'metrics' => array(
308	'lcp' => self::num( $r['lcp'] ?? null ),
309	'fcp' => self::num( $r['fcp'] ?? null ),
310	'cls' => self::num( $r['cls'] ?? null ),
311	'tbt' => self::num( $r['tbt'] ?? null ),
312	'si' => self::num( $r['si'] ?? null ),
313	'ttfb' => self::num( $r['ttfb'] ?? null ),
314	),
315	'report_url' => isset( $r['report_url'] ) && '' !== $r['report_url'] ? (string) $r['report_url'] : null,
316	'remote_id' => isset( $r['remote_id'] ) ? (string) $r['remote_id'] : '',
317	'opportunities' => self::decode_opportunities( $r['opportunities'] ?? null ),
318	'error' => isset( $r['error'] ) && '' !== $r['error'] ? (string) $r['error'] : '',
319	);
320	}
321
322	/**
323	* A number, or null.
324	*
325	* NEVER casts null to 0. "We have no measurement" and "the value is zero"
326	* are different facts, and the whole history is built on telling them
327	* apart.
328	*
329	* @param mixed $value Raw value.
330	*/
331	/**
332	* A safe, storable URL — or null.
333	*
334	* Applied to everything that will be rendered as a link, including
335	* values that came back from the Hub. esc_url_raw() enforces the scheme
336	* allow-list, which is what stops `javascript:` reaching an href.
337	*
338	* @param mixed $value Raw value.
339	*/
340	/**
341	* Validate and encode the audit list for storage.
342	*
343	* Comes over the network from the Hub, so it is treated as untrusted:
344	* every row must have an id and a title, both bounded, and the list is
345	* capped. Returns null rather than an empty array, so "none" and "not
346	* recorded" are the same absent value in the column.
347	*
348	* @param mixed $value Raw value.
349	*/
350	private static function encode_opportunities( $value ): ?string {
351	if ( ! is_array( $value ) \|\| empty( $value ) ) {
352	return null;
353	}
354
355	$clean = array();
356	foreach ( $value as $row ) {
357	if ( ! is_array( $row ) ) {
358	continue;
359	}
360	$id = isset( $row['id'] ) ? substr( sanitize_text_field( (string) $row['id'] ), 0, 64 ) : '';
361	$title = isset( $row['title'] ) ? substr( sanitize_text_field( (string) $row['title'] ), 0, 160 ) : '';
362	if ( '' === $id \|\| '' === $title ) {
363	continue;
364	}
365	$metrics = array();
366	if ( isset( $row['metrics'] ) && is_array( $row['metrics'] ) ) {
367	foreach ( array_slice( $row['metrics'], 0, 6 ) as $m ) {
368	$metrics[] = substr( sanitize_text_field( (string) $m ), 0, 8 );
369	}
370	}
371	$clean[] = array(
372	'id' => $id,
373	'title' => $title,
374	'metrics' => $metrics,
375	);
376	if ( count( $clean ) >= 15 ) {
377	break;
378	}
379	}
380
381	return empty( $clean ) ? null : (string) wp_json_encode( $clean );
382	}
383
384	/**
385	* The stored audit list, back as an array.
386	*
387	* @param mixed $value Raw column value.
388	* @return array<int,array<string,mixed>>\|null
389	*/
390	private static function decode_opportunities( $value ): ?array {
391	if ( ! is_string( $value ) \|\| '' === $value ) {
392	return null;
393	}
394	$decoded = json_decode( $value, true );
395	return is_array( $decoded ) && ! empty( $decoded ) ? $decoded : null;
396	}
397
398	private static function url( $value ): ?string {
399	if ( ! is_string( $value ) \|\| '' === $value ) {
400	return null;
401	}
402	$clean = esc_url_raw( $value );
403	return '' === $clean ? null : $clean;
404	}
405
406	private static function num( $value ): ?float {
407	if ( null === $value \|\| '' === $value ) {
408	return null;
409	}
410	return is_numeric( $value ) ? (float) $value : null;
411	}
412	}
413