Mercurial > dive4elements > river

annotate flys-artifacts/doc/conf/datacage.sql @ 4380:19754e5227c8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add facets for fixanalysis data from the datacage Add facets for fixanalysis data from the datacage to the bed difference height year, bed difference year and bed difference epoch output modes.
author Björn Ricks <bjoern.ricks@intevation.de>
date Fri, 02 Nov 2012 15:54:41 +0100
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;
http://dive4elements.wald.intevation.org