Mercurial > gemma
changeset 4605:7650d2431f86 geoserver_sql_views
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);