view contrib/make_flys_release/h2/artifacts-h2.sql @ 4380:19754e5227c8

Add facets for fixanalysis data from the datacage Add facets for fixanalysis data from the datacage to the bed difference height year, bed difference year and bed difference epoch output modes.
author Björn Ricks <bjoern.ricks@intevation.de>
date Fri, 02 Nov 2012 15:54:41 +0100
parents 9d404069f361
children
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