easyfonts / src / Database / Migrator.php

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

321 lines 9.4 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.1';
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	last_seen DATETIME DEFAULT NULL,
124	PRIMARY KEY (id),
125	UNIQUE KEY uq_usage (usage_key),
126	KEY idx_family (family)
127	) {$charset};"
128	);
129
130	dbDelta(
131	"CREATE TABLE {$dec} (
132	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
133	route_key CHAR(40) NOT NULL,
134	route VARCHAR(500) NOT NULL,
135	device VARCHAR(10) NOT NULL DEFAULT 'any',
136	preload LONGTEXT DEFAULT NULL,
137	unload LONGTEXT DEFAULT NULL,
138	updated_at DATETIME DEFAULT NULL,
139	PRIMARY KEY (id),
140	UNIQUE KEY uq_route (route_key)
141	) {$charset};"
142	);
143
144	// dbDelta never DROPs an index that exists in the table but not in the
145	// definition, so retire the now-unused usage.idx_page (it indexed a
146	// column we never query) explicitly. Guarded + idempotent: only runs
147	// when the index is actually present, and a failure can't break install.
148	$this->drop_index_if_exists( 'easyfonts_usage', 'idx_page' );
149	}
150
151	/**
152	* Drop an index from a table if it exists. Safe no-op otherwise.
153	*
154	* @param string $name Unprefixed table name.
155	* @param string $index Index name.
156	* @return void
157	*/
158	private function drop_index_if_exists( string $name, string $index ): void {
159	global $wpdb;
160
161	if ( ! $this->table_exists( $name ) ) {
162	return;
163	}
164
165	$table = $wpdb->prefix . $name;
166
167	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
168	$found = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", $index ) );
169
170	if ( $found ) {
171	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
172	$wpdb->query( "ALTER TABLE `{$table}` DROP INDEX `{$index}`" );
173	}
174	}
175
176	/**
177	* Drop all three tables (IF EXISTS).
178	*/
179	private function drop_tables(): void {
180	global $wpdb;
181
182	foreach ( array( 'easyfonts_fonts', 'easyfonts_usage', 'easyfonts_decisions' ) as $name ) {
183	$table = $wpdb->prefix . $name;
184	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
185	$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" );
186	}
187	}
188
189	/**
190	* Are all three tables present with the current signature columns?
191	*
192	* @return bool
193	*/
194	public function tables_ok(): bool {
195	return $this->column_exists( 'easyfonts_fonts', 'variant_key' )
196	&& $this->column_exists( 'easyfonts_fonts', 'is_enabled' )
197	&& $this->column_exists( 'easyfonts_fonts', 'load_user_set' )
198	&& $this->column_exists( 'easyfonts_fonts', 'preload_user_set' )
199	&& $this->column_exists( 'easyfonts_usage', 'usage_key' )
200	&& $this->column_exists( 'easyfonts_decisions', 'route_key' );
201	}
202
203	/**
204	* Is a legacy fonts table present? Detected by an old column that
205	* the current schema never uses, alongside the absence of its signature column.
206	*
207	* @return bool
208	*/
209	public function has_legacy_schema(): bool {
210	if ( ! $this->table_exists( 'easyfonts_fonts' ) ) {
211	return false;
212	}
213
214	$has_v3 = $this->column_exists( 'easyfonts_fonts', 'variant_key' );
215	$has_legacy = $this->column_exists( 'easyfonts_fonts', 'local_filename' )
216	\|\| $this->column_exists( 'easyfonts_fonts', 'is_active' )
217	\|\| $this->column_exists( 'easyfonts_fonts', 'downloaded_at' );
218
219	return $has_legacy && ! $has_v3;
220	}
221
222	/**
223	* Per-table status for diagnostics.
224	*
225	* @return array<string,array{exists:bool,schema_ok:bool,rows:int}>
226	*/
227	public function status(): array {
228	global $wpdb;
229
230	$map = array(
231	'fonts' => array( 'easyfonts_fonts', 'variant_key' ),
232	'usage' => array( 'easyfonts_usage', 'usage_key' ),
233	'decisions' => array( 'easyfonts_decisions', 'route_key' ),
234	);
235
236	$out = array();
237
238	foreach ( $map as $label => [$name, $signature] ) {
239	$exists = $this->table_exists( $name );
240	$rows = 0;
241
242	if ( $exists ) {
243	$table = $wpdb->prefix . $name;
244	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
245	$rows = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
246	}
247
248	$out[ $label ] = array(
249	'exists' => $exists,
250	'schema_ok' => $exists && $this->column_exists( $name, $signature ),
251	'rows' => $rows,
252	);
253	}
254
255	return $out;
256	}
257
258	/**
259	* Does a table exist?
260	*
261	* @param string $name Unprefixed table name.
262	* @return bool
263	*/
264	private function table_exists( string $name ): bool {
265	global $wpdb;
266
267	$table = $wpdb->prefix . $name;
268
269	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
270	$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
271
272	return $found === $table;
273	}
274
275	/**
276	* Does a column exist on a table?
277	*
278	* @param string $name Unprefixed table name.
279	* @param string $column Column name.
280	* @return bool
281	*/
282	private function column_exists( string $name, string $column ): bool {
283	global $wpdb;
284
285	if ( ! $this->table_exists( $name ) ) {
286	return false;
287	}
288
289	$table = $wpdb->prefix . $name;
290
291	// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
292	$found = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$table}` LIKE %s", $column ) );
293
294	return ! empty( $found );
295	}
296
297	/**
298	* Default settings. Detectors are managed by Auto-Config, not exposed as toggles.
299	*
300	* @return array<string,mixed>
301	*/
302	public static function default_settings(): array {
303	return array(
304	'enabled' => 1,
305	'auto_config' => 1,
306	'font_display' => 'swap',
307	'strip_hints' => 1,
308	'smart_preload' => 1,
309	'metric_fallbacks' => 1,
310	'beacon' => 1,
311	'inline_css' => 0,
312	'async_blocker' => 0,
313	'subsets' => array( 'latin', 'latin-ext' ),
314	'detectors' => array(),
315	'excluded_urls' => array(),
316	'cdn_url' => '',
317	'remove_data_on_uninstall' => 0,
318	);
319	}
320	}
321