[Mapbender-commits] r9719 - trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Wed Mar 29 11:34:23 PDT 2017
Author: armin11
Date: 2017-03-29 11:34:23 -0700 (Wed, 29 Mar 2017)
New Revision: 9719
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
Fix for catalogue search (wms/wfs/datasets) - pull the right organizations
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-28 13:45:52 UTC (rev 9718)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql 2017-03-29 18:34:23 UTC (rev 9719)
@@ -1776,6 +1776,36 @@
ALTER FUNCTION f_collect_searchtext_dataset(integer)
OWNER TO postgres;
+--new views to make search views somewhat easier
+
+-- View: groups_for_publishing
+
+-- DROP VIEW groups_for_publishing;
+
+CREATE OR REPLACE VIEW groups_for_publishing AS
+ SELECT mb_group.mb_group_id AS fkey_mb_group_id, mb_group.*
+ FROM mb_group
+ WHERE (mb_group.mb_group_id IN ( SELECT DISTINCT f.fkey_mb_group_id
+ FROM mb_user_mb_group f, mb_user_mb_group s
+ WHERE (f.mb_user_mb_group_type = ANY (ARRAY[2, 3])) AND s.fkey_mb_group_id = 36 AND f.fkey_mb_user_id = s.fkey_mb_user_id));
+
+ALTER TABLE groups_for_publishing
+ OWNER TO postgres;
+
+-- View: users_for_publishing
+
+-- DROP VIEW users_for_publishing;
+
+CREATE OR REPLACE VIEW users_for_publishing AS
+ SELECT DISTINCT f.fkey_mb_user_id, f.fkey_mb_group_id AS primary_group_id
+ FROM mb_user_mb_group f, mb_user_mb_group s
+ WHERE f.mb_user_mb_group_type = 2 AND s.fkey_mb_group_id = 36 AND f.fkey_mb_user_id = s.fkey_mb_user_id
+ ORDER BY f.fkey_mb_user_id;
+
+ALTER TABLE users_for_publishing
+ OWNER TO postgres;
+
+
-- View: search_wms_view
-- DROP VIEW search_wms_view;
@@ -1784,11 +1814,23 @@
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
+ FROM (
+ --adoption to pull two different kinds of wms metadata, based on mapbenders role system
+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, publishing_registrating_authorities.mb_group_id AS department, publishing_registrating_authorities.mb_group_id AS fkey_mb_group_id, wms.fkey_mb_group_id AS wms_department, publishing_registrating_authorities.mb_group_name, publishing_registrating_authorities.mb_group_logo_path
+ FROM groups_for_publishing AS publishing_registrating_authorities, wms, mb_wms_availability
+ WHERE wms.fkey_mb_group_id = publishing_registrating_authorities.mb_group_id AND wms.wms_id = mb_wms_availability.fkey_wms_id
+
+UNION ALL
+
+--now pull the resources for whith the primary group of the registrating user
+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, wms.fkey_mb_group_id AS wms_department, user_dep.mb_group_name, user_dep.mb_group_logo_path
+ FROM (
+
+SELECT publishing_registrating_authorities.*, users_for_publishing.fkey_mb_user_id FROM groups_for_publishing AS publishing_registrating_authorities, users_for_publishing WHERE users_for_publishing.primary_group_id = publishing_registrating_authorities.fkey_mb_group_id) user_dep,
+
+ wms, mb_wms_availability
+ WHERE (wms.fkey_mb_group_id IS null OR wms.fkey_mb_group_id = 0) AND 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;
@@ -1808,21 +1850,31 @@
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
+ FROM (
+--
+--adoption to pull two different kinds of wms metadata, based on mapbenders role system
+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, publishing_registrating_authorities.mb_group_id AS department, publishing_registrating_authorities.mb_group_name, publishing_registrating_authorities.mb_group_logo_path
+ FROM groups_for_publishing AS publishing_registrating_authorities, wfs
+ WHERE wfs.fkey_mb_group_id = publishing_registrating_authorities.mb_group_id
+
+UNION ALL
+
+--now pull the resources for whith the primary group of the registrating user
+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.fkey_mb_group_id AS department, user_dep.mb_group_name, user_dep.mb_group_logo_path
+ FROM (
+
+SELECT publishing_registrating_authorities.*, users_for_publishing.fkey_mb_user_id FROM groups_for_publishing AS publishing_registrating_authorities, users_for_publishing WHERE users_for_publishing.primary_group_id = publishing_registrating_authorities.fkey_mb_group_id) user_dep,
+
+ wfs
+ WHERE (wfs.fkey_mb_group_id IS null OR wfs.fkey_mb_group_id = 0) AND wfs.wfs_owner = user_dep.fkey_mb_user_id
+--
+) 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,
@@ -1841,13 +1893,42 @@
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 ( 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, mb_metadata.mb_group_id AS department, mb_metadata.mb_group_name, mb_metadata.mb_group_title, mb_metadata.mb_group_country, mb_metadata.mb_group_stateorprovince, mb_metadata.mb_group_logo_path
+ FROM (
+
+--begin union select
+SELECT * FROM (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
+ LEFT JOIN metadata_load_count ON mb_metadata.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata,
+(
+--
+SELECT groups_for_publishing.fkey_mb_group_id, groups_for_publishing.mb_group_id, groups_for_publishing.mb_group_name, groups_for_publishing.mb_group_title, groups_for_publishing.mb_group_country, groups_for_publishing.mb_group_stateorprovince, groups_for_publishing.mb_group_logo_path, 0 AS fkey_mb_user_id_from_users FROM groups_for_publishing
+--
+) user_dep
+
+ WHERE (mb_metadata.fkey_mb_group_id = user_dep.mb_group_id) AND mb_metadata.the_geom IS NOT NULL AND mb_metadata.searchable IS TRUE
+
+UNION ALL
+
+SELECT * FROM (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,
+(
+--
+SELECT publishing_registrating_authorities.fkey_mb_group_id, publishing_registrating_authorities.mb_group_id, publishing_registrating_authorities.mb_group_name, publishing_registrating_authorities.mb_group_title, publishing_registrating_authorities.mb_group_country, publishing_registrating_authorities.mb_group_stateorprovince, publishing_registrating_authorities.mb_group_logo_path, users_for_publishing.fkey_mb_user_id AS fkey_mb_user_id_from_users FROM groups_for_publishing AS publishing_registrating_authorities, users_for_publishing WHERE users_for_publishing.primary_group_id = publishing_registrating_authorities.fkey_mb_group_id
+--
+) user_dep
+
+ WHERE (mb_metadata.fkey_mb_group_id IS null OR mb_metadata.fkey_mb_group_id = 0) AND mb_metadata.fkey_mb_user_id = user_dep.fkey_mb_user_id_from_users AND mb_metadata.the_geom IS NOT NULL AND mb_metadata.searchable IS TRUE
+--end for union select
+) mb_metadata ) dataset_dep
+
+
+
ORDER BY dataset_dep.dataset_id) datasets;
ALTER TABLE search_dataset_view
More information about the Mapbender_commits
mailing list