summaryrefslogtreecommitdiff
path: root/util/updates
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates')
-rw-r--r--util/updates/update_2.25-sqlsplit.sql216
1 files changed, 216 insertions, 0 deletions
diff --git a/util/updates/update_2.25-sqlsplit.sql b/util/updates/update_2.25-sqlsplit.sql
new file mode 100644
index 00000000..eb1ce231
--- /dev/null
+++ b/util/updates/update_2.25-sqlsplit.sql
@@ -0,0 +1,216 @@
+-- Q: Why recreate all the tables rather than modify existing ones?
+-- A: Because the production tables have been modified many times, and columns
+-- weren't always in the same order as in scheme.sql. Recreating everything
+-- also has the advantage of ensuring that all references and indices are
+-- handled and documented here. In hindsight, it also seems like the easier
+-- approach.
+
+ALTER TABLE changes RENAME TO changes_old;
+ALTER TABLE chars RENAME TO chars_old;
+ALTER TABLE chars_rev RENAME TO chars_rev_old;
+ALTER TABLE chars_traits RENAME TO chars_traits_old;
+ALTER TABLE chars_vns RENAME TO chars_vns_old;
+ALTER TABLE producers RENAME TO producers_old;
+ALTER TABLE producers_rev RENAME TO producers_rev_old;
+ALTER TABLE producers_relations RENAME TO producers_relations_old;
+ALTER TABLE releases RENAME TO releases_old;
+ALTER TABLE releases_rev RENAME TO releases_rev_old;
+ALTER TABLE releases_lang RENAME TO releases_lang_old;
+ALTER TABLE releases_media RENAME TO releases_media_old;
+ALTER TABLE releases_platforms RENAME TO releases_platforms_old;
+ALTER TABLE releases_producers RENAME TO releases_producers_old;
+ALTER TABLE releases_vn RENAME TO releases_vn_old;
+ALTER TABLE staff RENAME TO staff_old;
+ALTER TABLE staff_rev RENAME TO staff_rev_old;
+ALTER TABLE staff_alias RENAME TO staff_alias_old;
+ALTER TABLE vn RENAME TO vn_old;
+ALTER TABLE vn_rev RENAME TO vn_rev_old;
+ALTER TABLE vn_anime RENAME TO vn_anime_old;
+ALTER TABLE vn_relations RENAME TO vn_relations_old;
+ALTER TABLE vn_screenshots RENAME TO vn_screenshots_old;
+ALTER TABLE vn_seiyuu RENAME TO vn_seiyuu_old;
+ALTER TABLE vn_staff RENAME TO vn_staff_old;
+
+
+\i util/sql/schema.sql
+
+
+-- XXX: This query uses a window function to generate changes.rev instead of
+-- copying the value from the old table. This is done because, in the old
+-- database schema, there was no uniqueness constraint on (type, itemid, rev),
+-- and due to a race condition it was possible for duplicates to appear. This
+-- is a pretty rare occurence, and easy to correct by renumbering the changes.
+-- (Changes the URL of a few revision pages, but there's no way to avoid that)
+INSERT INTO changes SELECT c.id, c.type, COALESCE(vr.vid, pr.pid, rr.rid, cr.cid, sr.sid),
+ row_number() OVER (PARTITION BY c.type, COALESCE(vr.vid, pr.pid, rr.rid, cr.cid, sr.sid) ORDER BY c.id ASC),
+ c.added, c.requester, c.ip, c.comments, c.ihid, c.ilock
+ FROM changes_old c
+ LEFT JOIN vn_rev_old vr ON vr.id = c.id
+ LEFT JOIN producers_rev_old pr ON pr.id = c.id
+ LEFT JOIN releases_rev_old rr ON rr.id = c.id
+ LEFT JOIN chars_rev_old cr ON cr.id = c.id
+ LEFT JOIN staff_rev_old sr ON sr.id = c.id;
+
+INSERT INTO chars SELECT c.id, c.locked, c.hidden,
+ cr.name, cr.original, cr.alias, cr.image, cr.desc, cr.gender, cr.s_bust, cr.s_waist, cr.s_hip,
+ cr.b_month, cr.b_day, cr.height, cr.weight, cr.bloodt, cr.main, cr.main_spoil
+ FROM chars_old c JOIN chars_rev_old cr ON cr.id = c.latest;
+
+INSERT INTO chars_hist SELECT cr.id,
+ cr.name, cr.original, cr.alias, cr.image, cr.desc, cr.gender, cr.s_bust, cr.s_waist, cr.s_hip,
+ cr.b_month, cr.b_day, cr.height, cr.weight, cr.bloodt, cr.main, cr.main_spoil
+ FROM chars_rev_old cr;
+
+INSERT INTO chars_traits SELECT c.id, ct.tid, ct.spoil
+ FROM chars_old c
+ JOIN chars_traits_old ct ON ct.cid = c.latest;
+
+INSERT INTO chars_traits_hist SELECT cid, tid, spoil
+ FROM chars_traits_old;
+
+INSERT INTO chars_vns SELECT c.id, cv.vid, cv.rid, cv.spoil, cv.role
+ FROM chars_old c
+ JOIN chars_vns_old cv ON cv.cid = c.latest;
+
+INSERT INTO chars_vns_hist SELECT cid, vid, rid, spoil, role
+ FROM chars_vns_old;
+
+INSERT INTO producers SELECT p.id, p.locked, p.hidden,
+ pr.type, pr.name, pr.original, pr.website, pr.lang, pr.desc, pr.alias, pr.l_wp, p.rgraph
+ FROM producers_old p JOIN producers_rev_old pr ON pr.id = p.latest;
+
+INSERT INTO producers_hist SELECT id, type, name, original, website, lang, "desc", alias, l_wp
+ FROM producers_rev_old;
+
+INSERT INTO producers_relations SELECT p.id, pr.pid2, pr.relation
+ FROM producers_old p
+ JOIN producers_relations_old pr ON p.latest = pr.pid1;
+
+INSERT INTO producers_relations_hist SELECT pid1, pid2, relation
+ FROM producers_relations_old;
+
+INSERT INTO releases SELECT r.id, r.locked, r.hidden,
+ rr.title, rr.original, rr.type, rr.website, rr.catalog, rr.gtin, rr.released, rr.notes, rr.minage, rr.patch,
+ rr.freeware, rr.doujin, rr.resolution, rr.voiced, rr.ani_story, rr.ani_ero
+ FROM releases_old r JOIN releases_rev_old rr ON rr.id = r.latest;
+
+INSERT INTO releases_hist SELECT rr.id,
+ rr.title, rr.original, rr.type, rr.website, rr.catalog, rr.gtin, rr.released, rr.notes, rr.minage, rr.patch,
+ rr.freeware, rr.doujin, rr.resolution, rr.voiced, rr.ani_story, rr.ani_ero
+ FROM releases_rev_old rr;
+
+INSERT INTO releases_lang SELECT r.id, rl.lang
+ FROM releases_old r JOIN releases_lang_old rl ON rl.rid = r.latest;
+
+INSERT INTO releases_lang_hist SELECT rl.rid, rl.lang
+ FROM releases_lang_old rl;
+
+INSERT INTO releases_media SELECT r.id, rm.medium, rm.qty
+ FROM releases_old r JOIN releases_media_old rm ON rm.rid = r.latest;
+
+INSERT INTO releases_media_hist SELECT rm.rid, rm.medium, rm.qty
+ FROM releases_media_old rm;
+
+INSERT INTO releases_platforms SELECT r.id, rp.platform
+ FROM releases_old r JOIN releases_platforms_old rp ON rp.rid = r.latest;
+
+INSERT INTO releases_platforms_hist SELECT rp.rid, rp.platform
+ FROM releases_platforms_old rp;
+
+INSERT INTO releases_producers SELECT r.id, rp.pid, rp.developer, rp.publisher
+ FROM releases_old r JOIN releases_producers_old rp ON rp.rid = r.latest;
+
+INSERT INTO releases_producers_hist SELECT rp.rid, rp.pid, rp.developer, rp.publisher
+ FROM releases_producers_old rp;
+
+INSERT INTO releases_vn SELECT r.id, rv.vid
+ FROM releases_old r JOIN releases_vn_old rv ON rv.rid = r.latest;
+
+INSERT INTO releases_vn_hist SELECT rv.rid, rv.vid
+ FROM releases_vn_old rv;
+
+INSERT INTO staff SELECT s.id, s.locked, s.hidden,
+ sr.aid, sr.gender, sr.lang, sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb
+ FROM staff_old s JOIN staff_rev_old sr ON sr.id = s.latest;
+
+INSERT INTO staff_hist SELECT sr.id,
+ sr.aid, sr.gender, sr.lang, sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb
+ FROM staff_rev_old sr;
+
+INSERT INTO staff_alias SELECT s.id, sa.id, sa.name, sa.original
+ FROM staff_old s JOIN staff_alias_old sa ON sa.rid = s.latest;
+
+INSERT INTO staff_alias_hist SELECT rid, id, name, original
+ FROM staff_alias_old;
+
+INSERT INTO vn SELECT v.id, v.locked, v.hidden,
+ vr.title, vr.original, vr.alias, vr.length, vr.img_nsfw, vr.image, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai,
+ v.rgraph, v.c_released, v.c_languages, v.c_olang, v.c_platforms, v.c_popularity, v.c_rating, v.c_votecount, v.c_search
+ FROM vn_old v JOIN vn_rev_old vr ON vr.id = v.latest;
+
+INSERT INTO vn_hist SELECT vr.id,
+ vr.title, vr.original, vr.alias, vr.length, vr.img_nsfw, vr.image, vr.desc, vr.l_wp, vr.l_encubed, vr.l_renai
+ FROM vn_rev_old vr;
+
+INSERT INTO vn_anime SELECT v.id, va.aid
+ FROM vn_old v JOIN vn_anime_old va ON va.vid = v.latest;
+
+INSERT INTO vn_anime_hist SELECT vid, aid
+ FROM vn_anime_old;
+
+INSERT INTO vn_relations SELECT v.id, vr.vid2, vr.relation, vr.official
+ FROM vn_old v JOIN vn_relations_old vr ON vr.vid1 = v.latest;
+
+INSERT INTO vn_relations_hist SELECT vid1, vid2, relation, official
+ FROM vn_relations_old;
+
+INSERT INTO vn_screenshots SELECT v.id, vs.scr, vs.rid, vs.nsfw
+ FROM vn_old v JOIN vn_screenshots_old vs ON vs.vid = v.latest;
+
+INSERT INTO vn_screenshots_hist SELECT vid, scr, rid, nsfw
+ FROM vn_screenshots_old;
+
+INSERT INTO vn_seiyuu SELECT v.id, vs.aid, vs.cid, vs.note
+ FROM vn_old v JOIN vn_seiyuu_old vs ON vs.vid = v.latest;
+
+INSERT INTO vn_seiyuu_hist SELECT vid, aid, cid, note
+ FROM vn_seiyuu_old;
+
+INSERT INTO vn_staff SELECT v.id, vs.aid, vs.role, vs.note
+ FROM vn_old v JOIN vn_staff_old vs ON vs.vid = v.latest;
+
+INSERT INTO vn_staff_hist SELECT vid, aid, role, note
+ FROM vn_staff_old;
+
+
+
+-- Dropping all tables with CASCADE causes all foreign key references to and
+-- from the tables to be dropped as well. This is exactly what we want, so we
+-- can re-add the constraints on the newly created tables.
+DROP TABLE changes_old CASCADE;
+DROP TABLE chars_old CASCADE;
+DROP TABLE chars_rev_old CASCADE;
+DROP TABLE chars_traits_old CASCADE;
+DROP TABLE chars_vns_old CASCADE;
+DROP TABLE producers_old CASCADE;
+DROP TABLE producers_rev_old CASCADE;
+DROP TABLE producers_relations_old CASCADE;
+DROP TABLE releases_old CASCADE;
+DROP TABLE releases_rev_old CASCADE;
+DROP TABLE releases_lang_old CASCADE;
+DROP TABLE releases_media_old CASCADE;
+DROP TABLE releases_platforms_old CASCADE;
+DROP TABLE releases_producers_old CASCADE;
+DROP TABLE releases_vn_old CASCADE;
+DROP TABLE staff_old CASCADE;
+DROP TABLE staff_rev_old CASCADE;
+DROP TABLE staff_alias_old CASCADE;
+DROP TABLE vn_old CASCADE;
+DROP TABLE vn_rev_old CASCADE;
+DROP TABLE vn_anime_old CASCADE;
+DROP TABLE vn_relations_old CASCADE;
+DROP TABLE vn_screenshots_old CASCADE;
+DROP TABLE vn_seiyuu_old CASCADE;
+DROP TABLE vn_staff_old CASCADE;
+
+\i util/sql/tableattrs.sql