diff options
Diffstat (limited to 'migrations')
| -rw-r--r-- | migrations/034_shopping_stores_and_sections.sql | 41 | ||||
| -rw-r--r-- | migrations/README.md | 3 |
2 files changed, 43 insertions, 1 deletions
diff --git a/migrations/034_shopping_stores_and_sections.sql b/migrations/034_shopping_stores_and_sections.sql new file mode 100644 index 0000000..2b47846 --- /dev/null +++ b/migrations/034_shopping_stores_and_sections.sql @@ -0,0 +1,41 @@ +-- A real registry for shopping stores and sections. +-- +-- Until now a store existed only because some item referenced it by name. +-- Trello's list names were quietly acting as the registry; removing Trello as +-- a shopping source (migration 032) made the gap visible. Without this you +-- cannot move an item to a store that is currently empty, and moving the last +-- item out of a store makes that store vanish mid-edit. +-- +-- Sections are the same idea one level down. They previously existed only as +-- PlanToEat's category strings, so a user item could not be filed under one at +-- all. + +CREATE TABLE IF NOT EXISTS shopping_stores ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT NOT NULL UNIQUE, + sort_order INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + +CREATE TABLE IF NOT EXISTS shopping_sections ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + store_id INTEGER NOT NULL REFERENCES shopping_stores(id) ON DELETE CASCADE, + name TEXT NOT NULL, + sort_order INTEGER NOT NULL DEFAULT 0, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP, + UNIQUE (store_id, name) +); + +CREATE INDEX IF NOT EXISTS idx_shopping_sections_store ON shopping_sections(store_id); + +-- Items gain a section. Store stays a name rather than a foreign key: items +-- also arrive from PlanToEat carrying a store name that may not be registered +-- yet, and a NOT NULL reference would mean either dropping those items or +-- auto-creating stores from scraped data. +ALTER TABLE user_shopping_items ADD COLUMN section TEXT NOT NULL DEFAULT ''; + +-- Seed the registry from the stores that currently exist, so nothing +-- disappears the moment the registry becomes authoritative. +INSERT OR IGNORE INTO shopping_stores (name) +SELECT DISTINCT TRIM(store) FROM user_shopping_items +WHERE TRIM(COALESCE(store, '')) <> ''; diff --git a/migrations/README.md b/migrations/README.md index 1564b48..07320af 100644 --- a/migrations/README.md +++ b/migrations/README.md @@ -29,10 +29,11 @@ Migration 007 was reserved during development but never implemented. The gap is 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 | | 033 | `033_ics_feeds.sql` | Subscribed iCalendar (.ics) feeds, merged into calendar_events | +| 034 | `034_shopping_stores_and_sections.sql` | Store/section registry + `user_shopping_items.section` | ## Creating New Migrations -1. Create file: `migrations/NNN_description.sql` (next number is 034) +1. Create file: `migrations/NNN_description.sql` (next number is 035) 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 |