diff options
Diffstat (limited to 'util')
| -rwxr-xr-x | util/dbdump.pl | 1 | ||||
| -rwxr-xr-x | util/devdump.pl | 2 | ||||
| -rw-r--r-- | util/updates/wip-vn-titles.sql | 41 |
3 files changed, 43 insertions, 1 deletions
diff --git a/util/dbdump.pl b/util/dbdump.pl index f71b9598..5488d6e4 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -107,6 +107,7 @@ my %tables = ( .' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' .' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' }, vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' }, + vn_titles => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, vn_length_votes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)' , order => 'vid, uid' }, wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden diff --git a/util/devdump.pl b/util/devdump.pl index 2a20544c..9d801912 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -157,7 +157,7 @@ sub copy_entry { # Visual novels copy anime => "SELECT DISTINCT a.* FROM anime a JOIN vn_anime_hist v ON v.aid = a.id JOIN changes c ON c.id = v.chid WHERE c.itemid IN($vids)"; - copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots/], \@vids; + copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots vn_titles/], \@vids; # VN-related niceties copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE vid IN($vids)", {uid => 'user'}; diff --git a/util/updates/wip-vn-titles.sql b/util/updates/wip-vn-titles.sql new file mode 100644 index 00000000..b8162ea7 --- /dev/null +++ b/util/updates/wip-vn-titles.sql @@ -0,0 +1,41 @@ +BEGIN; + +CREATE TABLE vn_titles ( + id vndbid NOT NULL, + lang language NOT NULL, + title text NOT NULL, + latin text, + official boolean NOT NULL, + PRIMARY KEY(id, lang) +); + +CREATE TABLE vn_titles_hist ( + chid integer NOT NULL, + lang language NOT NULL, + title text NOT NULL, + latin text, + official boolean NOT NULL, + PRIMARY KEY(chid, lang) +); + +INSERT INTO vn_titles SELECT id, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn; +INSERT INTO vn_titles_hist SELECT chid, olang, CASE WHEN original = '' THEN title ELSE original END, CASE WHEN original = '' THEN NULL ELSE title END, true FROM vn_hist; + +ALTER TABLE vn_titles ADD CONSTRAINT vn_titles_id_fkey FOREIGN KEY (id) REFERENCES vn (id); +ALTER TABLE vn_titles_hist ADD CONSTRAINT vn_titles_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; +ALTER TABLE vn ADD CONSTRAINT vn_olang_fkey FOREIGN KEY (id,olang) REFERENCES vn_titles (id,lang) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_olang_fkey FOREIGN KEY (chid,olang)REFERENCES vn_titles_hist(chid,lang) DEFERRABLE INITIALLY DEFERRED; + +-- TODO: actually drop +ALTER TABLE vn RENAME COLUMN original TO old_original; +ALTER TABLE vn RENAME COLUMN title TO old_title; +--ALTER TABLE vn RENAME COLUMN old_original TO original; +--ALTER TABLE vn RENAME COLUMN old_title TO title; +--ALTER TABLE vn DROP COLUMN original, DROP COLUMN title; + +CREATE VIEW vnt AS SELECT v.*, COALESCE(vo.latin, vo.title) AS title, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END AS alttitle FROM vn v JOIN vn_titles vo ON vo.id = v.id AND vo.lang = v.olang; + +COMMIT; +\i sql/func.sql +\i sql/editfunc.sql +\i sql/perms.sql |