Mercurial > gemma
changeset 4621:d2eac69ba86b geoserver_sql_views
Merge default into geoserver_sql_views
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 04 Oct 2019 16:40:04 +0200 |
| parents | f77a6f9216ae (diff) 7caf620dda50 (current diff) |
| children | b03aa1502736 |
| files | 3rdpartylibs.sh package.json schema/gemma.sql schema/version.sql yarn.lock |
| diffstat | 11 files changed, 680 insertions(+), 279 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/geoserver/boot.go Fri Oct 04 11:58:37 2019 +0200 +++ b/pkg/geoserver/boot.go Fri Oct 04 16:40:04 2019 +0200 @@ -34,7 +34,6 @@ const ( workspaceName = "gemma" datastoreName = "gemma" - databaseScheme = "waterway" databaseType = "postgis" primaryKeyMetadataTbl = "waterway.gt_pk_metadata" ) @@ -60,6 +59,42 @@ return bytes.NewReader(buf.Bytes()) } +// XXX: Creating SQL views with JSON via GeoServer REST-API fails +// Begin code for handling with XML instead +func toXMLStream(x interface{}) io.Reader { + buf := bytes.NewBufferString(xml.Header) + if err := xml.NewEncoder(buf).Encode(x); err != nil { + // Should not happen + log.Printf("warn: bad XML: %v\n", err) + } + return bytes.NewReader(buf.Bytes()) +} + +type ftXML struct { + XMLName xml.Name `xml:"featureType"` + Name string `xml:"name"` + Title string `xml:"title"` + SRS *string `xml:"srs,omitempty"` + Metadata ftMetadata +} + +type ftMetadata struct { + XMLName xml.Name `xml:"metadata"` + Entry ftMetadataEntry +} + +type ftMetadataEntry struct { + XMLName xml.Name `xml:"entry"` + Key string `xml:"key,attr"` + VirtTable ftVirtTable +} + +type ftVirtTable struct { + XMLName xml.Name `xml:"virtualTable"` + Name string `xml:"name"` + SQL string `xml:"sql"` +} // End code for handling with XML + func asJSON(req *http.Request) { req.Header.Set("Content-Type", "application/json") } @@ -168,7 +203,7 @@ {"host", config.DBHost()}, {"port", config.DBPort()}, {"database", config.DBName()}, - {"schema", databaseScheme}, + {"schema", models.DatabaseScheme}, {"user", config.DBUser()}, {"passwd", config.DBPassword()}, {"dbtype", databaseType}, @@ -214,6 +249,9 @@ auth = basicAuth(user, password) ) + datastoreURL := geoURL + "/rest/workspaces/" + workspaceName + + "/datastores/" + datastoreName + tables := models.InternalServices.Filter(models.IntWFS) if len(tables) == 0 { log.Println("info: no tables to publish") @@ -242,9 +280,7 @@ // Fetch all featuretypes. req, err := http.NewRequest( http.MethodGet, - geoURL+"/rest/workspaces/"+workspaceName+ - "/datastores/"+datastoreName+ - "/featuretypes.json", + datastoreURL+"/featuretypes.json", nil) if err != nil { return err @@ -286,26 +322,50 @@ // Create featuretype. log.Printf("info: creating featuretype %s.\n", table) - // Create featuretype - ft := map[string]interface{}{ - "featureType": map[string]interface{}{ - "name": table, - "nativeName": table, - "title": table, - }, - } + var req *http.Request + if models.IntSQLView(tables[i]) { + // XXX: Creating SQL views with JSON via GeoServer REST-API fails + // Begin code for handling with XML instead + ft := ftXML{ + Name: table, + Title: table, + SRS: tables[i].SRS, + Metadata: ftMetadata{ + Entry: ftMetadataEntry{ + Key: "JDBC_VIRTUAL_TABLE", + VirtTable: ftVirtTable{ + Name: table, + SQL: *tables[i].SQL}}}} - req, err := http.NewRequest( - http.MethodPost, - geoURL+"/rest/workspaces/"+workspaceName+ - "/datastores/"+datastoreName+ - "/featuretypes", - toStream(ft)) - if err != nil { - return err + req, err = http.NewRequest( + http.MethodPost, + datastoreURL+"/featuretypes", + toXMLStream(ft)) + if err != nil { + return err + } + asContentType(req, "text/xml") + // End code for handling with XML instead + } else { + ft := map[string]interface{}{ + "featureType": map[string]interface{}{ + "name": table, + "nativeName": table, + "title": table, + }, + } + + req, err = http.NewRequest( + http.MethodPost, + datastoreURL+"/featuretypes", + toStream(ft)) + if err != nil { + return err + } + asJSON(req) } auth(req) - asJSON(req) + resp, err := http.DefaultClient.Do(req) if err != nil { return err
--- a/pkg/models/intservices.go Fri Oct 04 11:58:37 2019 +0200 +++ b/pkg/models/intservices.go Fri Oct 04 16:40:04 2019 +0200 @@ -25,11 +25,16 @@ "gemma.intevation.de/gemma/pkg/config" ) +const DatabaseScheme = "waterway" + type IntEntry struct { - Name string `json:"name"` - Style bool `json:"style"` - WMS bool `json:"wms"` - WFS bool `json:"wfs"` + Schema string `json:"schema"` + Name string `json:"name"` + SQL *string `json:"sql"` + SRS *string `json:"srs"` + Style bool `json:"style"` + WMS bool `json:"wms"` + WFS bool `json:"wfs"` } type IntServices struct { @@ -39,20 +44,23 @@ const ( selectServicesSQL = ` -SELECT relname, style IS NOT NULL, as_wms, as_wfs +SELECT schema, name, + view_def, auth_name || ':' || auth_srid, + style IS NOT NULL, as_wms, as_wfs FROM sys_admin.published_services -JOIN pg_class ON name = oid ORDER by relname` + LEFT JOIN spatial_ref_sys USING (srid) +WHERE schema = $1 +ORDER by name` selectStyleSQL = ` SELECT XMLSERIALIZE(DOCUMENT style AS text) FROM sys_admin.published_services -JOIN pg_class ON name = oid -WHERE relname = $1` +WHERE name = $1 AND schema = $2` updateStyleSQL = ` UPDATE sys_admin.published_services SET style = XMLPARSE(DOCUMENT $1) -WHERE name IN (SELECT oid FROM pg_class WHERE relname = $2)` +WHERE name = $2 AND schema = $3` ) var InternalServices = &IntServices{} @@ -65,7 +73,7 @@ return conn.QueryRowContext( ctx, selectStyleSQL, - e.Name).Scan(&style) + e.Name, e.Schema).Scan(&style) }) return style, err } @@ -74,7 +82,7 @@ return auth.RunAsSessionUser(req, func(conn *sql.Conn) error { _, err := conn.ExecContext( req.Context(), updateStyleSQL, - style, name) + style, name, DatabaseScheme) if err == nil { InternalServices.Invalidate() } @@ -124,7 +132,7 @@ return auth.RunAs(ctx, "sys_admin", func(conn *sql.Conn) error { rows, err := conn.QueryContext( - ctx, selectServicesSQL) + ctx, selectServicesSQL, DatabaseScheme) if err != nil { return err } @@ -132,7 +140,8 @@ for rows.Next() { var entry IntEntry if err := rows.Scan( - &entry.Name, &entry.Style, + &entry.Schema, &entry.Name, + &entry.SQL, &entry.SRS, &entry.Style, &entry.WMS, &entry.WFS, ); err != nil { return err @@ -152,6 +161,7 @@ func InternalAll(IntEntry) bool { return true } func IntWMS(entry IntEntry) bool { return entry.WMS } func IntWFS(entry IntEntry) bool { return entry.WFS } +func IntSQLView(entry IntEntry) bool { return entry.SQL != nil } func IntWithStyle(entry IntEntry) bool { return entry.Style } func IntByName(name string) func(IntEntry) bool {
--- a/schema/default_sysconfig.sql Fri Oct 04 11:58:37 2019 +0200 +++ b/schema/default_sysconfig.sql Fri Oct 04 16:40:04 2019 +0200 @@ -23,20 +23,253 @@ -- -- Tables and views published via GeoServer -- -INSERT INTO sys_admin.published_services (name) VALUES - ('waterway.sections_geoserver'), - ('waterway.stretches_geoserver'), - ('waterway.fairway_dimensions'), - ('waterway.gauges_geoserver'), - ('waterway.distance_marks_ashore_geoserver'), - ('waterway.distance_marks_geoserver'), - ('waterway.sounding_results_contour_lines_geoserver'), - ('waterway.bottlenecks_geoserver'), - ('waterway.bottleneck_overview'), - ('waterway.waterway_axis'), - ('waterway.waterway_area'), - ('waterway.waterway_profiles'), - ('waterway.sounding_differences'); + +-- temporary table to store portions of SQL shared by multiple SQL views +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.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, + date_issue, + measure_date, + water_level, + count(*) OVER (PARTITION BY location) 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 + ORDER BY location, measure_date DESC) AS wl + USING (location) + LEFT JOIN (SELECT DISTINCT ON (location) + location, + date_issue, + max(acc) FILTER (WHERE measure_date + <= current_timestamp + '1 day'::interval) + OVER loc_date_issue AS forecast_accuracy_1d, + max(acc) OVER loc_date_issue AS forecast_accuracy_3d + FROM (SELECT location, date_issue, measure_date, + GREATEST(water_level - lower(conf_interval), + upper(conf_interval) - water_level) AS acc + FROM waterway.gauge_predictions + -- consider predictions made within last 14 days ... + WHERE date_issue + >= current_timestamp - '14 days 00:15'::interval + -- ... for the next three days from now + AND measure_date BETWEEN current_timestamp + AND current_timestamp + '3 days'::interval) AS acc + WINDOW loc_date_issue AS (PARTITION BY location, date_issue) + ORDER BY location, date_issue DESC) AS fca + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue + $$); + +INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES + -- Directly accessed tables + ('waterway', 'waterway_axis', NULL, NULL), + ('waterway', 'waterway_area', NULL, NULL), + ('waterway', 'waterway_profiles', NULL, NULL), + ('waterway', 'fairway_dimensions', NULL, NULL), + -- GeoServer SQL views + ('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, + 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, + s.objnam, + s.nobjnam, + s.date_info, + s.source_organization, + (SELECT string_agg(country, ', ') + FROM users.stretch_countries + WHERE stretch_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 users.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, + related_enc, + hectom, + catdis, + position_code + FROM waterway.distance_marks + $$), + ('waterway', 'distance_marks_geoserver', 4326, $$ + SELECT + isrs_asText(location_code) AS location, + geom, + related_enc, + (location_code).hectometre + FROM waterway.distance_marks_virtual + $$), + ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$ + SELECT bottleneck_id, + date_info, + height, + 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) 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, + sdcl.lines 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 + $$); -- -- Settings
--- a/schema/gemma.sql Fri Oct 04 11:58:37 2019 +0200 +++ b/schema/gemma.sql Fri Oct 04 16:40:04 2019 +0200 @@ -23,6 +23,26 @@ CREATE EXTENSION btree_gist; -- +-- Functions to be used in CHECK constraints +-- + +-- Check if a given string can be used as a FROM item in an SQL statement +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; + + +-- -- Trigger functions -- -- TODO: will there ever be UPDATEs or can we drop that function due to @@ -361,10 +381,20 @@ ) CREATE TABLE published_services ( - name regclass PRIMARY KEY, + schema varchar CHECK(to_regnamespace(schema) IS NOT NULL), + name varchar, + PRIMARY KEY (schema, name), + -- SQL statement used for an SQL view in GeoServer: + view_def text CHECK (is_valid_from_item(view_def)), + -- SRID to be used with SQL view: + srid int REFERENCES spatial_ref_sys, + -- SLD style document: style xml CHECK(style IS DOCUMENT), as_wms boolean NOT NULL DEFAULT TRUE, - as_wfs boolean NOT NULL DEFAULT TRUE + as_wfs boolean NOT NULL DEFAULT TRUE, + -- Either give a valid relation or a SQL statement: + CHECK (to_regclass(schema || '.' || name) IS NOT NULL + OR view_def IS NOT NULL) ) ;
--- a/schema/gemma_tests.sql Fri Oct 04 11:58:37 2019 +0200 +++ b/schema/gemma_tests.sql Fri Oct 04 16:40:04 2019 +0200 @@ -15,6 +15,15 @@ -- pgTAP test script for gemma schema definition -- +SELECT ok(is_valid_from_item('SELECT * FROM sys_admin.published_services'), + 'Valid statement passes check'); + +SELECT ok(NOT is_valid_from_item('This is not SQL'), + 'Arbitrary text does not pass check'); + +SELECT ok(is_valid_from_item(NULL) IS NULL, + 'NULL value is not checked'); + SELECT throws_ok($$ INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test'),
--- a/schema/geoserver_views.sql Fri Oct 04 11:58:37 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,225 +0,0 @@ -CREATE OR REPLACE VIEW waterway.gauges_base_view AS - 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.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, - date_issue, - measure_date, - water_level, - count(*) OVER (PARTITION BY location) 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 - ORDER BY location, measure_date DESC) AS wl - USING (location) - LEFT JOIN (SELECT DISTINCT ON (location) - location, - date_issue, - max(acc) FILTER (WHERE measure_date - <= current_timestamp + '1 day'::interval) - OVER loc_date_issue AS forecast_accuracy_1d, - max(acc) OVER loc_date_issue AS forecast_accuracy_3d - FROM (SELECT location, date_issue, measure_date, - GREATEST(water_level - lower(conf_interval), - upper(conf_interval) - water_level) AS acc - FROM waterway.gauge_predictions - -- consider predictions made within last 14 days ... - WHERE date_issue - >= current_timestamp - '14 days 00:15'::interval - -- ... for the next three days from now - AND measure_date BETWEEN current_timestamp - AND current_timestamp + '3 days'::interval) AS acc - WINDOW loc_date_issue AS (PARTITION BY location, date_issue) - ORDER BY location, date_issue DESC) AS fca - -- Show only forecasts issued with latest measurements or later - ON fca.location = g.location AND fca.date_issue >= wl.date_issue; - -CREATE OR REPLACE VIEW waterway.gauges_geoserver AS - 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 waterway.gauges_base_view - WHERE NOT erased; - -CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS - SELECT - isrs_asText(location_code) AS location, - geom::Geometry(POINT, 4326), - related_enc, - (location_code).hectometre - FROM waterway.distance_marks_virtual; - -CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS - SELECT id, - country, - geom::Geometry(POINT, 4326), - related_enc, - hectom, - catdis, - position_code - FROM waterway.distance_marks; - -CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS - 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 waterway.gauges_base_view 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; - -CREATE OR REPLACE VIEW waterway.stretches_geoserver AS - 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, ', ') - FROM users.stretch_countries - WHERE stretch_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 users.stretches s - LEFT JOIN waterway.gauges_geoserver g - ON isrs_fromtext(g.isrs_code) <@ s.stretch - GROUP BY s.id; - -CREATE OR REPLACE VIEW waterway.sections_geoserver AS - 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 waterway.gauges_geoserver g - ON isrs_fromtext(g.isrs_code) <@ s.section - GROUP BY s.id; - -CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS - 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; - -CREATE OR REPLACE VIEW waterway.bottleneck_overview AS - 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; - -CREATE OR REPLACE VIEW waterway.sounding_differences AS - 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;
--- a/schema/install-db.sh Fri Oct 04 11:58:37 2019 +0200 +++ b/schema/install-db.sh Fri Oct 04 16:40:04 2019 +0200 @@ -130,7 +130,6 @@ -f "$BASEDIR/search_functions.sql" \ -f "$BASEDIR/geonames.sql" \ -f "$BASEDIR/manage_users.sql" \ - -f "$BASEDIR/geoserver_views.sql" \ -f "$BASEDIR/auth.sql" \ -f "$BASEDIR/isrs_functions.sql" \ -f "$BASEDIR/default_sysconfig.sql" \
--- a/schema/run_tests.sh Fri Oct 04 11:58:37 2019 +0200 +++ b/schema/run_tests.sh Fri Oct 04 16:40:04 2019 +0200 @@ -80,7 +80,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f "$BASEDIR"/tap_tests_data.sql \ - -c "SELECT plan(75 + ( + -c "SELECT plan(78 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1300/01.add_view_def.sql Fri Oct 04 16:40:04 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 Fri Oct 04 16:40:04 2019 +0200 @@ -0,0 +1,257 @@ +-- 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.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, + date_issue, + measure_date, + water_level, + count(*) OVER (PARTITION BY location) 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 + ORDER BY location, measure_date DESC) AS wl + USING (location) + LEFT JOIN (SELECT DISTINCT ON (location) + location, + date_issue, + max(acc) FILTER (WHERE measure_date + <= current_timestamp + '1 day'::interval) + OVER loc_date_issue AS forecast_accuracy_1d, + max(acc) OVER loc_date_issue AS forecast_accuracy_3d + FROM (SELECT location, date_issue, measure_date, + GREATEST(water_level - lower(conf_interval), + upper(conf_interval) - water_level) AS acc + FROM waterway.gauge_predictions + -- consider predictions made within last 14 days ... + WHERE date_issue + >= current_timestamp - '14 days 00:15'::interval + -- ... for the next three days from now + AND measure_date BETWEEN current_timestamp + AND current_timestamp + '3 days'::interval) AS acc + WINDOW loc_date_issue AS (PARTITION BY location, date_issue) + ORDER BY location, date_issue DESC) AS fca + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue + $$); + +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, ', ') + FROM users.stretch_countries + WHERE stretch_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 users.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;