Mercurial > dive4elements > river
annotate contrib/make_flys_release/h2/datacage.sql @ 4984:6baf466e8cb5
GaugeInfoHead: Removed (not used anymore).
author | Felix Wolfsteller <felix.wolfsteller@intevation.de> |
---|---|
date | Thu, 14 Feb 2013 09:56:44 +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; |