diff options
Diffstat (limited to 'db/scripts/water_init.sql')
-rw-r--r-- | db/scripts/water_init.sql | 40 |
1 files changed, 35 insertions, 5 deletions
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql index 0751c41..6a4de24 100644 --- a/db/scripts/water_init.sql +++ b/db/scripts/water_init.sql | |||
@@ -1,8 +1,9 @@ | |||
1 | -- user table for users. | 1 | -- user table for users. |
2 | CREATE TABLE IF NOT EXISTS Users ( | 2 | CREATE TABLE IF NOT EXISTS Users ( |
3 | id INTEGER PRIMARY KEY, | 3 | id INTEGER PRIMARY KEY, |
4 | name TEXT NOT NULL, | 4 | password TEXT UNIQUE NOT NULL, |
5 | UNIQUE(name) | 5 | uuid TEXT UNIQUE NOT NULL, |
6 | name TEXT UNIQUE NOT NULL | ||
6 | ); | 7 | ); |
7 | 8 | ||
8 | -- statistics table for users to log their consumption | 9 | -- statistics table for users to log their consumption |
@@ -19,7 +20,7 @@ CREATE TABLE IF NOT EXISTS Preferences ( | |||
19 | color TEXT NOT NULL DEFAULT "#000000", | 20 | color TEXT NOT NULL DEFAULT "#000000", |
20 | user_id INT NOT NULL, | 21 | user_id INT NOT NULL, |
21 | size_id INT NOT NULL DEFAULT 1, | 22 | size_id INT NOT NULL DEFAULT 1, |
22 | FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE | 23 | FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, |
23 | FOREIGN KEY(size_id) REFERENCES Sizes(id) | 24 | FOREIGN KEY(size_id) REFERENCES Sizes(id) |
24 | ); | 25 | ); |
25 | 26 | ||
@@ -30,13 +31,42 @@ CREATE TABLE IF NOT EXISTS Sizes ( | |||
30 | unit TEXT DEFAULT "oz" | 31 | unit TEXT DEFAULT "oz" |
31 | ); | 32 | ); |
32 | 33 | ||
34 | CREATE TABLE IF NOT EXISTS APIToken ( | ||
35 | id INTEGER PRIMARY KEY, | ||
36 | token TEXT NOT NULL, | ||
37 | user_id INTEGER NOT NULL, | ||
38 | FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE | ||
39 | ); | ||
40 | |||
33 | -- create default sizes for sizes lookup table. | 41 | -- create default sizes for sizes lookup table. |
34 | INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); | 42 | INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); |
35 | 43 | ||
36 | -- create default users. | 44 | -- create default users. |
37 | INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach'); | 45 | INSERT OR IGNORE INTO Users (name, password, uuid) VALUES ( |
46 | 'parker', | ||
47 | '$2y$10$2UlKrQJQV5cQOo/8VcFlq.ai3MWf7mA4//knEs2xVnHTeB.RnfN.m', | ||
48 | '1aa668f3-7527-4a67-9c24-fdf307542eeb' | ||
49 | ), ( | ||
50 | 'zach', | ||
51 | '$2y$10$35UJnLpBj8ulhqN/3G4qKe0GYBOa/YunXit11n7ET6zknZpNeKpRS', | ||
52 | 'be3fd6b7-cf55-4eb8-92d8-1b745b439f34' | ||
53 | ); | ||
38 | 54 | ||
39 | -- create default preferences. | 55 | -- create default preferences. |
40 | INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2); | 56 | INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2); |
41 | 57 | ||
58 | CREATE TRIGGER IF NOT EXISTS enforce_size_id | ||
59 | BEFORE INSERT ON Preferences | ||
60 | BEGIN | ||
61 | SELECT | ||
62 | CASE | ||
63 | WHEN ( | ||
64 | SELECT COUNT(*) FROM Sizes WHERE id = new.size_id | ||
65 | ) = 0 | ||
66 | THEN RAISE(ABORT, 'Size does not exist') | ||
67 | END; | ||
68 | END; | ||
42 | 69 | ||
70 | -- | ||
71 | CREATE VIEW IF NOT EXISTS aggregated_stats AS | ||
72 | SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; | ||