view artifact-database/doc/schema-h2.sql @ 458:4dcc59e851d7 2.9.11

Added tag 2.9.10 for changeset 5c07601fe60e
author Sascha L. Teichmann <teichmann@intevation.de>
date Fri, 01 Mar 2013 13:22:04 +0100
parents 10aa92f2eca0
children b8067c6f67f6
line wrap: on
line source
--
-- schema to store artifacts in H2 databases.
--

BEGIN;

-- not using AUTO_INCREMENT to be more compatible with
-- other dbms.
CREATE SEQUENCE ARTIFACTS_ID_SEQ;

CREATE TABLE artifacts (
    id          INT PRIMARY KEY NOT NULL,
    gid         UUID            NOT NULL UNIQUE,
    creation    TIMESTAMP       NOT NULL,
    last_access TIMESTAMP       NOT NULL,
    ttl         BIGINT, -- NULL means eternal
    factory     VARCHAR(256)    NOT NULL,
    data        BINARY
);

CREATE SEQUENCE USERS_ID_SEQ;

CREATE TABLE users (
    id   INT PRIMARY KEY NOT NULL,
    gid  UUID            NOT NULL UNIQUE,
    name VARCHAR(256)    NOT NULL,
    account VARCHAR(256) NOT NULL UNIQUE,
    role BINARY
);

CREATE SEQUENCE COLLECTIONS_ID_SEQ;

CREATE TABLE collections (
    id          INT PRIMARY KEY NOT NULL,
    gid         UUID            NOT NULL UNIQUE,
    name VARCHAR(256)           NOT NULL,
    owner_id    INT             NOT NULL REFERENCES users(id),
    creation    TIMESTAMP       NOT NULL,
    last_access TIMESTAMP       NOT NULL,
    ttl         BIGINT, -- NULL means eternal
    attribute   BINARY
);

CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;

CREATE TABLE collection_items (
    id            INT PRIMARY KEY NOT NULL,
    collection_id INT             NOT NULL REFERENCES collections(id),
    artifact_id   INT             NOT NULL REFERENCES artifacts(id),
    attribute     BINARY,
    creation      TIMESTAMP       NOT NULL,
    UNIQUE (collection_id, artifact_id)
);

CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
    ON artifacts FOR EACH ROW 
    CALL "de.intevation.artifactdatabase.h2.CollectionAccessUpdateTrigger";

COMMIT;

http://dive4elements.wald.intevation.org