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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2010-04-22 05:54:46 -0400 (Thu, 22 Apr 2010)
New Revision: 5999

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql
Log:
view and functions to allow search over published wmc by searchmodule 

Modified: trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql
===================================================================
--- trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql	2010-04-22 09:45:43 UTC (rev 5998)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.6.1_to_2.7rc1_pgsql_UTF-8.sql	2010-04-22 09:54:46 UTC (rev 5999)
@@ -2282,4 +2282,97 @@
 
 -- increase size of target field
 ALTER TABLE gui_element ALTER COLUMN e_target TYPE VARCHAR(255);
+--function to collect the md categories for a single wmc into a column 
 
+
+CREATE OR REPLACE FUNCTION f_collect_topic_cat_wmc(character varying)
+  RETURNS text AS
+  $BODY$DECLARE
+  i_wmc_id ALIAS FOR $1;
+  topic_cat_string  TEXT;
+  topic_cat_record  RECORD;
+
+BEGIN
+topic_cat_string := '';
+
+FOR topic_cat_record IN SELECT wmc_md_topic_category.fkey_md_topic_category_id from wmc_md_topic_category WHERE wmc_md_topic_category.fkey_wmc_id=$1  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;
+ALTER FUNCTION f_collect_topic_cat_wmc(character varying) OWNER TO postgres;
+
+
+
+-- Function: f_collect_searchtext_wmc(varchar)
+
+-- DROP FUNCTION f_collect_searchtext_wmc(varchar);
+
+CREATE OR REPLACE FUNCTION f_collect_searchtext_wmc(varchar)
+  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_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 )
+        ) AS __keywords__ LOOP
+        l_result := l_result || ' ' || COALESCE(r_keywords.keyword, '');
+    END LOOP;
+   l_result := UPPER(l_result);
+   l_result := replace(replace(replace(replace(l_result,'Ä','AE'),'ß','SS'),'Ö','OE'),'Ü','UE');
+
+    RETURN l_result;
+END;
+$BODY$
+  LANGUAGE 'plpgsql' VOLATILE;
+ALTER FUNCTION f_collect_searchtext_wmc(varchar) OWNER TO postgres;
+
+
+--set mb_user_mb_group_type
+--of mb_user_mb_group to 1 for root!
+
+UPDATE mb_user_mb_group SET mb_user_mb_group_type = 1 WHERE fkey_mb_user_id = 1 AND fkey_mb_group_id = 20;
+--generate demo group for decentral registrating and publishing services
+
+-- View: search_wmc_view
+
+-- DROP VIEW search_wmc_view;
+--TODO: get group information out of mb_group table instead of ows information, cause in this case there can be more than one different service in a resource 
+
+-- View: search_wmc_view
+
+-- DROP VIEW search_wmc_view;
+--TODO: get group information out of mb_group table instead of ows information, cause in this case there can be more than one different service in a resource 
+--TODO: set public flag to some wmc docs to generate usefull results
+
+CREATE OR REPLACE VIEW search_wmc_view AS 
+ SELECT wmc_dep.fkey_user_id as user_id, wmc_dep.wmc_id , wmc_dep.srs as wmc_srs, wmc_dep.wmc_title , wmc_dep.abstract as wmc_abstract, f_collect_searchtext_wmc(wmc_dep.wmc_id) AS searchtext
+  , wmc_dep.wmc_timestamp, wmc_dep.department, wmc_dep.mb_group_name,wmc_dep.mb_group_title,wmc_dep.mb_group_country, wmc_dep.mb_group_stateorprovince ,f_collect_topic_cat_wmc(wmc_dep.wmc_id) as md_topic_cats,transform(geometryfromtext(((((((((((((((((((('POLYGON(('::text || wmc_dep.minx::text) || ' '::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.minx::text) || ' '::text) || wmc_dep.maxy::text) || ','::text) || wmc_dep.maxx::text) || ' '::text) || wmc_dep.maxy::text) || ','::text) || wmc_dep.maxx::text) || ' '::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.minx::text) || ' '::text) || wmc_dep.miny::text) || '))'::text, regexp_replace(UPPER(wmc_dep.srs), 'EPSG:', '')::INTEGER),4326) AS the_geom, (((((wmc_dep.minx::text || ','::text) || wmc_dep.miny::text) || ','::text) || wmc_dep.maxx::text) || ','::text) || wmc_dep.maxy::text AS bbox 
+	FROM ( SELECT mb_user_wmc.wmc_public, mb_user_wmc.maxy ,mb_user_wmc.maxx ,mb_user_wmc.miny ,mb_user_wmc.minx ,mb_user_wmc.srs ,mb_user_wmc.wmc_id, mb_user_wmc.wmc_title, mb_user_wmc.abstract, mb_user_wmc.wmc_timestamp, mb_user_wmc.fkey_user_id, user_dep.mb_group_id AS department, user_dep.mb_group_name,
+user_dep.mb_group_title,user_dep.mb_group_country, user_dep.mb_group_stateorprovince
+		FROM ( SELECT registrating_groups.fkey_mb_user_id AS mb_user_id, mb_group.mb_group_id, mb_group.mb_group_name, mb_group.mb_group_title, mb_group.mb_group_country, mb_group.mb_group_stateorprovince
+			FROM registrating_groups, mb_group WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) as user_dep, mb_user_wmc
+		WHERE user_dep.mb_user_id = mb_user_wmc.fkey_user_id) wmc_dep WHERE  wmc_dep.wmc_public = 1 ORDER BY wmc_dep.wmc_id;
+
+ALTER TABLE search_wmc_view OWNER TO postgres;
+
+-- insert deleteWFSConf modul in admin1
+--
+-- add delete wfs conf module from geoportal.rlp
+--
+INSERT INTO gui_element(fkey_gui_id, e_id, e_pos, e_public, e_comment, e_title, e_element, e_src, e_attributes, e_left, e_top, e_width, e_height, e_z_index, e_more_styles, e_content, e_closetag, e_js_file, e_mb_mod, e_target, e_requires, e_url) VALUES('admin1','deleteWFSConf',2,1,'delete wfs conf','','a','','href = "../javascripts/mod_deleteWfsConf_client.html" target="AdminFrame"',10,1204,250,20,NULL ,'font-family: Arial, Helvetica, sans-serif; font-size : 12px; text-decoration : none; color: #808080;','DELETE WFS CONF','a','','','','AdminFrame','');
+--
+
+
+
+