Mercurial > dive4elements > river
view contrib/make_flys_release/h2/datacage.sql @ 4276:0aa42c9d58e9
Documentation in ChartGenerator added.
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Fri, 26 Oct 2012 16:41:36 +0200 |
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;