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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2015-12-03 03:13:43 -0800 (Thu, 03 Dec 2015)
New Revision: 9327

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
New function to resolve coupled resources from dataset metadata

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	2015-12-03 09:15:50 UTC (rev 9326)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2015-12-03 11:13:43 UTC (rev 9327)
@@ -696,3 +696,52 @@
 ALTER TABLE mb_metadata ADD COLUMN md_license_source_note text DEFAULT null;
 
 UPDATE gui_element SET e_content='<div id="printPDF_working_bg"></div><div id="printPDF_working"><img src="../img/indicator_wheel.gif" style="padding:10px 0 0 10px">Generating PDF</div><div id="printPDF_input"><form id="printPDF_form" action="../print/printFactory.php"><div id="printPDF_selector"></div><div class="print_option"><input type="hidden" id="map_url" name="map_url" value=""/><input type="hidden" id="legend_url" name="legend_url" value=""/><input type="hidden" id="opacity" name="opacity" value=""/> <input type="hidden" id="overview_url" name="overview_url" value=""/><input type="hidden" id="map_scale" name="map_scale" value=""/><input type="hidden" name="measured_x_values" /><input type="hidden" name="measured_y_values" /><input type="hidden" name="map_svg_kml" /><input type="hidden" name="svg_extent" /><input type="hidden" name="map_svg_measures" /><br /></div><div class="print_option" id="printPDF_formsubmit"><input id="submit" type="submit" value="Print"><br /></div></f
 orm><div id="printPDF_result"></div></div>' WHERE e_id='printPDF';
+
+--new function for resolving coupled resources from metadata id
+-- Function: f_get_coupled_resources(integer)
+
+-- DROP FUNCTION f_get_coupled_resources(integer);
+
+CREATE OR REPLACE FUNCTION f_get_coupled_resources(integer)
+  RETURNS text AS
+$BODY$DECLARE
+  i_metadata_id ALIAS FOR $1;
+  layer_id_str  TEXT;
+  featuretype_id_str TEXT;
+  layer_id_record RECORD;
+  featuretype_id_record RECORD;
+
+BEGIN
+layer_id_str := '"layerIds":[';
+
+FOR layer_id_record IN SELECT DISTINCT 
+
+layer.layer_id from layer inner join ows_relation_metadata on layer.layer_id = ows_relation_metadata.fkey_layer_id where ows_relation_metadata.fkey_metadata_id = $1 and layer_searchable = 1
+
+LOOP
+layer_id_str := layer_id_str || layer_id_record.layer_id || ',';
+END LOOP ;
+  
+featuretype_id_str := '"featuretypeIds":[';
+
+FOR featuretype_id_record IN SELECT DISTINCT 
+
+wfs_featuretype.featuretype_id from wfs_featuretype inner join ows_relation_metadata on wfs_featuretype.featuretype_id = ows_relation_metadata.fkey_layer_id where ows_relation_metadata.fkey_metadata_id = $1 and featuretype_searchable = 1
+
+LOOP
+featuretype_id_str := featuretype_id_str || featuretype_id_record.layer_id || ',';
+END LOOP ;
+
+featuretype_id_str := trim(trailing ',' from featuretype_id_str);
+layer_id_str := trim(trailing ',' from layer_id_str);
+
+RETURN '{"coupledResources":' || '{' || layer_id_str || ']' || ',' || featuretype_id_str || ']' || '}}';
+
+END;
+
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_coupled_resources(integer)
+  OWNER TO postgres;
+