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
42
43
44
45
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
);
|
