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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2018-02-06 01:05:12 -0800 (Tue, 06 Feb 2018)
New Revision: 9858

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
New function to pull responsible organization for ressource (dataset,wms,wfs) - needed for ckan sync

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	2018-02-01 15:53:20 UTC (rev 9857)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2018-02-06 09:05:12 UTC (rev 9858)
@@ -2291,3 +2291,49 @@
 ALTER FUNCTION mb_monitor_after()
   OWNER TO postgres;
 
+-- new function to pull responsible organization
+-- Function: f_get_responsible_organization_for_ressource(integer, varchar)
+
+-- DROP FUNCTION f_get_responsible_organization_for_ressource(integer, varchar);
+
+CREATE OR REPLACE FUNCTION f_get_responsible_organization_for_ressource(i_ressource_id integer, s_ressource_type varchar)
+  RETURNS integer AS
+$BODY$DECLARE
+  -- s_ressource_type is 'dataset', 'wms' or 'wfs'
+  -- select f_get_responsible_organization_for_ressource(1,'dataset');
+  i_resp_orga_id INTEGER;
+
+BEGIN
+
+IF s_ressource_type='dataset' THEN 
+     i_resp_orga_id := fkey_mb_group_id FROM mb_metadata WHERE metadata_id = i_ressource_id;
+     IF i_resp_orga_id IS NULL THEN
+        --get primary group for fkey_user_id
+	i_resp_orga_id := fkey_mb_group_id FROM mb_user_mb_group WHERE mb_user_mb_group_type = 2 AND fkey_mb_user_id = (SELECT fkey_mb_user_id FROM mb_metadata WHERE metadata_id = i_ressource_id) LIMIT 1;
+        --get primary group for fkey_user_id
+     END IF;
+ELSIF s_ressource_type='wms' THEN 
+     i_resp_orga_id := fkey_mb_group_id FROM wms WHERE wms_id = i_ressource_id;
+     IF i_resp_orga_id IS NULL THEN
+        --get primary group for fkey_user_id
+	i_resp_orga_id := fkey_mb_group_id FROM mb_user_mb_group WHERE mb_user_mb_group_type = 2 AND fkey_mb_user_id = (SELECT wms_owner FROM wms WHERE wms_id = i_ressource_id) LIMIT 1;
+        --get primary group for fkey_user_id
+     END IF;
+ELSIF s_ressource_type='wfs' THEN 
+     i_resp_orga_id := fkey_mb_group_id FROM wfs WHERE wfs_id = i_ressource_id;
+     IF i_resp_orga_id IS NULL THEN
+        --get primary group for fkey_user_id
+	i_resp_orga_id := fkey_mb_group_id FROM mb_user_mb_group WHERE mb_user_mb_group_type = 2 AND fkey_mb_user_id = (SELECT wfs_owner FROM wfs WHERE wfs_id = i_ressource_id) LIMIT 1;
+        --get primary group for fkey_user_id
+     END IF;
+END IF;
+
+RETURN i_resp_orga_id;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_responsible_organization_for_ressource(integer, varchar)
+  OWNER TO postgres;
+