1.1 --- a/core.sql	Mon Aug 26 15:52:03 2019 +0200
     1.2 +++ b/core.sql	Thu Feb 06 18:40:46 2020 +0100
     1.3 @@ -53,6 +53,21 @@
     1.4  COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
     1.5  
     1.6  
     1.7 +CREATE TABLE "file" (
     1.8 +        "id"                    SERIAL8         PRIMARY KEY,
     1.9 +        "hash"                  TEXT            NOT NULL UNIQUE,
    1.10 +        "data"                  BYTEA           NOT NULL,
    1.11 +        "preview_data"          BYTEA,
    1.12 +        "preview_content_type"  TEXT );
    1.13 +
    1.14 +COMMENT ON TABLE "file" IS 'Table holding file contents for draft attachments';
    1.15 +
    1.16 +COMMENT ON COLUMN "file"."hash"                 IS 'Hash of file contents to avoid storing duplicates';
    1.17 +COMMENT ON COLUMN "file"."data"                 IS 'Binary content';
    1.18 +COMMENT ON COLUMN "file"."preview_data"         IS 'Preview (e.g. preview image)';
    1.19 +COMMENT ON COLUMN "file"."preview_content_type" IS 'Content type of "preview_data"';
    1.20 +
    1.21 +
    1.22  CREATE TABLE "member" (
    1.23          "id"                    SERIAL4         PRIMARY KEY,
    1.24          "created"               TIMESTAMPTZ     NOT NULL DEFAULT now(),
    1.25 @@ -996,6 +1011,17 @@
    1.26  COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
    1.27  
    1.28  
    1.29 +CREATE TABLE "draft_attachment" (
    1.30 +        "id"                    SERIAL8         PRIMARY KEY,
    1.31 +        "draft_id"              INT8            REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    1.32 +        "file_id"               INT8            REFERENCES "file" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    1.33 +        "content_type"          TEXT,
    1.34 +        "title"                 TEXT,
    1.35 +        "description"           TEXT );
    1.36 +
    1.37 +COMMENT ON TABLE "draft_attachment" IS 'Binary attachments for a draft (images, PDF file, etc.); Implicitly ordered through ''id'' column';
    1.38 +
    1.39 +
    1.40  CREATE TABLE "suggestion" (
    1.41          UNIQUE ("initiative_id", "id"),  -- index needed for foreign-key on table "opinion"
    1.42          "initiative_id"         INT4            NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,