sascha@979: BEGIN;
sascha@979: 
sascha@979: CREATE TABLE users (
sascha@979:     id  IDENTITY PRIMARY KEY NOT NULL,
sascha@979:     gid UUID                 NOT NULL UNIQUE
sascha@979: );
sascha@979: 
sascha@979: CREATE TABLE collections (
sascha@979:     id      IDENTITY PRIMARY KEY NOT NULL,
sascha@979:     gid     UUID                 NOT NULL UNIQUE,
sascha@980:     user_id INT                  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
sascha@979:     name    VARCHAR(256)         NOT NULL
sascha@979: );
sascha@979: 
sascha@979: CREATE TABLE artifacts (
sascha@979:     id    IDENTITY PRIMARY KEY NOT NULL,
sascha@979:     gid   UUID                 NOT NULL UNIQUE,
sascha@979:     state VARCHAR(256)         NOT NULL
sascha@979: );
sascha@979: 
sascha@979: CREATE TABLE collection_items (
sascha@979:     id            IDENTITY PRIMARY KEY NOT NULL,
sascha@980:     collection_id INT                  NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
sascha@980:     artifact_id   INT                  NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
sascha@979: );
sascha@979: 
sascha@979: CREATE TABLE artifact_data (
sascha@979:     id          IDENTITY PRIMARY KEY NOT NULL,
sascha@980:     artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
sascha@979:     k           VARCHAR(256)         NOT NULL,
sascha@979:     v           VARCHAR(256),         -- Maybe too short
sascha@979:     UNIQUE (artifact_id, k)
sascha@979: );
sascha@979: 
sascha@981: CREATE TABLE outs (
sascha@981:     id          IDENTITY PRIMARY KEY NOT NULL,
sascha@981:     artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
sascha@981:     name        VARCHAR(256)         NOT NULL,
sascha@981:     description VARCHAR(256),
sascha@981:     out_type    VARCHAR(256)
sascha@981: );
sascha@981: 
sascha@979: CREATE TABLE facets (
sascha@979:     id          IDENTITY PRIMARY KEY NOT NULL,
sascha@980:     artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
sascha@981:     out_id      INT                  NOT NULL REFERENCES outs(id)      ON DELETE CASCADE,
sascha@979:     name        VARCHAR(256)         NOT NULL,
sascha@979:     num         INT                  NOT NULL,
sascha@980:     state       VARCHAR(256)         NOT NULL,
sascha@979:     description VARCHAR(256),
sascha@979:     UNIQUE (artifact_id, output, num, name)
sascha@979: );
sascha@979: 
sascha@980: -- DROP TABLE facets;
sascha@981: -- DROP TABLE outs;
sascha@980: -- DROP TABLE artifact_data;
sascha@980: -- DROP TABLE collection_items;
sascha@980: -- DROP TABLE collections;
sascha@980: -- DROP TABLE artifacts;
sascha@980: -- DROP TABLE users;
sascha@980: 
sascha@979: COMMIT;