diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/032_import_trello_shopping_items.sql | 46 | ||||
| -rw-r--r-- | migrations/README.md | 3 |
2 files changed, 48 insertions, 1 deletions
diff --git a/migrations/032_import_trello_shopping_items.sql b/migrations/032_import_trello_shopping_items.sql new file mode 100644 index 0000000..a12a675 --- /dev/null +++ b/migrations/032_import_trello_shopping_items.sql @@ -0,0 +1,46 @@ +-- One-time import of Trello Shopping-board cards into user shopping items, +-- ahead of removing the Trello source from the shopping list entirely. +-- +-- The Trello "Shopping" board had become a dumping ground: of its 24 cards, 13 +-- had no list at all (rendering as "Uncategorized") and the rest were spread +-- over Costco, Hardware Store, "Perennial " (note the trailing space, which +-- made it a separate store from "Perennial") and online. +-- +-- Only the two groups worth keeping are imported: Hardware Store, and the +-- unlisted cards. Costco, Perennial and online cards are deliberately left +-- behind — they stop appearing once the Trello source is removed. Nothing is +-- destroyed by that: the cards remain in Trello, this only stops doot +-- displaying them. +-- +-- Imported rows become ordinary user items, which means they also become fully +-- editable (rename, move store, delete) — Trello-sourced items never were. +-- +-- Reads from the local `cards` cache rather than the Trello API. On any +-- database where that cache is empty (a fresh dev DB, a restore predating a +-- sync) this is simply a no-op, which is the correct outcome there. + +INSERT INTO user_shopping_items (name, store) +SELECT + c.name, + CASE + WHEN TRIM(COALESCE(c.list_name, '')) = '' THEN 'Uncategorized' + ELSE TRIM(c.list_name) + END AS store_name +FROM cards c +JOIN boards b ON c.board_id = b.id +WHERE LOWER(b.name) = 'shopping' + AND ( + TRIM(COALESCE(c.list_name, '')) = '' + OR LOWER(TRIM(c.list_name)) = 'hardware store' + ) + -- Guard against creating a duplicate of something already added by hand. + -- Migrations are tracked by filename so this runs once, but the guard makes + -- the statement safe to replay against a partially-imported database. + AND NOT EXISTS ( + SELECT 1 FROM user_shopping_items u + WHERE u.name = c.name + AND u.store = CASE + WHEN TRIM(COALESCE(c.list_name, '')) = '' THEN 'Uncategorized' + ELSE TRIM(c.list_name) + END + ); diff --git a/migrations/README.md b/migrations/README.md index e849b9c..a98efa2 100644 --- a/migrations/README.md +++ b/migrations/README.md @@ -27,10 +27,11 @@ Migration 007 was reserved during development but never implemented. The gap is ## Running Migrations Migrations are applied automatically on application startup via `store.RunMigrations()`. The `schema_migrations` table tracks which migrations have been applied. +| 032 | `032_import_trello_shopping_items.sql` | One-time import of Trello Shopping-board cards (Hardware Store + unlisted) into user items, ahead of removing Trello as a shopping source | ## Creating New Migrations -1. Create file: `migrations/NNN_description.sql` (next number is 014) +1. Create file: `migrations/NNN_description.sql` (next number is 033) 2. Write idempotent SQL (use `IF NOT EXISTS` where possible) 3. Test locally: `go run cmd/dashboard/main.go` 4. Verify: Check `schema_migrations` table for new entry |