aboutsummaryrefslogtreecommitdiff
path: root/db/sql/tables.sql
diff options
context:
space:
mode:
Diffstat (limited to 'db/sql/tables.sql')
-rw-r--r--db/sql/tables.sql39
1 files changed, 39 insertions, 0 deletions
diff --git a/db/sql/tables.sql b/db/sql/tables.sql
new file mode 100644
index 0000000..0fbfef8
--- /dev/null
+++ b/db/sql/tables.sql
@@ -0,0 +1,39 @@
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); \ No newline at end of file