Mercurial > gemma
changeset 4614:970e90d3d5eb geoserver_sql_views
Merge default into geoserver_sql_views
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 09 Sep 2019 17:24:07 +0200 |
| parents | 0c5829ff4dea (diff) 90b72e811efd (current diff) |
| children | 32d3e0cecf4f |
| files | pkg/geoserver/boot.go schema/gemma.sql schema/version.sql |
| diffstat | 8 files changed, 191 insertions(+), 53 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/geoserver/boot.go Mon Sep 09 17:13:37 2019 +0200 +++ b/pkg/geoserver/boot.go Mon Sep 09 17:24:07 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 Mon Sep 09 17:13:37 2019 +0200 +++ b/pkg/models/intservices.go Mon Sep 09 17:24:07 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/demo-data/published_services.sql Mon Sep 09 17:13:37 2019 +0200 +++ b/schema/demo-data/published_services.sql Mon Sep 09 17:24:07 2019 +0200 @@ -11,17 +11,18 @@ -- Author(s): -- * Tom Gottfried <tom@intevation.de> -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') +INSERT INTO sys_admin.published_services (schema, name, view_def) VALUES + ('waterway', 'sections_geoserver', NULL), + ('waterway', 'stretches_geoserver', NULL), + ('waterway', 'fairway_dimensions', NULL), + ('waterway', 'gauges_geoserver', NULL), + ('waterway', 'distance_marks_ashore_geoserver', NULL), + ('waterway', 'distance_marks_geoserver', NULL), + ('waterway', 'sounding_results_contour_lines_geoserver', NULL), + ('waterway', 'bottlenecks_geoserver', NULL), + ('waterway', 'bottleneck_overview', NULL), + ('waterway', 'waterway_axis', NULL), + ('waterway', 'waterway_area', NULL), + ('waterway', 'waterway_profiles', NULL), + ('waterway', 'sounding_differences', NULL) +ON CONFLICT DO NOTHING
--- a/schema/gemma.sql Mon Sep 09 17:13:37 2019 +0200 +++ b/schema/gemma.sql Mon Sep 09 17:24:07 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 @@ -272,10 +292,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 Mon Sep 09 17:13:37 2019 +0200 +++ b/schema/gemma_tests.sql Mon Sep 09 17:24:07 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/run_tests.sh Mon Sep 09 17:13:37 2019 +0200 +++ b/schema/run_tests.sh Mon Sep 09 17:24:07 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(73 + ( + -c "SELECT plan(76 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1113/01.add_view_def.sql Mon Sep 09 17:24:07 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);