Mercurial > gemma
comparison schema/gemma.sql @ 2865:cf295096234e
gemma.sql fixed: Index creation extracted and put after schmema creation
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Fri, 29 Mar 2019 12:02:33 +0100 |
parents | e2d362589ce6 |
children | 1fa2c9022362 |
comparison
equal
deleted
inserted
replaced
2863:e2d362589ce6 | 2865:cf295096234e |
---|---|
337 --- TODO: Add a double range type for checking? | 337 --- TODO: Add a double range type for checking? |
338 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | 338 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
339 source_organization varchar NOT NULL, -- "originator" | 339 source_organization varchar NOT NULL, -- "originator" |
340 staging_done boolean NOT NULL DEFAULT false | 340 staging_done boolean NOT NULL DEFAULT false |
341 ) | 341 ) |
342 -- Constraints are conditional for gauge_measurements, as they | |
343 -- differ between predicted values and measured ones. PG does not | |
344 -- have real conditional unique constraints, but we can use unique | |
345 -- indeces for that. | |
346 -- | |
347 -- So we can have a staged and a non-staged | |
348 -- fk_gauge_id/measure_date pairs in measured values. | |
349 CREATE UNIQUE INDEX gm_measured_unique_constraint | |
350 ON gauge_measurements (fk_gauge_id, measure_date, staging_done) | |
351 WHERE NOT predicted; | |
352 -- And we can have multiple predictions for one point in time | |
353 -- (but they are never staged). | |
354 CREATE UNIQUE INDEX gm_predicted_unique_constraint | |
355 ON gauge_measurements (fk_gauge_id, measure_date, date_issue) | |
356 WHERE predicted; | |
357 | 342 |
358 CREATE TABLE waterway_axis ( | 343 CREATE TABLE waterway_axis ( |
359 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, | 344 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
360 wtwaxs geography(LINESTRING, 4326) NOT NULL | 345 wtwaxs geography(LINESTRING, 4326) NOT NULL |
361 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), | 346 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), |
707 FROM gauge_measurements_waterlevel | 692 FROM gauge_measurements_waterlevel |
708 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE | 693 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE |
709 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; | 694 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; |
710 ; | 695 ; |
711 | 696 |
697 -- Constraints are conditional for gauge_measurements, as they | |
698 -- differ between predicted values and measured ones. PG does not | |
699 -- have real conditional unique constraints, but we can use unique | |
700 -- indeces for that. | |
701 -- | |
702 -- So we can have a staged and a non-staged | |
703 -- fk_gauge_id/measure_date pairs in measured values. | |
704 CREATE UNIQUE INDEX gm_measured_unique_constraint | |
705 ON waterway.gauge_measurements (fk_gauge_id, measure_date, staging_done) | |
706 WHERE NOT predicted; | |
707 -- And we can have multiple predictions for one point in time | |
708 -- (but they are never staged). | |
709 CREATE UNIQUE INDEX gm_predicted_unique_constraint | |
710 ON waterway.gauge_measurements (fk_gauge_id, measure_date, date_issue) | |
711 WHERE predicted; | |
712 | |
712 -- Configure primary keys for geoserver views | 713 -- Configure primary keys for geoserver views |
713 INSERT INTO waterway.gt_pk_metadata VALUES | 714 INSERT INTO waterway.gt_pk_metadata VALUES |
714 ('waterway', 'gauges_geoserver', 'location'), | 715 ('waterway', 'gauges_geoserver', 'location'), |
715 ('waterway', 'distance_marks_geoserver', 'location_code'), | 716 ('waterway', 'distance_marks_geoserver', 'location_code'), |
716 ('waterway', 'distance_marks_ashore_geoserver', 'id'), | 717 ('waterway', 'distance_marks_ashore_geoserver', 'id'), |