easyfonts / src / Database / Migrator.php

Migrator.php in EasyFonts – Host Google Fonts Locally, Fast & Auto-Optimize, GDPR Compliant trunk, at src/Database/Migrator.php

325 lines 9.6 KB

Raw Download Zip

1	<?php
2	/**
3	* Database installation, schema, and self-healing repair.
4	*
5	* @package EasyFonts
6	*/
7
8	namespace EasyFonts\Database;
9
10	defined( 'ABSPATH' ) \|\| exit;
11
12	/**
13	* Creates, versions, verifies, and repairs the plugin's tables.
14	*/
15	class Migrator {
16
17	const DB_VERSION = '2.0.2';
18
19	/**
20	* Install, upgrade, or repair tables. Safe to call on every load.
21	*
22	* Verifies the real schema (column presence), not just the version option,
23	* so a site left with an incompatible legacy schema heals automatically.
24	*/
25	public function install(): void {
26	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
27
28	$tables_ok = $this->tables_ok();
29
30	// Up to date and intact → nothing to do.
31	if ( $tables_ok && get_option( 'easyfonts_db_version' ) === self::DB_VERSION ) {
32	return;
33	}
34
35	// An incompatible legacy schema is present → drop the empty legacy
36	// tables so we can create the correct ones. Scoped to legacy schema only.
37	if ( $this->has_legacy_schema() ) {
38	$this->drop_tables();
39	}
40
41	$this->create_tables();
42
43	if ( false === get_option( 'easyfonts_settings' ) ) {
44	add_option( 'easyfonts_settings', self::default_settings(), '', false );
45	}
46
47	// Only record the version once the schema is verified correct.
48	if ( $this->tables_ok() ) {
49	update_option( 'easyfonts_db_version', self::DB_VERSION, false );
50	}
51
52	if ( false === get_option( 'easyfonts_cache_buster' ) ) {
53	update_option( 'easyfonts_cache_buster', time(), false );
54	}
55	}
56
57	/**
58	* Force a clean rebuild (drop + recreate). Used by the repair action / CLI.
59	*/
60	public function repair(): void {
61	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
62
63	$this->drop_tables();
64	$this->create_tables();
65
66	if ( $this->tables_ok() ) {
67	update_option( 'easyfonts_db_version', self::DB_VERSION, false );
68	}
69	}
70
71	/**
72	* Create (or, via dbDelta, reconcile) the three tables. Timestamps are set
73	* from PHP, so no DB-side DEFAULT CURRENT_TIMESTAMP is required (portable
74	* across MySQL/MariaDB versions and strict sql_modes).
75	*/
76	private function create_tables(): void {
77	global $wpdb;
78
79	$charset = $wpdb->get_charset_collate();
80	$fonts = $wpdb->prefix . 'easyfonts_fonts';
81	$usage = $wpdb->prefix . 'easyfonts_usage';
82	$dec = $wpdb->prefix . 'easyfonts_decisions';
83
84	dbDelta(
85	"CREATE TABLE {$fonts} (
86	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
87	family VARCHAR(190) NOT NULL,
88	weight VARCHAR(40) NOT NULL DEFAULT '400',
89	style VARCHAR(20) NOT NULL DEFAULT 'normal',
90	subset VARCHAR(60) NOT NULL DEFAULT 'latin',
91	variant_key VARCHAR(120) NOT NULL,
92	is_variable TINYINT(1) NOT NULL DEFAULT 0,
93	is_enabled TINYINT(1) NOT NULL DEFAULT 1,
94	is_preloaded TINYINT(1) NOT NULL DEFAULT 0,
95	load_user_set TINYINT(1) NOT NULL DEFAULT 0,
96	preload_user_set TINYINT(1) NOT NULL DEFAULT 0,
97	provider VARCHAR(40) NOT NULL DEFAULT 'google',
98	css_file VARCHAR(190) DEFAULT NULL,
99	font_file VARCHAR(190) DEFAULT NULL,
100	file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
101	metrics LONGTEXT DEFAULT NULL,
102	source_url TEXT,
103	created_at DATETIME DEFAULT NULL,
104	PRIMARY KEY (id),
105	UNIQUE KEY uq_variant (variant_key),
106	KEY idx_family (family)
107	) {$charset};"
108	);
109
110	dbDelta(
111	"CREATE TABLE {$usage} (
112	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
113	usage_key CHAR(40) NOT NULL,
114	page_url VARCHAR(500) NOT NULL,
115	page_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
116	family VARCHAR(190) NOT NULL,
117	weight VARCHAR(40) NOT NULL DEFAULT '400',
118	style VARCHAR(20) NOT NULL DEFAULT 'normal',
119	origin VARCHAR(30) NOT NULL DEFAULT 'buffer',
120	rendered TINYINT(1) NOT NULL DEFAULT 0,
121	above_fold TINYINT(1) NOT NULL DEFAULT 0,
122	hits BIGINT UNSIGNED NOT NULL DEFAULT 1,
123	beacon_misses SMALLINT UNSIGNED NOT NULL DEFAULT 0,
124	last_seen DATETIME DEFAULT NULL,
125	PRIMARY KEY (id),
126	UNIQUE KEY uq_usage (usage_key),
127	KEY idx_family (family),
128	KEY idx_last_seen (last_seen)
129	) {$charset};"
130	);
131
132	dbDelta(
133	"CREATE TABLE {$dec} (
134	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
135	route_key CHAR(40) NOT NULL,
136	route VARCHAR(500) NOT NULL,
137	device VARCHAR(10) NOT NULL DEFAULT 'any',
138	preload LONGTEXT DEFAULT NULL,
139	unload LONGTEXT DEFAULT NULL,
140	updated_at DATETIME DEFAULT NULL,
141	PRIMARY KEY (id),
142	UNIQUE KEY uq_route (route_key),
143	KEY idx_updated (updated_at)
144	) {$charset};"
145	);
146
147	// dbDelta never DROPs an index that exists in the table but not in the
148	// definition, so retire the now-unused usage.idx_page (it indexed a
149	// column we never query) explicitly. Guarded + idempotent: only runs
150	// when the index is actually present, and a failure can't break install.
151	$this->drop_index_if_exists( 'easyfonts_usage', 'idx_page' );
152	}
153
154	/**
155	* Drop an index from a table if it exists. Safe no-op otherwise.
156	*
157	* @param string $name Unprefixed table name.
158	* @param string $index Index name.
159	* @return void
160	*/
161	private function drop_index_if_exists( string $name, string $index ): void {
162	global $wpdb;
163
164	if ( ! $this->table_exists( $name ) ) {
165	return;
166	}
167
168	$table = $wpdb->prefix . $name;
169
170	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
171	$found = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", $index ) );
172
173	if ( $found ) {
174	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
175	$wpdb->query( "ALTER TABLE `{$table}` DROP INDEX `{$index}`" );
176	}
177	}
178
179	/**
180	* Drop all three tables (IF EXISTS).
181	*/
182	private function drop_tables(): void {
183	global $wpdb;
184
185	foreach ( array( 'easyfonts_fonts', 'easyfonts_usage', 'easyfonts_decisions' ) as $name ) {
186	$table = $wpdb->prefix . $name;
187	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
188	$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" );
189	}
190	}
191
192	/**
193	* Are all three tables present with the current signature columns?
194	*
195	* @return bool
196	*/
197	public function tables_ok(): bool {
198	return $this->column_exists( 'easyfonts_fonts', 'variant_key' )
199	&& $this->column_exists( 'easyfonts_fonts', 'is_enabled' )
200	&& $this->column_exists( 'easyfonts_fonts', 'load_user_set' )
201	&& $this->column_exists( 'easyfonts_fonts', 'preload_user_set' )
202	&& $this->column_exists( 'easyfonts_usage', 'usage_key' )
203	&& $this->column_exists( 'easyfonts_usage', 'beacon_misses' )
204	&& $this->column_exists( 'easyfonts_decisions', 'route_key' );
205	}
206
207	/**
208	* Is a legacy fonts table present? Detected by an old column that
209	* the current schema never uses, alongside the absence of its signature column.
210	*
211	* @return bool
212	*/
213	public function has_legacy_schema(): bool {
214	if ( ! $this->table_exists( 'easyfonts_fonts' ) ) {
215	return false;
216	}
217
218	$has_v3 = $this->column_exists( 'easyfonts_fonts', 'variant_key' );
219	$has_legacy = $this->column_exists( 'easyfonts_fonts', 'local_filename' )
220	\|\| $this->column_exists( 'easyfonts_fonts', 'is_active' )
221	\|\| $this->column_exists( 'easyfonts_fonts', 'downloaded_at' );
222
223	return $has_legacy && ! $has_v3;
224	}
225
226	/**
227	* Per-table status for diagnostics.
228	*
229	* @return array<string,array{exists:bool,schema_ok:bool,rows:int}>
230	*/
231	public function status(): array {
232	global $wpdb;
233
234	$map = array(
235	'fonts' => array( 'easyfonts_fonts', 'variant_key' ),
236	'usage' => array( 'easyfonts_usage', 'usage_key' ),
237	'decisions' => array( 'easyfonts_decisions', 'route_key' ),
238	);
239
240	$out = array();
241
242	foreach ( $map as $label => [$name, $signature] ) {
243	$exists = $this->table_exists( $name );
244	$rows = 0;
245
246	if ( $exists ) {
247	$table = $wpdb->prefix . $name;
248	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
249	$rows = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
250	}
251
252	$out[ $label ] = array(
253	'exists' => $exists,
254	'schema_ok' => $exists && $this->column_exists( $name, $signature ),
255	'rows' => $rows,
256	);
257	}
258
259	return $out;
260	}
261
262	/**
263	* Does a table exist?
264	*
265	* @param string $name Unprefixed table name.
266	* @return bool
267	*/
268	private function table_exists( string $name ): bool {
269	global $wpdb;
270
271	$table = $wpdb->prefix . $name;
272
273	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
274	$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
275
276	return $found === $table;
277	}
278
279	/**
280	* Does a column exist on a table?
281	*
282	* @param string $name Unprefixed table name.
283	* @param string $column Column name.
284	* @return bool
285	*/
286	private function column_exists( string $name, string $column ): bool {
287	global $wpdb;
288
289	if ( ! $this->table_exists( $name ) ) {
290	return false;
291	}
292
293	$table = $wpdb->prefix . $name;
294
295	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
296	$found = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $column ) );
297
298	return ! empty( $found );
299	}
300
301	/**
302	* Default settings. Detectors are managed by Auto-Config, not exposed as toggles.
303	*
304	* @return array<string,mixed>
305	*/
306	public static function default_settings(): array {
307	return array(
308	'enabled' => 1,
309	'auto_config' => 1,
310	'font_display' => 'swap',
311	'strip_hints' => 1,
312	'smart_preload' => 1,
313	'metric_fallbacks' => 1,
314	'beacon' => 1,
315	'inline_css' => 0,
316	'async_blocker' => 0,
317	'subsets' => array( 'latin', 'latin-ext' ),
318	'detectors' => array(),
319	'excluded_urls' => array(),
320	'cdn_url' => '',
321	'remove_data_on_uninstall' => 0,
322	);
323	}
324	}
325