ingo@4065: BEGIN;
ingo@4065: 
ingo@4065: CREATE SEQUENCE USERS_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE users (
ingo@4065:     id  INT  PRIMARY KEY NOT NULL,
ingo@4065:     gid UUID             NOT NULL UNIQUE
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE SEQUENCE COLLECTIONS_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE collections (
ingo@4065:     id       INT  PRIMARY KEY NOT NULL,
ingo@4065:     gid      UUID             NOT NULL UNIQUE,
ingo@4065:     user_id  INT              NOT NULL REFERENCES users(id) ON DELETE CASCADE,
ingo@4065:     name     VARCHAR(256)     NOT NULL,
ingo@4065:     creation TIMESTAMP        NOT NULL
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE SEQUENCE ARTIFACTS_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE artifacts (
ingo@4065:     id       INT  PRIMARY KEY NOT NULL,
ingo@4065:     gid      UUID             NOT NULL UNIQUE,
ingo@4065:     state    VARCHAR(256)     NOT NULL,
ingo@4065:     creation TIMESTAMP        NOT NULL
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE collection_items (
ingo@4065:     id            INT PRIMARY KEY NOT NULL,
ingo@4065:     collection_id INT             NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
ingo@4065:     artifact_id   INT             NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE artifact_data (
ingo@4065:     id          INT PRIMARY KEY NOT NULL,
ingo@4065:     artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
ingo@4065:     kind        VARCHAR(256)    NOT NULL,
ingo@4065:     k           VARCHAR(256)    NOT NULL,
ingo@4065:     v           VARCHAR(256),   -- Maybe too short
ingo@4065:     UNIQUE (artifact_id, k)
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE SEQUENCE OUTS_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE outs (
ingo@4065:     id          INT PRIMARY KEY NOT NULL,
ingo@4065:     artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
ingo@4065:     name        VARCHAR(256)    NOT NULL,
ingo@4065:     description VARCHAR(256),
ingo@4065:     out_type    VARCHAR(256)
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE SEQUENCE FACETS_ID_SEQ;
ingo@4065: 
ingo@4065: CREATE TABLE facets (
ingo@4065:     id          INT PRIMARY KEY NOT NULL,
ingo@4065:     out_id      INT             NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
ingo@4065:     name        VARCHAR(256)    NOT NULL,
ingo@4065:     num         INT             NOT NULL,
ingo@4065:     state       VARCHAR(256)    NOT NULL,
ingo@4065:     description VARCHAR(256),
ingo@4065:     UNIQUE (out_id, num, name)
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE VIEW master_artifacts AS
ingo@4065:     SELECT a2.id             AS id,
ingo@4065:            a2.gid            AS gid,
ingo@4065:            a2.state          AS state,
ingo@4065:            a2.creation       AS creation,
ingo@4065:            ci2.collection_id AS collection_id
ingo@4065:     FROM   collection_items ci2 
ingo@4065:            JOIN artifacts a2 
ingo@4065:              ON ci2.artifact_id = a2.id 
ingo@4065:            JOIN (SELECT ci.collection_id AS c_id, 
ingo@4065:                         MIN(a.creation)  AS oldest_a 
ingo@4065:                  FROM   collection_items ci 
ingo@4065:                         JOIN artifacts a 
ingo@4065:                           ON ci.artifact_id = a.id 
ingo@4065:                  GROUP  BY ci.collection_id) o 
ingo@4065:              ON o.c_id = ci2.collection_id 
ingo@4065:     WHERE  a2.creation = o.oldest_a;
ingo@4065: 
ingo@4065: -- DROP VIEW master_artifacts;
ingo@4065: -- DROP SEQUENCE USERS_ID_SEQ;
ingo@4065: -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
ingo@4065: -- DROP SEQUENCE ARTIFACTS_ID_SEQ;
ingo@4065: -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
ingo@4065: -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
ingo@4065: -- DROP SEQUENCE OUTS_ID_SEQ;
ingo@4065: -- DROP SEQUENCE FACETS_ID_SEQ;
ingo@4065: -- DROP TABLE facets;
ingo@4065: -- DROP TABLE outs;
ingo@4065: -- DROP TABLE artifact_data;
ingo@4065: -- DROP TABLE collection_items;
ingo@4065: -- DROP TABLE collections;
ingo@4065: -- DROP TABLE artifacts;
ingo@4065: -- DROP TABLE users;
ingo@4065: 
ingo@4065: COMMIT;