diff options
Diffstat (limited to 'db/scripts/water_init.sql')
-rw-r--r-- | db/scripts/water_init.sql | 86 |
1 files changed, 0 insertions, 86 deletions
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql deleted file mode 100644 index 1099d09..0000000 --- a/db/scripts/water_init.sql +++ /dev/null | |||
@@ -1,86 +0,0 @@ | |||
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 preferences. | ||
45 | INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2); | ||
46 | |||
47 | CREATE TRIGGER IF NOT EXISTS enforce_size_id | ||
48 | BEFORE INSERT ON Preferences | ||
49 | BEGIN | ||
50 | SELECT | ||
51 | CASE | ||
52 | WHEN ( | ||
53 | SELECT COUNT(*) FROM Sizes WHERE id = new.size_id | ||
54 | ) = 0 | ||
55 | THEN RAISE(ABORT, 'Size does not exist') | ||
56 | END; | ||
57 | END; | ||
58 | |||
59 | -- | ||
60 | CREATE VIEW IF NOT EXISTS aggregated_stats AS | ||
61 | 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; | ||
62 | |||
63 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS | ||
64 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color | ||
65 | FROM users | ||
66 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') | ||
67 | LEFT JOIN preferences ON users.id = preferences.user_id | ||
68 | GROUP BY users.name; | ||
69 | |||
70 | |||
71 | CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS | ||
72 | WITH DateSequence(Dates) AS | ||
73 | ( | ||
74 | SELECT Date(CURRENT_DATE, '-7 day') | ||
75 | UNION ALL | ||
76 | SELECT Date(Dates, '+1 day') | ||
77 | FROM DateSequence | ||
78 | WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) | ||
79 | ) | ||
80 | SELECT DateSequence.Dates as 'date', | ||
81 | IFNULL(SUM(statistics.quantity), 0) AS 'total' | ||
82 | FROM DateSequence | ||
83 | LEFT JOIN statistics | ||
84 | ON Date(statistics.date) = DateSequence.Dates | ||
85 | GROUP BY DateSequence.Dates | ||
86 | ORDER BY DateSequence.Dates; \ No newline at end of file | ||