summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-08-08 14:30:29 +0200
committerYorhel <git@yorhel.nl>2009-08-08 14:50:13 +0200
commit3c0db27f773f99b325dcc7c6a7c7d1c5bbfb69a9 (patch)
tree47e94570f74f2007cc67d36c23be1bd2426fa013 /util
parent56e1cdc886144b85ede74c15d1ced116c790f7c2 (diff)
Convert old category relations to tags and remove vn_categories
This will currently generate a 500 error for VN pages, because they still try to look something up in the vn_categories table. Will fix that soon. Obviously, this conversion script only works on the actual VNDB database, if you run your own VNDB you can safely ignore the INSERT error when executing update_2.6.sql.
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql9
-rw-r--r--util/updates/update_2.6.sql54
2 files changed, 52 insertions, 11 deletions
diff --git a/util/dump.sql b/util/dump.sql
index 3a470102..acd639d5 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -287,14 +287,6 @@ CREATE TABLE vn_anime (
PRIMARY KEY(vid, aid)
);
--- vn_categories
-CREATE TABLE vn_categories (
- vid integer NOT NULL DEFAULT 0,
- cat character(3) NOT NULL DEFAULT '',
- lvl smallint NOT NULL DEFAULT 3,
- PRIMARY KEY(vid, cat)
-);
-
-- vn_relations
CREATE TABLE vn_relations (
vid1 integer NOT NULL DEFAULT 0,
@@ -390,7 +382,6 @@ ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev
ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (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_categories 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);
diff --git a/util/updates/update_2.6.sql b/util/updates/update_2.6.sql
index 06722cc2..d9fe8198 100644
--- a/util/updates/update_2.6.sql
+++ b/util/updates/update_2.6.sql
@@ -94,7 +94,6 @@ ALTER TABLE threads_boards DROP CONSTRAINT threads_tags_tid_fkey; -- threads
ALTER TABLE vn DROP CONSTRAINT vn_rgraph_fkey;
ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_aid_fkey;
ALTER TABLE vn_anime DROP CONSTRAINT vn_anime_vid_fkey;
-ALTER TABLE vn_categories DROP CONSTRAINT vn_categories_vid_fkey;
ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid1_fkey;
ALTER TABLE vn_relations DROP CONSTRAINT vn_relations_vid2_fkey;
ALTER TABLE vn_rev DROP CONSTRAINT vn_rev_id_fkey;
@@ -136,7 +135,6 @@ ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads
ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (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_categories 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);
@@ -259,3 +257,55 @@ CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE
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();
+
+
+-- convert the old categories to the related tags for VNs that didn't have tags already
+INSERT INTO tags_vn (uid, spoiler, vid, vote, tag)
+ SELECT 1, 0, v.id,
+ CASE
+ WHEN vc.cat IN('gaa', 'gab', 'pli', 'pbr', 'tfu', 'tpa', 'tpr', 'lea', 'lfa', 'lsp', 'hfa', 'hfe') THEN 2
+ ELSE vc.lvl
+ END,
+ CASE
+ WHEN vc.cat = 'gaa' THEN 43 -- NVL
+ WHEN vc.cat = 'gab' THEN 32 -- ADV
+ WHEN vc.cat = 'gac' THEN 31 -- Action game
+ WHEN vc.cat = 'grp' THEN 35 -- RPG
+ WHEN vc.cat = 'gst' THEN 33 -- Strategy game
+ WHEN vc.cat = 'gsi' THEN 34 -- Simulation game
+ WHEN vc.cat = 'pli' THEN 145 -- Linear plot
+ WHEN vc.cat = 'pbr' THEN 606 -- Branching plot
+ WHEN vc.cat = 'eac' THEN 12 -- Action
+ WHEN vc.cat = 'eco' THEN 104 -- Comedy
+ WHEN vc.cat = 'edr' THEN 147 -- Drama
+ WHEN vc.cat = 'efa' THEN 2 -- Fantasy
+ WHEN vc.cat = 'eho' THEN 7 -- Horror
+ WHEN vc.cat = 'emy' THEN 19 -- Mystery
+ WHEN vc.cat = 'ero' THEN 96 -- Romance
+ WHEN vc.cat = 'esc' THEN 47 -- School life
+ WHEN vc.cat = 'esf' THEN 105 -- Sci-Fi
+ WHEN vc.cat = 'esj' THEN 97 -- Shoujo ai
+ WHEN vc.cat = 'esn' THEN 98 -- Shounen ai
+ WHEN vc.cat = 'tfu' THEN 140 -- Future
+ WHEN vc.cat = 'tpa' THEN 141 -- Past
+ WHEN vc.cat = 'tpr' THEN 143 -- Present
+ WHEN vc.cat = 'lea' THEN 52 -- Earth
+ WHEN vc.cat = 'lfa' THEN 259 -- Fantasy world
+ WHEN vc.cat = 'lsp' THEN 53 -- Space
+ WHEN vc.cat = 'hfa' THEN 133 -- Male protag
+ WHEN vc.cat = 'hfe' THEN 134 -- Female protag
+ WHEN vc.cat = 'saa' THEN 23 -- Sexual content
+ WHEN vc.cat = 'sbe' THEN 183 -- Bestiality
+ WHEN vc.cat = 'sin' THEN 86 -- Insect
+ WHEN vc.cat = 'slo' THEN 156 -- Lolicon
+ WHEN vc.cat = 'ssh' THEN 184 -- Shotacon
+ WHEN vc.cat = 'sya' THEN 83 -- Yaoi
+ WHEN vc.cat = 'syu' THEN 82 -- Yuri
+ WHEN vc.cat = 'sra' THEN 84 -- Rape
+ ELSE 11 -- the deleted 'Awesome' tag, this shouldn't happen
+ END
+ FROM vn v
+ JOIN vn_categories vc ON vc.vid = v.latest
+ WHERE NOT EXISTS(SELECT 1 FROM tags_vn tv WHERE tv.vid = v.id);
+DROP TABLE vn_categories;
+