# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1567679011 -7200
# Node ID dfe9cde6a20c970c98e68d1789c0d79ec7daf769
# Parent  7650d2431f86a8509e8fc0ed71ae67ef3989bba5
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.

diff -r 7650d2431f86 -r dfe9cde6a20c pkg/geoserver/boot.go
--- 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},
diff -r 7650d2431f86 -r dfe9cde6a20c pkg/models/intservices.go
--- 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 {