1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
-- user table for users.
CREATE TABLE IF NOT EXISTS Users (
id INTEGER PRIMARY KEY,
password TEXT UNIQUE NOT NULL,
uuid TEXT UNIQUE NOT NULL,
name TEXT UNIQUE NOT NULL
);
-- statistics table for users to log their consumption
CREATE TABLE IF NOT EXISTS Statistics (
id INTEGER PRIMARY KEY,
date DATETIME NOT NULL,
user_id INT NOT NULL,
quantity INT
);
-- preferences table for a user.
CREATE TABLE IF NOT EXISTS Preferences (
id INTEGER PRIMARY KEY,
color TEXT NOT NULL DEFAULT "#000000",
user_id INT UNIQUE NOT NULL,
size_id INT NOT NULL DEFAULT 1,
FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
FOREIGN KEY(size_id) REFERENCES Sizes(id)
);
-- lookup table for sizes.
CREATE TABLE IF NOT EXISTS Sizes (
id INTEGER PRIMARY KEY,
size INT NOT NULL,
unit TEXT DEFAULT "oz"
);
CREATE TABLE IF NOT EXISTS APIToken (
id INTEGER PRIMARY KEY,
token TEXT NOT NULL,
user_id INTEGER NOT NULL,
FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
);
-- create default sizes for sizes lookup table.
INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48);
-- create default users.
INSERT OR IGNORE INTO Users (name, password, uuid) VALUES (
'parker',
'$2y$10$2UlKrQJQV5cQOo/8VcFlq.ai3MWf7mA4//knEs2xVnHTeB.RnfN.m',
'1aa668f3-7527-4a67-9c24-fdf307542eeb'
), (
'zach',
'$2y$10$35UJnLpBj8ulhqN/3G4qKe0GYBOa/YunXit11n7ET6zknZpNeKpRS',
'be3fd6b7-cf55-4eb8-92d8-1b745b439f34'
);
-- create default preferences.
INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
CREATE TRIGGER IF NOT EXISTS enforce_size_id
BEFORE INSERT ON Preferences
BEGIN
SELECT
CASE
WHEN (
SELECT COUNT(*) FROM Sizes WHERE id = new.size_id
) = 0
THEN RAISE(ABORT, 'Size does not exist')
END;
END;
--
CREATE VIEW IF NOT EXISTS aggregated_stats AS
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;
CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
FROM users
LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
LEFT JOIN preferences ON users.id = preferences.user_id
GROUP BY users.name;
CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
WITH DateSequence(Dates) AS
(
SELECT Date(CURRENT_DATE, '-7 day')
UNION ALL
SELECT Date(Dates, '+1 day')
FROM DateSequence
WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
)
SELECT DateSequence.Dates as 'date',
IFNULL(SUM(statistics.quantity), 0) AS 'total'
FROM DateSequence
LEFT JOIN statistics
ON Date(statistics.date) = DateSequence.Dates
GROUP BY DateSequence.Dates
ORDER BY DateSequence.Dates;
|
