view flys-artifacts/doc/conf/datacage.sql @ 2792:fe987587ebc9

Merged revisions 4539-4540,4543,4545-4546 via svnmerge from file:///home/clients/bsh/bsh-generischer-viewer/Material/SVN/flys-artifacts/trunk ........ r4539 | teichmann | 2012-05-27 20:02:13 +0200 (So, 27 Mai 2012) | 1 line FixA: Added forgotten csv/report facets/generators to conf. ........ r4540 | teichmann | 2012-05-27 20:11:31 +0200 (So, 27 Mai 2012) | 1 line FixA: Fixed class cast bug in report facet. ........ r4543 | teichmann | 2012-05-28 20:35:01 +0200 (Mo, 28 Mai 2012) | 1 line FixA: Added facet to return delta w/t as CSV ........ r4545 | teichmann | 2012-05-28 22:59:27 +0200 (Mo, 28 Mai 2012) | 1 line FixA: Made Delta W/t calculation finally work ........ r4546 | teichmann | 2012-05-28 23:34:24 +0200 (Mo, 28 Mai 2012) | 1 line FixA: corrected fitting (Q->W instead W->Q). ........ flys-artifacts/tags/2.7@4547 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Tue, 29 May 2012 04:58:29 +0000
parents a007a4368768
children 1071aacd042c
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