Mercurial > gemma
annotate schema/gemma.sql @ 193:1585c334e8a7
More on persisting sessions.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 20 Jul 2018 18:32:11 +0200 |
parents | bc7829defa99 |
children | 5dc8e734487a |
rev | line source |
---|---|
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 BEGIN; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
3 -- |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
4 -- Infrastructure |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
5 -- |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 CREATE EXTENSION postgis; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
8 -- TODO: will there ever be UPDATEs or can we drop that function due to |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
9 -- historicisation? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 CREATE FUNCTION update_date_info() RETURNS trigger |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 LANGUAGE plpgsql |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 AS $$ |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 BEGIN |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 NEW.date_info = CURRENT_TIMESTAMP; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 RETURN NEW; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 END; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 $$; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
19 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. |
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
20 -- See RIS-Index Encoding Guide |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 CREATE TYPE isrs AS ( |
182 | 22 country_code char(2), -- ISO 3166 country code |
23 -- could be validated against countries table. | |
24 locode char(3), -- without the country code: | |
25 -- http://www.unece.org/cefact/locode/welcome.html | |
26 fairway_section char(5), | |
27 object_reference char(5), | |
28 hectometre int -- should be constrained to five digits | |
37
6f273a649f08
Be more specific with ISRS and update some comments.
Tom Gottfried <tom@intevation.de>
parents:
35
diff
changeset
|
29 ); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 CREATE TYPE isrsrange AS RANGE ( |
182 | 32 subtype = isrs |
33 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
35 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
36 -- GEMMA data |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
37 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
38 CREATE SCHEMA gemma; |
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
39 CREATE SCHEMA gemma_waterway; |
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
40 CREATE SCHEMA gemma_fairway; |
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
41 SET search_path TO public, gemma, gemma_waterway, gemma_fairway; |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
42 -- TODO: consolidate schemas. The current distribution of tables is mainly |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
43 -- for diagram generation. A privilege based distribution might ease |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
44 -- privilege management. |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
45 |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
46 -- |
83 | 47 -- Auxiliary tables |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
48 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
49 CREATE TABLE gemma.system_config ( |
182 | 50 config_key varchar PRIMARY KEY, |
51 config_val varchar | |
52 ); | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
53 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
54 CREATE TABLE gemma.countries ( |
182 | 55 country_code char(2) PRIMARY KEY -- ISO 3166 country code |
56 -- A smallint PK would require even less disk space i.e. on the FK side. | |
57 -- This might be an issue in case cache space becomes a limiting | |
58 -- factor when there are many FKs pointing here. | |
59 ); | |
82 | 60 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
61 CREATE TABLE gemma.responsibility_areas ( |
182 | 62 country char(2) PRIMARY KEY REFERENCES countries, |
63 area geometry(MULTIPOLYGON, 4326) | |
64 --XXX: Should be geography (elsewhere too) | |
65 ); | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
66 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
67 CREATE TABLE gemma.language_codes ( |
182 | 68 language_code varchar PRIMARY KEY |
69 ); | |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
70 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
71 CREATE TABLE gemma.user_profiles ( |
182 | 72 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger |
73 country char(2) NOT NULL REFERENCES countries, | |
74 map_extent box2d, | |
75 email_adress varchar NOT NULL UNIQUE | |
76 ); | |
82 | 77 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
78 CREATE TABLE gemma.templates ( |
182 | 79 template_name varchar PRIMARY KEY, |
80 template_data bytea NOT NULL, | |
81 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP | |
82 ); | |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
135
diff
changeset
|
83 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates |
182 | 84 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
82 | 85 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
86 CREATE TABLE gemma.user_templates ( |
182 | 87 username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE, |
88 template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE, | |
89 PRIMARY KEY (username, template_name) | |
90 ); | |
82 | 91 |
92 -- | |
93 -- General river information | |
94 -- | |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
95 |
53 | 96 -- Eventually obsolete. |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
97 -- See https://roundup-intern.intevation.de/gemma/issue5 |
62
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
98 -- CREATE TABLE rwdrs ( |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
99 -- stretch isrsrange PRIMARY KEY, |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
100 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html: |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
101 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
102 -- -- below anyhow. |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
103 -- -- Is it a good idea to build B-tree indexes on relatively large |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
104 -- -- composites of string values or should we use inter PKs? |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
105 -- -- => In case the index is used and cache space becomes a limiting |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
106 -- -- factor, this might be an issue. |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
107 -- rwdr double precision NOT NULL, |
39e0e3d8dd1b
Comment potentially obsolete table out.
Tom Gottfried <tom@intevation.de>
parents:
60
diff
changeset
|
108 -- EXCLUDE USING GIST (stretch WITH &&) |
182 | 109 --); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
111 CREATE TABLE gemma_waterway.catccls ( |
182 | 112 catccl smallint PRIMARY KEY |
113 -- TODO: Do we need name and/or definition from IENC feature catalogue? | |
114 ); | |
85
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
115 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
116 CREATE TABLE gemma_waterway.dirimps ( |
182 | 117 dirimp smallint PRIMARY KEY |
118 -- TODO: Do we need name and/or definition from IENC feature catalogue? | |
119 ); | |
85
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
120 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
121 CREATE TABLE gemma_waterway.waterway_area ( |
182 | 122 area geometry(POLYGON, 4326) PRIMARY KEY, |
123 catccl smallint REFERENCES catccls, | |
124 dirimp smallint REFERENCES dirimps | |
125 ); | |
69 | 126 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
127 CREATE TABLE gemma_fairway.depth_references ( |
182 | 128 depth_reference varchar(4) PRIMARY KEY |
129 -- See col. AB and AI RIS-Index Encoding Guide | |
130 -- XXX: We need a way to distinguish between geodetic (eg. col. AP | |
131 -- RIS-Index) and other references (e.g. col. AB and AI): | |
132 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; | |
133 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. | |
134 -- _ Do not mixup things with different meanings in one table at all | |
135 -- (which would mean a model differing a bit from RIS-Index ideas) | |
136 ); | |
93 | 137 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
138 CREATE TABLE gemma_fairway.reference_water_levels ( |
182 | 139 reference_water_level varchar(20) PRIMARY KEY |
140 ); | |
70
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
141 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
142 CREATE TABLE gemma_fairway.gauges ( |
182 | 143 location isrs PRIMARY KEY, |
144 function_code varchar(10) NOT NULL, -- XXX: What is this really for? | |
145 objname varchar NOT NULL, | |
146 is_left boolean, -- XXX: Or reference position_codes? | |
147 geom geometry(POINT, 4326) NOT NULL, | |
148 applicability isrsrange, | |
149 validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy | |
150 -- pasted text from a more general specification is given | |
151 -- (a gauge is not a berth!) | |
152 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps? | |
153 zero_point double precision NOT NULL, | |
154 geodref varchar(4) REFERENCES depth_references, | |
155 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
156 source_organization varchar NOT NULL | |
157 ); | |
93 | 158 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges |
182 | 159 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
160 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
161 CREATE TABLE gemma_fairway.gauges_reference_water_levels ( |
182 | 162 gauge_id isrs NOT NULL REFERENCES gauges, |
163 reference_water_level varchar(20) | |
164 NOT NULL REFERENCES reference_water_levels, | |
165 PRIMARY KEY (gauge_id, reference_water_level), | |
166 value int NOT NULL | |
167 ); | |
70
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
168 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
169 CREATE TABLE gemma_fairway.gauge_measurements ( |
182 | 170 fk_gauge_id isrs NOT NULL REFERENCES gauges, |
171 measure_date timestamp with time zone NOT NULL, | |
172 PRIMARY KEY (fk_gauge_id, measure_date), | |
173 -- XXX: Is country_code really relevant for GEMMA or just NtS? | |
174 -- country_code char(2) NOT NULL REFERENCES countries, | |
175 -- TODO: add relations to stuff provided as enumerations | |
176 sender varchar NOT NULL, -- "from" attribute from DRC | |
177 language_code varchar NOT NULL REFERENCES language_codes, | |
178 date_issue timestamp with time zone NOT NULL, | |
179 -- reference_code varchar(4) NOT NULL REFERENCES depth_references, | |
180 -- XXX: Always ZPG? | |
181 water_level double precision NOT NULL, | |
182 predicted boolean NOT NULL, | |
183 is_waterlevel boolean NOT NULL, | |
184 -- XXX: "measure_code" if really only W or Q | |
185 -- XXX: Do we need "unit" attribute or can we normalise on import? | |
186 value_min double precision, -- XXX: NOT NULL if predicted? | |
187 value_max double precision, -- XXX: NOT NULL if predicted? | |
188 --- TODO: Add a double range type for checking? | |
189 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
190 source_organization varchar NOT NULL -- "originator" | |
191 ); | |
94
611dc09dbcd8
Add gauge measurement attributes.
Tom Gottfried <tom@intevation.de>
parents:
93
diff
changeset
|
192 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements |
182 | 193 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
72
158f776732c7
Add gauge measurements stub.
Tom Gottfried <tom@intevation.de>
parents:
71
diff
changeset
|
194 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
195 CREATE TABLE gemma_waterway.waterway_axis ( |
182 | 196 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY, |
197 -- TODO: Do we need to check data set quality as described in DRC 2.1.6? | |
198 objnam varchar NOT NULL, | |
199 nobjnam varchar | |
200 ); | |
73 | 201 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
202 CREATE TABLE gemma_waterway.distance_mark_functions ( |
182 | 203 -- XXX: Redundant information to object code in isrs code of dist. mark |
204 distance_mark_function varchar(8) PRIMARY KEY | |
205 ); | |
78
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
206 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
207 CREATE TABLE gemma_waterway.position_codes ( |
182 | 208 position_code char(2) PRIMARY KEY |
209 -- Use smallint because of fairway availability provided on daily basis? | |
210 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, | |
211 -- sheet "Position_code" or RIS-Index encoding guide? | |
212 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. | |
213 -- Clarify! | |
214 -- TODO: Do we need an attribute "meaning" or so? | |
215 ); | |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
216 |
78
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
217 -- This table allows linkage between the 1D ISRS location codes and 2D space |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
218 -- e.g. for cutting bottleneck area out of waterway area based on virtual |
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
219 -- distance marks along waterway axis (see SUC7). |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
220 CREATE TABLE gemma_waterway.distance_marks ( |
182 | 221 location_code isrs PRIMARY KEY, |
222 geom geometry(POINT, 4326) NOT NULL, | |
223 distance_mark_function varchar(8) | |
224 NOT NULL REFERENCES distance_mark_functions, | |
225 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem | |
226 -- to encode the same thing as the object code in ISRS location code. | |
227 position_code char(2) NOT NULL REFERENCES position_codes | |
228 ); | |
75
ed7a50cfbc19
Add sections and stretches stub and fix comment.
Tom Gottfried <tom@intevation.de>
parents:
74
diff
changeset
|
229 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
230 CREATE TABLE gemma_waterway.sections_stretches ( |
182 | 231 id varchar PRIMARY KEY, |
232 is_section boolean NOT NULL, -- maps 'function' from interface | |
233 stretch isrsrange, | |
234 objnam varchar NOT NULL, | |
235 nobjnam varchar, | |
236 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
237 source_organization varchar NOT NULL | |
238 ); | |
88
a96fee9654db
Add sections and stretches attributes.
Tom Gottfried <tom@intevation.de>
parents:
87
diff
changeset
|
239 CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches |
182 | 240 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
74 | 241 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
242 CREATE TABLE gemma_waterway.waterway_profiles ( |
182 | 243 location isrs NOT NULL, |
244 validity tstzrange, | |
245 EXCLUDE USING GIST (validity WITH &&), | |
246 PRIMARY KEY (location, validity), | |
247 lnwl smallint, | |
248 mwl smallint, | |
249 hnwl smallint, | |
250 fe30 smallint, | |
251 fe100 smallint, | |
252 -- XXX: further normalise using reference_water_levels? | |
253 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL | |
254 OR validity IS NOT NULL), | |
255 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
256 source_organization varchar NOT NULL | |
257 ); | |
91 | 258 CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles |
182 | 259 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
79
c22ddffc2071
Add waterway profiles stub.
Tom Gottfried <tom@intevation.de>
parents:
78
diff
changeset
|
260 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
261 CREATE TABLE gemma_waterway.levels_of_service ( |
182 | 262 level_of_service smallint PRIMARY KEY |
263 ); | |
89
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
264 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
265 CREATE TABLE gemma_waterway.fairway_dimensions ( |
182 | 266 area geometry(POLYGON, 4326) PRIMARY KEY, |
267 level_of_service smallint NOT NULL REFERENCES levels_of_service, | |
268 min_width smallint NOT NULL, | |
269 max_width smallint NOT NULL, | |
270 min_depth smallint NOT NULL, | |
271 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
272 source_organization varchar NOT NULL | |
273 ); | |
90 | 274 CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions |
182 | 275 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
89
a36bfec5edd3
Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents:
88
diff
changeset
|
276 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
277 -- |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
278 -- Bottlenecks |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
279 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
280 CREATE TABLE gemma_fairway.riverbed_materials ( |
182 | 281 material varchar PRIMARY KEY |
282 -- XXX: Should this table contain choices from DRC 2.2.3 or | |
283 -- from IENC Encoding Guide M.4.3, attribute NATSUR? | |
284 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
285 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
286 CREATE TABLE gemma_fairway.survey_types ( |
182 | 287 survey_type varchar PRIMARY KEY |
288 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
289 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
290 CREATE TABLE gemma_fairway.coverage_types ( |
182 | 291 coverage_type varchar PRIMARY KEY |
292 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
293 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
294 CREATE TABLE gemma_fairway.limiting_factors ( |
182 | 295 limiting_factor varchar PRIMARY KEY |
296 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
297 |
55
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
298 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL |
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
299 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and |
ecb9bb0591c9
Note on spurious nullability.
Tom Gottfried <tom@intevation.de>
parents:
54
diff
changeset
|
300 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
301 CREATE TABLE gemma_fairway.bottlenecks ( |
182 | 302 bottleneck_id varchar PRIMARY KEY, |
303 fk_g_fid isrs NOT NULL REFERENCES gauges, | |
304 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. | |
305 objnam varchar, | |
306 nobjnm varchar, | |
307 stretch isrsrange NOT NULL, | |
308 area geometry(POLYGON, 4326) NOT NULL, | |
309 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface | |
310 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface | |
311 responsible_country char(2) NOT NULL REFERENCES countries, | |
312 revisiting_time smallint NOT NULL, | |
313 limiting varchar NOT NULL REFERENCES limiting_factors, | |
314 -- surtyp varchar NOT NULL REFERENCES survey_types, | |
315 -- XXX: Also an attribut of sounding result? | |
316 -- coverage varchar REFERENCES coverage_types, | |
317 -- XXX: Also an attribut of sounding result? | |
318 -- CHECK allowed combinations of surtyp and coverage or | |
319 -- different model approach? | |
320 -- depth_reference char(3) NOT NULL REFERENCES depth_references, | |
321 -- XXX: Also an attribut of sounding result? | |
322 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
323 source_organization varchar NOT NULL, | |
324 -- additional_data xml -- Currently not relevant for GEMMA | |
325 staging_done boolean NOT NULL DEFAULT false | |
326 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
327 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks |
182 | 328 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
329 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
330 CREATE TABLE gemma_fairway.bottlenecks_riverbed_materials ( |
182 | 331 bottleneck_id varchar REFERENCES bottlenecks, |
332 riverbed varchar REFERENCES riverbed_materials, | |
333 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 | |
334 PRIMARY KEY (bottleneck_id, riverbed) | |
335 ); | |
44
dcc3b1134b24
Multiple riverbed materials per bottleneck possible.
Tom Gottfried <tom@intevation.de>
parents:
43
diff
changeset
|
336 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
337 CREATE TABLE gemma_fairway.sounding_results ( |
182 | 338 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
339 date_info date NOT NULL, | |
340 PRIMARY KEY (bottleneck_id, date_info), | |
341 area geometry(POLYGON, 4326) NOT NULL, | |
342 surtyp varchar NOT NULL REFERENCES survey_types, | |
343 coverage varchar REFERENCES coverage_types, | |
344 depth_reference char(3) NOT NULL REFERENCES depth_references, | |
345 sounding_data raster NOT NULL, | |
346 staging_done boolean NOT NULL DEFAULT false | |
347 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
348 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
349 -- |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
350 -- Fairway availability |
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
351 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
352 CREATE TABLE gemma_fairway.measure_types ( |
182 | 353 measure_type varchar PRIMARY KEY |
354 ); | |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
355 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
356 CREATE TABLE gemma_fairway.fairway_availability ( |
182 | 357 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
358 position_code char(2) REFERENCES position_codes, | |
359 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, | |
360 surdat date NOT NULL, | |
361 UNIQUE (bottleneck_id, surdat), | |
362 -- additional_data xml -- Currently not relevant for GEMMA | |
363 critical boolean, | |
364 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
365 source_organization varchar NOT NULL | |
366 ); | |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
367 CREATE TRIGGER fairway_availability_date_info |
182 | 368 BEFORE UPDATE ON fairway_availability |
369 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); | |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
370 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
371 CREATE TABLE gemma_fairway.fa_reference_values ( |
182 | 372 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
373 level_of_service smallint NOT NULL REFERENCES levels_of_service, | |
374 PRIMARY KEY (fairway_availability_id, level_of_service), | |
375 fairway_depth smallint, | |
376 fairway_width smallint, | |
377 fairway_radius int, | |
378 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), | |
379 shallowest_spot geometry(POINT, 4326) | |
380 ); | |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
381 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
382 CREATE TABLE gemma_fairway.bottleneck_pdfs ( |
182 | 383 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
384 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL | |
385 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? | |
386 PRIMARY KEY (fairway_availability_id, profile_pdf_url), | |
387 pdf_generation_date timestamp with time zone NOT NULL, | |
388 source_organization varchar NOT NULL | |
389 ); | |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
390 |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
391 CREATE TABLE gemma_fairway.effective_fairway_availability ( |
182 | 392 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
393 measure_date timestamp with time zone NOT NULL, | |
394 level_of_service smallint NOT NULL REFERENCES levels_of_service, | |
395 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), | |
396 available_depth_value smallint, | |
397 available_width_value smallint, | |
398 water_level_value smallint, | |
399 CHECK(COALESCE(available_depth_value, available_width_value, | |
400 water_level_value) IS NOT NULL), | |
401 measure_type varchar NOT NULL REFERENCES measure_types, | |
402 source_organization varchar NOT NULL, | |
403 forecast_generation_time timestamp with time zone, | |
404 CHECK(measure_type <> 'forecasted' | |
405 OR forecast_generation_time IS NOT NULL), | |
406 value_lifetime timestamp with time zone, | |
407 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) | |
408 ); | |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
409 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
410 COMMIT; |