SQL snippets

tree depth in sqlite3.sql

-- initial
UPDATE item SET depth = 0 WHERE parent_item_guid IS NULL;

-- SQLite3 doesn't support loops, so run this until `select count(*) from item where product_code is null` = 0
INSERT OR REPLACE INTO item(item_guid, user_guid, parent_item_guid, name, depth)
SELECT t.item_guid, t.user_guid, t.parent_item_guid, t.name, p.depth + 1 FROM item AS t
INNER JOIN item AS p ON (t.parent_item_guid = p.item_guid)
WHERE p.depth >= 0
AND NOT p.item_guid IS NULL
AND t.depth IS NULL;