nosetests errors due to removal of projekte column

[ltclm]

After removing of the projekte column from

• bod.public.dataset
• bod.re3.view_layers_js
• bod.re3.view_catalog
• bod.re3.view_bod_layer_info_xx
• bod.re3.view_bod_wmts_getcapabilities_xx

there are new errors in the following nosetests:

• chsdi.models.bod
• chsdi.views.features
• chsdi.views.layers
• chsdi.views.wmtsgetcapabilities

The column can be removed on pgcluster0t with the following sql [1]. Please do not change the original database on bgdipg01t! Any idea whats going wrong here? https://github.com/geoadmin/mf-chsdi3/pull/978 https://github.com/geoadmin/mf-chsdi3/issues/891 [1]

begin;
-- remove projekte column from bod table dataset and related views
-- https://github.com/geoadmin/mf-chsdi3/pull/978
-- https://github.com/geoadmin/mf-chsdi3/issues/891

DROP VIEW re3.view_layers_js;
CREATE OR REPLACE VIEW re3.view_layers_js AS 
 SELECT l.bgdi_id, l.pk_layer AS layer_id, l.server_layername AS bod_layer_id, topics.info AS topics, d.chargeable, d.staging, l.server_layername, contactorganisation.attribution, l.layertype, l.opacity::double precision AS opacity, l.minresolution::double precision AS minresolution, l.maxresolution::double precision AS maxresolution, l.backgroundlayer, l.queryable, l.selectbyrectangle, l.searchable, l.timeenabled, l.haslegend, l.singletile, l.highlightable, l.wms_layers, wms.onlineresource AS wms_url, l.time_behaviour, COALESCE(
        CASE l.layertype
            WHEN 'wmts'::text THEN tileset.format[1]::text
            ELSE l.image_format
        END, l.image_format) AS image_format, 
        CASE l.layertype
            WHEN 'wmts'::text THEN tileset.tilematrixsetid[1]
            ELSE NULL::text
        END AS tilematrixsetid, 
        CASE l.layertype
            WHEN 'wmts'::text THEN tileset."timestamp"
            ELSE wms_time.timestamps
        END AS timestamps, l.fk_parent_layer AS parentlayerid, 
        CASE l.layertype
            WHEN 'aggregate'::text THEN ( SELECT array_to_string(array_agg(layers_js.pk_layer), ','::text) AS array_to_string
               FROM re3.layers_js
              WHERE l.pk_layer = layers_js.fk_parent_layer)
            ELSE NULL::text
        END AS sublayerids, 
        CASE l.layertype
            WHEN 'aggregate'::text THEN ( SELECT array_agg(layers_js.pk_layer) AS array_agg
               FROM re3.layers_js
              WHERE l.pk_layer = layers_js.fk_parent_layer)
            ELSE NULL::text[]
        END AS sublayersids, l.time_get_parameter, l.time_format, l.wms_gutter
   FROM ( SELECT a.bgdi_id, a.fk_parent_layer, a.pk_layer, 
                CASE
                    WHEN a.layertype = ANY (ARRAY['wmts'::text, 'aggregate'::text]) THEN a.fk_id_dataset
                    WHEN a.layertype = 'wms'::text AND "position"(a.wms_layers, ','::text) > 0 THEN a.pk_layer
                    WHEN a.layertype = 'wms'::text AND "position"(a.wms_layers, ','::text) = 0 THEN a.wms_layers
                    ELSE 'not defined'::text
                END AS server_layername, a.layertype, a.opacity, a.minresolution, a.maxresolution, a.image_format, a.wms_layers, a.fk_wms_metadata, a.backgroundlayer, a.queryable, a.selectbyrectangle, a.searchable, a.timeenabled, a.haslegend, a.singletile, a.highlightable, a.time_get_parameter, a.time_format, a.time_behaviour, a.wms_gutter
           FROM re3.layers_js a) l
   LEFT JOIN dataset d ON l.server_layername = d.id_dataset
   LEFT JOIN wms_metadata wms ON l.fk_wms_metadata = wms.pk_map_name::text
   LEFT JOIN contactorganisation ON contactorganisation.pk_contactorganisation_id = d.fk_contactorganisation_id
   LEFT JOIN ( SELECT wms_timestamps.fk_dataset_id, array_agg(wms_timestamps."timestamp" ORDER BY wms_timestamps."timestamp" DESC) AS timestamps
   FROM wms_timestamps
  GROUP BY wms_timestamps.fk_dataset_id) wms_time ON wms_time.fk_dataset_id = d.id_dataset
   LEFT JOIN ( SELECT tileset.fk_dataset_id, array_agg(tileset.format ORDER BY tileset."timestamp" DESC) AS format, array_agg((tileset.tile_matrix_set_id::text || '_'::text) || tileset.zoomlevel_max) AS tilematrixsetid, array_agg(tileset."timestamp"::text ORDER BY tileset."timestamp" DESC) AS "timestamp"
   FROM tileset
  GROUP BY tileset.fk_dataset_id) tileset ON tileset.fk_dataset_id::text = d.id_dataset
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset
  WHERE l.layertype = 'wmts'::text AND NOT tileset.tilematrixsetid IS NULL OR l.layertype <> 'wmts'::text;

ALTER TABLE re3.view_layers_js
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_layers_js TO pgkogis;
GRANT SELECT ON TABLE re3.view_layers_js TO public;
GRANT SELECT ON TABLE re3.view_layers_js TO "www-data";
COMMENT ON VIEW re3.view_layers_js
  IS 'View für den ESRI Mapserver Dienst, baut das re2 layers.js auf mit den Tabellen dataset, re3.layers_js und tileset';

