From 4304d30193646fdecbe2fcd081cfbce46ad82ed6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 8 Oct 2009 22:19:35 +0200 Subject: Synchronised and updated dump.sql and dbgraph.pl Removed the 'SET default_with_oids', as the minimum required PostgreSQL version (8.3) has OIDS disables by default already. Also uncommented the CREATE LANGUAGE, plpgsql is a "trusted language" and as such doesn't need superuser priveleges to create anymore. --- util/dump.sql | 31 ++++++++++++++----------------- 1 file changed, 14 insertions(+), 17 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index aaa1a5e9..7a621103 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -1,15 +1,12 @@ - --- we don't use PgSQL's OIDS -SET default_with_oids = false; - --- for the functions to work, the following query must --- be executed on the database by a superuser: --- CREATE PROCEDURAL LANGUAGE plpgsql - +-- plpgsql is required for our (trigger) functions +CREATE LANGUAGE plpgsql; +-- data types +CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); +CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); ----------------------------------------- @@ -23,7 +20,7 @@ CREATE TABLE anime ( year smallint, ann_id integer, nfo_id varchar(200), - type smallint, + type anime_type, title_romaji, title_kanji, lastfetch timestamptz @@ -137,12 +134,6 @@ CREATE TABLE releases_vn ( PRIMARY KEY(rid, vid) ); --- relgraph -CREATE TABLE relgraph ( - id SERIAL NOT NULL PRIMARY KEY, - cmap text NOT NULL DEFAULT '' -); - -- rlists CREATE TABLE rlists ( uid integer NOT NULL DEFAULT 0, @@ -288,11 +279,17 @@ CREATE TABLE vn_anime ( PRIMARY KEY(vid, aid) ); +-- vn_graphs +CREATE TABLE vn_graphs ( + id SERIAL PRIMARY KEY, + svg xml NOT NULL +); + -- vn_relations CREATE TABLE vn_relations ( vid1 integer NOT NULL DEFAULT 0, vid2 integer NOT NULL DEFAULT 0, - relation integer NOT NULL DEFAULT 0, + relation vn_relation NOT NULL, PRIMARY KEY(vid1, vid2) ); @@ -380,7 +377,7 @@ ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads 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 relgraph (id); +ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES vn_graphs (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); -- cgit v1.2.3 From d96c546f8beb1fdc79847b462f46ced9332e4c48 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Fri, 9 Oct 2009 08:52:32 +0200 Subject: SQL: Converted releases_media.medium to an ENUM type --- util/dump.sql | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 7a621103..b43d8c62 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -5,8 +5,9 @@ CREATE LANGUAGE plpgsql; -- data types -CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); +CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); +CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); ----------------------------------------- @@ -86,7 +87,7 @@ CREATE TABLE releases_lang ( -- releases_media CREATE TABLE releases_media ( rid integer NOT NULL DEFAULT 0, - medium character(3) NOT NULL DEFAULT '', + medium medium NOT NULL, qty smallint NOT NULL DEFAULT 1, PRIMARY KEY(rid, medium, qty) ); -- cgit v1.2.3 From d43ae33a587743fc0916930e0a39f561f3174a69 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Fri, 9 Oct 2009 11:45:32 +0200 Subject: Added producer role (developer/publisher) to DB and release editor They aren't displayed on the site yet, though. --- util/dump.sql | 3 +++ 1 file changed, 3 insertions(+) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index b43d8c62..10d13b9d 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -103,6 +103,9 @@ CREATE TABLE releases_platforms ( 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) ); -- cgit v1.2.3 From 5ce2ce4c494feef5940c032549472a52b5282ad1 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 10 Oct 2009 15:51:57 +0200 Subject: Display number of unread posts in user menu Instead of displaying the total number of threads. Posts are marked as read when the thread is opened. --- util/dump.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 10d13b9d..c952c162 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -240,6 +240,7 @@ 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) ); -- cgit v1.2.3 From 69d8738688ebb72707fe377b7ce7c717407aea96 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 12 Oct 2009 09:34:06 +0200 Subject: SQL: Converted changes.type to an ENUM This is a very important column in a very important table, I hope I didn't forget to update a piece of code somewhere... --- util/dump.sql | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index c952c162..8a8d2c61 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -5,9 +5,10 @@ CREATE LANGUAGE plpgsql; -- data types -CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); -CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); -CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); +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 vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); ----------------------------------------- @@ -30,7 +31,7 @@ CREATE TABLE anime ( -- changes CREATE TABLE changes ( id SERIAL NOT NULL PRIMARY KEY, - type smallint NOT NULL DEFAULT 0, + type dbentry_type NOT NULL, rev integer NOT NULL DEFAULT 1, added timestamptz NOT NULL DEFAULT NOW(), requester integer NOT NULL DEFAULT 0, -- cgit v1.2.3 From 668833467f8231ebdc622d42edcb49e8374b42b4 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 17 Oct 2009 16:21:38 +0200 Subject: SQL: Converted releases_rev.type to an ENUM data type --- util/dump.sql | 9 +++++---- 1 file changed, 5 insertions(+), 4 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 8a8d2c61..145c09e8 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -8,6 +8,7 @@ CREATE LANGUAGE plpgsql; 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 release_type AS ENUM ('complete', 'partial', 'trial'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); @@ -116,7 +117,7 @@ CREATE TABLE releases_rev ( rid integer NOT NULL DEFAULT 0, title varchar(250) NOT NULL DEFAULT '', original varchar(250) NOT NULL DEFAULT '', - type smallint NOT NULL DEFAULT 0, + type release_type NOT NULL DEFAULT 'complete', website varchar(250) NOT NULL DEFAULT '', released integer NOT NULL, notes text NOT NULL DEFAULT '', @@ -448,7 +449,7 @@ BEGIN 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 <> 2 + AND rr1.type <> ''trial'' AND r1.hidden = FALSE AND rr1.released <> 0 GROUP BY rv1.vid @@ -460,7 +461,7 @@ BEGIN 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 <> 2 + AND rr2.type <> ''trial'' AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer AND r2.hidden = FALSE GROUP BY rl2.lang @@ -473,7 +474,7 @@ BEGIN 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 <> 2 + AND rr3.type <> ''trial'' AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer AND r3.hidden = FALSE GROUP BY rp3.platform -- cgit v1.2.3 From 62cb41c3b8780bffe5a8ea58a6a7b5053d9e1059 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 18 Oct 2009 18:06:14 +0200 Subject: SQL: Fixed calculation of tags_vn_bayesian.spoiler --- util/dump.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 145c09e8..9fbff0a2 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -589,7 +589,7 @@ BEGIN SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs(); -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS - SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler + SELECT tag, vid, uid, MAX(vote)::real AS vote, AVG(spoiler)::real AS spoiler FROM tags_vn_all GROUP BY tag, vid, uid; -- grouped by (tag, vid) and serialized into a table DROP INDEX IF EXISTS tags_vn_bayesian_tag; -- cgit v1.2.3 From 91b6578e17f1ce6d0ff7974d06ae55ad2e39bf1d Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 21 Oct 2009 13:47:59 +0200 Subject: SQL: Synced update_2.8.sql into dump.sql This doesn't mean the relations are final, though... --- util/dump.sql | 23 ++++++++++++++++++----- 1 file changed, 18 insertions(+), 5 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 9fbff0a2..f5ce2614 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -5,11 +5,12 @@ 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 release_type AS ENUM ('complete', 'partial', 'trial'); -CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); +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', 'par', 'sub', 'imp', 'ipa'); +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'); ----------------------------------------- @@ -49,6 +50,16 @@ CREATE TABLE producers ( hidden boolean NOT NULL DEFAULT FALSE ); + +-- 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, @@ -356,6 +367,8 @@ CREATE TABLE wlists ( ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id); ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; +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); -- cgit v1.2.3 From 96e77619793fbe8351e023d17e257e96cdb37509 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 21 Oct 2009 14:21:38 +0200 Subject: SQL: Renamed vn_graphs to relgraphs and synced dbgraph.pl As the same table can easily be used to store producer relation graphs as well. --- util/dump.sql | 18 +++++++----------- 1 file changed, 7 insertions(+), 11 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index f5ce2614..97d317e0 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -50,7 +50,6 @@ CREATE TABLE producers ( hidden boolean NOT NULL DEFAULT FALSE ); - -- producers_relations CREATE TABLE producers_relations ( pid1 integer NOT NULL, @@ -59,7 +58,6 @@ CREATE TABLE producers_relations ( PRIMARY KEY(pid1, pid2) ); - -- producers_rev CREATE TABLE producers_rev ( id integer NOT NULL PRIMARY KEY, @@ -73,7 +71,6 @@ CREATE TABLE producers_rev ( alias varchar(500) NOT NULL DEFAULT '' ); - -- quotes CREATE TABLE quotes ( vid integer NOT NULL, @@ -81,7 +78,6 @@ CREATE TABLE quotes ( PRIMARY KEY(vid, quote) ); - -- releases CREATE TABLE releases ( id SERIAL NOT NULL PRIMARY KEY, @@ -151,6 +147,12 @@ CREATE TABLE releases_vn ( 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, @@ -297,12 +299,6 @@ CREATE TABLE vn_anime ( PRIMARY KEY(vid, aid) ); --- vn_graphs -CREATE TABLE vn_graphs ( - id SERIAL PRIMARY KEY, - svg xml NOT NULL -); - -- vn_relations CREATE TABLE vn_relations ( vid1 integer NOT NULL DEFAULT 0, @@ -397,7 +393,7 @@ ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads 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 vn_graphs (id); +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); -- cgit v1.2.3 From 6900668e8dad97762e4a4c493c21dec1391998cb Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 21 Oct 2009 16:01:08 +0200 Subject: Added relation graphs for producers TODO: - document the relations - emit a relgraph notify when needed --- util/dump.sql | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 97d317e0..84d862fd 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -47,7 +47,8 @@ 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 + hidden boolean NOT NULL DEFAULT FALSE, + rgraph integer ); -- producers_relations @@ -363,6 +364,7 @@ CREATE TABLE wlists ( ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (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); -- cgit v1.2.3 From 76154a765f9b69af92495462a5e880d5c2e608ce Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 21 Oct 2009 16:21:31 +0200 Subject: SQL: Send relgraph notify when needed for producer entries --- util/dump.sql | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 84d862fd..c04273b6 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -796,6 +796,36 @@ $$ LANGUAGE plpgsql; CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); +-- Same as above for producers, with slight differences in the steps: +-- There is no 2, and +-- 3 = New producer revision of which the name, language or type differs from the previous revision (deferred) +CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$ +BEGIN + -- 1. + IF TG_TABLE_NAME = 'producers' 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 TG_TABLE_NAME = 'producers' AND TG_OP = 'UPDATE' THEN + IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN + -- 3 & 4 + IF OLD.latest <> NEW.latest AND ( + 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) + 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 EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = NEW.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = OLD.latest) + ) THEN + UPDATE producers SET rgraph = NULL WHERE id = NEW.id; + END IF; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON producers DEFERRABLE INITIALLY DEFERRED 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 -- cgit v1.2.3 From 0557a463cf3e1b2cd5dbb4f799a2d08c9a6cd789 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 24 Oct 2009 11:36:57 +0200 Subject: Added Spawned <> Originated from producer relation --- util/dump.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index c04273b6..9f8710f8 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -8,7 +8,7 @@ CREATE LANGUAGE plpgsql; 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', 'par', 'sub', 'imp', 'ipa'); +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'); -- cgit v1.2.3 From b4bef02c08a3bc8198f97d0e97701981f9bb85c8 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 24 Oct 2009 11:41:51 +0200 Subject: Removed update_rev() function and monthly revcache cron The changes.rev column should be correct in the first place, and in the (most likely) impossible condition that it isn't, the update_rev() function is more likely to make things worse. --- util/dump.sql | 25 ------------------------- 1 file changed, 25 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 9f8710f8..0fdf5696 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -420,31 +420,6 @@ ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn ------------------------- --- update_rev(table, ids) - updates the rev column in the changes table -CREATE FUNCTION update_rev(tbl text, ids text) RETURNS void AS $$ -DECLARE - r RECORD; - r2 RECORD; - i integer; - t text; - e text; -BEGIN - SELECT INTO t SUBSTRING(tbl, 1, 1); - e := ''; - IF ids <> '' THEN - e := ' WHERE id IN('||ids||')'; - END IF; - FOR r IN EXECUTE 'SELECT id FROM '||tbl||e LOOP - i := 1; - FOR r2 IN EXECUTE 'SELECT id FROM '||tbl||'_rev WHERE '||t||'id = '||r.id||' ORDER BY id ASC' LOOP - UPDATE changes SET rev = i WHERE id = r2.id; - i := i+1; - END LOOP; - END LOOP; -END; -$$ LANGUAGE plpgsql; - - -- update_vncache(id) - updates the c_* columns in the vn table CREATE FUNCTION update_vncache(id integer) RETURNS void AS $$ DECLARE -- cgit v1.2.3