Shirly Radco has uploaded a new change for review. Change subject: reports: edited ic query-all_dcs_with_entities ......................................................................
reports: edited ic query-all_dcs_with_entities I edited the, all_dcs_with_entities, input control query, so it will be more readable and added comments. Change-Id: Ief87a0c632215108649a68f8f410055b4f371384 Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_entities/P_DataCenter_ID.xml 1 file changed, 320 insertions(+), 131 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/81/25281/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_entities/P_DataCenter_ID.xml b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_entities/P_DataCenter_ID.xml index 0895535..753ec1b 100644 --- a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_entities/P_DataCenter_ID.xml +++ b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_entities/P_DataCenter_ID.xml @@ -20,139 +20,328 @@ <creationDate>2013-01-16T15:44:28.874+02:00</creationDate> <updateDate>2013-01-16T15:44:28.874+02:00</updateDate> <language>sql</language> - <queryString>SELECT DISTINCT cast(datacenter_id as varchar), delete_date, CASE - WHEN delete_date IS NULL THEN datacenter_name - ELSE datacenter_name || ' (' || loc_removed_on.removed_on || ' ' || to_char(delete_date, $P{datetimelocalepattern}) || ')' - END as combo_name -from v3_4_configuration_history_datacenters - LEFT OUTER JOIN (SELECT DISTINCT coalesce(enum_translator_localized.value_localized,enum_translator_default.value) as removed_on - FROM enum_translator as enum_translator_default - LEFT OUTER JOIN (SELECT enum_type, enum_key, value as value_localized - FROM enum_translator - WHERE language_code = $P{userlocale}) as enum_translator_localized ON (enum_translator_localized.enum_type = enum_translator_default.enum_type AND enum_translator_localized.enum_key = enum_translator_default.enum_key) - WHERE enum_translator_default.language_code = 'en_US' - AND enum_translator_default.enum_type = 'REPORTS_REMOVED_ON') as loc_removed_on ON (0=0) -WHERE (datacenter_id in (SELECT DISTINCT datacenter_id - FROM v3_4_configuration_history_clusters - WHERE history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_clusters a - GROUP BY a.cluster_id) - AND create_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted} - AND cluster_id in (SELECT DISTINCT cluster_id - FROM v3_4_configuration_history_hosts - WHERE history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_hosts a - GROUP BY a.host_id) - AND create_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted})) - OR datacenter_id in (SELECT DISTINCT datacenter_id - FROM v3_4_map_history_datacenters_storage_domains, v3_4_configuration_history_storage_domains - WHERE v3_4_map_history_datacenters_storage_domains.storage_domain_id = v3_4_configuration_history_storage_domains.storage_domain_id - AND v3_4_map_history_datacenters_storage_domains.history_id in (SELECT max(a.history_id) - FROM v3_4_map_history_datacenters_storage_domains a - GROUP BY a.storage_domain_id, a.datacenter_id) - AND v3_4_configuration_history_storage_domains.history_id in (SELECT max(b.history_id) - FROM v3_4_configuration_history_storage_domains b - GROUP BY b.storage_domain_id) - AND create_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - AND attach_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (detach_date IS NULL OR detach_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted})) - AND history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_datacenters a - GROUP BY a.datacenter_id) - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted} + <queryString>-- all_dcs_with_entities - datacenters +-- This query will return only datacenters that have storage domains or hosts. + +SELECT DISTINCT + CAST ( datacenter_id AS varchar ), + delete_date, + CASE + WHEN delete_date IS NULL + THEN datacenter_name + ELSE + datacenter_name + || + ' (' + || + loc_removed_on.removed_on + || + ' ' + || + to_char ( delete_date, $P{datetimelocalepattern} ) + || + ')' + END AS combo_name +FROM v3_4_configuration_history_datacenters + LEFT OUTER JOIN ( + SELECT DISTINCT + COALESCE ( + enum_translator_localized.value_localized, + enum_translator_default.value + ) + AS removed_on + FROM enum_translator as enum_translator_default + LEFT OUTER JOIN ( + SELECT + enum_type, + enum_key, + value AS value_localized + FROM enum_translator + WHERE language_code = $P{userlocale} + ) + AS enum_translator_localized + ON ( + enum_translator_localized.enum_type = + enum_translator_default.enum_type + AND enum_translator_localized.enum_key = + enum_translator_default.enum_key + ) + WHERE enum_translator_default.language_code = 'en_US' + AND enum_translator_default.enum_type = 'REPORTS_REMOVED_ON' + ) + AS loc_removed_on + ON ( 0 = 0 ) +WHERE datacenter_id IN ( + SELECT DISTINCT datacenter_id + FROM v3_4_configuration_history_clusters + WHERE history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_clusters a + GROUP BY a.cluster_id + ) + AND create_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + -- This will determine if deleted entities will be included in the report, + -- according to the user selection for "is_deleted" parameter + $P!{is_deleted} + AND cluster_id IN ( + SELECT DISTINCT cluster_id + FROM v3_4_configuration_history_hosts + WHERE history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_hosts a + GROUP BY a.host_id + ) + AND create_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + $P!{is_deleted} + ) +) +OR datacenter_id IN ( + SELECT DISTINCT datacenter_id + FROM + v3_4_map_history_datacenters_storage_domains, + v3_4_configuration_history_storage_domains + WHERE + v3_4_map_history_datacenters_storage_domains.storage_domain_id = + v3_4_configuration_history_storage_domains.storage_domain_id + -- Latest configuration of datacenters_storage_domains map + AND v3_4_map_history_datacenters_storage_domains.history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_map_history_datacenters_storage_domains a + GROUP BY a.storage_domain_id, a.datacenter_id + ) + -- Latest configuration of storage domains + AND v3_4_configuration_history_storage_domains.history_id IN ( + SELECT MAX ( b.history_id ) + FROM v3_4_configuration_history_storage_domains b + GROUP BY b.storage_domain_id + ) + AND create_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + AND attach_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + $P!{is_deleted} +) +AND history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_datacenters a + GROUP BY a.datacenter_id +) +AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) +) +$P!{is_deleted} + UNION ALL -SELECT distinct '00000000-0000-0000-0000-000000000000', cast(null as timestamp), coalesce(enum_translator_localized.value_localized,enum_translator_default.value) -FROM enum_translator as enum_translator_default - LEFT OUTER JOIN (SELECT enum_type, enum_key, value as value_localized - FROM enum_translator - WHERE language_code = $P{userlocale}) as enum_translator_localized ON (enum_translator_localized.enum_type = enum_translator_default.enum_type AND enum_translator_localized.enum_key = enum_translator_default.enum_key) + +-- If there are no datacenters with hosts or storage domains, the list will include +-- "None Available" value, localized according to the session +SELECT DISTINCT + '00000000-0000-0000-0000-000000000000', + CAST ( NULL AS timestamp ), + COALESCE ( + enum_translator_localized.value_localized, + enum_translator_default.value + ) +FROM enum_translator AS enum_translator_default + LEFT OUTER JOIN ( + SELECT + enum_type, + enum_key, + value AS value_localized + FROM enum_translator + WHERE language_code = $P{userlocale} + ) + AS enum_translator_localized + ON ( + enum_translator_localized.enum_type = + enum_translator_default.enum_type + AND enum_translator_localized.enum_key = + enum_translator_default.enum_key + ) WHERE language_code = 'en_US' - AND enum_translator_default.enum_type = 'REPORTS_NONE_AVAILABLE' - AND NOT EXISTS (Select 1 - from v3_4_configuration_history_datacenters - WHERE (datacenter_id in (SELECT DISTINCT datacenter_id - FROM v3_4_configuration_history_clusters - WHERE history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_clusters a - GROUP BY a.cluster_id) - AND create_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted} - AND cluster_id in (SELECT DISTINCT cluster_id - FROM v3_4_configuration_history_hosts - WHERE history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_hosts a - GROUP BY a.host_id) - AND create_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted})) - OR datacenter_id in (SELECT DISTINCT datacenter_id - FROM v3_4_map_history_datacenters_storage_domains, v3_4_configuration_history_storage_domains - WHERE v3_4_map_history_datacenters_storage_domains.storage_domain_id = v3_4_configuration_history_storage_domains.storage_domain_id - AND v3_4_map_history_datacenters_storage_domains.history_id in (SELECT max(a.history_id) - FROM v3_4_map_history_datacenters_storage_domains a - GROUP BY a.storage_domain_id, a.datacenter_id) - AND v3_4_configuration_history_storage_domains.history_id in (SELECT max(b.history_id) - FROM v3_4_configuration_history_storage_domains b - GROUP BY b.storage_domain_id) - AND create_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - AND attach_date <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND (detach_date IS NULL OR detach_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted})) - AND history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_datacenters a - GROUP BY a.datacenter_id) - AND (delete_date IS NULL OR delete_date >= CAST($P{P_Start_Date} as TIMESTAMP)) - $P!{is_deleted}) -order by delete_date DESC, combo_name +AND enum_translator_default.enum_type = 'REPORTS_NONE_AVAILABLE' +AND NOT EXISTS ( + SELECT 1 + FROM v3_4_configuration_history_datacenters + WHERE datacenter_id IN ( + SELECT DISTINCT datacenter_id + FROM v3_4_configuration_history_clusters + WHERE history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_clusters a + GROUP BY a.cluster_id + ) + AND create_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + $P!{is_deleted} + AND cluster_id IN ( + SELECT DISTINCT cluster_id + FROM v3_4_configuration_history_hosts + WHERE history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_hosts a + GROUP BY a.host_id + ) + AND create_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + $P!{is_deleted} + ) + ) + OR datacenter_id IN ( + SELECT DISTINCT datacenter_id + FROM + v3_4_map_history_datacenters_storage_domains, + v3_4_configuration_history_storage_domains + WHERE + v3_4_map_history_datacenters_storage_domains.storage_domain_id = + v3_4_configuration_history_storage_domains.storage_domain_id + -- Latest configuration of datacenters_storage_domains map + AND v3_4_map_history_datacenters_storage_domains.history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_map_history_datacenters_storage_domains a + GROUP BY a.storage_domain_id, a.datacenter_id + ) + -- Latest configuration of storage domains + AND v3_4_configuration_history_storage_domains.history_id IN ( + SELECT MAX ( b.history_id ) + FROM v3_4_configuration_history_storage_domains b + GROUP BY b.storage_domain_id + ) + AND create_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + AND attach_date <= + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS timestamp ) + interval '1 year' + END + AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) + ) + $P!{is_deleted} + ) +AND history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_datacenters a + GROUP BY a.datacenter_id +) +AND ( + delete_date IS NULL + OR + delete_date >= CAST ( $P{P_Start_Date} AS timestamp ) +) +$P!{is_deleted} +) +ORDER BY delete_date DESC, combo_name </queryString> <dataSource> <uri>/reports_resources/JDBC/data_sources/ovirt</uri> -- To view, visit http://gerrit.ovirt.org/25281 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ief87a0c632215108649a68f8f410055b4f371384 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-reports Gerrit-Branch: master Gerrit-Owner: Shirly Radco <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
