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.sql97
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.
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 users.
45INSERT 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.
56INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
57
58CREATE TRIGGER IF NOT EXISTS enforce_size_id
59BEFORE INSERT ON Preferences
60BEGIN
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;
68END;
69
70--
71CREATE 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
74CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
75SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
76FROM users
77LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
78LEFT JOIN preferences ON users.id = preferences.user_id
79GROUP BY users.name;
80
81
82CREATE 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 )
91SELECT DateSequence.Dates as 'date',
92 IFNULL(SUM(statistics.quantity), 0) AS 'total'
93FROM DateSequence
94LEFT JOIN statistics
95ON Date(statistics.date) = DateSequence.Dates
96GROUP BY DateSequence.Dates
97ORDER BY DateSequence.Dates; \ No newline at end of file