← All changes | database/Migrations/AttributeGroupsMigrator.php +204 -10 1.4.1 → 1.6.5 View file →

		@@ -1,8 +1,11 @@
1	1	<?php
2	2
3	3	namespace FluentCart\Database\Migrations;
4	4
	5	+use FluentCart\Database\Migrations\Migrator;
	6	+use FluentCart\Framework\Database\Schema;
	7	+
5	8	class AttributeGroupsMigrator extends Migrator
6	9	{
7	10	public static string $tableName = 'fct_atts_groups';
8	11
		@@ -12,8 +15,10 @@
12	15	`title` VARCHAR(192) NOT NULL,
13	16	`slug` VARCHAR(192) NOT NULL UNIQUE,
14	17	`description` longtext NULL,
15	18	`settings` longtext NULL,
	19	+ `serial` INT(11) UNSIGNED NOT NULL DEFAULT 0,
	20	+ `is_system` TINYINT(1) NOT NULL DEFAULT 0,
16	21	`created_at` DATETIME NULL,
17	22	`updated_at` DATETIME NULL";
18	23	}
19	24
		@@ -18,9 +23,181 @@
18	23	}
19	24
20	25	public static function migrated()
21	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	+
22	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);
23	200	}
24	201
25	202	public static function dropLegacyTitleUniqueIndexes()
26	203	{
		@@ -27,24 +204,41 @@
27	204	global $wpdb;
28	205
29	206	$tableName = static::getTableName();
30	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.
31	215	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
32		- $indexes = $wpdb->get_results($wpdb->prepare(
33		- "SHOW INDEX FROM %i WHERE Column_name = %s",
34		- $tableName,
35		- 'title'
	216	+ $rows = $wpdb->get_results($wpdb->prepare(
	217	+ "SHOW INDEX FROM %i",
	218	+ $tableName
36	219	), ARRAY_A);
37	220
38		- if (empty($indexes)) {
	221	+ if (empty($rows)) {
39	222	return;
40	223	}
41	224
42		- foreach ($indexes as $index) {
43		- if (($index['Non_unique'] ?? '1') !== '0') {
44		- continue;
	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
45	233	}
46		-
47		- static::dropIndexIfExists($index['Key_name']);
	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);
48	242	}
49	243	}
50	244	}