diff options
Diffstat (limited to 'db/sql/tables.sql')
-rw-r--r-- | db/sql/tables.sql | 39 |
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. | ||
2 | CREATE 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 | ||
10 | CREATE 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. | ||
18 | CREATE 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. | ||
28 | CREATE TABLE IF NOT EXISTS Sizes ( | ||
29 | id INTEGER PRIMARY KEY, | ||
30 | size INT NOT NULL, | ||
31 | unit TEXT DEFAULT "oz" | ||
32 | ); | ||
33 | |||
34 | CREATE 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 | ||