| Line 17... |
Line 17... |
| 17 |
-- sha256 over the normalised content: modes, values, banks, timing, schemes, tenures,
|
17 |
-- sha256 over the normalised content: modes, values, banks, timing, schemes, tenures,
|
| 18 |
-- windows. Two models with the same terms hash the same and share the badge.
|
18 |
-- windows. Two models with the same terms hash the same and share the badge.
|
| 19 |
ADD COLUMN circular_offer_key CHAR(64) NULL,
|
19 |
ADD COLUMN circular_offer_key CHAR(64) NULL,
|
| 20 |
ADD UNIQUE KEY uk_circular_offer_key (circular_document_id, circular_offer_key);
|
20 |
ADD UNIQUE KEY uk_circular_offer_key (circular_document_id, circular_offer_key);
|
| 21 |
|
21 |
|
| 22 |
-- The old row-keyed badges cannot be migrated: one of them is a fragment of what a
|
22 |
-- ⚠️ NO DELETE. The old row-keyed badges are retired by the SYNC itself, which
|
| 23 |
-- content-keyed badge now says, and there is no mapping from five fragments to one whole.
|
23 |
-- deactivates any CIRCULAR row with a NULL circular_offer_key on its next run. That is
|
| 24 |
-- They are regenerated in full by the next ingest.
|
24 |
-- strictly better than deleting them here:
|
| 25 |
--
|
25 |
--
|
| - |
|
26 |
-- * nothing is destroyed, so a bad sync is one UPDATE away from being undone
|
| 26 |
-- ⚠️ web_offer_product has NO foreign key to web_offer, so its rows do NOT cascade and
|
27 |
-- * web_offer_product has NO foreign key to web_offer, so a DELETE here would have to
|
| 27 |
-- must be cleared first or they are orphaned. web_offer_sync_shadow DOES cascade.
|
28 |
-- clear its rows separately or strand them as orphans
|
| - |
|
29 |
-- * there is no window where a circular has no badges at all - the old ones stay live
|
| - |
|
30 |
-- until the new ones exist
|
| 28 |
--
|
31 |
--
|
| 29 |
-- source='CIRCULAR' only. The 3,850 hand-authored MANUAL rows are never touched.
|
32 |
-- So this migration is purely additive and safe to run ahead of the deploy.
|
| 30 |
DELETE FROM dtr.web_offer_product
|
- |
|
| 31 |
WHERE webOfferId IN (SELECT id FROM dtr.web_offer WHERE source = 'CIRCULAR');
|
- |
|
| 32 |
|
33 |
--
|
| - |
|
34 |
-- To retire them by hand instead (not needed, but this is the statement):
|
| 33 |
DELETE FROM dtr.web_offer WHERE source = 'CIRCULAR';
|
35 |
-- UPDATE dtr.web_offer SET active = 0
|
| - |
|
36 |
-- WHERE source = 'CIRCULAR' AND circular_offer_key IS NULL;
|