Mercurial > dive4elements > river
annotate flys-artifacts/doc/conf/datacage.sql @ 1059:fdb0f4ef96f0
Made FLYS artifacts cloneable.
flys-artifacts/trunk@2531 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 23 Aug 2011 11:07:21 +0000 |
parents | a007a4368768 |
children | 1071aacd042c |
rev | line source |
---|---|
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 BEGIN; |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
3 CREATE SEQUENCE USERS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
4 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
5 CREATE TABLE users ( |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
6 id INT PRIMARY KEY NOT NULL, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
7 gid UUID NOT NULL UNIQUE |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
8 ); |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
9 |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
10 CREATE SEQUENCE COLLECTIONS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
11 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
12 CREATE TABLE collections ( |
1025
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
13 id INT PRIMARY KEY NOT NULL, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
14 gid UUID NOT NULL UNIQUE, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
15 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
16 name VARCHAR(256) NOT NULL, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
17 creation TIMESTAMP NOT NULL |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
18 ); |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
19 |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
20 CREATE SEQUENCE ARTIFACTS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
21 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
22 CREATE TABLE artifacts ( |
1025
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
23 id INT PRIMARY KEY NOT NULL, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
24 gid UUID NOT NULL UNIQUE, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
25 state VARCHAR(256) NOT NULL, |
02c327ffbad7
Datacage: Extended schema for artifacts and collections
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
993
diff
changeset
|
26 creation TIMESTAMP NOT NULL |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
27 ); |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
28 |
988
dbe39e1fb5e7
Datacage: Add collection items at initial scan.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
985
diff
changeset
|
29 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ; |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
30 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
31 CREATE TABLE collection_items ( |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
32 id INT PRIMARY KEY NOT NULL, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
33 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
34 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
35 ); |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
36 |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
37 CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
38 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 CREATE TABLE artifact_data ( |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
40 id INT PRIMARY KEY NOT NULL, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
41 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, |
990
e573c4715d81
Add artifacts data into datacage db at initial scan.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
988
diff
changeset
|
42 kind VARCHAR(256) NOT NULL, |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
43 k VARCHAR(256) NOT NULL, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
44 v VARCHAR(256), -- Maybe too short |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
45 UNIQUE (artifact_id, k) |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
46 ); |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
48 CREATE SEQUENCE OUTS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
49 |
981
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
50 CREATE TABLE outs ( |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
51 id INT PRIMARY KEY NOT NULL, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
52 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
53 name VARCHAR(256) NOT NULL, |
981
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
54 description VARCHAR(256), |
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
55 out_type VARCHAR(256) |
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
56 ); |
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
57 |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
58 CREATE SEQUENCE FACETS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
59 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
60 CREATE TABLE facets ( |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
61 id INT PRIMARY KEY NOT NULL, |
992
5b467bc27eda
Datacage: Store
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
990
diff
changeset
|
62 out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE, |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
63 name VARCHAR(256) NOT NULL, |
992
5b467bc27eda
Datacage: Store
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
990
diff
changeset
|
64 num INT NOT NULL, |
5b467bc27eda
Datacage: Store
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
990
diff
changeset
|
65 state VARCHAR(256) NOT NULL, |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
66 description VARCHAR(256), |
993
aabcca7aeb6c
Fixed datacage db scheme. add some debug output.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
992
diff
changeset
|
67 UNIQUE (out_id, num, name) |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
68 ); |
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
69 |
1044
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
70 CREATE VIEW master_artifacts AS |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
71 SELECT a2.id AS id, |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
72 a2.gid AS gid, |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
73 a2.state AS state, |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
74 a2.creation AS creation, |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
75 ci2.collection_id AS collection_id |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
76 FROM collection_items ci2 |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
77 JOIN artifacts a2 |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
78 ON ci2.artifact_id = a2.id |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
79 JOIN (SELECT ci.collection_id AS c_id, |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
80 MIN(a.creation) AS oldest_a |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
81 FROM collection_items ci |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
82 JOIN artifacts a |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
83 ON ci.artifact_id = a.id |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
84 GROUP BY ci.collection_id) o |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
85 ON o.c_id = ci2.collection_id |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
86 WHERE a2.creation = o.oldest_a; |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
87 |
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
88 -- DROP VIEW master_artifacts; |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
89 -- DROP SEQUENCE USERS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
90 -- DROP SEQUENCE COLLECTIONS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
91 -- DROP SEQUENCE ARTIFACTS_ID_SEQ; |
988
dbe39e1fb5e7
Datacage: Add collection items at initial scan.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
985
diff
changeset
|
92 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ; |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
93 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
94 -- DROP SEQUENCE OUTS_ID_SEQ; |
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
95 -- DROP SEQUENCE FACETS_ID_SEQ; |
980
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
96 -- DROP TABLE facets; |
981
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
97 -- DROP TABLE outs; |
980
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
98 -- DROP TABLE artifact_data; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
99 -- DROP TABLE collection_items; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
100 -- DROP TABLE collections; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
101 -- DROP TABLE artifacts; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
102 -- DROP TABLE users; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
103 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
104 COMMIT; |