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
|
BEGIN;
CREATE TABLE user_score (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password TEXT NOT NULL,
burn_time TIMESTAMP NOT NULL,
score SMALLINT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE action (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
magnitude SMALLINT NOT NULL DEFAULT 1,
repeatable BOOLEAN NOT NULL DEFAULT FALSE,
type TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT FALSE,
username TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE(username, name),
CONSTRAINT fk_user_score
FOREIGN KEY(username)
REFERENCES user_score(username)
);
COMMIT;
|
