view artifact-database/doc/schema-pg.sql @ 186:15c7638511dc

Added an interface for CollectionItems and renamed the CollectionItem class from the artifact-database package to DefaultCollectionItem - because this is the default implementation of the interface. artifacts/trunk@1416 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Mon, 07 Mar 2011 15:13:36 +0000
parents a76de72ad6d1
children a85d889a1b0d
line wrap: on
line source
--
-- schema to store artifacts in PostgreSQL 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        bytea
);

CREATE SEQUENCE USERS_ID_SEQ;

CREATE TABLE users (
    id   int PRIMARY KEY NOT NULL,
    gid  uuid            NOT NULL UNIQUE,
    name 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
);

CREATE SEQUENCE COLLECTION_ITEMS_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,
    UNIQUE (collection_id, artifact_id)
);

COMMIT;

http://dive4elements.wald.intevation.org