[Mapbender-commits] r10368 - trunk/mapbender/resources/db/pgsql/UTF-8/update
svn_mapbender at osgeo.org
svn_mapbender at osgeo.org
Mon Dec 9 05:24:44 PST 2019
Author: armin11
Date: 2019-12-09 05:24:44 -0800 (Mon, 09 Dec 2019)
New Revision: 10368
Modified:
trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql
Log:
new search application 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 2019-12-09 13:20:27 UTC (rev 10367)
+++ trunk/mapbender/resources/db/pgsql/UTF-8/update/update_2.7.4_to_2.8_pgsql_UTF-8.sql 2019-12-09 13:24:44 UTC (rev 10368)
@@ -2908,4 +2908,932 @@
UPDATE gui_element SET e_target = 'editMaintenance_collection, reindexWMS_collection,reindexWFS_collection,reindexDATASET_collection,reindexWMC_collection,Start_Monitor_for_WMS_collection,CSW_collection' WHERE fkey_gui_id = 'PortalAdmin_DE' AND e_id = 'menu_maintenance';
+-- Table: custom_category_origin
+-- DROP TABLE custom_category_origin;
+
+CREATE TABLE custom_category_origin
+(
+ id serial,
+ name character varying(255) NOT NULL,
+ uri character varying(500) NOT NULL,
+ type character varying(100) NOT NULL,
+ CONSTRAINT custom_category_origin_pkey PRIMARY KEY (id)
+)
+WITH (
+ OIDS=TRUE
+);
+ALTER TABLE custom_category_origin
+ OWNER TO postgres;
+
+
+ALTER TABLE custom_category ADD COLUMN fkey_custom_category_origin_id integer;
+
+
+-- Foreign Key: custom_category_ibfk_1
+
+-- ALTER TABLE custom_category DROP CONSTRAINT custom_category_ibfk_1;
+
+ALTER TABLE custom_category
+ ADD CONSTRAINT custom_category_ibfk_1 FOREIGN KEY (fkey_custom_category_origin_id)
+ REFERENCES custom_category_origin (id) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE RESTRICT;
+
+-- Column: custom_category_description_en
+
+-- ALTER TABLE custom_category DROP COLUMN custom_category_description_en;
+
+ALTER TABLE custom_category ADD COLUMN custom_category_description_en text;
+
+
+-- Column: custom_category_online_link
+
+-- ALTER TABLE custom_category DROP COLUMN custom_category_online_link;
+
+ALTER TABLE custom_category ADD COLUMN custom_category_online_link text;
+
+ALTER TABLE custom_category ALTER COLUMN custom_category_key TYPE VARCHAR(4096);
+
+ALTER TABLE custom_category ADD COLUMN custom_category_parent_key VARCHAR(4096);
+
+ALTER TABLE custom_category ADD CONSTRAINT custom_category_key_unique_c UNIQUE (custom_category_key);
+
+ALTER TABLE custom_category
+ADD CONSTRAINT custom_category_key_parent_ibfk1 FOREIGN KEY (custom_category_parent_key)
+ REFERENCES custom_category (custom_category_key) MATCH SIMPLE
+ ON UPDATE CASCADE ON DELETE CASCADE;
+
+--remove application metadata from searchInterface for datasets
+-- 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,
+ 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 ( SELECT mb_metadata_1.metadata_id,
+ mb_metadata_1.uuid,
+ mb_metadata_1.origin,
+ mb_metadata_1.includeincaps,
+ mb_metadata_1.fkey_mb_group_id,
+ mb_metadata_1.schema,
+ mb_metadata_1.createdate,
+ mb_metadata_1.changedate,
+ mb_metadata_1.lastchanged,
+ mb_metadata_1.link,
+ mb_metadata_1.linktype,
+ mb_metadata_1.md_format,
+ mb_metadata_1.title,
+ mb_metadata_1.abstract,
+ mb_metadata_1.searchtext,
+ mb_metadata_1.status,
+ mb_metadata_1.type,
+ mb_metadata_1.harvestresult,
+ mb_metadata_1.harvestexception,
+ mb_metadata_1.export2csw,
+ mb_metadata_1.tmp_reference_1,
+ mb_metadata_1.tmp_reference_2,
+ mb_metadata_1.spatial_res_type,
+ mb_metadata_1.spatial_res_value,
+ mb_metadata_1.ref_system,
+ mb_metadata_1.format,
+ mb_metadata_1.inspire_charset,
+ mb_metadata_1.inspire_top_consistence,
+ mb_metadata_1.fkey_mb_user_id,
+ mb_metadata_1.responsible_party,
+ mb_metadata_1.individual_name,
+ mb_metadata_1.visibility,
+ mb_metadata_1.locked,
+ mb_metadata_1.copyof,
+ mb_metadata_1.constraints,
+ mb_metadata_1.fees,
+ mb_metadata_1.classification,
+ mb_metadata_1.browse_graphic,
+ mb_metadata_1.inspire_conformance,
+ mb_metadata_1.preview_image,
+ mb_metadata_1.the_geom,
+ mb_metadata_1.lineage,
+ mb_metadata_1.datasetid,
+ mb_metadata_1.randomid,
+ mb_metadata_1.update_frequency,
+ mb_metadata_1.datasetid_codespace,
+ mb_metadata_1.bounding_geom,
+ mb_metadata_1.inspire_whole_area,
+ mb_metadata_1.inspire_actual_coverage,
+ mb_metadata_1.datalinks,
+ mb_metadata_1.inspire_download,
+ mb_metadata_1.transfer_size,
+ mb_metadata_1.md_license_source_note,
+ mb_metadata_1.responsible_party_name,
+ mb_metadata_1.responsible_party_email,
+ mb_metadata_1.searchable,
+ mb_metadata_1.load_count,
+ user_dep.fkey_mb_group_id,
+ user_dep.mb_group_id,
+ 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,
+ user_dep.fkey_mb_user_id_from_users
+ FROM ( SELECT mb_metadata_2.metadata_id,
+ mb_metadata_2.uuid,
+ mb_metadata_2.origin,
+ mb_metadata_2.includeincaps,
+ mb_metadata_2.fkey_mb_group_id,
+ mb_metadata_2.schema,
+ mb_metadata_2.createdate,
+ mb_metadata_2.changedate,
+ mb_metadata_2.lastchanged,
+ mb_metadata_2.link,
+ mb_metadata_2.linktype,
+ mb_metadata_2.md_format,
+ mb_metadata_2.title,
+ mb_metadata_2.abstract,
+ mb_metadata_2.searchtext,
+ mb_metadata_2.status,
+ mb_metadata_2.type,
+ mb_metadata_2.harvestresult,
+ mb_metadata_2.harvestexception,
+ mb_metadata_2.export2csw,
+ mb_metadata_2.tmp_reference_1,
+ mb_metadata_2.tmp_reference_2,
+ mb_metadata_2.spatial_res_type,
+ mb_metadata_2.spatial_res_value,
+ mb_metadata_2.ref_system,
+ mb_metadata_2.format,
+ mb_metadata_2.inspire_charset,
+ mb_metadata_2.inspire_top_consistence,
+ mb_metadata_2.fkey_mb_user_id,
+ mb_metadata_2.responsible_party,
+ mb_metadata_2.individual_name,
+ mb_metadata_2.visibility,
+ mb_metadata_2.locked,
+ mb_metadata_2.copyof,
+ mb_metadata_2.constraints,
+ mb_metadata_2.fees,
+ mb_metadata_2.classification,
+ mb_metadata_2.browse_graphic,
+ mb_metadata_2.inspire_conformance,
+ mb_metadata_2.preview_image,
+ mb_metadata_2.the_geom,
+ mb_metadata_2.lineage,
+ mb_metadata_2.datasetid,
+ mb_metadata_2.randomid,
+ mb_metadata_2.update_frequency,
+ mb_metadata_2.datasetid_codespace,
+ mb_metadata_2.bounding_geom,
+ mb_metadata_2.inspire_whole_area,
+ mb_metadata_2.inspire_actual_coverage,
+ mb_metadata_2.datalinks,
+ mb_metadata_2.inspire_download,
+ mb_metadata_2.transfer_size,
+ mb_metadata_2.md_license_source_note,
+ mb_metadata_2.responsible_party_name,
+ mb_metadata_2.responsible_party_email,
+ mb_metadata_2.searchable,
+ metadata_load_count.load_count
+ FROM mb_metadata mb_metadata_2
+ LEFT JOIN metadata_load_count ON mb_metadata_2.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata_1,
+ ( 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_1.fkey_mb_group_id = user_dep.mb_group_id AND mb_metadata_1.the_geom IS NOT NULL AND mb_metadata_1.searchable IS TRUE
+ UNION ALL
+ SELECT mb_metadata_1.metadata_id,
+ mb_metadata_1.uuid,
+ mb_metadata_1.origin,
+ mb_metadata_1.includeincaps,
+ mb_metadata_1.fkey_mb_group_id,
+ mb_metadata_1.schema,
+ mb_metadata_1.createdate,
+ mb_metadata_1.changedate,
+ mb_metadata_1.lastchanged,
+ mb_metadata_1.link,
+ mb_metadata_1.linktype,
+ mb_metadata_1.md_format,
+ mb_metadata_1.title,
+ mb_metadata_1.abstract,
+ mb_metadata_1.searchtext,
+ mb_metadata_1.status,
+ mb_metadata_1.type,
+ mb_metadata_1.harvestresult,
+ mb_metadata_1.harvestexception,
+ mb_metadata_1.export2csw,
+ mb_metadata_1.tmp_reference_1,
+ mb_metadata_1.tmp_reference_2,
+ mb_metadata_1.spatial_res_type,
+ mb_metadata_1.spatial_res_value,
+ mb_metadata_1.ref_system,
+ mb_metadata_1.format,
+ mb_metadata_1.inspire_charset,
+ mb_metadata_1.inspire_top_consistence,
+ mb_metadata_1.fkey_mb_user_id,
+ mb_metadata_1.responsible_party,
+ mb_metadata_1.individual_name,
+ mb_metadata_1.visibility,
+ mb_metadata_1.locked,
+ mb_metadata_1.copyof,
+ mb_metadata_1.constraints,
+ mb_metadata_1.fees,
+ mb_metadata_1.classification,
+ mb_metadata_1.browse_graphic,
+ mb_metadata_1.inspire_conformance,
+ mb_metadata_1.preview_image,
+ mb_metadata_1.the_geom,
+ mb_metadata_1.lineage,
+ mb_metadata_1.datasetid,
+ mb_metadata_1.randomid,
+ mb_metadata_1.update_frequency,
+ mb_metadata_1.datasetid_codespace,
+ mb_metadata_1.bounding_geom,
+ mb_metadata_1.inspire_whole_area,
+ mb_metadata_1.inspire_actual_coverage,
+ mb_metadata_1.datalinks,
+ mb_metadata_1.inspire_download,
+ mb_metadata_1.transfer_size,
+ mb_metadata_1.md_license_source_note,
+ mb_metadata_1.responsible_party_name,
+ mb_metadata_1.responsible_party_email,
+ mb_metadata_1.searchable,
+ mb_metadata_1.load_count,
+ user_dep.fkey_mb_group_id,
+ user_dep.mb_group_id,
+ 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,
+ user_dep.fkey_mb_user_id_from_users
+ FROM ( SELECT mb_metadata_2.metadata_id,
+ mb_metadata_2.uuid,
+ mb_metadata_2.origin,
+ mb_metadata_2.includeincaps,
+ mb_metadata_2.fkey_mb_group_id,
+ mb_metadata_2.schema,
+ mb_metadata_2.createdate,
+ mb_metadata_2.changedate,
+ mb_metadata_2.lastchanged,
+ mb_metadata_2.link,
+ mb_metadata_2.linktype,
+ mb_metadata_2.md_format,
+ mb_metadata_2.title,
+ mb_metadata_2.abstract,
+ mb_metadata_2.searchtext,
+ mb_metadata_2.status,
+ mb_metadata_2.type,
+ mb_metadata_2.harvestresult,
+ mb_metadata_2.harvestexception,
+ mb_metadata_2.export2csw,
+ mb_metadata_2.tmp_reference_1,
+ mb_metadata_2.tmp_reference_2,
+ mb_metadata_2.spatial_res_type,
+ mb_metadata_2.spatial_res_value,
+ mb_metadata_2.ref_system,
+ mb_metadata_2.format,
+ mb_metadata_2.inspire_charset,
+ mb_metadata_2.inspire_top_consistence,
+ mb_metadata_2.fkey_mb_user_id,
+ mb_metadata_2.responsible_party,
+ mb_metadata_2.individual_name,
+ mb_metadata_2.visibility,
+ mb_metadata_2.locked,
+ mb_metadata_2.copyof,
+ mb_metadata_2.constraints,
+ mb_metadata_2.fees,
+ mb_metadata_2.classification,
+ mb_metadata_2.browse_graphic,
+ mb_metadata_2.inspire_conformance,
+ mb_metadata_2.preview_image,
+ mb_metadata_2.the_geom,
+ mb_metadata_2.lineage,
+ mb_metadata_2.datasetid,
+ mb_metadata_2.randomid,
+ mb_metadata_2.update_frequency,
+ mb_metadata_2.datasetid_codespace,
+ mb_metadata_2.bounding_geom,
+ mb_metadata_2.inspire_whole_area,
+ mb_metadata_2.inspire_actual_coverage,
+ mb_metadata_2.datalinks,
+ mb_metadata_2.inspire_download,
+ mb_metadata_2.transfer_size,
+ mb_metadata_2.md_license_source_note,
+ mb_metadata_2.responsible_party_name,
+ mb_metadata_2.responsible_party_email,
+ mb_metadata_2.searchable,
+ metadata_load_count.load_count
+ FROM mb_metadata mb_metadata_2
+ LEFT JOIN metadata_load_count ON mb_metadata_2.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata_1,
+ ( 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 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_1.fkey_mb_group_id IS NULL OR mb_metadata_1.fkey_mb_group_id = 0) AND mb_metadata_1.fkey_mb_user_id = user_dep.fkey_mb_user_id_from_users AND mb_metadata_1.the_geom IS NOT NULL AND mb_metadata_1.searchable IS TRUE) mb_metadata (metadata_id, uuid, origin, includeincaps, fkey_mb_group_id, schema, createdate, changedate, lastchanged, link, linktype, md_format, title, abstract, searchtext, status, type, harvestresult, harvestexception, export2csw, tmp_reference_1, tmp_reference_2, spatial_res_type, spatial_res_value, ref_system, format, inspire_charset, inspire_top_consistence, fkey_mb_user_id, responsible_party, individual_name, visibility, locked, copyof, constraints, fees, classification, browse_graphic, inspire_conformance, preview_image, the_geom, lineage, datasetid, randomid, update_frequency, datasetid_codespace, bounding_geom, inspire_whole_area, inspire_actual_coverage, datalinks, inspire_download, transfer_size, md_license_source_no
te, responsible_party_name, responsible_party_email, searchable, load_count, fkey_mb_group_id_1, mb_group_id, mb_group_name, mb_group_title, mb_group_country, mb_group_stateorprovince, mb_group_logo_path, fkey_mb_user_id_from_users)) dataset_dep
+ ORDER BY dataset_dep.dataset_id) datasets;
+
+--ALTER TABLE search_dataset_view
+-- OWNER TO postgres;
+
+--GRANT ALL ON TABLE search_dataset_view TO postgres;
+-- Materialized View: search_application_view
+
+-- DROP MATERIALIZED VIEW search_application_view;
+
+CREATE MATERIALIZED VIEW search_application_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,
+ datasets.link,
+ datasets.fkey_gui_id,
+ datasets.fkey_wmc_serial_id,
+ datasets.fkey_mapviewer_id
+ 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,
+ dataset_dep.link,
+ dataset_dep.fkey_gui_id,
+ dataset_dep.fkey_wmc_serial_id,
+ dataset_dep.fkey_mapviewer_id,
+ 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,
+ 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,
+ mb_metadata.link,
+ mb_metadata.fkey_gui_id,
+ mb_metadata.fkey_wmc_serial_id,
+ mb_metadata.fkey_mapviewer_id
+ FROM ( SELECT mb_metadata_1.metadata_id,
+ mb_metadata_1.uuid,
+ mb_metadata_1.origin,
+ mb_metadata_1.includeincaps,
+ mb_metadata_1.fkey_mb_group_id,
+ mb_metadata_1.schema,
+ mb_metadata_1.createdate,
+ mb_metadata_1.changedate,
+ mb_metadata_1.lastchanged,
+ mb_metadata_1.link,
+ mb_metadata_1.linktype,
+ mb_metadata_1.md_format,
+ mb_metadata_1.title,
+ mb_metadata_1.abstract,
+ mb_metadata_1.searchtext,
+ mb_metadata_1.status,
+ mb_metadata_1.type,
+ mb_metadata_1.harvestresult,
+ mb_metadata_1.harvestexception,
+ mb_metadata_1.export2csw,
+ mb_metadata_1.tmp_reference_1,
+ mb_metadata_1.tmp_reference_2,
+ mb_metadata_1.spatial_res_type,
+ mb_metadata_1.spatial_res_value,
+ mb_metadata_1.ref_system,
+ mb_metadata_1.format,
+ mb_metadata_1.inspire_charset,
+ mb_metadata_1.inspire_top_consistence,
+ mb_metadata_1.fkey_mb_user_id,
+ mb_metadata_1.responsible_party,
+ mb_metadata_1.individual_name,
+ mb_metadata_1.visibility,
+ mb_metadata_1.locked,
+ mb_metadata_1.copyof,
+ mb_metadata_1.constraints,
+ mb_metadata_1.fees,
+ mb_metadata_1.classification,
+ mb_metadata_1.browse_graphic,
+ mb_metadata_1.inspire_conformance,
+ mb_metadata_1.preview_image,
+ mb_metadata_1.the_geom,
+ mb_metadata_1.lineage,
+ mb_metadata_1.datasetid,
+ mb_metadata_1.randomid,
+ mb_metadata_1.update_frequency,
+ mb_metadata_1.datasetid_codespace,
+ mb_metadata_1.bounding_geom,
+ mb_metadata_1.inspire_whole_area,
+ mb_metadata_1.inspire_actual_coverage,
+ mb_metadata_1.datalinks,
+ mb_metadata_1.inspire_download,
+ mb_metadata_1.transfer_size,
+ mb_metadata_1.md_license_source_note,
+ mb_metadata_1.responsible_party_name,
+ mb_metadata_1.responsible_party_email,
+ mb_metadata_1.searchable,
+ mb_metadata_1.load_count,
+ mb_metadata_1.fkey_gui_id,
+ mb_metadata_1.fkey_wmc_serial_id,
+ mb_metadata_1.fkey_mapviewer_id,
+ user_dep.fkey_mb_group_id,
+ user_dep.mb_group_id,
+ 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,
+ user_dep.fkey_mb_user_id_from_users
+ FROM ( SELECT mb_metadata_2.metadata_id,
+ mb_metadata_2.uuid,
+ mb_metadata_2.origin,
+ mb_metadata_2.includeincaps,
+ mb_metadata_2.fkey_mb_group_id,
+ mb_metadata_2.schema,
+ mb_metadata_2.createdate,
+ mb_metadata_2.changedate,
+ mb_metadata_2.lastchanged,
+ mb_metadata_2.link,
+ mb_metadata_2.linktype,
+ mb_metadata_2.md_format,
+ mb_metadata_2.title,
+ mb_metadata_2.abstract,
+ mb_metadata_2.searchtext,
+ mb_metadata_2.status,
+ mb_metadata_2.type,
+ mb_metadata_2.harvestresult,
+ mb_metadata_2.harvestexception,
+ mb_metadata_2.export2csw,
+ mb_metadata_2.tmp_reference_1,
+ mb_metadata_2.tmp_reference_2,
+ mb_metadata_2.spatial_res_type,
+ mb_metadata_2.spatial_res_value,
+ mb_metadata_2.ref_system,
+ mb_metadata_2.format,
+ mb_metadata_2.inspire_charset,
+ mb_metadata_2.inspire_top_consistence,
+ mb_metadata_2.fkey_mb_user_id,
+ mb_metadata_2.responsible_party,
+ mb_metadata_2.individual_name,
+ mb_metadata_2.visibility,
+ mb_metadata_2.locked,
+ mb_metadata_2.copyof,
+ mb_metadata_2.constraints,
+ mb_metadata_2.fees,
+ mb_metadata_2.classification,
+ mb_metadata_2.browse_graphic,
+ mb_metadata_2.inspire_conformance,
+ mb_metadata_2.preview_image,
+ mb_metadata_2.the_geom,
+ mb_metadata_2.lineage,
+ mb_metadata_2.datasetid,
+ mb_metadata_2.randomid,
+ mb_metadata_2.update_frequency,
+ mb_metadata_2.datasetid_codespace,
+ mb_metadata_2.bounding_geom,
+ mb_metadata_2.inspire_whole_area,
+ mb_metadata_2.inspire_actual_coverage,
+ mb_metadata_2.datalinks,
+ mb_metadata_2.inspire_download,
+ mb_metadata_2.transfer_size,
+ mb_metadata_2.md_license_source_note,
+ mb_metadata_2.responsible_party_name,
+ mb_metadata_2.responsible_party_email,
+ mb_metadata_2.searchable,
+ mb_metadata_2.fkey_gui_id,
+ mb_metadata_2.fkey_wmc_serial_id,
+ mb_metadata_2.fkey_mapviewer_id,
+ metadata_load_count.load_count
+ FROM mb_metadata mb_metadata_2
+ LEFT JOIN metadata_load_count ON mb_metadata_2.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata_1,
+ ( 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_1.fkey_mb_group_id = user_dep.mb_group_id AND mb_metadata_1.the_geom IS NOT NULL AND mb_metadata_1.searchable IS TRUE AND mb_metadata_1.type::text = 'application'::text
+ UNION ALL
+ SELECT mb_metadata_1.metadata_id,
+ mb_metadata_1.uuid,
+ mb_metadata_1.origin,
+ mb_metadata_1.includeincaps,
+ mb_metadata_1.fkey_mb_group_id,
+ mb_metadata_1.schema,
+ mb_metadata_1.createdate,
+ mb_metadata_1.changedate,
+ mb_metadata_1.lastchanged,
+ mb_metadata_1.link,
+ mb_metadata_1.linktype,
+ mb_metadata_1.md_format,
+ mb_metadata_1.title,
+ mb_metadata_1.abstract,
+ mb_metadata_1.searchtext,
+ mb_metadata_1.status,
+ mb_metadata_1.type,
+ mb_metadata_1.harvestresult,
+ mb_metadata_1.harvestexception,
+ mb_metadata_1.export2csw,
+ mb_metadata_1.tmp_reference_1,
+ mb_metadata_1.tmp_reference_2,
+ mb_metadata_1.spatial_res_type,
+ mb_metadata_1.spatial_res_value,
+ mb_metadata_1.ref_system,
+ mb_metadata_1.format,
+ mb_metadata_1.inspire_charset,
+ mb_metadata_1.inspire_top_consistence,
+ mb_metadata_1.fkey_mb_user_id,
+ mb_metadata_1.responsible_party,
+ mb_metadata_1.individual_name,
+ mb_metadata_1.visibility,
+ mb_metadata_1.locked,
+ mb_metadata_1.copyof,
+ mb_metadata_1.constraints,
+ mb_metadata_1.fees,
+ mb_metadata_1.classification,
+ mb_metadata_1.browse_graphic,
+ mb_metadata_1.inspire_conformance,
+ mb_metadata_1.preview_image,
+ mb_metadata_1.the_geom,
+ mb_metadata_1.lineage,
+ mb_metadata_1.datasetid,
+ mb_metadata_1.randomid,
+ mb_metadata_1.update_frequency,
+ mb_metadata_1.datasetid_codespace,
+ mb_metadata_1.bounding_geom,
+ mb_metadata_1.inspire_whole_area,
+ mb_metadata_1.inspire_actual_coverage,
+ mb_metadata_1.datalinks,
+ mb_metadata_1.inspire_download,
+ mb_metadata_1.transfer_size,
+ mb_metadata_1.md_license_source_note,
+ mb_metadata_1.responsible_party_name,
+ mb_metadata_1.responsible_party_email,
+ mb_metadata_1.searchable,
+ mb_metadata_1.load_count,
+ mb_metadata_1.fkey_gui_id,
+ mb_metadata_1.fkey_wmc_serial_id,
+ mb_metadata_1.fkey_mapviewer_id,
+ user_dep.fkey_mb_group_id,
+ user_dep.mb_group_id,
+ 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,
+ user_dep.fkey_mb_user_id_from_users
+ FROM ( SELECT mb_metadata_2.metadata_id,
+ mb_metadata_2.uuid,
+ mb_metadata_2.origin,
+ mb_metadata_2.includeincaps,
+ mb_metadata_2.fkey_mb_group_id,
+ mb_metadata_2.schema,
+ mb_metadata_2.createdate,
+ mb_metadata_2.changedate,
+ mb_metadata_2.lastchanged,
+ mb_metadata_2.link,
+ mb_metadata_2.linktype,
+ mb_metadata_2.md_format,
+ mb_metadata_2.title,
+ mb_metadata_2.abstract,
+ mb_metadata_2.searchtext,
+ mb_metadata_2.status,
+ mb_metadata_2.type,
+ mb_metadata_2.harvestresult,
+ mb_metadata_2.harvestexception,
+ mb_metadata_2.export2csw,
+ mb_metadata_2.tmp_reference_1,
+ mb_metadata_2.tmp_reference_2,
+ mb_metadata_2.spatial_res_type,
+ mb_metadata_2.spatial_res_value,
+ mb_metadata_2.ref_system,
+ mb_metadata_2.format,
+ mb_metadata_2.inspire_charset,
+ mb_metadata_2.inspire_top_consistence,
+ mb_metadata_2.fkey_mb_user_id,
+ mb_metadata_2.responsible_party,
+ mb_metadata_2.individual_name,
+ mb_metadata_2.visibility,
+ mb_metadata_2.locked,
+ mb_metadata_2.copyof,
+ mb_metadata_2.constraints,
+ mb_metadata_2.fees,
+ mb_metadata_2.classification,
+ mb_metadata_2.browse_graphic,
+ mb_metadata_2.inspire_conformance,
+ mb_metadata_2.preview_image,
+ mb_metadata_2.the_geom,
+ mb_metadata_2.lineage,
+ mb_metadata_2.datasetid,
+ mb_metadata_2.randomid,
+ mb_metadata_2.update_frequency,
+ mb_metadata_2.datasetid_codespace,
+ mb_metadata_2.bounding_geom,
+ mb_metadata_2.inspire_whole_area,
+ mb_metadata_2.inspire_actual_coverage,
+ mb_metadata_2.datalinks,
+ mb_metadata_2.inspire_download,
+ mb_metadata_2.transfer_size,
+ mb_metadata_2.md_license_source_note,
+ mb_metadata_2.responsible_party_name,
+ mb_metadata_2.responsible_party_email,
+ mb_metadata_2.searchable,
+ mb_metadata_2.fkey_gui_id,
+ mb_metadata_2.fkey_wmc_serial_id,
+ mb_metadata_2.fkey_mapviewer_id,
+ metadata_load_count.load_count
+ FROM mb_metadata mb_metadata_2
+ LEFT JOIN metadata_load_count ON mb_metadata_2.metadata_id = metadata_load_count.fkey_metadata_id) mb_metadata_1,
+ ( 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 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_1.fkey_mb_group_id IS NULL OR mb_metadata_1.fkey_mb_group_id = 0) AND mb_metadata_1.fkey_mb_user_id = user_dep.fkey_mb_user_id_from_users AND mb_metadata_1.the_geom IS NOT NULL AND mb_metadata_1.searchable IS TRUE AND mb_metadata_1.type::text = 'application'::text) mb_metadata(metadata_id, uuid, origin, includeincaps, fkey_mb_group_id, schema, createdate, changedate, lastchanged, link, linktype, md_format, title, abstract, searchtext, status, type, harvestresult, harvestexception, export2csw, tmp_reference_1, tmp_reference_2, spatial_res_type, spatial_res_value, ref_system, format, inspire_charset, inspire_top_consistence, fkey_mb_user_id, responsible_party, individual_name, visibility, locked, copyof, constraints, fees, classification, browse_graphic, inspire_conformance, preview_image, the_geom, lineage, datasetid, randomid, update_frequency, datasetid_codespace, bounding_geom, inspire_whole_area, inspire_actual_coverage, datalinks, ins
pire_download, transfer_size, md_license_source_note, responsible_party_name, responsible_party_email, searchable, load_count, fkey_gui_id, fkey_wmc_serial_id, fkey_mapviewer_id, fkey_mb_group_id_1, mb_group_id, mb_group_name, mb_group_title, mb_group_country, mb_group_stateorprovince, mb_group_logo_path, fkey_mb_user_id_from_users)) dataset_dep
+ ORDER BY dataset_dep.dataset_id) datasets
+WITH DATA;
+
+ALTER TABLE search_application_view
+ OWNER TO postgres;
+GRANT ALL ON TABLE search_application_view TO postgres;
+GRANT ALL ON TABLE search_application_view TO mapbenderdbuser;
+
+-- Index: gist_wst_application_the_geom
+
+-- DROP INDEX gist_wst_application_the_geom;
+
+CREATE INDEX gist_wst_application_the_geom
+ ON search_application_view
+ USING gist
+ (the_geom);
+
+-- Index: idx_wst_application_dataset_id
+
+-- DROP INDEX idx_wst_application_dataset_id;
+
+CREATE INDEX idx_wst_application_dataset_id
+ ON search_application_view
+ USING btree
+ (dataset_id);
+
+-- Index: idx_wst_application_department
+
+-- DROP INDEX idx_wst_application_department;
+
+CREATE INDEX idx_wst_application_department
+ ON search_application_view
+ USING btree
+ (department);
+
+-- Index: idx_wst_application_md_custom_cats
+
+-- DROP INDEX idx_wst_application_md_custom_cats;
+
+CREATE INDEX idx_wst_application_md_custom_cats
+ ON search_application_view
+ USING btree
+ (md_custom_cats COLLATE pg_catalog."default");
+
+-- Index: idx_wst_application_md_inspire_cats
+
+-- DROP INDEX idx_wst_application_md_inspire_cats;
+
+CREATE INDEX idx_wst_application_md_inspire_cats
+ ON search_application_view
+ USING btree
+ (md_inspire_cats COLLATE pg_catalog."default");
+
+-- Index: idx_wst_application_md_topic_cats
+
+-- DROP INDEX idx_wst_application_md_topic_cats;
+
+CREATE INDEX idx_wst_application_md_topic_cats
+ ON search_application_view
+ USING btree
+ (md_topic_cats COLLATE pg_catalog."default");
+
+-- Index: idx_wst_application_metadata_id
+
+-- DROP INDEX idx_wst_application_metadata_id;
+
+CREATE INDEX idx_wst_application_metadata_id
+ ON search_application_view
+ USING btree
+ (metadata_id);
+
+-- Index: idx_wst_application_searchtext
+
+-- DROP INDEX idx_wst_application_searchtext;
+
+CREATE INDEX idx_wst_application_searchtext
+ ON search_application_view
+ USING btree
+ (searchtext COLLATE pg_catalog."default");
+
+-- Index: idx_wst_application_timebegin
+
+-- DROP INDEX idx_wst_application_timebegin;
+
+CREATE INDEX idx_wst_application_timebegin
+ ON search_application_view
+ USING btree
+ (timebegin);
+
+-- Index: idx_wst_application_timeend
+
+-- DROP INDEX idx_wst_application_timeend;
+
+CREATE INDEX idx_wst_application_timeend
+ ON search_application_view
+ USING btree
+ (timeend);
+
+-- Index: idx_wst_application_timestamp
+
+-- DROP INDEX idx_wst_application_timestamp;
+
+CREATE INDEX idx_wst_application_timestamp
+ ON search_application_view
+ USING btree
+ (dataset_timestamp);
+
+
+
More information about the Mapbender_commits
mailing list