From 4531862cec3b982eb8e08e66b4ad8ea7987d4bc3 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
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/dump.sql | 789 ----------------------------------------------------------
 1 file changed, 789 deletions(-)
 delete mode 100644 util/dump.sql

(limited to 'util/dump.sql')

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);
-
-- 
cgit v1.2.3

