Shirly Radco has uploaded a new change for review.
Change subject: reports: edited ic query-all_dcs_with_storage
......................................................................
reports: edited ic query-all_dcs_with_storage
I edited the, all_dcs_with_storage, input control query,
so it will be more readable and added comments.
Change-Id: Ib493194b999faf7d8f371a12c06a357ce8ab6b0e
Signed-off-by: Shirly Radco <sra...@redhat.com>
---
M
packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_storage/P_DataCenter_ID.xml
1 file changed, 219 insertions(+), 83 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/78/25278/1
diff --git
a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_storage/P_DataCenter_ID.xml
b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_storage/P_DataCenter_ID.xml
index 6d22b11..2c4c2b1 100644
---
a/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_storage/P_DataCenter_ID.xml
+++
b/packaging/ovirt-reports/resources/reports_resources/JDBC/Input_Controls/Period_Affected/single_select/datacenter_ic/all_dcs_with_storage/P_DataCenter_ID.xml
@@ -4,8 +4,8 @@
<name>P_DataCenter_ID</name>
<version>29</version>
<label>$R{ic.dc}</label>
- <creationDate>2013-01-09T15:03:27.913+02:00</creationDate>
- <updateDate>2013-01-16T15:50:27.125+02:00</updateDate>
+ <creationDate>2014-03-03T12:38:16.626+02:00</creationDate>
+ <updateDate>2014-03-03T16:32:59.783+02:00</updateDate>
<type>4</type>
<mandatory>true</mandatory>
<readOnly>false</readOnly>
@@ -17,90 +17,226 @@
<name>Data_Centers</name>
<version>1</version>
<label>Data_Centers</label>
- <creationDate>2013-01-16T15:50:27.125+02:00</creationDate>
- <updateDate>2013-01-16T15:50:27.125+02:00</updateDate>
+ <creationDate>2014-03-03T16:32:59.783+02:00</creationDate>
+ <updateDate>2014-03-03T16:32:59.783+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_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_storage - datacenters
+-- This query will return only datacenters that that have storage domains
+
+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_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}
+)
+-- Latest configuration of the datacenters
+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 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_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 ( -- Check if there aren't any datacenter with storage domains
+ SELECT 1
+ FROM v3_4_configuration_history_datacenters
+ WHERE 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/25278
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ib493194b999faf7d8f371a12c06a357ce8ab6b0e
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-reports
Gerrit-Branch: master
Gerrit-Owner: Shirly Radco <sra...@redhat.com>
_______________________________________________
Engine-patches mailing list
Engine-patches@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-patches
