1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
-- user table for users.
CREATE TABLE IF NOT EXISTS Users (
id INTEGER PRIMARY KEY,
password TEXT UNIQUE NOT NULL,
uuid TEXT UNIQUE NOT NULL,
name TEXT UNIQUE NOT NULL
);
-- statistics table for users to log their consumption
CREATE TABLE IF NOT EXISTS Statistics (
id INTEGER PRIMARY KEY,
date DATETIME NOT NULL,
user_id INT NOT NULL,
quantity INT
);
-- preferences table for a user.
CREATE TABLE IF NOT EXISTS Preferences (
id INTEGER PRIMARY KEY,
color TEXT NOT NULL DEFAULT "#000000",
user_id INT UNIQUE NOT NULL,
size_id INT NOT NULL DEFAULT 1,
FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
FOREIGN KEY(size_id) REFERENCES Sizes(id)
);
-- lookup table for sizes.
CREATE TABLE IF NOT EXISTS Sizes (
id INTEGER PRIMARY KEY,
size INT NOT NULL,
unit TEXT DEFAULT "oz"
);
CREATE TABLE IF NOT EXISTS APIToken (
id INTEGER PRIMARY KEY,
token TEXT NOT NULL,
user_id INTEGER NOT NULL,
FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
);
|
