# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1567442715 -7200
# Node ID 9fef9930aa8ab383b7f9d3cd10c073f3411ae7a0
# Parent  e5a831ecd557c5502adc8191081f742fb2257c99
Add field for GeoServer SQL view definition

diff -r e5a831ecd557 -r 9fef9930aa8a schema/gemma.sql
--- a/schema/gemma.sql	Mon Sep 02 18:38:27 2019 +0200
+++ b/schema/gemma.sql	Mon Sep 02 18:45:15 2019 +0200
@@ -23,6 +23,25 @@
 CREATE EXTENSION btree_gist;
 
 --
+-- Functions to be used in CHECK constraints
+--
+
+-- Check if a given string can be used as a FROM item in an SQL statement
+CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
+AS $$
+BEGIN
+    EXECUTE format('SELECT * FROM (%s) AS test', stmt);
+    RETURN true;
+EXCEPTION
+    WHEN OTHERS THEN
+        RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
+        RETURN false;
+END
+$$
+    LANGUAGE plpgsql;
+
+
+--
 -- Trigger functions
 --
 -- TODO: will there ever be UPDATEs or can we drop that function due to
@@ -272,10 +291,14 @@
     )
 
     CREATE TABLE published_services (
-        name regclass PRIMARY KEY,
+        name varchar PRIMARY KEY,
+        -- 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
+        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)
     )
 ;
 
diff -r e5a831ecd557 -r 9fef9930aa8a schema/gemma_tests.sql
--- a/schema/gemma_tests.sql	Mon Sep 02 18:38:27 2019 +0200
+++ b/schema/gemma_tests.sql	Mon Sep 02 18:45:15 2019 +0200
@@ -15,6 +15,12 @@
 -- pgTAP test script for gemma schema definition
 --
 
+SELECT ok(is_valid_from_item('SELECT * FROM sys_admin.published_services'),
+    'Valid statement passes check');
+
+SELECT ok(NOT is_valid_from_item('This is not SQL'),
+    'Arbitrary text does not pass check');
+
 SELECT throws_ok($$
     INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES
         (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test'),
diff -r e5a831ecd557 -r 9fef9930aa8a schema/run_tests.sh
--- a/schema/run_tests.sh	Mon Sep 02 18:38:27 2019 +0200
+++ b/schema/run_tests.sh	Mon Sep 02 18:45:15 2019 +0200
@@ -80,7 +80,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f "$BASEDIR"/tap_tests_data.sql \
-    -c "SELECT plan(73 + (
+    -c "SELECT plan(75 + (
             SELECT count(*)::int
                 FROM information_schema.tables
                 WHERE table_schema = 'waterway'))" \