fluent-cart / database / Migrations / AttributeGroupsMigrator.php

AttributeGroupsMigrator.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at database/Migrations/AttributeGroupsMigrator.php

245 lines 11.7 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database\Migrations;
4
5	use FluentCart\Database\Migrations\Migrator;
6	use FluentCart\Framework\Database\Schema;
7
8	class AttributeGroupsMigrator extends Migrator
9	{
10	public static string $tableName = 'fct_atts_groups';
11
12	public static function getSqlSchema(): string
13	{
14	return "`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
15	`title` VARCHAR(192) NOT NULL,
16	`slug` VARCHAR(192) NOT NULL UNIQUE,
17	`description` longtext NULL,
18	`settings` longtext NULL,
19	`serial` INT(11) UNSIGNED NOT NULL DEFAULT 0,
20	`is_system` TINYINT(1) NOT NULL DEFAULT 0,
21	`created_at` DATETIME NULL,
22	`updated_at` DATETIME NULL";
23	}
24
25	public static function migrated()
26	{
27	global $wpdb;
28	$tableName = static::getTableName();
29	$slugUniqueName = static::getDbPrefix() . 'fct_atts_grp_slug_unique';
30	$slugTempIndex = static::getDbPrefix() . 'fct_atts_grp_slug_tmp';
31
32	// Defensive: dbDelta can silently fail to create the table (read-only
33	// DB user, full disk, plugin conflict). Bail before running cleanup
34	// queries that would error against a missing table and surface as
35	// "unexpected output during activation" warnings.
36	if (!Schema::hasTable(static::$tableName)) {
37	return;
38	}
39
40	// Add columns before the slug-dedupe transaction — independent of slug
41	// cleanup, so a dedupe failure (non-convergent duplicates) cannot block
42	// these columns from landing on existing installs.
43	static::addColumnIfNotExists('is_system', 'TINYINT(1) NOT NULL DEFAULT 0');
44	// `serial` drives the merchant's manual drag-order of attribute groups in
45	// the Attributes library. New installs get it from getSqlSchema(); existing
46	// installs get it here so the column lands on every activation. NOT NULL
47	// DEFAULT 0 so existing rows (and any insert that omits serial) land at 0 —
48	// our "unassigned" sentinel — rather than NULL, which would sort ahead of
49	// every ordered row in MySQL.
50	static::addColumnIfNotExists('serial', 'INT(11) UNSIGNED NOT NULL DEFAULT 0');
51
52	// One-time backfill: give every unassigned group (serial 0 / NULL) a dense
53	// serial so the manual order has a stable starting point. Assigned in the
54	// legacy display order (system groups first, then title) so existing
55	// installs keep a familiar order until the merchant drags. New serials
56	// continue after any already-assigned (>0) rows so a backfill never
57	// collides with a prior reorder. Gated on the presence of unassigned rows
58	// so it's a no-op once every group has serial >= 1. The per-row UPDATE loop
59	// is a bounded one-shot (groups are capped at 200) wrapped in a transaction
60	// so a mid-loop failure rolls back rather than leaving a partial backfill.
61	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
62	$serialBackfillIds = $wpdb->get_col($wpdb->prepare(
63	"SELECT id FROM %i WHERE serial = 0 OR serial IS NULL ORDER BY is_system DESC, title ASC, id ASC",
64	$tableName
65	));
66	if (!empty($serialBackfillIds)) {
67	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
68	$nextSerial = (int) $wpdb->get_var($wpdb->prepare(
69	"SELECT COALESCE(MAX(serial), 0) FROM %i WHERE serial > 0",
70	$tableName
71	));
72	$wpdb->query('START TRANSACTION');
73	try {
74	foreach ($serialBackfillIds as $groupId) {
75	$nextSerial++;
76	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
77	$wpdb->query($wpdb->prepare(
78	"UPDATE %i SET serial = %d WHERE id = %d",
79	$tableName,
80	$nextSerial,
81	(int) $groupId
82	));
83	}
84	$wpdb->query('COMMIT');
85	} catch (\Throwable $e) {
86	$wpdb->query('ROLLBACK');
87	throw $e;
88	}
89	}
90
91	// Performance: the slug dedupe self-join below joins g1.slug = g2.slug.
92	// On a fresh install the inline UNIQUE on slug from getSqlSchema()
93	// covers the join, but on an existing install with duplicates dbDelta
94	// silently failed to add that UNIQUE, leaving the self-join with no
95	// covering index. Without this temp index the UPDATE degrades to a
96	// quadratic full table scan and can time out activation on large
97	// groups tables. The temp is dropped after the post-cleanup UNIQUE
98	// add below takes over.
99	static::addIndexIfNotExists($slugTempIndex, ['slug']);
100
101	// Reconcile duplicate slugs before any constraint enforcement can fail.
102	// The inline UNIQUE on slug in getSqlSchema() applies cleanly on fresh
103	// CREATE TABLE, but on existing installs whose data was touched by raw
104	// SQL or partial imports, duplicates can sneak in and block the index.
105	// Append a per-row suffix to the higher-id duplicate so the lowest id
106	// keeps the canonical slug and no data is lost. LEFT slug 170 caps
107	// the base so the dup-id suffix cannot overflow the column VARCHAR
108	// 192 limit even with the largest plausible BIGINT id.
109	//
110	// The dedupe loops with an increasing iteration counter appended to
111	// the generated slug so a pathological collision (e.g. a pre-existing
112	// row whose slug already matches the first-pass generated slug-dup-id
113	// value) gets resolved on a subsequent pass with extra entropy. Cap
114	// at 50 iterations as a hard safety net so the migrator cannot spin
115	// forever on truly pathological input. Each iteration appends a
116	// larger counter so a fresh collision space is sampled every pass —
117	// even on heavily corrupted data 50 rounds converge in practice.
118	// EXISTS is used instead of COUNT for the convergence check so the
119	// verification can short-circuit on the first duplicate row rather
120	// than scanning the whole table on every pass.
121	// Wrap the dedupe loop in a transaction so a mid-loop failure (lock
122	// timeout, OOM, deadlock) rolls every UPDATE back to the pre-loop
123	// state instead of leaving the table partially mutated. ALTER TABLE
124	// statements above (temp index ADD) and below (final UNIQUE) auto-
125	// commit and stay outside this transaction by design.
126	$iterationLimit = 50;
127	$converged = false;
128	$wpdb->query('START TRANSACTION');
129	try {
130	for ($iteration = 1; $iteration <= $iterationLimit; $iteration++) {
131	$suffix = $iteration === 1 ? "CONCAT('-dup-', g1.id)"
132	: "CONCAT('-dup-', g1.id, '-', " . (int) $iteration . ")";
133	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
134	$wpdb->query($wpdb->prepare(
135	"UPDATE %i g1
136	INNER JOIN %i g2 ON g1.slug = g2.slug AND g1.id > g2.id
137	SET g1.slug = CONCAT(LEFT(g1.slug, 160), {$suffix})",
138	$tableName,
139	$tableName
140	));
141
142	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
143	$hasDuplicate = (int) $wpdb->get_var($wpdb->prepare(
144	'SELECT EXISTS(SELECT 1 FROM %i GROUP BY slug HAVING COUNT(*) > 1)',
145	$tableName
146	));
147	if ($hasDuplicate === 0) {
148	$converged = true;
149	break;
150	}
151	}
152
153	// Throw BEFORE COMMIT so the catch block can rollback every
154	// partial UPDATE pass. If we committed first and threw after,
155	// the table would persist 50 iterations of -dup-id-N suffixes
156	// even though activation failed. Matches the throw-before-commit
157	// pattern in AttributeTermsMigrator::migrated().
158	if (!$converged) {
159	throw new \RuntimeException(
160	'Attributes migration: failed to converge group slug dedupe within ' .
161	$iterationLimit . ' iterations on table ' . $tableName . '. Manual repair required.'
162	);
163	}
164
165	$wpdb->query('COMMIT');
166	} catch (\Throwable $e) {
167	$wpdb->query('ROLLBACK');
168	throw $e;
169	}
170
171	static::dropLegacyTitleUniqueIndexes();
172
173	// Now that duplicate slugs are reconciled, ensure the UNIQUE constraint
174	// is actually present on the slug column. On existing installs with
175	// pre-existing duplicates, dbDelta silently fails to add the inline
176	// UNIQUE declared in getSqlSchema() and never retries it — leaving the
177	// table without DB-level slug uniqueness until a manual repair. Detect
178	// any existing unique index on the slug column (handles both the
179	// auto-named index dbDelta would have created from the inline UNIQUE
180	// and our explicit named version) and add the explicit named one if
181	// nothing covers it.
182	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
183	$existingSlugUnique = $wpdb->get_results($wpdb->prepare(
184	"SHOW INDEX FROM %i WHERE Column_name = %s AND Non_unique = 0",
185	$tableName,
186	'slug'
187	));
188	if (empty($existingSlugUnique)) {
189	Schema::alterTable(
190	static::$tableName,
191	"ADD UNIQUE INDEX `{$slugUniqueName}` (`slug`)"
192	);
193	}
194
195	// Drop the temporary supporting index now that the final UNIQUE on
196	// slug covers the same column. Leaving the non-unique index in place
197	// would shadow the UNIQUE and waste write amplification on every
198	// insert and update.
199	static::dropIndexIfExists($slugTempIndex);
200	}
201
202	public static function dropLegacyTitleUniqueIndexes()
203	{
204	global $wpdb;
205
206	$tableName = static::getTableName();
207
208	// SHOW INDEX returns one row per column, so a composite UNIQUE that
209	// happens to include "title" alongside other columns surfaces with
210	// Column_name=title too. Pull every index row (no Column_name filter)
211	// so we can group by Key_name and confirm the candidate is a
212	// single-column UNIQUE strictly on title before dropping it. Without
213	// this guard, a future schema that adds e.g. UNIQUE(title, slug) would
214	// be silently dropped on every activation.
215	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
216	$rows = $wpdb->get_results($wpdb->prepare(
217	"SHOW INDEX FROM %i",
218	$tableName
219	), ARRAY_A);
220
221	if (empty($rows)) {
222	return;
223	}
224
225	$byKey = [];
226	foreach ($rows as $row) {
227	$byKey[$row['Key_name']][] = $row;
228	}
229
230	foreach ($byKey as $keyName => $cols) {
231	if (count($cols) !== 1) {
232	continue; // composite — leave alone
233	}
234	$only = $cols[0];
235	if (($only['Non_unique'] ?? '1') !== '0') {
236	continue; // not unique
237	}
238	if (($only['Column_name'] ?? '') !== 'title') {
239	continue; // unique but not on title
240	}
241	static::dropIndexIfExists($keyName);
242	}
243	}
244	}
245