Mercurial > dive4elements > river
annotate artifacts/doc/conf/datacage.sql @ 7537:b1c5a567d44b
Add units to bedquality exporter columns.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 07 Nov 2013 19:31:16 +0100 |
parents | 6d55614d6f87 |
children |
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, |
6609
6d55614d6f87
Change artifact database v param to be type TEXT
Andre Heinecke <aheinecke@intevation.de>
parents:
5838
diff
changeset
|
44 v TEXT, |
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 |
4901
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
88 CREATE VIEW master_artifacts_range AS |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
89 SELECT ma.id AS id, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
90 ma.gid AS gid, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
91 ma.state AS state, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
92 ma.creation AS creation, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
93 ma.collection_id AS collection_id, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
94 mam.ld_mode AS ld_mode, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
95 mal.ld_locations AS ld_locations, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
96 maf.ld_from AS ld_from, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
97 mat.ld_to AS ld_to |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
98 FROM master_artifacts ma |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
99 LEFT JOIN (SELECT ad.v AS ld_mode, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
100 ad.artifact_id AS artifact_id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
101 FROM artifact_data ad |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
102 WHERE ad.k = 'ld_mode') mam |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
103 ON mam.artifact_id = ma.id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
104 LEFT JOIN (SELECT ad.v AS ld_locations, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
105 ad.artifact_id AS artifact_id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
106 FROM artifact_data ad |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
107 WHERE ad.k = 'ld_locations') mal |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
108 ON mal.artifact_id = ma.id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
109 LEFT JOIN (SELECT ad.v AS ld_from, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
110 ad.artifact_id AS artifact_id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
111 FROM artifact_data ad |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
112 WHERE ad.k = 'ld_from') maf |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
113 ON maf.artifact_id = ma.id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
114 LEFT JOIN (SELECT ad.v AS ld_to, |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
115 ad.artifact_id AS artifact_id |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
116 FROM artifact_data ad |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
117 WHERE ad.k = 'ld_to') mat |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
118 ON mat.artifact_id = ma.id; |
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
119 |
1044
a007a4368768
Datacage: Added view to schema to select master artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1025
diff
changeset
|
120 -- DROP VIEW master_artifacts; |
4901
1071aacd042c
Added view to datacage db to get location/distance information.
Raimund Renkert <rrenkert@intevation.de>
parents:
1044
diff
changeset
|
121 -- DROP VIEW master_artifacts_range; |
985
8094ba4ab873
Datacage SQL schema: Using sequences for id generation now
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
981
diff
changeset
|
122 -- 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
|
123 -- 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
|
124 -- 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
|
125 -- 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
|
126 -- 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
|
127 -- 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
|
128 -- 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
|
129 -- DROP TABLE facets; |
981
799c7108ea6d
datacage: prepared access to outs of artifacts.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
980
diff
changeset
|
130 -- DROP TABLE outs; |
980
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
131 -- DROP TABLE artifact_data; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
132 -- DROP TABLE collection_items; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
133 -- DROP TABLE collections; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
134 -- DROP TABLE artifacts; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
135 -- DROP TABLE users; |
f9a6a9cd918e
Datacage: Added ON DELETE CASCADE constraints in schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
979
diff
changeset
|
136 |
979
2306340d7540
Added schema for datacage db
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
137 COMMIT; |