[Mapbender-commits] r9628 - trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Tue Dec 13 08:09:17 PST 2016
Author: verenadiewald
Date: 2016-12-13 08:09:17 -0800 (Tue, 13 Dec 2016)
New Revision: 9628
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
featuretype_title auf varchar 100 ge?\195?\164ndert, abh?\195?\164ngige Views zuvor gel?\195?\182scht und dann neu angelegt
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 2016-12-13 15:50:48 UTC (rev 9627)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql 2016-12-13 16:09:17 UTC (rev 9628)
@@ -1290,3 +1290,239 @@
ALTER TABLE wms ADD COLUMN wms_bequeath_contact_info INTEGER;
-- New column for metadata proxy - if some information from service should be inherited!
ALTER TABLE mb_metadata ADD COLUMN md_proxy BOOLEAN;
+
+
+-- Loesche die abhängigen Sichten, um anschließend die Tabelle wfs_featuretype anpassen zu können
+DROP VIEW mapbender.search_wfs_view;
+DROP VIEW mapbender.wfs_service_metadata_new;
+DROP VIEW mapbender.wfs_service_metadata;
+
+-- Änderung Tabellenspalte auf varchar(100)
+alter table wfs_featuretype alter column featuretype_title TYPE varchar(100);
+
+
+-- Erneutes anlegen der 3 zuvor gelöschten Sichten search_wfs_view,wfs_service_metadata_new,wfs_service_metadata
+CREATE OR REPLACE VIEW mapbender.search_wfs_view AS
+ SELECT wfs_without_geom.wfs_id,
+ wfs_without_geom.wfs_title,
+ wfs_without_geom.wfs_abstract,
+ wfs_without_geom.administrativearea,
+ wfs_without_geom.country,
+ wfs_without_geom.accessconstraints,
+ wfs_without_geom.termsofuse,
+ wfs_without_geom.isopen,
+ wfs_without_geom.wfs_owner,
+ wfs_without_geom.featuretype_id,
+ wfs_without_geom.featuretype_srs,
+ wfs_without_geom.featuretype_title,
+ wfs_without_geom.featuretype_abstract,
+ wfs_without_geom.searchtext,
+ wfs_without_geom.element_type,
+ wfs_without_geom.wfs_conf_id,
+ wfs_without_geom.wfs_conf_abstract,
+ wfs_without_geom.wfs_conf_description,
+ wfs_without_geom.modultype,
+ wfs_without_geom.wfs_timestamp,
+ wfs_without_geom.department,
+ wfs_without_geom.mb_group_name,
+ wfs_without_geom.mb_group_logo_path,
+ wfs_without_geom.wfs_network_access,
+ wfs_without_geom.wfs_pricevolume,
+ wfs_without_geom.wfs_proxylog,
+ wfs_without_geom.featuretype_latlon_bbox,
+ wfs_without_geom.featuretype_latlon_array,
+ geometryfromtext(((((((((((((((((((('POLYGON(('::text || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[4]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[1]) || ' '::text) || wfs_without_geom.featuretype_latlon_array[2]) || '))'::text, 4326) AS the_geom,
+ (((((wfs_without_geom.featuretype_latlon_array[1] || ','::text) || wfs_without_geom.featuretype_latlon_array[2]) || ','::text) || wfs_without_geom.featuretype_latlon_array[3]) || ','::text) || wfs_without_geom.featuretype_latlon_array[4] AS bbox
+ FROM ( SELECT wfs_element.wfs_id,
+ wfs_element.wfs_title,
+ wfs_element.wfs_abstract,
+ wfs_element.administrativearea,
+ wfs_element.country,
+ wfs_element.accessconstraints,
+ wfs_element.termsofuse,
+ wfs_element.isopen,
+ wfs_element.wfs_owner,
+ wfs_element.featuretype_id,
+ wfs_element.featuretype_srs,
+ wfs_element.featuretype_title,
+ wfs_element.featuretype_abstract,
+ wfs_element.searchtext,
+ wfs_element.element_type,
+ wfs_element.wfs_timestamp,
+ wfs_element.department,
+ wfs_element.mb_group_name,
+ wfs_element.mb_group_logo_path,
+ wfs_element.wfs_network_access,
+ wfs_element.wfs_pricevolume,
+ wfs_element.wfs_proxylog,
+ wfs_element.featuretype_latlon_bbox,
+ wfs_element.featuretype_latlon_array,
+ wfs_conf.wfs_conf_id,
+ wfs_conf.wfs_conf_abstract,
+ wfs_conf.wfs_conf_description,
+ f_getwfsmodultype(wfs_conf.wfs_conf_id) AS modultype
+ FROM ( SELECT wfs_dep.wfs_id,
+ wfs_dep.wfs_title,
+ wfs_dep.wfs_abstract,
+ wfs_dep.administrativearea,
+ wfs_dep.country,
+ wfs_dep.accessconstraints,
+ wfs_dep.termsofuse,
+ wfs_dep.isopen,
+ wfs_dep.wfs_owner,
+ wfs_featuretype.featuretype_id,
+ wfs_featuretype.featuretype_srs,
+ wfs_featuretype.featuretype_title,
+ wfs_featuretype.featuretype_abstract,
+ f_collect_searchtext_wfs(wfs_dep.wfs_id, wfs_featuretype.featuretype_id) AS searchtext,
+ wfs_element_1.element_type,
+ wfs_dep.wfs_timestamp,
+ wfs_dep.department,
+ wfs_dep.mb_group_name,
+ wfs_dep.mb_group_logo_path,
+ wfs_dep.wfs_network_access,
+ wfs_dep.wfs_pricevolume,
+ wfs_dep.wfs_proxylog,
+ wfs_featuretype.featuretype_latlon_bbox,
+ 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
+ FROM ( SELECT wfs.wfs_id,
+ wfs.wfs_title,
+ wfs.wfs_abstract,
+ wfs.administrativearea,
+ wfs.country,
+ wfs.accessconstraints,
+ f_getwfs_tou(wfs.wfs_id) AS termsofuse,
+ f_tou_isopen(f_getwfs_tou(wfs.wfs_id)) AS isopen,
+ wfs.wfs_timestamp,
+ wfs.wfs_owner,
+ wfs.wfs_proxylog,
+ wfs.wfs_network_access,
+ wfs.wfs_pricevolume,
+ user_dep.mb_group_id AS department,
+ user_dep.mb_group_name,
+ user_dep.mb_group_logo_path
+ 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_logo_path
+ FROM registrating_groups,
+ mb_group
+ WHERE registrating_groups.fkey_mb_group_id = mb_group.mb_group_id) user_dep,
+ wfs
+ WHERE user_dep.mb_user_id = wfs.wfs_owner) wfs_dep,
+ wfs_featuretype,
+ wfs_element wfs_element_1
+ WHERE wfs_featuretype.fkey_wfs_id = wfs_dep.wfs_id AND wfs_featuretype.featuretype_searchable = 1 AND wfs_element_1.element_type::text ~~ '%Type'::text AND wfs_featuretype.featuretype_id = wfs_element_1.fkey_featuretype_id
+ ORDER BY wfs_featuretype.featuretype_id) wfs_element
+ LEFT JOIN wfs_conf ON wfs_element.featuretype_id = wfs_conf.fkey_featuretype_id) wfs_without_geom;
+
+ALTER TABLE search_wfs_view
+ OWNER TO postgres;
+
+
+CREATE OR REPLACE VIEW mapbender.wfs_service_metadata_new AS
+ SELECT wfs_dep.wfs_id,
+ wfs_dep.wfs_title,
+ wfs_dep.wfs_abstract,
+ wfs_dep.administrativearea,
+ wfs_dep.country,
+ wfs_dep.accessconstraints,
+ wfs_dep.termsofuse,
+ wfs_dep.wfs_owner,
+ wfs_featuretype.featuretype_id,
+ wfs_featuretype.featuretype_srs,
+ wfs_featuretype.featuretype_title,
+ wfs_featuretype.featuretype_abstract,
+ f_collect_searchtext_wfs(wfs_dep.wfs_id, wfs_featuretype.featuretype_id) AS searchtext,
+ wfs_element.element_type,
+ wfs_conf.wfs_conf_id,
+ wfs_conf.wfs_conf_abstract,
+ wfs_conf.wfs_conf_description,
+ f_getwfsmodultype(wfs_conf.wfs_conf_id) AS modultype,
+ wfs_dep.wfs_timestamp,
+ wfs_dep.department,
+ wfs_dep.mb_group_name
+ FROM ( SELECT wfs.wfs_id,
+ wfs.wfs_title,
+ wfs.wfs_abstract,
+ wfs.administrativearea,
+ wfs.country,
+ wfs.accessconstraints,
+ f_getwfs_tou(wfs.wfs_id) AS termsofuse,
+ wfs.wfs_timestamp,
+ wfs.wfs_owner,
+ user_dep.mb_group_description AS department,
+ user_dep.mb_group_name
+ FROM ( SELECT mb_user.mb_user_id,
+ mb_group.mb_group_description,
+ mb_group.mb_group_name
+ FROM mb_user,
+ mb_group
+ WHERE mb_user.mb_user_department::text <> ''::text AND mb_user.mb_user_department::text = mb_group.mb_group_description::text) user_dep,
+ wfs
+ WHERE user_dep.mb_user_id = wfs.wfs_owner) wfs_dep,
+ wfs_featuretype,
+ wfs_element,
+ wfs_conf
+ WHERE wfs_featuretype.fkey_wfs_id = wfs_dep.wfs_id AND wfs_featuretype.featuretype_searchable = 1 AND wfs_element.element_type::text ~~ '%Type'::text AND wfs_featuretype.featuretype_id = wfs_element.fkey_featuretype_id AND wfs_featuretype.featuretype_id = wfs_conf.fkey_featuretype_id
+ ORDER BY wfs_featuretype.featuretype_id;
+
+ALTER TABLE wfs_service_metadata_new
+ OWNER TO postgres;
+
+CREATE OR REPLACE VIEW mapbender.wfs_service_metadata AS
+ SELECT wfs.wfs_id,
+ wfs.wfs_version,
+ wfs.wfs_name,
+ wfs.wfs_title,
+ wfs.wfs_abstract,
+ wfs.wfs_getcapabilities,
+ wfs.wfs_describefeaturetype,
+ wfs.wfs_getfeature,
+ wfs.wfs_transaction,
+ wfs.wfs_owsproxy,
+ wfs.wfs_getcapabilities_doc,
+ wfs.wfs_upload_url,
+ wfs.fees,
+ wfs.accessconstraints,
+ wfs.individualname,
+ wfs.positionname,
+ wfs.providername,
+ wfs.city,
+ wfs.deliverypoint,
+ wfs.postalcode,
+ wfs.voice,
+ wfs.facsimile,
+ wfs.electronicmailaddress,
+ wfs.wfs_mb_getcapabilities_doc,
+ wfs.wfs_owner,
+ wfs.wfs_timestamp,
+ wfs.country,
+ wfs.administrativearea,
+ wfs_featuretype.fkey_wfs_id,
+ wfs_featuretype.featuretype_id,
+ wfs_featuretype.featuretype_name,
+ wfs_featuretype.featuretype_title,
+ wfs_featuretype.featuretype_srs,
+ wfs_featuretype.featuretype_searchable,
+ wfs_featuretype.featuretype_abstract,
+ f_collect_searchtext_wfs(wfs.wfs_id, wfs_featuretype.featuretype_id) AS searchtext,
+ mb_user.mb_user_id,
+ mb_user.mb_user_department,
+ mb_group.mb_group_description,
+ mb_group.mb_group_name,
+ wfs_conf.wfs_conf_id,
+ wfs_conf.wfs_conf_abstract,
+ wfs_conf.wfs_conf_description
+ FROM wfs
+ LEFT JOIN wfs_featuretype ON wfs.wfs_id = wfs_featuretype.fkey_wfs_id
+ JOIN mb_user ON wfs.wfs_owner = mb_user.mb_user_id
+ JOIN mb_group ON mb_user.mb_user_department::text = mb_group.mb_group_description::text
+ LEFT JOIN wfs_conf ON wfs_featuretype.featuretype_id = wfs_conf.fkey_featuretype_id;
+
+ALTER TABLE wfs_service_metadata
+ OWNER TO postgres;
More information about the Mapbender_commits
mailing list