Mercurial > gemma
changeset 4673:443867b548b5
Fix identifiers in layers generated from SQL views
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 15 Oct 2019 15:59:38 +0200 |
parents | c7dc1a6da93d |
children | 3b7de89527a3 |
files | pkg/geoserver/boot.go pkg/models/intservices.go schema/default_sysconfig.sql schema/gemma.sql schema/updates/1303/01.add_key_column.sql schema/version.sql |
diffstat | 6 files changed, 51 insertions(+), 54 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/geoserver/boot.go Tue Oct 15 15:55:12 2019 +0200 +++ b/pkg/geoserver/boot.go Tue Oct 15 15:59:38 2019 +0200 @@ -90,9 +90,10 @@ } type ftVirtTable struct { - XMLName xml.Name `xml:"virtualTable"` - Name string `xml:"name"` - SQL string `xml:"sql"` + XMLName xml.Name `xml:"virtualTable"` + Name string `xml:"name"` + SQL string `xml:"sql"` + KeyColumn *string `xml:"keyColumn,omitempty"` } // End code for handling with XML func asJSON(req *http.Request) { @@ -335,8 +336,9 @@ Entry: ftMetadataEntry{ Key: "JDBC_VIRTUAL_TABLE", VirtTable: ftVirtTable{ - Name: table, - SQL: *tables[i].SQL}}}} + Name: table, + SQL: *tables[i].SQL, + KeyColumn: tables[i].KeyColumn}}}} req, err = http.NewRequest( http.MethodPost,
--- a/pkg/models/intservices.go Tue Oct 15 15:55:12 2019 +0200 +++ b/pkg/models/intservices.go Tue Oct 15 15:59:38 2019 +0200 @@ -28,13 +28,14 @@ const DatabaseScheme = "waterway" type IntEntry struct { - 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"` + Schema string `json:"schema"` + Name string `json:"name"` + SQL *string `json:"sql"` + KeyColumn *string `json:"keycolumn"` + SRS *string `json:"srs"` + Style bool `json:"style"` + WMS bool `json:"wms"` + WFS bool `json:"wfs"` } type IntServices struct { @@ -45,7 +46,7 @@ const ( selectServicesSQL = ` SELECT schema, name, - view_def, auth_name || ':' || auth_srid, + view_def, key_column, auth_name || ':' || auth_srid, style IS NOT NULL, as_wms, as_wfs FROM sys_admin.published_services LEFT JOIN spatial_ref_sys USING (srid) @@ -141,7 +142,7 @@ var entry IntEntry if err := rows.Scan( &entry.Schema, &entry.Name, - &entry.SQL, &entry.SRS, &entry.Style, + &entry.SQL, &entry.KeyColumn, &entry.SRS, &entry.Style, &entry.WMS, &entry.WFS, ); err != nil { return err
--- a/schema/default_sysconfig.sql Tue Oct 15 15:55:12 2019 +0200 +++ b/schema/default_sysconfig.sql Tue Oct 15 15:59:38 2019 +0200 @@ -88,14 +88,16 @@ ON fca.location = g.location AND fca.date_issue >= wl.date_issue $$); -INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES +INSERT INTO sys_admin.published_services ( + schema, name, srid, key_column, 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), + ('waterway', 'waterway_axis', NULL, NULL, NULL), + ('waterway', 'waterway_area', NULL, NULL, NULL), + ('waterway', 'waterway_profiles', NULL, NULL, NULL), + ('waterway', 'fairway_dimensions', NULL, NULL, NULL), -- GeoServer SQL views - ('waterway', 'gauges_geoserver', 4326, $$ + ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$ SELECT isrs_code, objname, @@ -117,7 +119,7 @@ ) AS gauges_base_view WHERE NOT erased $$), - ('waterway', 'sections_geoserver', 4326, $$ + ('waterway', 'sections_geoserver', 4326, 'id', $$ SELECT s.id, s.name, @@ -140,7 +142,7 @@ ON g.location <@ s.section GROUP BY s.id $$), - ('waterway', 'stretches_geoserver', 4326, $$ + ('waterway', 'stretches_geoserver', 4326, 'id', $$ SELECT s.id, s.name, @@ -166,7 +168,7 @@ ON g.location <@ s.stretch GROUP BY s.id $$), - ('waterway', 'distance_marks_ashore_geoserver', 4326, $$ + ('waterway', 'distance_marks_ashore_geoserver', 4326, 'id', $$ SELECT id, country, geom, @@ -176,7 +178,7 @@ position_code FROM waterway.distance_marks $$), - ('waterway', 'distance_marks_geoserver', 4326, $$ + ('waterway', 'distance_marks_geoserver', 4326, 'location', $$ SELECT isrs_asText(location_code) AS location, geom, @@ -184,7 +186,7 @@ (location_code).hectometre FROM waterway.distance_marks_virtual $$), - ('waterway', 'sounding_results_areas_geoserver', 4326, $$ + ('waterway', 'sounding_results_areas_geoserver', 4326, NULL, $$ SELECT bottleneck_id, date_info, height, @@ -192,7 +194,7 @@ FROM waterway.sounding_results_iso_areas ia JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id $$), - ('waterway', 'bottlenecks_geoserver', 4326, $$ + ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$ SELECT b.id, b.bottleneck_id, @@ -235,7 +237,7 @@ ON b.bottleneck_id = srl.bottleneck_id WHERE b.validity @> current_timestamp $$), - ('waterway', 'bottleneck_overview', 4326, $$ + ('waterway', 'bottleneck_overview', 4326, NULL, $$ SELECT objnam AS name, ST_Centroid(area) AS point, @@ -251,7 +253,7 @@ WHERE bn.validity @> current_timestamp ORDER BY objnam $$), - ('waterway', 'sounding_differences', 4326, $$ + ('waterway', 'sounding_differences', 4326, NULL, $$ SELECT sd.id AS id, bn.objnam AS objnam,
--- a/schema/gemma.sql Tue Oct 15 15:55:12 2019 +0200 +++ b/schema/gemma.sql Tue Oct 15 15:59:38 2019 +0200 @@ -388,6 +388,8 @@ PRIMARY KEY (schema, name), -- SQL statement used for an SQL view in GeoServer: view_def text CHECK (is_valid_from_item(view_def)), + -- Column in output of SQL statement to be used as primary key: + key_column varchar, -- SRID to be used with SQL view: srid int REFERENCES spatial_ref_sys, -- SLD style document: @@ -577,23 +579,6 @@ related_enc varchar(12) ) - -- We need to configure primary keys for the views used by - -- geoserver for wfs, otherwise it will generate ids on the fly, - -- which will change for the same feature... - -- See - -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html - -- for details. - CREATE TABLE gt_pk_metadata ( - table_schema VARCHAR(32) NOT NULL, - table_name VARCHAR(32) NOT NULL, - pk_column VARCHAR(32) NOT NULL, - pk_column_idx INTEGER, - pk_policy VARCHAR(32), - pk_sequence VARCHAR(64), - unique (table_schema, table_name, pk_column), - check (pk_policy in ('sequence', 'assigned', 'autogenerated')) - ) - -- Like stretches without the countries CREATE TABLE sections ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, @@ -802,14 +787,6 @@ ) ; --- Configure primary keys for geoserver views -INSERT INTO waterway.gt_pk_metadata VALUES - ('waterway', 'gauges_geoserver', 'isrs_code'), - ('waterway', 'distance_marks_geoserver', 'location'), - ('waterway', 'distance_marks_ashore_geoserver', 'id'), - ('waterway', 'bottlenecks_geoserver', 'id'), - ('waterway', 'stretches_geoserver', 'id'), - ('waterway', 'sections_geoserver', 'id'); -- -- Import queue and respective logging
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1303/01.add_key_column.sql Tue Oct 15 15:59:38 2019 +0200 @@ -0,0 +1,15 @@ +ALTER TABLE sys_admin.published_services ADD key_column varchar; + +UPDATE sys_admin.published_services SET key_column = 'isrs_code' + WHERE schema = 'waterway' AND name = 'gauges_geoserver'; +UPDATE sys_admin.published_services SET key_column = 'id' + WHERE schema = 'waterway' + AND name IN( + 'sections_geoserver', + 'stretches_geoserver', + 'distance_marks_ashore_geoserver', + 'bottlenecks_geoserver'); +UPDATE sys_admin.published_services SET key_column = 'location' + WHERE schema = 'waterway' AND name = 'distance_marks_geoserver'; + +DROP TABLE waterway.gt_pk_metadata