1 package database
2
3 var dbSchema = []string{
4 `CREATE TABLE account (
5 id smallserial PRIMARY KEY,
6 username varchar(255) NOT NULL,
7 password text NOT NULL,
8 role integer NOT NULL,
9 lastactive bigint NOT NULL,
10 session varchar(64) NOT NULL
11 -- v2: style varchar(64) NOT NULL DEFAULT ''
12 -- v6: locale varchar(64) NOT NULL DEFAULT ''
13 );
14 CREATE UNIQUE INDEX ON account (username);
15 CREATE UNIQUE INDEX ON account (session);
16
17 CREATE TABLE ban (
18 id serial PRIMARY KEY,
19 ip varchar(64) NOT NULL,
20 timestamp bigint NOT NULL,
21 expire bigint NOT NULL,
22 reason text NOT NULL
23 );
24 CREATE UNIQUE INDEX ON ban (ip);
25
26 -- v9: CREATE TABLE banfile (
27 -- v9: hash char(64) PRIMARY KEY
28 -- v9: );
29
30 -- v13: CREATE TABLE banner (
31 -- v13: id serial PRIMARY KEY,
32 -- v13: name text NOT NULL,
33 -- v13: width smallint NOT NULL,
34 -- v13: height smallint NOT NULL,
35 -- v13: overboard smallint NOT NULL,
36 -- v13: news smallint NOT NULL,
37 -- v13: pages smallint NOT NULL
38 -- v13: );
39 -- v13: CREATE UNIQUE INDEX ON banner (name);
40
41 -- v13: CREATE TABLE banner_board (
42 -- v13: banner smallint NOT NULL REFERENCES banner (id) ON DELETE CASCADE,
43 -- v13: board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
44 -- v13: PRIMARY KEY (banner, board)
45 -- v13: );
46
47 CREATE TABLE board (
48 id smallserial PRIMARY KEY,
49 dir varchar(255) NOT NULL,
50 name varchar(255) NOT NULL,
51 description text NOT NULL,
52 type smallint NOT NULL,
53 lock smallint NOT NULL,
54 approval smallint NOT NULL,
55 reports smallint NOT NULL,
56 style varchar(64) NOT NULL,
57 locale varchar(64) NOT NULL,
58 delay integer NOT NULL,
59 minname smallint NOT NULL,
60 maxname smallint NOT NULL,
61 minemail smallint NOT NULL,
62 maxemail smallint NOT NULL,
63 minsubject smallint NOT NULL,
64 maxsubject smallint NOT NULL,
65 minmessage smallint NOT NULL,
66 maxmessage smallint NOT NULL,
67 minsizethread bigint NOT NULL,
68 maxsizethread bigint NOT NULL,
69 minsizereply bigint NOT NULL,
70 maxsizereply bigint NOT NULL,
71 thumbwidth smallint NOT NULL,
72 thumbheight smallint NOT NULL,
73 defaultname varchar(255) NOT NULL,
74 wordbreak smallint NOT NULL,
75 truncate smallint NOT NULL,
76 threads smallint NOT NULL,
77 replies smallint NOT NULL,
78 maxthreads smallint NOT NULL,
79 maxreplies smallint NOT NULL
80 -- v3: oekaki smallint NOT NULL DEFAULT 0
81 -- v4: rules text NOT NULL DEFAULT ''
82 -- v7: hide smallint NOT NULL DEFAULT 0
83 -- v8: backlinks smallint NOT NULL DEFAULT 0
84 -- v8: instances smallint NOT NULL DEFAULT 1
85 -- v9: identifiers smallint NOT NULL DEFAULT 0
86 -- v10: files smallint NOT NULL DEFAULT 1
87 -- v11: gallery smallint NOT NULL DEFAULT 1
88 );
89 CREATE UNIQUE INDEX ON board (dir);
90
91 CREATE TABLE board_embed (
92 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
93 embed varchar(64) NOT NULL,
94 PRIMARY KEY (board, embed)
95 );
96
97 CREATE TABLE board_upload (
98 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
99 upload varchar(64) NOT NULL,
100 PRIMARY KEY (board, upload)
101 );
102
103 CREATE TABLE captcha (
104 ip varchar(64) PRIMARY KEY,
105 timestamp bigint NOT NULL,
106 refresh smallint NOT NULL,
107 image varchar(64) NOT NULL,
108 text varchar(5) NOT NULL
109 );
110
111 CREATE TABLE config (
112 name text PRIMARY KEY,
113 value text NOT NULL
114 );
115 INSERT INTO config VALUES ('version', 1);
116
117 CREATE TABLE keyword (
118 id smallserial PRIMARY KEY,
119 text varchar(255) NOT NULL,
120 action varchar(255) NOT NULL
121 );
122 CREATE UNIQUE INDEX ON keyword (text);
123
124 CREATE TABLE keyword_board (
125 keyword smallint NOT NULL REFERENCES keyword (id) ON DELETE CASCADE,
126 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
127 PRIMARY KEY (keyword, board)
128 );
129
130 CREATE TABLE log (
131 id serial PRIMARY KEY,
132 account smallint NULL REFERENCES account (id) ON DELETE SET NULL,
133 board smallint NULL REFERENCES board (id) ON DELETE SET NULL,
134 timestamp bigint NOT NULL,
135 message text NOT NULL,
136 changes text NOT NULL
137 );
138
139 -- v4: CREATE TABLE news (
140 -- v4: id serial PRIMARY KEY,
141 -- v4: account smallint NULL REFERENCES account (id) ON DELETE SET NULL,
142 -- v4: timestamp bigint NOT NULL,
143 -- v4: modified bigint NOT NULL,
144 -- v4: share smallint NOT NULL,
145 -- v4: name varchar(255) NOT NULL,
146 -- v4: subject varchar(255) NOT NULL,
147 -- v4: message text NOT NULL
148 -- v4: );
149
150 -- v12: CREATE TABLE page (
151 -- v12: id serial PRIMARY KEY,
152 -- v12: path text NOT NULL,
153 -- v12: content text NOT NULL
154 -- v12: );
155 -- v13: CREATE UNIQUE INDEX ON page (path);
156
157 CREATE TABLE post (
158 id serial PRIMARY KEY,
159 parent integer REFERENCES post (id) ON DELETE CASCADE,
160 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
161 timestamp bigint NOT NULL,
162 bumped bigint NOT NULL,
163 ip varchar(64) NOT NULL,
164 name varchar(75) NOT NULL,
165 tripcode varchar(24) NOT NULL,
166 email varchar(75) NOT NULL,
167 nameblock varchar(255) NOT NULL,
168 subject varchar(75) NOT NULL,
169 message text NOT NULL,
170 password varchar(255) NOT NULL,
171 file text NOT NULL,
172 filehash text NULL,
173 fileoriginal varchar(255) NOT NULL,
174 filesize integer NOT NULL default '0',
175 filewidth smallint NOT NULL default '0',
176 fileheight smallint NOT NULL default '0',
177 thumb varchar(255) NOT NULL,
178 thumbwidth smallint NOT NULL default '0',
179 thumbheight smallint NOT NULL default '0',
180 moderated smallint NOT NULL default '1',
181 stickied smallint NOT NULL default '0',
182 locked smallint NOT NULL default '0'
183 -- v5: filemime varchar(64) NOT NULL default ''
184 );
185 CREATE INDEX ON post (board);
186 CREATE INDEX ON post (parent);
187 CREATE INDEX ON post (moderated);
188 CREATE INDEX ON post (stickied);
189 CREATE INDEX ON post (bumped);
190 CREATE UNIQUE INDEX ON post (filehash);
191 -- v8: CREATE INDEX ON post (filehash);
192
193 CREATE TABLE report (
194 id serial PRIMARY KEY,
195 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
196 post integer NOT NULL REFERENCES post (id) ON DELETE CASCADE,
197 timestamp bigint NOT NULL,
198 ip varchar(64) NOT NULL
199 );
200 CREATE UNIQUE INDEX ON report (board, post, ip);
201
202 -- v14: CREATE TABLE subscription (
203 -- v14: id serial PRIMARY KEY,
204 -- v14: ip text NOT NULL,
205 -- v14: confirm bigint NOT NULL,
206 -- v14: email text NOT NULL,
207 -- v14: board int NOT NULL,
208 -- v14: target int NOT NULL
209 -- v14: );`,
210
211 `ALTER TABLE account ADD COLUMN style varchar(64) NOT NULL DEFAULT '';
212 UPDATE config SET value = '2' WHERE name = 'version';`,
213
214 `ALTER TABLE board ADD COLUMN oekaki smallint NOT NULL DEFAULT 0;
215 UPDATE config SET value = '3' WHERE name = 'version';`,
216
217 `ALTER TABLE board ADD COLUMN rules text NOT NULL DEFAULT '';
218 CREATE TABLE news (
219 id serial PRIMARY KEY,
220 account smallint NULL REFERENCES account (id) ON DELETE SET NULL,
221 timestamp bigint NOT NULL,
222 modified bigint NOT NULL,
223 share smallint NOT NULL,
224 name varchar(255) NOT NULL,
225 subject varchar(255) NOT NULL,
226 message text NOT NULL
227 );
228 UPDATE config SET value = '4' WHERE name = 'version';`,
229
230 `ALTER TABLE post ADD COLUMN filemime varchar(64) NOT NULL default '';
231 UPDATE config SET value = '5' WHERE name = 'version';`,
232
233 `ALTER TABLE account ADD COLUMN locale varchar(64) NOT NULL default '';
234 UPDATE config SET value = '6' WHERE name = 'version';`,
235
236 `ALTER TABLE board ADD COLUMN hide smallint NOT NULL DEFAULT 0;
237 UPDATE config SET value = '7' WHERE name = 'version';`,
238
239 `ALTER TABLE board ADD COLUMN backlinks smallint NOT NULL DEFAULT 0;
240 ALTER TABLE board ADD COLUMN instances smallint NOT NULL DEFAULT 1;
241 DROP INDEX post_filehash_idx;
242 CREATE INDEX ON post (filehash);
243 UPDATE config SET value = '8' WHERE name = 'version';`,
244
245 `ALTER TABLE board ADD COLUMN identifiers smallint NOT NULL DEFAULT 0;
246 CREATE TABLE banfile (
247 hash char(64) PRIMARY KEY
248 );
249 UPDATE config SET value = '9' WHERE name = 'version';`,
250
251 `ALTER TABLE board ADD COLUMN files smallint NOT NULL DEFAULT 1;
252 UPDATE config SET value = '10' WHERE name = 'version';`,
253
254 `ALTER TABLE board ADD COLUMN gallery smallint NOT NULL DEFAULT 1;
255 UPDATE config SET value = '11' WHERE name = 'version';`,
256
257 `CREATE TABLE page (
258 id serial PRIMARY KEY,
259 path text NOT NULL,
260 content text NOT NULL
261 );
262 UPDATE config SET value = '12' WHERE name = 'version';`,
263
264 `CREATE UNIQUE INDEX ON page (path);
265 CREATE TABLE banner (
266 id serial PRIMARY KEY,
267 name text NOT NULL,
268 width smallint NOT NULL,
269 height smallint NOT NULL,
270 overboard smallint NOT NULL,
271 news smallint NOT NULL,
272 pages smallint NOT NULL
273 );
274 CREATE UNIQUE INDEX ON banner (name);
275 CREATE TABLE banner_board (
276 banner smallint NOT NULL REFERENCES banner (id) ON DELETE CASCADE,
277 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
278 PRIMARY KEY (banner, board)
279 );
280 UPDATE config SET value = '13' WHERE name = 'version';`,
281
282 `CREATE TABLE subscription (
283 id serial PRIMARY KEY,
284 ip text NOT NULL,
285 confirm bigint NOT NULL,
286 email text NOT NULL,
287 board int NOT NULL,
288 target int NOT NULL
289 );
290 UPDATE config SET value = '14' WHERE name = 'version';`,
291 }
292
View as plain text