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 -- v15: CREATE TABLE category (
112 -- v15: id smallserial PRIMARY KEY,
113 -- v15: parent smallint REFERENCES category (id) ON DELETE CASCADE,
114 -- v15: sort smallint NOT NULL,
115 -- v15: name varchar(255) NOT NULL,
116 -- v15: description varchar(255) NOT NULL
117 -- v15: );
118
119 -- v15: CREATE TABLE category_board (
120 -- v15: category smallint NOT NULL REFERENCES category (id) ON DELETE CASCADE,
121 -- v15: board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
122 -- v15: sort smallint NOT NULL,
123 -- v15: PRIMARY KEY (category, board)
124 -- v15: );
125
126 CREATE TABLE config (
127 name text PRIMARY KEY,
128 value text NOT NULL
129 );
130 INSERT INTO config VALUES ('version', 1);
131
132 CREATE TABLE keyword (
133 id smallserial PRIMARY KEY,
134 text varchar(255) NOT NULL,
135 action varchar(255) NOT NULL
136 );
137 CREATE UNIQUE INDEX ON keyword (text);
138
139 CREATE TABLE keyword_board (
140 keyword smallint NOT NULL REFERENCES keyword (id) ON DELETE CASCADE,
141 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
142 PRIMARY KEY (keyword, board)
143 );
144
145 CREATE TABLE log (
146 id serial PRIMARY KEY,
147 account smallint NULL REFERENCES account (id) ON DELETE SET NULL,
148 board smallint NULL REFERENCES board (id) ON DELETE SET NULL,
149 timestamp bigint NOT NULL,
150 message text NOT NULL,
151 changes text NOT NULL
152 );
153
154 -- v4: CREATE TABLE news (
155 -- v4: id serial PRIMARY KEY,
156 -- v4: account smallint NULL REFERENCES account (id) ON DELETE SET NULL,
157 -- v4: timestamp bigint NOT NULL,
158 -- v4: modified bigint NOT NULL,
159 -- v4: share smallint NOT NULL,
160 -- v4: name varchar(255) NOT NULL,
161 -- v4: subject varchar(255) NOT NULL,
162 -- v4: message text NOT NULL
163 -- v4: );
164
165 -- v12: CREATE TABLE page (
166 -- v12: id serial PRIMARY KEY,
167 -- v12: path text NOT NULL,
168 -- v12: content text NOT NULL
169 -- v12: );
170 -- v13: CREATE UNIQUE INDEX ON page (path);
171
172 CREATE TABLE post (
173 id serial PRIMARY KEY,
174 parent integer REFERENCES post (id) ON DELETE CASCADE,
175 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
176 timestamp bigint NOT NULL,
177 bumped bigint NOT NULL,
178 ip varchar(64) NOT NULL,
179 name varchar(75) NOT NULL,
180 tripcode varchar(24) NOT NULL,
181 email varchar(75) NOT NULL,
182 nameblock varchar(255) NOT NULL,
183 subject varchar(75) NOT NULL,
184 message text NOT NULL,
185 password varchar(255) NOT NULL,
186 file text NOT NULL,
187 filehash text NULL,
188 fileoriginal varchar(255) NOT NULL,
189 filesize integer NOT NULL default '0',
190 filewidth smallint NOT NULL default '0',
191 fileheight smallint NOT NULL default '0',
192 thumb varchar(255) NOT NULL,
193 thumbwidth smallint NOT NULL default '0',
194 thumbheight smallint NOT NULL default '0',
195 moderated smallint NOT NULL default '1',
196 stickied smallint NOT NULL default '0',
197 locked smallint NOT NULL default '0'
198 -- v5: filemime varchar(64) NOT NULL default ''
199 );
200 CREATE INDEX ON post (board);
201 CREATE INDEX ON post (parent);
202 CREATE INDEX ON post (moderated);
203 CREATE INDEX ON post (stickied);
204 CREATE INDEX ON post (bumped);
205 CREATE UNIQUE INDEX ON post (filehash);
206 -- v8: CREATE INDEX ON post (filehash);
207
208 CREATE TABLE report (
209 id serial PRIMARY KEY,
210 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
211 post integer NOT NULL REFERENCES post (id) ON DELETE CASCADE,
212 timestamp bigint NOT NULL,
213 ip varchar(64) NOT NULL
214 );
215 CREATE UNIQUE INDEX ON report (board, post, ip);
216
217 -- v14: CREATE TABLE subscription (
218 -- v14: id serial PRIMARY KEY,
219 -- v14: ip text NOT NULL,
220 -- v14: confirm bigint NOT NULL,
221 -- v14: email text NOT NULL,
222 -- v14: board int NOT NULL,
223 -- v14: target int NOT NULL
224 -- v14: );`,
225
226 `ALTER TABLE account ADD COLUMN style varchar(64) NOT NULL DEFAULT '';
227 UPDATE config SET value = '2' WHERE name = 'version';`,
228
229 `ALTER TABLE board ADD COLUMN oekaki smallint NOT NULL DEFAULT 0;
230 UPDATE config SET value = '3' WHERE name = 'version';`,
231
232 `ALTER TABLE board ADD COLUMN rules text NOT NULL DEFAULT '';
233 CREATE TABLE news (
234 id serial PRIMARY KEY,
235 account smallint NULL REFERENCES account (id) ON DELETE SET NULL,
236 timestamp bigint NOT NULL,
237 modified bigint NOT NULL,
238 share smallint NOT NULL,
239 name varchar(255) NOT NULL,
240 subject varchar(255) NOT NULL,
241 message text NOT NULL
242 );
243 UPDATE config SET value = '4' WHERE name = 'version';`,
244
245 `ALTER TABLE post ADD COLUMN filemime varchar(64) NOT NULL default '';
246 UPDATE config SET value = '5' WHERE name = 'version';`,
247
248 `ALTER TABLE account ADD COLUMN locale varchar(64) NOT NULL default '';
249 UPDATE config SET value = '6' WHERE name = 'version';`,
250
251 `ALTER TABLE board ADD COLUMN hide smallint NOT NULL DEFAULT 0;
252 UPDATE config SET value = '7' WHERE name = 'version';`,
253
254 `ALTER TABLE board ADD COLUMN backlinks smallint NOT NULL DEFAULT 0;
255 ALTER TABLE board ADD COLUMN instances smallint NOT NULL DEFAULT 1;
256 DROP INDEX post_filehash_idx;
257 CREATE INDEX ON post (filehash);
258 UPDATE config SET value = '8' WHERE name = 'version';`,
259
260 `ALTER TABLE board ADD COLUMN identifiers smallint NOT NULL DEFAULT 0;
261 CREATE TABLE banfile (
262 hash char(64) PRIMARY KEY
263 );
264 UPDATE config SET value = '9' WHERE name = 'version';`,
265
266 `ALTER TABLE board ADD COLUMN files smallint NOT NULL DEFAULT 1;
267 UPDATE config SET value = '10' WHERE name = 'version';`,
268
269 `ALTER TABLE board ADD COLUMN gallery smallint NOT NULL DEFAULT 1;
270 UPDATE config SET value = '11' WHERE name = 'version';`,
271
272 `CREATE TABLE page (
273 id serial PRIMARY KEY,
274 path text NOT NULL,
275 content text NOT NULL
276 );
277 UPDATE config SET value = '12' WHERE name = 'version';`,
278
279 `CREATE UNIQUE INDEX ON page (path);
280 CREATE TABLE banner (
281 id serial PRIMARY KEY,
282 name text NOT NULL,
283 width smallint NOT NULL,
284 height smallint NOT NULL,
285 overboard smallint NOT NULL,
286 news smallint NOT NULL,
287 pages smallint NOT NULL
288 );
289 CREATE UNIQUE INDEX ON banner (name);
290 CREATE TABLE banner_board (
291 banner smallint NOT NULL REFERENCES banner (id) ON DELETE CASCADE,
292 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
293 PRIMARY KEY (banner, board)
294 );
295 UPDATE config SET value = '13' WHERE name = 'version';`,
296
297 `CREATE TABLE subscription (
298 id serial PRIMARY KEY,
299 ip text NOT NULL,
300 confirm bigint NOT NULL,
301 email text NOT NULL,
302 board int NOT NULL,
303 target int NOT NULL
304 );
305 UPDATE config SET value = '14' WHERE name = 'version';`,
306
307 `CREATE TABLE category (
308 id smallserial PRIMARY KEY,
309 parent smallint REFERENCES category (id) ON DELETE CASCADE,
310 sort smallint NOT NULL,
311 name varchar(255) NOT NULL,
312 description varchar(255) NOT NULL
313 );
314 CREATE TABLE category_board (
315 category smallint NOT NULL REFERENCES category (id) ON DELETE CASCADE,
316 board smallint NOT NULL REFERENCES board (id) ON DELETE CASCADE,
317 sort smallint NOT NULL,
318 PRIMARY KEY (category, board)
319 );
320 UPDATE config SET value = '15' WHERE name = 'version';`,
321 }
322
View as plain text