summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--sql/schema.sql6
-rw-r--r--sql/tableattrs.sql12
-rw-r--r--util/updates/2020-08-07-schema-sync.sql14
3 files changed, 23 insertions, 9 deletions
diff --git a/sql/schema.sql b/sql/schema.sql
index 6389c97e..bcf1bfc2 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -455,7 +455,7 @@ CREATE TABLE releases_producers (
pid integer NOT NULL, -- [pub] producers.id
developer boolean NOT NULL DEFAULT FALSE, -- [pub]
publisher boolean NOT NULL DEFAULT TRUE, -- [pub]
- CHECK(developer OR publisher),
+ CONSTRAINT releases_producers_check1 CHECK(developer OR publisher),
PRIMARY KEY(id, pid)
);
@@ -674,7 +674,7 @@ CREATE TABLE tags_vn_inherit (
-- threads
CREATE TABLE threads (
- id vndbid PRIMARY KEY CONSTRAINT threads_id_check CHECK(vndbid_type(id) = 't'),
+ id vndbid PRIMARY KEY DEFAULT vndbid('t', nextval('threads_id_seq')::int) CONSTRAINT threads_id_check CHECK(vndbid_type(id) = 't'),
title varchar(50) NOT NULL DEFAULT '',
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
@@ -709,8 +709,8 @@ CREATE TABLE threads_posts (
uid integer,
date timestamptz NOT NULL DEFAULT NOW(),
edited timestamptz,
- msg text NOT NULL DEFAULT '',
hidden boolean NOT NULL DEFAULT FALSE,
+ msg text NOT NULL DEFAULT '',
PRIMARY KEY(tid, num),
CONSTRAINT threads_posts_first_nonhidden CHECK(num > 1 OR NOT hidden)
);
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index ce77b118..a1dd16b6 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -11,11 +11,11 @@ ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey
ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id) ON DELETE CASCADE;
ALTER TABLE image_votes ADD CONSTRAINT image_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
@@ -90,10 +90,10 @@ ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey
ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
+ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
-ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
+ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
diff --git a/util/updates/2020-08-07-schema-sync.sql b/util/updates/2020-08-07-schema-sync.sql
new file mode 100644
index 00000000..9e6229da
--- /dev/null
+++ b/util/updates/2020-08-07-schema-sync.sql
@@ -0,0 +1,14 @@
+-- The credit_type definition used in production was... wrong.
+-- It had more values than in the schema and values were ordered incorrectly.
+-- Redefine it with the proper definition.
+ALTER TYPE credit_type RENAME TO old_credit_type;
+CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
+
+ALTER TABLE vn_staff ALTER COLUMN role DROP DEFAULT;
+ALTER TABLE vn_staff ALTER COLUMN role TYPE credit_type USING role::text::credit_type;
+ALTER TABLE vn_staff ALTER COLUMN role SET DEFAULT 'staff';
+ALTER TABLE vn_staff_hist ALTER COLUMN role DROP DEFAULT;
+ALTER TABLE vn_staff_hist ALTER COLUMN role TYPE credit_type USING role::text::credit_type;
+ALTER TABLE vn_staff_hist ALTER COLUMN role SET DEFAULT 'staff';
+
+DROP TYPE old_credit_type;