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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2018-11-05 07:02:57 -0800 (Mon, 05 Nov 2018)
New Revision: 9988

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Fix for export of edited metadata - bequeauth the categories from layers and featuretypes to metadata which was edited by metadator

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-11-02 09:06:19 UTC (rev 9987)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2018-11-05 15:02:57 UTC (rev 9988)
@@ -2450,4 +2450,117 @@
 
 UPDATE gui_element_vars SET var_value = '../css/metadataeditor.css' , var_type = 'file/css' WHERE fkey_gui_id like 'admin_%' AND fkey_e_id = 'body' AND var_name = 'css_class_bg';
 
+-- Function: f_collect_topic_cat_dataset(integer)
 
+-- DROP FUNCTION f_collect_topic_cat_dataset(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_dataset(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_dataset_serial_id ALIAS FOR $1;
+  topic_cat_string  TEXT;
+  topic_cat_record  RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT DISTINCT fkey_md_topic_category_id FROM (
+
+SELECT mb_metadata_md_topic_category.fkey_md_topic_category_id from mb_metadata_md_topic_category WHERE mb_metadata_md_topic_category.fkey_metadata_id= $1
+
+UNION 
+
+SELECT layer_md_topic_category.fkey_md_topic_category_id from layer_md_topic_category WHERE fkey_layer_id IN (SELECT fkey_layer_id FROM ows_relation_metadata WHERE fkey_metadata_id = $1)
+
+UNION
+
+SELECT wfs_featuretype_md_topic_category.fkey_md_topic_category_id from wfs_featuretype_md_topic_category WHERE fkey_featuretype_id IN (SELECT fkey_featuretype_id FROM ows_relation_metadata WHERE fkey_metadata_id = $1)) as md_topic_category LOOP
+
+topic_cat_string := topic_cat_string || '{' ||topic_cat_record.fkey_md_topic_category_id || '}';
+END LOOP ;
+  
+RETURN topic_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_topic_cat_dataset(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_inspire_cat_dataset(integer)
+
+-- DROP FUNCTION f_collect_inspire_cat_dataset(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_inspire_cat_dataset(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_dataset_id ALIAS FOR $1;
+  inspire_cat_string  TEXT;
+  inspire_cat_record  RECORD;
+
+BEGIN
+inspire_cat_string := '';
+
+FOR inspire_cat_record IN SELECT DISTINCT fkey_inspire_category_id FROM (
+
+SELECT mb_metadata_inspire_category.fkey_inspire_category_id from mb_metadata_inspire_category WHERE mb_metadata_inspire_category.fkey_metadata_id= $1
+
+UNION 
+
+SELECT layer_inspire_category.fkey_inspire_category_id from layer_inspire_category WHERE fkey_layer_id IN (SELECT fkey_layer_id FROM ows_relation_metadata WHERE fkey_metadata_id = $1)
+
+UNION
+
+SELECT wfs_featuretype_inspire_category.fkey_inspire_category_id from wfs_featuretype_inspire_category WHERE fkey_featuretype_id IN (SELECT fkey_featuretype_id FROM ows_relation_metadata WHERE fkey_metadata_id = $1)) as inspire_category LOOP
+inspire_cat_string := inspire_cat_string || '{' ||inspire_cat_record.fkey_inspire_category_id || '}';
+END LOOP ;
+  
+RETURN inspire_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_inspire_cat_dataset(integer)
+  OWNER TO postgres;
+
+-- Function: f_collect_custom_cat_dataset(integer)
+
+-- DROP FUNCTION f_collect_custom_cat_dataset(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_custom_cat_dataset(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_dataset_id ALIAS FOR $1;
+  custom_cat_string  TEXT;
+  custom_cat_record  RECORD;
+
+BEGIN
+custom_cat_string := '';
+
+FOR custom_cat_record IN SELECT DISTINCT fkey_custom_category_id FROM (
+
+SELECT mb_metadata_custom_category.fkey_custom_category_id from mb_metadata_custom_category WHERE mb_metadata_custom_category.fkey_metadata_id= $1
+
+UNION 
+
+SELECT layer_custom_category.fkey_custom_category_id from layer_custom_category WHERE fkey_layer_id IN (SELECT fkey_layer_id FROM ows_relation_metadata WHERE fkey_metadata_id = $1)
+
+UNION
+
+SELECT wfs_featuretype_custom_category.fkey_custom_category_id from wfs_featuretype_custom_category WHERE fkey_featuretype_id IN (SELECT fkey_featuretype_id FROM ows_relation_metadata WHERE fkey_metadata_id = $1)) as custom_category LOOP
+custom_cat_string := custom_cat_string || '{' ||custom_cat_record.fkey_custom_category_id || '}';
+END LOOP ;
+  
+RETURN custom_cat_string;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_collect_custom_cat_dataset(integer)
+  OWNER TO postgres;
+
+
+