← back to Dw Vendor Microsites
lib/export-catalog.sh
225 lines
#!/usr/bin/env bash
# export-catalog.sh <catalog_table> <out_jsonl> [banned_name1] [banned_name2] ...
#
# Exports a *_catalog table from dw_unified (READ-ONLY) to a JSONL normalized to
# the canonical cowtan shape, then scrubs any banned upstream names. The per-table
# schemas vary (name/pattern_name, sku/mfr_sku, pattern_repeat/repeat_v) so we pull
# full row_to_json and normalize/scrub in python — keeping the viewer engine uniform.
#
# Scrub policy (private-label safety):
# - drop product_url / product_link entirely (they carry upstream domains)
# - remove banned name substrings (case-insensitive) from EVERY string value
# - if an image_url/all_images path contains a banned name token, blank it
#
# Implementation note: we dump the raw `row_to_json` stream to a temp file first,
# then hand that PATH to the python normalizer as an arg. This avoids the
# heredoc-vs-pipe stdin conflict (`python3 - <<'PY'` makes the heredoc BE stdin,
# which would swallow piped data). SQL is piped to a single-string ssh command so
# the remote shell receives the psql invocation intact. Over `ssh my-server` the
# OS user is root -> must `sudo -u postgres psql` (standing memo).
set -euo pipefail
TABLE="${1:?usage: export-catalog.sh <catalog_table> <out_jsonl> [banned...]}"
OUT="${2:?missing out_jsonl}"
shift 2
BANNED=("$@")
mkdir -p "$(dirname "$OUT")"
RAW="$(mktemp /tmp/dwvm-export.XXXXXX)"
trap 'rm -f "$RAW"' EXIT
# stream raw JSON (one object/line) into the temp file
printf '%s\n' "select row_to_json(t) from ${TABLE} t;" \
| ssh my-server "sudo -u postgres psql -d dw_unified -tA" > "$RAW" 2>/dev/null
# normalize + scrub (raw path passed as arg; stdin free for the heredoc script)
# HOUSE (the customer-facing house name) is passed via env so banned upstream
# tokens in TEXT fields are *replaced with the house name*, not just deleted.
HOUSE_NAME="${HOUSE:-}" python3 - "$RAW" "$OUT" "${BANNED[@]+"${BANNED[@]}"}" <<'PY'
import sys, os, json, re
raw_path = sys.argv[1]
out_path = sys.argv[2]
banned = [b for b in sys.argv[3:] if b.strip()]
house = (os.environ.get('HOUSE_NAME') or '').strip()
# --- pattern construction -----------------------------------------------------
# TEXT scrub = WORD-BOUNDARY aware so "twill" (fabric weave) is NOT mangled by a
# "TWIL" ban, and "Momentum" only matches as a standalone word. We sort longer
# banned names first so multi-word brands (e.g. "TWIL Karin") win over the short
# "TWIL" token and the whole phrase collapses to ONE house name.
banned_sorted = sorted([b for b in banned if b.strip()], key=len, reverse=True)
# \b on each side; \b around a token starting/ending with a non-word char would
# never match, but all our brand tokens are word-ish so \b is correct here.
text_pats = [re.compile(r'\b' + re.escape(b) + r'\b', re.IGNORECASE) for b in banned_sorted]
# token form (alnum only) for image/url/path + KEY-NAME detection — filenames
# like "momentumtextilesandwalls" have no word boundaries, so use substring match
banned_tokens = [re.sub(r'[^a-z0-9]', '', b.lower()) for b in banned_sorted if re.sub(r'[^a-z0-9]', '', b.lower())]
# ---- place-name FALSE-POSITIVE guard (mirrors dw-leak-scanner/leak-match.mjs) --
# The malibu/phillipe_romano/hollywood houses source from the real York/Brewster
# and legitimately ban "York". A bare "york" ban must NOT corrupt "New York" NYC
# place names ("Concrete New York" -> "Concrete New MalibuWallpaper"), the "York
# Weave" pattern, or the internal "york-scrape" provenance token — it must still
# de-brand a standalone "York" / "yorkwall". We PROTECT those phrases by masking
# them to a sentinel before the banned-word scrub runs, then RESTORE them after.
# Guard is armed ONLY when a bare "york" is in the banned set; no-op otherwise.
_york_guard = any(re.sub(r'[^a-z0-9]', '', b.lower()) == 'york' for b in banned_sorted)
_YORK_PROTECT = re.compile(r'new york|nueva york|york weave|york[-_]scrape', re.IGNORECASE)
_YORK_SENTINEL = '{}' # private-use chars, never appear in catalog text
_york_saved = []
def protect_york(s):
if not _york_guard or not isinstance(s, str) or not s:
return s
def _mask(m):
_york_saved.append(m.group(0))
return _YORK_SENTINEL.format(len(_york_saved) - 1)
return _YORK_PROTECT.sub(_mask, s)
def restore_york(s):
if not _york_guard or not isinstance(s, str) or '' not in s:
return s
return re.sub(r'(\d+)', lambda m: _york_saved[int(m.group(1))], s)
DROP_KEYS = {'product_url', 'product_link', 'source_url', 'vendor_url'}
# a value/key is treated as image/url/path-like if its KEY hints at it OR the
# value itself looks like a URL or file path
URLISH_KEY = re.compile(r'(image|img|url|src|href|link|photo|thumb|swatch|cdn|file|path|asset)', re.IGNORECASE)
URLISH_VAL = re.compile(r'^(https?:)?//|^/|\.(jpe?g|png|gif|webp|svg|tiff?|bmp|pdf|mp4|webm)(\?|$)', re.IGNORECASE)
def has_banned_token(s):
low = re.sub(r'[^a-z0-9]', '', s.lower())
return any(tok in low for tok in banned_tokens)
def scrub_text(s):
# replace each banned word with the house name (word-boundary aware)
for p in text_pats:
s = p.sub(house, s)
s = re.sub(r'\s{2,}', ' ', s).strip(' -|,/')
return s
def is_urlish(key, val):
if isinstance(key, str) and URLISH_KEY.search(key):
return True
return bool(URLISH_VAL.search(val))
def scrub_value(key, v):
"""Recursively scrub a value. URL/image-like strings carrying a banned token
are blanked; other text gets the banned word replaced with the house name."""
if isinstance(v, str):
if not banned_tokens or not v:
return v
# place-name FP guard: mask "New York"/"York Weave"/"york-scrape" so the
# bare-"york" ban can't corrupt or blank them; restore before returning.
v = protect_york(v)
if is_urlish(key, v):
return '' if has_banned_token(v) else restore_york(v)
# plain text -> word-boundary replace; but if a banned token is still
# embedded (e.g. a glued slug not caught by \b) and the string is a slug
# with no spaces, blank it as a leak vector rather than leaving it.
out = scrub_text(v)
if has_banned_token(out):
# token survived word-boundary replace (glued slug / handle) -> de-brand
if ' ' not in out:
# slug/handle: strip the token outright, clean separators
low = out
for tok, orig in zip(banned_tokens, banned_sorted):
low = re.sub(re.escape(orig), '', low, flags=re.IGNORECASE)
# also kill the glued alnum token
low = re.sub(re.escape(tok), '', low, flags=re.IGNORECASE)
out = re.sub(r'[-_]{2,}', '-', low).strip('-_ ')
else:
# still de-brand the glued occurrence inside a sentence. Replace
# BOTH the spaced form (orig, e.g. "Justin David") AND the glued
# alnum form (tok, e.g. "JustinDavid" as in "... | JustinDavid")
# with the house name. The glued form is replaced ONLY when the
# token is a WHOLE glued word — bounded by a non-letter or a
# string edge on each side — so a legit superword like "twilight"
# / "twill" (tok "twil" FOLLOWED by a letter) is NOT mangled.
for orig, tok in zip(banned_sorted, banned_tokens):
out = re.sub(re.escape(orig), house, out, flags=re.IGNORECASE)
gp = re.compile(r'(?<![A-Za-z])' + re.escape(tok) + r'(?![A-Za-z])', re.IGNORECASE)
out = gp.sub(house, out)
out = re.sub(r'\s{2,}', ' ', out).strip(' -|,/')
return restore_york(out)
if isinstance(v, list):
cleaned = [scrub_value(key, x) for x in v]
# for url-ish list keys, drop emptied entries
if isinstance(key, str) and URLISH_KEY.search(key):
cleaned = [x for x in cleaned if not (isinstance(x, str) and x == '')]
return cleaned
if isinstance(v, dict):
return scrub_dict(v)
return v
def clean_key(k):
"""Rename a key that embeds a banned token (e.g. 'momentum_sku' -> 'sku'),
collision-safe."""
if not isinstance(k, str) or not has_banned_token(k):
return k
nk = k
for orig in banned_sorted:
nk = re.sub(re.escape(orig), '', nk, flags=re.IGNORECASE)
for tok in banned_tokens:
nk = re.sub(re.escape(tok), '', nk, flags=re.IGNORECASE)
nk = re.sub(r'[-_]{2,}', '_', nk).strip('-_ ')
return nk or 'field'
def scrub_dict(d):
out = {}
for k, v in d.items():
if k in DROP_KEYS:
continue
nv = scrub_value(k, v)
nk = clean_key(k) if banned_tokens else k
# collision-safe key rename
if nk != k:
base = nk; i = 2
while nk in out:
nk = f"{base}_{i}"; i += 1
out[nk] = nv
return out
def normalize(r):
# alias map -> canonical cowtan keys (only fill if missing)
o = dict(r)
o['mfr_sku'] = r.get('mfr_sku') or r.get('sku') or r.get('pj_item_number')
o['pattern_name'] = r.get('pattern_name') or r.get('name')
o['repeat_v'] = r.get('repeat_v') or r.get('pattern_repeat')
o['price_retail'] = r.get('price_retail') or r.get('price') or r.get('us_map') or r.get('dw_sell_price')
o['line'] = r.get('line') or r.get('brand') or r.get('material')
return o
# all_images sometimes arrives as a JSON-encoded string -> decode so the
# recursive scrubber can clean each entry, then it stays a list.
def predecode_all_images(r):
ai = r.get('all_images')
if isinstance(ai, str) and ai.strip().startswith('['):
try:
r['all_images'] = json.loads(ai)
except Exception:
pass
return r
n = 0
with open(raw_path) as fin, open(out_path, 'w') as fout:
for line in fin:
line = line.strip()
if not line: continue
try:
r = json.loads(line)
except Exception:
continue
r = normalize(r)
r = predecode_all_images(r)
if banned_tokens:
r = scrub_dict(r)
else:
for k in list(r.keys()):
if k in DROP_KEYS:
r.pop(k, None)
fout.write(json.dumps(r) + '\n')
n += 1
print(n)
PY