CREATE TABLE lists ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, color TEXT NOT NULL DEFAULT 'lime', created_at TEXT NOT NULL DEFAULT (date('now')) ); CREATE TABLE items ( id INTEGER PRIMARY KEY AUTOINCREMENT, list_id INTEGER NOT NULL REFERENCES lists(id) ON DELETE CASCADE, name TEXT NOT NULL, amount REAL, unit TEXT, icon TEXT NOT NULL, category TEXT NOT NULL, checked INTEGER NOT NULL DEFAULT 0 ); CREATE INDEX items_list_id ON items(list_id); -- Products already bought at least once; feeds the suggestion tiles. CREATE TABLE catalog ( name TEXT PRIMARY KEY COLLATE NOCASE, icon TEXT NOT NULL, category TEXT NOT NULL, use_count INTEGER NOT NULL DEFAULT 0 ); INSERT INTO lists (name, color) VALUES ('Courses de la semaine', 'lime'); INSERT INTO catalog (name, icon, category, use_count) VALUES ('Beurre', 'cheese', 'frais', 3), ('Œufs', 'egg', 'frais', 3), ('Citrons', 'lemon', 'fruits_legumes', 2), ('Pain complet', 'bread', 'boulangerie', 2), ('Bananes', 'banana', 'fruits_legumes', 2), ('Café en grains', 'coffee', 'epicerie', 1);