view contrib/make_flys_release/h2/datacage.sql @ 4377:512a3af69e18

Extract common generator method from FixLongitudinalSectionGenerator The extracted methods and code can be reused for generating charts for bed differences if fixanalysis data is loaded via the datacache.
author Björn Ricks <bjoern.ricks@intevation.de>
date Fri, 02 Nov 2012 15:47:15 +0100
parents 9d404069f361
children
line wrap: on
line source
BEGIN;

CREATE SEQUENCE USERS_ID_SEQ;

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

CREATE SEQUENCE COLLECTIONS_ID_SEQ;

CREATE TABLE collections (
    id       INT  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,
    creation TIMESTAMP        NOT NULL
);

CREATE SEQUENCE ARTIFACTS_ID_SEQ;

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

CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;

CREATE TABLE collection_items (
    id            INT 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 SEQUENCE ARTIFACT_DATA_ID_SEQ;

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

CREATE SEQUENCE OUTS_ID_SEQ;

CREATE TABLE outs (
    id          INT 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 SEQUENCE FACETS_ID_SEQ;

CREATE TABLE facets (
    id          INT PRIMARY KEY NOT NULL,
    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 (out_id, num, name)
);

CREATE VIEW master_artifacts AS
    SELECT a2.id             AS id,
           a2.gid            AS gid,
           a2.state          AS state,
           a2.creation       AS creation,
           ci2.collection_id AS collection_id
    FROM   collection_items ci2 
           JOIN artifacts a2 
             ON ci2.artifact_id = a2.id 
           JOIN (SELECT ci.collection_id AS c_id, 
                        MIN(a.creation)  AS oldest_a 
                 FROM   collection_items ci 
                        JOIN artifacts a 
                          ON ci.artifact_id = a.id 
                 GROUP  BY ci.collection_id) o 
             ON o.c_id = ci2.collection_id 
    WHERE  a2.creation = o.oldest_a;

-- DROP VIEW master_artifacts;
-- DROP SEQUENCE USERS_ID_SEQ;
-- DROP SEQUENCE COLLECTIONS_ID_SEQ;
-- DROP SEQUENCE ARTIFACTS_ID_SEQ;
-- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
-- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
-- DROP SEQUENCE OUTS_ID_SEQ;
-- DROP SEQUENCE FACETS_ID_SEQ;
-- 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