aboutsummaryrefslogtreecommitdiff
path: root/db/scripts/water_init.sql
blob: 0751c41066ca8b36e84527c2a7cac982fdf8b80f (plain)
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
-- user table for users.
CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    UNIQUE(name)
);

-- statistics table for users to log their consumption
CREATE TABLE IF NOT EXISTS Statistics (
    id INTEGER PRIMARY KEY,
    date DATETIME NOT NULL,
    user_id INT NOT NULL,
    quantity INT
);

-- preferences table for a user.
CREATE TABLE IF NOT EXISTS Preferences (
    id INTEGER PRIMARY KEY,
    color TEXT NOT NULL DEFAULT "#000000",
    user_id INT NOT NULL,
    size_id INT NOT NULL DEFAULT 1,
    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
    FOREIGN KEY(size_id) REFERENCES Sizes(id)
);

-- lookup table for sizes.
CREATE TABLE IF NOT EXISTS Sizes (
    id INTEGER PRIMARY KEY,
    size INT NOT NULL,
    unit TEXT DEFAULT "oz"
);

-- create default sizes for sizes lookup table.
INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 

-- create default users.
INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach');

-- create default preferences.
INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2);