-- remove projekte column, is not used in the code
DROP VIEW re3.view_catalog;
CREATE OR REPLACE VIEW re3.view_catalog AS 
 SELECT sub.catalog_id::integer AS bgdi_id, sub.catalog_parent_id::integer AS parent_id, sub.topic, sub.category, sub.bod_layer_id, sub.name_de, sub.name_fr, COALESCE(sub.name_it, sub.name_fr) AS name_it, COALESCE(sub.name_en, sub.name_de) AS name_en, COALESCE(sub.name_rm, sub.name_de) AS name_rm, sub.order_key, sub.selected_open, sub.access, sub.staging, sub.path, sub.depth
   FROM ( WITH RECURSIVE tree(catalog_id, catalog_parent_id, topic, category, bod_layer_id, name_de, name_fr, name_it, name_en, name_rm, order_key, selected_open, access, staging, path, depth) AS (
                         SELECT catalog_adjacency_list.catalog_id, catalog_adjacency_list.catalog_parent_id, catalog_adjacency_list.topic, catalog_adjacency_list.category, catalog_adjacency_list.bod_layer_id, catalog_adjacency_list.name_de, catalog_adjacency_list.name_fr, catalog_adjacency_list.name_it, catalog_adjacency_list.name_en, catalog_adjacency_list.name_rm, catalog_adjacency_list.order_key, catalog_adjacency_list.selected_open, catalog_adjacency_list.access, t.staging, catalog_adjacency_list.category AS path, 0 AS depth
                           FROM re3.catalog catalog_adjacency_list
                      LEFT JOIN re3.topics t ON catalog_adjacency_list.topic = t.topic
                     WHERE catalog_adjacency_list.catalog_parent_id IS NULL AND NOT t.show_catalog IS NULL
                UNION ALL 
                         SELECT c.catalog_id, c.catalog_parent_id, c.topic, c.category, c.bod_layer_id, COALESCE(d.kurzbezeichnung_de, c.name_de) AS name_de, COALESCE(d.kurzbezeichnung_fr, c.name_fr) AS name_fr, COALESCE(d.kurzbezeichnung_it, c.name_it) AS name_it, COALESCE(d.kurzbezeichnung_en, c.name_en) AS name_en, COALESCE(d.kurzbezeichnung_rm, c.name_rm) AS name_rm, c.order_key, c.selected_open, c.access, 
                                CASE
                                    WHEN c.category <> ALL (ARRAY['layer'::text, 'root'::text]) THEN c.staging
                                    ELSE d.staging
                                END AS staging, (tree.path || '/'::text) || c.category AS path, tree.depth + 1 AS depth
                           FROM tree
                      JOIN re3.catalog c ON tree.catalog_id = c.catalog_parent_id AND tree.topic = c.topic
                 LEFT JOIN dataset d ON d.id_dataset = c.bod_layer_id
           WHERE d.parent_id IS NULL AND NOT COALESCE(d.kurzbezeichnung_de, c.name_de) IS NULL AND NOT COALESCE(d.kurzbezeichnung_fr, c.name_fr) IS NULL
                )
         SELECT tree.catalog_id, tree.catalog_parent_id, tree.topic, tree.category, tree.bod_layer_id, tree.name_de, tree.name_fr, tree.name_it, tree.name_en, tree.name_rm, tree.order_key, tree.selected_open, tree.access, lower(tree.staging) AS staging, tree.path, tree.depth
           FROM tree) sub
  ORDER BY sub.topic, sub.path, 
        CASE
            WHEN sub.order_key IS NOT NULL THEN to_char(sub.order_key, '000'::text)
            ELSE COALESCE(sub.name_de, sub.bod_layer_id)
        END;

ALTER TABLE re3.view_catalog
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_catalog TO pgkogis;
GRANT SELECT ON TABLE re3.view_catalog TO public;
GRANT SELECT ON TABLE re3.view_catalog TO "www-data";
COMMENT ON VIEW re3.view_catalog
  IS 'Rekursive Abfrage auf die Tabelle re3.catalog zum Auslesen der Tree struktur für den Katalog in RE3
    enthält alle sprachen (de,fr,it,rm,en)
    Das Resultat wird sortiert nach 
    1) topic
    2) path
    3) order_key (if null) -> name_rm (if null) -> bod_layer_id
  ';

