`gen_update_sql.py`

92 lines
#!/usr/bin/env python3
"""Generate the gated, idempotent, additive jsonb UPDATE for the 26 enriched
Quadrille room images. Matches each jsonb object by thumb-stripped URL and
updates ONLY credit/richness/designer/photographer/publication/date inside the
existing object. Wrapped BEGIN ... (counts) ... ROLLBACK by default."""
import csv, json

ROWS = [r for r in csv.DictReader(open('/Users/macstudio3/QuadrilleRoomSettings-credit-enriched.csv'))
        if r['source'] == 'filename-reparse']


def sqlstr(v):
    if v is None or v == '':
        return 'NULL'
    return "'" + v.replace("'", "''") + "'"


def base(u):
    return u.replace('-sm-thumb', '')


lines = []
lines.append("-- Quadrille room-setting thin-credit enrichment (DTD verdict A, 3/3)")
lines.append("-- Additive: updates credit/richness/sub-fields inside EXISTING jsonb objects.")
lines.append("-- Idempotent (re-running yields same result). BEGIN...ROLLBACK dry-run.")
lines.append("\\set ON_ERROR_STOP on")
lines.append("BEGIN;")
lines.append("")
lines.append("CREATE TEMP TABLE _enrich(key text PRIMARY KEY, credit text, richness text,"
             " designer text, photographer text, publication text, date text) ON COMMIT DROP;")
for r in ROWS:
    lines.append(
        "INSERT INTO _enrich VALUES ("
        f"{sqlstr(base(r['url']))}, {sqlstr(r['new_credit'])}, {sqlstr(r['new_richness'])}, "
        f"{sqlstr(r['new_designer'])}, {sqlstr(r['new_photographer'])}, "
        f"{sqlstr(r['new_publication'])}, {sqlstr(r['new_date'])}) ON CONFLICT (key) DO NOTHING;")
lines.append("")

# Pre-update counts
lines.append("-- BEFORE: how many jsonb objects match an enrich key, by table")
for tbl in ('china_seas_catalog', 'quadrille_house_catalog'):
    lines.append(f"""SELECT '{tbl} matching-objects (before)' AS metric, count(*) FROM {tbl} t,
  jsonb_array_elements(t.room_setting_images_credited) obj
  JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','');""")
lines.append("")

# The additive update: rebuild each row's array, replacing matched objects.
update_tmpl = """
UPDATE {tbl} t SET room_setting_images_credited = (
  SELECT jsonb_agg(
    CASE WHEN e.key IS NOT NULL THEN
      obj
        || jsonb_build_object('credit', e.credit)
        || jsonb_build_object('richness', e.richness)
        || jsonb_build_object('designer', to_jsonb(e.designer))
        || jsonb_build_object('photographer', to_jsonb(e.photographer))
        || jsonb_build_object('publication', to_jsonb(e.publication))
        || jsonb_build_object('date', to_jsonb(e.date))
    ELSE obj END
    ORDER BY ord
  )
  FROM jsonb_array_elements(t.room_setting_images_credited) WITH ORDINALITY AS a(obj, ord)
  LEFT JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
)
WHERE EXISTS (
  SELECT 1 FROM jsonb_array_elements(t.room_setting_images_credited) o2
  JOIN _enrich e2 ON e2.key = replace(o2->>'url','-sm-thumb','')
);"""
for tbl in ('china_seas_catalog', 'quadrille_house_catalog'):
    lines.append(update_tmpl.format(tbl=tbl))
lines.append("")

# After counts: objects now carrying the new credit
lines.append("-- AFTER: objects whose credit now equals the enriched credit")
for tbl in ('china_seas_catalog', 'quadrille_house_catalog'):
    lines.append(f"""SELECT '{tbl} objects-updated (after)' AS metric, count(*) FROM {tbl} t,
  jsonb_array_elements(t.room_setting_images_credited) obj
  JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
  WHERE obj->>'credit' = e.credit AND obj->>'richness' = e.richness;""")
lines.append("")
lines.append("-- Sample of updated objects for eyeball verification")
lines.append("""SELECT obj->>'url' url, obj->>'credit' credit, obj->>'richness' richness,
  obj->>'publication' publication, obj->>'date' date
FROM china_seas_catalog t, jsonb_array_elements(t.room_setting_images_credited) obj
JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','')
WHERE obj->>'credit' = e.credit LIMIT 8;""")
lines.append("")
lines.append("ROLLBACK;  -- DRY RUN. Change to COMMIT; once counts verified.")

open('/Users/macstudio3/Projects/quadrille-room-credit-enrich/data/update_credit.sql', 'w').write('\n'.join(lines))
print(f"wrote data/update_credit.sql ({len(ROWS)} enrich rows)")