summaryrefslogtreecommitdiff
path: root/util/updates/update_2.25-sqlsplit.sql
blob: eb1ce231f2efc5363aaf8fe7bcc01f290b2747cd (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
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