-- 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 to_timestamp(lastfetch); 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; -- rlists.added -> timestamptz ALTER TABLE rlists ALTER COLUMN added DROP DEFAULT; ALTER TABLE rlists ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); ALTER TABLE rlists ALTER COLUMN added SET DEFAULT NOW(); -- wlists.added -> timestamptz ALTER TABLE wlists ALTER COLUMN added DROP DEFAULT; ALTER TABLE wlists ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); ALTER TABLE wlists ALTER COLUMN added SET DEFAULT NOW(); -- threads_posts.date -> timestamptz ALTER TABLE threads_posts ALTER COLUMN date DROP DEFAULT; ALTER TABLE threads_posts ALTER COLUMN date TYPE timestamptz USING to_timestamp(date); ALTER TABLE threads_posts ALTER COLUMN date SET DEFAULT NOW(); -- threads_posts.edited -> timestamptz + allow NULL ALTER TABLE threads_posts ALTER COLUMN edited DROP NOT NULL; ALTER TABLE threads_posts ALTER COLUMN edited DROP DEFAULT; ALTER TABLE threads_posts ALTER COLUMN edited TYPE timestamptz USING CASE WHEN edited = 0 THEN NULL ELSE to_timestamp(edited) END; -- votes.date -> timestamptz ALTER TABLE votes ALTER COLUMN date DROP DEFAULT; ALTER TABLE votes ALTER COLUMN date TYPE timestamptz USING to_timestamp(date); ALTER TABLE votes ALTER COLUMN date SET DEFAULT NOW(); -- users.registered -> timestamptz ALTER TABLE users ALTER COLUMN registered DROP DEFAULT; ALTER TABLE users ALTER COLUMN registered TYPE timestamptz USING to_timestamp(registered); ALTER TABLE users ALTER COLUMN registered SET DEFAULT NOW(); -- tags.added -> timestamptz ALTER TABLE tags ALTER COLUMN added DROP DEFAULT; ALTER TABLE tags ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); ALTER TABLE tags ALTER COLUMN added SET DEFAULT NOW(); -- changes.added -> timestamptz ALTER TABLE changes ALTER COLUMN added DROP DEFAULT; ALTER TABLE changes ALTER COLUMN added TYPE timestamptz USING to_timestamp(added); ALTER TABLE changes ALTER COLUMN added SET DEFAULT NOW(); -- 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;