diff options
Diffstat (limited to 'db/scripts/water_init.sql')
-rw-r--r-- | db/scripts/water_init.sql | 97 |
1 files changed, 97 insertions, 0 deletions
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql new file mode 100644 index 0000000..3b79ed5 --- /dev/null +++ b/db/scripts/water_init.sql | |||
@@ -0,0 +1,97 @@ | |||
1 | -- user table for users. | ||
2 | CREATE TABLE IF NOT EXISTS Users ( | ||
3 | id INTEGER PRIMARY KEY, | ||
4 | password TEXT UNIQUE NOT NULL, | ||
5 | uuid TEXT UNIQUE NOT NULL, | ||
6 | name TEXT UNIQUE NOT NULL | ||
7 | ); | ||
8 | |||
9 | -- statistics table for users to log their consumption | ||
10 | CREATE TABLE IF NOT EXISTS Statistics ( | ||
11 | id INTEGER PRIMARY KEY, | ||
12 | date DATETIME NOT NULL, | ||
13 | user_id INT NOT NULL, | ||
14 | quantity INT | ||
15 | ); | ||
16 | |||
17 | -- preferences table for a user. | ||
18 | CREATE TABLE IF NOT EXISTS Preferences ( | ||
19 | id INTEGER PRIMARY KEY, | ||
20 | color TEXT NOT NULL DEFAULT "#000000", | ||
21 | user_id INT UNIQUE NOT NULL, | ||
22 | size_id INT NOT NULL DEFAULT 1, | ||
23 | FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE, | ||
24 | FOREIGN KEY(size_id) REFERENCES Sizes(id) | ||
25 | ); | ||
26 | |||
27 | -- lookup table for sizes. | ||
28 | CREATE TABLE IF NOT EXISTS Sizes ( | ||
29 | id INTEGER PRIMARY KEY, | ||
30 | size INT NOT NULL, | ||
31 | unit TEXT DEFAULT "oz" | ||
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 | |||
41 | -- create default sizes for sizes lookup table. | ||
42 | INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); | ||
43 | |||
44 | -- create default users. | ||
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 | ); | ||
54 | |||
55 | -- create default preferences. | ||
56 | INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2); | ||
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; | ||
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; | ||
73 | |||
74 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS | ||
75 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color | ||
76 | FROM users | ||
77 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') | ||
78 | LEFT JOIN preferences ON users.id = preferences.user_id | ||
79 | GROUP BY users.name; | ||
80 | |||
81 | |||
82 | CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS | ||
83 | WITH DateSequence(Dates) AS | ||
84 | ( | ||
85 | SELECT Date(CURRENT_DATE, '-7 day') | ||
86 | UNION ALL | ||
87 | SELECT Date(Dates, '+1 day') | ||
88 | FROM DateSequence | ||
89 | WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) | ||
90 | ) | ||
91 | SELECT DateSequence.Dates as 'date', | ||
92 | IFNULL(SUM(statistics.quantity), 0) AS 'total' | ||
93 | FROM DateSequence | ||
94 | LEFT JOIN statistics | ||
95 | ON Date(statistics.date) = DateSequence.Dates | ||
96 | GROUP BY DateSequence.Dates | ||
97 | ORDER BY DateSequence.Dates; \ No newline at end of file | ||