java/code/src/com/redhat/rhn/common/db/datasource/xml/Action_queries.xml | 10 +-
java/code/src/com/redhat/rhn/common/db/datasource/xml/CustomInfo_queries.xml | 12 +-
java/code/src/com/redhat/rhn/common/db/datasource/xml/Monitoring_queries.xml | 49 ++++++----
java/code/src/com/redhat/rhn/common/db/datasource/xml/SystemGroup_queries.xml | 11 +-
java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml | 8 -
web/modules/rhn/RHN/DB/DataSource/xml/System_queries.xml | 22 ++--
6 files changed, 64 insertions(+), 48 deletions(-)
New commits:
commit 9103a4c0b5f7a715d37ddab1e7d7961d09edbb04
Author: Julian Einwag <je(a)rockenstein.de>
Date: Tue May 24 10:35:59 2011 +0200
made some queries PG compatible: - replaced (+) with ANSI left join - changed TO_DATE(NULL) to TO_DATE(NULL,NULL) - changed TO_NUMBER(NULL) to TO_NUMBER(NULL,NULL) - replaced DECODE with CASE
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Action_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Action_queries.xml
index be03a6d..43ac7b6 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Action_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Action_queries.xml
@@ -13,8 +13,8 @@ SELECT EVENT_ID as ID,
DETAILS
FROM (SELECT SH.id EVENT_ID,
SH.summary SUMMARY,
- TO_DATE(NULL) AS created,
- TO_DATE(NULL) AS picked_up,
+ TO_DATE(NULL,NULL) AS created,
+ TO_DATE(NULL,NULL) AS picked_up,
SH.created as completed, -- view this as the "completed" date for sorting reasons
NULL AS history_status,
NULL AS history_type,
@@ -34,14 +34,14 @@ SELECT EVENT_ID as ID,
NULL AS details
FROM rhnActionType AType,
rhnActionStatus AStat,
- rhnUser U,
- rhnAction A,
+ rhnAction A
+ LEFT JOIN rhnUser U
+ ON A.scheduler = U.id,
rhnServerAction SA
WHERE SA.server_id = :sid
AND SA.action_id = A.id
AND ATYPE.id = A.action_type
AND AStat.id = SA.status
- AND U.id(+) = A.scheduler
AND AStat.id IN (1, 2, 3)
) X
ORDER BY COMPLETED DESC, PICKED_UP DESC, CREATED DESC, EVENT_ID DESC
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/CustomInfo_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/CustomInfo_queries.xml
index 3fd7d79..ec7d34c 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/CustomInfo_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/CustomInfo_queries.xml
@@ -7,10 +7,10 @@ SELECT CDK.id,
CDK.description,
(
SELECT COUNT(SCDV.server_id)
- FROM rhnServerCustomDataValue SCDV,
- rhnUserServerPerms USP
+ FROM rhnUserServerPerms USP
+ LEFT JOIN rhnServerCustomDataValue SCDV
+ ON USP.server_id = SCDV.server_id
WHERE USP.user_id = :user_id
- AND USP.server_id = SCDV.server_id (+)
AND SCDV.key_id = CDK.id
) as system_count,
TO_CHAR(CDK.modified, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
@@ -28,10 +28,10 @@ SELECT CDK.id,
CDK.description,
COUNT(SCDV.key_id) as system_count,
TO_CHAR(CDK.modified, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
- FROM rhnServerCustomDataValue SCDV,
- rhnCustomDataKey CDK
+ FROM rhnCustomDataKey CDK
+ LEFT JOIN rhnServerCustomDataValue SCDV
+ ON CDK.id = SCDV.key_id
WHERE CDK.org_id = :org_id
- AND CDK.id = SCDV.key_id (+)
AND NOT EXISTS (
SELECT 1 FROM rhnServerCustomDataValue
WHERE server_id = :sid
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Monitoring_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Monitoring_queries.xml
index 8ccae6a..92fa36f 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Monitoring_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Monitoring_queries.xml
@@ -124,21 +124,22 @@ WHERE customer_id = :org_id
<mode name="methods_in_org" class="com.redhat.rhn.frontend.dto.monitoring.MethodDto">
<query params="org_id">
<![CDATA[
-SELECT WC.id user_id,
- WC.login login,
- CM.recid recid,
- CM.method_name method_name,
- DECODE( MT.method_type_name,
- 'Email', CM.email_address,
- 'Pager', CM.pager_email,
- 'SNMP', CM.snmp_host) method_target,
+SELECT WC.id AS user_id,
+ WC.login AS login,
+ CM.recid AS recid,
+ CM.method_name AS method_name,
+ (CASE MT.method_type_name
+ WHEN 'Email' THEN CM.email_address
+ WHEN 'Pager' THEN CM.pager_email
+ WHEN 'SNMP' THEN CM.snmp_host
+ END) AS method_target,
MT.method_type_name method_type
- FROM rhn_contact_methods CM,
- web_contact WC,
- rhn_method_types MT
+ FROM rhn_contact_methods CM
+ LEFT JOIN rhn_method_types MT
+ ON CM.method_type_id = MT.recid,
+ web_contact WC
WHERE WC.org_id = :org_id
AND CM.contact_id = WC.id
- AND CM.method_type_id = MT.recid (+)
ORDER BY WC.login, CM.method_name
]]>
</query>
@@ -147,13 +148,23 @@ ORDER BY WC.login, CM.method_name
<mode name="servers_in_suite" class="com.redhat.rhn.frontend.dto.monitoring.MonitoredServerDto">
<query params="suite_id"><
