sascha@979: BEGIN;
sascha@979: 
sascha@985: CREATE SEQUENCE USERS_ID_SEQ;
sascha@985: 
sascha@979: CREATE TABLE users (
sascha@985:     id  INT  PRIMARY KEY NOT NULL,
sascha@985:     gid UUID             NOT NULL UNIQUE
sascha@979: );
sascha@979: 
sascha@985: CREATE SEQUENCE COLLECTIONS_ID_SEQ;
sascha@985: 
sascha@979: CREATE TABLE collections (
sascha@1025:     id       INT  PRIMARY KEY NOT NULL,
sascha@1025:     gid      UUID             NOT NULL UNIQUE,
sascha@1025:     user_id  INT              NOT NULL REFERENCES users(id) ON DELETE CASCADE,
sascha@1025:     name     VARCHAR(256)     NOT NULL,
sascha@1025:     creation TIMESTAMP        NOT NULL
sascha@979: );
sascha@979: 
sascha@985: CREATE SEQUENCE ARTIFACTS_ID_SEQ;
sascha@985: 
sascha@979: CREATE TABLE artifacts (
sascha@1025:     id       INT  PRIMARY KEY NOT NULL,
sascha@1025:     gid      UUID             NOT NULL UNIQUE,
sascha@1025:     state    VARCHAR(256)     NOT NULL,
sascha@1025:     creation TIMESTAMP        NOT NULL
sascha@979: );
sascha@979: 
sascha@988: CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
sascha@985: 
sascha@979: CREATE TABLE collection_items (
sascha@985:     id            INT PRIMARY KEY NOT NULL,
sascha@985:     collection_id INT             NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
sascha@985:     artifact_id   INT             NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
sascha@979: );
sascha@979: 
sascha@985: CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
sascha@985: 
sascha@979: CREATE TABLE artifact_data (
sascha@985:     id          INT PRIMARY KEY NOT NULL,
sascha@985:     artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
sascha@990:     kind        VARCHAR(256)    NOT NULL,
sascha@985:     k           VARCHAR(256)    NOT NULL,
aheinecke@6609:     v           TEXT,
sascha@979:     UNIQUE (artifact_id, k)
sascha@979: );
sascha@979: 
sascha@985: CREATE SEQUENCE OUTS_ID_SEQ;
sascha@985: 
sascha@981: CREATE TABLE outs (
sascha@985:     id          INT PRIMARY KEY NOT NULL,
sascha@985:     artifact_id INT             NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
sascha@985:     name        VARCHAR(256)    NOT NULL,
sascha@981:     description VARCHAR(256),
sascha@981:     out_type    VARCHAR(256)
sascha@981: );
sascha@981: 
sascha@985: CREATE SEQUENCE FACETS_ID_SEQ;
sascha@985: 
sascha@979: CREATE TABLE facets (
sascha@985:     id          INT PRIMARY KEY NOT NULL,
sascha@992:     out_id      INT             NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
sascha@985:     name        VARCHAR(256)    NOT NULL,
sascha@992:     num         INT             NOT NULL,
sascha@992:     state       VARCHAR(256)    NOT NULL,
sascha@979:     description VARCHAR(256),
sascha@993:     UNIQUE (out_id, num, name)
sascha@979: );
sascha@979: 
sascha@1044: CREATE VIEW master_artifacts AS
sascha@1044:     SELECT a2.id             AS id,
sascha@1044:            a2.gid            AS gid,
sascha@1044:            a2.state          AS state,
sascha@1044:            a2.creation       AS creation,
sascha@1044:            ci2.collection_id AS collection_id
sascha@1044:     FROM   collection_items ci2 
sascha@1044:            JOIN artifacts a2 
sascha@1044:              ON ci2.artifact_id = a2.id 
sascha@1044:            JOIN (SELECT ci.collection_id AS c_id, 
sascha@1044:                         MIN(a.creation)  AS oldest_a 
sascha@1044:                  FROM   collection_items ci 
sascha@1044:                         JOIN artifacts a 
sascha@1044:                           ON ci.artifact_id = a.id 
sascha@1044:                  GROUP  BY ci.collection_id) o 
sascha@1044:              ON o.c_id = ci2.collection_id 
sascha@1044:     WHERE  a2.creation = o.oldest_a;
sascha@1044: 
rrenkert@4901: CREATE VIEW master_artifacts_range AS
rrenkert@4901:     SELECT ma.id                   AS id,
rrenkert@4901:            ma.gid                  AS gid,
rrenkert@4901:            ma.state                AS state,
rrenkert@4901:            ma.creation             AS creation,
rrenkert@4901:            ma.collection_id        AS collection_id,
rrenkert@4901:            mam.ld_mode             AS ld_mode,
rrenkert@4901:            mal.ld_locations        AS ld_locations,
rrenkert@4901:            maf.ld_from             AS ld_from,
rrenkert@4901:            mat.ld_to               AS ld_to
rrenkert@4901:     FROM master_artifacts ma
rrenkert@4901:         LEFT JOIN (SELECT ad.v           AS ld_mode,
rrenkert@4901:                           ad.artifact_id AS artifact_id
rrenkert@4901:                    FROM artifact_data ad
rrenkert@4901:                    WHERE ad.k = 'ld_mode') mam
rrenkert@4901:                    ON mam.artifact_id = ma.id
rrenkert@4901:         LEFT JOIN (SELECT ad.v           AS ld_locations,
rrenkert@4901:                           ad.artifact_id AS artifact_id
rrenkert@4901:                    FROM artifact_data ad
rrenkert@4901:                    WHERE ad.k = 'ld_locations') mal
rrenkert@4901:                    ON mal.artifact_id = ma.id
rrenkert@4901:         LEFT JOIN (SELECT ad.v           AS ld_from,
rrenkert@4901:                           ad.artifact_id AS artifact_id
rrenkert@4901:                    FROM artifact_data ad
rrenkert@4901:                    WHERE ad.k = 'ld_from') maf
rrenkert@4901:                    ON maf.artifact_id = ma.id
rrenkert@4901:         LEFT JOIN (SELECT ad.v           AS ld_to,
rrenkert@4901:                           ad.artifact_id AS artifact_id
rrenkert@4901:                    FROM artifact_data ad
rrenkert@4901:                    WHERE ad.k = 'ld_to') mat
rrenkert@4901:                    ON mat.artifact_id = ma.id;
rrenkert@4901: 
sascha@1044: -- DROP VIEW master_artifacts;
rrenkert@4901: -- DROP VIEW master_artifacts_range;
sascha@985: -- DROP SEQUENCE USERS_ID_SEQ;
sascha@985: -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
sascha@985: -- DROP SEQUENCE ARTIFACTS_ID_SEQ;
sascha@988: -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
sascha@985: -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
sascha@985: -- DROP SEQUENCE OUTS_ID_SEQ;
sascha@985: -- DROP SEQUENCE FACETS_ID_SEQ;
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;