custom-404-pro / tests / integration / SchemaUpgradeTest.php

SchemaUpgradeTest.php in Custom 404 Pro trunk, at tests/integration/SchemaUpgradeTest.php

553 lines 20.6 KB

Raw Download Zip

1	<?php
2	/**
3	* Integration tests for the logs table schema and the in-place upgrade path.
4	*
5	* @package Custom_404_Pro
6	*/
7
8	/**
9	* Tests ActivateClass::create_tables() and PluginClass::maybe_upgrade().
10	*
11	* The upgrade path matters more than the activation path: most users update the
12	* plugin without deactivating it first, so register_activation_hook never fires
13	* for them and everything has to be reapplied on plugins_loaded instead.
14	*/
15	class C404P_Integration_SchemaUpgradeTest extends WP_UnitTestCase {
16
17	/**
18	* Helpers instance.
19	*
20	* @var Helpers
21	*/
22	private $helpers;
23
24	/**
25	* Fully-qualified logs table name.
26	*
27	* @var string
28	*/
29	private $table;
30
31	/**
32	* Set up: start from a clean slate with no table and no stored db version.
33	*/
34	public function setUp(): void {
35	global $wpdb;
36
37	parent::setUp();
38
39	$admin_id = self::factory()->user->create( array( 'role' => 'administrator' ) );
40	wp_set_current_user( $admin_id );
41
42	$this->helpers = new Helpers();
43	$this->table = $wpdb->prefix . $this->helpers->table_logs;
44
45	$wpdb->query( 'DROP TABLE IF EXISTS ' . $this->table ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
46	delete_option( 'custom_404_pro_db_version' );
47	wp_clear_scheduled_hook( 'custom_404_pro_prune_logs' );
48
49	// The upgrade only runs in an admin/cron/CLI request, so most tests here
50	// have to look like one. Tests that assert the front-end guard clear it.
51	set_current_screen( 'dashboard' );
52
53	// Surface MySQL errors instead of letting a broken ALTER pass silently.
54	$wpdb->suppress_errors( false );
55	$wpdb->show_errors( false );
56	$wpdb->last_error = '';
57	}
58
59	/**
60	* Tear down: drop the table and clear scheduled events.
61	*/
62	public function tearDown(): void {
63	global $wpdb;
64
65	$wpdb->query( 'DROP TABLE IF EXISTS ' . $this->table ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
66	delete_option( 'custom_404_pro_db_version' );
67	wp_clear_scheduled_hook( 'custom_404_pro_prune_logs' );
68
69	parent::tearDown();
70	}
71
72	/**
73	* Asserts the last query did not produce a MySQL error.
74	*
75	* @param string $message Assertion message.
76	*/
77	private function assertNoDbError( string $message ) { // phpcs:ignore WordPress.NamingConventions.ValidFunctionName.MethodNameInvalid
78	global $wpdb;
79	$this->assertSame( '', (string) $wpdb->last_error, $message . ' MySQL error: ' . $wpdb->last_error );
80	}
81
82	/**
83	* Returns the AUTO_INCREMENT counter for the logs table.
84	*
85	* @return int Next auto-increment value.
86	*/
87	private function get_auto_increment(): int {
88	global $wpdb;
89	return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
90	$wpdb->prepare(
91	'SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_schema = DATABASE() AND table_name = %s',
92	$this->table
93	)
94	);
95	}
96
97	/**
98	* Inserts $count log rows in a single statement.
99	*
100	* @param int $count Number of rows to insert.
101	*/
102	private function seed_rows( int $count ) {
103	global $wpdb;
104	$values = array();
105	for ( $i = 0; $i < $count; $i++ ) {
106	$values[] = $wpdb->prepare( '(%s, %s, %s, %s)', '10.0.0.' . ( $i % 250 ), '/missing-' . $i, 'https://ref.example/' . $i, 'crawler-' . $i );
107	}
108	$wpdb->query( 'INSERT INTO ' . $this->table . ' (ip, path, referer, user_agent) VALUES ' . implode( ',', $values ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
109	}
110
111	/**
112	* Returns the index names defined on the logs table.
113	*
114	* @return array<string> Index names.
115	*/
116	private function get_index_names(): array {
117	global $wpdb;
118	$rows = $wpdb->get_results( 'SHOW INDEX FROM ' . $this->table ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
119	$names = array();
120	foreach ( (array) $rows as $row ) {
121	$names[] = $row->Key_name; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase -- wpdb column object property
122	}
123	return array_unique( $names );
124	}
125
126	/**
127	* Returns the declared column type for a logs table column.
128	*
129	* @param string $column Column name.
130	* @return string Column type as reported by MySQL, lowercased.
131	*/
132	private function get_column_type( string $column ): string {
133	global $wpdb;
134	$row = $wpdb->get_row( $wpdb->prepare( 'SHOW COLUMNS FROM ' . $this->table . ' LIKE %s', $column ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
135	return null === $row ? '' : strtolower( $row->Type ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase -- wpdb column object property
136	}
137
138	/**
139	* Creates the logs table using the pre-3.16.0 schema.
140	*
141	* Deliberately hand-written rather than calling create_tables(), so the test
142	* exercises a genuine upgrade from what existing installations actually have
143	* on disk: a narrow id column and no index on `created`.
144	*/
145	private function create_legacy_table() {
146	global $wpdb;
147	$charset_collate = $wpdb->get_charset_collate();
148	$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
149	"CREATE TABLE {$this->table} (
150	id mediumint(9) NOT NULL AUTO_INCREMENT,
151	ip text,
152	path text,
153	referer text,
154	user_agent text,
155	created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
156	updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
157	PRIMARY KEY (id)
158	) {$charset_collate}"
159	);
160	}
161
162	// -------------------------------------------------------------------------
163	// Fresh install schema
164	// -------------------------------------------------------------------------
165
166	/**
167	* A freshly created table must carry an index on `created`.
168	*
169	* The retention policy sorts and filters on this column, so without the
170	* index every daily prune is a full table scan.
171	*/
172	public function test_fresh_table_has_index_on_created() {
173	ActivateClass::create_tables();
174
175	$this->assertContains( 'created', $this->get_index_names(), 'The logs table must have an index on `created`.' );
176	}
177
178	/**
179	* A freshly created table must use a bigint id.
180	*/
181	public function test_fresh_table_id_column_is_bigint() {
182	ActivateClass::create_tables();
183
184	$this->assertStringContainsString( 'bigint', $this->get_column_type( 'id' ), 'The logs table id column must be bigint.' );
185	}
186
187	/**
188	* Repeated create_tables() calls must report no further schema changes.
189	*
190	* This is the guard against dbDelta reissuing the same ALTER TABLE forever.
191	* dbDelta compares the schema string against SHOW COLUMNS output, and the
192	* comparison is fragile in two ways this table touches:
193	*
194	* - MySQL 8.0.19+ drops the display width from integer types, so the
195	* `bigint(20) unsigned` in the definition comes back as
196	* `bigint unsigned`.
197	* - dbDelta parses the definition one field per line; collapsing two
198	* columns onto one line makes it emit a malformed ALTER.
199	*
200	* Either mistake turns the once-per-version upgrade into an ALTER TABLE on
201	* every upgrade check. Asserting dbDelta reports zero changes on the second
202	* and third call catches both.
203	*/
204	public function test_repeated_create_tables_reports_no_further_changes() {
205	$first = ActivateClass::create_tables();
206	$this->assertNotEmpty( $first, 'The first call should report creating the table.' );
207
208	$second = ActivateClass::create_tables();
209	$third = ActivateClass::create_tables();
210
211	$this->assertSame( array(), $second, 'The second create_tables() call must report no schema changes.' );
212	$this->assertSame( array(), $third, 'The third create_tables() call must report no schema changes.' );
213	$this->assertNoDbError( 'Repeated create_tables() calls must not error.' );
214	}
215
216	/**
217	* Upgrading a legacy table must settle after one pass.
218	*/
219	public function test_upgrade_from_legacy_schema_settles_after_one_pass() {
220	$this->create_legacy_table();
221
222	$applied = ActivateClass::create_tables();
223	$this->assertNotEmpty( $applied, 'The upgrade should report the schema changes it applied.' );
224
225	$this->assertSame( array(), ActivateClass::create_tables(), 'A second pass must report no changes.' );
226	$this->assertNoDbError( 'The legacy upgrade must not error.' );
227	}
228
229	/**
230	* Running create_tables() twice must not duplicate indexes.
231	*/
232	public function test_create_tables_does_not_duplicate_indexes() {
233	ActivateClass::create_tables();
234	$first = $this->get_index_names();
235
236	ActivateClass::create_tables();
237	$second = $this->get_index_names();
238
239	$this->assertSame( $first, $second, 'Repeated create_tables() calls must not change the schema.' );
240	}
241
242	// -------------------------------------------------------------------------
243	// Upgrade path
244	// -------------------------------------------------------------------------
245
246	/**
247	* Upgrading in place must add the missing index to an existing table.
248	*
249	* Regression: the plugins_loaded upgrade routine only ran the legacy options
250	* migration, so schema changes never reached users who updated without
251	* deactivating the plugin first.
252	*/
253	public function test_upgrade_adds_index_to_existing_legacy_table() {
254	$this->create_legacy_table();
255	$this->assertNotContains( 'created', $this->get_index_names(), 'Precondition: the legacy table has no index on created.' );
256
257	( new PluginClass() )->maybe_upgrade();
258
259	$this->assertContains( 'created', $this->get_index_names(), 'The upgrade must add the index on `created`.' );
260	}
261
262	/**
263	* Upgrading in place must widen the id column on an existing table.
264	*/
265	public function test_upgrade_widens_id_column_on_existing_legacy_table() {
266	$this->create_legacy_table();
267	$this->assertStringContainsString( 'mediumint', $this->get_column_type( 'id' ), 'Precondition: the legacy table uses mediumint.' );
268
269	( new PluginClass() )->maybe_upgrade();
270
271	$this->assertStringContainsString( 'bigint', $this->get_column_type( 'id' ), 'The upgrade must widen id to bigint.' );
272	}
273
274	/**
275	* The upgrade must preserve existing log rows.
276	*/
277	public function test_upgrade_preserves_existing_log_rows() {
278	global $wpdb;
279
280	$this->create_legacy_table();
281	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
282	$this->table,
283	array(
284	'ip' => '203.0.113.7',
285	'path' => '/an-important-404',
286	'referer' => 'https://example.com',
287	'user_agent' => 'PHPUnit',
288	)
289	);
290
291	( new PluginClass() )->maybe_upgrade();
292
293	$rows = $wpdb->get_results( 'SELECT * FROM ' . $this->table, ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
294	$this->assertCount( 1, $rows, 'The upgrade must not drop existing log rows.' );
295	$this->assertSame( '/an-important-404', $rows[0]['path'] );
296	}
297
298	/**
299	* The upgrade must schedule the daily prune cron event.
300	*/
301	public function test_upgrade_schedules_the_prune_cron_event() {
302	$this->assertFalse( wp_next_scheduled( 'custom_404_pro_prune_logs' ), 'Precondition: no prune event scheduled.' );
303
304	( new PluginClass() )->maybe_upgrade();
305
306	$this->assertNotFalse( wp_next_scheduled( 'custom_404_pro_prune_logs' ), 'The upgrade must schedule the daily prune event.' );
307	}
308
309	/**
310	* The upgrade must record the db version so it does not rerun every request.
311	*/
312	public function test_upgrade_records_the_db_version() {
313	( new PluginClass() )->maybe_upgrade();
314
315	$this->assertSame( CUSTOM_404_PRO_VERSION, get_option( 'custom_404_pro_db_version' ) );
316	}
317
318	/**
319	* Once the db version matches, the upgrade must not run again.
320	*
321	* dbDelta on every page load would be an unacceptable cost on a busy site.
322	*/
323	public function test_upgrade_is_a_noop_once_the_db_version_matches() {
324	global $wpdb;
325
326	update_option( 'custom_404_pro_db_version', CUSTOM_404_PRO_VERSION );
327
328	$queries_before = $wpdb->num_queries;
329	( new PluginClass() )->maybe_upgrade();
330	$queries_after = $wpdb->num_queries;
331
332	$this->assertSame( $queries_before, $queries_after, 'A completed upgrade must not issue further queries.' );
333	$this->assertSame( '', $wpdb->get_var( "SHOW TABLES LIKE '{$this->table}'" ) ?? '', 'No table should have been created.' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
334	}
335
336	/**
337	* The deprecated method name must still delegate to the new routine.
338	*/
339	public function test_deprecated_alias_still_performs_the_upgrade() {
340	( new PluginClass() )->maybe_migrate_legacy_options();
341
342	$this->assertSame( CUSTOM_404_PRO_VERSION, get_option( 'custom_404_pro_db_version' ) );
343	$this->assertContains( 'created', $this->get_index_names(), 'The deprecated alias must still apply schema changes.' );
344	}
345
346	// -------------------------------------------------------------------------
347	// Pruning against the indexed schema
348	// -------------------------------------------------------------------------
349
350	/**
351	* The prune queries must still behave correctly against the new schema.
352	*/
353	public function test_prune_still_works_against_the_upgraded_schema() {
354	global $wpdb;
355
356	$this->create_legacy_table();
357	( new PluginClass() )->maybe_upgrade();
358
359	for ( $i = 0; $i < 5; $i++ ) {
360	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
361	$this->table,
362	array(
363	'ip' => '10.0.0.1',
364	'path' => '/missing-' . $i,
365	'referer' => '',
366	'user_agent' => 'PHPUnit',
367	)
368	);
369	}
370
371	$this->helpers->update_settings(
372	array(
373	'log_retention_count' => 2,
374	'log_retention_days' => 0,
375	)
376	);
377
378	$deleted = $this->helpers->prune_logs();
379
380	$this->assertSame( 3, $deleted );
381	$this->assertSame( 2, $this->helpers->get_logs_count() );
382	}
383
384	// -------------------------------------------------------------------------
385	// ALTER TABLE safety
386	// -------------------------------------------------------------------------
387
388	/**
389	* The upgrade must complete without a MySQL error.
390	*
391	* A malformed dbDelta definition fails as a SQL error that WordPress
392	* swallows, leaving the table half-migrated and the db version recorded as
393	* done. Asserting on last_error catches that.
394	*/
395	public function test_upgrade_produces_no_sql_error() {
396	$this->create_legacy_table();
397	$this->seed_rows( 50 );
398
399	( new PluginClass() )->maybe_upgrade();
400
401	$this->assertNoDbError( 'The schema upgrade must not produce a SQL error.' );
402	}
403
404	/**
405	* The ALTER must preserve primary key values and the AUTO_INCREMENT counter.
406	*
407	* Widening the id column rebuilds the table. If ids were renumbered, the
408	* delete-by-id links on the Logs screen would start pointing at the wrong
409	* rows; if AUTO_INCREMENT reset, new inserts would collide.
410	*/
411	public function test_upgrade_preserves_id_values_and_auto_increment() {
412	global $wpdb;
413
414	$this->create_legacy_table();
415	$this->seed_rows( 25 );
416
417	$ids_before = $wpdb->get_col( 'SELECT id FROM ' . $this->table . ' ORDER BY id ASC' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
418	$next_before = $this->get_auto_increment();
419
420	( new PluginClass() )->maybe_upgrade();
421
422	$ids_after = $wpdb->get_col( 'SELECT id FROM ' . $this->table . ' ORDER BY id ASC' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
423
424	$this->assertSame( $ids_before, $ids_after, 'The ALTER must preserve every id value.' );
425	$this->assertGreaterThanOrEqual( $next_before, $this->get_auto_increment(), 'AUTO_INCREMENT must not go backwards.' );
426	}
427
428	/**
429	* Column contents must survive the rebuild byte for byte.
430	*/
431	public function test_upgrade_preserves_row_contents_exactly() {
432	global $wpdb;
433
434	$this->create_legacy_table();
435	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
436	$this->table,
437	array(
438	'ip' => '198.51.100.42',
439	'path' => '/path/with spaces/&ampersand?q=1',
440	'referer' => 'https://example.com/a?b=c&d=e',
441	'user_agent' => 'Mozilla/5.0 (X11; Linux) "quoted" \'single\'',
442	)
443	);
444	$before = $wpdb->get_row( 'SELECT ip, path, referer, user_agent FROM ' . $this->table, ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
445
446	( new PluginClass() )->maybe_upgrade();
447
448	$after = $wpdb->get_row( 'SELECT ip, path, referer, user_agent FROM ' . $this->table, ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
449
450	$this->assertSame( $before, $after, 'The ALTER must preserve column contents exactly.' );
451	}
452
453	/**
454	* A table with a meaningful number of rows must migrate intact.
455	*
456	* Kept modest so CI stays fast. A 1,000,000-row rehearsal of this same path
457	* completed in about 3 seconds on MySQL 8 with every row and id preserved.
458	*/
459	public function test_upgrade_preserves_a_populated_table() {
460	$this->create_legacy_table();
461	$this->seed_rows( 5000 );
462
463	( new PluginClass() )->maybe_upgrade();
464
465	$this->assertNoDbError( 'Upgrading a populated table must not error.' );
466	$this->assertSame( 5000, $this->helpers->get_logs_count(), 'Every row must survive the ALTER.' );
467	$this->assertContains( 'created', $this->get_index_names() );
468	$this->assertStringContainsString( 'bigint', $this->get_column_type( 'id' ) );
469	}
470
471	// -------------------------------------------------------------------------
472	// Request context gating
473	// -------------------------------------------------------------------------
474
475	/**
476	* A front-end request must never trigger the ALTER.
477	*
478	* On a site with a very large logs table the rebuild takes seconds. That
479	* cost belongs on an administrator's own page load, not on a visitor who
480	* happened to hit a 404.
481	*/
482	public function test_upgrade_does_not_run_on_a_front_end_request() {
483	global $wpdb;
484
485	set_current_screen( 'front' );
486	$this->create_legacy_table();
487
488	( new PluginClass() )->maybe_upgrade();
489
490	$this->assertNotContains( 'created', $this->get_index_names(), 'A front-end request must not alter the schema.' );
491	$this->assertStringContainsString( 'mediumint', $this->get_column_type( 'id' ), 'A front-end request must not alter the schema.' );
492	$this->assertFalse( get_option( 'custom_404_pro_db_version' ), 'A skipped upgrade must not be recorded as done.' );
493	}
494
495	/**
496	* Skipping the upgrade on the front end must leave it pending, so the next
497	* admin request still performs it.
498	*/
499	public function test_upgrade_still_runs_on_the_next_admin_request() {
500	set_current_screen( 'front' );
501	$this->create_legacy_table();
502	( new PluginClass() )->maybe_upgrade();
503
504	set_current_screen( 'dashboard' );
505	( new PluginClass() )->maybe_upgrade();
506
507	$this->assertContains( 'created', $this->get_index_names(), 'The next admin request must apply the upgrade.' );
508	$this->assertSame( CUSTOM_404_PRO_VERSION, get_option( 'custom_404_pro_db_version' ) );
509	}
510
511	/**
512	* A cron run must be allowed to perform the upgrade.
513	*
514	* Signalled through the wp_doing_cron filter rather than by defining
515	* DOING_CRON: a constant cannot be unset, so defining it here would leak
516	* into every test that ran afterwards and silently satisfy the context gate
517	* for all of them.
518	*/
519	public function test_upgrade_runs_during_cron() {
520	set_current_screen( 'front' );
521	$this->create_legacy_table();
522
523	add_filter( 'wp_doing_cron', '__return_true' );
524	( new PluginClass() )->maybe_upgrade();
525	remove_filter( 'wp_doing_cron', '__return_true' );
526
527	$this->assertContains( 'created', $this->get_index_names(), 'A cron request must be allowed to upgrade.' );
528	$this->assertFalse( wp_doing_cron(), 'The cron signal must not leak past this test.' );
529	}
530
531	/**
532	* Front-end 404 logging must keep working while the upgrade is pending.
533	*
534	* The schema gate is only safe if the un-upgraded table still serves the
535	* plugin's core job.
536	*/
537	public function test_logging_still_works_while_the_upgrade_is_pending() {
538	set_current_screen( 'front' );
539	$this->create_legacy_table();
540	( new PluginClass() )->maybe_upgrade();
541
542	$log = new stdClass();
543	$log->ip = '203.0.113.9';
544	$log->path = '/still-logging';
545	$log->referer = '';
546	$log->user_agent = 'PHPUnit';
547	$this->helpers->create_logs( array( $log ), false );
548
549	$this->assertNoDbError( 'Logging must work against the un-upgraded table.' );
550	$this->assertSame( 1, $this->helpers->get_logs_count() );
551	}
552	}
553