Mercurial > gemma

comparison schema/gemma.sql @ 345:b97b3172c61a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add staging feature to more tables Added tables currently only have limited visibility for waterway_user but not yet policies for write access.
author Tom Gottfried <tom@intevation.de>
date Mon, 06 Aug 2018 15:19:05 +0200
parents c6b32c6ae95e
children f5087cebc740
comparison
equal deleted inserted replaced
344:e98033e3683a 345:b97b3172c61a
247 -- XXX: Do we need "unit" attribute or can we normalise on import? 247 -- XXX: Do we need "unit" attribute or can we normalise on import?
248 value_min double precision, -- XXX: NOT NULL if predicted? 248 value_min double precision, -- XXX: NOT NULL if predicted?
249 value_max double precision, -- XXX: NOT NULL if predicted? 249 value_max double precision, -- XXX: NOT NULL if predicted?
250 --- TODO: Add a double range type for checking? 250 --- TODO: Add a double range type for checking?
251 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 251 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
252 source_organization varchar NOT NULL -- "originator" 252 source_organization varchar NOT NULL, -- "originator"
253 staging_done boolean NOT NULL DEFAULT false
253 ) 254 )
254 CREATE TRIGGER gauge_measurements_date_info 255 CREATE TRIGGER gauge_measurements_date_info
255 BEFORE UPDATE ON gauge_measurements 256 BEFORE UPDATE ON gauge_measurements
256 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 257 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
257 258
280 is_section boolean NOT NULL, -- maps 'function' from interface 281 is_section boolean NOT NULL, -- maps 'function' from interface
281 stretch isrsrange, 282 stretch isrsrange,
282 objnam varchar NOT NULL, 283 objnam varchar NOT NULL,
283 nobjnam varchar, 284 nobjnam varchar,
284 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 285 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
285 source_organization varchar NOT NULL 286 source_organization varchar NOT NULL,
287 staging_done boolean NOT NULL DEFAULT false
286 ) 288 )
287 CREATE TRIGGER sections_stretches_date_info 289 CREATE TRIGGER sections_stretches_date_info
288 BEFORE UPDATE ON sections_stretches 290 BEFORE UPDATE ON sections_stretches
289 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 291 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
290 292
300 fe100 smallint, 302 fe100 smallint,
301 -- XXX: further normalise using reference_water_levels? 303 -- XXX: further normalise using reference_water_levels?
302 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL 304 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
303 OR validity IS NOT NULL), 305 OR validity IS NOT NULL),
304 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 306 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
305 source_organization varchar NOT NULL 307 source_organization varchar NOT NULL,
308 staging_done boolean NOT NULL DEFAULT false
306 ) 309 )
307 CREATE TRIGGER waterway_profiles_date_info 310 CREATE TRIGGER waterway_profiles_date_info
308 BEFORE UPDATE ON waterway_profiles 311 BEFORE UPDATE ON waterway_profiles
309 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 312 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
310 313
313 level_of_service smallint NOT NULL REFERENCES levels_of_service, 316 level_of_service smallint NOT NULL REFERENCES levels_of_service,
314 min_width smallint NOT NULL, 317 min_width smallint NOT NULL,
315 max_width smallint NOT NULL, 318 max_width smallint NOT NULL,
316 min_depth smallint NOT NULL, 319 min_depth smallint NOT NULL,
317 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 320 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
318 source_organization varchar NOT NULL 321 source_organization varchar NOT NULL,
322 staging_done boolean NOT NULL DEFAULT false
319 ) 323 )
320 CREATE TRIGGER fairway_dimensions_date_info 324 CREATE TRIGGER fairway_dimensions_date_info
321 BEFORE UPDATE ON fairway_dimensions 325 BEFORE UPDATE ON fairway_dimensions
322 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 326 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
323 327