← All changes | database/Migrations/AttributeTermsMigrator.php +241 -3 1.3.27 → 1.6.5 View file →

		@@ -1,8 +1,9 @@
1	1	<?php
2	2
3	3	namespace FluentCart\Database\Migrations;
4	4
	5	+use FluentCart\Database\Migrations\Migrator;
5	6	use FluentCart\Framework\Database\Schema;
6	7
7	8	class AttributeTermsMigrator extends Migrator
8	9	{
		@@ -12,9 +13,9 @@
12	13	public static function getSqlSchema(): string
13	14	{
14	15	$indexPrefix = static::getDbPrefix() . 'fct_attt_';
15	16	return "`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
16		- `group_id` BIGINT(20) UNSIGNED,
	17	+ `group_id` BIGINT(20) UNSIGNED NOT NULL,
17	18	`serial` INT(11) UNSIGNED,
18	19	`title` VARCHAR(192) NOT NULL,
19	20	`slug` VARCHAR(192) NOT NULL,
20	21	`description` longtext NULL,
		@@ -20,9 +21,246 @@
20	21	`description` longtext NULL,
21	22	`settings` longtext NULL,
22	23	`created_at` DATETIME NULL,
23	24	`updated_at` DATETIME NULL,
24		- INDEX `{$indexPrefix}_group_id_idx` (`group_id` ASC)";
	25	+ INDEX `{$indexPrefix}_group_id_idx` (`group_id` ASC),
	26	+ UNIQUE INDEX `{$indexPrefix}_group_slug_unique` (`group_id` ASC, `slug`(192) ASC)";
25	27	}
26		-}
27	28
	29	+ public static function migrated()
	30	+ {
	31	+ global $wpdb;
28	32
	33	+ $indexPrefix = static::getDbPrefix() . 'fct_attt_';
	34	+ $tableName = static::getTableName();
	35	+ $relationsTable = static::getDbPrefix() . 'fct_atts_relations';
	36	+ $relationsIndexPrefix = static::getDbPrefix() . 'fct_at_rel_';
	37	+
	38	+ // Defensive: bail if either table is missing. dbDelta can silently
	39	+ // fail to create tables (read-only DB user, full disk, plugin conflict),
	40	+ // and the cross-table cleanup below would error against a missing
	41	+ // table and surface as "unexpected output during activation" warnings.
	42	+ if (!Schema::hasTable(static::$tableName)
	43	+ || !Schema::hasTable(AttributeObjectRelationsMigrator::$tableName)) {
	44	+ return;
	45	+ }
	46	+
	47	+ $termsUniqueName = "{$indexPrefix}_group_slug_unique";
	48	+ $relationsUniqueName = "{$relationsIndexPrefix}_obj_grp_term_unique";
	49	+ $termsTempIndexName = "{$indexPrefix}_grp_slug_tmp";
	50	+ $relsTempIndexName = "{$relationsIndexPrefix}_obj_grp_term_tmp";
	51	+ $relsOrphanTempIndex = "{$relationsIndexPrefix}_term_grp_tmp";
	52	+
	53	+ // Performance: every cleanup query below joins on (group_id, slug) on
	54	+ // terms, (object_id, group_id, term_id) on relations, or
	55	+ // (term_id, group_id) on relations (the orphan LEFT JOIN sweep). On
	56	+ // existing installs whose tables predate the final UNIQUE composites,
	57	+ // those self-joins degrade to full table scans and can time out during
	58	+ // activation. Add temporary non-unique supporting indexes first so the
	59	+ // cleanup runs against an index either way. The temps are dropped at
	60	+ // the bottom of this method once the final UNIQUE composites take over.
	61	+ static::addIndexIfNotExists($termsTempIndexName, ['group_id', 'slug']);
	62	+ if (!static::indexExistsOnTable($relationsTable, $relsTempIndexName)) {
	63	+ Schema::alterTable(
	64	+ AttributeObjectRelationsMigrator::$tableName,
	65	+ "ADD INDEX `{$relsTempIndexName}` (`object_id` ASC, `group_id` ASC, `term_id` ASC)"
	66	+ );
	67	+ }
	68	+ if (!static::indexExistsOnTable($relationsTable, $relsOrphanTempIndex)) {
	69	+ Schema::alterTable(
	70	+ AttributeObjectRelationsMigrator::$tableName,
	71	+ "ADD INDEX `{$relsOrphanTempIndex}` (`term_id` ASC, `group_id` ASC)"
	72	+ );
	73	+ }
	74	+
	75	+ // Wrap the whole cleanup chain (every DELETE/UPDATE through the
	76	+ // terms+relations dedupe loops below) in a transaction so a mid-
	77	+ // chain failure rolls every write back to the pre-cleanup state
	78	+ // instead of leaving the tables partially repaired. ALTER TABLE
	79	+ // statements above (temp index ADDs) and below (final UNIQUE adds,
	80	+ // NOT NULL, plain index, temp DROPs) auto-commit and stay outside
	81	+ // this transaction by design.
	82	+ $iterationLimit = 50;
	83	+ $wpdb->query('START TRANSACTION');
	84	+ try {
	85	+
	86	+ // Drop relations that point at NULL-group terms BEFORE we delete those
	87	+ // terms below. A NULL-group term has no group context, so any relation
	88	+ // referencing it is meaningless and cannot be repointed (the slug-twin
	89	+ // repoint below depends on a non-NULL group_id for the JOIN). Without
	90	+ // this step, deleting the orphan term would leave the relation row
	91	+ // pointing at a non-existent term_id.
	92	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	93	+ $wpdb->query($wpdb->prepare(
	94	+ 'DELETE r FROM %i r
	95	+ INNER JOIN %i t ON r.term_id = t.id
	96	+ WHERE t.group_id IS NULL',
	97	+ $relationsTable,
	98	+ $tableName
	99	+ ));
	100	+
	101	+ // Remove orphaned terms with no group before enforcing NOT NULL.
	102	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	103	+ $wpdb->query($wpdb->prepare('DELETE FROM %i WHERE `group_id` IS NULL', $tableName));
	104	+
	105	+ // Repoint any relations that reference a term about to be discarded to its
	106	+ // surviving twin (lowest id wins), so no fct_atts_relations rows are orphaned.
	107	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	108	+ $wpdb->query($wpdb->prepare(
	109	+ 'UPDATE %i r
	110	+ INNER JOIN %i t1 ON r.term_id = t1.id
	111	+ INNER JOIN %i t2 ON t1.group_id = t2.group_id AND t1.slug = t2.slug AND t1.id > t2.id
	112	+ SET r.term_id = t2.id',
	113	+ $relationsTable,
	114	+ $tableName,
	115	+ $tableName
	116	+ ));
	117	+
	118	+ // Drop pre-existing orphan relations whose term no longer exists OR whose
	119	+ // group_id does not match the matched term's group_id. The cleanup steps
	120	+ // above all use INNER JOIN on terms, so they skip relations with dangling
	121	+ // term_id references (rows left over from terms that were hard-deleted
	122	+ // via raw SQL bypass before this migrator shipped) and rows where the
	123	+ // relation's stored group_id disagrees with its term's group_id. A LEFT
	124	+ // JOIN that matches both fields catches both classes in one pass —
	125	+ // anything that does not have a live matching term row is an orphan and
	126	+ // gets removed before the UNIQUE add below.
	127	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	128	+ $wpdb->query($wpdb->prepare(
	129	+ 'DELETE r FROM %i r
	130	+ LEFT JOIN %i t ON r.term_id = t.id AND r.group_id = t.group_id
	131	+ WHERE t.id IS NULL',
	132	+ $relationsTable,
	133	+ $tableName
	134	+ ));
	135	+
	136	+ // The repoint above can leave duplicate (object_id, group_id, term_id) rows when
	137	+ // an object was related to both a duplicate term and its surviving twin before
	138	+ // migration — both rows now have the same term_id. Also catches any pre-existing
	139	+ // duplicates (rare import artifacts) so the UNIQUE index below can be added
	140	+ // without violations. Keep the lowest id, delete the rest. Loop with
	141	+ // verification in case a concurrent write during activation produces a fresh
	142	+ // duplicate after the first DELETE pass; bail with an exception if 50
	143	+ // iterations cannot converge so activation fails loudly instead of silently
	144	+ // skipping the UNIQUE add downstream.
	145	+ $relsConverged = false;
	146	+ for ($iteration = 1; $iteration <= $iterationLimit; $iteration++) {
	147	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	148	+ $wpdb->query($wpdb->prepare(
	149	+ 'DELETE r1 FROM %i r1
	150	+ INNER JOIN %i r2
	151	+ ON r1.object_id = r2.object_id
	152	+ AND r1.group_id = r2.group_id
	153	+ AND r1.term_id = r2.term_id
	154	+ AND r1.id > r2.id',
	155	+ $relationsTable,
	156	+ $relationsTable
	157	+ ));
	158	+
	159	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	160	+ $hasDup = (int) $wpdb->get_var($wpdb->prepare(
	161	+ 'SELECT EXISTS(SELECT 1 FROM %i GROUP BY object_id, group_id, term_id HAVING COUNT(*) > 1)',
	162	+ $relationsTable
	163	+ ));
	164	+ if ($hasDup === 0) {
	165	+ $relsConverged = true;
	166	+ break;
	167	+ }
	168	+ }
	169	+ if (!$relsConverged) {
	170	+ throw new \RuntimeException(
	171	+ 'Attributes migration: failed to converge relations dedupe within ' .
	172	+ $iterationLimit . ' iterations on table ' . $relationsTable . '. Manual repair required.'
	173	+ );
	174	+ }
	175	+
	176	+ // Remove duplicate (group_id, slug) pairs, keeping the lowest id, so the
	177	+ // unique index below can be added without a constraint violation. Same
	178	+ // loop + verify + bail pattern as the relations dedupe above for symmetry.
	179	+ $termsConverged = false;
	180	+ for ($iteration = 1; $iteration <= $iterationLimit; $iteration++) {
	181	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	182	+ $wpdb->query($wpdb->prepare(
	183	+ 'DELETE t1 FROM %i t1 INNER JOIN %i t2
	184	+ ON t1.group_id = t2.group_id AND t1.slug = t2.slug AND t1.id > t2.id',
	185	+ $tableName,
	186	+ $tableName
	187	+ ));
	188	+
	189	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	190	+ $hasDup = (int) $wpdb->get_var($wpdb->prepare(
	191	+ 'SELECT EXISTS(SELECT 1 FROM %i GROUP BY group_id, slug HAVING COUNT(*) > 1)',
	192	+ $tableName
	193	+ ));
	194	+ if ($hasDup === 0) {
	195	+ $termsConverged = true;
	196	+ break;
	197	+ }
	198	+ }
	199	+ if (!$termsConverged) {
	200	+ throw new \RuntimeException(
	201	+ 'Attributes migration: failed to converge terms dedupe within ' .
	202	+ $iterationLimit . ' iterations on table ' . $tableName . '. Manual repair required.'
	203	+ );
	204	+ }
	205	+
	206	+ $wpdb->query('COMMIT');
	207	+ } catch (\Throwable $e) {
	208	+ $wpdb->query('ROLLBACK');
	209	+ throw $e;
	210	+ }
	211	+
	212	+ // Now that relations are clean, enforce the composite UNIQUE on existing
	213	+ // tables that predate it. Fresh installs already have this index from
	214	+ // AttributeObjectRelationsMigrator::getSqlSchema().
	215	+ if (!static::indexExistsOnTable($relationsTable, $relationsUniqueName)) {
	216	+ Schema::alterTable(
	217	+ AttributeObjectRelationsMigrator::$tableName,
	218	+ "ADD UNIQUE INDEX `{$relationsUniqueName}` (`object_id` ASC, `group_id` ASC, `term_id` ASC)"
	219	+ );
	220	+ }
	221	+
	222	+ // Enforce NOT NULL on existing tables that were created with the old nullable schema.
	223	+ static::modifyColumnIfExists('group_id', 'BIGINT(20) UNSIGNED NOT NULL');
	224	+
	225	+ // Add plain group_id index if missing.
	226	+ static::addIndexIfNotExists("{$indexPrefix}_group_id_idx", 'group_id');
	227	+
	228	+ // Add composite unique index — uses slug(192) prefix so built manually.
	229	+ if (!static::hasIndex($termsUniqueName)) {
	230	+ Schema::alterTable(
	231	+ static::$tableName,
	232	+ "ADD UNIQUE INDEX `{$termsUniqueName}` (`group_id` ASC, `slug`(192) ASC)"
	233	+ );
	234	+ }
	235	+
	236	+ // Drop the temporary supporting indexes now that the final UNIQUE
	237	+ // composites cover the same columns. Leaving them in place would
	238	+ // shadow the UNIQUE indexes and waste write amplification on every
	239	+ // insert/update.
	240	+ static::dropIndexIfExists($termsTempIndexName);
	241	+ if (static::indexExistsOnTable($relationsTable, $relsTempIndexName)) {
	242	+ Schema::alterTable(AttributeObjectRelationsMigrator::$tableName, "DROP INDEX `{$relsTempIndexName}`");
	243	+ }
	244	+ if (static::indexExistsOnTable($relationsTable, $relsOrphanTempIndex)) {
	245	+ Schema::alterTable(AttributeObjectRelationsMigrator::$tableName, "DROP INDEX `{$relsOrphanTempIndex}`");
	246	+ }
	247	+ }
	248	+
	249	+ /**
	250	+ * Inline equivalent of Migrator::hasIndex() for an arbitrary table — the
	251	+ * base helper hardcodes static::getTableName() so we cannot reuse it when
	252	+ * the cleanup needs to touch fct_atts_relations from inside the terms
	253	+ * migrator.
	254	+ */
	255	+ private static function indexExistsOnTable(string $tableName, string $indexName): bool
	256	+ {
	257	+ global $wpdb;
	258	+ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	259	+ $rows = $wpdb->get_results($wpdb->prepare(
	260	+ "SHOW INDEX FROM %i WHERE Key_name = %s",
	261	+ $tableName,
	262	+ $indexName
	263	+ ));
	264	+ return !empty($rows);
	265	+ }
	266	+}