view flys-artifacts/doc/conf/datacage.sql @ 983:4ea5f5a2284e

Ajusted datacage to use the SQL infrastructure from the artifact database. flys-artifacts/trunk@2413 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 28 Jul 2011 13:32:27 +0000
parents 799c7108ea6d
children 8094ba4ab873
line wrap: on
line source
BEGIN;

CREATE TABLE users (
    id  IDENTITY PRIMARY KEY NOT NULL,
    gid UUID                 NOT NULL UNIQUE
);

CREATE TABLE collections (
    id      IDENTITY PRIMARY KEY NOT NULL,
    gid     UUID                 NOT NULL UNIQUE,
    user_id INT                  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name    VARCHAR(256)         NOT NULL
);

CREATE TABLE artifacts (
    id    IDENTITY PRIMARY KEY NOT NULL,
    gid   UUID                 NOT NULL UNIQUE,
    state VARCHAR(256)         NOT NULL
);

CREATE TABLE collection_items (
    id            IDENTITY PRIMARY KEY NOT NULL,
    collection_id INT                  NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
    artifact_id   INT                  NOT NULL REFERENCES artifacts(id)   ON DELETE CASCADE
);

CREATE TABLE artifact_data (
    id          IDENTITY PRIMARY KEY NOT NULL,
    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
    k           VARCHAR(256)         NOT NULL,
    v           VARCHAR(256),         -- Maybe too short
    UNIQUE (artifact_id, k)
);

CREATE TABLE outs (
    id          IDENTITY PRIMARY KEY NOT NULL,
    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
    name        VARCHAR(256)         NOT NULL,
    description VARCHAR(256),
    out_type    VARCHAR(256)
);

CREATE TABLE facets (
    id          IDENTITY PRIMARY KEY NOT NULL,
    artifact_id INT                  NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
    out_id      INT                  NOT NULL REFERENCES outs(id)      ON DELETE CASCADE,
    name        VARCHAR(256)         NOT NULL,
    num         INT                  NOT NULL,
    state       VARCHAR(256)         NOT NULL,
    description VARCHAR(256),
    UNIQUE (artifact_id, output, num, name)
);

-- DROP TABLE facets;
-- DROP TABLE outs;
-- DROP TABLE artifact_data;
-- DROP TABLE collection_items;
-- DROP TABLE collections;
-- DROP TABLE artifacts;
-- DROP TABLE users;

COMMIT;

http://dive4elements.wald.intevation.org