Mercurial > dive4elements > river

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
rev   line source
4065
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 BEGIN;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
3 CREATE SEQUENCE USERS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
4
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 CREATE TABLE users (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
6 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 gid UUID NOT NULL UNIQUE
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
8 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
9
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
10 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
11
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
12 CREATE TABLE collections (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
14 gid UUID NOT NULL UNIQUE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
15 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
16 name VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
17 creation TIMESTAMP NOT NULL
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
18 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
20 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
21
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22 CREATE TABLE artifacts (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
23 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
24 gid UUID NOT NULL UNIQUE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
25 state VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
26 creation TIMESTAMP NOT NULL
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
27 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
28
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
29 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
30
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
31 CREATE TABLE collection_items (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
33 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
35 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
36
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
37 CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39 CREATE TABLE artifact_data (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
41 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
42 kind VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43 k VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
44 v VARCHAR(256), -- Maybe too short
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45 UNIQUE (artifact_id, k)
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 CREATE SEQUENCE OUTS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
49
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
50 CREATE TABLE outs (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
52 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
53 name VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
54 description VARCHAR(256),
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
55 out_type VARCHAR(256)
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58 CREATE SEQUENCE FACETS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
60 CREATE TABLE facets (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
61 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
62 out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
63 name VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
64 num INT NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
65 state VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
66 description VARCHAR(256),
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
67 UNIQUE (out_id, num, name)
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
68 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
69
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
70 CREATE VIEW master_artifacts AS
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
71 SELECT a2.id AS id,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
72 a2.gid AS gid,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
73 a2.state AS state,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
74 a2.creation AS creation,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
75 ci2.collection_id AS collection_id
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
76 FROM collection_items ci2
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
77 JOIN artifacts a2
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
78 ON ci2.artifact_id = a2.id
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
79 JOIN (SELECT ci.collection_id AS c_id,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
80 MIN(a.creation) AS oldest_a
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
81 FROM collection_items ci
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
82 JOIN artifacts a
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
83 ON ci.artifact_id = a.id
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
84 GROUP BY ci.collection_id) o
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
85 ON o.c_id = ci2.collection_id
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
86 WHERE a2.creation = o.oldest_a;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
87
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
88 -- DROP VIEW master_artifacts;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
89 -- DROP SEQUENCE USERS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
90 -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
91 -- DROP SEQUENCE ARTIFACTS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
92 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
93 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
94 -- DROP SEQUENCE OUTS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
95 -- DROP SEQUENCE FACETS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
96 -- DROP TABLE facets;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
97 -- DROP TABLE outs;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
98 -- DROP TABLE artifact_data;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
99 -- DROP TABLE collection_items;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
100 -- DROP TABLE collections;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
101 -- DROP TABLE artifacts;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
102 -- DROP TABLE users;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
103
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
104 COMMIT;
http://dive4elements.wald.intevation.org