1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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, '')) <> '';
|
