[Mapbender-commits] r8232 - branches/2.7/resources/db/pgsql/UTF-8/update

[svn_mapbender at osgeo.org]

Author: astrid_emde
Date: 2012-01-17 01:54:44 -0800 (Tue, 17 Jan 2012)
New Revision: 8232

Modified:
   branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql
Log:
meged with trunk: added functions:
 f_collect_searchtext_wmc(integer)
f_collect_searchtext(integer, integer)
f_count_layer_couplings(integer)

Modified: branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql
===================================================================
--- branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql	2012-01-17 09:52:43 UTC (rev 8231)
+++ branches/2.7/resources/db/pgsql/UTF-8/update/update_2.7.2_to_2.7.3_pgsql_UTF-8.sql	2012-01-17 09:54:44 UTC (rev 8232)
@@ -99,6 +99,88 @@
 --get featureInfoTunnel ready for working with toggleModule
 UPDATE gui_element SET e_attributes = NULL WHERE e_id = 'featureInfoTunnel';
 
+-- Function: f_count_layer_couplings(integer)
+
+DROP FUNCTION f_count_layer_couplings(integer);
+CREATE OR REPLACE FUNCTION f_count_layer_couplings(integer)
+  RETURNS integer AS
+$BODY$
+DECLARE
+   layer_rel int4;
+BEGIN
+layer_rel := count(*) from ows_relation_metadata WHERE fkey_layer_id=$1;
+RETURN layer_rel;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE;
+
+-- Function: f_collect_searchtext(integer, integer)
+
+-- DROP FUNCTION f_collect_searchtext(integer, integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext(integer, integer)
+  RETURNS text AS
+$BODY$DECLARE
+    p_wms_id ALIAS FOR $1;
+    p_layer_id ALIAS FOR $2;
+    
+    r_keywords RECORD;
+    l_result TEXT;
+BEGIN
+    l_result := '';
+    l_result := l_result || (SELECT COALESCE(wms_title, '') || ' ' || COALESCE(wms_abstract, '')  || ' ' || wms_id::text FROM wms WHERE wms_id = p_wms_id);
+    l_result := l_result || (SELECT COALESCE(layer_name, '')|| ' ' || COALESCE(layer_title, '')  || ' ' || COALESCE(layer_abstract, '') || ' ' || layer_id::text FROM layer WHERE layer_id = p_layer_id);
+    FOR r_keywords IN SELECT DISTINCT keyword FROM
+        (SELECT keyword FROM layer_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id AND L.fkey_layer_id = p_layer_id)
+        ) AS __keywords__ LOOP
+        l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+    END LOOP;
+    FOR r_keywords IN SELECT DISTINCT md_topic_category_code_de FROM
+        (SELECT md_topic_category_code_de FROM md_topic_category T JOIN layer_md_topic_category C ON (C.fkey_md_topic_category_id = T.md_topic_category_id AND C.fkey_layer_id = p_layer_id)
+        ) AS __keywords__ LOOP
+        l_result := l_result || ' ' || COALESCE(r_keywords.md_topic_category_code_de, '');
+    END LOOP;
+   l_result := UPPER(l_result);
+   l_result := replace(replace(replace(replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE'),'ä','AE'),'ü','UE'),'ö','OE');
+
+    RETURN l_result;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+
+-- Fix error of missing wmc_serial_id
+
+-- Function: f_collect_searchtext_wmc(integer)
+
+-- DROP FUNCTION f_collect_searchtext_wmc(integer);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext_wmc(integer)
+  RETURNS text AS
+$BODY$
+DECLARE
+    p_wmc_id ALIAS FOR $1;
+    
+    r_keywords RECORD;
+    l_result TEXT;
+BEGIN
+    l_result := '';
+    l_result := l_result || (SELECT COALESCE(wmc_title, '') || ' ' || COALESCE(abstract, '') FROM mb_user_wmc WHERE wmc_serial_id = p_wmc_id);
+    FOR r_keywords IN SELECT DISTINCT keyword FROM
+        (SELECT keyword FROM wmc_keyword L JOIN keyword K ON (K.keyword_id = L.fkey_keyword_id AND L.fkey_wmc_serial_id = p_wmc_id)
+        ) AS __keywords__ LOOP
+        l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+    END LOOP;
+   l_result := UPPER(l_result);
+   l_result := replace(replace(replace(replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE'),'ä','AE'),'ü','UE'),'ö','OE');
+
+    RETURN l_result;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+
 -- Fix: wrong tooltip for Legend-button
 UPDATE gui_element SET e_title = 'Legend' WHERE e_id = 'legendButton' AND fkey_gui_id = 'template_print';