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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
|
-- Create table for session data storage
CREATE TABLE sessions (
uid integer NOT NULL REFERENCES users(id),
token bytea NOT NULL,
expiration timestamptz NOT NULL DEFAULT (NOW() + '1 year'::interval),
PRIMARY KEY (uid, token)
);
-- Add column to users for salt storage
ALTER TABLE users ADD COLUMN salt character(9) NOT NULL DEFAULT ''::bpchar;
-- The anime table:
-- - use timestamp data type for anime.lastfetch
-- - allow NULL for all columns except id
ALTER TABLE anime ALTER COLUMN lastfetch DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN lastfetch DROP DEFAULT;
UPDATE anime SET lastfetch = NULL WHERE lastfetch <= 0;
ALTER TABLE anime ALTER COLUMN lastfetch TYPE timestamptz USING 'epoch'::timestamptz + lastfetch * interval '1 second';
ALTER TABLE anime ALTER COLUMN ann_id DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN ann_id DROP DEFAULT;
UPDATE anime SET ann_id = NULL WHERE ann_id = 0;
ALTER TABLE anime ALTER COLUMN nfo_id DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN nfo_id DROP DEFAULT;
UPDATE anime SET nfo_id = NULL WHERE nfo_id = '';
ALTER TABLE anime ALTER COLUMN title_kanji DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN title_kanji DROP DEFAULT;
UPDATE anime SET title_kanji = NULL WHERE title_kanji = '';
ALTER TABLE anime ALTER COLUMN title_romaji DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN title_romaji DROP DEFAULT;
UPDATE anime SET title_romaji = NULL WHERE title_romaji = '';
ALTER TABLE anime ALTER COLUMN type DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN type DROP DEFAULT;
UPDATE anime SET type = NULL WHERE type = 0;
UPDATE anime SET type = type-1;
ALTER TABLE anime ALTER COLUMN year DROP NOT NULL;
ALTER TABLE anime ALTER COLUMN year DROP DEFAULT;
UPDATE anime SET year = NULL WHERE year = 0;
-- screenshots.status (smallint) -> screenshots.processed (boolean)
ALTER TABLE screenshots RENAME COLUMN status TO processed;
ALTER TABLE screenshots ALTER COLUMN processed DROP DEFAULT;
ALTER TABLE screenshots ALTER COLUMN processed TYPE boolean USING processed::int::boolean;
ALTER TABLE screenshots ALTER COLUMN processed SET DEFAULT FALSE;
-- two new resolutions have been added, array indexes have changed
UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 5;
UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 7;
-- remove the DEFERRED attribute on all foreign key checks on which it isn't necessary
-- (note: these queries all assume the foreign keys have their default names, as given
-- by PostgreSQL. This shouldn't be a problem, provided if you haven't touched them.)
ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey;
ALTER TABLE changes DROP CONSTRAINT changes_causedby_fkey;
ALTER TABLE producers_rev DROP CONSTRAINT producers_rev_id_fkey;
ALTER TABLE producers_rev DROP CONSTRAINT producers_rev_pid_fkey;
ALTER TABLE quotes DROP CONSTRAINT quotes_vid_fkey;
ALTER TABLE releases_lang DROP CONSTRAINT releases_lang_rid_fkey;
ALTER TABLE releases_media DROP CONSTRAINT releases_media_rid_fkey;
ALTER TABLE releases_platforms DROP CONSTRAINT releases_platforms_rid_fkey;
ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_rid_fkey;
ALTER TABLE releases_producers DROP CONSTRAINT releases_producers_pid_fkey;
ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_id_fkey;
ALTER TABLE releases_rev DROP CONSTRAINT releases_rev_rid_fkey;
ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_rid_fkey;
ALTER TABLE releases_vn DROP CONSTRAINT releases_vn_vid_fkey;
ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey;
ALTER TABLE rlists DROP CONSTRAINT rlists_rid_fkey;
ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey;
ALTER TABLE tags_aliases DROP CONSTRAINT tags_aliases_tag_fkey;
ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_tag_fkey;
ALTER TABLE tags_parents DROP CONSTRAINT tags_parents_parent_fkey;
ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_tag_fkey;
ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_vid_fkey;
ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey;
ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_tid_fkey;
ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey;
ALTER TABLE threads_boards DROP CONSTRAINT threads_tags_tid_fkey; -- threads_boards used to be called threads_tags
ALTER TABLE vn DROP CONSTRAINT vn_rgraph_fkey;
ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_aid_fkey;
ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_vid_fkey;
ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid1_fkey;
ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid2_fkey;
ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_id_fkey;
ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_vid_fkey;
ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_vid_fkey;
ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_scr_fkey;
ALTER TABLE vn_screenshots DROP CONSTRAINT vn_screenshots_rid_fkey;
ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey;
ALTER TABLE votes DROP CONSTRAINT votes_vid_fkey;
ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey;
ALTER TABLE wlists DROP CONSTRAINT wlists_vid_fkey;
ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id);
ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id);
ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id);
ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id);
ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id);
ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id);
ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id);
ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id);
ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id);
ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id);
ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);
ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id);
ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id);
ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
-- automatically insert rows into the anime table for unknown aids
-- when inserted into vn_anime
CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$
BEGIN
IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN
INSERT INTO anime (id) VALUES (NEW.aid);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
-- Send a notify whenever anime info should be fetched
CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
BEGIN
IF NEW.lastfetch IS NULL THEN
NOTIFY anime;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify();
-- Send a notify when a new cover image is uploaded
CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$
BEGIN
IF NEW.image < 0 THEN
NOTIFY coverimage;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify();
-- Send a notify when a screenshot needs to be processed
CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$
BEGIN
IF NEW.processed = FALSE THEN
NOTIFY screenshot;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify();
-- Update vn.rgraph column and send notify when a relation graph needs to be regenerated
-- 1. NOTIFY is sent on an UPDATE or INSERT on vn with rgraph = NULL and with entries in vn_relations (deferred)
-- vn.rgraph is set to NULL when:
-- 2. UPDATE on vn where c_released or c_languages has changed (deferred, but doesn't have to be)
-- 3. New VN revision of which the title differs from previous revision (deferred)
-- 4. New VN revision with items in vn_relations that differ from previous revision (deferred)
CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
BEGIN
-- 1.
IF TG_TABLE_NAME = 'vn' THEN
IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN
NOTIFY relgraph;
END IF;
END IF;
IF TG_TABLE_NAME = 'vn' AND TG_OP = 'UPDATE' THEN
IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
-- 2.
IF OLD.c_released <> NEW.c_released OR OLD.c_languages <> NEW.c_languages THEN
UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
END IF;
-- 3 & 4
IF OLD.latest <> NEW.latest AND (
EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 WHERE v2.title <> v1.title AND v1.id = OLD.latest AND v2.id = NEW.latest)
OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = OLD.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = NEW.latest)
OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = NEW.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = OLD.latest)
) THEN
UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
END IF;
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
-- NOTIFY on insert into changes/posts/tags
CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
BEGIN
IF TG_TABLE_NAME = 'changes' THEN
NOTIFY newrevision;
ELSIF TG_TABLE_NAME = 'threads_posts' THEN
NOTIFY newpost;
ELSIF TG_TABLE_NAME = 'tags' THEN
NOTIFY newtag;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-- convert the old categories to the related tags for VNs that didn't have tags already
INSERT INTO tags_vn (uid, spoiler, vid, vote, tag)
SELECT 1, 0, v.id,
CASE
WHEN vc.cat IN('gaa', 'gab', 'pli', 'pbr', 'tfu', 'tpa', 'tpr', 'lea', 'lfa', 'lsp', 'hfa', 'hfe') THEN 2
ELSE vc.lvl
END,
CASE
WHEN vc.cat = 'gaa' THEN 43 -- NVL
WHEN vc.cat = 'gab' THEN 32 -- ADV
WHEN vc.cat = 'gac' THEN 31 -- Action game
WHEN vc.cat = 'grp' THEN 35 -- RPG
WHEN vc.cat = 'gst' THEN 33 -- Strategy game
WHEN vc.cat = 'gsi' THEN 34 -- Simulation game
WHEN vc.cat = 'pli' THEN 145 -- Linear plot
WHEN vc.cat = 'pbr' THEN 606 -- Branching plot
WHEN vc.cat = 'eac' THEN 12 -- Action
WHEN vc.cat = 'eco' THEN 104 -- Comedy
WHEN vc.cat = 'edr' THEN 147 -- Drama
WHEN vc.cat = 'efa' THEN 2 -- Fantasy
WHEN vc.cat = 'eho' THEN 7 -- Horror
WHEN vc.cat = 'emy' THEN 19 -- Mystery
WHEN vc.cat = 'ero' THEN 96 -- Romance
WHEN vc.cat = 'esc' THEN 47 -- School life
WHEN vc.cat = 'esf' THEN 105 -- Sci-Fi
WHEN vc.cat = 'esj' THEN 97 -- Shoujo ai
WHEN vc.cat = 'esn' THEN 98 -- Shounen ai
WHEN vc.cat = 'tfu' THEN 140 -- Future
WHEN vc.cat = 'tpa' THEN 141 -- Past
WHEN vc.cat = 'tpr' THEN 143 -- Present
WHEN vc.cat = 'lea' THEN 52 -- Earth
WHEN vc.cat = 'lfa' THEN 259 -- Fantasy world
WHEN vc.cat = 'lsp' THEN 53 -- Space
WHEN vc.cat = 'hfa' THEN 133 -- Male protag
WHEN vc.cat = 'hfe' THEN 134 -- Female protag
WHEN vc.cat = 'saa' THEN 23 -- Sexual content
WHEN vc.cat = 'sbe' THEN 183 -- Bestiality
WHEN vc.cat = 'sin' THEN 86 -- Insect
WHEN vc.cat = 'slo' THEN 156 -- Lolicon
WHEN vc.cat = 'ssh' THEN 184 -- Shotacon
WHEN vc.cat = 'sya' THEN 83 -- Yaoi
WHEN vc.cat = 'syu' THEN 82 -- Yuri
WHEN vc.cat = 'sra' THEN 84 -- Rape
ELSE 11 -- the deleted 'Awesome' tag, this shouldn't happen
END
FROM vn v
JOIN vn_categories vc ON vc.vid = v.latest
WHERE NOT EXISTS(SELECT 1 FROM tags_vn tv WHERE tv.vid = v.id);
DROP TABLE vn_categories;
|