Mercurial > dive4elements > river
view contrib/make_flys_release/h2/datacage.sql @ 4269:0c766c475805
Add Panel and Tree UI classes for dispayling the measurement station info
The new ui classes are using the new extracted base class InfoPanel and InfoTree
which are in common with the gauge info.
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Fri, 26 Oct 2012 12:22:06 +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;