/* Create a mimefs database. */


CREATE DATABASE mimefs_0 ENCODING 'UTF8'; -- int is for forwards-compatibility

\connect mimefs_0;

BEGIN;

    /* Each entity -- volume, message -- is universally uniquely identified,
       so that in a future implementation the volume table can more easily
       reside on a different server than the message table, etc.

       */

    CREATE TABLE volume (
        vid         CHAR(36) PRIMARY KEY    -- a UUID
       );

    CREATE TABLE message (
        vid         CHAR(36)                -- a UUID from the volume table
                    REFERENCES volume
                        MATCH FULL          -- paranoia
                        ON DELETE CASCADE   -- DELETE messages when a volume
                        ON UPDATE RESTRICT  -- is DELETEd, but raise an error
                                            -- if a vid is ever UPDATEd
      , mid         CHAR(36) PRIMARY KEY    -- a UUID
      , headers     TEXT                    -- MIME headers
                    NOT NULL
                    DEFAULT ''
      , body        TEXT                    -- MIME body
                    NOT NULL
                    DEFAULT ''
       );

    CREATE TABLE field (
        mid         CHAR(36)                -- a UUID from the message table
                    REFERENCES message
                        MATCH FULL          -- paranoia
                        ON DELETE CASCADE   -- DELETE message metadata when a
                        ON UPDATE RESTRICT  -- message is DELETEd, but raise an
                                            -- error if a mid is ever UPDATEd
      , name        TEXT                    -- a MIME header field-name
                    NOT NULL
                    CHECK (name <> '')
      , body        TEXT                    -- a MIME header field-body
                    NOT NULL
                    DEFAULT ''
       );

COMMIT;