[mb-commits] branch, mbs-4115, created. MBS-4115, Schema changes for .png/.gif support.

The branch, mbs-4115 has been created
        at  2e0b85f916a7de8fb9b9fde0b660a7bad9941cc0 (commit)

- Log -----------------------------------------------------------------
commit 2e0b85f916a7de8fb9b9fde0b660a7bad9941cc0
Author: warp <[email protected]>
Date:   Fri Jan 18 16:27:45 2013 +0100

    MBS-4115, Schema changes for .png/.gif support.

diff --git a/admin/sql/caa/CreateFKConstraints.sql b/admin/sql/caa/CreateFKConstraints.sql
index 808986d..8864b61 100644
--- a/admin/sql/caa/CreateFKConstraints.sql
+++ b/admin/sql/caa/CreateFKConstraints.sql
@@ -30,3 +30,7 @@ ALTER TABLE cover_art_archive.release_group_cover_art
 
 ALTER TABLE cover_art_archive.release_group_cover_art
    ADD FOREIGN KEY (release) REFERENCES musicbrainz.release(id);
+
+ALTER TABLE cover_art
+   ADD FOREIGN KEY (image_type) REFERENCES image_type(id);
+
diff --git a/admin/sql/caa/CreatePrimaryKeys.sql b/admin/sql/caa/CreatePrimaryKeys.sql
index 8041b90..68033e2 100644
--- a/admin/sql/caa/CreatePrimaryKeys.sql
+++ b/admin/sql/caa/CreatePrimaryKeys.sql
@@ -4,6 +4,7 @@
 SET search_path = 'cover_art_archive';
 
 ALTER TABLE art_type ADD CONSTRAINT art_type_pkey PRIMARY KEY (id);
+ALTER TABLE image_type ADD CONSTRAINT image_type_pkey PRIMARY KEY (id);
 ALTER TABLE cover_art ADD CONSTRAINT cover_art_pkey PRIMARY KEY (id);
 ALTER TABLE cover_art_type ADD CONSTRAINT cover_art_type_pkey PRIMARY KEY (id, type_id);
 ALTER TABLE release_group_cover_art ADD PRIMARY KEY (release_group);
diff --git a/admin/sql/caa/CreateTables.sql b/admin/sql/caa/CreateTables.sql
index 9edf753..ad09c2f 100644
--- a/admin/sql/caa/CreateTables.sql
+++ b/admin/sql/caa/CreateTables.sql
@@ -7,8 +7,15 @@ CREATE TABLE art_type (
     name TEXT NOT NULL
 );
 
+CREATE TABLE image_type (
+    id SERIAL NOT NULL, -- PK
+    mime_type TEXT NOT NULL,
+    suffix TEXT NOT NULL
+);
+
 CREATE TABLE cover_art (
     id BIGINT NOT NULL, -- PK
+    image_type INTEGER NOT NULL,  -- references image_type.id
     release INTEGER NOT NULL, -- references musicbrainz.release.id CASCADE
     comment TEXT NOT NULL DEFAULT '',
     edit INTEGER NOT NULL, -- references musicbrainz.edit.id
diff --git a/admin/sql/updates/20130117-cover-image-types.sql b/admin/sql/updates/20130117-cover-image-types.sql
new file mode 100644
index 0000000..089f258
--- /dev/null
+++ b/admin/sql/updates/20130117-cover-image-types.sql
@@ -0,0 +1,40 @@
+SET search_path = 'cover_art_archive';
+
+BEGIN;
+
+CREATE TABLE image_type (
+    id SERIAL NOT NULL, -- PK
+    mime_type TEXT NOT NULL,
+    suffix TEXT NOT NULL
+);
+
+ALTER TABLE image_type ADD CONSTRAINT image_type_pkey PRIMARY KEY (id);
+
+INSERT INTO image_type (mime_type, suffix)
+    VALUES ('image/jpeg', 'jpg'),
+           ('image/png', 'png'),
+           ('image/gif', 'gif');
+
+-- references image_type.id
+ALTER TABLE cover_art ADD COLUMN image_type INTEGER;
+
+COMMIT;
+
+-- New transaction because postgresql isn't happy when I update
+-- the table I just altered in the same transaction.
+
+BEGIN;
+
+UPDATE cover_art SET image_type = (
+    SELECT id FROM image_type WHERE suffix = 'jpg');
+
+COMMIT;
+
+BEGIN;
+
+ALTER TABLE cover_art ALTER COLUMN image_type SET NOT NULL;
+
+ALTER TABLE cover_art
+    ADD FOREIGN KEY (image_type) REFERENCES image_type(id);
+
+COMMIT;

-----------------------------------------------------------------------


hooks/post-receive
-- 
mb_server