Shirly Radco has uploaded a new change for review. Change subject: reports: edited ic query-only_hosts_hosts_list ......................................................................
reports: edited ic query-only_hosts_hosts_list I edited the, only_hosts_hosts_list, input control query, so it will be more readable and added comments. Change-Id: I002de9e97065f9c9be961a22218cc677874951a4 Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_Hosts/P_Host_ListID.xml 1 file changed, 91 insertions(+), 32 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/18/25418/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_Hosts/P_Host_ListID.xml b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_Hosts/P_Host_ListID.xml index 5f973a1..45790d7 100644 --- a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_Hosts/P_Host_ListID.xml +++ b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_Hosts/P_Host_ListID.xml @@ -2,10 +2,10 @@ <inputControl> <folder>/reports_resources/JDBC/Input_Controls/Regular_Input_Controls/Only_Hosts</folder> <name>P_Host_ListID</name> - <version>23</version> + <version>33</version> <label>$R{ic.hosts.list}</label> - <creationDate>2013-01-09T15:04:15.870+02:00</creationDate> - <updateDate>2013-01-16T15:13:37.601+02:00</updateDate> + <creationDate>2014-03-05T13:33:13.800+02:00</creationDate> + <updateDate>2014-03-05T15:00:24.087+02:00</updateDate> <type>7</type> <mandatory>true</mandatory> <readOnly>false</readOnly> @@ -17,38 +17,97 @@ <name>host_multiselect</name> <version>1</version> <label>host_multiselect</label> - <creationDate>2013-01-16T15:13:37.601+02:00</creationDate> - <updateDate>2013-01-16T15:13:37.601+02:00</updateDate> + <creationDate>2014-03-05T15:00:24.087+02:00</creationDate> + <updateDate>2014-03-05T15:00:24.087+02:00</updateDate> <language>sql</language> - <queryString>SELECT distinct delete_date, CASE - WHEN delete_date IS NULL then host_name - ELSE host_name || ' (' || loc_removed_on.removed_on || ' ' || to_char(delete_date, $P{datetimelocalepattern}) || ')' - END as combo_name, cast(host_id as varchar) + <queryString>-- This query will return hosts ids list, +-- filtered by datacenter, cluster and host type chosen by the user + +SELECT DISTINCT + delete_date, + CASE + WHEN delete_date IS NULL + THEN host_name + ELSE + host_name + || + ' (' + || + loc_removed_on.removed_on + || + ' ' + || + to_char( + delete_date, + $P{datetimelocalepattern} + ) + || + ')' + END AS combo_name, + CAST ( host_id AS varchar ) FROM v3_4_configuration_history_hosts - 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 v3_4_configuration_history_hosts.cluster_id in (SELECT v3_4_configuration_history_clusters.cluster_id - FROM v3_4_configuration_history_clusters - WHERE v3_4_configuration_history_clusters.datacenter_id = cast($P{P_DataCenter_ID} as uuid)) -AND v3_4_configuration_history_hosts.cluster_id = CASE $P{P_Cluster_ID} -WHEN '11111111-1111-1111-1111-111111111111' then v3_4_configuration_history_hosts.cluster_id -ELSE cast($P{P_Cluster_ID} as uuid) -END -AND v3_4_configuration_history_hosts.host_type = CASE cast($P{P_Host_Type} as int) - WHEN -1 THEN v3_4_configuration_history_hosts.host_type - WHEN NULL THEN v3_4_configuration_history_hosts.host_type - ELSE cast($P{P_Host_Type} as int) - END -AND history_id in (SELECT max(a.history_id) - FROM v3_4_configuration_history_hosts as a - GROUP BY a.host_id) + 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 ) +-- Here we filter the results by the chosen datacenter +WHERE v3_4_configuration_history_hosts.cluster_id IN ( + SELECT v3_4_configuration_history_clusters.cluster_id + FROM v3_4_configuration_history_clusters + WHERE + v3_4_configuration_history_clusters.datacenter_id = + CAST ( $P{P_DataCenter_ID} as uuid ) +) +-- Here we filter the results by the chosen cluster +AND v3_4_configuration_history_hosts.cluster_id = + CASE $P{P_Cluster_ID} + WHEN '11111111-1111-1111-1111-111111111111' + THEN v3_4_configuration_history_hosts.cluster_id + ELSE CAST ( $P{P_Cluster_ID} as uuid ) + END +-- Here we filter the results by the chosen host type +AND v3_4_configuration_history_hosts.host_type = + CASE CAST ( $P{P_Host_Type} as int ) + WHEN -1 + THEN v3_4_configuration_history_hosts.host_type + WHEN NULL + THEN v3_4_configuration_history_hosts.host_type + ELSE CAST ( $P{P_Host_Type} as int ) + END +-- Here we get the latest hosts configuration +AND history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_4_configuration_history_hosts as a + GROUP BY a.host_id +) +-- This will determine if deleted entities will be included in the report, +-- according to the user selection for "is_deleted" parameter $P!{active_hosts_select} -order by delete_date DESC, combo_name</queryString> +ORDER BY delete_date DESC, combo_name</queryString> <dataSource> <uri>/reports_resources/JDBC/data_sources/ovirt</uri> </dataSource> -- To view, visit http://gerrit.ovirt.org/25418 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I002de9e97065f9c9be961a22218cc677874951a4 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
