aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorDoog <157747121+doogongithub@users.noreply.github.com>2024-02-29 20:13:48 -0500
committerDoog <157747121+doogongithub@users.noreply.github.com>2024-02-29 20:13:48 -0500
commit9f9a33cbf55d38987a66b709284d2bb4ffea0fe9 (patch)
tree1e0539e708983ca05bb4e07d22b9ec10b95d2473 /db
parente37c73e33a4aaf7fb8d25b5af03627f20bcda19f (diff)
modify api, build additional FE components, add types
Diffstat (limited to 'db')
-rw-r--r--db/scripts/water_init.sql40
-rw-r--r--db/water.sqlite3bin24576 -> 36864 bytes
2 files changed, 35 insertions, 5 deletions
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
index 0751c41..6a4de24 100644
--- a/db/scripts/water_init.sql
+++ b/db/scripts/water_init.sql
@@ -1,8 +1,9 @@
1-- user table for users. 1-- user table for users.
2CREATE TABLE IF NOT EXISTS Users ( 2CREATE TABLE IF NOT EXISTS Users (
3 id INTEGER PRIMARY KEY, 3 id INTEGER PRIMARY KEY,
4 name TEXT NOT NULL, 4 password TEXT UNIQUE NOT NULL,
5 UNIQUE(name) 5 uuid TEXT UNIQUE NOT NULL,
6 name TEXT UNIQUE NOT NULL
6); 7);
7 8
8-- statistics table for users to log their consumption 9-- statistics table for users to log their consumption
@@ -19,7 +20,7 @@ CREATE TABLE IF NOT EXISTS Preferences (
19 color TEXT NOT NULL DEFAULT "#000000", 20 color TEXT NOT NULL DEFAULT "#000000",
20 user_id INT NOT NULL, 21 user_id INT NOT NULL,
21 size_id INT NOT NULL DEFAULT 1, 22 size_id INT NOT NULL DEFAULT 1,
22 FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE 23 FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
23 FOREIGN KEY(size_id) REFERENCES Sizes(id) 24 FOREIGN KEY(size_id) REFERENCES Sizes(id)
24); 25);
25 26
@@ -30,13 +31,42 @@ CREATE TABLE IF NOT EXISTS Sizes (
30 unit TEXT DEFAULT "oz" 31 unit TEXT DEFAULT "oz"
31); 32);
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
33-- create default sizes for sizes lookup table. 41-- create default sizes for sizes lookup table.
34INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 42INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48);
35 43
36-- create default users. 44-- create default users.
37INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach'); 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);
38 54
39-- create default preferences. 55-- create default preferences.
40INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2); 56INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
41 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;
42 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;
diff --git a/db/water.sqlite3 b/db/water.sqlite3
index c800708..716c5a4 100644
--- a/db/water.sqlite3
+++ b/db/water.sqlite3
Binary files differ