Mercurial > gemma

comparison wamos.sql @ 65:cad19acc53b3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid duplicate direct relations in composite primary keys.
author Tom Gottfried <tom@intevation.de>
date Fri, 25 May 2018 21:45:08 +0200
parents 1e9589cf9c00
children 21df0e95db4a
comparison
equal deleted inserted replaced
64:1e9589cf9c00 65:cad19acc53b3
171 CREATE TRIGGER fairway_availability_date_info 171 CREATE TRIGGER fairway_availability_date_info
172 BEFORE UPDATE ON fairway_availability 172 BEFORE UPDATE ON fairway_availability
173 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); 173 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
174 174
175 CREATE TABLE wamos.fa_reference_values ( 175 CREATE TABLE wamos.fa_reference_values (
176 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 176 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
177 surdat date NOT NULL, 177 fairway_availability_id NOT NULL REFERENCES fairway_availability,
178 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
179 level_of_service smallint NOT NULL REFERENCES levels_of_service, 178 level_of_service smallint NOT NULL REFERENCES levels_of_service,
180 PRIMARY KEY (bottleneck_id, surdat, level_of_service), 179 PRIMARY KEY (fairway_availability_id, level_of_service),
181 fairway_depth smallint, 180 fairway_depth smallint,
182 fairway_width smallint, 181 fairway_width smallint,
183 fairway_radius int, 182 fairway_radius int,
184 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), 183 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
185 shallowest_spot geometry(POINT, 3146) 184 shallowest_spot geometry(POINT, 3146)
186 ); 185 );
187 186
188 CREATE TABLE wamos.bottleneck_pdfs ( 187 CREATE TABLE wamos.bottleneck_pdfs (
189 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 188 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
190 surdat date NOT NULL, 189 fairway_availability_id NOT NULL REFERENCES fairway_availability,
191 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
192 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL 190 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
193 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? 191 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
194 PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url), 192 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
195 pdf_generation_date timestamp with time zone NOT NULL, 193 pdf_generation_date timestamp with time zone NOT NULL,
196 source_organization varchar NOT NULL 194 source_organization varchar NOT NULL
197 ); 195 );
198 196
199 CREATE TABLE wamos.effective_fairway_availability ( 197 CREATE TABLE wamos.effective_fairway_availability (
200 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 198 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
201 surdat date NOT NULL, 199 fairway_availability_id NOT NULL REFERENCES fairway_availability,
202 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
203 measure_date timestamp with time zone NOT NULL, 200 measure_date timestamp with time zone NOT NULL,
204 level_of_service smallint NOT NULL REFERENCES levels_of_service, 201 level_of_service smallint NOT NULL REFERENCES levels_of_service,
205 PRIMARY KEY (bottleneck_id, surdat, measure_date, level_of_service), 202 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
206 available_depth_value smallint, 203 available_depth_value smallint,
207 available_width_value smallint, 204 available_width_value smallint,
208 water_level_value smallint, 205 water_level_value smallint,
209 CHECK(COALESCE(available_depth_value, available_width_value, 206 CHECK(COALESCE(available_depth_value, available_width_value,
210 water_level_value) IS NOT NULL), 207 water_level_value) IS NOT NULL),