From e1835977fea7be42b702275ac88e69921d82777d Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 25 Jan 2009 12:30:50 +0100
Subject: Random quote feature

---
 util/dump.sql | 10 ++++++++++
 1 file changed, 10 insertions(+)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index f23d6d62..3d937832 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -61,6 +61,15 @@ CREATE TABLE producers_rev (
   "desc" text NOT NULL DEFAULT ''
 );
 
+
+-- 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,
@@ -296,6 +305,7 @@ ALTER TABLE changes            ADD FOREIGN KEY (causedby)  REFERENCES changes
 ALTER TABLE producers          ADD FOREIGN KEY (latest)    REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE producers_rev      ADD FOREIGN KEY (id)        REFERENCES changes       (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE producers_rev      ADD FOREIGN KEY (pid)       REFERENCES producers     (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE quotes             ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE releases           ADD FOREIGN KEY (latest)    REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE releases_media     ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE releases_platforms ADD FOREIGN KEY (rid)       REFERENCES releases_rev  (id) DEFERRABLE INITIALLY DEFERRED;
-- 
cgit v1.2.3


From 6089007c7b57c3ff158d6169ec22ad6d9325a7ec Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Thu, 12 Feb 2009 22:38:10 +0100
Subject: Catalog number field to release entries

---
 util/dump.sql | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 3d937832..470a8aeb 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -113,7 +113,8 @@ CREATE TABLE releases_rev (
   notes text NOT NULL DEFAULT '',
   minage smallint NOT NULL DEFAULT -1,
   gtin bigint NOT NULL DEFAULT 0,
-  patch boolean NOT NULL DEFAULT FALSE
+  patch boolean NOT NULL DEFAULT FALSE,
+  catalog varchar(50) NOT NULL DEFAULT ''
 );
 
 -- releases_vn
-- 
cgit v1.2.3


From d03465b573c10b10819201bba8257e59362589f1 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 21 Feb 2009 19:00:12 +0100
Subject: Aliases field to producer entries

---
 util/dump.sql | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 470a8aeb..e3652a0f 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -58,7 +58,8 @@ CREATE TABLE producers_rev (
   original varchar(200) NOT NULL DEFAULT '',
   website varchar(250) NOT NULL DEFAULT '',
   lang varchar NOT NULL DEFAULT 'ja',
-  "desc" text NOT NULL DEFAULT ''
+  "desc" text NOT NULL DEFAULT '',
+  alias varchar(500) NOT NULL DEFAULT ''
 );
 
 
-- 
cgit v1.2.3


From 1b99dd8701ce572c8ac20b71b3eb9b471b69d788 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 22 Mar 2009 10:47:36 +0100
Subject: Incorporated update_2.3.sql into dump.sql and updated the dbgrapher
 script

---
 util/dump.sql | 179 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 174 insertions(+), 5 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index 470a8aeb..26294511 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -154,6 +154,40 @@ CREATE TABLE stats_cache (
   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 bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()),
+  state smallint NOT NULL DEFAULT 0,
+  c_vns integer NOT NULL DEFAULT 0
+);
+
+-- 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)
+);
+
 -- threads
 CREATE TABLE threads (
   id SERIAL NOT NULL PRIMARY KEY,
@@ -175,8 +209,8 @@ CREATE TABLE threads_posts (
   PRIMARY KEY(tid, num)
 );
 
--- threads_tags
-CREATE TABLE threads_tags (
+-- 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,
@@ -198,7 +232,8 @@ CREATE TABLE users (
   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'
+  ip inet NOT NULL DEFAULT '0.0.0.0',
+  c_tags integer NOT NULL DEFAULT 0
 );
 
 -- vn
@@ -295,6 +330,32 @@ CREATE TABLE wlists (
 
 
 
+-----------------
+--  V I E W S  --
+-----------------
+
+
+CREATE OR REPLACE VIEW tags_vn_all AS
+    SELECT *
+      FROM tags_vn
+  UNION
+    SELECT *
+      FROM tag_vn_childs();
+
+CREATE OR REPLACE VIEW tags_vn_grouped AS
+    SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+      FROM tags_vn_all
+  GROUP BY tag, vid, uid;
+
+CREATE OR REPLACE VIEW tags_vn_inherited AS
+    SELECT tag, vid, COUNT(uid)::real 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 tags_vn_grouped
+  GROUP BY tag, vid;
+
+
+
+
 
 -----------------------------------------------
 --  F O R E I G N   K E Y   C H E C K I N G  --
@@ -318,10 +379,16 @@ ALTER TABLE releases_vn        ADD FOREIGN KEY (rid)       REFERENCES releases_r
 ALTER TABLE releases_vn        ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE rlists             ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE rlists             ADD FOREIGN KEY (rid)       REFERENCES releases      (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_aliases       ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_parents       ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_parents       ADD FOREIGN KEY (parent)    REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_vn            ADD FOREIGN KEY (tag)       REFERENCES tags          (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_vn            ADD FOREIGN KEY (vid)       REFERENCES vn            (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_vn            ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
 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) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE threads_posts      ADD FOREIGN KEY (uid)       REFERENCES users         (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE threads_tags       ADD FOREIGN KEY (tid)       REFERENCES threads       (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_boards     ADD FOREIGN KEY (tid)       REFERENCES threads       (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn                 ADD FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn                 ADD FOREIGN KEY (rgraph)    REFERENCES relgraph      (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn_anime           ADD FOREIGN KEY (aid)       REFERENCES anime         (id) DEFERRABLE INITIALLY DEFERRED;
@@ -439,6 +506,101 @@ END;
 $$ LANGUAGE plpgsql;
 
 
+-- tag: tag to start with,
+-- lvl: recursion level
+-- dir: direction, true = parent->child, false = child->parent
+CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer);
+CREATE OR REPLACE FUNCTION tag_tree(tag integer, lvl integer, dir boolean) RETURNS SETOF tag_tree_item AS $$
+DECLARE
+  r tag_tree_item%rowtype;
+  r2 tag_tree_item%rowtype;
+BEGIN
+  IF dir AND tag = 0 THEN
+    FOR r IN
+      SELECT lvl, t.id, t.name, t.c_vns
+        FROM tags t
+        WHERE state = 2 AND NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
+        ORDER BY t.name
+    LOOP
+      RETURN NEXT r;
+      IF lvl-1 <> 0 THEN
+        FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
+          RETURN NEXT r2;
+        END LOOP;
+      END IF;
+    END LOOP;
+  ELSIF dir THEN
+    FOR r IN
+      SELECT lvl, tp.tag, t.name, t.c_vns
+        FROM tags_parents tp
+        JOIN tags t ON t.id = tp.tag
+        WHERE tp.parent = tag
+          AND state = 2
+        ORDER BY t.name
+    LOOP
+      RETURN NEXT r;
+      IF lvl-1 <> 0 THEN
+        FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
+          RETURN NEXT r2;
+        END LOOP;
+      END IF;
+    END LOOP;
+  ELSE
+    FOR r IN
+      SELECT lvl, tp.parent, t.name, t.c_vns
+        FROM tags_parents tp
+        JOIN tags t ON t.id = tp.parent
+        WHERE tp.tag = tag
+          AND state = 2
+        ORDER BY t.name
+    LOOP
+      RETURN NEXT r;
+      IF lvl-1 <> 0 THEN
+        FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
+          RETURN NEXT r2;
+        END LOOP;
+      END IF;
+    END LOOP;
+  END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- returns all votes inherited by childs
+-- UNION this with tags_vn and you have all votes for all tags
+CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$
+DECLARE
+  r tags_vn%rowtype;
+  i RECORD;
+  l RECORD;
+BEGIN
+  FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
+    FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
+      FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
+        RETURN NEXT r;
+      END LOOP;
+    END LOOP;
+  END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- recalculate tags_vn_stored
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+  DROP TABLE IF EXISTS tags_vn_stored;
+  CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
+  CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);
+
+  UPDATE tags_vn_stored tvs SET rating =
+      ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
+    / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
+
+  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
+  RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
 
 
 
@@ -457,12 +619,18 @@ BEGIN
     ELSE
       UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
     END IF;
-  ELSE
+  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;
@@ -470,6 +638,7 @@ $$ 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
-- 
cgit v1.2.3


From 9765be8dc46d9f458fea8c548cde02e3dba7b920 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 22 Mar 2009 11:13:04 +0100
Subject: Fixed tiny SQL bug

---
 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 993baaa3..a9681c10 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -575,7 +575,7 @@ DECLARE
   i RECORD;
   l RECORD;
 BEGIN
-  FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
+  FOR l IN SElECT id FROM tags WHERE meta = FALSE AND state = 2 AND EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
     FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
       FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
         RETURN NEXT r;
-- 
cgit v1.2.3


From 864bce475636282e31643cbbb8cab2fc9ac47ce0 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sat, 28 Mar 2009 15:10:33 +0100
Subject: Fixed and re-organized tag<->vn calculation

Fixed major performance bug caused by referencing the wrong table, moved
all intermediate views to tag_vn_calc() as temporary views (similar to
update_vnpopularity()) and renamed tags_vn_stored to tags_vn_bayesian.
---
 util/dump.sql | 70 ++++++++++++++++++++++++++++-------------------------------
 1 file changed, 33 insertions(+), 37 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index a9681c10..5003c6e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -189,6 +189,15 @@ CREATE TABLE tags_vn (
   PRIMARY KEY(tag, vid, uid)
 );
 
+-- tags_vn_bayesian
+CREATE TABLE tags_vn_bayesian (
+  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,
@@ -331,33 +340,6 @@ CREATE TABLE wlists (
 
 
 
------------------
---  V I E W S  --
------------------
-
-
-CREATE OR REPLACE VIEW tags_vn_all AS
-    SELECT *
-      FROM tags_vn
-  UNION
-    SELECT *
-      FROM tag_vn_childs();
-
-CREATE OR REPLACE VIEW tags_vn_grouped AS
-    SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
-      FROM tags_vn_all
-  GROUP BY tag, vid, uid;
-
-CREATE OR REPLACE VIEW tags_vn_inherited AS
-    SELECT tag, vid, COUNT(uid)::real 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 tags_vn_grouped
-  GROUP BY tag, vid;
-
-
-
-
-
 -----------------------------------------------
 --  F O R E I G N   K E Y   C H E C K I N G  --
 -----------------------------------------------
@@ -586,18 +568,31 @@ END;
 $$ LANGUAGE plpgsql;
 
 
--- recalculate tags_vn_stored
+-- recalculate tags_vn_bayesian
 CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
 BEGIN
-  DROP TABLE IF EXISTS tags_vn_stored;
-  CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
-  CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);
-
-  UPDATE tags_vn_stored tvs SET rating =
-      ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
-    / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
-
-  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
+  -- all votes for all tags
+  CREATE OR REPLACE TEMPORARY VIEW tags_vn_all AS
+    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, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::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;
+  TRUNCATE tags_vn_bayesian;
+  INSERT INTO tags_vn_bayesian
+      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 tags_vn_grouped
+    GROUP BY tag, vid;
+  CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
+  -- now perform the bayesian ranking calculation
+  UPDATE tags_vn_bayesian tvs SET rating =
+      ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
+    / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+  -- and update the VN count in the tags table as well
+  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id);
   RETURN;
 END;
 $$ LANGUAGE plpgsql;
@@ -606,6 +601,7 @@ SELECT tag_vn_calc();
 
 
 
+
 -----------------------
 --  T R I G G E R S  --
 -----------------------
-- 
cgit v1.2.3

