diff options
author | Doog <157747121+doogongithub@users.noreply.github.com> | 2024-02-21 22:07:27 -0500 |
---|---|---|
committer | Doog <157747121+doogongithub@users.noreply.github.com> | 2024-02-21 22:07:27 -0500 |
commit | 3eafb413a48cde60dea8a7355ee621c6acca952f (patch) | |
tree | 044bb4ef57033a33024a3a3ffe6ff64f9a00dc9c /db | |
parent | fac21fa0a72d4a7f1a01ccd44e3acf9c90fd95bd (diff) |
first commit
Diffstat (limited to 'db')
-rw-r--r-- | db/scripts/water_init.sql | 42 | ||||
-rw-r--r-- | db/water.sqlite3 | bin | 0 -> 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. | ||
2 | CREATE 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 | ||
9 | CREATE 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. | ||
17 | CREATE 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. | ||
27 | CREATE 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. | ||
34 | INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); | ||
35 | |||
36 | -- create default users. | ||
37 | INSERT OR IGNORE INTO Users (id, name) VALUES (1, 'Parker'), (2, 'Zach'); | ||
38 | |||
39 | -- create default preferences. | ||
40 | INSERT 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 | |||