1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
BEGIN;
CREATE TABLE user_score (
id INT GENERATED BY DEFAULT AS IDENTITY,
username TEXT UNIQUE NOT NULL,
burn_time TIMESTAMP NOT NULL DEFAULT NOW() + interval '1 day',
score SMALLINT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE action (
id INT GENERATED BY DEFAULT AS IDENTITY,
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 NOW(),
UNIQUE(username, name),
CONSTRAINT fk_user_score
FOREIGN KEY(username)
REFERENCES user_score(username)
);
COMMIT;
|
