SQL: Convert all item-tables to a different schema

This commit breaks pretty much everything. Lots of code will have to be
fixed to work with this new schema.

The basic idea is to separate live data from archived data, which allows
for smaller and more effective indices on the live data, and the
archived data doesn't need such indices and have to be accessed at all
for most operations.  Another goal is to eliminate table joins to fetch
some necessary information, e.g. it's not necessary anymore to join the
main item tables in order to fetch only the latest revision of some item
data.

This is very much work in progress. I might stumble upon some weird
issue while fixing the code, and might have to redesign everything
from scratch again. Let's just see how things go.

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