Mercurial > gemma

comparison wamos.sql @ 59:37faa7359ea6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Interfaces may provide timestamps with time zone. Storing the time zone seems easier than normalising everything to UTC at application level.
author Tom Gottfried <tom@intevation.de>
date Thu, 24 May 2018 14:41:41 +0200
parents 30cb2f87c268
children f0ca64da9446
comparison
equal deleted inserted replaced
58:30cb2f87c268 59:37faa7359ea6
110 -- XXX: Also an attribut of sounding result? 110 -- XXX: Also an attribut of sounding result?
111 -- CHECK allowed combinations of surtyp and coverage or 111 -- CHECK allowed combinations of surtyp and coverage or
112 -- different model approach? 112 -- different model approach?
113 depth_reference char(3) NOT NULL REFERENCES depth_references, 113 depth_reference char(3) NOT NULL REFERENCES depth_references,
114 -- XXX: Also an attribut of sounding result? 114 -- XXX: Also an attribut of sounding result?
115 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 115 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
116 source_organization varchar NOT NULL 116 source_organization varchar NOT NULL
117 -- additional_data xml -- Currently not relevant for WAMOS 117 -- additional_data xml -- Currently not relevant for WAMOS
118 ); 118 );
119 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks 119 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
120 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); 120 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
158 surdat date NOT NULL, 158 surdat date NOT NULL,
159 PRIMARY KEY (bottleneck_id, surdat), 159 PRIMARY KEY (bottleneck_id, surdat),
160 position varchar REFERENCES spot_marks, 160 position varchar REFERENCES spot_marks,
161 -- additional_data xml -- Currently not relevant for WAMOS 161 -- additional_data xml -- Currently not relevant for WAMOS
162 critical boolean, 162 critical boolean,
163 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 163 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
164 source_organization varchar NOT NULL 164 source_organization varchar NOT NULL
165 ); 165 );
166 CREATE TRIGGER fairway_availability_date_info 166 CREATE TRIGGER fairway_availability_date_info
167 BEFORE UPDATE ON fairway_availability 167 BEFORE UPDATE ON fairway_availability
168 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); 168 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
185 surdat date NOT NULL, 185 surdat date NOT NULL,
186 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, 186 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
187 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL 187 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
188 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? 188 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
189 PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url), 189 PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url),
190 pdf_generation_date timestamp NOT NULL, 190 pdf_generation_date timestamp with time zone NOT NULL,
191 source_organization varchar NOT NULL 191 source_organization varchar NOT NULL
192 ); 192 );
193 193
194 CREATE TABLE effective_fairway_availability ( 194 CREATE TABLE effective_fairway_availability (
195 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 195 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
196 surdat date NOT NULL, 196 surdat date NOT NULL,
197 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, 197 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
198 measure_date timestamp NOT NULL, 198 measure_date timestamp with time zone NOT NULL,
199 level_of_service smallint NOT NULL REFERENCES levels_of_service, 199 level_of_service smallint NOT NULL REFERENCES levels_of_service,
200 PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service), 200 PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service),
201 available_depth_value smallint, 201 available_depth_value smallint,
202 available_width_value smallint, 202 available_width_value smallint,
203 water_level_value smallint, 203 water_level_value smallint,
204 CHECK(COALESCE(available_depth_value, available_width_value, 204 CHECK(COALESCE(available_depth_value, available_width_value,
205 water_level_value) IS NOT NULL), 205 water_level_value) IS NOT NULL),
206 measure_type varchar NOT NULL REFERENCES measure_types, 206 measure_type varchar NOT NULL REFERENCES measure_types,
207 source_organization varchar NOT NULL, 207 source_organization varchar NOT NULL,
208 forecast_generation_time timestamp, 208 forecast_generation_time timestamp with time zone,
209 CHECK(measure_type <> 'forecasted' 209 CHECK(measure_type <> 'forecasted'
210 OR forecast_generation_time IS NOT NULL), 210 OR forecast_generation_time IS NOT NULL),
211 value_lifetime timestamp, 211 value_lifetime timestamp with time zone,
212 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) 212 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
213 ); 213 );
214 214
215 COMMIT; 215 COMMIT;