summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbdump.pl4
-rw-r--r--util/updates/2022-08-25-staff-editions.sql43
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