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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2018-04-13 02:46:51 -0700 (Fri, 13 Apr 2018)
New Revision: 9895

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Add availability and status of wfs to search view

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-04-12 15:20:26 UTC (rev 9894)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2018-04-13 09:46:51 UTC (rev 9895)
@@ -2344,6 +2344,92 @@
 
 UPDATE mb_group SET export2ckan = TRUE WHERE export2ckan IS NULL;
 
+-- Function: f_get_availability_of_ressource(integer, character varying)
 
+-- DROP FUNCTION f_get_availability_of_ressource(integer, character varying);
 
+CREATE OR REPLACE FUNCTION f_get_availability_of_ressource(i_ressource_id integer, s_ressource_type character varying)
+  RETURNS real AS
+$BODY$DECLARE
+  -- s_ressource_type is 'wms' or 'wfs'
+  -- select f_get_availability_of_ressource(1,'wms');
+  r_availability real;
 
+BEGIN
+
+IF s_ressource_type='wms' THEN 
+     r_availability := availability FROM mb_wms_availability WHERE fkey_wms_id = i_ressource_id;
+ELSIF s_ressource_type='wfs' THEN 
+     r_availability := availability FROM mb_wfs_availability WHERE fkey_wfs_id = i_ressource_id;
+END IF;
+
+RETURN r_availability;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_availability_of_ressource(integer, character varying)
+  OWNER TO postgres;
+
+-- Function: f_get_status_of_ressource(integer, character varying)
+
+-- DROP FUNCTION f_get_status_of_ressource(integer, character varying);
+
+CREATE OR REPLACE FUNCTION f_get_status_of_ressource(i_ressource_id integer, s_ressource_type character varying)
+  RETURNS integer AS
+$BODY$DECLARE
+  -- s_ressource_type is 'wms' or 'wfs'
+  -- select f_get_availability_of_ressource(1,'wms');
+  i_status integer;
+
+BEGIN
+
+IF s_ressource_type='wms' THEN 
+     i_status := last_status FROM mb_wms_availability WHERE fkey_wms_id = i_ressource_id;
+ELSIF s_ressource_type='wfs' THEN 
+     i_status := last_status FROM mb_wfs_availability WHERE fkey_wfs_id = i_ressource_id;
+END IF;
+
+RETURN i_status;
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE STRICT
+  COST 100;
+ALTER FUNCTION f_get_status_of_ressource(integer, character varying)
+  OWNER TO postgres;
+
+-- View: search_wfs_view
+
+DROP VIEW search_wfs_view;
+
+CREATE OR REPLACE VIEW search_wfs_view AS 
+
+
+SELECT wfs_new.featuretype_id, wfs_new.featuretype_srs, wfs_new.featuretype_title, wfs_new.featuretype_abstract, wfs_new.featuretype_latlon_bbox, wfs_new.element_type, wfs_new.featuretype_latlon_array, wfs_new.wfs_id, f_get_availability_of_ressource(wfs_new.wfs_id ,'wfs') as availability,  f_get_status_of_ressource(wfs_new.wfs_id ,'wfs') as status, wfs_new.wfs_title, wfs_new.wfs_abstract, wfs_new.wfs_timestamp_create, wfs_new.wfs_timestamp, wfs_new.wfs_network_access, wfs_new.wfs_pricevolume, wfs_new.wfs_proxylog, wfs_new.wfs_owner, wfs_new.country, wfs_new.administrativearea, wfs_new.accessconstraints, wfs_new.fkey_mb_group_id, wfs_new.orga_id, wfs_new.the_geom, wfs_new.bbox, wfs_new.searchtext, wfs_new.wfs_conf_id, wfs_new.wfs_conf_abstract, wfs_new.wfs_conf_description, wfs_new.modultype, wfs_new.department, wfs_new.mb_group_name, wfs_new.mb_group_logo_path, wfs_new.fkey_termsofuse_id, termsofuse.isopen
+   FROM ( SELECT wfs_table.featuretype_id, wfs_table.featuretype_srs, wfs_table.featuretype_title, wfs_table.featuretype_abstract, wfs_table.featuretype_latlon_bbox, wfs_table.element_type, wfs_table.featuretype_latlon_array, wfs_table.wfs_id, wfs_table.wfs_title, wfs_table.wfs_abstract, wfs_table.wfs_timestamp_create, wfs_table.wfs_timestamp, wfs_table.wfs_network_access, wfs_table.wfs_pricevolume, wfs_table.wfs_proxylog, wfs_table.wfs_owner, wfs_table.country, wfs_table.administrativearea, wfs_table.accessconstraints, wfs_table.fkey_mb_group_id, wfs_table.orga_id, wfs_table.the_geom, wfs_table.bbox, wfs_table.searchtext, wfs_table.wfs_conf_id, wfs_table.wfs_conf_abstract, wfs_table.wfs_conf_description, wfs_table.modultype, wfs_table.department, wfs_table.mb_group_name, wfs_table.mb_group_logo_path, wfs_termsofuse.fkey_termsofuse_id
+           FROM ( SELECT wfs_info.featuretype_id, wfs_info.featuretype_srs, wfs_info.featuretype_title, wfs_info.featuretype_abstract, wfs_info.featuretype_latlon_bbox, wfs_info.element_type, wfs_info.featuretype_latlon_array, wfs_info.wfs_id, wfs_info.wfs_title, wfs_info.wfs_abstract, wfs_info.wfs_timestamp_create, wfs_info.wfs_timestamp, wfs_info.wfs_network_access, wfs_info.wfs_pricevolume, wfs_info.wfs_proxylog, wfs_info.wfs_owner, wfs_info.country, wfs_info.administrativearea, wfs_info.accessconstraints, wfs_info.fkey_mb_group_id, wfs_info.orga_id, wfs_info.the_geom, wfs_info.bbox, wfs_info.searchtext, wfs_info.wfs_conf_id, wfs_info.wfs_conf_abstract, wfs_info.wfs_conf_description, wfs_info.modultype, mb_group.mb_group_id AS department, mb_group.mb_group_name, mb_group.mb_group_logo_path
+                   FROM ( SELECT featuretype.featuretype_id, featuretype.featuretype_srs, featuretype.featuretype_title, featuretype.featuretype_abstract, featuretype.featuretype_latlon_bbox, featuretype.element_type, featuretype.featuretype_latlon_array, featuretype.wfs_id, featuretype.wfs_title, featuretype.wfs_abstract, featuretype.wfs_timestamp_create, featuretype.wfs_timestamp, featuretype.wfs_network_access, featuretype.wfs_pricevolume, featuretype.wfs_proxylog, featuretype.wfs_owner, featuretype.country, featuretype.administrativearea, featuretype.accessconstraints, featuretype.fkey_mb_group_id, featuretype.orga_id, geometryfromtext(((((((((((((((((((('POLYGON(('::text || featuretype.featuretype_latlon_array[1]) || ' '::text) || featuretype.featuretype_latlon_array[2]) || ','::text) || featuretype.featuretype_latlon_array[1]) || ' '::text) || featuretype.featuretype_latlon_array[4]) || ','::text) || featuretype.featuretype_latlon_array[3]) || ' '::text) || featuretype.feature
 type_latlon_array[4]) || ','::text) || featuretype.featuretype_latlon_array[3]) || ' '::text) || featuretype.featuretype_latlon_array[2]) || ','::text) || featuretype.featuretype_latlon_array[1]) || ' '::text) || featuretype.featuretype_latlon_array[2]) || '))'::text, 4326) AS the_geom, (((((featuretype.featuretype_latlon_array[1] || ','::text) || featuretype.featuretype_latlon_array[2]) || ','::text) || featuretype.featuretype_latlon_array[3]) || ','::text) || featuretype.featuretype_latlon_array[4] AS bbox, f_collect_searchtext_wfs(featuretype.wfs_id, featuretype.featuretype_id) AS searchtext, wfs_conf.wfs_conf_id, wfs_conf.wfs_conf_abstract, wfs_conf.wfs_conf_description, wfs_conf.wfs_conf_type AS modultype
+                           FROM ( SELECT DISTINCT wfs_featuretype.featuretype_id, wfs_featuretype.featuretype_srs, wfs_featuretype.featuretype_title, wfs_featuretype.featuretype_abstract, wfs_featuretype.featuretype_latlon_bbox, f_get_geometry_type(wfs_featuretype.featuretype_id) AS element_type, 
+                                        CASE
+                                            WHEN wfs_featuretype.featuretype_latlon_bbox::text = ''::text THEN string_to_array('-180,-90,180,90'::text, ','::text)
+                                            WHEN wfs_featuretype.featuretype_latlon_bbox IS NULL THEN string_to_array('-180,-90,180,90'::text, ','::text)
+                                            ELSE string_to_array(wfs_featuretype.featuretype_latlon_bbox::text, ','::text)
+                                        END AS featuretype_latlon_array, wfs.wfs_id, wfs.wfs_title, wfs.wfs_abstract, wfs.wfs_timestamp_create, wfs.wfs_timestamp, wfs.wfs_network_access, wfs.wfs_pricevolume, wfs.wfs_proxylog, wfs.wfs_owner, wfs.country, wfs.administrativearea, wfs.accessconstraints, wfs.fkey_mb_group_id, wfs.orga_id
+                                   FROM ( SELECT DISTINCT wfs.wfs_id, wfs.wfs_title, wfs.wfs_abstract, wfs.wfs_timestamp_create, wfs.wfs_timestamp, wfs.wfs_network_access, wfs.wfs_pricevolume, wfs.wfs_proxylog, wfs.wfs_owner, wfs.country, wfs.administrativearea, wfs.accessconstraints, wfs.fkey_mb_group_id, f_get_responsible_organization(wfs.wfs_owner, COALESCE(wfs.fkey_mb_group_id, 0)) AS orga_id
+                                           FROM wfs
+                                          WHERE (wfs.wfs_owner IN ( SELECT users_for_publishing.fkey_mb_user_id
+                                                   FROM users_for_publishing))
+                                          ORDER BY wfs.wfs_id) wfs
+                              LEFT JOIN wfs_featuretype ON wfs.wfs_id = wfs_featuretype.fkey_wfs_id
+                             WHERE wfs_featuretype.featuretype_searchable = 1) featuretype
+                      LEFT JOIN wfs_conf ON featuretype.featuretype_id = wfs_conf.fkey_featuretype_id) wfs_info
+              LEFT JOIN mb_group ON wfs_info.orga_id = mb_group.mb_group_id) wfs_table
+      LEFT JOIN wfs_termsofuse ON wfs_table.wfs_id = wfs_termsofuse.fkey_wfs_id) wfs_new
+   LEFT JOIN termsofuse ON wfs_new.fkey_termsofuse_id = termsofuse.termsofuse_id
+  WHERE wfs_new.element_type IS NOT NULL;
+
+ALTER TABLE search_wfs_view
+  OWNER TO postgres;
+