SQL: Sync production DB schema with the one in git

Found by comparing the output of pg_dump --schema-only.

There were a few minor differences, nothing that would cause any issues.
The different credit_type definition in prod was odd, though.

Most names of the primary key constraint are different, too, but I'll
not bother with renaming those for now - it's not likely to cause
issues anytime soon.

Interestingly, pg_dump output for the threads_id_seq and threads.id
DEFAULT is pretty different, though the only real difference that I
could see was the different OWNER of threads_id_seq. Not likely to cause
problems, anyway.

1 files changed, 14 insertions, 0 deletions

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;