[Mapbender-commits] r10087 - trunk/mapbender/resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2019-03-22 01:09:42 -0700 (Fri, 22 Mar 2019)
New Revision: 10087

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
New postgresql function to resolve coupling between registered layers and featuretypes. This is needed to allow navigation from layertree layers to featuretype datatables in guis.

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2019-03-21 12:55:04 UTC (rev 10086)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2019-03-22 08:09:42 UTC (rev 10087)
@@ -2564,3 +2564,43 @@
 
 ALTER TABLE layer_style ALTER COLUMN legendurl TYPE varchar(2048);
 
+--FUNCTION to get couplings between layers and featuretypes via sql to support navigation from layertree layer elements to wfs getfeature tables in guis
+
+-- Function: f_get_layer_featuretype_coupling()
+
+-- DROP FUNCTION f_get_layer_featuretype_coupling();
+
+    -- examples:
+    --select f_get_layer_featuretype_coupling(array[20370,20369], FALSE);
+    --select f_get_layer_featuretype_coupling(ARRAY(select layer_id from layer), TRUE);
+
+CREATE OR REPLACE FUNCTION f_get_layer_featuretype_coupling(INT[], only_with_wfs_conf boolean default FALSE) RETURNS text AS
+$BODY$
+DECLARE
+    layer_featuretype_relations_json TEXT; --json representation
+    layer_featuretype_relations_record  RECORD;
+BEGIN
+    layer_featuretype_relations_json := '[';
+
+    IF only_with_wfs_conf = TRUE THEN
+        FOR layer_featuretype_relations_record IN SELECT layer_metadata_featuretype.*, CASE WHEN wfs_conf_id IS NULL THEN 0 ELSE wfs_conf_id END, CASE WHEN wfs_conf_type IS NULL THEN 0 ELSE wfs_conf_type END FROM (SELECT a.fkey_layer_id, CASE WHEN a.fkey_metadata_id IS NULL THEN 0 ELSE a.fkey_metadata_id END, CASE WHEN b.fkey_featuretype_id IS NULL THEN 0 ELSE b.fkey_featuretype_id END FROM (SELECT fkey_layer_id, fkey_metadata_id FROM ows_relation_metadata WHERE fkey_layer_id = ANY ( $1 )) AS a, (SELECT fkey_featuretype_id, fkey_metadata_id FROM ows_relation_metadata WHERE fkey_featuretype_id IS NOT null) AS b WHERE a.fkey_metadata_id = b.fkey_metadata_id) AS layer_metadata_featuretype LEFT JOIN wfs_conf ON layer_metadata_featuretype.fkey_featuretype_id = wfs_conf.fkey_featuretype_id  ORDER by fkey_layer_id DESC LOOP
+        
+            layer_featuretype_relations_json := layer_featuretype_relations_json || '{"layerId":' ||layer_featuretype_relations_record.fkey_layer_id || ',"metadataId":' || layer_featuretype_relations_record.fkey_metadata_id || ',"featuretypeId":'|| layer_featuretype_relations_record.fkey_featuretype_id || ',"wfsConfId":'|| layer_featuretype_relations_record.wfs_conf_id || ',"wfsConfType":'|| layer_featuretype_relations_record.wfs_conf_type || '},';
+        END LOOP;
+    ELSIF only_with_wfs_conf = FALSE THEN
+        FOR layer_featuretype_relations_record IN SELECT layer_metadata_featuretype.*, CASE WHEN wfs_conf_id IS NULL THEN 0 ELSE wfs_conf_id END, CASE WHEN wfs_conf_type IS NULL THEN 0 ELSE wfs_conf_type END FROM (SELECT a.fkey_layer_id, CASE WHEN a.fkey_metadata_id IS NULL THEN 0 ELSE a.fkey_metadata_id END, CASE WHEN b.fkey_featuretype_id IS NULL THEN 0 ELSE b.fkey_featuretype_id END FROM (SELECT fkey_layer_id, fkey_metadata_id FROM ows_relation_metadata WHERE fkey_layer_id = ANY ( $1 )) AS a, (SELECT fkey_featuretype_id, fkey_metadata_id FROM ows_relation_metadata WHERE fkey_featuretype_id IS NOT null) AS b WHERE a.fkey_metadata_id = b.fkey_metadata_id) AS layer_metadata_featuretype INNER JOIN wfs_conf ON layer_metadata_featuretype.fkey_featuretype_id = wfs_conf.fkey_featuretype_id  ORDER by fkey_layer_id DESC LOOP
+        
+            layer_featuretype_relations_json := layer_featuretype_relations_json || '{"layerId":' ||layer_featuretype_relations_record.fkey_layer_id || ',"metadataId":' || layer_featuretype_relations_record.fkey_metadata_id || ',"featuretypeId":'|| layer_featuretype_relations_record.fkey_featuretype_id || ',"wfsConfId":'|| layer_featuretype_relations_record.wfs_conf_id || ',"wfsConfType":'|| layer_featuretype_relations_record.wfs_conf_type || '},';
+        END LOOP;
+    END IF;
+    layer_featuretype_relations_json = rtrim(layer_featuretype_relations_json, ',');
+    layer_featuretype_relations_json := layer_featuretype_relations_json || ']'; 
+    RETURN layer_featuretype_relations_json;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_layer_featuretype_coupling(INT[])
+  OWNER TO postgres;
+
+