From 4531862cec3b982eb8e08e66b4ad8ea7987d4bc3 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 9 Jan 2010 11:21:23 +0100 Subject: SQL: Split dump.sql into several files and merged update_2.10.sql The functions can now be edited without having to repeat them in the update scripts. Just importing the func.sql file with \i will do the trick. --- util/dbgraph.pl | 8 +- util/dump.sql | 789 ------------------------------------------- util/sql/all.sql | 75 ++++ util/sql/func.sql | 542 +++++++++++++++++++++++++++++ util/sql/schema.sql | 388 +++++++++++++++++++++ util/updates/update_2.10.sql | 374 +------------------- 6 files changed, 1028 insertions(+), 1148 deletions(-) delete mode 100644 util/dump.sql create mode 100644 util/sql/all.sql create mode 100644 util/sql/func.sql create mode 100644 util/sql/schema.sql (limited to 'util') diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 0b72ceba..10297b66 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -2,7 +2,7 @@ # Generates a graphviz relation graph of the complete SQL database, -# information is parsed from dump.sql (has to be in the 'current directory'). +# information is parsed from sql/schema.sql # outputs the graph in dot format, usable as input to graphviz. # # Usage: @@ -15,6 +15,10 @@ use strict; use warnings; +use Cwd 'abs_path'; +(my $ROOT = abs_path $0) =~ s{/util/dbgraph\.pl$}{}; + + my %subgraphs = ( 'Producers' => [qw| FFFFCC producers producers_rev producers_relations |], 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |], @@ -29,7 +33,7 @@ my %tables; # table_name => [ [ col1, pri ], ... ] my @rel; # 'table:col -- table:col', ... sub parse_dump { - open my $R, '<', 'dump.sql' or die $!; + open my $R, '<', "$ROOT/util/sql/schema.sql" or die $!; my $in=''; while (<$R>) { chomp; diff --git a/util/dump.sql b/util/dump.sql deleted file mode 100644 index dc910a7f..00000000 --- a/util/dump.sql +++ /dev/null @@ -1,789 +0,0 @@ - --- plpgsql is required for our (trigger) functions -CREATE LANGUAGE plpgsql; - - --- data types - -CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); -CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p'); -CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); -CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); -CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); -CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); - - ------------------------------------------ --- T A B L E D E F I N I T I O N S -- ------------------------------------------ - - --- anime -CREATE TABLE anime ( - id integer NOT NULL PRIMARY KEY, - year smallint, - ann_id integer, - nfo_id varchar(200), - type anime_type, - title_romaji, - title_kanji, - lastfetch timestamptz -); - --- 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 '' -); - --- 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 -); - --- producers_relations -CREATE TABLE producers_relations ( - pid1 integer NOT NULL, - pid2 integer NOT NULL, - relation producer_relation NOT NULL, - PRIMARY KEY(pid1, pid2) -); - --- producers_rev -CREATE TABLE producers_rev ( - id integer NOT NULL PRIMARY KEY, - pid integer NOT NULL DEFAULT 0, - type character(2) NOT NULL DEFAULT 'co', - name varchar(200) NOT NULL DEFAULT '', - original varchar(200) NOT NULL DEFAULT '', - website varchar(250) NOT NULL DEFAULT '', - lang varchar NOT NULL DEFAULT 'ja', - "desc" text NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '', - l_wp varchar(150) -); - --- quotes -CREATE TABLE quotes ( - vid integer NOT NULL, - quote varchar(250) NOT NULL, - PRIMARY KEY(vid, quote) -); - --- 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 -); - --- releases_lang -CREATE TABLE releases_lang ( - rid integer NOT NULL, - lang varchar NOT NULL, - PRIMARY KEY(rid, 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) -); - --- releases_platforms -CREATE TABLE releases_platforms ( - rid integer NOT NULL DEFAULT 0, - platform character(3) NOT NULL DEFAULT 0, - PRIMARY KEY(rid, platform) -); - --- releases_producers -CREATE TABLE releases_producers ( - rid integer NOT NULL, - pid integer NOT NULL, - developer boolean NOT NULL DEFAULT FALSE, - publisher boolean NOT NULL DEFAULT TRUE, - CHECK(developer OR publisher), - PRIMARY KEY(pid, rid) -); - --- 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_vn -CREATE TABLE releases_vn ( - rid integer NOT NULL DEFAULT 0, - vid integer NOT NULL DEFAULT 0, - PRIMARY KEY(rid, vid) -); - --- relgraphs -CREATE TABLE relgraphs ( - id SERIAL PRIMARY KEY, - svg xml NOT NULL -); - --- rlists -CREATE TABLE rlists ( - uid integer NOT NULL DEFAULT 0, - rid integer NOT NULL DEFAULT 0, - vstat smallint NOT NULL DEFAULT 0, - rstat smallint NOT NULL DEFAULT 0, - added timestamptz NOT NULL DEFAULT NOW(), - PRIMARY KEY(uid, rid) -); - --- screenshots -CREATE TABLE screenshots ( - id SERIAL NOT NULL PRIMARY KEY, - processed boolean NOT NULL DEFAULT FALSE, - width smallint NOT NULL DEFAULT 0, - height smallint NOT NULL DEFAULT 0 -); - --- sessions -CREATE TABLE sessions ( - uid integer NOT NULL, - token bytea NOT NULL, - expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval), - PRIMARY KEY (uid, token) -); - --- stats_cache -CREATE TABLE stats_cache ( - section varchar(25) NOT NULL PRIMARY KEY, - count integer NOT NULL DEFAULT 0 -); - --- tags -CREATE TABLE tags ( - id SERIAL NOT NULL PRIMARY KEY, - name varchar(250) NOT NULL UNIQUE, - description text NOT NULL DEFAULT '', - meta boolean NOT NULL DEFAULT FALSE, - added timestamptz NOT NULL DEFAULT NOW(), - state smallint NOT NULL DEFAULT 0, - c_vns integer NOT NULL DEFAULT 0, - addedby integer NOT NULL DEFAULT 1 -); - --- tags_aliases -CREATE TABLE tags_aliases ( - alias varchar(250) NOT NULL PRIMARY KEY, - tag integer NOT NULL, -); - --- tags_parents -CREATE TABLE tags_parents ( - tag integer NOT NULL, - parent integer NOT NULL, - PRIMARY KEY(tag, parent) -); - --- tags_vn -CREATE TABLE tags_vn ( - tag integer NOT NULL, - vid integer NOT NULL, - uid integer NOT NULL, - vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), - spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), - PRIMARY KEY(tag, vid, uid) -); - --- tags_vn_inherit -CREATE TABLE tags_vn_inherit ( - tag integer NOT NULL, - vid integer NOT NULL, - users integer NOT NULL, - rating real NOT NULL, - spoiler smallint NOT NULL -); - --- threads -CREATE TABLE threads ( - id SERIAL NOT NULL PRIMARY KEY, - title varchar(50) NOT NULL DEFAULT '', - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE, - count smallint NOT NULL DEFAULT 0 -); - --- threads_posts -CREATE TABLE threads_posts ( - tid integer NOT NULL DEFAULT 0, - num smallint NOT NULL DEFAULT 0, - uid integer NOT NULL DEFAULT 0, - date timestamptz NOT NULL DEFAULT NOW(), - edited timestamptz, - msg text NOT NULL DEFAULT '', - hidden boolean NOT NULL DEFAULT FALSE, - PRIMARY KEY(tid, num) -); - --- threads_boards -CREATE TABLE threads_boards ( - tid integer NOT NULL DEFAULT 0, - type character(2) NOT NULL DEFAULT 0, - iid integer NOT NULL DEFAULT 0, - lastread smallint NOT NULL, - PRIMARY KEY(tid, type, iid) -); - --- users -CREATE TABLE users ( - id SERIAL NOT NULL PRIMARY KEY, - username varchar(20) NOT NULL UNIQUE, - mail varchar(100) NOT NULL, - rank smallint NOT NULL DEFAULT 3, - passwd bytea NOT NULL DEFAULT '', - registered timestamptz NOT NULL DEFAULT NOW(), - show_nsfw boolean NOT NULL DEFAULT FALSE, - show_list boolean NOT NULL DEFAULT TRUE, - c_votes integer NOT NULL DEFAULT 0, - c_changes integer NOT NULL DEFAULT 0, - skin varchar(128) NOT NULL DEFAULT '', - customcss text NOT NULL DEFAULT '', - ip inet NOT NULL DEFAULT '0.0.0.0', - c_tags integer NOT NULL DEFAULT 0, - salt character(9) NOT NULL DEFAULT '', - ign_votes voolean NOT NULL DEFAULT FALSE -); - --- 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, - c_released integer NOT NULL DEFAULT 0, - c_languages varchar(32) NOT NULL DEFAULT '', - c_platforms varchar(32) NOT NULL DEFAULT '', - c_popularity real, - c_rating real, - c_votecount integer NOT NULL DEFAULT 0 -); - --- vn_anime -CREATE TABLE vn_anime ( - vid integer NOT NULL, - aid integer NOT NULL, - PRIMARY KEY(vid, aid) -); - --- vn_relations -CREATE TABLE vn_relations ( - vid1 integer NOT NULL DEFAULT 0, - vid2 integer NOT NULL DEFAULT 0, - relation vn_relation NOT NULL, - PRIMARY KEY(vid1, vid2) -); - --- vn_rev -CREATE TABLE vn_rev ( - id integer NOT NULL PRIMARY KEY, - vid integer NOT NULL DEFAULT 0, - title varchar(250) NOT NULL DEFAULT '', - original 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 '', - l_vnn integer NOT NULL DEFAULT 0, - image integer NOT NULL DEFAULT 0, - l_encubed varchar(100) NOT NULL DEFAULT '', - l_renai varchar(100) NOT NULL DEFAULT '' -); - --- 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 DEFAULT NULL, - PRIMARY KEY(vid, scr) -); - --- votes -CREATE TABLE votes ( - vid integer NOT NULL DEFAULT 0, - uid integer NOT NULL DEFAULT 0, - vote integer NOT NULL DEFAULT 0, - date timestamptz NOT NULL DEFAULT NOW(), - PRIMARY KEY(vid, uid) -); - --- wlists -CREATE TABLE wlists ( - uid integer NOT NULL DEFAULT 0, - vid integer NOT NULL DEFAULT 0, - wstat smallint NOT NULL DEFAULT 0, - added timestamptz NOT NULL DEFAULT NOW(), - PRIMARY KEY(uid, vid) -); - - - - - ------------------------------------------------ --- F O R E I G N K E Y C H E C K I N G -- ------------------------------------------------ - - -ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); -ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); -ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id); -ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id); -ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); -ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); -ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id); -ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id); -ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id); -ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id); -ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id); -ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); -ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); -ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id); -ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); -ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id); -ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id); -ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id); -ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); -ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id); -ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id); -ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id); -ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); - - - - - - -------------------------- --- F U N C T I O N S -- -------------------------- - - --- update_vncache(id) - updates the 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 - ), 0), - c_languages = COALESCE(ARRAY_TO_STRING(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 - ), '/'), ''), - c_platforms = COALESCE(ARRAY_TO_STRING(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 - ), '/'), '') - WHERE id = $1; -$$ LANGUAGE sql; - - --- recalculate vn.c_popularity -CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$ -BEGIN - CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS - SELECT v.uid, v.vid, sqrt(count(*))::real - FROM votes v - JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote - JOIN users u ON u.id = v.uid AND NOT ign_votes - GROUP BY v.vid, v.uid; - CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS - SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; - UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id); - RETURN; -END; -$$ LANGUAGE plpgsql; - - --- recalculate tags_vn_inherit -CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ -BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; - TRUNCATE tags_vn_inherit; - -- populate tags_vn_inherit - INSERT INTO tags_vn_inherit - -- all votes for all tags, including votes inherited by child tags - -- (also includes meta tags, because they could have a normal tag as parent) - WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS ( - SELECT 15, tag, vid, uid, vote, spoiler, false - FROM tags_vn - UNION ALL - SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta - FROM tags_vn_all ta - JOIN tags_parents tp ON tp.tag = ta.tag - JOIN tags t ON t.id = tp.parent - WHERE t.state = 2 - AND ta.lvl > 0 - ) - -- grouped by (tag, vid) - SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating, - (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler - FROM ( - -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags) - SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real - FROM tags_vn_all - WHERE NOT meta - GROUP BY tag, vid, uid - ) AS t(tag, vid, uid, vote, spoiler) - GROUP BY tag, vid - HAVING AVG(vote) > 0; - -- recreate index - CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); - -- and update the VN count in the tags table - UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); - RETURN; -END; -$$ LANGUAGE plpgsql; -SELECT tag_vn_calc(); - - - - - ------------------------ --- T R I G G E R S -- ------------------------ - - --- keep the c_* columns in the users table up to date -CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$ -BEGIN - IF TG_TABLE_NAME = 'votes' THEN - IF TG_OP = 'INSERT' THEN - UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid; - ELSE - UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid; - END IF; - ELSIF TG_TABLE_NAME = 'changes' THEN - IF TG_OP = 'INSERT' THEN - UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester; - ELSE - UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester; - END IF; - ELSIF TG_TABLE_NAME = 'tags_vn' THEN - IF TG_OP = 'INSERT' THEN - UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid; - ELSE - UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid; - END IF; - END IF; - RETURN NULL; -END; -$$ LANGUAGE 'plpgsql'; - -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(); - - --- the stats_cache table -CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ -BEGIN - IF TG_OP = 'INSERT' THEN - IF TG_TABLE_NAME = 'users' THEN - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - ELSIF NEW.hidden = FALSE THEN - IF TG_TABLE_NAME = 'threads_posts' THEN - IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - END IF; - ELSE - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - END IF; - END IF; - - ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN - IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN - IF TG_TABLE_NAME = 'threads' THEN - UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; - END IF; - UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; - ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN - IF TG_TABLE_NAME = 'threads' THEN - UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; - END IF; - UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; - END IF; - - ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN - UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; - END IF; - RETURN NULL; -END; -$$ LANGUAGE 'plpgsql'; - -CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); -CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); - - --- insert rows into anime for new vn_anime.aid items -CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ -BEGIN - IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN - INSERT INTO anime (id) VALUES (NEW.aid); - END IF; - RETURN NEW; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); - - --- Send a notify whenever anime info should be fetched -CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$ -BEGIN - IF NEW.lastfetch IS NULL THEN - NOTIFY anime; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify(); - - --- Send a notify when a new cover image is uploaded -CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$ -BEGIN - IF NEW.image < 0 THEN - NOTIFY coverimage; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify(); - - --- Send a notify when a screenshot needs to be processed -CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$ -BEGIN - IF NEW.processed = FALSE THEN - NOTIFY screenshot; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify(); - - --- Update vn.rgraph column and send notify when a relation graph needs to be regenerated --- 1. NOTIFY is sent on 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 -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 - 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 FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation 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 - UPDATE vn SET rgraph = NULL WHERE id = NEW.id; - END IF; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); - - --- 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 -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; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify(); - - --- NOTIFY on insert into changes/posts/tags -CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ -BEGIN - IF TG_TABLE_NAME = 'changes' THEN - NOTIFY newrevision; - ELSIF TG_TABLE_NAME = 'threads_posts' THEN - NOTIFY newpost; - ELSIF TG_TABLE_NAME = 'tags' THEN - NOTIFY newtag; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); -CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); - - --- call update_vncache() when a release is added, edited, hidden or unhidden -CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$ -BEGIN - IF OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden THEN - PERFORM update_vncache(vid) FROM ( - SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest - ) AS v(vid); - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); - - - - - ---------------------------------- --- M I S C E L L A N E O U S -- ---------------------------------- - - --- Sequences used for ID generation of items not in the DB -CREATE SEQUENCE covers_seq; - - --- Rows that are assumed to be available -INSERT INTO users (id, username, mail, rank) - VALUES (0, 'deleted', 'del@vndb.org', 0); -INSERT INTO users (username, mail, rank) - VALUES ('multi', 'multi@vndb.org', 0); - -INSERT INTO stats_cache (section, count) VALUES - ('users', 1), - ('vn', 0), - ('producers', 0), - ('releases', 0), - ('threads', 0), - ('threads_posts', 0); - diff --git a/util/sql/all.sql b/util/sql/all.sql new file mode 100644 index 00000000..04e1dbf9 --- /dev/null +++ b/util/sql/all.sql @@ -0,0 +1,75 @@ +-- NOTE: Make sure you're cd'ed in the vndb root directory before running this script + +-- plpgsql is required for our (trigger) functions +CREATE LANGUAGE plpgsql; + + +-- data types + +CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); +CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p'); +CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); +CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); +CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); +CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); +CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); + + +-- schema + +\i util/sql/schema.sql + + +-- functions + +\i util/sql/func.sql + + +-- triggers + +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(); + +CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); + +CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); + +CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify(); + +CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify(); + +CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify(); + +CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); + +CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW 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 release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); + + +-- Sequences used for ID generation of items not in the DB +CREATE SEQUENCE covers_seq; + + +-- Rows that are assumed to be available +INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0); +INSERT INTO users (username, mail, rank) VALUES ('multi', 'multi@vndb.org', 0); + +INSERT INTO stats_cache (section, count) VALUES + ('users', 1), + ('vn', 0), + ('producers', 0), + ('releases', 0), + ('threads', 0), + ('threads_posts', 0); + diff --git a/util/sql/func.sql b/util/sql/func.sql new file mode 100644 index 00000000..ff38f02d --- /dev/null +++ b/util/sql/func.sql @@ -0,0 +1,542 @@ + + +-- update_vncache(id) - updates the 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 + ), 0), + c_languages = COALESCE(ARRAY_TO_STRING(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 + ), '/'), ''), + c_platforms = COALESCE(ARRAY_TO_STRING(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 + ), '/'), '') + WHERE id = $1; +$$ LANGUAGE sql; + + + +-- recalculate vn.c_popularity +CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$ +BEGIN + CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS + SELECT v.uid, v.vid, sqrt(count(*))::real + FROM votes v + JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote + JOIN users u ON u.id = v.uid AND NOT ign_votes + GROUP BY v.vid, v.uid; + CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS + SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; + UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id); + RETURN; +END; +$$ LANGUAGE plpgsql; + + + +-- recalculate tags_vn_inherit +CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ +BEGIN + DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; + TRUNCATE tags_vn_inherit; + -- populate tags_vn_inherit + INSERT INTO tags_vn_inherit + -- all votes for all tags, including votes inherited by child tags + -- (also includes meta tags, because they could have a normal tag as parent) + WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS ( + SELECT 15, tag, vid, uid, vote, spoiler, false + FROM tags_vn + UNION ALL + SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta + FROM tags_vn_all ta + JOIN tags_parents tp ON tp.tag = ta.tag + JOIN tags t ON t.id = tp.parent + WHERE t.state = 2 + AND ta.lvl > 0 + ) + -- grouped by (tag, vid) + SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating, + (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler + FROM ( + -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags) + SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real + FROM tags_vn_all + WHERE NOT meta + GROUP BY tag, vid, uid + ) AS t(tag, vid, uid, vote, spoiler) + GROUP BY tag, vid + HAVING AVG(vote) > 0; + -- recreate index + CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); + -- and update the VN count in the tags table + UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); + RETURN; +END; +$$ LANGUAGE plpgsql; + + + + + +---------------------------------------------------------- +-- revision insertion abstraction -- +---------------------------------------------------------- + + +-- 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 $$ +BEGIN + CREATE TEMPORARY TABLE edit_revision ( + type dbentry_type NOT NULL, + iid integer, + requester integer, + ip inet, + comments text + ); + INSERT INTO edit_revision (type, iid) VALUES (t, i); +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; + t dbentry_type; + i integer; +BEGIN + SELECT type INTO t FROM edit_revision; + SELECT iid INTO i 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 + LEFT JOIN vn_rev vr ON c.id = vr.id + LEFT JOIN releases_rev rr ON c.id = rr.id + LEFT JOIN producers_rev pr ON c.id = pr.id + WHERE (t = 'v' AND vr.vid = i) + OR (t = 'r' AND rr.rid = i) + OR (t = 'p' AND pr.pid = i) + ORDER BY c.id DESC + LIMIT 1; + END IF; + -- insert change + INSERT INTO changes (type, requester, ip, comments, rev) + SELECT t, requester, ip, comments, r.rev + FROM edit_revision + RETURNING id INTO r.cid; + -- 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; + 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 $$ +BEGIN + -- create tables, based on existing tables (so that the column types are always synchronised) + 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; + -- new VN, load defaults + IF cid IS NULL THEN + PERFORM edit_revtable('v', NULL); + INSERT INTO edit_vn DEFAULT VALUES; + -- otherwise, load revision + ELSE + PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid)); + INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, 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 FROM vn_relations WHERE vid1 = cid; + INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots 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, l_vnn, 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 FROM edit_vn_relations; + INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots; + UPDATE vn SET latest = r.cid WHERE id = r.iid; + DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; + RETURN r; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$ +BEGIN + -- temp. tables + 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; + -- new release + IF cid IS NULL THEN + PERFORM edit_revtable('r', NULL); + INSERT INTO edit_release DEFAULT VALUES; + -- load revision + ELSE + PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid)); + 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; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION edit_release_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; +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!'; + 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; + DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; + RETURN r; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$ +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; + -- new producer + IF cid IS NULL THEN + PERFORM edit_revtable('p', NULL); + INSERT INTO edit_producer DEFAULT VALUES; + -- load revision + ELSE + PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid)); + 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; + 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!'; + 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; + DROP TABLE edit_revision, edit_producer, edit_producer_relations; + RETURN r; +END; +$$ LANGUAGE plpgsql; + + + + + +---------------------------------------------------------- +-- trigger functions -- +---------------------------------------------------------- + + +-- keep the c_* columns in the users table up to date +CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$ +BEGIN + IF TG_TABLE_NAME = 'votes' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid; + ELSE + UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid; + END IF; + ELSIF TG_TABLE_NAME = 'changes' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester; + ELSE + UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester; + END IF; + ELSIF TG_TABLE_NAME = 'tags_vn' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid; + ELSE + UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + + + +-- the stats_cache table +CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + IF TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF NEW.hidden = FALSE THEN + IF TG_TABLE_NAME = 'threads_posts' THEN + IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + ELSE + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + END IF; + END IF; + + ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN + IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME; + ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN + IF TG_TABLE_NAME = 'threads' THEN + UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; + END IF; + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + + ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN + UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + + + +-- insert rows into anime for new vn_anime.aid items +CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ +BEGIN + IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN + INSERT INTO anime (id) VALUES (NEW.aid); + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + + + +-- Send a notify whenever anime info should be fetched +CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.lastfetch IS NULL THEN + NOTIFY anime; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + + +-- Send a notify when a new cover image is uploaded +CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.image < 0 THEN + NOTIFY coverimage; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + + +-- Send a notify when a screenshot needs to be processed +CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$ +BEGIN + IF NEW.processed = FALSE THEN + NOTIFY screenshot; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + + +-- 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 +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 + 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 FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation 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 + UPDATE vn SET rgraph = NULL WHERE id = NEW.id; + END IF; + END IF; + RETURN NULL; +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 +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; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + + +-- NOTIFY on insert into changes/posts/tags +CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$ +BEGIN + IF TG_TABLE_NAME = 'changes' THEN + NOTIFY newrevision; + ELSIF TG_TABLE_NAME = 'threads_posts' THEN + NOTIFY newpost; + ELSIF TG_TABLE_NAME = 'tags' THEN + NOTIFY newtag; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + + +-- call update_vncache() when a release is added, edited, hidden or unhidden +CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$ +BEGIN + IF OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden THEN + PERFORM update_vncache(vid) FROM ( + SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest + ) AS v(vid); + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/sql/schema.sql b/util/sql/schema.sql new file mode 100644 index 00000000..d5286a94 --- /dev/null +++ b/util/sql/schema.sql @@ -0,0 +1,388 @@ + + +-- anime +CREATE TABLE anime ( + id integer NOT NULL PRIMARY KEY, + year smallint, + ann_id integer, + nfo_id varchar(200), + type anime_type, + title_romaji, + title_kanji, + lastfetch timestamptz +); + +-- 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 '' +); + +-- 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 +); + +-- producers_relations +CREATE TABLE producers_relations ( + pid1 integer NOT NULL, + pid2 integer NOT NULL, + relation producer_relation NOT NULL, + PRIMARY KEY(pid1, pid2) +); + +-- producers_rev +CREATE TABLE producers_rev ( + id integer NOT NULL PRIMARY KEY, + pid integer NOT NULL DEFAULT 0, + type character(2) NOT NULL DEFAULT 'co', + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + website varchar(250) NOT NULL DEFAULT '', + lang varchar NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + alias varchar(500) NOT NULL DEFAULT '', + l_wp varchar(150) +); + +-- quotes +CREATE TABLE quotes ( + vid integer NOT NULL, + quote varchar(250) NOT NULL, + PRIMARY KEY(vid, quote) +); + +-- 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 +); + +-- releases_lang +CREATE TABLE releases_lang ( + rid integer NOT NULL, + lang varchar NOT NULL, + PRIMARY KEY(rid, 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) +); + +-- releases_platforms +CREATE TABLE releases_platforms ( + rid integer NOT NULL DEFAULT 0, + platform character(3) NOT NULL DEFAULT 0, + PRIMARY KEY(rid, platform) +); + +-- releases_producers +CREATE TABLE releases_producers ( + rid integer NOT NULL, + pid integer NOT NULL, + developer boolean NOT NULL DEFAULT FALSE, + publisher boolean NOT NULL DEFAULT TRUE, + CHECK(developer OR publisher), + PRIMARY KEY(pid, rid) +); + +-- 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_vn +CREATE TABLE releases_vn ( + rid integer NOT NULL DEFAULT 0, + vid integer NOT NULL DEFAULT 0, + PRIMARY KEY(rid, vid) +); + +-- relgraphs +CREATE TABLE relgraphs ( + id SERIAL PRIMARY KEY, + svg xml NOT NULL +); + +-- rlists +CREATE TABLE rlists ( + uid integer NOT NULL DEFAULT 0, + rid integer NOT NULL DEFAULT 0, + vstat smallint NOT NULL DEFAULT 0, + rstat smallint NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT NOW(), + PRIMARY KEY(uid, rid) +); + +-- screenshots +CREATE TABLE screenshots ( + id SERIAL NOT NULL PRIMARY KEY, + processed boolean NOT NULL DEFAULT FALSE, + width smallint NOT NULL DEFAULT 0, + height smallint NOT NULL DEFAULT 0 +); + +-- sessions +CREATE TABLE sessions ( + uid integer NOT NULL, + token bytea NOT NULL, + expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval), + PRIMARY KEY (uid, token) +); + +-- stats_cache +CREATE TABLE stats_cache ( + section varchar(25) NOT NULL PRIMARY KEY, + count integer NOT NULL DEFAULT 0 +); + +-- tags +CREATE TABLE tags ( + id SERIAL NOT NULL PRIMARY KEY, + name varchar(250) NOT NULL UNIQUE, + description text NOT NULL DEFAULT '', + meta boolean NOT NULL DEFAULT FALSE, + added timestamptz NOT NULL DEFAULT NOW(), + state smallint NOT NULL DEFAULT 0, + c_vns integer NOT NULL DEFAULT 0, + addedby integer NOT NULL DEFAULT 1 +); + +-- tags_aliases +CREATE TABLE tags_aliases ( + alias varchar(250) NOT NULL PRIMARY KEY, + tag integer NOT NULL, +); + +-- tags_parents +CREATE TABLE tags_parents ( + tag integer NOT NULL, + parent integer NOT NULL, + PRIMARY KEY(tag, parent) +); + +-- tags_vn +CREATE TABLE tags_vn ( + tag integer NOT NULL, + vid integer NOT NULL, + uid integer NOT NULL, + vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), + spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), + PRIMARY KEY(tag, vid, uid) +); + +-- tags_vn_inherit +CREATE TABLE tags_vn_inherit ( + tag integer NOT NULL, + vid integer NOT NULL, + users integer NOT NULL, + rating real NOT NULL, + spoiler smallint NOT NULL +); + +-- threads +CREATE TABLE threads ( + id SERIAL NOT NULL PRIMARY KEY, + title varchar(50) NOT NULL DEFAULT '', + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE, + count smallint NOT NULL DEFAULT 0 +); + +-- threads_posts +CREATE TABLE threads_posts ( + tid integer NOT NULL DEFAULT 0, + num smallint NOT NULL DEFAULT 0, + uid integer NOT NULL DEFAULT 0, + date timestamptz NOT NULL DEFAULT NOW(), + edited timestamptz, + msg text NOT NULL DEFAULT '', + hidden boolean NOT NULL DEFAULT FALSE, + PRIMARY KEY(tid, num) +); + +-- threads_boards +CREATE TABLE threads_boards ( + tid integer NOT NULL DEFAULT 0, + type character(2) NOT NULL DEFAULT 0, + iid integer NOT NULL DEFAULT 0, + lastread smallint NOT NULL, + PRIMARY KEY(tid, type, iid) +); + +-- users +CREATE TABLE users ( + id SERIAL NOT NULL PRIMARY KEY, + username varchar(20) NOT NULL UNIQUE, + mail varchar(100) NOT NULL, + rank smallint NOT NULL DEFAULT 3, + passwd bytea NOT NULL DEFAULT '', + registered timestamptz NOT NULL DEFAULT NOW(), + show_nsfw boolean NOT NULL DEFAULT FALSE, + show_list boolean NOT NULL DEFAULT TRUE, + c_votes integer NOT NULL DEFAULT 0, + c_changes integer NOT NULL DEFAULT 0, + skin varchar(128) NOT NULL DEFAULT '', + customcss text NOT NULL DEFAULT '', + ip inet NOT NULL DEFAULT '0.0.0.0', + c_tags integer NOT NULL DEFAULT 0, + salt character(9) NOT NULL DEFAULT '', + ign_votes voolean NOT NULL DEFAULT FALSE +); + +-- 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, + c_released integer NOT NULL DEFAULT 0, + c_languages varchar(32) NOT NULL DEFAULT '', + c_platforms varchar(32) NOT NULL DEFAULT '', + c_popularity real, + c_rating real, + c_votecount integer NOT NULL DEFAULT 0 +); + +-- vn_anime +CREATE TABLE vn_anime ( + vid integer NOT NULL, + aid integer NOT NULL, + PRIMARY KEY(vid, aid) +); + +-- vn_relations +CREATE TABLE vn_relations ( + vid1 integer NOT NULL DEFAULT 0, + vid2 integer NOT NULL DEFAULT 0, + relation vn_relation NOT NULL, + PRIMARY KEY(vid1, vid2) +); + +-- vn_rev +CREATE TABLE vn_rev ( + id integer NOT NULL PRIMARY KEY, + vid integer NOT NULL DEFAULT 0, + title varchar(250) NOT NULL DEFAULT '', + original 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 '', + l_vnn integer NOT NULL DEFAULT 0, + image integer NOT NULL DEFAULT 0, + l_encubed varchar(100) NOT NULL DEFAULT '', + l_renai varchar(100) NOT NULL DEFAULT '' +); + +-- 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 DEFAULT NULL, + PRIMARY KEY(vid, scr) +); + +-- votes +CREATE TABLE votes ( + vid integer NOT NULL DEFAULT 0, + uid integer NOT NULL DEFAULT 0, + vote integer NOT NULL DEFAULT 0, + date timestamptz NOT NULL DEFAULT NOW(), + PRIMARY KEY(vid, uid) +); + +-- wlists +CREATE TABLE wlists ( + uid integer NOT NULL DEFAULT 0, + vid integer NOT NULL DEFAULT 0, + wstat smallint NOT NULL DEFAULT 0, + added timestamptz NOT NULL DEFAULT NOW(), + PRIMARY KEY(uid, vid) +); + + + +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); +ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); +ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id); +ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id); +ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id); +ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); +ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id); +ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id); +ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id); +ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id); +ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id); +ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id); +ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); + diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index 2219bb57..e54e028c 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -10,175 +10,12 @@ CREATE TABLE tags_vn_inherit ( ); --- more efficient version of tag_vn_calc() -CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ -BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; - TRUNCATE tags_vn_inherit; - -- populate tags_vn_inherit - INSERT INTO tags_vn_inherit - -- all votes for all tags, including votes inherited by child tags - -- (also includes meta tags, because they could have a normal tag as parent) - WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS ( - SELECT 15, tag, vid, uid, vote, spoiler, false - FROM tags_vn - UNION ALL - SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta - FROM tags_vn_all ta - JOIN tags_parents tp ON tp.tag = ta.tag - JOIN tags t ON t.id = tp.parent - WHERE t.state = 2 - AND ta.lvl > 0 - ) - -- grouped by (tag, vid) - SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating, - (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler - FROM ( - -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags) - SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real - FROM tags_vn_all - WHERE NOT meta - GROUP BY tag, vid, uid - ) AS t(tag, vid, uid, vote, spoiler) - GROUP BY tag, vid - HAVING AVG(vote) > 0; - -- recreate index - CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); - -- and update the VN count in the tags table - UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); - RETURN; -END; -$$ LANGUAGE plpgsql; -SELECT tag_vn_calc(); - - -- remove unused functions DROP FUNCTION tag_vn_childs() CASCADE; DROP FUNCTION tag_tree(integer, integer, boolean); DROP TYPE tag_tree_item; - --- improved relgraph notify triggers -DROP TRIGGER vn_relgraph_notify ON vn; -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 - 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 FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation 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 - UPDATE vn SET rgraph = NULL WHERE id = NEW.id; - END IF; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; -CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); - - -DROP TRIGGER vn_relgraph_notify ON producers; -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; - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify(); - - --- don't allow vid=0 for update_vncache -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 - ), 0), - c_languages = COALESCE(ARRAY_TO_STRING(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 - ), '/'), ''), - c_platforms = COALESCE(ARRAY_TO_STRING(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 - ), '/'), '') - WHERE id = $1; -$$ LANGUAGE sql; - - --- call update_vncache() when a release is added, edited, hidden or unhidden -CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$ -BEGIN - IF OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden THEN - PERFORM update_vncache(vid) FROM ( - SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest - ) AS v(vid); - END IF; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); - - -- remove changes.causedby and give the affected changes to Multi UPDATE changes SET requester = 1 WHERE causedby IS NOT NULL; ALTER TABLE changes DROP COLUMN causedby; @@ -194,210 +31,33 @@ UPDATE users SET -- set default on releases_rev.released, required for the revision insertion abstraction ALTER TABLE releases_rev ALTER COLUMN released SET DEFAULT 0; --- revision insertion abstraction --- IMPORTANT: these functions will need to be updated on each change in the DB structure --- of the relevant tables - -CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); - --- create temporary table for generic revision info -CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$ -BEGIN - CREATE TEMPORARY TABLE edit_revision ( - type dbentry_type NOT NULL, - iid integer, - requester integer, - ip inet, - comments text - ); - INSERT INTO edit_revision (type, iid) VALUES (t, i); -END; -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; - t dbentry_type; - i integer; -BEGIN - SELECT type INTO t FROM edit_revision; - SELECT iid INTO i 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 - LEFT JOIN vn_rev vr ON c.id = vr.id - LEFT JOIN releases_rev rr ON c.id = rr.id - LEFT JOIN producers_rev pr ON c.id = pr.id - WHERE (t = 'v' AND vr.vid = i) - OR (t = 'r' AND rr.rid = i) - OR (t = 'p' AND pr.pid = i) - ORDER BY c.id DESC - LIMIT 1; - END IF; - -- insert change - INSERT INTO changes (type, requester, ip, comments, rev) - SELECT t, requester, ip, comments, r.rev - FROM edit_revision - RETURNING id INTO r.cid; - -- 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; - 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 $$ -BEGIN - -- create tables, based on existing tables (so that the column types are always synchronised) - 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; - -- new VN, load defaults - IF cid IS NULL THEN - PERFORM edit_revtable('v', NULL); - INSERT INTO edit_vn DEFAULT VALUES; - -- otherwise, load revision - ELSE - PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid)); - INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, 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 FROM vn_relations WHERE vid1 = cid; - INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid; - END IF; -END; -$$ LANGUAGE plpgsql; +-- type used for the revision inserting functions +CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); -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, l_vnn, 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 FROM edit_vn_relations; - INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots; - UPDATE vn SET latest = r.cid WHERE id = r.iid; - DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; - RETURN r; -END; -$$ LANGUAGE plpgsql; +-- import the new and updated functions +\i util/sql/func.sql -CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$ -BEGIN - -- temp. tables - 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; - -- new release - IF cid IS NULL THEN - PERFORM edit_revtable('r', NULL); - INSERT INTO edit_release DEFAULT VALUES; - -- load revision - ELSE - PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid)); - 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; -END; -$$ LANGUAGE plpgsql; +-- call update_vncache() when a release is added, edited, hidden or unhidden +CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); -CREATE OR REPLACE FUNCTION edit_release_commit() RETURNS edit_rettype AS $$ -DECLARE - r edit_rettype; -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!'; - 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; - DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; - RETURN r; -END; -$$ LANGUAGE plpgsql; +-- improved relgraph notify triggers +DROP TRIGGER vn_relgraph_notify ON vn; +CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); +DROP TRIGGER vn_relgraph_notify ON producers; +CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify(); -CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$ -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; - -- new producer - IF cid IS NULL THEN - PERFORM edit_revtable('p', NULL); - INSERT INTO edit_producer DEFAULT VALUES; - -- load revision - ELSE - PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid)); - 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; - END IF; -END; -$$ LANGUAGE plpgsql; +-- more efficient version of tag_vn_calc() +SELECT tag_vn_calc(); -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!'; - 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; - DROP TABLE edit_revision, edit_producer, edit_producer_relations; - RETURN r; -END; -$$ LANGUAGE plpgsql; +-- regenerate the relation graphs so that they contain IDs for highlighting +UPDATE vn SET rgraph = NULL; +UPDATE producers SET rgraph = NULL; +DELETE FROM relgraphs; -- cgit v1.2.3