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

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2017-03-30 03:26:52 -0700 (Thu, 30 Mar 2017)
New Revision: 9728

Modified:
   trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Fix for views which depends on each other

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	2017-03-30 10:06:30 UTC (rev 9727)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql	2017-03-30 10:26:52 UTC (rev 9728)
@@ -1898,5 +1898,35 @@
 ALTER TABLE search_dataset_view
   OWNER TO postgres;
 
+-- recreate view, cause it depends on earlier view :-(
 
+-- new view for wfs search
 
+-- View: search_wfs_view_2
+
+DROP VIEW search_wfs_view;
+
+CREATE OR REPLACE VIEW search_wfs_view AS 
+
+SELECT wfs_new.*, isopen FROM (SELECT wfs_table.*, wfs_termsofuse.fkey_termsofuse_id FROM
+(SELECT wfs_info.*, mb_group.mb_group_id as department, mb_group.mb_group_name, mb_group_logo_path  FROM (SELECT featuretype.*, 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.featuretype_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(wfs_id, featuretype.featuretype_id) AS searchtext, wfs_conf_id, wfs_conf_abstract, wfs_conf_description, wfs_conf_type as modultype FROM 
+
+(SELECT DISTINCT featuretype_id, featuretype_srs, featuretype_title, featuretype_abstract,  featuretype_latlon_bbox, f_get_geometry_type(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.*  FROM 
+
+(SELECT DISTINCT wfs_id, wfs_title, wfs_abstract, wfs_timestamp_create, wfs_timestamp, wfs_network_access, wfs_pricevolume, wfs_proxylog, wfs_owner, country, administrativearea, accessconstraints, fkey_mb_group_id, f_get_responsible_organization(wfs_owner, COALESCE(fkey_mb_group_id, 0)) AS orga_id
+  
+FROM wfs WHERE wfs_owner IN (SELECT fkey_mb_user_id FROM users_for_publishing) ORDER BY wfs_id) AS wfs 
+
+LEFT JOIN wfs_featuretype ON wfs.wfs_id = wfs_featuretype.fkey_wfs_id WHERE wfs_featuretype.featuretype_searchable = 1) AS featuretype
+
+LEFT JOIN wfs_conf ON featuretype.featuretype_id = wfs_conf.fkey_featuretype_id) AS wfs_info LEFT JOIN mb_group ON wfs_info.orga_id = mb_group.mb_group_id) AS wfs_table LEFT JOIN wfs_termsofuse ON wfs_table.wfs_id = wfs_termsofuse.fkey_wfs_id) AS wfs_new LEFT JOIN termsofuse ON wfs_new.fkey_termsofuse_id = termsofuse.termsofuse_id 
+
+ WHERE element_type IS NOT NULL;
+
+ALTER TABLE search_wfs_view
+  OWNER TO postgres;
+