Files
2026-10-09 12:33:27 +02:00

38 lines
1.2 KiB
SQL

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);