fluent-cart / database / Migrations / AttributeTermsMigrator.php

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

267 lines 12.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 AttributeTermsMigrator extends Migrator
9	{
10
11	public static string $tableName = 'fct_atts_terms';
12
13	public static function getSqlSchema(): string
14	{
15	$indexPrefix = static::getDbPrefix() . 'fct_attt_';
16	return "`id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
17	`group_id` BIGINT(20) UNSIGNED NOT NULL,
18	`serial` INT(11) UNSIGNED,
19	`title` VARCHAR(192) NOT NULL,
20	`slug` VARCHAR(192) NOT NULL,
21	`description` longtext NULL,
22	`settings` longtext NULL,
23	`created_at` DATETIME NULL,
24	`updated_at` DATETIME NULL,
25	INDEX `{$indexPrefix}_group_id_idx` (`group_id` ASC),
26	UNIQUE INDEX `{$indexPrefix}_group_slug_unique` (`group_id` ASC, `slug`(192) ASC)";
27	}
28
29	public static function migrated()
30	{
31	global $wpdb;
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	}
267