Mercurial > gemma
changeset 4617:ae840f9eb4c8 geoserver_sql_views
Advance schema version number
Do a bigger step to allow more schema changes in default
branch before we have to move here (branch geoserver_sql_views)
again.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 16 Sep 2019 16:56:11 +0200 |
| parents | b605e91f08f0 |
| children | 0f2c3cb139cc |
| files | schema/updates/1113/01.add_view_def.sql schema/updates/1113/02.views_to_geoservers.sql schema/updates/1300/01.add_view_def.sql schema/updates/1300/02.views_to_geoservers.sql schema/version.sql |
| diffstat | 5 files changed, 280 insertions(+), 280 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/updates/1113/01.add_view_def.sql Tue Sep 10 19:56:43 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,28 +0,0 @@ -CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean -AS $$ -BEGIN - EXECUTE format('SELECT * FROM (%s) AS test', stmt); - RETURN true; -EXCEPTION - WHEN OTHERS THEN - RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; - RETURN false; -END -$$ - LANGUAGE plpgsql - STRICT; - -ALTER TABLE sys_admin.published_services - ADD schema varchar; -UPDATE sys_admin.published_services - SET schema = substring(CAST(name AS varchar) from '(.*)\.'); -ALTER TABLE sys_admin.published_services - ADD CHECK(to_regnamespace(schema) IS NOT NULL), - ALTER name TYPE varchar - USING substring(CAST(name AS varchar) from '\.(.*)'), - DROP CONSTRAINT published_services_pkey, - ADD PRIMARY KEY (schema, name), - ADD view_def text CHECK (is_valid_from_item(view_def)), - ADD srid int REFERENCES spatial_ref_sys, - ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL - OR view_def IS NOT NULL);
--- a/schema/updates/1113/02.views_to_geoservers.sql Tue Sep 10 19:56:43 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,251 +0,0 @@ --- Copied from ../../default_sysconfig.sql --- plus conflict resolution to achieve updates - -CREATE TEMP TABLE base_views (name, def) AS VALUES ( - 'gauges_base_view', $$ - SELECT - g.location, - isrs_asText(g.location) AS isrs_code, - g.objname, - g.geom, - g.applicability_from_km, - g.applicability_to_km, - g.validity, - g.zero_point, - g.geodref, - g.date_info, - g.source_organization, - g.erased, - r.rwls AS reference_water_levels, - wl.measure_date AS gm_measuredate, - wl.water_level AS gm_waterlevel, - wl_14d.n AS gm_n_14d, - fca.forecast_accuracy_3d, - fca.forecast_accuracy_1d - FROM waterway.gauges g - LEFT JOIN (SELECT location, validity, - json_strip_nulls(json_object_agg( - coalesce(depth_reference, 'empty'), value)) AS rwls - FROM waterway.gauges_reference_water_levels - GROUP BY location, validity) AS r - USING (location, validity) - LEFT JOIN (SELECT DISTINCT ON (location) - location, - measure_date, - water_level - FROM waterway.gauge_measurements - ORDER BY location, measure_date DESC) AS wl - USING (location) - LEFT JOIN (SELECT location, count(water_level) AS n - FROM waterway.gauge_measurements - -- consider all measurements within 14 days plus a tolerance - WHERE measure_date - >= current_timestamp - '14 days 00:15'::interval - GROUP BY location) AS wl_14d - USING (location) - LEFT JOIN (SELECT location, - max(acc) FILTER (WHERE - measure_date <= current_timestamp + '1 day'::interval) - AS forecast_accuracy_1d, - max(acc) AS forecast_accuracy_3d - FROM waterway.gauge_predictions, - GREATEST(water_level - lower(conf_interval), - upper(conf_interval) - water_level) AS acc (acc) - WHERE measure_date - BETWEEN current_timestamp - AND current_timestamp + '3 days'::interval - GROUP BY location) AS fca - USING (location) - $$); - -INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES - ('waterway', 'gauges_geoserver', 4326, $$ - SELECT - isrs_code, - objname, - geom, - applicability_from_km, - applicability_to_km, - zero_point, - geodref, - date_info, - source_organization, - reference_water_levels, - gm_measuredate, - gm_waterlevel, - gm_n_14d, - forecast_accuracy_3d, - forecast_accuracy_1d - FROM ( - $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ - ) AS gauges_base_view - WHERE NOT erased - $$), - ('waterway', 'sections_geoserver', 4326, $$ - SELECT - s.id, - s.name, - (s.section).lower::varchar as lower, - (s.section).upper::varchar as upper, - s.area::Geometry(MULTIPOLYGON, 4326), - s.objnam, - s.nobjnam, - s.date_info, - s.source_organization, - s.staging_done, - min(g.gm_measuredate) AS gm_measuredate, - min(g.gm_n_14d) AS gm_n_14d, - max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, - max(g.forecast_accuracy_1d) AS forecast_accuracy_1d - FROM waterway.sections s - LEFT JOIN ( - $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ - WHERE NOT erased) AS g - ON g.location <@ s.section - GROUP BY s.id - $$), - ('waterway', 'stretches_geoserver', 4326, $$ - SELECT - s.id, - s.name, - (s.stretch).lower::varchar as lower, - (s.stretch).upper::varchar as upper, - s.area::Geometry(MULTIPOLYGON, 4326), - s.objnam, - s.nobjnam, - s.date_info, - s.source_organization, - (SELECT string_agg(country_code, ', ') - FROM waterway.stretch_countries - WHERE stretches_id = s.id) AS countries, - s.staging_done, - min(g.gm_measuredate) AS gm_measuredate, - min(g.gm_n_14d) AS gm_n_14d, - max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, - max(g.forecast_accuracy_1d) AS forecast_accuracy_1d - FROM waterway.stretches s - LEFT JOIN ( - $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ - WHERE NOT erased) AS g - ON g.location <@ s.stretch - GROUP BY s.id - $$), - ('waterway', 'distance_marks_ashore_geoserver', 4326, $$ - SELECT id, - country, - geom::Geometry(POINT, 4326), - related_enc, - hectom, - catdis, - position_code - FROM waterway.distance_marks - $$), - ('waterway', 'distance_marks_geoserver', 4326, $$ - SELECT - isrs_asText(location_code) AS location, - geom::Geometry(POINT, 4326), - related_enc, - (location_code).hectometre - FROM waterway.distance_marks_virtual - $$), - ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$ - SELECT bottleneck_id, - date_info, - height, - CAST(lines AS geometry(multilinestring, 4326)) AS lines - FROM waterway.sounding_results_contour_lines cl - JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id - $$), - ('waterway', 'bottlenecks_geoserver', 4326, $$ - SELECT - b.id, - b.bottleneck_id, - b.objnam, - b.nobjnm, - b.area, - b.rb, - b.lb, - b.responsible_country, - b.revisiting_time, - b.limiting, - b.date_info, - b.source_organization, - g.objname AS gauge_objname, - g.reference_water_levels, - fal.date_info AS fa_date_info, - fal.critical AS fa_critical, - g.gm_measuredate, - g.gm_waterlevel, - g.gm_n_14d, - srl.date_max, - g.forecast_accuracy_3d, - g.forecast_accuracy_1d - FROM waterway.bottlenecks b - LEFT JOIN ( - $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ - ) AS g - ON b.gauge_location = g.location - AND g.validity @> current_timestamp - LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) - bottleneck_id, date_info, critical - FROM waterway.fairway_availability - ORDER BY bottleneck_id, date_info DESC) AS fal - ON b.bottleneck_id = fal.bottleneck_id - LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) - bottleneck_id, max(date_info) AS date_max - FROM waterway.sounding_results - GROUP BY bottleneck_id - ORDER BY bottleneck_id DESC) AS srl - ON b.bottleneck_id = srl.bottleneck_id - WHERE b.validity @> current_timestamp - $$), - ('waterway', 'bottleneck_overview', 4326, $$ - SELECT - objnam AS name, - ST_Centroid(area)::Geometry(POINT, 4326) AS point, - (lower(stretch)).hectometre AS from, - (upper(stretch)).hectometre AS to, - sr.current::text, - responsible_country - FROM waterway.bottlenecks bn LEFT JOIN ( - SELECT bottleneck_id, max(date_info) AS current - FROM waterway.sounding_results - GROUP BY bottleneck_id) sr - ON sr.bottleneck_id = bn.bottleneck_id - WHERE bn.validity @> current_timestamp - ORDER BY objnam - $$), - ('waterway', 'sounding_differences', 4326, $$ - SELECT - sd.id AS id, - bn.objnam AS objnam, - srm.date_info AS minuend, - srs.date_info AS subtrahend, - sdcl.height AS height, - CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines - FROM caching.sounding_differences sd - JOIN caching.sounding_differences_contour_lines sdcl - ON sd.id = sdcl.sounding_differences_id - JOIN waterway.sounding_results srm - ON sd.minuend = srm.id - JOIN waterway.sounding_results srs - ON sd.subtrahend = srs.id - JOIN waterway.bottlenecks bn - ON srm.bottleneck_id = bn.bottleneck_id - AND srm.date_info::timestamptz <@ bn.validity - $$) -ON CONFLICT (schema, name) DO UPDATE SET - srid = EXCLUDED.srid, - view_def = EXCLUDED.view_def; - -DROP VIEW - waterway.gauges_base_view, - waterway.gauges_geoserver, - waterway.distance_marks_geoserver, - waterway.distance_marks_ashore_geoserver, - waterway.bottlenecks_geoserver, - waterway.stretches_geoserver, - waterway.sections_geoserver, - waterway.sounding_results_contour_lines_geoserver, - waterway.bottleneck_overview, - waterway.sounding_differences;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1300/01.add_view_def.sql Mon Sep 16 16:56:11 2019 +0200 @@ -0,0 +1,28 @@ +CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean +AS $$ +BEGIN + EXECUTE format('SELECT * FROM (%s) AS test', stmt); + RETURN true; +EXCEPTION + WHEN OTHERS THEN + RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; + RETURN false; +END +$$ + LANGUAGE plpgsql + STRICT; + +ALTER TABLE sys_admin.published_services + ADD schema varchar; +UPDATE sys_admin.published_services + SET schema = substring(CAST(name AS varchar) from '(.*)\.'); +ALTER TABLE sys_admin.published_services + ADD CHECK(to_regnamespace(schema) IS NOT NULL), + ALTER name TYPE varchar + USING substring(CAST(name AS varchar) from '\.(.*)'), + DROP CONSTRAINT published_services_pkey, + ADD PRIMARY KEY (schema, name), + ADD view_def text CHECK (is_valid_from_item(view_def)), + ADD srid int REFERENCES spatial_ref_sys, + ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL + OR view_def IS NOT NULL);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1300/02.views_to_geoservers.sql Mon Sep 16 16:56:11 2019 +0200 @@ -0,0 +1,251 @@ +-- Copied from ../../default_sysconfig.sql +-- plus conflict resolution to achieve updates + +CREATE TEMP TABLE base_views (name, def) AS VALUES ( + 'gauges_base_view', $$ + SELECT + g.location, + isrs_asText(g.location) AS isrs_code, + g.objname, + g.geom, + g.applicability_from_km, + g.applicability_to_km, + g.validity, + g.zero_point, + g.geodref, + g.date_info, + g.source_organization, + g.erased, + r.rwls AS reference_water_levels, + wl.measure_date AS gm_measuredate, + wl.water_level AS gm_waterlevel, + wl_14d.n AS gm_n_14d, + fca.forecast_accuracy_3d, + fca.forecast_accuracy_1d + FROM waterway.gauges g + LEFT JOIN (SELECT location, validity, + json_strip_nulls(json_object_agg( + coalesce(depth_reference, 'empty'), value)) AS rwls + FROM waterway.gauges_reference_water_levels + GROUP BY location, validity) AS r + USING (location, validity) + LEFT JOIN (SELECT DISTINCT ON (location) + location, + measure_date, + water_level + FROM waterway.gauge_measurements + ORDER BY location, measure_date DESC) AS wl + USING (location) + LEFT JOIN (SELECT location, count(water_level) AS n + FROM waterway.gauge_measurements + -- consider all measurements within 14 days plus a tolerance + WHERE measure_date + >= current_timestamp - '14 days 00:15'::interval + GROUP BY location) AS wl_14d + USING (location) + LEFT JOIN (SELECT location, + max(acc) FILTER (WHERE + measure_date <= current_timestamp + '1 day'::interval) + AS forecast_accuracy_1d, + max(acc) AS forecast_accuracy_3d + FROM waterway.gauge_predictions, + GREATEST(water_level - lower(conf_interval), + upper(conf_interval) - water_level) AS acc (acc) + WHERE measure_date + BETWEEN current_timestamp + AND current_timestamp + '3 days'::interval + GROUP BY location) AS fca + USING (location) + $$); + +INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES + ('waterway', 'gauges_geoserver', 4326, $$ + SELECT + isrs_code, + objname, + geom, + applicability_from_km, + applicability_to_km, + zero_point, + geodref, + date_info, + source_organization, + reference_water_levels, + gm_measuredate, + gm_waterlevel, + gm_n_14d, + forecast_accuracy_3d, + forecast_accuracy_1d + FROM ( + $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ + ) AS gauges_base_view + WHERE NOT erased + $$), + ('waterway', 'sections_geoserver', 4326, $$ + SELECT + s.id, + s.name, + (s.section).lower::varchar as lower, + (s.section).upper::varchar as upper, + s.area::Geometry(MULTIPOLYGON, 4326), + s.objnam, + s.nobjnam, + s.date_info, + s.source_organization, + s.staging_done, + min(g.gm_measuredate) AS gm_measuredate, + min(g.gm_n_14d) AS gm_n_14d, + max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, + max(g.forecast_accuracy_1d) AS forecast_accuracy_1d + FROM waterway.sections s + LEFT JOIN ( + $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ + WHERE NOT erased) AS g + ON g.location <@ s.section + GROUP BY s.id + $$), + ('waterway', 'stretches_geoserver', 4326, $$ + SELECT + s.id, + s.name, + (s.stretch).lower::varchar as lower, + (s.stretch).upper::varchar as upper, + s.area::Geometry(MULTIPOLYGON, 4326), + s.objnam, + s.nobjnam, + s.date_info, + s.source_organization, + (SELECT string_agg(country_code, ', ') + FROM waterway.stretch_countries + WHERE stretches_id = s.id) AS countries, + s.staging_done, + min(g.gm_measuredate) AS gm_measuredate, + min(g.gm_n_14d) AS gm_n_14d, + max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, + max(g.forecast_accuracy_1d) AS forecast_accuracy_1d + FROM waterway.stretches s + LEFT JOIN ( + $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ + WHERE NOT erased) AS g + ON g.location <@ s.stretch + GROUP BY s.id + $$), + ('waterway', 'distance_marks_ashore_geoserver', 4326, $$ + SELECT id, + country, + geom::Geometry(POINT, 4326), + related_enc, + hectom, + catdis, + position_code + FROM waterway.distance_marks + $$), + ('waterway', 'distance_marks_geoserver', 4326, $$ + SELECT + isrs_asText(location_code) AS location, + geom::Geometry(POINT, 4326), + related_enc, + (location_code).hectometre + FROM waterway.distance_marks_virtual + $$), + ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$ + SELECT bottleneck_id, + date_info, + height, + CAST(lines AS geometry(multilinestring, 4326)) AS lines + FROM waterway.sounding_results_contour_lines cl + JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id + $$), + ('waterway', 'bottlenecks_geoserver', 4326, $$ + SELECT + b.id, + b.bottleneck_id, + b.objnam, + b.nobjnm, + b.area, + b.rb, + b.lb, + b.responsible_country, + b.revisiting_time, + b.limiting, + b.date_info, + b.source_organization, + g.objname AS gauge_objname, + g.reference_water_levels, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical, + g.gm_measuredate, + g.gm_waterlevel, + g.gm_n_14d, + srl.date_max, + g.forecast_accuracy_3d, + g.forecast_accuracy_1d + FROM waterway.bottlenecks b + LEFT JOIN ( + $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ + ) AS g + ON b.gauge_location = g.location + AND g.validity @> current_timestamp + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, date_info, critical + FROM waterway.fairway_availability + ORDER BY bottleneck_id, date_info DESC) AS fal + ON b.bottleneck_id = fal.bottleneck_id + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, max(date_info) AS date_max + FROM waterway.sounding_results + GROUP BY bottleneck_id + ORDER BY bottleneck_id DESC) AS srl + ON b.bottleneck_id = srl.bottleneck_id + WHERE b.validity @> current_timestamp + $$), + ('waterway', 'bottleneck_overview', 4326, $$ + SELECT + objnam AS name, + ST_Centroid(area)::Geometry(POINT, 4326) AS point, + (lower(stretch)).hectometre AS from, + (upper(stretch)).hectometre AS to, + sr.current::text, + responsible_country + FROM waterway.bottlenecks bn LEFT JOIN ( + SELECT bottleneck_id, max(date_info) AS current + FROM waterway.sounding_results + GROUP BY bottleneck_id) sr + ON sr.bottleneck_id = bn.bottleneck_id + WHERE bn.validity @> current_timestamp + ORDER BY objnam + $$), + ('waterway', 'sounding_differences', 4326, $$ + SELECT + sd.id AS id, + bn.objnam AS objnam, + srm.date_info AS minuend, + srs.date_info AS subtrahend, + sdcl.height AS height, + CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines + FROM caching.sounding_differences sd + JOIN caching.sounding_differences_contour_lines sdcl + ON sd.id = sdcl.sounding_differences_id + JOIN waterway.sounding_results srm + ON sd.minuend = srm.id + JOIN waterway.sounding_results srs + ON sd.subtrahend = srs.id + JOIN waterway.bottlenecks bn + ON srm.bottleneck_id = bn.bottleneck_id + AND srm.date_info::timestamptz <@ bn.validity + $$) +ON CONFLICT (schema, name) DO UPDATE SET + srid = EXCLUDED.srid, + view_def = EXCLUDED.view_def; + +DROP VIEW + waterway.gauges_base_view, + waterway.gauges_geoserver, + waterway.distance_marks_geoserver, + waterway.distance_marks_ashore_geoserver, + waterway.bottlenecks_geoserver, + waterway.stretches_geoserver, + waterway.sections_geoserver, + waterway.sounding_results_contour_lines_geoserver, + waterway.bottleneck_overview, + waterway.sounding_differences;