Mercurial > gemma

changeset 4606:dfe9cde6a20c geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reflect database model changes for SQL views in backend In principle, we could use many datasources with different database schemas, but this would imply changing GeoServer initialization, service filtering, endpoints and eventually more. Since we do not need it, just hard-code the schema name as a constant.
author Tom Gottfried <tom@intevation.de>
date Thu, 05 Sep 2019 12:23:31 +0200
parents 7650d2431f86
children 064b6c46ea6c
files pkg/geoserver/boot.go pkg/models/intservices.go
diffstat 2 files changed, 20 insertions(+), 15 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/geoserver/boot.go	Thu Sep 05 11:33:01 2019 +0200
+++ b/pkg/geoserver/boot.go	Thu Sep 05 12:23:31 2019 +0200
@@ -34,7 +34,6 @@
 const (
 	workspaceName         = "gemma"
 	datastoreName         = "gemma"
-	databaseScheme        = "waterway"
 	databaseType          = "postgis"
 	primaryKeyMetadataTbl = "waterway.gt_pk_metadata"
 )
@@ -168,7 +167,7 @@
 					{"host", config.DBHost()},
 					{"port", config.DBPort()},
 					{"database", config.DBName()},
-					{"schema", databaseScheme},
+					{"schema", models.DatabaseScheme},
 					{"user", config.DBUser()},
 					{"passwd", config.DBPassword()},
 					{"dbtype", databaseType},
--- a/pkg/models/intservices.go	Thu Sep 05 11:33:01 2019 +0200
+++ b/pkg/models/intservices.go	Thu Sep 05 12:23:31 2019 +0200
@@ -25,11 +25,15 @@
 	"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"`
+	Style  bool    `json:"style"`
+	WMS    bool    `json:"wms"`
+	WFS    bool    `json:"wfs"`
 }
 
 type IntServices struct {
@@ -39,20 +43,20 @@
 
 const (
 	selectServicesSQL = `
-SELECT relname, style IS NOT NULL, as_wms, as_wfs
+SELECT schema, name, view_def, style IS NOT NULL, as_wms, as_wfs
 FROM sys_admin.published_services
-JOIN pg_class ON name = oid ORDER by relname`
+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 +69,7 @@
 			return conn.QueryRowContext(
 				ctx,
 				selectStyleSQL,
-				e.Name).Scan(&style)
+				e.Name, e.Schema).Scan(&style)
 		})
 	return style, err
 }
@@ -74,7 +78,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 +128,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 +136,8 @@
 			for rows.Next() {
 				var entry IntEntry
 				if err := rows.Scan(
-					&entry.Name, &entry.Style,
+					&entry.Schema, &entry.Name,
+					&entry.SQL, &entry.Style,
 					&entry.WMS, &entry.WFS,
 				); err != nil {
 					return err
@@ -152,6 +157,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 {