← back to Quadrille Room Credit Enrich
Add gated jsonb update SQL (BEGIN/ROLLBACK dry-run + COMMITTED): 525 objects enriched across 26 URLs, element-conserved, rich-count monotonic
14df51818fb6efe3dd253bfaef8a99b77b73f710 · 2026-06-26 09:49:39 -0700 · Steve
Files touched
A data/update_credit.sqlA data/update_credit_COMMIT.sqlA gen_update_sql.py
Diff
commit 14df51818fb6efe3dd253bfaef8a99b77b73f710
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jun 26 09:49:39 2026 -0700
Add gated jsonb update SQL (BEGIN/ROLLBACK dry-run + COMMITTED): 525 objects enriched across 26 URLs, element-conserved, rich-count monotonic
---
data/update_credit.sql | 103 ++++++++++++++++++++++++++++++++++++++++++
data/update_credit_COMMIT.sql | 103 ++++++++++++++++++++++++++++++++++++++++++
gen_update_sql.py | 91 +++++++++++++++++++++++++++++++++++++
3 files changed, 297 insertions(+)
diff --git a/data/update_credit.sql b/data/update_credit.sql
new file mode 100644
index 0000000..581d9f0
--- /dev/null
+++ b/data/update_credit.sql
@@ -0,0 +1,103 @@
+-- Quadrille room-setting thin-credit enrichment (DTD verdict A, 3/3)
+-- Additive: updates credit/richness/sub-fields inside EXISTING jsonb objects.
+-- Idempotent (re-running yields same result). BEGIN...ROLLBACK dry-run.
+\set ON_ERROR_STOP on
+BEGIN;
+
+CREATE TEMP TABLE _enrich(key text PRIMARY KEY, credit text, richness text, designer text, photographer text, publication text, date text) ON COMMIT DROP;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/abaco_stripe_shade_and_pillow_meg_braff_the_decorated_home_pc_1448.jpg', 'Meg Braff (As Seen in The Decorated Home)', 'rich', 'Meg Braff', NULL, 'The Decorated Home', NULL) ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/arbre_de_matisse_pillows_at_home_fairfield_county_fall_2007_pc_819.jpg', 'As Seen in At Home Fairfield County Fall 2007', 'medium', NULL, NULL, 'At Home Fairfield County', 'Fall 2007') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/arbre_de_matisse_reverse_sofa_new_york_spaces_august_2013_pc_844.jpg', 'As Seen in New York Spaces August 2013', 'medium', NULL, NULL, 'New York Spaces', 'August 2013') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Arbre-de-Matisse-Reverse-wallpaper-Martha-Stewart-Living-September-2011.jpg', 'As Seen in Martha Stewart Living September 2011', 'medium', NULL, NULL, 'Martha Stewart Living', 'September 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_diamond_pillows_meg_braff_the_decorated_home_pc_1256.jpg', 'Meg Braff (As Seen in The Decorated Home)', 'rich', 'Meg Braff', NULL, 'The Decorated Home', NULL) ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_ii_chairs_liza_pulitzer_calhoun_flower_magazine_august_2015_pc_1376.jpg', 'Liza Pulitzer Calhoun (As Seen in Flower Magazine August 2015)', 'rich', 'Liza Pulitzer Calhoun', NULL, 'Flower Magazine', 'August 2015') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_ii_wallpaper_bali_isle_headboard_chiqui_and_nena_woolworthelle_decor_june_2009_pc_1372.jpg', 'Chiqui Nena Woolworth (As Seen in Elle Decor June 2009)', 'rich', 'Chiqui Nena Woolworth', NULL, 'Elle Decor', 'June 2009') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Bali-II-wallpaper-Bali-Isle-headboard-Chiqui-and-Nena-WoolworthElle-Decor-June-2009.jpg', 'Chiqui Nena Woolworth (As Seen in Elle Decor June 2009)', 'rich', 'Chiqui Nena Woolworth', NULL, 'Elle Decor', 'June 2009') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/birds_ii_susanne_morgan_at_home_fairfield_county_fall_2006_pc_1545.jpg', 'Susanne Morgan (As Seen in At Home Fairfield County Fall 2006)', 'rich', 'Susanne Morgan', NULL, 'At Home Fairfield County', 'Fall 2006') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Brenta-Wallpaper-Martha-Stewart-Living-September-2011.jpg', 'As Seen in Martha Stewart Living September 2011', 'medium', NULL, NULL, 'Martha Stewart Living', 'September 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/climbing_hydrangea_wallpaper_new_batik_cushions_terrace_tablecloth_samantha_williams_pasadena_showhouse_2025_pc_923.jpg', 'Samantha Williams (As Seen in Pasadena Showhouse 2025)', 'rich', 'Samantha Williams', NULL, 'Pasadena Showhouse', '2025') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Climbing-Hydrangea-wallpaper-New-Batik-cushions-Terrace-tablecloth-Samantha-Williams-Pasadena-showhouse-2025.jpg', 'Samantha Williams (As Seen in Pasadena Showhouse 2025)', 'rich', 'Samantha Williams', NULL, 'Pasadena Showhouse', '2025') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/henriot_floral_chair_nitik_ii_bed_phoebe_howard_southern_accents_july_2008_pc_1196.jpg', 'Phoebe Howard (As Seen in Southern Accents July 2008)', 'rich', 'Phoebe Howard', NULL, 'Southern Accents', 'July 2008') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/indramayu_beds_elizabeth_dexter_serendipity_may_2016_pc_1532.jpg', 'Elizabeth Dexter (As Seen in Serendipity May 2016)', 'rich', 'Elizabeth Dexter', NULL, 'Serendipity', 'May 2016') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/island_ikat_chairs_emily_ruddo_high_gloss_magazine_july_2011_pc_940.jpg', 'Emily Ruddo (As Seen in High Gloss Magazine July 2011)', 'rich', 'Emily Ruddo', NULL, 'High Gloss Magazine', 'July 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/java_java_edo_ziggurat_anne_hepfer_canadian_house_and_home_april_2011_pc_1126.jpg', 'Anne Hepfer (As Seen in Canadian House and Home April 2011)', 'rich', 'Anne Hepfer', NULL, 'Canadian House and Home', 'April 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Java-Java-wallpaper-In-Style-April-2010.jpg', 'As Seen in In Style April 2010', 'medium', NULL, NULL, 'In Style', 'April 2010') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/lyford_background_chair_and_pillows_johnson_vann_interiors_atlanta_magazine_april_2019_pc_1291.jpg', 'Johnson Vann (As Seen in Atlanta Magazine April 2019)', 'rich', 'Johnson Vann', NULL, 'Atlanta Magazine', 'April 2019') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/lyford_print_curtains_and_bed_canopy_barry_dixon_metropolitan_home_june_2008_pc_1309.jpg', 'Barry Dixon (As Seen in Metropolitan Home June 2008)', 'rich', 'Barry Dixon', NULL, 'Metropolitan Home', 'June 2008') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/macambo_curtains_and_pillows_aga_bed_dana_small_classic_home_summer_2018_pc_1097.jpg', 'Dana Small (As Seen in Classic Home Summer 2018)', 'rich', 'Dana Small', NULL, 'Classic Home', 'Summer 2018') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Mojave-wallpaper-Christpher-Maya-New-York-Spaces.jpg', 'Christopher Maya (As Seen in New York Spaces)', 'rich', 'Christopher Maya', NULL, 'New York Spaces', NULL) ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/nitik_ii_java_java_melong_batik_pillows_victoria_sanchez_dc_design_house_2014_pc_931.jpg', 'Victoria Sanchez (As Seen in DC Design House 2014)', 'rich', 'Victoria Sanchez', NULL, 'DC Design House', '2014') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Potalla-wallpaper-Southern-Accents-January-2007.jpg', 'As Seen in Southern Accents January 2007', 'medium', NULL, NULL, 'Southern Accents', 'January 2007') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/San-Marco-Reverse-wallpaper-Marika-Meyer-DC-Design-House-2014.jpg', 'Marika Meyer (As Seen in DC Design House 2014)', 'rich', 'Marika Meyer', NULL, 'DC Design House', '2014') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Sigourney-wallpaper-Sigourney-Small-Scale-curtains-DC-Design-House-2017-Home-On-Cameron.jpg', 'As Seen in DC Design House 2017', 'medium', NULL, NULL, 'DC Design House', '2017') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/ziggurat_chair_new_york_spaces_november_2008_pc_1215.jpg', 'Celerie Kemble (As Seen in New York Spaces November 2008)', 'rich', 'Celerie Kemble', NULL, 'New York Spaces', 'November 2008') ON CONFLICT (key) DO NOTHING;
+
+-- BEFORE: how many jsonb objects match an enrich key, by table
+SELECT 'china_seas_catalog matching-objects (before)' AS metric, count(*) 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','');
+SELECT 'quadrille_house_catalog matching-objects (before)' AS metric, count(*) FROM quadrille_house_catalog t,
+ jsonb_array_elements(t.room_setting_images_credited) obj
+ JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','');
+
+
+UPDATE china_seas_catalog 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','')
+);
+
+UPDATE quadrille_house_catalog 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','')
+);
+
+-- AFTER: objects whose credit now equals the enriched credit
+SELECT 'china_seas_catalog objects-updated (after)' AS metric, count(*) 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 AND obj->>'richness' = e.richness;
+SELECT 'quadrille_house_catalog objects-updated (after)' AS metric, count(*) FROM quadrille_house_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 AND obj->>'richness' = e.richness;
+
+-- Sample of updated objects for eyeball verification
+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;
+
+ROLLBACK; -- DRY RUN. Change to COMMIT; once counts verified.
\ No newline at end of file
diff --git a/data/update_credit_COMMIT.sql b/data/update_credit_COMMIT.sql
new file mode 100644
index 0000000..7c78411
--- /dev/null
+++ b/data/update_credit_COMMIT.sql
@@ -0,0 +1,103 @@
+-- Quadrille room-setting thin-credit enrichment (DTD verdict A, 3/3)
+-- Additive: updates credit/richness/sub-fields inside EXISTING jsonb objects.
+-- Idempotent (re-running yields same result). BEGIN...ROLLBACK dry-run.
+\set ON_ERROR_STOP on
+BEGIN;
+
+CREATE TEMP TABLE _enrich(key text PRIMARY KEY, credit text, richness text, designer text, photographer text, publication text, date text) ON COMMIT DROP;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/abaco_stripe_shade_and_pillow_meg_braff_the_decorated_home_pc_1448.jpg', 'Meg Braff (As Seen in The Decorated Home)', 'rich', 'Meg Braff', NULL, 'The Decorated Home', NULL) ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/arbre_de_matisse_pillows_at_home_fairfield_county_fall_2007_pc_819.jpg', 'As Seen in At Home Fairfield County Fall 2007', 'medium', NULL, NULL, 'At Home Fairfield County', 'Fall 2007') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/arbre_de_matisse_reverse_sofa_new_york_spaces_august_2013_pc_844.jpg', 'As Seen in New York Spaces August 2013', 'medium', NULL, NULL, 'New York Spaces', 'August 2013') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Arbre-de-Matisse-Reverse-wallpaper-Martha-Stewart-Living-September-2011.jpg', 'As Seen in Martha Stewart Living September 2011', 'medium', NULL, NULL, 'Martha Stewart Living', 'September 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_diamond_pillows_meg_braff_the_decorated_home_pc_1256.jpg', 'Meg Braff (As Seen in The Decorated Home)', 'rich', 'Meg Braff', NULL, 'The Decorated Home', NULL) ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_ii_chairs_liza_pulitzer_calhoun_flower_magazine_august_2015_pc_1376.jpg', 'Liza Pulitzer Calhoun (As Seen in Flower Magazine August 2015)', 'rich', 'Liza Pulitzer Calhoun', NULL, 'Flower Magazine', 'August 2015') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/bali_ii_wallpaper_bali_isle_headboard_chiqui_and_nena_woolworthelle_decor_june_2009_pc_1372.jpg', 'Chiqui Nena Woolworth (As Seen in Elle Decor June 2009)', 'rich', 'Chiqui Nena Woolworth', NULL, 'Elle Decor', 'June 2009') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Bali-II-wallpaper-Bali-Isle-headboard-Chiqui-and-Nena-WoolworthElle-Decor-June-2009.jpg', 'Chiqui Nena Woolworth (As Seen in Elle Decor June 2009)', 'rich', 'Chiqui Nena Woolworth', NULL, 'Elle Decor', 'June 2009') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/birds_ii_susanne_morgan_at_home_fairfield_county_fall_2006_pc_1545.jpg', 'Susanne Morgan (As Seen in At Home Fairfield County Fall 2006)', 'rich', 'Susanne Morgan', NULL, 'At Home Fairfield County', 'Fall 2006') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Brenta-Wallpaper-Martha-Stewart-Living-September-2011.jpg', 'As Seen in Martha Stewart Living September 2011', 'medium', NULL, NULL, 'Martha Stewart Living', 'September 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/climbing_hydrangea_wallpaper_new_batik_cushions_terrace_tablecloth_samantha_williams_pasadena_showhouse_2025_pc_923.jpg', 'Samantha Williams (As Seen in Pasadena Showhouse 2025)', 'rich', 'Samantha Williams', NULL, 'Pasadena Showhouse', '2025') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Climbing-Hydrangea-wallpaper-New-Batik-cushions-Terrace-tablecloth-Samantha-Williams-Pasadena-showhouse-2025.jpg', 'Samantha Williams (As Seen in Pasadena Showhouse 2025)', 'rich', 'Samantha Williams', NULL, 'Pasadena Showhouse', '2025') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/henriot_floral_chair_nitik_ii_bed_phoebe_howard_southern_accents_july_2008_pc_1196.jpg', 'Phoebe Howard (As Seen in Southern Accents July 2008)', 'rich', 'Phoebe Howard', NULL, 'Southern Accents', 'July 2008') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/indramayu_beds_elizabeth_dexter_serendipity_may_2016_pc_1532.jpg', 'Elizabeth Dexter (As Seen in Serendipity May 2016)', 'rich', 'Elizabeth Dexter', NULL, 'Serendipity', 'May 2016') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/island_ikat_chairs_emily_ruddo_high_gloss_magazine_july_2011_pc_940.jpg', 'Emily Ruddo (As Seen in High Gloss Magazine July 2011)', 'rich', 'Emily Ruddo', NULL, 'High Gloss Magazine', 'July 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/java_java_edo_ziggurat_anne_hepfer_canadian_house_and_home_april_2011_pc_1126.jpg', 'Anne Hepfer (As Seen in Canadian House and Home April 2011)', 'rich', 'Anne Hepfer', NULL, 'Canadian House and Home', 'April 2011') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Java-Java-wallpaper-In-Style-April-2010.jpg', 'As Seen in In Style April 2010', 'medium', NULL, NULL, 'In Style', 'April 2010') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/lyford_background_chair_and_pillows_johnson_vann_interiors_atlanta_magazine_april_2019_pc_1291.jpg', 'Johnson Vann (As Seen in Atlanta Magazine April 2019)', 'rich', 'Johnson Vann', NULL, 'Atlanta Magazine', 'April 2019') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/lyford_print_curtains_and_bed_canopy_barry_dixon_metropolitan_home_june_2008_pc_1309.jpg', 'Barry Dixon (As Seen in Metropolitan Home June 2008)', 'rich', 'Barry Dixon', NULL, 'Metropolitan Home', 'June 2008') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/macambo_curtains_and_pillows_aga_bed_dana_small_classic_home_summer_2018_pc_1097.jpg', 'Dana Small (As Seen in Classic Home Summer 2018)', 'rich', 'Dana Small', NULL, 'Classic Home', 'Summer 2018') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Mojave-wallpaper-Christpher-Maya-New-York-Spaces.jpg', 'Christopher Maya (As Seen in New York Spaces)', 'rich', 'Christopher Maya', NULL, 'New York Spaces', NULL) ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/nitik_ii_java_java_melong_batik_pillows_victoria_sanchez_dc_design_house_2014_pc_931.jpg', 'Victoria Sanchez (As Seen in DC Design House 2014)', 'rich', 'Victoria Sanchez', NULL, 'DC Design House', '2014') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Potalla-wallpaper-Southern-Accents-January-2007.jpg', 'As Seen in Southern Accents January 2007', 'medium', NULL, NULL, 'Southern Accents', 'January 2007') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/San-Marco-Reverse-wallpaper-Marika-Meyer-DC-Design-House-2014.jpg', 'Marika Meyer (As Seen in DC Design House 2014)', 'rich', 'Marika Meyer', NULL, 'DC Design House', '2014') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/Sigourney-wallpaper-Sigourney-Small-Scale-curtains-DC-Design-House-2017-Home-On-Cameron.jpg', 'As Seen in DC Design House 2017', 'medium', NULL, NULL, 'DC Design House', '2017') ON CONFLICT (key) DO NOTHING;
+INSERT INTO _enrich VALUES ('https://quadrillefabrics.s3.us-east-2.amazonaws.com/Editorial-Images/ziggurat_chair_new_york_spaces_november_2008_pc_1215.jpg', 'Celerie Kemble (As Seen in New York Spaces November 2008)', 'rich', 'Celerie Kemble', NULL, 'New York Spaces', 'November 2008') ON CONFLICT (key) DO NOTHING;
+
+-- BEFORE: how many jsonb objects match an enrich key, by table
+SELECT 'china_seas_catalog matching-objects (before)' AS metric, count(*) 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','');
+SELECT 'quadrille_house_catalog matching-objects (before)' AS metric, count(*) FROM quadrille_house_catalog t,
+ jsonb_array_elements(t.room_setting_images_credited) obj
+ JOIN _enrich e ON e.key = replace(obj->>'url','-sm-thumb','');
+
+
+UPDATE china_seas_catalog 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','')
+);
+
+UPDATE quadrille_house_catalog 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','')
+);
+
+-- AFTER: objects whose credit now equals the enriched credit
+SELECT 'china_seas_catalog objects-updated (after)' AS metric, count(*) 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 AND obj->>'richness' = e.richness;
+SELECT 'quadrille_house_catalog objects-updated (after)' AS metric, count(*) FROM quadrille_house_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 AND obj->>'richness' = e.richness;
+
+-- Sample of updated objects for eyeball verification
+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;
+
+COMMIT;
\ No newline at end of file
diff --git a/gen_update_sql.py b/gen_update_sql.py
new file mode 100644
index 0000000..e32932e
--- /dev/null
+++ b/gen_update_sql.py
@@ -0,0 +1,91 @@
+#!/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/stevestudio2/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/stevestudio2/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)")
← b95c814 Quadrille room-setting thin-credit enrichment: scrape + file
·
back to Quadrille Room Credit Enrich
·
chore: macstudio3 migration — reconcile from mac2 + repoint d892fdb →