diff options
author | morkt <morkt@users.noreply.github.com> | 2015-11-10 06:06:54 +0400 |
---|---|---|
committer | morkt <morkt@users.noreply.github.com> | 2015-11-10 06:06:54 +0400 |
commit | d488967ebdde36bf2c959f1d4e1c55c622887bf8 (patch) | |
tree | deed89549ba634260924c932021be0de1796d62e /util | |
parent | ddbf3ae0ae530954b0e105e99819c85cfe4de17c (diff) | |
parent | 052b78e84b4c7379f107cfaa5f9cede09b8b1b7b (diff) |
Merge branch 'master' into poll
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/all.sql | 6 | ||||
-rw-r--r-- | util/sql/func.sql | 768 | ||||
-rw-r--r-- | util/sql/schema.sql | 530 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 189 | ||||
-rw-r--r-- | util/sql/triggers.sql | 41 | ||||
-rwxr-xr-x | util/sqleditfunc.pl | 112 | ||||
-rw-r--r-- | util/updates/update_2.25-sqlsplit.sql | 258 | ||||
-rw-r--r-- | util/updates/update_2.26.sql | 9 |
8 files changed, 1096 insertions, 817 deletions
diff --git a/util/sql/all.sql b/util/sql/all.sql index 496479a2..b2b4fabc 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -9,7 +9,7 @@ CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u'); CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears'); CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff'); CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's'); -CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); +CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer); CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b'); CREATE TYPE language AS ENUM ('ar', 'ca', 'cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'he', 'hu', 'id', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sv', 'tr', 'uk', 'vi', 'zh'); CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); @@ -33,6 +33,10 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid \i util/sql/func.sql +-- auto-generated editing functions + +\i util/sql/editfunc.sql + -- constraints & indices \i util/sql/tableattrs.sql diff --git a/util/sql/func.sql b/util/sql/func.sql index 51711f77..125455a2 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -15,6 +15,14 @@ CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$ SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi'); $$ LANGUAGE sql IMMUTABLE; +-- Wrapper around to_tsvector() and strip_bb_tags(), implemented in plpgsql and +-- with an associated cost function to make it opaque to the query planner and +-- ensure the query planner realizes that this function is _slow_. +CREATE OR REPLACE FUNCTION bb_tsvector(t text) RETURNS tsvector AS $$ +BEGIN + RETURN to_tsvector('english', strip_bb_tags(t)); +END; +$$ LANGUAGE plpgsql IMMUTABLE COST 500; -- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers. CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$ @@ -23,60 +31,56 @@ CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$ $$ LANGUAGE sql IMMUTABLE; --- update_vncache(id) - updates the c_* columns in the vn table +-- update_vncache(id) - updates some c_* columns in the vn table CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ UPDATE vn SET - c_released = COALESCE((SELECT - MIN(rr1.released) - FROM releases_rev rr1 - JOIN releases r1 ON rr1.id = r1.latest - JOIN releases_vn rv1 ON rr1.id = rv1.rid - WHERE rv1.vid = vn.id - AND rr1.type <> 'trial' - AND r1.hidden = FALSE - AND rr1.released <> 0 - GROUP BY rv1.vid + c_released = COALESCE(( + SELECT MIN(r.released) + FROM releases r + JOIN releases_vn rv ON r.id = rv.id + WHERE rv.vid = $1 + AND r.type <> 'trial' + AND r.hidden = FALSE + AND r.released <> 0 + GROUP BY rv.vid ), 0), c_olang = ARRAY( SELECT lang FROM releases_lang - WHERE rid = ( - SELECT r.latest + WHERE id = ( + SELECT r.id FROM releases_vn rv - JOIN releases r ON rv.rid = r.latest - JOIN releases_rev rr ON rr.id = r.latest AND rr.rid = r.id - WHERE rr.released > 0 + JOIN releases r ON rv.id = r.id + WHERE r.released > 0 AND NOT r.hidden AND rv.vid = $1 - ORDER BY rr.released + ORDER BY r.released LIMIT 1 ) ), c_languages = ARRAY( - SELECT rl2.lang - FROM releases_rev rr2 - JOIN releases_lang rl2 ON rl2.rid = rr2.id - JOIN releases r2 ON rr2.id = r2.latest - JOIN releases_vn rv2 ON rr2.id = rv2.rid - WHERE rv2.vid = vn.id - AND rr2.type <> 'trial' - AND rr2.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer - AND r2.hidden = FALSE - GROUP BY rl2.lang - ORDER BY rl2.lang + SELECT rl.lang + FROM releases_lang rl + JOIN releases r ON r.id = rl.id + JOIN releases_vn rv ON r.id = rv.id + WHERE rv.vid = $1 + AND r.type <> 'trial' + AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer + AND r.hidden = FALSE + GROUP BY rl.lang + ORDER BY rl.lang ), c_platforms = ARRAY( - SELECT rp3.platform - FROM releases_platforms rp3 - JOIN releases_rev rr3 ON rp3.rid = rr3.id - JOIN releases r3 ON rp3.rid = r3.latest - JOIN releases_vn rv3 ON rp3.rid = rv3.rid - WHERE rv3.vid = vn.id - AND rr3.type <> 'trial' - AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer - AND r3.hidden = FALSE - GROUP BY rp3.platform - ORDER BY rp3.platform + SELECT rp.platform + FROM releases_platforms rp + JOIN releases r ON rp.id = r.id + JOIN releases_vn rv ON rp.id = rv.id + WHERE rv.vid = $1 + AND r.type <> 'trial' + AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer + AND r.hidden = FALSE + GROUP BY rp.platform + ORDER BY rp.platform ) WHERE id = $1; $$ LANGUAGE sql; @@ -154,9 +158,9 @@ BEGIN -- all char<->trait links of the latest revisions, including chars inherited from child traits -- (also includes meta traits, because they could have a normal trait as parent) WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, meta) AS ( - SELECT 15, tid, c.id, spoil, false + SELECT 15, tid, ct.id, spoil, false FROM chars_traits ct - JOIN chars c ON c.latest = ct.cid + JOIN chars c ON c.id = ct.id WHERE NOT c.hidden UNION ALL SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta @@ -186,18 +190,18 @@ $$ LANGUAGE plpgsql; -- revision insertion abstraction -- ---------------------------------------------------------- +-- The two functions below are utility functions used by the item-specific functions in editfunc.sql --- IMPORTANT: these functions will need to be updated on each change in the DB structure --- of the relevant tables - - --- create temporary table for generic revision info -CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$ +-- create temporary table for generic revision info, and returns the chid of the revision being edited (or NULL). +CREATE OR REPLACE FUNCTION edit_revtable(xtype dbentry_type, xitemid integer, xrev integer) RETURNS integer AS $$ +DECLARE + ret integer; + x record; BEGIN BEGIN CREATE TEMPORARY TABLE edit_revision ( type dbentry_type NOT NULL, - iid integer, + itemid integer, requester integer, ip inet, comments text, @@ -207,15 +211,9 @@ BEGIN EXCEPTION WHEN duplicate_table THEN TRUNCATE edit_revision; END; - INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t, - ( SELECT vid FROM vn_rev WHERE id = i - UNION SELECT rid FROM releases_rev WHERE id = i - UNION SELECT cid FROM chars_rev WHERE id = i - UNION SELECT pid FROM producers_rev WHERE id = i - UNION SELECT sid FROM staff_rev WHERE id = i), - COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE), - COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE) - ); + SELECT INTO x id, ihid, ilock FROM changes c WHERE type = xtype AND itemid = xitemid AND rev = xrev; + INSERT INTO edit_revision (type, itemid, ihid, ilock) VALUES (xtype, xitemid, COALESCE(x.ihid, FALSE), COALESCE(x.ilock, FALSE)); + RETURN x.id; END; $$ LANGUAGE plpgsql; @@ -223,305 +221,145 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$ DECLARE - r edit_rettype; - t dbentry_type; - i integer; + ret edit_rettype; + xtype dbentry_type; BEGIN - SELECT type INTO t FROM edit_revision; - SELECT iid INTO i FROM edit_revision; + SELECT type INTO xtype FROM edit_revision; + SELECT itemid INTO ret.itemid FROM edit_revision; -- figure out revision number - IF i IS NULL THEN - r.rev := 1; - ELSE - SELECT c.rev+1 INTO r.rev FROM changes c - JOIN ( SELECT id FROM vn_rev WHERE t = 'v' AND vid = i - UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i - UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i - UNION SELECT id FROM chars_rev WHERE t = 'c' AND cid = i - UNION SELECT id FROM staff_rev WHERE t = 's' AND sid = i - ) x(id) ON x.id = c.id - ORDER BY c.id DESC - LIMIT 1; - END IF; - -- insert change - INSERT INTO changes (type, requester, ip, comments, ihid, ilock, rev) - SELECT t, requester, ip, comments, ihid, ilock, r.rev - FROM edit_revision - RETURNING id INTO r.cid; + SELECT MAX(rev)+1 INTO ret.rev FROM changes WHERE type = xtype AND itemid = ret.itemid; + SELECT COALESCE(ret.rev, 1) INTO ret.rev; -- insert DB item - IF i IS NULL THEN - CASE t - WHEN 'v' THEN INSERT INTO vn (latest) VALUES (0) RETURNING id INTO r.iid; - WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid; - WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid; - WHEN 'c' THEN INSERT INTO chars (latest) VALUES (0) RETURNING id INTO r.iid; - WHEN 's' THEN INSERT INTO staff (latest) VALUES (0) RETURNING id INTO r.iid; + IF ret.itemid IS NULL THEN + CASE xtype + WHEN 'v' THEN INSERT INTO vn DEFAULT VALUES RETURNING id INTO ret.itemid; + WHEN 'r' THEN INSERT INTO releases DEFAULT VALUES RETURNING id INTO ret.itemid; + WHEN 'p' THEN INSERT INTO producers DEFAULT VALUES RETURNING id INTO ret.itemid; + WHEN 'c' THEN INSERT INTO chars DEFAULT VALUES RETURNING id INTO ret.itemid; + WHEN 's' THEN INSERT INTO staff DEFAULT VALUES RETURNING id INTO ret.itemid; END CASE; - ELSE - r.iid := i; - END IF; - RETURN r; -END; -$$ LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$ -#variable_conflict use_variable -BEGIN - -- create tables, based on existing tables (so that the column types are always synchronised) - BEGIN - CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn DROP COLUMN id; - ALTER TABLE edit_vn DROP COLUMN vid; - CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_anime DROP COLUMN vid; - CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_relations DROP COLUMN vid1; - ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid; - CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_screenshots DROP COLUMN vid; - CREATE TEMPORARY TABLE edit_vn_staff (LIKE vn_staff INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_staff DROP COLUMN vid; - CREATE TEMPORARY TABLE edit_vn_seiyuu (LIKE vn_seiyuu INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_seiyuu DROP COLUMN vid; - EXCEPTION WHEN duplicate_table THEN - TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff, edit_vn_seiyuu; - END; - PERFORM edit_revtable('v', cid); - -- new VN, load defaults - IF cid IS NULL THEN - INSERT INTO edit_vn DEFAULT VALUES; - -- otherwise, load revision - ELSE - INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, image, l_encubed, l_renai, original FROM vn_rev WHERE id = cid; - INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid; - INSERT INTO edit_vn_relations SELECT vid2, relation, official FROM vn_relations WHERE vid1 = cid; - INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid; - INSERT INTO edit_vn_staff SELECT aid, role, note FROM vn_staff WHERE vid = cid; - INSERT INTO edit_vn_seiyuu SELECT aid, vs.cid, note FROM vn_seiyuu vs WHERE vid = cid; - END IF; -END; -$$ LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION edit_vn_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; -BEGIN - IF (SELECT COUNT(*) FROM edit_vn) <> 1 THEN - RAISE 'edit_vn must have exactly one row!'; - END IF; - SELECT INTO r * FROM edit_commit(); - INSERT INTO vn_rev SELECT r.cid, r.iid, title, alias, img_nsfw, length, "desc", l_wp, image, l_encubed, l_renai, original FROM edit_vn; - INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime; - INSERT INTO vn_relations SELECT r.cid, vid, relation, official FROM edit_vn_relations; - INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots; - INSERT INTO vn_staff SELECT r.cid, aid, role, note FROM edit_vn_staff; - INSERT INTO vn_seiyuu SELECT r.cid, aid, cid, note FROM edit_vn_seiyuu; - UPDATE vn SET latest = r.cid WHERE id = r.iid; - RETURN r; -END; -$$ LANGUAGE plpgsql; - - - -CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$ -BEGIN - -- temp. tables - BEGIN - CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release DROP COLUMN id; - ALTER TABLE edit_release DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_lang DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_media DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_platforms DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_producers DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_vn DROP COLUMN rid; - EXCEPTION WHEN duplicate_table THEN - TRUNCATE edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; - END; - PERFORM edit_revtable('r', cid); - -- new release - IF cid IS NULL THEN - INSERT INTO edit_release DEFAULT VALUES; - -- load revision - ELSE - INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid; - INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid; - INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid; - INSERT INTO edit_release_platforms SELECT platform FROM releases_platforms WHERE rid = cid; - INSERT INTO edit_release_producers SELECT pid, developer, publisher FROM releases_producers WHERE rid = cid; - INSERT INTO edit_release_vn SELECT vid FROM releases_vn WHERE rid = cid; END IF; + -- insert change + INSERT INTO changes (type, itemid, rev, requester, ip, comments, ihid, ilock) + SELECT type, ret.itemid, ret.rev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO ret.chid; + RETURN ret; END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION edit_release_commit() RETURNS edit_rettype AS $$ +-- Check for stuff to be done when an item has been changed +CREATE OR REPLACE FUNCTION edit_committed(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ DECLARE - r edit_rettype; + xoldchid integer; BEGIN - IF (SELECT COUNT(*) FROM edit_release) <> 1 THEN - RAISE 'edit_release must have exactly one row!'; - ELSIF NOT EXISTS(SELECT 1 FROM edit_release_vn) THEN - RAISE 'edit_release_vn must have at least one row!'; + SELECT id INTO xoldchid FROM changes WHERE type = xtype AND itemid = xedit.itemid AND rev = xedit.rev-1; + + -- Set producers.rgraph to NULL and notify when: + -- 1. There's a new producer entry with some relations + -- 2. The producer name/type/language has changed + -- 3. The producer relations have been changed + IF xtype = 'p' THEN + IF -- 1. + (xoldchid IS NULL AND EXISTS(SELECT 1 FROM producers_relations_hist WHERE chid = xedit.chid)) + OR (xoldchid IS NOT NULL AND ( + -- 2. + EXISTS(SELECT 1 FROM producers_hist p1, producers_hist p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.chid = xoldchid AND p2.chid = xedit.chid) + -- 3. + OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid) + OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid) + )) + THEN + UPDATE producers SET rgraph = NULL WHERE id = xedit.itemid; + NOTIFY relgraph; -- This notify is not done by the producer_relgraph_notify trigger for new entries or if rgraph was already NULL + END IF; END IF; - SELECT INTO r * FROM edit_commit(); - INSERT INTO releases_rev SELECT r.cid, r.iid, title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM edit_release; - INSERT INTO releases_lang SELECT r.cid, lang FROM edit_release_lang; - INSERT INTO releases_media SELECT r.cid, medium, qty FROM edit_release_media; - INSERT INTO releases_platforms SELECT r.cid, platform FROM edit_release_platforms; - INSERT INTO releases_producers SELECT pid, r.cid, developer, publisher FROM edit_release_producers; - INSERT INTO releases_vn SELECT r.cid, vid FROM edit_release_vn; - UPDATE releases SET latest = r.cid WHERE id = r.iid; - RETURN r; -END; -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$ -BEGIN - BEGIN - CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_producer DROP COLUMN id; - ALTER TABLE edit_producer DROP COLUMN pid; - CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_producer_relations DROP COLUMN pid1; - ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid; - EXCEPTION WHEN duplicate_table THEN - TRUNCATE edit_producer, edit_producer_relations; - END; - PERFORM edit_revtable('p', cid); - -- new producer - IF cid IS NULL THEN - INSERT INTO edit_producer DEFAULT VALUES; - -- load revision - ELSE - INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid; - INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid; + -- Set vn.rgraph to NULL and notify when: + -- 1. There's a new vn entry with some relations + -- 2. The vn title has changed + -- 3. The vn relations have been changed + IF xtype = 'v' THEN + IF -- 1. + (xoldchid IS NULL AND EXISTS(SELECT 1 FROM vn_relations_hist WHERE chid = xedit.chid)) + OR (xoldchid IS NOT NULL AND ( + -- 2. + EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE v2.title <> v1.title AND v1.chid = xoldchid AND v2.chid = xedit.chid) + -- 3. + OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid) + OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid) + )) + THEN + UPDATE vn SET rgraph = NULL WHERE id = xedit.itemid; + NOTIFY relgraph; + END IF; END IF; -END; -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION edit_producer_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; -BEGIN - IF (SELECT COUNT(*) FROM edit_producer) <> 1 THEN - RAISE 'edit_producer must have exactly one row!'; + -- Set c_search to NULL and notify when + -- 1. A new VN entry is created + -- 2. The vn title/original/alias has changed + IF xtype = 'v' THEN + IF -- 1. + xoldchid IS NULL OR + -- 2. + EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE (v2.title <> v1.title OR v2.original <> v1.original OR v2.alias <> v1.alias) AND v1.chid = xoldchid AND v2.chid = xedit.chid) + THEN + UPDATE vn SET c_search = NULL WHERE id = xedit.itemid; + NOTIFY vnsearch; + END IF; END IF; - SELECT INTO r * FROM edit_commit(); - INSERT INTO producers_rev SELECT r.cid, r.iid, type, name, original, website, lang, "desc", alias, l_wp FROM edit_producer; - INSERT INTO producers_relations SELECT r.cid, pid, relation FROM edit_producer_relations; - UPDATE producers SET latest = r.cid WHERE id = r.iid; - RETURN r; -END; -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION edit_char_init(hid integer) RETURNS void AS $$ -BEGIN - BEGIN - CREATE TEMPORARY TABLE edit_char (LIKE chars_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_char DROP COLUMN id; - ALTER TABLE edit_char DROP COLUMN cid; - CREATE TEMPORARY TABLE edit_char_traits (LIKE chars_traits INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_char_traits DROP COLUMN cid; - CREATE TEMPORARY TABLE edit_char_vns (LIKE chars_vns INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_char_vns DROP COLUMN cid; - EXCEPTION WHEN duplicate_table THEN - TRUNCATE edit_char, edit_char_traits, edit_char_vns; - END; - PERFORM edit_revtable('c', hid); - -- new char - IF hid IS NULL THEN - INSERT INTO edit_char DEFAULT VALUES; - -- load revision - ELSE - INSERT INTO edit_char SELECT name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil FROM chars_rev WHERE id = hid; - INSERT INTO edit_char_traits SELECT tid, spoil FROM chars_traits WHERE cid = hid; - INSERT INTO edit_char_vns SELECT vid, rid, spoil, role FROM chars_vns WHERE cid = hid; + -- Set related vn.c_search columns to NULL and notify when + -- 1. A new release is created + -- 2. A release has been hidden or unhidden + -- 3. The release title/original has changed + -- 4. The releases_vn table differs from a previous revision + IF xtype = 'r' THEN + IF -- 1. + xoldchid IS NULL OR + -- 2. + EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = xedit.chid AND c2.id = xoldchid) OR + -- 3. + EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = xedit.chid) OR + -- 4. + EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid) OR + EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid) + THEN + UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid)); + NOTIFY vnsearch; + END IF; END IF; -END; -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION edit_char_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; -BEGIN - IF (SELECT COUNT(*) FROM edit_char) <> 1 THEN - RAISE 'edit_char must have exactly one row!'; + -- Call update_vncache() for related VNs when a release has been created or edited + -- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity) + IF xtype = 'r' THEN + PERFORM update_vncache(vid) FROM ( + SELECT DISTINCT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid) + ) AS v(vid); END IF; - SELECT INTO r * FROM edit_commit(); - INSERT INTO chars_rev SELECT r.cid, r.iid, name, original, alias, image, "desc", gender, s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt, main, main_spoil FROM edit_char; - INSERT INTO chars_traits SELECT r.cid, tid, spoil FROM edit_char_traits; - INSERT INTO chars_vns SELECT r.cid, vid, rid, spoil, role FROM edit_char_vns; - UPDATE chars SET latest = r.cid WHERE id = r.iid; - RETURN r; -END; -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION edit_staff_init(cid integer) RETURNS void AS $$ -BEGIN - BEGIN - CREATE TEMPORARY TABLE edit_staff (LIKE staff_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_staff DROP COLUMN id; - ALTER TABLE edit_staff DROP COLUMN sid; - CREATE TEMPORARY TABLE edit_staff_aliases (LIKE staff_alias INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_staff_aliases DROP COLUMN rid; - EXCEPTION WHEN duplicate_table THEN - TRUNCATE edit_staff, edit_staff_aliases; - END; - PERFORM edit_revtable('s', cid); - -- new staff member - IF cid IS NULL THEN - INSERT INTO edit_staff (aid) VALUES (0); - -- load revision - ELSE - INSERT INTO edit_staff SELECT aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM staff_rev WHERE id = cid; - INSERT INTO edit_staff_aliases SELECT id, name, original FROM staff_alias WHERE rid = cid; + -- Call notify_dbdel() if an entry has been deleted + -- Call notify_listdel() if a vn/release entry has been deleted + IF xoldchid IS NOT NULL + AND EXISTS(SELECT 1 FROM changes WHERE id = xoldchid AND NOT ihid) + AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND ihid) + THEN + PERFORM notify_dbdel(xtype, xedit); + IF xtype = 'v' OR xtype = 'r' THEN + PERFORM notify_listdel(xtype, xedit); + END IF; END IF; -END; -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION edit_staff_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; -BEGIN - IF (SELECT COUNT(*) FROM edit_staff) <> 1 THEN - RAISE 'edit_staff must have exactly one row!'; + -- Call notify_dbedit() if a non-hidden entry has been edited + IF xoldchid IS NOT NULL AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND NOT ihid) + THEN + PERFORM notify_dbedit(xtype, xedit); END IF; - SELECT INTO r * FROM edit_commit(); - INSERT INTO staff_alias (id, rid, name, original) - SELECT id, r.cid, name, original FROM edit_staff_aliases; - INSERT INTO staff_rev (id, sid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb) - SELECT r.cid, r.iid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM edit_staff; - UPDATE staff SET latest = r.cid WHERE id = r.iid; - RETURN r; END; $$ LANGUAGE plpgsql; - - ---------------------------------------------------------- -- trigger functions -- ---------------------------------------------------------- @@ -627,18 +465,17 @@ CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$ BEGIN -- 1. IF TG_TABLE_NAME = 'vnlists' THEN - DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT r.id + DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT rv.id -- fetch all related rows in rlists FROM releases_vn rv - JOIN releases r ON r.latest = rv.rid - JOIN rlists rl ON rl.rid = r.id + JOIN rlists rl ON rl.rid = rv.id WHERE rv.vid = OLD.vid AND rl.uid = OLD.uid -- and test for a corresponding row in vnlists AND NOT EXISTS( SELECT 1 FROM releases_vn rvi JOIN vnlists vl ON vl.vid = rvi.vid AND uid = OLD.uid - WHERE rvi.rid = r.latest + WHERE rvi.id = rv.id )); -- 2. @@ -646,15 +483,13 @@ BEGIN INSERT INTO vnlists (uid, vid) SELECT NEW.uid, rv.vid -- all VNs linked to the release FROM releases_vn rv - JOIN releases r ON rv.rid = r.latest - WHERE r.id = NEW.rid + WHERE rv.id = NEW.rid -- but only if there are no corresponding rows in vnlists yet AND NOT EXISTS( SELECT 1 FROM releases_vn rvi - JOIN releases ri ON rvi.rid = ri.latest JOIN vnlists vl ON vl.vid = rvi.vid - WHERE ri.id = NEW.rid AND vl.uid = NEW.uid + WHERE rvi.id = NEW.rid AND vl.uid = NEW.uid ); END IF; RETURN NULL; @@ -670,33 +505,16 @@ $$ LANGUAGE plpgsql; --- Update vn.rgraph column and send notify when a relation graph needs to be regenerated --- 1. NOTIFY is sent on VN edit or insert or change in vn.rgraph, when rgraph = NULL and entries in vn_relations --- vn.rgraph is set to NULL when: --- 2. UPDATE on vn where c_released or c_languages has changed --- 3. VN edit of which the title differs from previous revision --- 4. VN edit with items in vn_relations that differ from previous +-- 1. Send a notify when vn.rgraph is set to NULL, and there are related entries in vn_relations +-- 2. Set rgraph to NULL when c_languages or c_released has changed CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$ BEGIN - -- 1. - IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN - IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN + IF EXISTS(SELECT 1 FROM vn_relations WHERE id = NEW.id) THEN + -- 1. + IF NEW.rgraph IS NULL THEN NOTIFY relgraph; - END IF; - END IF; - IF NEW.rgraph IS NOT NULL THEN - IF - -- 2. - OLD.c_released IS DISTINCT FROM NEW.c_released - OR OLD.c_languages IS DISTINCT FROM NEW.c_languages - OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND ( - -- 3. - 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) - -- 4. (not-really-readable method of comparing two query results) - OR EXISTS(SELECT vid2, relation, official FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation, official FROM vn_relations WHERE vid1 = NEW.latest) - OR (SELECT COUNT(*) FROM vn_relations WHERE vid1 = OLD.latest) <> (SELECT COUNT(*) FROM vn_relations WHERE vid1 = NEW.latest) - ) - THEN + -- 2. + ELSE UPDATE vn SET rgraph = NULL WHERE id = NEW.id; END IF; END IF; @@ -705,30 +523,11 @@ END; $$ LANGUAGE plpgsql; - --- Same as above for producers, with slight differences in the steps: --- There is no 2, and --- 3 = Producer edit of which the name, language or type differs from the previous revision +-- Send a notify when producers.rgraph is set to NULL, and there are related entries in producers_relations CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$ BEGIN - -- 1. - IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN - IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN - NOTIFY relgraph; - END IF; - END IF; - IF NEW.rgraph IS NOT NULL THEN - -- 2. - IF OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND ( - -- 3. - EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest) - -- 4. (not-really-readable method of comparing two query results) - OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest) - OR (SELECT COUNT(*) FROM producers_relations WHERE pid1 = OLD.latest) <> (SELECT COUNT(*) FROM producers_relations WHERE pid1 = NEW.latest) - ) - THEN - UPDATE producers SET rgraph = NULL WHERE id = NEW.id; - END IF; + IF EXISTS(SELECT 1 FROM producers_relations WHERE id = NEW.id) THEN + NOTIFY relgraph; END IF; RETURN NULL; END; @@ -754,80 +553,16 @@ $$ LANGUAGE plpgsql; --- call update_vncache() when a release is added, edited, hidden or unhidden -CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$ -BEGIN - PERFORM update_vncache(vid) FROM ( - SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest - ) AS v(vid); - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - - --- update (vn|release|producer|char).(hidden|locked) on a new revision --- NOTE: this is a /before/ trigger, it modifies NEW -CREATE OR REPLACE FUNCTION update_hidlock() RETURNS trigger AS $$ -DECLARE - r record; -BEGIN - SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest; - NEW.hidden := r.ihid; - NEW.locked := r.ilock; - RETURN NEW; -END; -$$ LANGUAGE plpgsql; - - - --- Check for updates to vn.c_search --- 1. NOTIFY is sent when vn.c_search goes from non-NULL to NULL --- vn.c_search is set to NULL when: --- 2. VN add/edit of which the title/original/alias fields differ from previous revision --- 3. Release gets hidden or unhidden --- 4. Release add/edit of which the title/original/vn fields differ from the previous revision +-- Send a vnsearch notification when the c_search column is set to NULL. CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$ -BEGIN - IF TG_TABLE_NAME = 'vn' THEN - -- 1. - IF NEW.c_search IS NULL THEN - NOTIFY vnsearch; - -- 2. - ELSIF NEW.latest IS DISTINCT FROM OLD.latest THEN - IF EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 - WHERE v1.id = OLD.latest AND v2.id = NEW.latest - AND (v1.title IS DISTINCT FROM v2.title OR v1.original IS DISTINCT FROM v2.original OR v1.alias IS DISTINCT FROM v2.alias) - ) THEN - UPDATE vn SET c_search = NULL WHERE id = NEW.id; - END IF; - END IF; - ELSIF TG_TABLE_NAME = 'releases' THEN - -- 3. & 4. - IF NEW.hidden IS DISTINCT FROM OLD.hidden OR ( - NEW.latest IS DISTINCT FROM OLD.latest AND ( - EXISTS( - SELECT 1 FROM releases_rev r1, releases_rev r2 - WHERE r1.id = OLD.latest AND r2.id = NEW.latest - AND (r1.title IS DISTINCT FROM r2.title OR r1.original IS DISTINCT FROM r2.original) - ) - OR EXISTS(SELECT vid FROM releases_vn WHERE rid = OLD.latest EXCEPT SELECT vid FROM releases_vn WHERE rid = NEW.latest) - OR (SELECT COUNT(*) FROM releases_vn WHERE rid = OLD.latest) <> (SELECT COUNT(*) FROM releases_vn WHERE rid = NEW.latest) - )) THEN - UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest); - END IF; - END IF; - RETURN NULL; -END; + BEGIN NOTIFY vnsearch; RETURN NULL; END; $$ LANGUAGE plpgsql; - ---------------------------------------------------------- -- notification functions -- --- (these are, in fact, also triggers) -- ---------------------------------------------------------- @@ -854,107 +589,70 @@ END; $$ LANGUAGE plpgsql; --- called on UPDATE vn / producers / releases / chars when (NOT OLD.hidden AND NEW.hidden) -CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$ -BEGIN + +-- called when an entry has been deleted +CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT DISTINCT 'dbdel'::notification_ntype, - (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype, - h.requester, NEW.id, h2.rev, x.title, h2.requester - -- look for changes of the deleted entry - -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs + SELECT DISTINCT 'dbdel'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester FROM changes h - JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr - JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest - WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id - UNION SELECT rr.id, rr2.title FROM releases_rev rr - JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest - WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id - UNION SELECT pr.id, pr2.name FROM producers_rev pr - JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest - WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id - UNION SELECT cr.id, cr2.name FROM chars_rev cr - JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest - WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id - UNION SELECT sr.id, sa.name FROM staff_rev sr - JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id - WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id - ) x(id, title) ON h.id = x.id -- join info about the deletion itself - JOIN changes h2 ON h2.id = NEW.latest - WHERE h.requester <> 1 -- exclude Multi - -- exclude the user who deleted the entry - AND h.requester <> h2.requester; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; + JOIN changes h2 ON h2.id = xedit.chid + -- Fetch the latest name/title of the entry + -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs + JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid + UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid + UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid + UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid + UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid + ) x(title) ON true + WHERE h.type = xtype AND h.itemid = xedit.itemid + AND h.requester <> 1 -- exclude Multi + AND h.requester <> h2.requester; -- exclude the user who deleted the entry +$$ LANGUAGE sql; --- called on UPDATE vn / releases when (NOT OLD.hidden AND NEW.hidden) -CREATE OR REPLACE FUNCTION notify_listdel() RETURNS trigger AS $$ -BEGIN + +-- Called when a non-deleted item has been edited. +CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT DISTINCT 'dbedit'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester + FROM changes h + -- join info about the edit itself + JOIN changes h2 ON h2.id = xedit.chid + -- Fetch the latest name/title of the entry + JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid + UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid + UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid + UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid + UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid + ) x(title) ON true + WHERE h.type = xtype AND h.itemid = xedit.itemid + AND h.requester <> h2.requester -- exclude the user who edited the entry + -- exclude users who don't want this notify + AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit'); +$$ LANGUAGE sql; + + + +-- called when a VN/release entry has been deleted +CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT DISTINCT 'listdel'::notification_ntype, - (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' ELSE 'r' END)::notification_ltype, - u.uid, NEW.id, c.rev, x.title, c.requester + SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester -- look for users who should get this notify FROM ( - -- voted on the VN - SELECT uid FROM votes WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - -- VN in vnlist - UNION SELECT uid FROM vnlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - -- VN in wishlist - UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - -- release in release list - UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id + SELECT uid FROM votes WHERE xtype = 'v' AND vid = xedit.itemid + UNION SELECT uid FROM vnlists WHERE xtype = 'v' AND vid = xedit.itemid + UNION SELECT uid FROM wlists WHERE xtype = 'v' AND vid = xedit.itemid + UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid ) u -- fetch info about this edit - JOIN changes c ON c.id = NEW.latest + JOIN changes c ON c.id = xedit.chid JOIN ( - SELECT id, title FROM vn_rev WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id - ) x ON c.id = x.id + SELECT title FROM vn WHERE xtype = 'v' AND id = xedit.itemid + UNION SELECT title FROM releases WHERE xtype = 'r' AND id = xedit.itemid + ) x ON true WHERE c.requester <> u.uid; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - - --- called on UPDATE vn / producers / releases / chars when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) --- this trigger is very similar to notify_dbdel() -CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$ -BEGIN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT DISTINCT 'dbedit'::notification_ntype, - (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype, - h.requester, NEW.id, h2.rev, x.title, h2.requester - -- look for changes of the edited entry - FROM changes h - JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr - JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest - WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id - UNION SELECT rr.id, rr2.title FROM releases_rev rr - JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest - WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id - UNION SELECT pr.id, pr2.name FROM producers_rev pr - JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest - WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id - UNION SELECT cr.id, cr2.name FROM chars_rev cr - JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest - WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id - UNION SELECT sr.id, sa.name FROM staff_rev sr - JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id - WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id - ) x(id, title) ON h.id = x.id - -- join info about the deletion itself - JOIN changes h2 ON h2.id = NEW.latest - -- exclude the user who edited the entry - WHERE h.requester <> h2.requester - -- exclude users who don't want this notify - AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit'); - RETURN NULL; -END; -$$ LANGUAGE plpgsql; +$$ LANGUAGE sql; -- called on INSERT INTO threads_posts when (NEW.num = 1) diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 8dd960be..d2d11b64 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -1,3 +1,44 @@ +-- Convention for database items with version control: +-- +-- CREATE TABLE items ( -- dbentry_type=x +-- id SERIAL PRIMARY KEY, +-- locked boolean NOT NULL DEFAULT FALSE, +-- hidden boolean NOT NULL DEFAULT FALSE, +-- -- item-specific columns here +-- ); +-- CREATE TABLE items_hist ( -- History of the 'items' table +-- chid integer NOT NULL, -- references changes.id +-- -- item-specific columns here +-- ); +-- +-- The '-- dbentry_type=x' comment is required, and is used by +-- util/sqleditfunc.pl to generate the correct editing functions. The history +-- of the 'locked' and 'hidden' flags is recorded in the changes table. It's +-- possible for 'items' to have more item-specific columns than 'items_hist'. +-- Some columns are caches or otherwise autogenerated, and do not need to be +-- versioned. +-- +-- item-related tables work roughly the same: +-- +-- CREATE TABLE items_field ( +-- id integer, -- references items.id +-- -- field-specific columns here +-- ); +-- CREATE TABLE items_field_hist ( -- History of the 'items_field' table +-- chid integer, -- references changes.id +-- -- field-specific columns here +-- ); +-- +-- The changes and *_hist tables contain all the data. In a sense, the other +-- tables related to the item are just a cache/view into the latest versions. +-- All modifications to the item tables has to go through the edit_* functions +-- in func.sql, these are also responsible for keeping things synchronized. +-- +-- Note: Every CREATE TABLE clause and each column should be on a separate +-- line. This file is parsed by util/sqleditfunc.pl, and it doesn't implement a +-- full SQL query parser. + + -- affiliate_links CREATE TABLE affiliate_links ( id SERIAL PRIMARY KEY, @@ -26,29 +67,44 @@ CREATE TABLE anime ( -- changes CREATE TABLE changes ( - id SERIAL NOT NULL PRIMARY KEY, - type dbentry_type NOT NULL, - rev integer NOT NULL DEFAULT 1, - added timestamptz NOT NULL DEFAULT NOW(), - requester integer NOT NULL DEFAULT 0, - ip inet NOT NULL DEFAULT '0.0.0.0', - comments text NOT NULL DEFAULT '', - ihid boolean NOT NULL DEFAULT FALSE, - ilock boolean NOT NULL DEFAULT FALSE + id SERIAL PRIMARY KEY, + type dbentry_type NOT NULL, + itemid integer NOT NULL, + rev integer NOT NULL DEFAULT 1, + added timestamptz NOT NULL DEFAULT NOW(), + requester integer NOT NULL DEFAULT 0, + ip inet NOT NULL DEFAULT '0.0.0.0', + comments text NOT NULL DEFAULT '', + ihid boolean NOT NULL DEFAULT FALSE, + ilock boolean NOT NULL DEFAULT FALSE ); -- chars -CREATE TABLE chars ( - id SERIAL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE +CREATE TABLE chars ( -- dbentry_type=c + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + name varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + image integer NOT NULL DEFAULT 0, + "desc" text NOT NULL DEFAULT '', + gender gender NOT NULL DEFAULT 'unknown', + s_bust smallint NOT NULL DEFAULT 0, + s_waist smallint NOT NULL DEFAULT 0, + s_hip smallint NOT NULL DEFAULT 0, + b_month smallint NOT NULL DEFAULT 0, + b_day smallint NOT NULL DEFAULT 0, + height smallint NOT NULL DEFAULT 0, + weight smallint NOT NULL DEFAULT 0, + bloodt blood_type NOT NULL DEFAULT 'unknown', + main integer, -- chars.id + main_spoil smallint NOT NULL DEFAULT 0 ); --- chars_rev -CREATE TABLE chars_rev ( - id integer NOT NULL PRIMARY KEY, - cid integer NOT NULL, +-- chars_hist +CREATE TABLE chars_hist ( + chid integer NOT NULL PRIMARY KEY, name varchar(250) NOT NULL DEFAULT '', original varchar(250) NOT NULL DEFAULT '', alias varchar(500) NOT NULL DEFAULT '', @@ -63,25 +119,42 @@ CREATE TABLE chars_rev ( height smallint NOT NULL DEFAULT 0, weight smallint NOT NULL DEFAULT 0, bloodt blood_type NOT NULL DEFAULT 'unknown', - main integer, + main integer, -- chars.id main_spoil smallint NOT NULL DEFAULT 0 ); -- chars_traits CREATE TABLE chars_traits ( - cid integer NOT NULL, - tid integer NOT NULL, - spoil smallint NOT NULL DEFAULT 0, - PRIMARY KEY(cid, tid) + id integer NOT NULL, + tid integer NOT NULL, -- traits.id + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(id, tid) +); + +-- chars_traits_hist +CREATE TABLE chars_traits_hist ( + chid integer NOT NULL, + tid integer NOT NULL, -- traits.id + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(chid, tid) ); -- chars_vns CREATE TABLE chars_vns ( - cid integer NOT NULL, - vid integer NOT NULL, - rid integer NULL, - spoil smallint NOT NULL DEFAULT 0, - role char_role NOT NULL DEFAULT 'main' + id integer NOT NULL, + vid integer NOT NULL, -- vn.id + rid integer NULL, -- releases.id + spoil smallint NOT NULL DEFAULT 0, + role char_role NOT NULL DEFAULT 'main' +); + +-- chars_vns_hist +CREATE TABLE chars_vns_hist ( + chid integer NOT NULL, + vid integer NOT NULL, -- vn.id + rid integer NULL, -- releases.id + spoil smallint NOT NULL DEFAULT 0, + role char_role NOT NULL DEFAULT 'main' ); -- login_throttle @@ -105,34 +178,48 @@ CREATE TABLE notifications ( ); -- producers -CREATE TABLE producers ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE, - rgraph integer +CREATE TABLE producers ( -- dbentry_type=p + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + type producer_type NOT NULL DEFAULT 'co', + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + website varchar(250) NOT NULL DEFAULT '', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + l_wp varchar(150), + rgraph integer -- relgraphs.id +); + +-- producers_hist +CREATE TABLE producers_hist ( + chid integer NOT NULL PRIMARY KEY, + type producer_type NOT NULL DEFAULT 'co', + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + website varchar(250) NOT NULL DEFAULT '', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + l_wp varchar(150) ); -- producers_relations CREATE TABLE producers_relations ( - pid1 integer NOT NULL, - pid2 integer NOT NULL, - relation producer_relation NOT NULL, - PRIMARY KEY(pid1, pid2) + id integer NOT NULL, + pid integer NOT NULL, -- producers.id + relation producer_relation NOT NULL, + PRIMARY KEY(id, pid) ); --- producers_rev -CREATE TABLE producers_rev ( - id integer NOT NULL PRIMARY KEY, - pid integer NOT NULL DEFAULT 0, - type producer_type NOT NULL DEFAULT 'co', - name varchar(200) NOT NULL DEFAULT '', - original varchar(200) NOT NULL DEFAULT '', - website varchar(250) NOT NULL DEFAULT '', - lang language NOT NULL DEFAULT 'ja', - "desc" text NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '', - l_wp varchar(150) +-- producers_relations_hist +CREATE TABLE producers_relations_hist ( + chid integer NOT NULL, + pid integer NOT NULL, -- producers.id + relation producer_relation NOT NULL, + PRIMARY KEY(chid, pid) ); -- quotes @@ -143,72 +230,125 @@ CREATE TABLE quotes ( ); -- releases -CREATE TABLE releases ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE +CREATE TABLE releases ( -- dbentry_type=r + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + type release_type NOT NULL DEFAULT 'complete', + website varchar(250) NOT NULL DEFAULT '', + catalog varchar(50) NOT NULL DEFAULT '', + gtin bigint NOT NULL DEFAULT 0, + released integer NOT NULL DEFAULT 0, + notes text NOT NULL DEFAULT '', + minage smallint, + patch boolean NOT NULL DEFAULT FALSE, + freeware boolean NOT NULL DEFAULT FALSE, + doujin boolean NOT NULL DEFAULT FALSE, + resolution smallint NOT NULL DEFAULT 0, + voiced smallint NOT NULL DEFAULT 0, + ani_story smallint NOT NULL DEFAULT 0, + ani_ero smallint NOT NULL DEFAULT 0 +); + +-- releases_hist +CREATE TABLE releases_hist ( + chid integer NOT NULL PRIMARY KEY, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + type release_type NOT NULL DEFAULT 'complete', + website varchar(250) NOT NULL DEFAULT '', + catalog varchar(50) NOT NULL DEFAULT '', + gtin bigint NOT NULL DEFAULT 0, + released integer NOT NULL DEFAULT 0, + notes text NOT NULL DEFAULT '', + minage smallint, + patch boolean NOT NULL DEFAULT FALSE, + freeware boolean NOT NULL DEFAULT FALSE, + doujin boolean NOT NULL DEFAULT FALSE, + resolution smallint NOT NULL DEFAULT 0, + voiced smallint NOT NULL DEFAULT 0, + ani_story smallint NOT NULL DEFAULT 0, + ani_ero smallint NOT NULL DEFAULT 0 ); -- releases_lang CREATE TABLE releases_lang ( - rid integer NOT NULL, - lang language NOT NULL, - PRIMARY KEY(rid, lang) + id integer NOT NULL, + lang language NOT NULL, + PRIMARY KEY(id, lang) +); + +-- releases_lang_hist +CREATE TABLE releases_lang_hist ( + chid integer NOT NULL, + lang language NOT NULL, + PRIMARY KEY(chid, lang) ); -- releases_media CREATE TABLE releases_media ( - rid integer NOT NULL DEFAULT 0, - medium medium NOT NULL, - qty smallint NOT NULL DEFAULT 1, - PRIMARY KEY(rid, medium, qty) + id integer NOT NULL, + medium medium NOT NULL, + qty smallint NOT NULL DEFAULT 1, + PRIMARY KEY(id, medium, qty) +); + +-- releases_media_hist +CREATE TABLE releases_media_hist ( + chid integer NOT NULL, + medium medium NOT NULL, + qty smallint NOT NULL DEFAULT 1, + PRIMARY KEY(chid, medium, qty) ); -- releases_platforms CREATE TABLE releases_platforms ( - rid integer NOT NULL DEFAULT 0, - platform platform NOT NULL, - PRIMARY KEY(rid, platform) + id integer NOT NULL, + platform platform NOT NULL, + PRIMARY KEY(id, platform) +); + +-- releases_platforms_hist +CREATE TABLE releases_platforms_hist ( + chid integer NOT NULL, + platform platform NOT NULL, + PRIMARY KEY(chid, platform) ); -- releases_producers CREATE TABLE releases_producers ( - pid integer NOT NULL, - rid integer NOT NULL, - developer boolean NOT NULL DEFAULT FALSE, - publisher boolean NOT NULL DEFAULT TRUE, + id integer NOT NULL, + pid integer NOT NULL, -- producers.id + developer boolean NOT NULL DEFAULT FALSE, + publisher boolean NOT NULL DEFAULT TRUE, CHECK(developer OR publisher), - PRIMARY KEY(pid, rid) + PRIMARY KEY(id, pid) ); --- releases_rev -CREATE TABLE releases_rev ( - id integer NOT NULL PRIMARY KEY, - rid integer NOT NULL DEFAULT 0, - title varchar(250) NOT NULL DEFAULT '', - original varchar(250) NOT NULL DEFAULT '', - type release_type NOT NULL DEFAULT 'complete', - website varchar(250) NOT NULL DEFAULT '', - released integer NOT NULL DEFAULT 0, - notes text NOT NULL DEFAULT '', - minage smallint, - gtin bigint NOT NULL DEFAULT 0, - patch boolean NOT NULL DEFAULT FALSE, - catalog varchar(50) NOT NULL DEFAULT '', - resolution smallint NOT NULL DEFAULT 0, - voiced smallint NOT NULL DEFAULT 0, - freeware boolean NOT NULL DEFAULT FALSE, - doujin boolean NOT NULL DEFAULT FALSE, - ani_story smallint NOT NULL DEFAULT 0, - ani_ero smallint NOT NULL DEFAULT 0 +-- releases_producers_hist +CREATE TABLE releases_producers_hist ( + chid integer NOT NULL, + pid integer NOT NULL, -- producers.id + developer boolean NOT NULL DEFAULT FALSE, + publisher boolean NOT NULL DEFAULT TRUE, + CHECK(developer OR publisher), + PRIMARY KEY(chid, pid) ); -- releases_vn CREATE TABLE releases_vn ( - rid integer NOT NULL DEFAULT 0, - vid integer NOT NULL DEFAULT 0, - PRIMARY KEY(rid, vid) + id integer NOT NULL, + vid integer NOT NULL, -- vn.id + PRIMARY KEY(id, vid) +); + +-- releases_vn_hist +CREATE TABLE releases_vn_hist ( + chid integer NOT NULL, + vid integer NOT NULL, -- vn.id + PRIMARY KEY(chid, vid) ); -- relgraphs @@ -243,34 +383,48 @@ CREATE TABLE sessions ( ); -- staff -CREATE TABLE staff ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE +CREATE TABLE staff ( -- dbentry_type=s + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + aid integer NOT NULL DEFAULT 0, -- staff_alias.aid + gender gender NOT NULL DEFAULT 'unknown', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_site varchar(250) NOT NULL DEFAULT '', + l_twitter varchar(16) NOT NULL DEFAULT '', + l_anidb integer +); + +-- staff_hist +CREATE TABLE staff_hist ( + chid integer NOT NULL PRIMARY KEY, + aid integer NOT NULL DEFAULT 0, -- Can't refer to staff_alias.id, because the alias might have been deleted + gender gender NOT NULL DEFAULT 'unknown', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_site varchar(250) NOT NULL DEFAULT '', + l_twitter varchar(16) NOT NULL DEFAULT '', + l_anidb integer ); -- staff_alias CREATE TABLE staff_alias ( - id SERIAL NOT NULL, - rid integer, - name varchar(200) NOT NULL DEFAULT '', - original varchar(200) NOT NULL DEFAULT '', - PRIMARY KEY (id, rid) + id integer NOT NULL, + aid SERIAL PRIMARY KEY, -- Globally unique ID of this alias + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '' ); --- staff_rev -CREATE TABLE staff_rev ( - id integer NOT NULL PRIMARY KEY, - sid integer NOT NULL, - aid integer NOT NULL, - gender gender NOT NULL DEFAULT 'unknown', - lang language NOT NULL DEFAULT 'ja', - "desc" text NOT NULL DEFAULT '', - l_wp varchar(150) NOT NULL DEFAULT '', - l_site varchar(250) NOT NULL DEFAULT '', - l_twitter varchar(16) NOT NULL DEFAULT '', - l_anidb integer +-- staff_alias_hist +CREATE TABLE staff_alias_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + PRIMARY KEY(chid, aid) ); -- stats_cache @@ -397,9 +551,6 @@ CREATE TABLE users ( -- First 9 bytes: salt (ASCII) -- Latter 20 bytes: sha1(hex(token) + salt) -- 'token' is a sha1 digest obtained from random data. - -- * 41 bytes: sha256 password - -- First 9 bytes: salt (ASCII) - -- Latter 32 bytes: sha256(global_salt + password + salt) -- * 46 bytes: scrypt password -- 4 bytes: N (big endian) -- 1 byte: r @@ -426,79 +577,130 @@ CREATE TABLE users_prefs ( ); -- vn -CREATE TABLE vn ( - id SERIAL NOT NULL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0, - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE, - rgraph integer, +CREATE TABLE vn ( -- dbentry_type=v + id SERIAL PRIMARY KEY, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + length smallint NOT NULL DEFAULT 0, + img_nsfw boolean NOT NULL DEFAULT FALSE, + image integer NOT NULL DEFAULT 0, + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_encubed varchar(100) NOT NULL DEFAULT '', + l_renai varchar(100) NOT NULL DEFAULT '', + rgraph integer, -- relgraphs.id c_released integer NOT NULL DEFAULT 0, c_languages language[] NOT NULL DEFAULT '{}', + c_olang language[] NOT NULL DEFAULT '{}', c_platforms platform[] NOT NULL DEFAULT '{}', c_popularity real, - c_rating real, + c_rating real, c_votecount integer NOT NULL DEFAULT 0, - c_search text, - c_olang language[] NOT NULL DEFAULT '{}' + c_search text +); + +-- vn_hist +CREATE TABLE vn_hist ( + chid integer NOT NULL PRIMARY KEY, + title varchar(250) NOT NULL DEFAULT '', + original varchar(250) NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + length smallint NOT NULL DEFAULT 0, + img_nsfw boolean NOT NULL DEFAULT FALSE, + image integer NOT NULL DEFAULT 0, + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_encubed varchar(100) NOT NULL DEFAULT '', + l_renai varchar(100) NOT NULL DEFAULT '' ); -- vn_anime CREATE TABLE vn_anime ( - vid integer NOT NULL, - aid integer NOT NULL, - PRIMARY KEY(vid, aid) + id integer NOT NULL, + aid integer NOT NULL, -- anime.id + PRIMARY KEY(id, aid) +); + +-- vn_anime_hist +CREATE TABLE vn_anime_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- anime.id + PRIMARY KEY(chid, aid) ); -- vn_relations CREATE TABLE vn_relations ( - vid1 integer NOT NULL DEFAULT 0, - vid2 integer NOT NULL DEFAULT 0, - relation vn_relation NOT NULL, - official boolean NOT NULL DEFAULT TRUE, - PRIMARY KEY(vid1, vid2) + id integer NOT NULL, + vid integer NOT NULL, -- vn.id + relation vn_relation NOT NULL, + official boolean NOT NULL DEFAULT TRUE, + PRIMARY KEY(id, vid) ); --- vn_rev -CREATE TABLE vn_rev ( - id integer NOT NULL PRIMARY KEY, - vid integer NOT NULL DEFAULT 0, - title varchar(250) NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '', - img_nsfw boolean NOT NULL DEFAULT FALSE, - length smallint NOT NULL DEFAULT 0, - "desc" text NOT NULL DEFAULT '', - l_wp varchar(150) NOT NULL DEFAULT '', - image integer NOT NULL DEFAULT 0, - l_encubed varchar(100) NOT NULL DEFAULT '', - l_renai varchar(100) NOT NULL DEFAULT '', - original varchar(250) NOT NULL DEFAULT '' +-- vn_relations_hist +CREATE TABLE vn_relations_hist ( + chid integer NOT NULL, + vid integer NOT NULL, -- vn.id + relation vn_relation NOT NULL, + official boolean NOT NULL DEFAULT TRUE, + PRIMARY KEY(chid, vid) ); -- vn_screenshots CREATE TABLE vn_screenshots ( - vid integer NOT NULL DEFAULT 0, - scr integer NOT NULL DEFAULT 0, - nsfw boolean NOT NULL DEFAULT FALSE, - rid integer, - PRIMARY KEY(vid, scr) + id integer NOT NULL, + scr integer NOT NULL, -- screenshots.id + rid integer, -- releases.id (only NULL for old revisions, nowadays not allowed anymore) + nsfw boolean NOT NULL DEFAULT FALSE, + PRIMARY KEY(id, scr) +); + +-- vn_screenshots_hist +CREATE TABLE vn_screenshots_hist ( + chid integer NOT NULL, + scr integer NOT NULL, + rid integer, + nsfw boolean NOT NULL DEFAULT FALSE, + PRIMARY KEY(chid, scr) ); -- vn_seiyuu CREATE TABLE vn_seiyuu ( - vid integer NOT NULL, - aid integer NOT NULL, - cid integer NOT NULL, - note varchar(250) NOT NULL DEFAULT '', - PRIMARY KEY (vid, aid, cid) + id integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid + cid integer NOT NULL, -- chars.id + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (id, aid, cid) +); + +-- vn_seiyuu_hist +CREATE TABLE vn_seiyuu_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted + cid integer NOT NULL, -- chars.id + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (chid, aid, cid) ); -- vn_staff CREATE TABLE vn_staff ( - vid integer NOT NULL, - aid integer NOT NULL, - role credit_type NOT NULL DEFAULT 'staff', - note varchar(250) NOT NULL DEFAULT '', - PRIMARY KEY (vid, aid, role) + id integer NOT NULL, + aid integer NOT NULL, -- staff_alias.aid + role credit_type NOT NULL DEFAULT 'staff', + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (id, aid, role) +); + +-- vn_staff_hist +CREATE TABLE vn_staff_hist ( + chid integer NOT NULL, + aid integer NOT NULL, -- See note at vn_seiyuu_hist.aid + role credit_type NOT NULL DEFAULT 'staff', + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (chid, aid, role) ); -- vnlists diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index 4028e98e..7a144e1f 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -1,88 +1,117 @@ -ALTER TABLE affiliate_links ADD CONSTRAINT affiliate_links_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; -ALTER TABLE chars ADD CONSTRAINT chars_latest_fkey FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); -ALTER TABLE chars_rev ADD CONSTRAINT chars_rev_main_fkey FOREIGN KEY (main) REFERENCES chars (id); -ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_cid_fkey FOREIGN KEY (cid) REFERENCES chars_rev (id); -ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); -ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_cid_fkey FOREIGN KEY (cid) REFERENCES chars_rev (id); -ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT; -ALTER TABLE producers ADD CONSTRAINT producers_latest_fkey FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); -ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid1_fkey FOREIGN KEY (pid1) REFERENCES producers_rev (id); -ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid2_fkey FOREIGN KEY (pid2) REFERENCES producers (id); -ALTER TABLE producers_rev ADD CONSTRAINT producers_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE producers_rev ADD CONSTRAINT producers_rev_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); -ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE releases ADD CONSTRAINT releases_latest_fkey FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_media ADD CONSTRAINT releases_media_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); -ALTER TABLE releases_rev ADD CONSTRAINT releases_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE releases_rev ADD CONSTRAINT releases_rev_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_rid_fkey FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE staff ADD CONSTRAINT staff_latest_fkey FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_rid_fkey FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_sid_fkey FOREIGN KEY (sid) REFERENCES staff (id); -ALTER TABLE staff_rev ADD CONSTRAINT staff_rev_aid_fkey FOREIGN KEY (aid,id) REFERENCES staff_alias (id,rid); -ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; -ALTER TABLE tags_aliases ADD CONSTRAINT tags_aliases_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id); -ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id); -ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id); -ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id); -ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id); -ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; -ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id); -ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id); -ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id); -ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id); -ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); -ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); -ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); -ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE vn ADD CONSTRAINT vn_latest_fkey FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); -ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id); -ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id); -ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid1_fkey FOREIGN KEY (vid1) REFERENCES vn_rev (id); -ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid2_fkey FOREIGN KEY (vid2) REFERENCES vn (id); -ALTER TABLE vn_rev ADD CONSTRAINT vn_rev_id_fkey FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE vn_rev ADD CONSTRAINT vn_rev_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id); -ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id); -ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); -ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id); -ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_vid_fkey FOREIGN KEY (vid) REFERENCES vn_rev (id); -ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE affiliate_links ADD CONSTRAINT affiliate_links_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE chars ADD CONSTRAINT chars_main_fkey FOREIGN KEY (main) REFERENCES chars (id); +ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_main_fkey FOREIGN KEY (main) REFERENCES chars (id); +ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_id_fkey FOREIGN KEY (id) REFERENCES chars (id); +ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); +ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); +ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_id_fkey FOREIGN KEY (id) REFERENCES chars (id); +ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_media ADD CONSTRAINT releases_media_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_platforms_hist ADD CONSTRAINT releases_platforms_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_id_fkey FOREIGN KEY (id) REFERENCES releases (id); +ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE staff ADD CONSTRAINT staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_id_fkey FOREIGN KEY (id) REFERENCES staff (id); +ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE tags_aliases ADD CONSTRAINT tags_aliases_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id); +ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id); +ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id); +ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id); +ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); +ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE traits_chars ADD CONSTRAINT traits_chars_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id); +ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id); +ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES screenshots (id); +ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id); +ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id); CREATE INDEX affiliate_links_rid ON affiliate_links (rid) WHERE NOT hidden; +CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+ +CREATE INDEX chars_vns_vid ON chars_vns (vid); CREATE INDEX notifications_uid ON notifications (uid); -CREATE INDEX releases_producers_rid ON releases_producers (rid); +CREATE INDEX releases_producers_pid ON releases_producers (pid); CREATE INDEX releases_vn_vid ON releases_vn (vid); +CREATE INDEX staff_alias_id ON staff_alias (id); CREATE INDEX tags_vn_date ON tags_vn (date); +CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); +CREATE INDEX tags_vn_uid ON tags_vn (uid); CREATE INDEX tags_vn_vid ON tags_vn (vid); -CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg))); -CREATE INDEX vn_staff_vid ON vn_staff (vid); +CREATE INDEX threads_posts_date ON threads_posts (date); +CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg)); +CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats +CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+? +CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+? CREATE INDEX vn_staff_aid ON vn_staff (aid); -CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (cid, vid, COALESCE(rid, 0)); +CREATE INDEX votes_date ON votes (date desc); -- Mainly used on /v+ pages, other pages don't really need it +CREATE INDEX votes_uid ON votes (uid); +CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev); +CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0)); +CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0)); diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql index 6988a62a..962610a7 100644 --- a/util/sql/triggers.sql +++ b/util/sql/triggers.sql @@ -1,9 +1,3 @@ -CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER hidlock_update BEFORE UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); - CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); @@ -34,48 +28,21 @@ CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW - WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph - OR OLD.latest IS DISTINCT FROM NEW.latest - OR OLD.c_released IS DISTINCT FROM NEW.c_released - OR OLD.c_languages IS DISTINCT FROM NEW.c_languages + WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) + OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages)) ) EXECUTE PROCEDURE vn_relgraph_notify(); -CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW - WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph - OR OLD.latest IS DISTINCT FROM NEW.latest - ) EXECUTE PROCEDURE producer_relgraph_notify(); +CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify(); 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(); CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW - WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden) - EXECUTE PROCEDURE release_vncache_update(); - CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel(); -CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce(); -CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW - WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL - OR NEW.latest IS DISTINCT FROM OLD.latest - ) EXECUTE PROCEDURE vn_vnsearch_notify(); -CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW - WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest) - EXECUTE PROCEDURE vn_vnsearch_notify(); +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify(); CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist(); diff --git a/util/sqleditfunc.pl b/util/sqleditfunc.pl new file mode 100755 index 00000000..773214f9 --- /dev/null +++ b/util/sqleditfunc.pl @@ -0,0 +1,112 @@ +#!/usr/bin/perl + +use strict; +use warnings; +use List::Util 'any'; + +use Cwd 'abs_path'; +our $ROOT; +BEGIN { ($ROOT = abs_path $0) =~ s{/util/sqleditfunc\.pl$}{}; } + +my %tabletypes; # table_name => dbentry_type +my %tables; # table_name => [ column_names ] +my %items; # item_name => { tables_without_hist => [ data_column_names ] } + + +# Fills %tables +sub readschema { + open my $F, '<', "$ROOT/util/sql/schema.sql" or die $!; + my $table = ''; + while(<$F>) { + chomp; + if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) { + $table = $1; + $tables{$table} = []; + $tabletypes{$table} = $1 if /--.*\s+dbentry_type=(.)/; + } elsif($table && /^\s+("?[^\( ]+"?)\s/ && !/^\s+PRIMARY\s+KEY/) { + push @{$tables{$table}}, $1; + } + } +} + + +sub gensql { + my($template, $item) = @_; + + # table_name_without_hist => [ column_names_without_chid ] + my %ts = map +($_, [ grep !/^chid$/, @{$tables{"${_}_hist"}} ]), map /^${item}_/ && /^(.+)_hist$/ ? $1 : (), keys %tables; + + my %replace = ( item => $item, itemtype => $tabletypes{$item} ); + $replace{createtemptables} = join "\n", map sprintf( + " CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS);\n". + " ALTER TABLE edit_%1\$s DROP COLUMN %s;", + $_, $_ eq 'staff_alias' ? ($_, 'id') : ("${_}_hist", 'chid') # staff_alias copies from the non-_hist table, because it needs the sequence + ), sort keys %ts; + $replace{temptablenames} = join ', ', map "edit_$_", sort keys %ts; + $replace{loadtemptables} = join "\n", map sprintf( + " INSERT INTO edit_%s (%s) SELECT %2\$s FROM %1\$s_hist WHERE chid = xchid;", + $_, join ', ', @{$ts{$_}}), sort keys %ts; + $replace{copyfromtemp} = join "\n", map sprintf( + " DELETE FROM %1\$s WHERE id = r.itemid;\n". + " INSERT INTO %1\$s (id, %2\$s) SELECT r.itemid, %2\$s FROM edit_%1\$s;\n". + " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;", + $_, join ', ', @{$ts{$_}}), grep $_ ne $item, sort keys %ts; + $replace{copymainfromtemp} = sprintf + " INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;\n". + " UPDATE %1\$s SET locked = (SELECT ilock FROM edit_revision), hidden = (SELECT ihid FROM edit_revision),\n". + " %3\$s FROM edit_%1\$s x WHERE id = r.itemid;", + $item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}}); + + $template =~ s/{([a-z]+)}/$replace{$1}/g; + $template; +} + + +readschema; +my $template = join '', <DATA>; + +open my $F, '>', "$ROOT/util/sql/editfunc.sql" or die $!; +print $F "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n"; +print $F gensql $template, $_ for sort keys %tabletypes; + + +__DATA__ + +CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid integer, xrev integer) RETURNS void AS $$ +DECLARE + xchid integer; +BEGIN + -- create tables, based on existing tables + BEGIN +{createtemptables} + EXCEPTION WHEN duplicate_table THEN + TRUNCATE {temptablenames}; + END; + -- Create edit_revision table and get relevant change ID. + SELECT edit_revtable('{itemtype}', xid, xrev) INTO xchid; + -- new entry, load defaults + IF xchid IS NULL THEN + INSERT INTO edit_{item} DEFAULT VALUES; + -- otherwise, load revision + ELSE +{loadtemptables} + END IF; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION edit_{itemtype}_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; +BEGIN + IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN + RAISE 'edit_{item} must have exactly one row!'; + END IF; + SELECT INTO r * FROM edit_commit(); +{copyfromtemp} +{copymainfromtemp} + PERFORM edit_committed('{itemtype}', r); + RETURN r; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/updates/update_2.25-sqlsplit.sql b/util/updates/update_2.25-sqlsplit.sql new file mode 100644 index 00000000..b7ffa91f --- /dev/null +++ b/util/updates/update_2.25-sqlsplit.sql @@ -0,0 +1,258 @@ +-- Q: Why recreate all the tables rather than modify existing ones? +-- A: Because the production tables have been modified many times, and columns +-- weren't always in the same order as in scheme.sql. Recreating everything +-- also has the advantage of ensuring that all references and indices are +-- handled and documented here. In hindsight, it also seems like the easier +-- approach. + +ALTER TABLE changes RENAME TO changes_old; +ALTER TABLE chars RENAME TO chars_old; +ALTER TABLE chars_rev RENAME TO chars_rev_old; +ALTER TABLE chars_traits RENAME TO chars_traits_old; +ALTER TABLE chars_vns RENAME TO chars_vns_old; +ALTER TABLE producers RENAME TO producers_old; +ALTER TABLE producers_rev RENAME TO producers_rev_old; +ALTER TABLE producers_relations RENAME TO producers_relations_old; +ALTER TABLE releases RENAME TO releases_old; +ALTER TABLE releases_rev RENAME TO releases_rev_old; +ALTER TABLE releases_lang RENAME TO releases_lang_old; +ALTER TABLE releases_media RENAME TO releases_media_old; +ALTER TABLE releases_platforms RENAME TO releases_platforms_old; +ALTER TABLE releases_producers RENAME TO releases_producers_old; +ALTER TABLE releases_vn RENAME TO releases_vn_old; +ALTER TABLE staff RENAME TO staff_old; +ALTER TABLE staff_rev RENAME TO staff_rev_old; +ALTER TABLE staff_alias RENAME TO staff_alias_old; +ALTER TABLE vn RENAME TO vn_old; +ALTER TABLE vn_rev RENAME TO vn_rev_old; +ALTER TABLE vn_anime RENAME TO vn_anime_old; +ALTER TABLE vn_relations RENAME TO vn_relations_old; +ALTER TABLE vn_screenshots RENAME TO vn_screenshots_old; +ALTER TABLE vn_seiyuu RENAME TO vn_seiyuu_old; +ALTER TABLE vn_staff RENAME TO vn_staff_old; + +-- XXX: The names of these sequences depend on how the corresponding tables +-- were generated. The names below are the ones in the production database. +ALTER SEQUENCE changes_id_seq RENAME TO changes_id_seq_old; +ALTER SEQUENCE chars_id_seq RENAME TO chars_id_seq_old; +ALTER SEQUENCE producers_id_seq RENAME TO producers_id_seq_old; +ALTER SEQUENCE releases_id_seq RENAME TO releases_id_seq_old; +ALTER SEQUENCE staff_alias_id_seq RENAME TO staff_alias_id_seq_old; +ALTER SEQUENCE staff_id_seq RENAME TO staff_id_seq_old; +ALTER SEQUENCE vn_id_seq RENAME TO vn_id_seq_old; + +\i util/sql/schema.sql + + +-- XXX: This query uses a window function to generate changes.rev instead of +-- copying the value from the old table. This is done because, in the old +-- database schema, there was no uniqueness constraint on (type, itemid, rev), +-- and due to a race condition it was possible for duplicates to appear. This +-- is a pretty rare occurence, and easy to correct by renumbering the changes. +-- (Changes the URL of a few revision pages, but there's no way to avoid that) +INSERT INTO changes SELECT c.id, c.type, COALESCE(vr.vid, pr.pid, rr.rid, cr.cid, sr.sid), + row_number() OVER (PARTITION BY c.type, COALESCE(vr.vid, pr.pid, rr.rid, cr.cid, sr.sid) ORDER BY c.id ASC), + c.added, c.requester, c.ip, c.comments, c.ihid, c.ilock + FROM changes_old c + LEFT JOIN vn_rev_old vr ON vr.id = c.id + LEFT JOIN producers_rev_old pr ON pr.id = c.id + LEFT JOIN releases_rev_old rr ON rr.id = c.id + LEFT JOIN chars_rev_old cr ON cr.id = c.id + LEFT JOIN staff_rev_old sr ON sr.id = c.id; + +INSERT INTO chars SELECT c.id, c.locked, c.hidden, + cr.name, cr.original, cr.alias, cr.image, cr.desc, cr.gender, cr.s_bust, cr.s_waist, cr.s_hip, + cr.b_month, cr.b_day, cr.height, cr.weight, cr.bloodt, cr.main, cr.main_spoil + FROM chars_old c JOIN chars_rev_old cr ON cr.id = c.latest; + +INSERT INTO chars_hist SELECT cr.id, + cr.name, cr.original, cr.alias, cr.image, cr.desc, cr.gender, cr.s_bust, cr.s_waist, cr.s_hip, + cr.b_month, cr.b_day, cr.height, cr.weight, cr.bloodt, cr.main, cr.main_spoil + FROM chars_rev_old cr; + +INSERT INTO chars_traits SELECT c.id, ct.tid, ct.spoil + FROM chars_old c + JOIN chars_traits_old ct ON ct.cid = c.latest; + +INSERT INTO chars_traits_hist SELECT cid, tid, spoil + FROM chars_traits_old; + +INSERT INTO chars_vns SELECT c.id, cv.vid, cv.rid, cv.spoil, cv.role + FROM chars_old c + JOIN chars_vns_old cv ON cv.cid = c.latest; + +INSERT INTO chars_vns_hist SELECT cid, vid, rid, spoil, role + FROM chars_vns_old; + +INSERT INTO producers SELECT p.id, p.locked, p.hidden, + pr.type, pr.name, pr.original, pr.website, pr.lang, pr.desc, pr.alias, pr.l_wp, p.rgraph + FROM producers_old p JOIN producers_rev_old pr ON pr.id = p.latest; + +INSERT INTO producers_hist SELECT id, type, name, original, website, lang, "desc", alias, l_wp + FROM producers_rev_old; + +INSERT INTO producers_relations SELECT p.id, pr.pid2, pr.relation + FROM producers_old p + JOIN producers_relations_old pr ON p.latest = pr.pid1; + +INSERT INTO producers_relations_hist SELECT pid1, pid2, relation + FROM producers_relations_old; + +INSERT INTO releases SELECT r.id, r.locked, r.hidden, + rr.title, rr.original, rr.type, rr.website, rr.catalog, rr.gtin, rr.released, rr.notes, rr.minage, rr.patch, + rr.freeware, rr.doujin, rr.resolution, rr.voiced, rr.ani_story, rr.ani_ero + FROM releases_old r JOIN releases_rev_old rr ON rr.id = r.latest; + +INSERT INTO releases_hist SELECT rr.id, + rr.title, rr.original, rr.type, rr.website, rr.catalog, rr.gtin, rr.released, rr.notes, rr.minage, rr.patch, + rr.freeware, rr.doujin, rr.resolution, rr.voiced, rr.ani_story, rr.ani_ero + FROM releases_rev_old rr; + +INSERT INTO releases_lang SELECT r.id, rl.lang + FROM releases_old r JOIN releases_lang_old rl ON rl.rid = r.latest; + +INSERT INTO releases_lang_hist SELECT rl.rid, rl.lang + FROM releases_lang_old rl; + +INSERT INTO releases_media SELECT r.id, rm.medium, rm.qty + FROM releases_old r JOIN releases_media_old rm ON rm.rid = r.latest; + +INSERT INTO releases_media_hist SELECT rm.rid, rm.medium, rm.qty + FROM releases_media_old rm; + +INSERT INTO releases_platforms SELECT r.id, rp.platform + FROM releases_old r JOIN releases_platforms_old rp ON rp.rid = r.latest; + +INSERT INTO releases_platforms_hist SELECT rp.rid, rp.platform + FROM releases_platforms_old rp; + +INSERT INTO releases_producers SELECT r.id, rp.pid, rp.developer, rp.publisher + FROM releases_old r JOIN releases_producers_old rp ON rp.rid = r.latest; + +INSERT INTO releases_producers_hist SELECT rp.rid, rp.pid, rp.developer, rp.publisher + FROM releases_producers_old rp; + +INSERT INTO releases_vn SELECT r.id, rv.vid + FROM releases_old r JOIN releases_vn_old rv ON rv.rid = r.latest; + +INSERT INTO releases_vn_hist SELECT rv.rid, rv.vid + FROM releases_vn_old rv; + +INSERT INTO staff SELECT s.id, s.locked, s.hidden, + sr.aid, sr.gender, sr.lang, sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb + FROM staff_old s JOIN staff_rev_old sr ON sr.id = s.latest; + +INSERT INTO staff_hist SELECT sr.id, + sr.aid, sr.gender, sr.lang, sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb + FROM staff_rev_old sr; + +INSERT INTO staff_alias SELECT s.id, sa.id, sa.name, sa.original + FROM staff_old s JOIN staff_alias_old sa ON sa.rid = s.latest; + +INSERT INTO staff_alias_hist SELECT rid, id, name, original + FROM staff_alias_old; + +INSERT INTO vn SELECT v.id, v.locked, v.hidden, + vr.title, vr.original, vr.alias, vr.length, vr.img_nsfw, vr.image, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai, + v.rgraph, v.c_released, v.c_languages, v.c_olang, v.c_platforms, v.c_popularity, v.c_rating, v.c_votecount, v.c_search + FROM vn_old v JOIN vn_rev_old vr ON vr.id = v.latest; + +INSERT INTO vn_hist SELECT vr.id, + vr.title, vr.original, vr.alias, vr.length, vr.img_nsfw, vr.image, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai + FROM vn_rev_old vr; + +INSERT INTO vn_anime SELECT v.id, va.aid + FROM vn_old v JOIN vn_anime_old va ON va.vid = v.latest; + +INSERT INTO vn_anime_hist SELECT vid, aid + FROM vn_anime_old; + +INSERT INTO vn_relations SELECT v.id, vr.vid2, vr.relation, vr.official + FROM vn_old v JOIN vn_relations_old vr ON vr.vid1 = v.latest; + +INSERT INTO vn_relations_hist SELECT vid1, vid2, relation, official + FROM vn_relations_old; + +INSERT INTO vn_screenshots SELECT v.id, vs.scr, vs.rid, vs.nsfw + FROM vn_old v JOIN vn_screenshots_old vs ON vs.vid = v.latest; + +INSERT INTO vn_screenshots_hist SELECT vid, scr, rid, nsfw + FROM vn_screenshots_old; + +INSERT INTO vn_seiyuu SELECT v.id, vs.aid, vs.cid, vs.note + FROM vn_old v JOIN vn_seiyuu_old vs ON vs.vid = v.latest; + +INSERT INTO vn_seiyuu_hist SELECT vid, aid, cid, note + FROM vn_seiyuu_old; + +INSERT INTO vn_staff SELECT v.id, vs.aid, vs.role, vs.note + FROM vn_old v JOIN vn_staff_old vs ON vs.vid = v.latest; + +INSERT INTO vn_staff_hist SELECT vid, aid, role, note + FROM vn_staff_old; + + +SELECT setval('changes_id_seq', nextval('changes_id_seq_old')); +SELECT setval('chars_id_seq', nextval('chars_id_seq_old')); +SELECT setval('producers_id_seq', nextval('producers_id_seq_old')); +SELECT setval('releases_id_seq', nextval('releases_id_seq_old')); +SELECT setval('staff_alias_aid_seq', nextval('staff_alias_id_seq_old')); -- note the change from id to aid +SELECT setval('staff_id_seq', nextval('staff_id_seq_old')); +SELECT setval('vn_id_seq', nextval('vn_id_seq_old')); + + +-- Dropping all tables with CASCADE causes all foreign key references to and +-- from the tables to be dropped as well. This is exactly what we want, so we +-- can re-add the constraints on the newly created tables. +DROP TABLE changes_old CASCADE; +DROP TABLE chars_old CASCADE; +DROP TABLE chars_rev_old CASCADE; +DROP TABLE chars_traits_old CASCADE; +DROP TABLE chars_vns_old CASCADE; +DROP TABLE producers_old CASCADE; +DROP TABLE producers_rev_old CASCADE; +DROP TABLE producers_relations_old CASCADE; +DROP TABLE releases_old CASCADE; +DROP TABLE releases_rev_old CASCADE; +DROP TABLE releases_lang_old CASCADE; +DROP TABLE releases_media_old CASCADE; +DROP TABLE releases_platforms_old CASCADE; +DROP TABLE releases_producers_old CASCADE; +DROP TABLE releases_vn_old CASCADE; +DROP TABLE staff_old CASCADE; +DROP TABLE staff_rev_old CASCADE; +DROP TABLE staff_alias_old CASCADE; +DROP TABLE vn_old CASCADE; +DROP TABLE vn_rev_old CASCADE; +DROP TABLE vn_anime_old CASCADE; +DROP TABLE vn_relations_old CASCADE; +DROP TABLE vn_screenshots_old CASCADE; +DROP TABLE vn_seiyuu_old CASCADE; +DROP TABLE vn_staff_old CASCADE; + +DROP INDEX threads_posts_ts; + +DROP FUNCTION edit_revtable(dbentry_type, integer); +DROP FUNCTION edit_vn_init(integer); +DROP FUNCTION edit_vn_commit(); +DROP FUNCTION edit_release_init(integer); +DROP FUNCTION edit_release_commit(); +DROP FUNCTION edit_producer_init(integer); +DROP FUNCTION edit_producer_commit(); +DROP FUNCTION edit_char_init(integer); +DROP FUNCTION edit_char_commit(); +DROP FUNCTION edit_staff_init(integer); +DROP FUNCTION edit_staff_commit(); +DROP FUNCTION release_vncache_update(); +DROP FUNCTION notify_dbdel(); +DROP FUNCTION notify_dbedit(); +DROP FUNCTION notify_listdel(); +DROP FUNCTION update_hidlock(); + +DROP TYPE edit_rettype CASCADE; +CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer); + +\i util/sql/func.sql +\i util/sql/editfunc.sql +\i util/sql/tableattrs.sql +\i util/sql/triggers.sql diff --git a/util/updates/update_2.26.sql b/util/updates/update_2.26.sql new file mode 100644 index 00000000..6900d080 --- /dev/null +++ b/util/updates/update_2.26.sql @@ -0,0 +1,9 @@ +-- No more 'staffedit' permission flag +UPDATE users SET perm = (perm & ~8); + +-- Removed support for sha256-hashed passwords +UPDATE users SET passwd = '' WHERE length(passwd) = 41; + +-- Need to regenerate all relation graphs in the switch to HTML5 +UPDATE vn SET rgraph = NULL; +UPDATE producers SET rgraph = NULL; |