sascha@38: --
sascha@38: -- schema to store artifacts in H2 databases.
sascha@38: --
sascha@38: 
sascha@38: BEGIN;
sascha@38: 
sascha@38: -- not using AUTO_INCREMENT to be more compatible with
sascha@38: -- other dbms.
sascha@38: CREATE SEQUENCE ARTIFACTS_ID_SEQ;
sascha@38: 
sascha@38: CREATE TABLE artifacts (
sascha@38:     id          INT PRIMARY KEY NOT NULL,
sascha@38:     gid         UUID            NOT NULL UNIQUE,
sascha@38:     creation    TIMESTAMP       NOT NULL,
sascha@38:     last_access TIMESTAMP       NOT NULL,
sascha@38:     ttl         BIGINT, -- NULL means eternal
sascha@41:     factory     VARCHAR(256)    NOT NULL,
sascha@38:     data        BINARY
sascha@38: );
sascha@38: 
sascha@121: CREATE SEQUENCE USERS_ID_SEQ;
sascha@121: 
sascha@121: CREATE TABLE users (
sascha@121:     id   INT PRIMARY KEY NOT NULL,
sascha@124:     gid  UUID            NOT NULL UNIQUE,
bjoern@406:     name VARCHAR(256)    NOT NULL,
bjoern@406:     account VARCHAR(256) NOT NULL UNIQUE,
sascha@121:     role BINARY
sascha@121: );
sascha@121: 
sascha@121: CREATE SEQUENCE COLLECTIONS_ID_SEQ;
sascha@121: 
sascha@121: CREATE TABLE collections (
sascha@121:     id          INT PRIMARY KEY NOT NULL,
sascha@124:     gid         UUID            NOT NULL UNIQUE,
sascha@121:     name VARCHAR(256)           NOT NULL,
sascha@121:     owner_id    INT             NOT NULL REFERENCES users(id),
sascha@121:     creation    TIMESTAMP       NOT NULL,
sascha@121:     last_access TIMESTAMP       NOT NULL,
sascha@121:     ttl         BIGINT, -- NULL means eternal
sascha@199:     attribute   BINARY
sascha@121: );
sascha@121: 
ingo@187: CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
sascha@121: 
sascha@121: CREATE TABLE collection_items (
sascha@121:     id            INT PRIMARY KEY NOT NULL,
sascha@121:     collection_id INT             NOT NULL REFERENCES collections(id),
sascha@121:     artifact_id   INT             NOT NULL REFERENCES artifacts(id),
sascha@156:     attribute     BINARY,
ingo@274:     creation      TIMESTAMP       NOT NULL,
sascha@121:     UNIQUE (collection_id, artifact_id)
sascha@121: );
sascha@121: 
sascha@229: CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
teichmann@479:     ON artifacts FOR EACH ROW
teichmann@479:     CALL "org.dive4elements.artifactdatabase.h2.CollectionAccessUpdateTrigger";
sascha@229: 
sascha@38: COMMIT;