summaryrefslogtreecommitdiff
path: root/util/updates/update_2.8.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.8.sql')
-rw-r--r--util/updates/update_2.8.sql213
1 files changed, 213 insertions, 0 deletions
diff --git a/util/updates/update_2.8.sql b/util/updates/update_2.8.sql
new file mode 100644
index 00000000..d8373210
--- /dev/null
+++ b/util/updates/update_2.8.sql
@@ -0,0 +1,213 @@
+
+-- !BEFORE! running this SQL file, make sure to kill Multi,
+-- After running this SQL file, also make sure to do a:
+-- $ rm -r static/rg/
+-- And start multi again
+
+-- VN Relation graphs are stored in the database as SVG - no cmaps and .png anymore
+UPDATE vn SET rgraph = NULL;
+ALTER TABLE vn DROP CONSTRAINT vn_rgraph_fkey;
+DROP TABLE relgraph;
+CREATE TABLE relgraphs (
+ id SERIAL PRIMARY KEY,
+ svg xml NOT NULL
+);
+ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+
+
+-- VN relations stored as enum
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+ALTER TABLE vn_relations ALTER COLUMN relation DROP DEFAULT;
+ALTER TABLE vn_relations ALTER COLUMN relation TYPE vn_relation USING
+ CASE
+ WHEN relation = 0 THEN 'seq'::vn_relation
+ WHEN relation = 1 THEN 'preq'
+ WHEN relation = 2 THEN 'set'
+ WHEN relation = 3 THEN 'alt'
+ WHEN relation = 4 THEN 'char'
+ WHEN relation = 5 THEN 'side'
+ WHEN relation = 6 THEN 'par'
+ WHEN relation = 7 THEN 'ser'
+ WHEN relation = 8 THEN 'fan'
+ ELSE 'orig'
+ END;
+
+
+-- producer relations
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
+CREATE TABLE producers_relations (
+ pid1 integer NOT NULL REFERENCES producers_rev (id),
+ pid2 integer NOT NULL REFERENCES producers (id),
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(pid1, pid2)
+);
+ALTER TABLE producers ADD COLUMN rgraph integer REFERENCES relgraphs (id);
+
+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();
+
+
+-- Anime types stored as enum
+CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+ALTER TABLE anime ALTER COLUMN type TYPE anime_type USING
+ CASE
+ WHEN type = 0 THEN 'tv'::anime_type
+ WHEN type = 1 THEN 'ova'
+ WHEN type = 2 THEN 'mov'
+ WHEN type = 3 THEN 'oth'
+ WHEN type = 4 THEN 'web'
+ WHEN type = 5 THEN 'spe'
+ WHEN type = 6 THEN 'mv'
+ ELSE NULL
+ END;
+
+
+-- Release media stored as enum
+CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+ALTER TABLE releases_media ALTER COLUMN medium DROP DEFAULT;
+ALTER TABLE releases_media ALTER COLUMN medium TYPE medium USING TRIM(both ' ' from medium)::medium;
+
+
+-- Differentiate between publishers and developers
+ALTER TABLE releases_producers ADD COLUMN developer boolean NOT NULL DEFAULT FALSE;
+ALTER TABLE releases_producers ADD COLUMN publisher boolean NOT NULL DEFAULT TRUE;
+ALTER TABLE releases_producers ADD CHECK(developer OR publisher);
+
+
+-- Keep track of last read post for PMs
+ALTER TABLE threads_boards ADD COLUMN lastread smallint;
+
+
+-- changes.type stored as enum
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
+ALTER TABLE changes ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE changes ALTER COLUMN type TYPE dbentry_type USING
+ CASE
+ WHEN type = 0 THEN 'v'::dbentry_type
+ WHEN type = 1 THEN 'r'
+ WHEN type = 2 THEN 'p'
+ ELSE NULL -- not allowed to happen, otherwise FIX YOUR DATABASE!
+ END;
+
+
+-- releases_rev.type stored as enum
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
+ALTER TABLE releases_rev ALTER COLUMN type DROP DEFAULT;
+ALTER TABLE releases_rev ALTER COLUMN type TYPE release_type USING
+ CASE
+ WHEN type = 0 THEN 'complete'::release_type
+ WHEN type = 1 THEN 'partial'
+ WHEN type = 2 THEN 'trial'
+ ELSE NULL
+ END;
+ALTER TABLE releases_rev ALTER COLUMN type SET DEFAULT 'complete';
+
+CREATE OR REPLACE FUNCTION update_vncache(id integer) RETURNS void AS $$
+DECLARE
+ w text := '';
+BEGIN
+ IF id > 0 THEN
+ w := ' WHERE id = '||id;
+ END IF;
+ EXECUTE '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
+ ), ''/''), '''')
+ '||w;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- fix calculation of the tags_vn_bayesian.spoiler column
+
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- 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, 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;
+ 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
+ HAVING AVG(vote) > 0;
+ 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;
+SELECT tag_vn_calc();
+
+
+-- remove update_rev()
+DROP FUNCTION update_rev(text, text);
+