[Mapbender-commits] r9718 - trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Tue Mar 28 06:45:53 PDT 2017
Author: armin11
Date: 2017-03-28 06:45:52 -0700 (Tue, 28 Mar 2017)
New Revision: 9718
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Fix for using right group information in metadata catalogue
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-27 16:25:07 UTC (rev 9717)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql 2017-03-28 13:45:52 UTC (rev 9718)
@@ -1776,6 +1776,82 @@
ALTER FUNCTION f_collect_searchtext_dataset(integer)
OWNER TO postgres;
+-- View: search_wms_view
+-- DROP VIEW search_wms_view;
+CREATE OR REPLACE VIEW search_wms_view AS
+ SELECT DISTINCT ON (wms_unref.layer_id) wms_unref.wms_id, wms_unref.availability, wms_unref.status, wms_unref.wms_title, wms_unref.wms_abstract, wms_unref.stateorprovince, wms_unref.country, wms_unref.accessconstraints, wms_unref.termsofuse, wms_unref.isopen, wms_unref.wms_owner, wms_unref.layer_id, wms_unref.epsg, wms_unref.layer_title, wms_unref.layer_abstract, wms_unref.layer_name, wms_unref.layer_parent, wms_unref.layer_pos, wms_unref.layer_queryable, wms_unref.export2csw, wms_unref.load_count, wms_unref.searchtext, wms_unref.wms_timestamp, wms_unref.department, wms_unref.mb_group_name, f_collect_custom_cat_layer(wms_unref.layer_id) AS md_custom_cats, f_collect_inspire_cat_layer(wms_unref.layer_id) AS md_inspire_cats, f_collect_topic_cat_layer(wms_unref.layer_id) AS md_topic_cats, geometryfromtext(((((((((((((((((((('POLYGON(('::text || layer_epsg.minx::text) || ' '::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.minx::text) || ' '::text) || layer_epsg.maxy::text)
|| ','::text) || layer_epsg.maxx::text) || ' '::text) || layer_epsg.maxy::text) || ','::text) || layer_epsg.maxx::text) || ' '::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.minx::text) || ' '::text) || layer_epsg.miny::text) || '))'::text, 4326) AS the_geom, (((((layer_epsg.minx::text || ','::text) || layer_epsg.miny::text) || ','::text) || layer_epsg.maxx::text) || ','::text) || layer_epsg.maxy::text AS bbox, wms_unref.wms_proxylog, wms_unref.wms_network_access, wms_unref.wms_pricevolume, wms_unref.mb_group_logo_path
+ FROM ( SELECT wms_uncat.wms_id, wms_uncat.availability, wms_uncat.status, wms_uncat.wms_title, wms_uncat.wms_abstract, wms_uncat.stateorprovince, wms_uncat.country, wms_uncat.accessconstraints, wms_uncat.termsofuse, wms_uncat.isopen, wms_uncat.wms_owner, wms_uncat.layer_id, wms_uncat.epsg, wms_uncat.layer_title, wms_uncat.layer_abstract, wms_uncat.layer_name, wms_uncat.layer_parent, wms_uncat.layer_pos, wms_uncat.layer_queryable, wms_uncat.export2csw, wms_uncat.load_count, wms_uncat.searchtext, wms_uncat.wms_timestamp, wms_uncat.department, wms_uncat.mb_group_name, wms_uncat.wms_proxylog, wms_uncat.wms_network_access, wms_uncat.wms_pricevolume, wms_uncat.mb_group_logo_path
+ FROM ( SELECT wms_dep.wms_id, wms_dep.availability, wms_dep.status, wms_dep.wms_title, wms_dep.wms_abstract, wms_dep.stateorprovince, wms_dep.country, wms_dep.accessconstraints, wms_dep.termsofuse, wms_dep.isopen, wms_dep.wms_owner, layer.layer_id, f_collect_epsg(layer.layer_id) AS epsg, layer.layer_title, layer.layer_abstract, layer.layer_name, layer.layer_parent, layer.layer_pos, layer.layer_queryable, layer.export2csw, f_layer_load_count(layer.layer_id) AS load_count, f_collect_searchtext(wms_dep.wms_id, layer.layer_id) AS searchtext, wms_dep.wms_timestamp, wms_dep.department, wms_dep.mb_group_name, wms_dep.wms_proxylog, wms_dep.wms_network_access, wms_dep.wms_pricevolume, wms_dep.mb_group_logo_path
+ FROM ( SELECT wms.wms_id, wms.wms_title, wms.wms_abstract, wms.stateorprovince, wms.country, mb_wms_availability.availability, mb_wms_availability.last_status AS status, wms.accessconstraints, f_getwms_tou(wms.wms_id) AS termsofuse, f_tou_isopen(f_getwms_tou(wms.wms_id)) AS isopen, wms.wms_timestamp, wms.wms_owner, wms.wms_proxylog, wms.wms_network_access, wms.wms_pricevolume, user_dep.fkey_mb_group_id AS department, user_dep.fkey_mb_group_id, user_dep.fkey_mb_group_id AS wms_department, user_dep.mb_group_name, user_dep.mb_group_logo_path
+ FROM ( SELECT registrating_groups.fkey_mb_user_id, mb_group.mb_group_id AS fkey_mb_group_id, mb_group.mb_group_name, mb_group.mb_group_title, mb_group.mb_group_country, mb_group.mb_group_stateorprovince, 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, wms, mb_wms_availability
+ WHERE (wms.fkey_mb_group_id = user_dep.fkey_mb_group_id OR wms.wms_owner = user_dep.fkey_mb_user_id) AND wms.wms_id = mb_wms_availability.fkey_wms_id) wms_dep, layer
+ WHERE layer.fkey_wms_id = wms_dep.wms_id AND layer.layer_searchable = 1) wms_uncat) wms_unref, layer_epsg
+ WHERE layer_epsg.epsg::text = 'EPSG:4326'::text AND wms_unref.layer_id = layer_epsg.fkey_layer_id
+ ORDER BY wms_unref.layer_id;
+ALTER TABLE search_wms_view
+ OWNER TO postgres;
+
+-- View: search_wfs_view
+
+-- DROP VIEW search_wfs_view;
+
+CREATE OR REPLACE VIEW 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_geo
m.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 DISTINCT wfs_featuretype.featuretype_id, 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_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, 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 (wfs.fkey_mb_group_id = user_dep.mb_group_id OR 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) wfs_without_geom;
+
+ALTER TABLE search_wfs_view
+ OWNER TO postgres;
+
+-- View: search_dataset_view
+
+-- DROP VIEW search_dataset_view;
+
+CREATE OR REPLACE VIEW search_dataset_view AS
+ SELECT DISTINCT ON (datasets.metadata_id) datasets.user_id, datasets.dataset_id, datasets.metadata_id, datasets.dataset_srs, datasets.title, datasets.dataset_abstract, datasets.accessconstraints, datasets.isopen, datasets.termsofuse, datasets.searchtext, datasets.dataset_timestamp, datasets.department, datasets.mb_group_name, datasets.mb_group_title, datasets.mb_group_country, datasets.load_count, datasets.mb_group_stateorprovince, datasets.md_inspire_cats, datasets.md_custom_cats, datasets.md_topic_cats, datasets.the_geom, datasets.bbox, datasets.preview_url, datasets.fileidentifier, datasets.coupled_resources, datasets.mb_group_logo_path, datasets.timebegin, datasets.timeend
+ FROM ( SELECT dataset_dep.fkey_mb_user_id AS user_id, dataset_dep.dataset_id, dataset_dep.dataset_id AS metadata_id, dataset_dep.srs AS dataset_srs, dataset_dep.title, dataset_dep.abstract AS dataset_abstract, dataset_dep.accessconstraints, dataset_dep.isopen, dataset_dep.termsofuse, f_collect_searchtext_dataset(dataset_dep.dataset_id) AS searchtext, dataset_dep.dataset_timestamp, dataset_dep.department, dataset_dep.mb_group_name, dataset_dep.mb_group_title, dataset_dep.mb_group_country,
+ CASE
+ WHEN dataset_dep.load_count IS NULL THEN 0::bigint
+ ELSE dataset_dep.load_count
+ END AS load_count, dataset_dep.mb_group_stateorprovince, f_collect_inspire_cat_dataset(dataset_dep.dataset_id) AS md_inspire_cats, f_collect_custom_cat_dataset(dataset_dep.dataset_id) AS md_custom_cats, f_collect_topic_cat_dataset(dataset_dep.dataset_id) AS md_topic_cats, dataset_dep.bbox AS the_geom, (((((st_xmin(dataset_dep.bbox::box3d)::text || ','::text) || st_ymin(dataset_dep.bbox::box3d)::text) || ','::text) || st_xmax(dataset_dep.bbox::box3d)::text) || ','::text) || st_ymax(dataset_dep.bbox::box3d)::text AS bbox, dataset_dep.preview_url, dataset_dep.fileidentifier, f_get_coupled_resources(dataset_dep.dataset_id) AS coupled_resources, dataset_dep.mb_group_logo_path, dataset_dep.timebegin::date AS timebegin,
+ CASE
+ WHEN dataset_dep.update_frequency::text = 'continual'::text THEN now()::date
+ WHEN dataset_dep.update_frequency::text = 'daily'::text THEN now()::date
+ WHEN dataset_dep.update_frequency::text = 'weekly'::text THEN (now() - '7 days'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'fortnightly'::text THEN (now() - '14 days'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'monthly'::text THEN (now() - '1 mon'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'quarterly'::text THEN (now() - '3 mons'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'biannually'::text THEN (now() - '6 mons'::interval)::date
+ WHEN dataset_dep.update_frequency::text = 'annually'::text THEN (now() - '1 year'::interval)::date
+ ELSE dataset_dep.timeend::date
+ END AS timeend
+ FROM ( SELECT mb_metadata.the_geom AS bbox, mb_metadata.ref_system AS srs, mb_metadata.metadata_id AS dataset_id, mb_metadata.title, mb_metadata.abstract, mb_metadata.lastchanged AS dataset_timestamp, mb_metadata.tmp_reference_1 AS timebegin, mb_metadata.tmp_reference_2 AS timeend, mb_metadata.uuid AS fileidentifier, mb_metadata.preview_image AS preview_url, mb_metadata.load_count, mb_metadata.fkey_mb_user_id, mb_metadata.constraints AS accessconstraints, mb_metadata.update_frequency, f_getmd_tou(mb_metadata.metadata_id) AS termsofuse, f_tou_isopen(f_getmd_tou(mb_metadata.metadata_id)) AS isopen, 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, 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_title, mb_group.mb_group_country, mb_group.mb_group_stateorprovince, 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, ( SELECT mb_metadata.metadata_id, mb_metadata.uuid, mb_metadata.origin, mb_metadata.includeincaps, mb_metadata.fkey_mb_group_id, mb_metadata.schema, mb_metadata.createdate, mb_metadata.changedate, mb_metadata.lastchanged, mb_metadata.data, mb_metadata.link, mb_metadata.linktype, mb_metadata.md_format, mb_metadata.title, mb_metadata.abstract, mb_metadata.searchtext, mb_metadata.status, mb_metadata.type, mb_metadata.harvestresult, mb_metadata.harvestexception, mb_metadata.export2csw, mb_metadata.tmp_reference_1, mb_metadata.tmp_reference_2, mb_metadata.spatial_res_type, mb_metadata.spatial_res_value, mb_metadata.ref_system, mb_metadata.format, mb_metadata.inspire_charset, mb_metadata.inspire_top_consistence, mb_metadata.fkey_mb_user_id, mb_metadata.responsible_party, mb_metadata.individual_name, mb_metadata.visibility, mb_metadata.locked, mb_metadata.copyof, mb_metadata.constraints,
mb_metadata.fees, mb_metadata.classification, mb_metadata.browse_graphic, mb_metadata.inspire_conformance, mb_metadata.preview_image, mb_metadata.the_geom, mb_metadata.lineage, mb_metadata.datasetid, mb_metadata.randomid, mb_metadata.update_frequency, mb_metadata.datasetid_codespace, mb_metadata.bounding_geom, mb_metadata.inspire_whole_area, mb_metadata.inspire_actual_coverage, mb_metadata.datalinks, mb_metadata.inspire_download, mb_metadata.transfer_size, mb_metadata.md_license_source_note, mb_metadata.responsible_party_name, mb_metadata.responsible_party_email, mb_metadata.searchable, metadata_load_count.load_count
+ FROM mb_metadata
+ LEFT JOIN metadata_load_count ON mb_metadata.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata
+ WHERE (mb_metadata.fkey_mb_group_id = user_dep.mb_group_id OR user_dep.mb_user_id = mb_metadata.fkey_mb_user_id) AND mb_metadata.the_geom IS NOT NULL AND mb_metadata.searchable IS TRUE) dataset_dep
+ ORDER BY dataset_dep.dataset_id) datasets;
+
+ALTER TABLE search_dataset_view
+ OWNER TO postgres;
+
+
+
More information about the Mapbender_commits
mailing list