Mercurial > gemma

changeset 4673:443867b548b5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
--- a/schema/version.sql	Tue Oct 15 15:55:12 2019 +0200
+++ b/schema/version.sql	Tue Oct 15 15:59:38 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1302);
+INSERT INTO gemma_schema_version(version) VALUES (1303);