aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorDoog <157747121+doogongithub@users.noreply.github.com>2024-02-21 22:07:27 -0500
committerDoog <157747121+doogongithub@users.noreply.github.com>2024-02-21 22:07:27 -0500
commit3eafb413a48cde60dea8a7355ee621c6acca952f (patch)
tree044bb4ef57033a33024a3a3ffe6ff64f9a00dc9c /db
parentfac21fa0a72d4a7f1a01ccd44e3acf9c90fd95bd (diff)
first commit
Diffstat (limited to 'db')
-rw-r--r--db/scripts/water_init.sql42
-rw-r--r--db/water.sqlite3bin0 -> 40960 bytes
2 files changed, 42 insertions, 0 deletions
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
new file mode 100644
index 0000000..d7b912a
--- /dev/null
+++ b/db/scripts/water_init.sql
@@ -0,0 +1,42 @@
1-- user table for users.
2CREATE TABLE IF NOT EXISTS Users (
3 id INT PRIMARY KEY,
4 name TEXT NOT NULL,
5 UNIQUE(name)
6);
7
8-- statistics table for users to log their consumption
9CREATE TABLE IF NOT EXISTS Statistics (
10 id INT PRIMARY KEY,
11 date DATETIME NOT NULL,
12 user_id INT NOT NULL,
13 quantity INT
14);
15
16-- preferences table for a user.
17CREATE TABLE IF NOT EXISTS Preferences (
18 id INT PRIMARY KEY,
19 color TEXT NOT NULL DEFAULT "#000000",
20 user_id INT NOT NULL,
21 size_id INT NOT NULL DEFAULT 1,
22 FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
23 FOREIGN KEY(size_id) REFERENCES Sizes(id)
24);
25
26-- lookup table for sizes.
27CREATE TABLE IF NOT EXISTS Sizes (
28 id INT PRIMARY KEY,
29 size INT NOT NULL
30 unit TEXT DEFAULT "oz"
31);
32
33-- 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);
35
36-- create default users.
37INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach');
38
39-- create default preferences.
40INSERT OR IGNORE INTO Preferences (id, user_id) VALUES (1, 1), (2, 2);
41
42
diff --git a/db/water.sqlite3 b/db/water.sqlite3
new file mode 100644
index 0000000..97f9214
--- /dev/null
+++ b/db/water.sqlite3
Binary files differ