DROP VIEW re3.view_bod_layer_info_de;
CREATE OR REPLACE VIEW re3.view_bod_layer_info_de AS 
 SELECT d.id_dataset AS bod_layer_id, d.frm_bezeichnung_de AS bezeichnung, d.kurzbezeichnung_de AS kurzbezeichnung, d.frm_abstract_de AS abstract, string_agg(DISTINCT i.name_abbreviation_de::text, ' | '::text ORDER BY i.name_abbreviation_de::text) AS inspire_name, string_agg(DISTINCT i.abstract_de::text, ' | '::text ORDER BY i.abstract_de::text) AS inspire_abstract, string_agg(DISTINCT s.name_abbreviation_de::text, ' | '::text ORDER BY s.name_abbreviation_de::text) AS inspire_oberthema_name, string_agg(DISTINCT s.abstract_de::text, ' | '::text ORDER BY s.abstract_de::text) AS inspire_oberthema_abstract, gbdsb.bezeichnung_de AS geobasisdatensatz_name, contact_data.name_de AS datenherr, string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT contact_wms.name_de::text, ' | '::text ORDER BY contact_wms.name_de::text) AS wms_kontakt_name, d.frm_scale_limit AS scale_limit, d.fk_geocat AS geocat_uuid, d.frm_url AS url, d.url_download, d.url_portale, string_agg(DISTINCT wms_meta.onlineresource::text, ' | '::text ORDER BY wms_meta.onlineresource::text) AS wms_resource, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, (((((((((((((btrim(COALESCE(d.frm_bezeichnung_de, ''::character varying::text)) || ' | '::text) || btrim(COALESCE(d.frm_abstract_de, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(gbdsb.bezeichnung_de, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.name_de::text, ' | '::text ORDER BY contact_wms.name_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(contact_data.name_de, ''::character varying)::text)) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(btrim(COALESCE(d.kurzbezeichnung_de, ''::character varying::text)), ''::character varying::text)) AS volltextsuche, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info AS topics, d.chargeable
   FROM dataset d
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, xt_dataset_inspire.fk_inspire_id
           FROM xt_dataset_inspire xt_dataset_inspire) xt_inspire ON d.id_dataset = xt_inspire.fk_id_dataset
   LEFT JOIN inspire i ON xt_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT xt_dataset_wms.fk_id_dataset, xt_dataset_wms.fk_map_name
   FROM xt_dataset_wms xt_dataset_wms
  WHERE COALESCE(xt_dataset_wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN dataset_timestamps t ON d.id_dataset = t.layer_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset
  WHERE d.parent_id IS NULL AND d.bodsearch IS TRUE
  GROUP BY d.id_dataset, d.frm_bezeichnung_de, d.kurzbezeichnung_de, d.frm_abstract_de, gbdsb.bezeichnung_de, contact_data.name_de, d.frm_scale_limit, d.fk_geocat, d.frm_url, d.url_download, d.url_portale, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info, d.chargeable;

ALTER TABLE re3.view_bod_layer_info_de
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_layer_info_de TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_layer_info_de TO public;
GRANT SELECT ON TABLE re3.view_bod_layer_info_de TO "www-data";
COMMENT ON VIEW re3.view_bod_layer_info_de
  IS 'RE3 Geoadmin  - Layerinfo und Suche nach Layern in der BOD, Sprache Deutsch
';

DROP VIEW re3.view_bod_layer_info_en;

CREATE OR REPLACE VIEW re3.view_bod_layer_info_en AS 
 SELECT d.id_dataset AS bod_layer_id, COALESCE(d.frm_bezeichnung_en, d.frm_bezeichnung_de) AS bezeichnung, COALESCE(d.kurzbezeichnung_en, d.kurzbezeichnung_de) AS kurzbezeichnung, COALESCE(d.frm_abstract_en, d.frm_abstract_de) AS abstract, string_agg(DISTINCT COALESCE(i.name_abbreviation_en, i.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(i.name_abbreviation_en, i.name_abbreviation_de)::text) AS inspire_name, string_agg(DISTINCT COALESCE(i.abstract_en, i.abstract_de)::text, ' | '::text ORDER BY COALESCE(i.abstract_en, i.abstract_de)::text) AS inspire_abstract, string_agg(DISTINCT COALESCE(s.name_abbreviation_en, s.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(s.name_abbreviation_en, s.name_abbreviation_de)::text) AS inspire_oberthema_name, string_agg(DISTINCT COALESCE(s.abstract_en, s.abstract_de)::text, ' | '::text ORDER BY COALESCE(s.abstract_en, s.abstract_de)::text) AS inspire_oberthema_abstract, gbdsb.bezeichnung_de AS geobasisdatensatz_name, COALESCE(contact_data.name_en, contact_data.name_de) AS datenherr, string_agg(DISTINCT COALESCE(contact_wms.abkuerzung_en, contact_wms.abkuerzung_de)::text, ' | '::text ORDER BY COALESCE(contact_wms.abkuerzung_en, contact_wms.abkuerzung_de)::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT COALESCE(contact_wms.name_en, contact_wms.name_de)::text, ' | '::text ORDER BY COALESCE(contact_wms.name_en, contact_wms.name_de)::text) AS wms_kontakt_name, d.frm_scale_limit AS scale_limit, d.fk_geocat AS geocat_uuid, d.frm_url AS url, d.url_download, d.url_portale, string_agg(DISTINCT wms_meta.onlineresource::text, ' | '::text ORDER BY wms_meta.onlineresource::text) AS wms_resource, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, (((((((((((((btrim(COALESCE(COALESCE(d.frm_bezeichnung_en, d.frm_bezeichnung_de), ''::character varying::text)) || ' | '::text) || btrim(COALESCE(COALESCE(d.frm_abstract_en, d.frm_abstract_de), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(gbdsb.bezeichnung_de, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.name_de::text, ' | '::text ORDER BY contact_wms.name_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(COALESCE(contact_data.name_en, contact_data.name_de), ''::character varying)::text)) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(btrim(COALESCE(COALESCE(d.kurzbezeichnung_en, d.kurzbezeichnung_de), ''::character varying::text)), ''::character varying::text)) AS volltextsuche, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info AS topics, d.chargeable
   FROM dataset d
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, xt_dataset_inspire.fk_inspire_id
           FROM xt_dataset_inspire xt_dataset_inspire) xt_inspire ON d.id_dataset = xt_inspire.fk_id_dataset
   LEFT JOIN inspire i ON xt_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT xt_dataset_wms.fk_id_dataset, xt_dataset_wms.fk_map_name
   FROM xt_dataset_wms xt_dataset_wms
  WHERE COALESCE(xt_dataset_wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN dataset_timestamps t ON d.id_dataset = t.layer_id
   LEFT JOIN (SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset
  WHERE d.parent_id IS NULL AND d.bodsearch IS TRUE
  GROUP BY d.id_dataset, COALESCE(d.frm_bezeichnung_en, d.frm_bezeichnung_de), COALESCE(d.kurzbezeichnung_en, d.kurzbezeichnung_de), COALESCE(d.frm_abstract_en, d.frm_abstract_de), gbdsb.bezeichnung_de, COALESCE(contact_data.name_en, contact_data.name_de), d.frm_scale_limit, d.fk_geocat, d.frm_url, d.url_download, d.url_portale, d.fk_geobasisdaten_sammlung_bundesrecht, d.staging, d.bodsearch, d.bgdi_id, d.download, t.datenstand, topics.info, d.chargeable;

ALTER TABLE re3.view_bod_layer_info_en
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_layer_info_en TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_layer_info_en TO public;
GRANT SELECT ON TABLE re3.view_bod_layer_info_en TO "www-data";
COMMENT ON VIEW re3.view_bod_layer_info_en
  IS 'RE3 Geoadmin  - Layerinfo und Suche nach Layern in der BOD, Sprache Englisch
';

DROP VIEW re3.view_bod_layer_info_fr;

CREATE OR REPLACE VIEW re3.view_bod_layer_info_fr AS 
 SELECT d.id_dataset AS bod_layer_id, d.frm_bezeichnung_fr AS bezeichnung, d.kurzbezeichnung_fr AS kurzbezeichnung, d.frm_abstract_fr AS abstract, string_agg(DISTINCT i.name_abbreviation_fr::text, ' | '::text ORDER BY i.name_abbreviation_fr::text) AS inspire_name, string_agg(DISTINCT i.abstract_fr::text, ' | '::text ORDER BY i.abstract_fr::text) AS inspire_abstract, string_agg(DISTINCT s.name_abbreviation_fr::text, ' | '::text ORDER BY s.name_abbreviation_fr::text) AS inspire_oberthema_name, string_agg(DISTINCT s.abstract_fr::text, ' | '::text ORDER BY s.abstract_fr::text) AS inspire_oberthema_abstract, gbdsb.bezeichnung_fr AS geobasisdatensatz_name, contact_data.name_fr AS datenherr, string_agg(DISTINCT contact_wms.abkuerzung_fr::text, ' | '::text ORDER BY contact_wms.abkuerzung_fr::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT contact_wms.name_fr::text, ' | '::text ORDER BY contact_wms.name_fr::text) AS wms_kontakt_name, d.frm_scale_limit AS scale_limit, d.fk_geocat AS geocat_uuid, d.frm_url AS url, d.url_download, d.url_portale, string_agg(DISTINCT wms_meta.onlineresource::text, ' | '::text ORDER BY wms_meta.onlineresource::text) AS wms_resource, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, (((((((((((((btrim(COALESCE(d.frm_bezeichnung_fr, ''::character varying::text)) || ' | '::text) || btrim(COALESCE(d.frm_abstract_fr, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(gbdsb.bezeichnung_fr, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_fr::text, ' | '::text ORDER BY contact_wms.abkuerzung_fr::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.name_fr::text, ' | '::text ORDER BY contact_wms.name_fr::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(contact_data.name_fr, ''::character varying)::text)) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_fr::text, ' | '::text ORDER BY contact_wms.abkuerzung_fr::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(btrim(COALESCE(d.kurzbezeichnung_fr, ''::character varying::text)), ''::character varying::text)) AS volltextsuche, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info AS topics, d.chargeable
   FROM dataset d
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, xt_dataset_inspire.fk_inspire_id
           FROM xt_dataset_inspire xt_dataset_inspire) xt_inspire ON d.id_dataset = xt_inspire.fk_id_dataset
   LEFT JOIN inspire i ON xt_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT xt_dataset_wms.fk_id_dataset, xt_dataset_wms.fk_map_name
   FROM xt_dataset_wms xt_dataset_wms
  WHERE COALESCE(xt_dataset_wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN dataset_timestamps t ON d.id_dataset = t.layer_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset
  WHERE d.parent_id IS NULL AND d.bodsearch IS TRUE
  GROUP BY d.id_dataset, d.frm_bezeichnung_fr, d.kurzbezeichnung_fr, d.frm_abstract_fr, gbdsb.bezeichnung_fr, contact_data.name_fr, d.frm_scale_limit, d.fk_geocat, d.frm_url, d.url_download, d.url_portale, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info, d.chargeable;

ALTER TABLE re3.view_bod_layer_info_fr
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_layer_info_fr TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_layer_info_fr TO public;
GRANT SELECT ON TABLE re3.view_bod_layer_info_fr TO "www-data";
COMMENT ON VIEW re3.view_bod_layer_info_fr
  IS 'RE3 Geoadmin  - Layerinfo und Suche nach Layern in der BOD, Sprache Fransosisch
';

DROP VIEW re3.view_bod_layer_info_it;

CREATE OR REPLACE VIEW re3.view_bod_layer_info_it AS 
 SELECT d.id_dataset AS bod_layer_id, COALESCE(d.frm_bezeichnung_it, d.frm_bezeichnung_fr) AS bezeichnung, COALESCE(d.kurzbezeichnung_it, d.kurzbezeichnung_fr) AS kurzbezeichnung, COALESCE(d.frm_abstract_it, d.frm_abstract_fr) AS abstract, string_agg(DISTINCT COALESCE(i.name_abbreviation_it, i.name_abbreviation_fr)::text, ' | '::text ORDER BY COALESCE(i.name_abbreviation_it, i.name_abbreviation_fr)::text) AS inspire_name, string_agg(DISTINCT COALESCE(i.abstract_it, i.abstract_fr)::text, ' | '::text ORDER BY COALESCE(i.abstract_it, i.abstract_fr)::text) AS inspire_abstract, string_agg(DISTINCT COALESCE(s.name_abbreviation_it, s.name_abbreviation_fr)::text, ' | '::text ORDER BY COALESCE(s.name_abbreviation_it, s.name_abbreviation_fr)::text) AS inspire_oberthema_name, string_agg(DISTINCT COALESCE(s.abstract_it, s.abstract_fr)::text, ' | '::text ORDER BY COALESCE(s.abstract_it, s.abstract_fr)::text) AS inspire_oberthema_abstract, gbdsb.bezeichnung_fr AS geobasisdatensatz_name, COALESCE(contact_data.name_it, contact_data.name_fr) AS datenherr, string_agg(DISTINCT COALESCE(contact_wms.abkuerzung_it, contact_wms.abkuerzung_fr)::text, ' | '::text ORDER BY COALESCE(contact_wms.abkuerzung_it, contact_wms.abkuerzung_fr)::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT COALESCE(contact_wms.name_it, contact_wms.name_fr)::text, ' | '::text ORDER BY COALESCE(contact_wms.name_it, contact_wms.name_fr)::text) AS wms_kontakt_name, d.frm_scale_limit AS scale_limit, d.fk_geocat AS geocat_uuid, d.frm_url AS url, d.url_download, d.url_portale, string_agg(DISTINCT wms_meta.onlineresource::text, ' | '::text ORDER BY wms_meta.onlineresource::text) AS wms_resource, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, (((((((((((((btrim(COALESCE(COALESCE(d.frm_bezeichnung_it, d.frm_bezeichnung_fr), ''::character varying::text)) || ' | '::text) || btrim(COALESCE(COALESCE(d.frm_abstract_it, d.frm_abstract_fr), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(gbdsb.bezeichnung_fr, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_fr::text, ' | '::text ORDER BY contact_wms.abkuerzung_fr::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.name_fr::text, ' | '::text ORDER BY contact_wms.name_fr::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(COALESCE(contact_data.name_it, contact_data.name_fr), ''::character varying)::text)) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_fr::text, ' | '::text ORDER BY contact_wms.abkuerzung_fr::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(btrim(COALESCE(COALESCE(d.kurzbezeichnung_it, d.kurzbezeichnung_fr), ''::character varying::text)), ''::character varying::text)) AS volltextsuche, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info AS topics, d.chargeable
   FROM dataset d
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, xt_dataset_inspire.fk_inspire_id
           FROM xt_dataset_inspire xt_dataset_inspire) xt_inspire ON d.id_dataset = xt_inspire.fk_id_dataset
   LEFT JOIN inspire i ON xt_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT xt_dataset_wms.fk_id_dataset, xt_dataset_wms.fk_map_name
   FROM xt_dataset_wms xt_dataset_wms
  WHERE COALESCE(xt_dataset_wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN dataset_timestamps t ON d.id_dataset = t.layer_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset
  WHERE d.parent_id IS NULL AND d.bodsearch IS TRUE
  GROUP BY d.id_dataset,COALESCE(d.frm_bezeichnung_it, d.frm_bezeichnung_fr), COALESCE(d.kurzbezeichnung_it, d.kurzbezeichnung_fr), COALESCE(d.frm_abstract_it, d.frm_abstract_fr), gbdsb.bezeichnung_fr, COALESCE(contact_data.name_it, contact_data.name_fr), d.frm_scale_limit, d.fk_geocat, d.frm_url, d.url_download, d.url_portale, d.fk_geobasisdaten_sammlung_bundesrecht, d.staging, d.bodsearch, d.bgdi_id, d.download, t.datenstand, topics.info, d.chargeable;

ALTER TABLE re3.view_bod_layer_info_it
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_layer_info_it TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_layer_info_it TO public;
GRANT SELECT ON TABLE re3.view_bod_layer_info_it TO "www-data";
COMMENT ON VIEW re3.view_bod_layer_info_it
  IS 'RE3 Geoadmin  - Layerinfo und Suche nach Layern in der BOD, Sprache Italienisch
';

DROP VIEW re3.view_bod_layer_info_rm;

CREATE OR REPLACE VIEW re3.view_bod_layer_info_rm AS 
 SELECT d.id_dataset AS bod_layer_id, COALESCE(d.frm_bezeichnung_rm, d.frm_bezeichnung_de) AS bezeichnung, COALESCE(d.kurzbezeichnung_rm, d.kurzbezeichnung_de) AS kurzbezeichnung, COALESCE(d.frm_abstract_rm, d.frm_abstract_de) AS abstract, string_agg(DISTINCT COALESCE(i.name_abbreviation_rm, i.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(i.name_abbreviation_rm, i.name_abbreviation_de)::text) AS inspire_name, string_agg(DISTINCT COALESCE(i.abstract_rm, i.abstract_de)::text, ' | '::text ORDER BY COALESCE(i.abstract_rm, i.abstract_de)::text) AS inspire_abstract, string_agg(DISTINCT COALESCE(s.name_abbreviation_rm, s.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(s.name_abbreviation_rm, s.name_abbreviation_de)::text) AS inspire_oberthema_name, string_agg(DISTINCT COALESCE(s.abstract_rm, s.abstract_de)::text, ' | '::text ORDER BY COALESCE(s.abstract_rm, s.abstract_de)::text) AS inspire_oberthema_abstract, gbdsb.bezeichnung_de AS geobasisdatensatz_name, COALESCE(contact_data.name_rm, contact_data.name_de) AS datenherr, string_agg(DISTINCT COALESCE(contact_wms.abkuerzung_rm, contact_wms.abkuerzung_de)::text, ' | '::text ORDER BY COALESCE(contact_wms.abkuerzung_rm, contact_wms.abkuerzung_de)::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT COALESCE(contact_wms.name_rm, contact_wms.name_de)::text, ' | '::text ORDER BY COALESCE(contact_wms.name_rm, contact_wms.name_de)::text) AS wms_kontakt_name, d.frm_scale_limit AS scale_limit, d.fk_geocat AS geocat_uuid, d.frm_url AS url, d.url_download, d.url_portale, string_agg(DISTINCT wms_meta.onlineresource::text, ' | '::text ORDER BY wms_meta.onlineresource::text) AS wms_resource, t.datenstand, d.fk_geobasisdaten_sammlung_bundesrecht, (((((((((((((btrim(COALESCE(COALESCE(d.frm_bezeichnung_rm, d.frm_bezeichnung_de), ''::character varying::text)) || ' | '::text) || btrim(COALESCE(COALESCE(d.frm_abstract_rm, d.frm_abstract_de), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(gbdsb.bezeichnung_de, ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.name_de::text, ' | '::text ORDER BY contact_wms.name_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(COALESCE(contact_data.name_rm, contact_data.name_de), ''::character varying)::text)) || ' | '::text) || btrim(COALESCE(string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text), ''::character varying::text))) || ' | '::text) || btrim(COALESCE(btrim(COALESCE(COALESCE(d.kurzbezeichnung_rm, d.kurzbezeichnung_de), ''::character varying::text)), ''::character varying::text)) AS volltextsuche, d.staging, d.bodsearch, d.bgdi_id, d.download, topics.info AS topics, d.chargeable
   FROM dataset d
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, xt_dataset_inspire.fk_inspire_id
           FROM xt_dataset_inspire xt_dataset_inspire) xt_inspire ON d.id_dataset = xt_inspire.fk_id_dataset
   LEFT JOIN inspire i ON xt_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT xt_dataset_wms.fk_id_dataset, xt_dataset_wms.fk_map_name
   FROM xt_dataset_wms xt_dataset_wms
  WHERE COALESCE(xt_dataset_wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN dataset_timestamps t ON d.id_dataset = t.layer_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset
  WHERE d.parent_id IS NULL AND d.bodsearch IS TRUE
  GROUP BY d.id_dataset, COALESCE(d.frm_bezeichnung_rm, d.frm_bezeichnung_de), COALESCE(d.kurzbezeichnung_rm, d.kurzbezeichnung_de), COALESCE(d.frm_abstract_rm, d.frm_abstract_de), gbdsb.bezeichnung_de, COALESCE(contact_data.name_rm, contact_data.name_de), d.frm_scale_limit, d.fk_geocat, d.frm_url, d.url_download, d.url_portale, d.fk_geobasisdaten_sammlung_bundesrecht, d.staging, d.bodsearch, d.bgdi_id, d.download, t.datenstand, topics.info, d.chargeable;

ALTER TABLE re3.view_bod_layer_info_rm
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_layer_info_rm TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_layer_info_rm TO public;
GRANT SELECT ON TABLE re3.view_bod_layer_info_rm TO "www-data";
COMMENT ON VIEW re3.view_bod_layer_info_rm
  IS 'RE3 Geoadmin  - Layerinfo und Suche nach Layern in der BOD, Sprache Rumantsch
';

DROP VIEW re3.view_bod_wmts_getcapabilities_de;

CREATE OR REPLACE VIEW re3.view_bod_wmts_getcapabilities_de AS 
 SELECT tileset.fk_dataset_id, tileset.tile_matrix_set_id, tileset.format, tileset."timestamp", tileset.sswmts, tileset.fk_dataset_id AS bod_layer_id, d.frm_bezeichnung_de AS bezeichnung, d.kurzbezeichnung_de AS kurzbezeichnung, d.frm_abstract_de AS abstract, i.inspire_name, i.inspire_abstract, i.inspire_oberthema_name, i.inspire_oberthema_abstract, gbdsb.bezeichnung_de AS geobasisdatensatz_name, contact_data.name_de AS datenherr, wms.wms_kontakt_abkuerzung, wms.wms_kontakt_name, tileset.zoomlevel_min, tileset.zoomlevel_max, topics.info AS topics, d.chargeable, d.staging
   FROM re3.view_tileset_concatenated tileset
   LEFT JOIN dataset d ON tileset.fk_dataset_id::text = d.id_dataset
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, string_agg(DISTINCT i.name_abbreviation_de::text, ' | '::text ORDER BY i.name_abbreviation_de::text) AS inspire_name, string_agg(DISTINCT i.abstract_de::text, ' | '::text ORDER BY i.abstract_de::text) AS inspire_abstract, string_agg(DISTINCT s.name_abbreviation_de::text, ' | '::text ORDER BY s.name_abbreviation_de::text) AS inspire_oberthema_name, string_agg(DISTINCT s.abstract_de::text, ' | '::text ORDER BY s.abstract_de::text) AS inspire_oberthema_abstract
      FROM xt_dataset_inspire xt_dataset_inspire
   LEFT JOIN inspire i ON xt_dataset_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
  GROUP BY xt_dataset_inspire.fk_id_dataset) i ON tileset.fk_dataset_id::text = i.fk_id_dataset
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT wms.fk_id_dataset, string_agg(DISTINCT contact_wms.abkuerzung_de::text, ' | '::text ORDER BY contact_wms.abkuerzung_de::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT contact_wms.name_de::text, ' | '::text ORDER BY contact_wms.name_de::text) AS wms_kontakt_name
   FROM xt_dataset_wms wms
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
  WHERE COALESCE(wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text
  GROUP BY wms.fk_id_dataset) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset;

ALTER TABLE re3.view_bod_wmts_getcapabilities_de
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_wmts_getcapabilities_de TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_de TO public;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_de TO "www-data";
COMMENT ON VIEW re3.view_bod_wmts_getcapabilities_de
  IS 'for WMTS-GetCapabilities every single timestamp';

DROP VIEW re3.view_bod_wmts_getcapabilities_en;

CREATE OR REPLACE VIEW re3.view_bod_wmts_getcapabilities_en AS 
 SELECT tileset.fk_dataset_id, tileset.tile_matrix_set_id, tileset.format, tileset."timestamp", tileset.sswmts, tileset.fk_dataset_id AS bod_layer_id, COALESCE(d.frm_bezeichnung_en, d.frm_bezeichnung_de) AS bezeichnung, COALESCE(d.kurzbezeichnung_en, d.kurzbezeichnung_de) AS kurzbezeichnung, COALESCE(d.frm_abstract_en, d.frm_abstract_de) AS abstract, i.inspire_name, i.inspire_abstract, i.inspire_oberthema_name, i.inspire_oberthema_abstract, gbdsb.bezeichnung_de AS geobasisdatensatz_name, contact_data.name_en AS datenherr, wms.wms_kontakt_abkuerzung, wms.wms_kontakt_name, tileset.zoomlevel_min, tileset.zoomlevel_max, topics.info AS topics, d.chargeable, d.staging
   FROM re3.view_tileset_concatenated tileset
   LEFT JOIN dataset d ON tileset.fk_dataset_id::text = d.id_dataset
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, string_agg(DISTINCT COALESCE(i.name_abbreviation_en, i.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(i.name_abbreviation_en, i.name_abbreviation_de)::text) AS inspire_name, string_agg(DISTINCT COALESCE(i.abstract_en, i.abstract_de)::text, ' | '::text ORDER BY COALESCE(i.abstract_en, i.abstract_de)::text) AS inspire_abstract, string_agg(DISTINCT COALESCE(s.name_abbreviation_en, s.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(s.name_abbreviation_en, s.name_abbreviation_de)::text) AS inspire_oberthema_name, string_agg(DISTINCT COALESCE(s.abstract_en, s.abstract_de)::text, ' | '::text ORDER BY COALESCE(s.abstract_en, s.abstract_de)::text) AS inspire_oberthema_abstract
      FROM xt_dataset_inspire xt_dataset_inspire
   LEFT JOIN inspire i ON xt_dataset_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
  GROUP BY xt_dataset_inspire.fk_id_dataset) i ON tileset.fk_dataset_id::text = i.fk_id_dataset
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT wms.fk_id_dataset, string_agg(DISTINCT contact_wms.abkuerzung_en::text, ' | '::text ORDER BY contact_wms.abkuerzung_en::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT COALESCE(contact_wms.name_en, contact_wms.name_de)::text, ' | '::text ORDER BY COALESCE(contact_wms.name_en, contact_wms.name_de)::text) AS wms_kontakt_name
   FROM xt_dataset_wms wms
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
  WHERE COALESCE(wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text
  GROUP BY wms.fk_id_dataset) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset;

ALTER TABLE re3.view_bod_wmts_getcapabilities_en
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_wmts_getcapabilities_en TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_en TO public;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_en TO "www-data";
COMMENT ON VIEW re3.view_bod_wmts_getcapabilities_en
  IS 'for WMTS-GetCapabilities every single timestamp';

DROP VIEW re3.view_bod_wmts_getcapabilities_fr;

CREATE OR REPLACE VIEW re3.view_bod_wmts_getcapabilities_fr AS 
 SELECT tileset.fk_dataset_id, tileset.tile_matrix_set_id, tileset.format, tileset."timestamp", tileset.sswmts, tileset.fk_dataset_id AS bod_layer_id, d.frm_bezeichnung_fr AS bezeichnung, d.kurzbezeichnung_fr AS kurzbezeichnung, d.frm_abstract_fr AS abstract, i.inspire_name, i.inspire_abstract, i.inspire_oberthema_name, i.inspire_oberthema_abstract, gbdsb.bezeichnung_fr AS geobasisdatensatz_name, contact_data.name_fr AS datenherr, wms.wms_kontakt_abkuerzung, wms.wms_kontakt_name, tileset.zoomlevel_min, tileset.zoomlevel_max, topics.info AS topics, d.chargeable, d.staging
   FROM re3.view_tileset_concatenated tileset
   LEFT JOIN dataset d ON tileset.fk_dataset_id::text = d.id_dataset
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, string_agg(DISTINCT i.name_abbreviation_fr::text, ' | '::text ORDER BY i.name_abbreviation_fr::text) AS inspire_name, string_agg(DISTINCT i.abstract_fr::text, ' | '::text ORDER BY i.abstract_fr::text) AS inspire_abstract, string_agg(DISTINCT s.name_abbreviation_fr::text, ' | '::text ORDER BY s.name_abbreviation_fr::text) AS inspire_oberthema_name, string_agg(DISTINCT s.abstract_fr::text, ' | '::text ORDER BY s.abstract_fr::text) AS inspire_oberthema_abstract
      FROM xt_dataset_inspire xt_dataset_inspire
   LEFT JOIN inspire i ON xt_dataset_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
  GROUP BY xt_dataset_inspire.fk_id_dataset) i ON tileset.fk_dataset_id::text = i.fk_id_dataset
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT wms.fk_id_dataset, string_agg(DISTINCT contact_wms.abkuerzung_fr::text, ' | '::text ORDER BY contact_wms.abkuerzung_fr::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT contact_wms.name_fr::text, ' | '::text ORDER BY contact_wms.name_fr::text) AS wms_kontakt_name
   FROM xt_dataset_wms wms
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
  WHERE COALESCE(wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text
  GROUP BY wms.fk_id_dataset) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset;

ALTER TABLE re3.view_bod_wmts_getcapabilities_fr
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_wmts_getcapabilities_fr TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_fr TO public;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_fr TO "www-data";
COMMENT ON VIEW re3.view_bod_wmts_getcapabilities_fr
  IS 'for WMTS-GetCapabilities every single timestamp';

DROP VIEW re3.view_bod_wmts_getcapabilities_it;

CREATE OR REPLACE VIEW re3.view_bod_wmts_getcapabilities_it AS 
 SELECT tileset.fk_dataset_id, tileset.tile_matrix_set_id, tileset.format, tileset."timestamp", tileset.sswmts, tileset.fk_dataset_id AS bod_layer_id, COALESCE(d.frm_bezeichnung_it, d.frm_bezeichnung_fr) AS bezeichnung, COALESCE(d.kurzbezeichnung_it, d.kurzbezeichnung_fr) AS kurzbezeichnung, COALESCE(d.frm_abstract_it, d.frm_abstract_fr) AS abstract, i.inspire_name, i.inspire_abstract, i.inspire_oberthema_name, i.inspire_oberthema_abstract, gbdsb.bezeichnung_fr AS geobasisdatensatz_name, contact_data.name_it AS datenherr, wms.wms_kontakt_abkuerzung, wms.wms_kontakt_name, tileset.zoomlevel_min, tileset.zoomlevel_max, topics.info AS topics, d.chargeable, d.staging
   FROM re3.view_tileset_concatenated tileset
   LEFT JOIN dataset d ON tileset.fk_dataset_id::text = d.id_dataset
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, string_agg(DISTINCT COALESCE(i.name_abbreviation_it, i.name_abbreviation_fr)::text, ' | '::text ORDER BY COALESCE(i.name_abbreviation_it, i.name_abbreviation_fr)::text) AS inspire_name, string_agg(DISTINCT COALESCE(i.abstract_it, i.abstract_fr)::text, ' | '::text ORDER BY COALESCE(i.abstract_it, i.abstract_fr)::text) AS inspire_abstract, string_agg(DISTINCT COALESCE(s.name_abbreviation_it, s.name_abbreviation_fr)::text, ' | '::text ORDER BY COALESCE(s.name_abbreviation_it, s.name_abbreviation_fr)::text) AS inspire_oberthema_name, string_agg(DISTINCT COALESCE(s.abstract_it, s.abstract_fr)::text, ' | '::text ORDER BY COALESCE(s.abstract_it, s.abstract_fr)::text) AS inspire_oberthema_abstract
      FROM xt_dataset_inspire xt_dataset_inspire
   LEFT JOIN inspire i ON xt_dataset_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
  GROUP BY xt_dataset_inspire.fk_id_dataset) i ON tileset.fk_dataset_id::text = i.fk_id_dataset
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT wms.fk_id_dataset, string_agg(DISTINCT contact_wms.abkuerzung_it::text, ' | '::text ORDER BY contact_wms.abkuerzung_it::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT COALESCE(contact_wms.name_it, contact_wms.name_fr)::text, ' | '::text ORDER BY COALESCE(contact_wms.name_it, contact_wms.name_fr)::text) AS wms_kontakt_name
   FROM xt_dataset_wms wms
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
  WHERE COALESCE(wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text
  GROUP BY wms.fk_id_dataset) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset;

ALTER TABLE re3.view_bod_wmts_getcapabilities_it
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_wmts_getcapabilities_it TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_it TO public;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_it TO "www-data";
COMMENT ON VIEW re3.view_bod_wmts_getcapabilities_it
  IS 'for WMTS-GetCapabilities every single timestamp';

DROP VIEW re3.view_bod_wmts_getcapabilities_rm;

CREATE OR REPLACE VIEW re3.view_bod_wmts_getcapabilities_rm AS 
 SELECT tileset.fk_dataset_id, tileset.tile_matrix_set_id, tileset.format, tileset."timestamp", tileset.sswmts, tileset.fk_dataset_id AS bod_layer_id, COALESCE(d.frm_bezeichnung_rm, d.frm_bezeichnung_de) AS bezeichnung, COALESCE(d.kurzbezeichnung_rm, d.kurzbezeichnung_de) AS kurzbezeichnung, COALESCE(d.frm_abstract_rm, d.frm_abstract_de) AS abstract, i.inspire_name, i.inspire_abstract, i.inspire_oberthema_name, i.inspire_oberthema_abstract, gbdsb.bezeichnung_de AS geobasisdatensatz_name, contact_data.name_rm AS datenherr, wms.wms_kontakt_abkuerzung, wms.wms_kontakt_name, tileset.zoomlevel_min, tileset.zoomlevel_max, topics.info AS topics, d.chargeable, d.staging
   FROM re3.view_tileset_concatenated tileset
   LEFT JOIN dataset d ON tileset.fk_dataset_id::text = d.id_dataset
   LEFT JOIN ( SELECT xt_dataset_inspire.fk_id_dataset, string_agg(DISTINCT COALESCE(i.name_abbreviation_rm, i.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(i.name_abbreviation_rm, i.name_abbreviation_de)::text) AS inspire_name, string_agg(DISTINCT COALESCE(i.abstract_rm, i.abstract_de)::text, ' | '::text ORDER BY COALESCE(i.abstract_rm, i.abstract_de)::text) AS inspire_abstract, string_agg(DISTINCT COALESCE(s.name_abbreviation_rm, s.name_abbreviation_de)::text, ' | '::text ORDER BY COALESCE(s.name_abbreviation_rm, s.name_abbreviation_de)::text) AS inspire_oberthema_name, string_agg(DISTINCT COALESCE(s.abstract_rm, s.abstract_de)::text, ' | '::text ORDER BY COALESCE(s.abstract_rm, s.abstract_de)::text) AS inspire_oberthema_abstract
      FROM xt_dataset_inspire xt_dataset_inspire
   LEFT JOIN inspire i ON xt_dataset_inspire.fk_inspire_id = i.twg_code::text
   LEFT JOIN swisstopo_theme s ON i.fk_swisstopo_theme_id = s.pk_swisstopo_theme_id
  GROUP BY xt_dataset_inspire.fk_id_dataset) i ON tileset.fk_dataset_id::text = i.fk_id_dataset
   LEFT JOIN geobasisdaten_sammlung_bundesrecht gbdsb ON d.fk_geobasisdaten_sammlung_bundesrecht = gbdsb.identifikator
   LEFT JOIN ( SELECT wms.fk_id_dataset, string_agg(DISTINCT contact_wms.abkuerzung_rm::text, ' | '::text ORDER BY contact_wms.abkuerzung_rm::text) AS wms_kontakt_abkuerzung, string_agg(DISTINCT COALESCE(contact_wms.name_rm, contact_wms.name_de)::text, ' | '::text ORDER BY COALESCE(contact_wms.name_rm, contact_wms.name_de)::text) AS wms_kontakt_name
   FROM xt_dataset_wms wms
   LEFT JOIN wms_metadata wms_meta ON wms.fk_map_name = wms_meta.pk_map_name::text
   LEFT JOIN contactorganisation contact_wms ON wms_meta.fk_contactorganisation_id = contact_wms.pk_contactorganisation_id
  WHERE COALESCE(wms.wms_enable_request, '--'::text) !~~* '%!getcapabilities%'::text
  GROUP BY wms.fk_id_dataset) wms ON d.id_dataset = wms.fk_id_dataset
   LEFT JOIN contactorganisation contact_data ON d.fk_contactorganisation_id = contact_data.pk_contactorganisation_id
   LEFT JOIN ( SELECT sub.fk_id_dataset, string_agg(DISTINCT sub.info, ','::text ORDER BY sub.info) AS info
   FROM (         SELECT catalog.bod_layer_id AS fk_id_dataset, catalog.topic AS info
                   FROM re3.catalog
        UNION 
                 SELECT xt_dataset_topics.fk_dataset AS fk_id_dataset, xt_dataset_topics.fk_topic AS info
                   FROM re3.xt_dataset_topics
    UNION 
                 SELECT fk_id_dataset, fk_map_name AS info
                   FROM xt_dataset_wms  where wms_enable_request IS NULL      ) sub
  GROUP BY sub.fk_id_dataset) topics ON d.id_dataset = topics.fk_id_dataset;

ALTER TABLE re3.view_bod_wmts_getcapabilities_rm
  OWNER TO pgkogis;
GRANT ALL ON TABLE re3.view_bod_wmts_getcapabilities_rm TO pgkogis;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_rm TO public;
GRANT SELECT ON TABLE re3.view_bod_wmts_getcapabilities_rm TO "www-data";
COMMENT ON VIEW re3.view_bod_wmts_getcapabilities_rm
  IS 'for WMTS-GetCapabilities every single timestamp';

ALTER TABLE dataset DROP COLUMN projekte;

commit;

[ltclm]

the nosetest problems have been fixed, there were access problems with some views. will run the sql script after this weeks deploys.

[ltclm]

changes applied to bod on bgdipg01t

[gjn]

Can you deploy to test cluster?

[ltclm]

db has been deployed to test cluster. here the diff in the database structure: https://github.com/geoadmin/db/commit/7b473a8f0bdaed8f7cfc10441355a42988f4f44c#diff-c24334701204a238fe43fecd0b103903

[gjn]

I let jenkins run now...