auto-save: 2026-07-13T02:22:18 (1 files) — step5-mirror-sync.sh

9228a4a5bca741b8c72bc42d60dc9b937e6aa212 · 2026-07-13 02:22:21 -0700 · Steve Abrams
Files touched

A step5-mirror-sync.sh
Diff

commit 9228a4a5bca741b8c72bc42d60dc9b937e6aa212
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Jul 13 02:22:21 2026 -0700

    auto-save: 2026-07-13T02:22:18 (1 files) — step5-mirror-sync.sh
---
 step5-mirror-sync.sh | 56 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 56 insertions(+)

diff --git a/step5-mirror-sync.sh b/step5-mirror-sync.sh
new file mode 100755
index 0000000..ac43fd6
--- /dev/null
+++ b/step5-mirror-sync.sh
@@ -0,0 +1,56 @@
+#!/usr/bin/env bash
+# STEP 5 — sync the dw_unified.shopify_products MIRROR to the new DWPF-600xxx numbers.
+# Run ONLY after the live Shopify bulk (step 4) has fully completed, to avoid live/mirror drift.
+# Reversible: takes a backup table before writing; whole thing is one transaction.
+set -euo pipefail
+export PGURL="postgresql:///dw_unified?host=/tmp"
+BAK="_bak_sp_pf_sku_sync_20260713"
+
+echo "=== PRE-CHECK: bulk must be done (expect 0 old-block DWPF still live on Shopify side is separate; here we sync the mirror) ==="
+psql "$PGURL" -tA -c "SELECT 'mirror DWPF old-block rows='||count(*) FROM shopify_products WHERE regexp_replace(sku,'-Sample\$','','i') ~ '^DWPF-(15|20|1[0-9])';"
+
+echo "=== Running backup + update + verify in ONE transaction ==="
+psql "$PGURL" -v ON_ERROR_STOP=1 <<SQL
+BEGIN;
+
+-- 1. Backup the exact rows we will touch (id + both sku columns) for rollback.
+DROP TABLE IF EXISTS ${BAK};
+CREATE TABLE ${BAK} AS
+SELECT id, sku AS old_sku, variant_sku AS old_variant_sku
+FROM shopify_products
+WHERE sku ILIKE 'DWPF-%'
+  AND regexp_replace(sku,'-Sample\$','','i') IN (SELECT old_base FROM pf_sku_renumber_map);
+
+-- 2. Update sku + variant_sku via the deterministic 1:1 map, preserving any suffix.
+UPDATE shopify_products sp
+SET sku = m.new_base || substring(sp.sku from length(m.old_base)+1),
+    variant_sku = CASE
+        WHEN sp.variant_sku ILIKE 'DWPF-%'
+         AND regexp_replace(sp.variant_sku,'-Sample\$','','i') = m.old_base
+        THEN m.new_base || substring(sp.variant_sku from length(m.old_base)+1)
+        ELSE sp.variant_sku END
+FROM pf_sku_renumber_map m
+WHERE sp.sku ILIKE 'DWPF-%'
+  AND regexp_replace(sp.sku,'-Sample\$','','i') = m.old_base;
+
+-- 3. Verify inside the txn: 0 old-block left, all DWPF now 6-digit.
+SELECT 'after_update' AS phase,
+       count(*) FILTER (WHERE regexp_replace(sku,'-Sample\$','','i') ~ '^DWPF-6[0-9]{5}\$')  AS now_6digit,
+       count(*) FILTER (WHERE regexp_replace(sku,'-Sample\$','','i') !~ '^DWPF-6[0-9]{5}\$') AS not_6digit_should_be_0
+FROM shopify_products WHERE sku ILIKE 'DWPF-%';
+
+-- Guard: abort if any DWPF row is not in the new 6-digit block.
+DO \$\$
+DECLARE bad int;
+BEGIN
+  SELECT count(*) INTO bad FROM shopify_products
+   WHERE sku ILIKE 'DWPF-%' AND regexp_replace(sku,'-Sample\$','','i') !~ '^DWPF-6[0-9]{5}\$';
+  IF bad > 0 THEN RAISE EXCEPTION 'ABORT: % DWPF mirror rows not in 600xxx block', bad; END IF;
+END \$\$;
+
+COMMIT;
+SQL
+
+echo "=== POST: variant_sku sanity ==="
+psql "$PGURL" -tA -c "SELECT 'variant_sku still old-block='||count(*) FROM shopify_products WHERE variant_sku ILIKE 'DWPF-%' AND regexp_replace(variant_sku,'-Sample\$','','i') !~ '^DWPF-6[0-9]{5}\$';"
+echo "=== DONE. Rollback if needed: UPDATE shopify_products sp SET sku=b.old_sku, variant_sku=b.old_variant_sku FROM ${BAK} b WHERE sp.id=b.id; ==="
← 1470f1c PF SKU renumber: worklist + Shopify renumber script (dry-run · back to Pf Sku Renumber 2026 · (newest)