ingo@4065: --
ingo@4065: -- schema to store artifacts in H2 databases.
ingo@4065: --
ingo@4065: 
ingo@4065: BEGIN;
ingo@4065: 
ingo@4065: -- not using AUTO_INCREMENT to be more compatible with
ingo@4065: -- other dbms.
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:     creation    TIMESTAMP       NOT NULL,
ingo@4065:     last_access TIMESTAMP       NOT NULL,
ingo@4065:     ttl         BIGINT, -- NULL means eternal
ingo@4065:     factory     VARCHAR(256)    NOT NULL,
ingo@4065:     data        BINARY
ingo@4065: );
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:     name VARCHAR(256)    NOT NULL,
ingo@4065:     account VARCHAR(256) NOT NULL UNIQUE,
ingo@4065:     role BINARY
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:     name VARCHAR(256)           NOT NULL,
ingo@4065:     owner_id    INT             NOT NULL REFERENCES users(id),
ingo@4065:     creation    TIMESTAMP       NOT NULL,
ingo@4065:     last_access TIMESTAMP       NOT NULL,
ingo@4065:     ttl         BIGINT, -- NULL means eternal
ingo@4065:     attribute   BINARY
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),
ingo@4065:     artifact_id   INT             NOT NULL REFERENCES artifacts(id),
ingo@4065:     attribute     BINARY,
ingo@4065:     creation      TIMESTAMP       NOT NULL,
ingo@4065:     UNIQUE (collection_id, artifact_id)
ingo@4065: );
ingo@4065: 
ingo@4065: CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
ingo@4065:     ON artifacts FOR EACH ROW 
ingo@4065:     CALL "de.intevation.artifactdatabase.h2.CollectionAccessUpdateTrigger";
ingo@4065: 
ingo@4065: COMMIT;