-
Notifications
You must be signed in to change notification settings - Fork 3
/
schema.sql
executable file
·113 lines (99 loc) · 3.31 KB
/
schema.sql
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
CREATE TABLE "users"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL UNIQUE,
password CHAR(76) NOT NULL,
avatar TEXT);
CREATE TABLE "diary"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
title TEXT,
content TEXT);
CREATE TABLE "diary_archive"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL,
username TEXT NOT NULL,
title TEXT,
content TEXT);
CREATE TABLE "rules"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s','now')),
username TEXT NOT NULL,
title TEXT NOT NULL,
note TEXT,
applies_to TEXT NOT NULL);
CREATE TABLE "rules_archive"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL,
username TEXT NOT NULL,
title TEXT NOT NULL,
note TEXT,
applies_to TEXT NOT NULL);
CREATE TABLE "stars"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT,
note TEXT,
owner TEXT);
CREATE TABLE "rewards"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
cost INTEGER NOT NULL,
title TEXT NOT NULL,
note TEXT,
image TEXT,
link TEXT,
owner TEXT,
award_date INTEGER DEFAULT NULL);
CREATE TABLE "rewards_archive"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
cost INTEGER NOT NULL,
title TEXT NOT NULL,
note TEXT,
image TEXT,
link TEXT,
owner TEXT,
award_date INTEGER DEFAULT NULL);
CREATE TABLE "notes"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
title TEXT NOT NULL,
note TEXT NOT NULL);
CREATE TABLE "notes_archive"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
title TEXT NOT NULL,
note TEXT NOT NULL);
CREATE TABLE "external_links"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
title TEXT NOT NULL,
link TEXT NOT NULL);
CREATE TABLE "gallery"(
id INTEGER PRIMARY KEY AUTOINCREMENT,
date INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
username TEXT NOT NULL,
image TEXT NOT NULL
);
CREATE TRIGGER update_username UPDATE OF username ON users
BEGIN
UPDATE rules SET username = new.username WHERE username = old.username;
UPDATE rules_archive SET username = new.username WHERE username = old.username;
UPDATE diary SET username = new.username WHERE username = old.username;
UPDATE diary_archive SET username = new.username WHERE username = old.username;
UPDATE stars SET username = new.username WHERE username = old.username;
UPDATE notes SET username = new.username WHERE username = old.username;
UPDATE notes_archive SET username = new.username WHERE username = old.username;
UPDATE rewards SET username = new.username WHERE username = old.username;
UPDATE rewards_archive SET username = new.username WHERE username = old.username;
UPDATE external_links SET username = new.username WHERE username = old.username;
UPDATE gallery SET username = new.username WHERE username = old.username;
END;
INSERT INTO users(username, password) VALUES('silty', '$2y$10$dWEB3n9q12CKMr8MzOX/Y.rLIBTLMJUmriOA1Yk.onpIe/uWTU0Fm');