diff options
Diffstat (limited to 'util/updates/update_2.8.sql')
-rw-r--r-- | util/updates/update_2.8.sql | 213 |
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); + |