Mercurial > dive4elements > river

annotate artifacts/doc/conf/datacage.sql @ 6045:2d16f4a0bdcc

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Schema change: there is only one bed height per station at a given point in time
author Tom Gottfried <tom@intevation.de>
date Thu, 16 May 2013 18:03:39 +0200
parents 5aa05a7a34b7
children 6d55614d6f87
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
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;
http://dive4elements.wald.intevation.org