diff options
Diffstat (limited to 'util')
-rwxr-xr-x | util/dbdump.pl | 4 | ||||
-rw-r--r-- | util/updates/2022-08-25-staff-editions.sql | 43 |
2 files changed, 46 insertions, 1 deletions
diff --git a/util/dbdump.pl b/util/dbdump.pl index f749ed72..01c1ba98 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -101,12 +101,14 @@ my %tables = ( .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes WHERE NOT private)' }, vn => { where => 'NOT hidden' }, vn_anime => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, + vn_editions => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, vn_relations => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, vn_screenshots => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, vn_seiyuu => { 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)' .' 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_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)' + , order => 'id, eid, aid, role' }, 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) AND NOT private' , order => 'vid, uid' }, diff --git a/util/updates/2022-08-25-staff-editions.sql b/util/updates/2022-08-25-staff-editions.sql new file mode 100644 index 00000000..d5a731e5 --- /dev/null +++ b/util/updates/2022-08-25-staff-editions.sql @@ -0,0 +1,43 @@ +ALTER TYPE credit_type ADD VALUE 'translator' AFTER 'director'; +ALTER TYPE credit_type ADD VALUE 'editor' AFTER 'translator'; +ALTER TYPE credit_type ADD VALUE 'qa' AFTER 'editor'; + +CREATE TABLE vn_editions ( + id vndbid NOT NULL, -- [pub] + lang language, -- [pub] + eid smallint NOT NULL, -- [pub] (not stable across entry revisions) + official boolean NOT NULL DEFAULT TRUE, -- [pub] + name text NOT NULL, -- [pub] + PRIMARY KEY(id, eid) +); + +CREATE TABLE vn_editions_hist ( + chid integer NOT NULL, + lang language, + eid smallint NOT NULL, + official boolean NOT NULL DEFAULT TRUE, + name text NOT NULL, + PRIMARY KEY(chid, eid) +); + +ALTER TABLE vn_staff ADD COLUMN eid smallint; +ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_pkey; +CREATE UNIQUE INDEX vn_staff_pkey ON vn_staff (id, COALESCE(eid,-1::smallint), aid, role); + +ALTER TABLE vn_staff_hist ADD COLUMN eid smallint; +ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_pkey; +CREATE UNIQUE INDEX vn_staff_hist_pkey ON vn_staff_hist (chid, COALESCE(eid,-1::smallint), aid, role); + +ALTER TABLE vn_staff DROP CONSTRAINT vn_staff_id_fkey; +ALTER TABLE vn_staff_hist DROP CONSTRAINT vn_staff_hist_chid_fkey; + +ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_eid_fkey FOREIGN KEY (id,eid) REFERENCES vn_editions (id,eid) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_eid_fkey FOREIGN KEY (chid,eid) REFERENCES vn_editions_hist (chid,eid) DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE users_prefs + ADD COLUMN staffed_langs language[], + ADD COLUMN staffed_olang boolean NOT NULL DEFAULT true, + ADD COLUMN staffed_unoff boolean NOT NULL DEFAULT false; + +\i sql/editfunc.sql +\i sql/perms.sql |