# HG changeset patch
# User jbe
# Date 1581021181 -3600
# Node ID f61caa45de94dd2408b363e2843322b04790d3ae
# Parent  81cd9463878ff75b0e09939d4f7152e1736bf2ae
Include column "content_type" of "file" table in UNIQUE index

diff -r 81cd9463878f -r f61caa45de94 core.sql
--- a/core.sql	Thu Feb 06 19:17:22 2020 +0100
+++ b/core.sql	Thu Feb 06 21:33:01 2020 +0100
@@ -55,17 +55,20 @@
 
 CREATE TABLE "file" (
         "id"                    SERIAL8         PRIMARY KEY,
-        "hash"                  TEXT            NOT NULL UNIQUE,
+        UNIQUE ("content_type", "hash"),
+        "content_type"          TEXT            NOT NULL,
+        "hash"                  TEXT            NOT NULL,
         "data"                  BYTEA           NOT NULL,
-        "preview_data"          BYTEA,
-        "preview_content_type"  TEXT );
+        "preview_content_type"  TEXT,
+        "preview_data"          BYTEA );
 
 COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';
 
-COMMENT ON COLUMN "file"."hash"                 IS 'Hash of file contents to avoid storing duplicates';
+COMMENT ON COLUMN "file"."content_type"         IS 'Content type of "data"';
+COMMENT ON COLUMN "file"."hash"                 IS 'Hash of "data" to avoid storing duplicates where content-type and data is identical';
 COMMENT ON COLUMN "file"."data"                 IS 'Binary content';
+COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';
 COMMENT ON COLUMN "file"."preview_data"         IS 'Preview (e.g. preview image)';
-COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';
 
 
 CREATE TABLE "member" (
@@ -312,7 +315,7 @@
         "member_id"             INT4            REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
         "image_type"            "member_image_type",
         "scaled"                BOOLEAN,
-        "content_type"          TEXT,
+        "content_type"          TEXT,  -- TODO: NOT NULL?
         "data"                  BYTEA           NOT NULL );
 
 COMMENT ON TABLE "member_image" IS 'Images of members';