aboutsummaryrefslogtreecommitdiff
path: root/db/scripts/water_init.sql
diff options
context:
space:
mode:
Diffstat (limited to 'db/scripts/water_init.sql')
-rw-r--r--db/scripts/water_init.sql86
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.
2CREATE 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
10CREATE 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.
18CREATE 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.
28CREATE TABLE IF NOT EXISTS Sizes (
29 id INTEGER PRIMARY KEY,
30 size INT NOT NULL,
31 unit TEXT DEFAULT "oz"
32);
33
34CREATE 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.
42INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48);
43
44-- create default preferences.
45INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
46
47CREATE TRIGGER IF NOT EXISTS enforce_size_id
48BEFORE INSERT ON Preferences
49BEGIN
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;
57END;
58
59--
60CREATE 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
63CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
64SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
65FROM users
66LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
67LEFT JOIN preferences ON users.id = preferences.user_id
68GROUP BY users.name;
69
70
71CREATE 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 )
80SELECT DateSequence.Dates as 'date',
81 IFNULL(SUM(statistics.quantity), 0) AS 'total'
82FROM DateSequence
83LEFT JOIN statistics
84ON Date(statistics.date) = DateSequence.Dates
85GROUP BY DateSequence.Dates
86ORDER BY DateSequence.Dates; \ No newline at end of file