Mercurial > gemma

changeset 4605:7650d2431f86 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Separate schema and table name Since a GeoServer data source is bound to a specific database schema, names have to be given without schema qualification. Since we cannot use regclass for names of SQL views, the name is now a varchar. This way it's more straightforward to store schema and name separately.
author Tom Gottfried <tom@intevation.de>
date Thu, 05 Sep 2019 11:33:01 +0200
parents ca8d18f794f3
children dfe9cde6a20c
files schema/gemma.sql schema/updates/1112/01.add_view_def.sql
diffstat 2 files changed, 17 insertions(+), 5 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Tue Sep 03 18:01:10 2019 +0200
+++ b/schema/gemma.sql	Thu Sep 05 11:33:01 2019 +0200
@@ -292,14 +292,17 @@
     )
 
     CREATE TABLE published_services (
-        name varchar 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)),
         style xml CHECK(style IS DOCUMENT),
         as_wms boolean NOT NULL DEFAULT TRUE,
         as_wfs boolean NOT NULL DEFAULT TRUE,
-        -- Either give a valid relation name or a SQL statement:
-        CHECK (to_regclass(name) IS NOT NULL OR view_def IS NOT NULL)
+        -- 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/updates/1112/01.add_view_def.sql	Tue Sep 03 18:01:10 2019 +0200
+++ b/schema/updates/1112/01.add_view_def.sql	Thu Sep 05 11:33:01 2019 +0200
@@ -13,6 +13,15 @@
     STRICT;
 
 ALTER TABLE sys_admin.published_services
-    ALTER name TYPE varchar,
+    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 CHECK (to_regclass(name) IS NOT NULL OR view_def IS NOT NULL);
+    ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL
+            OR view_def IS NOT NULL);