Mercurial > dive4elements > river

annotate contrib/make_flys_release/h2/datacage.sql @ 4775:d9f1202cef78

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in AFT and 'discharge_table.bfg_id' in FLYS. TODO: Create new column in discharge_tabel.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sat, 05 Jan 2013 14:52:55 +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