java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml | 87 ++--
java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml | 70 ++-
java/spacewalk-java.spec | 9
rel-eng/packages/spacewalk-java | 2
web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml | 177
+++++-----
5 files changed, 203 insertions(+), 142 deletions(-)
New commits:
commit de311c8de35542c338a175046c86869f17f4abc0
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Fri Oct 29 08:47:42 2010 +0200
Automatic commit of package [spacewalk-java] release [1.2.76-1].
diff --git a/java/spacewalk-java.spec b/java/spacewalk-java.spec
index 3a7fec7..7426314 100644
--- a/java/spacewalk-java.spec
+++ b/java/spacewalk-java.spec
@@ -19,7 +19,7 @@ Name: spacewalk-java
Summary: Spacewalk Java site packages
Group: Applications/Internet
License: GPLv2
-Version: 1.2.75
+Version: 1.2.76
Release: 1%{?dist}
URL:
https://fedorahosted.org/spacewalk
Source0:
https://fedorahosted.org/releases/s/p/spacewalk/%{name}-%{version}.tar.gz
@@ -358,6 +358,13 @@ fi
%{jardir}/postgresql-jdbc.jar
%changelog
+* Fri Oct 29 2010 Lukas Zapletal 1.2.76-1
+- Making DISTINCT-ORDER BY package/system queries portable
+- Removing unnecessary subselect
+- Simplifying ORDER BY clauses in package queries
+- Revert "Reverting "Removed unnecessary ORDER BY" commits and
fixing"
+
+
* Wed Oct 27 2010 Shannon Hughes <shughes(a)redhat.com> 1.2.75-1
- fix for checkstyle (shughes(a)redhat.com)
diff --git a/rel-eng/packages/spacewalk-java b/rel-eng/packages/spacewalk-java
index de31ef9..87c88f0 100644
--- a/rel-eng/packages/spacewalk-java
+++ b/rel-eng/packages/spacewalk-java
@@ -1 +1 @@
-1.2.75-1 java/
+1.2.76-1 java/
commit a283b7f6b7192250e9ab99c3942a3c0e5be3b1f2
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Fri Oct 29 08:45:08 2010 +0200
Making DISTINCT-ORDER BY package/system queries portable
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
index 3fec969..53bf7d4 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
@@ -44,41 +44,49 @@ ORDER BY UPPER(pn.name), full_list.evr
<mode name="package_conflicts">
<query params="pid">
+select * from (
SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageConflicts P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY UPPER(C.name), C.version
+) X
+ORDER BY UPPER(X.name), X.version
</query>
</mode>
<mode name="package_provides">
<query params="pid">
+select * from (
SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageProvides P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY UPPER(C.name), C.version
+) X
+ORDER BY UPPER(X.name), X.version
</query>
</mode>
<mode name="package_obsoletes">
<query params="pid">
+select * from (
SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageObsoletes P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY UPPER(C.name), C.version
+) X
+ORDER BY UPPER(X.name), X.version
</query>
</mode>
<mode name="package_requires">
<query params="pid">
+select * from (
SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageRequires P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY UPPER(C.name), C.version
+) X
+ORDER BY UPPER(X.name), X.version
</query>
</mode>
@@ -194,6 +202,7 @@ ORDER BY UPPER(C.label)
<mode name="name_by_provide"
class="com.redhat.rhn.frontend.dto.PackageListItem">
<query params="org_id, cap_name">
+select * from (
SELECT DISTINCT PN.name
FROM rhnPackageName PN,
rhnPackage P,
@@ -212,12 +221,14 @@ WHERE PC.name = :cap_name
AND CFM.CHANNEL_ID = CP.channel_id
AND CP.package_id = P.id))
AND P.name_id = PN.id
-ORDER BY UPPER(PN.name)
+) X
+ORDER BY UPPER(X.name)
</query>
</mode>
<mode name="name_by_provide_and_channel"
class="com.redhat.rhn.frontend.dto.PackageListItem">
<query params="channel_id, org_id, cap_name">
+select * from (
SELECT DISTINCT PN.name
FROM rhnPackageName PN,
@@ -244,7 +255,8 @@ WHERE PC.name = :cap_name
AND CFM.CHANNEL_ID = CP.channel_id
AND CP.package_id = P.id))
AND P.name_id = PN.id
-ORDER BY UPPER(PN.name)
+) X
+ORDER BY UPPER(X.name)
</query>
</mode>
@@ -336,6 +348,7 @@ SELECT P.id AS ID,
<!-- used by ChannelSoftwareHandler -->
<mode name="latest_packages_in_channel_api">
<query params="cid">
+select * from (
SELECT DISTINCT
CNP.package_id as id,
PN.name as name,
@@ -353,7 +366,8 @@ SELECT DISTINCT
AND PN.id = CNP.name_id
AND PE.id = CNP.evr_id
AND PA.id = CNP.package_arch_id
-ORDER BY UPPER(PN.name)
+) X
+ORDER BY UPPER(X.name)
</query>
</mode>
@@ -362,6 +376,7 @@ ORDER BY UPPER(PN.name)
<!-- must call the id_combo simply 'id' because listview assumes that
will be the name -->
+select * from (
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS nvre,
PN.id || '|' || CNP.evr_id || '|' || PA.id AS id_combo,
@@ -375,7 +390,8 @@ SELECT DISTINCT
AND PN.id = CNP.name_id
AND PE.id = CNP.evr_id
AND PA.id = CNP.PACKAGE_ARCH_ID
-ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
+) X
+ORDER BY UPPER(X.nvre)
</query>
</mode>
@@ -510,6 +526,7 @@ ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr)),
UPPER(PA.label), P
<mode name="packages_available_to_errata"
class="com.redhat.rhn.frontend.dto.PackageOverview">
<query params="org_id, eid">
+select * from (
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN ''
ELSE ':' || PE.epoch END)
|| '-' || PA.label PACKAGE_NVRE
@@ -528,13 +545,15 @@ SELECT DISTINCT P.id id, PN.name || '-' || PE.version
FROM rhnErrataPackage EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
-ORDER BY UPPER(PACKAGE_NVRE)
+) X
+ORDER BY UPPER(X.PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
</mode>
<mode name="packages_available_to_tmp_errata"
class="com.redhat.rhn.frontend.dto.PackageOverview">
<query params="org_id, eid">
+select * from (
SELECT DISTINCT P.id ID,
PN.name || '-' || PE.version || '-' || PE.release
|| (CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' ||
PE.epoch END)
@@ -554,7 +573,8 @@ ORDER BY UPPER(PACKAGE_NVRE)
FROM rhnErrataPackageTmp EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
-ORDER BY UPPER(PACKAGE_NVRE)
+) X
+ORDER BY UPPER(X.PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
</mode>
@@ -860,6 +880,7 @@ SELECT CP.package_id as ID,
<mode name="packages_associated_with_action">
<query params="aid">
+select * from (
SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
@@ -867,7 +888,8 @@ SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name ||
'-' || evr_t_as_vre_sim
WHERE AP.action_id = :aid
AND PN.id = AP.name_id
AND PE.id = AP.evr_id
-ORDER BY UPPER(NVRE)
+) X
+ORDER BY UPPER(X.NVRE)
</query>
</mode>
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
index bb447c7..7437492 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
@@ -2,6 +2,7 @@
<mode name="systems_with_feature"
class="com.redhat.rhn.frontend.dto.SystemOverview">
<query params="user_id, feature">
+select * from (
SELECT DISTINCT S.id,
S.name,
SI.server_id AS IS_RHN_SATELLITE,
@@ -14,12 +15,14 @@
AND S.id = USP.server_id
AND Sinfo.server_id = S.id
AND EXISTS(SELECT 1 FROM rhnServerFeaturesView SFV WHERE SFV.server_id = S.ID AND
SFV.label = :feature)
-ORDER BY UPPER(NVL(S.name, '(none)')), S.id
+) X
+ORDER BY UPPER(NVL(X.name, '(none)')), X.id
</query>
</mode>
<mode name="virtual_hosts_for_user"
class="com.redhat.rhn.frontend.dto.SystemOverview">
<query params="user_id">
+select * from (
SELECT DISTINCT S.id,
S.name
FROM rhnServer S,
@@ -30,7 +33,8 @@ ORDER BY UPPER(NVL(S.name, '(none)')), S.id
AND VI.host_system_id = S.id
AND USP.server_id = S.id
AND USP.user_id = :user_id
-ORDER BY UPPER(NVL(S.name, '(none)')), S.id
+) X
+ORDER BY UPPER(NVL(X.name, '(none)')), X.id
</query>
<elaborator name="system_overview" />
</mode>
@@ -71,6 +75,7 @@ ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
<mode name="ssm_remote_commandable">
<query params="user_id">
+select * from (
SELECT DISTINCT S.id,
S.name AS SERVER_NAME
FROM rhnServer S,
@@ -87,7 +92,8 @@ SELECT DISTINCT S.id,
AND CC.capability_name_id = CCN.id
AND CCN.name = 'script.run'
AND USP.server_id = S.id
-ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
+) X
+ORDER BY UPPER(NVL(X.name, '(none)')), X.id
</query>
<elaborator name="system_overview" />
</mode>
@@ -112,6 +118,7 @@ SELECT CDK.id,
<query name="ssm_package_upgrades_systems" params="user_id, set_label,
package_set_label">
+select * from (
SELECT DISTINCT
S.id,
S.name AS SERVER_NAME
@@ -139,12 +146,14 @@ SELECT DISTINCT
AND C.id = CP.channel_id
AND CP.package_id = P.id
AND P.package_arch_id = PA.id
-ORDER BY UPPER(S.name)
+) X
+ORDER BY UPPER(X.name)
</query>
<mode name="ssm_package_upgrades_conf">
<query name="ssm_package_upgrades_systems" />
<elaborator params="user_id, package_set_label"
multiple="t">
+select * from (
SELECT DISTINCT
S.id AS ID,
S.name AS SERVER_NAME,
@@ -179,7 +188,8 @@ SELECT DISTINCT
AND P.name_id = PN.id
AND P.evr_id = PE.id
AND P.package_arch_id = PA.id
-ORDER BY UPPER(S.name), UPPER(PN.name || '-' || PE.evr.as_vre_simple() ||
'-' || PA.label)
+) X
+ORDER BY UPPER(X.name), UPPER(X.NVRE)
</elaborator>
</mode>
@@ -401,6 +411,7 @@ SELECT ES.id, S.name, 1 as selectable
<mode name="xmlrpc_visible_to_user">
<query params="user_id">
+select * from (
SELECT DISTINCT S.id AS ID,
S.name AS NAME,
INFO.checkin AS LAST_CHECKIN
@@ -408,7 +419,8 @@ SELECT ES.id, S.name, 1 as selectable
WHERE USP.user_id = :user_id
AND S.id = USP.server_id
AND S.id = INFO.server_id
-ORDER BY UPPER(NVL(S.name, '(none)')), S.id
+) X
+ORDER BY UPPER(NVL(X.name, '(none)')), X.id
</query>
</mode>
@@ -467,6 +479,7 @@ ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</mode>
<query name="available_to_uid" params="formvar_uid">
+select * from (
SELECT DISTINCT S.id AS ID,
S.name AS SERVER_NAME,
(SELECT 1
@@ -476,7 +489,8 @@ ORDER BY UPPER(NVL(S.name, '(none)')), S.id
FROM rhnServer S, rhnUserServerPerms USP
WHERE USP.user_id = :formvar_uid
AND S.id = USP.server_id
-ORDER BY UPPER(NVL(S.name, '(none)')), S.id
+) X
+ORDER BY UPPER(NVL(X.name, '(none)')), X.id
</query>
@@ -529,6 +543,7 @@ where a.user_id = :formvar_uid
<mode name="affected_by_errata_no_selectable"
class="com.redhat.rhn.frontend.dto.SystemOverview">
<query params="eid, user_id">
+select * from (
SELECT DISTINCT S.id as system_id,
S.name as system_name,
S.id as id,
@@ -540,13 +555,14 @@ where a.user_id = :formvar_uid
AND USP.user_id = :user_id
AND S.id = USP.server_id
AND S.id = SNEC.server_id
-ORDER BY UPPER(NVL(S.name, '(none)')), S.id
+) X
+ORDER BY UPPER(NVL(X.name, '(none)')), X.id
</query>
</mode>
<mode name="affected_by_errata"
class="com.redhat.rhn.frontend.dto.SystemOverview">
<query params="eid, user_id">
- SELECT * FROM (
+select * from (
SELECT DISTINCT S.id, S.name,
1 as selectable
FROM rhnServerNeededErrataCache SNEC,
@@ -556,7 +572,7 @@ ORDER BY UPPER(NVL(S.name, '(none)')), S.id
AND USP.user_id = :user_id
AND S.id = USP.server_id
AND S.id = SNEC.server_id
- ) X, rhnEntitledServers S
+) X, rhnEntitledServers S
ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</query>
<elaborator params="eid" multiple="t">
@@ -606,6 +622,7 @@ ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
<query name="system_set_remove_package_query" params="user_id,
set_label, package_set_label">
+select * from (
SELECT DISTINCT S.id,
S.name SYSTEM_NAME
FROM rhnServer S,
@@ -618,6 +635,7 @@ ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
AND SP.evr_id = ST2.element_two
AND (SP.package_arch_id = ST2.element_three OR (SP.package_arch_id is null and
ST2.element_three is null))
AND SP.server_id = S.id
+) X, rhnServer S
ORDER BY UPPER(S.name), S.id
</query>
@@ -664,7 +682,7 @@ SELECT DISTINCT SP.server_id id,
<mode name="out_of_date"
class="com.redhat.rhn.frontend.dto.SystemOverview">
<query params="org_id, user_id">
-SELECT * FROM (
+select * from (
SELECT DISTINCT S.ID, S.NAME,
(SELECT 1
FROM rhnServerFeaturesView SFV
@@ -672,7 +690,7 @@ SELECT * FROM (
AND SFV.label = 'ftr_system_grouping') AS selectable
FROM rhnServer S, rhnUserServerPerms USP, rhnServerNeededPackageCache SNPC
WHERE S.ORG_ID = :org_id AND S.ID = USP.server_id AND USP.user_id = :user_id AND
S.id = SNPC.server_id
- ) X, rhnServer S
+) X, rhnServer S
ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
</query>
<elaborator name="system_overview" />
@@ -1238,13 +1256,13 @@ ORDER BY SERVER_ID
<query name="in_set" params="user_id, set_label">
- SELECT * FROM (
+select * from (
SELECT DISTINCT S.id, S.name
FROM rhnServer S, rhnSet ST
WHERE ST.user_id = :user_id
AND ST.label = :set_label
AND ST.element = S.id
- ) X
+) X
ORDER BY UPPER(NVL(name, '(none)')), id
</query>
@@ -1283,6 +1301,7 @@ ORDER BY NAME, VI.virtual_system_id, VI.uuid DESC
</mode>
<query name="system_package_data_from_set" params="user_id,
package_set_label">
+select * from (
SELECT DISTINCT S.id SYSTEM_ID, S.name SYSTEM_NAME, PN.name || '-' ||
PE.evr.as_vre_simple() NVRE
FROM rhnServer S,
rhnPackageEVR PE,
@@ -1293,6 +1312,7 @@ SELECT DISTINCT S.id SYSTEM_ID, S.name SYSTEM_NAME, PN.name ||
'-' || PE.evr.as
AND PN.id IN (SELECT element FROM rhnSet WHERE user_id = :user_id AND label =
:package_set_label)
AND SP.evr_id = PE.id
AND SP.server_id = S.id
+) X, rhnServer S
ORDER BY UPPER(S.name)
</query>
diff --git a/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
b/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
index 83cf36d..d3e1daf 100644
--- a/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
+++ b/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
@@ -2,6 +2,7 @@
<mode name="name_by_provide">
<query params="org_id, cap_name">
+select * from (
SELECT DISTINCT PN.name
FROM rhnPackageName PN,
rhnPackage P,
@@ -18,7 +19,8 @@ WHERE PC.name = :cap_name
AND AC.channel_id = CP.channel_id
AND CP.package_id = P.id))
AND P.name_id = PN.id
-ORDER BY UPPER(PN.name)
+) X
+ORDER BY UPPER(X.name)
</query>
</mode>
@@ -125,6 +127,7 @@ ORDER BY UPPER(PACKAGE)
<mode name="packages_in_errata">
<query params="eid">
+select * from (
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE
':' || PE.epoch END)
|| '-' || PA.label package_nvre
@@ -138,6 +141,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
+) X, rhnPackageName PN, rhnPackageEVR PE, rhnPackageArch PA, rhnPackage P,
rhnErrataPackage EP
ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
@@ -145,6 +149,7 @@ ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label,
P.id
<mode name="packages_in_tmp_errata">
<query params="eid">
+select * from (
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE
':' || PE.epoch END)
|| '-' || PA.label package_nvre
@@ -158,6 +163,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
+) X, rhnPackageName PN, rhnPackageEVR PE, rhnPackageArch PA, rhnPackage P,
rhnErrataPackage EP
ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
@@ -331,6 +337,7 @@ SELECT 1 FROM DUAL
</mode>
<query name="newest_packages_in_channel" params="cid">
+select * from (
SELECT DISTINCT CNP.package_id as ID,
CNP.name_id,
CNP.evr_id,
@@ -350,11 +357,13 @@ SELECT DISTINCT CNP.package_id as ID,
AND PN.id = CNP.name_id
AND PE.id = CNP.evr_id
AND PA.id = CNP.package_arch_id
+) X
ORDER BY UPPER(nvre)
</query>
<mode name="latest_packages_in_channel">
<query params="cid">
+select * from (
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
CNP.channel_id AS channel_id,
@@ -377,13 +386,14 @@ SELECT DISTINCT
AND PE.id = CNP.evr_id
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.id = P.package_arch_id
+) X
ORDER BY UPPER(NVREA)
-
</query>
</mode>
<mode name="latest_solaris_packages_in_channel">
<query params="cid">
+select * from (
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
CNP.channel_id AS channel_id,
@@ -406,6 +416,7 @@ SELECT DISTINCT
AND PE.id = CNP.evr_id
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.arch_type_id = CA.arch_type_id
+) X
ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -414,6 +425,7 @@ ORDER BY UPPER(NVREA)
<query params="cid">
<!-- must call the id_combo simply 'id' because listview assumes that
will be the name -->
+select * from (
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
CNP.channel_id AS channel_id,
@@ -430,6 +442,7 @@ SELECT DISTINCT
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.arch_type_id = AT.id
AND AT.label = 'solaris-patch'
+) X
ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -438,6 +451,7 @@ ORDER BY UPPER(NVREA)
<query params="cid">
<!-- must call the id_combo simply 'id' because listview assumes that
will be the name -->
+select * from (
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
CNP.channel_id AS channel_id,
@@ -454,6 +468,7 @@ SELECT DISTINCT
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.arch_type_id = AT.id
AND AT.label = 'solaris-patch-cluster'
+) X
ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -792,6 +807,7 @@ ORDER BY UPPER(NVREA)
<mode name="packages_owned_by_org">
<query params="org_id">
+select * from (
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN ''
ELSE ':' || PE.epoch END)
|| '-' || PA.label package_nvre
@@ -803,6 +819,7 @@ ORDER BY UPPER(NVREA)
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
+) X, rhnPackageName PN, rhnPackageEVR PE, rhnPackageArch PA, rhnPackage P
ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
@@ -810,6 +827,7 @@ ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label,
P.id
<mode name="packages_available_to_errata">
<query params="org_id, eid">
+select * from (
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN ''
ELSE ':' || PE.epoch END)
|| '-' || PA.label PACKAGE_NVRE
@@ -825,6 +843,7 @@ ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label,
P.id
FROM rhnErrataPackage EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
+) X
ORDER BY UPPER(PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
@@ -832,6 +851,7 @@ ORDER BY UPPER(PACKAGE_NVRE)
<mode name="packages_available_to_tmp_errata">
<query params="org_id, eid">
+select * from (
SELECT DISTINCT P.id ID,
PN.name || '-' || PE.version || '-' || PE.release
|| (CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' ||
PE.epoch END)
@@ -848,6 +868,7 @@ ORDER BY UPPER(PACKAGE_NVRE)
FROM rhnErrataPackageTmp EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
+) X
ORDER BY UPPER(PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
@@ -909,6 +930,7 @@ SELECT CP.package_id id, C.name package_channels
<mode name="managed_packages_in_channel">
<query params="channel_id, org_id">
+select * from (
SELECT DISTINCT CP.package_id id, PN.name || '-' || PE.version ||
'-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE
':'
|| PE.epoch END) || '-' || PA.label package_nvre
@@ -923,6 +945,7 @@ SELECT CP.package_id id, C.name package_channels
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
AND P.org_id = :org_id
+) X, rhnPackageName PN, rhnPackageEVR PE, rhnPackageArch PA, rhnPackage P,
rhnErrataPackage EP
ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, CP.package_id
</query>
<elaborator name="package_channels" />
@@ -930,6 +953,7 @@ ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label,
CP.package_
<mode name="packages_in_no_channels_owned_by_org">
<query params="org_id">
+select * from (
SELECT DISTINCT P.id, PN.name || '-' || PE.version || '-' ||
PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' ||
PE.epoch END) || '-' || PA.label package_nvre
@@ -944,12 +968,14 @@ ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label,
CP.package_
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
+) X, rhnPackageName PN, rhnPackageEVR PE, rhnPackageArch PA, rhnPackage P
ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
</mode>
<mode name="packages_available_to_channel">
<query params="source_cid, target_cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PACKAGE_NVREA,
P.summary
@@ -973,6 +999,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
+) X
ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
@@ -980,6 +1007,7 @@ ORDER BY UPPER(PACKAGE_NVREA)
<mode name="solaris_packages_available_to_channel">
<query params="source_cid, target_cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PACKAGE_NVREA,
P.summary
@@ -1005,6 +1033,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
+) X
ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1012,6 +1041,7 @@ ORDER BY UPPER(PACKAGE_NVREA)
<mode name="patches_available_to_channel">
<query params="source_cid, target_cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PATCH_NVREA,
P.summary
@@ -1037,6 +1067,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
+) X
ORDER BY UPPER(PATCH_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1044,6 +1075,7 @@ ORDER BY UPPER(PATCH_NVREA)
<mode name="unused_patches_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCH_NVREA
FROM rhnPackageName PN,
@@ -1064,12 +1096,14 @@ ORDER BY UPPER(PATCH_NVREA)
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
+) X
ORDER BY UPPER(PATCH_NVREA)
</query>
</mode>
<mode name="all_patches_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCH_NVREA
FROM rhnPackageName PN,
@@ -1087,6 +1121,7 @@ ORDER BY UPPER(PATCH_NVREA)
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
+) X
ORDER BY UPPER(PATCH_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1094,6 +1129,7 @@ ORDER BY UPPER(PATCH_NVREA)
<mode name="patchsets_available_to_channel">
<query params="source_cid, target_cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PATCHSET_NVREA,
P.summary
@@ -1119,6 +1155,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
+) X
ORDER BY UPPER(PATCHSET_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1126,6 +1163,7 @@ ORDER BY UPPER(PATCHSET_NVREA)
<mode name="unused_patchsets_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCHSET_NVREA
FROM rhnPackageName PN,
@@ -1146,12 +1184,14 @@ ORDER BY UPPER(PATCHSET_NVREA)
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
+) X
ORDER BY UPPER(PATCHSET_NVREA)
</query>
</mode>
<mode name="all_patchsets_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCHSET_NVREA
FROM rhnPackageName PN,
@@ -1169,6 +1209,7 @@ ORDER BY UPPER(PATCHSET_NVREA)
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
+) X
ORDER BY UPPER(PATCHSET_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1176,6 +1217,7 @@ ORDER BY UPPER(PATCHSET_NVREA)
<mode name="unused_packages_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
@@ -1194,12 +1236,14 @@ ORDER BY UPPER(PATCHSET_NVREA)
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
+) X
ORDER BY UPPER(PACKAGE_NVREA)
</query>
</mode>
<mode name="all_packages_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
@@ -1215,6 +1259,7 @@ ORDER BY UPPER(PACKAGE_NVREA)
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
+) X
ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1222,6 +1267,7 @@ ORDER BY UPPER(PACKAGE_NVREA)
<mode name="unused_solaris_packages_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
@@ -1242,12 +1288,14 @@ ORDER BY UPPER(PACKAGE_NVREA)
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
+) X
ORDER BY UPPER(PACKAGE_NVREA)
</query>
</mode>
<mode name="all_solaris_packages_available_to_channel">
<query params="org_id, cid">
+select * from (
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
@@ -1265,6 +1313,7 @@ ORDER BY UPPER(PACKAGE_NVREA)
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
+) X
ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
@@ -1559,6 +1608,7 @@ ORDER BY CHANNEL_NAME DESC, EVR DESC
<mode name="packages_associated_with_action">
<query params="aid">
+select * from (
SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
@@ -1566,6 +1616,7 @@ SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name ||
'-' || evr_t_as_vre_sim
WHERE AP.action_id = :aid
AND PN.id = AP.name_id
AND PE.id = AP.evr_id
+) X
ORDER BY UPPER(NVRE)
</query>
</mode>
commit c142e84d31b15c68172b01b49f281f3afdc47960
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Fri Oct 29 08:09:20 2010 +0200
Removing unnecessary subselect
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
index 5e72217..3fec969 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
@@ -490,7 +490,6 @@ ORDER BY UPPER(NVRE)
<mode name="package_ids_in_set"
class="com.redhat.rhn.frontend.dto.PackageOverview">
<query params="set_label, user_id">
- select * from (
SELECT P.id AS ID, PN.name || '-' || evr_t_as_vre_simple(PE.evr) ||
'.' || PA.label AS NVREA
FROM rhnPackage P,
rhnPackageName PN,
@@ -503,8 +502,7 @@ ORDER BY UPPER(NVRE)
AND P.name_id = PN.id
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
- ) x
-ORDER BY UPPER(NVREA)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr)), UPPER(PA.label), P.id
</query>
<elaborator name="package_details" />
<elaborator name="package_channels" />
commit 7897b97ae2493d5dd2703ed5ea40c227e3a74b74
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Wed Oct 27 17:19:37 2010 +0200
Simplifying ORDER BY clauses in package queries
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
index 2f5b072..5e72217 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
@@ -263,7 +263,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
</mode>
@@ -283,7 +283,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
</mode>
diff --git a/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
b/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
index 08274ef..83cf36d 100644
--- a/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
+++ b/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
@@ -138,7 +138,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
</mode>
@@ -158,7 +158,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
</mode>
@@ -803,7 +803,7 @@ ORDER BY UPPER(NVREA)
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
<elaborator name="package_channels" />
</mode>
@@ -923,7 +923,7 @@ SELECT CP.package_id id, C.name package_channels
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
AND P.org_id = :org_id
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, CP.package_id
</query>
<elaborator name="package_channels" />
</mode>
@@ -944,7 +944,7 @@ ORDER BY UPPER(PN.name || '-' || PE.version || '-' ||
PE.release || (CASE WHEN P
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
-ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
+ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
</query>
</mode>
commit 8e9711e1761e271100246c014c922a059130fcc1
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Wed Oct 27 17:14:31 2010 +0200
Revert "Reverting "Removed unnecessary ORDER BY" commits and
fixing"
This reverts commit e549a9907132b4622735b6429890fcbd091e715f.
It has been agreed we will fix this by creating a subselect.
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
index 523babc..2f5b072 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
@@ -44,41 +44,41 @@ ORDER BY UPPER(pn.name), full_list.evr
<mode name="package_conflicts">
<query params="pid">
-SELECT DISTINCT C.name, C.version, P.sense, UPPER(C.name) AS name_upper
+SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageConflicts P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY name_upper, C.version
+ORDER BY UPPER(C.name), C.version
</query>
</mode>
<mode name="package_provides">
<query params="pid">
-SELECT DISTINCT C.name, C.version, P.sense, UPPER(C.name) AS name_upper
+SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageProvides P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY name_upper, C.version
+ORDER BY UPPER(C.name), C.version
</query>
</mode>
<mode name="package_obsoletes">
<query params="pid">
-SELECT DISTINCT C.name, C.version, P.sense, UPPER(C.name) AS name_upper
+SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageObsoletes P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY name_upper, C.version
+ORDER BY UPPER(C.name), C.version
</query>
</mode>
<mode name="package_requires">
<query params="pid">
-SELECT DISTINCT C.name, C.version, P.sense, UPPER(C.name) AS name_upper
+SELECT DISTINCT C.name, C.version, P.sense
FROM rhnPackageCapability C, rhnPackageRequires P
WHERE P.package_id = :pid
AND P.capability_id = C.id
-ORDER BY name_upper, C.version
+ORDER BY UPPER(C.name), C.version
</query>
</mode>
@@ -194,7 +194,7 @@ ORDER BY UPPER(C.label)
<mode name="name_by_provide"
class="com.redhat.rhn.frontend.dto.PackageListItem">
<query params="org_id, cap_name">
-SELECT DISTINCT PN.name, UPPER(PN.name) AS name_upper
+SELECT DISTINCT PN.name
FROM rhnPackageName PN,
rhnPackage P,
rhnPackageProvides PP,
@@ -212,13 +212,14 @@ WHERE PC.name = :cap_name
AND CFM.CHANNEL_ID = CP.channel_id
AND CP.package_id = P.id))
AND P.name_id = PN.id
-ORDER BY name_upper
+ORDER BY UPPER(PN.name)
</query>
</mode>
<mode name="name_by_provide_and_channel"
class="com.redhat.rhn.frontend.dto.PackageListItem">
<query params="channel_id, org_id, cap_name">
-SELECT DISTINCT PN.name, UPPER(PN.name) AS name_upper
+SELECT DISTINCT PN.name
+
FROM rhnPackageName PN,
rhnPackage P,
rhnPackageProvides PP,
@@ -243,7 +244,7 @@ WHERE PC.name = :cap_name
AND CFM.CHANNEL_ID = CP.channel_id
AND CP.package_id = P.id))
AND P.name_id = PN.id
-ORDER BY name_upper
+ORDER BY UPPER(PN.name)
</query>
</mode>
@@ -262,7 +263,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
- ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
<elaborator name="package_channels" />
</mode>
@@ -282,7 +283,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name), PE.version, PE.release, PE.epoch, PA.label, P.id
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
<elaborator name="package_channels" />
</mode>
@@ -307,7 +308,7 @@ SELECT PN.id || '|' || PE.id || '|' || PA.id AS
ID_COMBO,
AND P.name_id = PN.id
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -338,7 +339,6 @@ SELECT P.id AS ID,
SELECT DISTINCT
CNP.package_id as id,
PN.name as name,
- UPPER(PN.name) as name_upper,
NVL(PE.version, ' ') as version,
NVL(PE.release, ' ') as release,
NVL(PE.epoch, ' ') as epoch,
@@ -353,7 +353,7 @@ SELECT DISTINCT
AND PN.id = CNP.name_id
AND PE.id = CNP.evr_id
AND PA.id = CNP.package_arch_id
-ORDER BY name_upper
+ORDER BY UPPER(PN.name)
</query>
</mode>
@@ -364,7 +364,6 @@ ORDER BY name_upper
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS nvre,
- UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS nvre_upper,
PN.id || '|' || CNP.evr_id || '|' || PA.id AS id_combo,
(SELECT MAX(summary) FROM rhnPackage P WHERE P.id = CNP.package_id) SUMMARY,
PA.label as arch
@@ -376,7 +375,7 @@ SELECT DISTINCT
AND PN.id = CNP.name_id
AND PE.id = CNP.evr_id
AND PA.id = CNP.PACKAGE_ARCH_ID
-ORDER BY nvre_upper
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -398,7 +397,7 @@ SELECT PN.id || '|' || PE.id || '|' || PA.id AS
ID_COMBO,
INNER JOIN rhnPackageEVR PE on SP.evr_id = PE.id
LEFT JOIN rhnPackageArch PA on SP.PACKAGE_ARCH_ID = PA.id
WHERE SP.server_id = :sid
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -515,8 +514,7 @@ ORDER BY UPPER(NVREA)
<query params="org_id, eid">
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN ''
ELSE ':' || PE.epoch END)
- || '-' || PA.label PACKAGE_NVRE,
- UPPER(PACKAGE_NVRE) AS nvre_upper
+ || '-' || PA.label PACKAGE_NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -532,7 +530,7 @@ SELECT DISTINCT P.id id, PN.name || '-' || PE.version
FROM rhnErrataPackage EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
-ORDER BY nvre_upper
+ORDER BY UPPER(PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
</mode>
@@ -542,8 +540,7 @@ ORDER BY nvre_upper
SELECT DISTINCT P.id ID,
PN.name || '-' || PE.version || '-' || PE.release
|| (CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' ||
PE.epoch END)
- || '-' || PA.label PACKAGE_NVRE,
- UPPER(PACKAGE_NVRE) AS nvre_upper
+ || '-' || PA.label PACKAGE_NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -559,7 +556,7 @@ ORDER BY nvre_upper
FROM rhnErrataPackageTmp EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
-ORDER BY nvre_upper
+ORDER BY UPPER(PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
</mode>
@@ -661,7 +658,6 @@ SELECT distinct pn.name AS NAME,
WHERE SP.server_id = :sid
AND SP.name_id = full_list.name_id
AND (SP.package_arch_id = full_list.arch_id or SP.package_arch_id
is null))
-ORDER BY name_upper
</query>
</mode>
@@ -866,15 +862,14 @@ SELECT CP.package_id as ID,
<mode name="packages_associated_with_action">
<query params="aid">
-SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE,
- UPPER(NVRE) as nvre_upper
+SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnActionPackage AP
WHERE AP.action_id = :aid
AND PN.id = AP.name_id
AND PE.id = AP.evr_id
-ORDER BY nvre_upper
+ORDER BY UPPER(NVRE)
</query>
</mode>
@@ -929,7 +924,7 @@ WHERE SP.server_id = :sid
WHERE PA.id = SP.package_arch_id
AND AT.id = PA.arch_type_id
AND AT.label = 'solaris-patch' )
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
<elaborator params="sid" multiple="t">
SELECT PN.id,
@@ -973,7 +968,7 @@ SELECT PN.id || '|' || PE.id AS ID_COMBO,
AND S.user_id = :user_id
AND S.element = PN.id
AND S.element_two = PE.id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -995,7 +990,7 @@ SELECT PN.id || '|' || PE.id AS ID_COMBO,
INNER JOIN rhnArchType AT ON AT.id = PA.arch_type_id
WHERE SP.server_id = :sid
AND AT.label = 'sysv-solaris'
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -1632,7 +1627,7 @@ SELECT P.id AS ID,
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
AND P.id = SP.package_id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
index fa417ef..bb447c7 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/System_queries.xml
@@ -4,7 +4,6 @@
<query params="user_id, feature">
SELECT DISTINCT S.id,
S.name,
- UPPER(NVL(S.name, '(none)')) name_upper,
SI.server_id AS IS_RHN_SATELLITE,
PI.server_id AS IS_RHN_PROXY,
TO_CHAR(Sinfo.checkin, 'YYYY-MM-DD HH24:MI:SS') AS
LAST_CHECKIN
@@ -15,15 +14,14 @@
AND S.id = USP.server_id
AND Sinfo.server_id = S.id
AND EXISTS(SELECT 1 FROM rhnServerFeaturesView SFV WHERE SFV.server_id = S.ID AND
SFV.label = :feature)
-ORDER BY name_upper, S.id
+ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</query>
</mode>
<mode name="virtual_hosts_for_user"
class="com.redhat.rhn.frontend.dto.SystemOverview">
<query params="user_id">
SELECT DISTINCT S.id,
- S.name,
- UPPER(NVL(S.name, '(none)')) name_upper
+ S.name
FROM rhnServer S,
rhnUserServerPerms USP,
rhnVirtualInstance VI
@@ -32,7 +30,7 @@ ORDER BY name_upper, S.id
AND VI.host_system_id = S.id
AND USP.server_id = S.id
AND USP.user_id = :user_id
-ORDER BY name_upper, S.id
+ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</query>
<elaborator name="system_overview" />
</mode>
@@ -74,7 +72,6 @@ ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
<mode name="ssm_remote_commandable">
<query params="user_id">
SELECT DISTINCT S.id,
- UPPER(NVL(S.name, '(none)')) name_upper,
S.name AS SERVER_NAME
FROM rhnServer S,
rhnClientCapabilityName CCN,
@@ -90,7 +87,7 @@ SELECT DISTINCT S.id,
AND CC.capability_name_id = CCN.id
AND CCN.name = 'script.run'
AND USP.server_id = S.id
-ORDER BY name_upper, S.ID
+ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
</query>
<elaborator name="system_overview" />
</mode>
@@ -117,7 +114,7 @@ SELECT CDK.id,
<query name="ssm_package_upgrades_systems" params="user_id, set_label,
package_set_label">
SELECT DISTINCT
S.id,
- S.name AS SERVER_NAME, UPPER(S.name) AS name_upper
+ S.name AS SERVER_NAME
FROM rhnChannelPackage CP,
rhnChannel C,
rhnServerChannel SC,
@@ -142,7 +139,7 @@ SELECT DISTINCT
AND C.id = CP.channel_id
AND CP.package_id = P.id
AND P.package_arch_id = PA.id
-ORDER BY name_upper
+ORDER BY UPPER(S.name)
</query>
<mode name="ssm_package_upgrades_conf">
@@ -156,8 +153,7 @@ SELECT DISTINCT
P.name_id AS NAME_ID,
P.evr_id AS EVR_ID,
PA.id AS ARCH_ID,
- C.name AS PROVIDING_CHANNEL_NAME,
- UPPER(S.name) AS name_upper, UPPER(PN.name || '-' ||
PE.evr.as_vre_simple() || '-' || PA.label) AS name_evr_upper
+ C.name AS PROVIDING_CHANNEL_NAME
FROM rhnChannelPackage CP,
rhnChannel C,
rhnServerChannel SC,
@@ -183,7 +179,7 @@ SELECT DISTINCT
AND P.name_id = PN.id
AND P.evr_id = PE.id
AND P.package_arch_id = PA.id
-ORDER BY name_upper, name_evr_upper
+ORDER BY UPPER(S.name), UPPER(PN.name || '-' || PE.evr.as_vre_simple() ||
'-' || PA.label)
</elaborator>
</mode>
@@ -407,13 +403,12 @@ SELECT ES.id, S.name, 1 as selectable
<query params="user_id">
SELECT DISTINCT S.id AS ID,
S.name AS NAME,
- UPPER(NVL(S.name, '(none)')) AS name_upper,
INFO.checkin AS LAST_CHECKIN
FROM rhnServer S, rhnUserServerPerms USP, rhnServerInfo INFO
WHERE USP.user_id = :user_id
AND S.id = USP.server_id
AND S.id = INFO.server_id
-ORDER BY name_upper, S.id
+ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</query>
</mode>
@@ -474,7 +469,6 @@ ORDER BY name_upper, S.id
<query name="available_to_uid" params="formvar_uid">
SELECT DISTINCT S.id AS ID,
S.name AS SERVER_NAME,
- UPPER(NVL(S.name, '(none)')) AS name_upper,
(SELECT 1
FROM rhnServerFeaturesView SFV
WHERE SFV.server_id = S.id
@@ -482,7 +476,7 @@ ORDER BY name_upper, S.id
FROM rhnServer S, rhnUserServerPerms USP
WHERE USP.user_id = :formvar_uid
AND S.id = USP.server_id
-ORDER BY name_upper, S.id
+ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</query>
@@ -537,7 +531,6 @@ where a.user_id = :formvar_uid
<query params="eid, user_id">
SELECT DISTINCT S.id as system_id,
S.name as system_name,
- UPPER(NVL(S.name, '(none)')) AS name_upper,
S.id as id,
S.name as name
FROM rhnServerNeededErrataCache SNEC,
@@ -547,7 +540,7 @@ where a.user_id = :formvar_uid
AND USP.user_id = :user_id
AND S.id = USP.server_id
AND S.id = SNEC.server_id
-ORDER BY name_upper, S.id
+ORDER BY UPPER(NVL(S.name, '(none)')), S.id
</query>
</mode>
@@ -614,8 +607,7 @@ ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
<query name="system_set_remove_package_query" params="user_id,
set_label, package_set_label">
SELECT DISTINCT S.id,
- S.name SYSTEM_NAME,
- UPPER(S.name) AS name_upper
+ S.name SYSTEM_NAME
FROM rhnServer S,
rhnServerPackage SP,
rhnSet ST2
@@ -626,7 +618,7 @@ ORDER BY UPPER(NVL(S.NAME, '(none)')), S.ID
AND SP.evr_id = ST2.element_two
AND (SP.package_arch_id = ST2.element_three OR (SP.package_arch_id is null and
ST2.element_three is null))
AND SP.server_id = S.id
- ORDER BY name_upper, S.id
+ ORDER BY UPPER(S.name), S.id
</query>
@@ -1291,7 +1283,7 @@ ORDER BY NAME, VI.virtual_system_id, VI.uuid DESC
</mode>
<query name="system_package_data_from_set" params="user_id,
package_set_label">
-SELECT DISTINCT S.id SYSTEM_ID, S.name SYSTEM_NAME, UPPER(S.name) AS name_upper, PN.name
|| '-' || PE.evr.as_vre_simple() NVRE
+SELECT DISTINCT S.id SYSTEM_ID, S.name SYSTEM_NAME, PN.name || '-' ||
PE.evr.as_vre_simple() NVRE
FROM rhnServer S,
rhnPackageEVR PE,
rhnPackageName PN,
@@ -1301,7 +1293,7 @@ SELECT DISTINCT S.id SYSTEM_ID, S.name SYSTEM_NAME, UPPER(S.name)
AS name_upper
AND PN.id IN (SELECT element FROM rhnSet WHERE user_id = :user_id AND label =
:package_set_label)
AND SP.evr_id = PE.id
AND SP.server_id = S.id
-ORDER BY name_upper
+ORDER BY UPPER(S.name)
</query>
<mode name="system_installed_packages">
diff --git a/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
b/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
index a864e47..08274ef 100644
--- a/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
+++ b/web/modules/rhn/RHN/DB/DataSource/xml/Package_queries.xml
@@ -2,7 +2,7 @@
<mode name="name_by_provide">
<query params="org_id, cap_name">
-SELECT DISTINCT PN.name, UPPER(PN.name) AS name_upper
+SELECT DISTINCT PN.name
FROM rhnPackageName PN,
rhnPackage P,
rhnPackageProvides PP,
@@ -18,7 +18,7 @@ WHERE PC.name = :cap_name
AND AC.channel_id = CP.channel_id
AND CP.package_id = P.id))
AND P.name_id = PN.id
-ORDER BY name_upper
+ORDER BY UPPER(PN.name)
</query>
</mode>
@@ -127,8 +127,7 @@ ORDER BY UPPER(PACKAGE)
<query params="eid">
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE
':' || PE.epoch END)
- || '-' || PA.label package_nvre,
- UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label) AS nvre_upper
+ || '-' || PA.label package_nvre
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -139,7 +138,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY nvre_upper
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
<elaborator name="package_channels" />
</mode>
@@ -148,8 +147,7 @@ ORDER BY nvre_upper
<query params="eid">
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE
':' || PE.epoch END)
- || '-' || PA.label package_nvre,
- UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label) AS nvre_upper
+ || '-' || PA.label package_nvre
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -160,7 +158,7 @@ WHERE EP.errata_id = :eid
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY nvre_upper
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
<elaborator name="package_channels" />
</mode>
@@ -184,7 +182,7 @@ SELECT PN.id || '|' || PE.id AS ID,
AND P.name_id = PN.id
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -211,7 +209,7 @@ SELECT P.id AS ID,
AND P.name_id = PN.id
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -236,7 +234,7 @@ SELECT P.id AS ID,
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
AND P.id = SP.package_id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -263,7 +261,7 @@ SELECT P.id AS ID,
AND PA.id = P.package_arch_id
AND P.id = SP.package_id
AND SP.patch_type = SPT.id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -289,7 +287,7 @@ SELECT P.id AS ID,
AND P.evr_id = PE.id
AND PA.id = P.package_arch_id
AND P.id = SP.package_id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -341,8 +339,7 @@ SELECT DISTINCT CNP.package_id as ID,
PE.version,
PE.release,
PA.label AS arch,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) nvre,
- UPPER(nvre) AS nvre_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) nvre
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -353,14 +350,13 @@ SELECT DISTINCT CNP.package_id as ID,
AND PN.id = CNP.name_id
AND PE.id = CNP.evr_id
AND PA.id = CNP.package_arch_id
-ORDER BY nvre_upper
+ORDER BY UPPER(nvre)
</query>
<mode name="latest_packages_in_channel">
<query params="cid">
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
- UPPER(nvrea) AS nvrea_upper,
CNP.channel_id AS channel_id,
P.id as ID,
P.SUMMARY as SUMMARY
@@ -381,7 +377,7 @@ SELECT DISTINCT
AND PE.id = CNP.evr_id
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.id = P.package_arch_id
-ORDER BY nvrea_upper
+ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -390,7 +386,6 @@ ORDER BY nvrea_upper
<query params="cid">
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
- UPPER(nvrea) AS nvrea_upper,
CNP.channel_id AS channel_id,
P.id,
P.SUMMARY as SUMMARY
@@ -411,7 +406,7 @@ SELECT DISTINCT
AND PE.id = CNP.evr_id
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.arch_type_id = CA.arch_type_id
-ORDER BY nvrea_upper
+ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -421,7 +416,6 @@ ORDER BY nvrea_upper
will be the name -->
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
- UPPER(nvrea) AS nvrea_upper,
CNP.channel_id AS channel_id,
PN.id || '|' || CNP.evr_id AS id,
(SELECT MAX(summary) FROM rhnPackage P WHERE P.id = CNP.package_id) SUMMARY
@@ -436,7 +430,7 @@ SELECT DISTINCT
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.arch_type_id = AT.id
AND AT.label = 'solaris-patch'
-ORDER BY nvrea_upper
+ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -446,7 +440,6 @@ ORDER BY nvrea_upper
will be the name -->
SELECT DISTINCT
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
nvrea,
- UPPER(nvrea) AS nvrea_upper,
CNP.channel_id AS channel_id,
PN.id || '|' || CNP.evr_id AS id,
(SELECT MAX(summary) FROM rhnPackage P WHERE P.id = CNP.package_id) SUMMARY
@@ -461,7 +454,7 @@ SELECT DISTINCT
AND PA.id = CNP.PACKAGE_ARCH_ID
AND PA.arch_type_id = AT.id
AND AT.label = 'solaris-patch-cluster'
-ORDER BY nvrea_upper
+ORDER BY UPPER(NVREA)
</query>
</mode>
@@ -481,7 +474,7 @@ SELECT PN.id || '|' || PE.id AS ID,
AND SP.name_id = PN.id
AND SP.evr_id = PE.id
AND SP.package_arch_id = PA.id
-ORDER BY UPPER(PN.name), evr_t_as_vre_simple(PE.evr)
+ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
</query>
</mode>
@@ -801,8 +794,7 @@ ORDER BY UPPER(NVREA)
<query params="org_id">
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN ''
ELSE ':' || PE.epoch END)
- || '-' || PA.label package_nvre,
- UPPER(PN.name || '-' || PE.version || '-' || PE.release ||
(CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) ||
'-' || PA.label) AS name_upper
+ || '-' || PA.label package_nvre
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -811,7 +803,7 @@ ORDER BY UPPER(NVREA)
AND PN.id = P.name_id
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
-ORDER BY name_upper
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
<elaborator name="package_channels" />
</mode>
@@ -820,8 +812,7 @@ ORDER BY name_upper
<query params="org_id, eid">
SELECT DISTINCT P.id id, PN.name || '-' || PE.version
|| '-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN ''
ELSE ':' || PE.epoch END)
- || '-' || PA.label PACKAGE_NVRE,
- UPPER(PACKAGE_NVRE) AS nvre_upper
+ || '-' || PA.label PACKAGE_NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -834,7 +825,7 @@ ORDER BY name_upper
FROM rhnErrataPackage EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
-ORDER BY nvre_upper
+ORDER BY UPPER(PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
</mode>
@@ -844,8 +835,7 @@ ORDER BY nvre_upper
SELECT DISTINCT P.id ID,
PN.name || '-' || PE.version || '-' || PE.release
|| (CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' ||
PE.epoch END)
- || '-' || PA.label PACKAGE_NVRE,
- UPPER(PACKAGE_NVRE) AS nvre_upper
+ || '-' || PA.label PACKAGE_NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -858,7 +848,7 @@ ORDER BY nvre_upper
FROM rhnErrataPackageTmp EP
WHERE EP.errata_id = :eid
AND EP.package_id = P.id)
-ORDER BY nvre_upper
+ORDER BY UPPER(PACKAGE_NVRE)
</query>
<elaborator name="package_channels" />
</mode>
@@ -921,8 +911,7 @@ SELECT CP.package_id id, C.name package_channels
<query params="channel_id, org_id">
SELECT DISTINCT CP.package_id id, PN.name || '-' || PE.version ||
'-' || PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE
':'
- || PE.epoch END) || '-' || PA.label package_nvre,
- UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE WHEN
PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label) AS nvre_upper
+ || PE.epoch END) || '-' || PA.label package_nvre
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -934,7 +923,7 @@ SELECT CP.package_id id, C.name package_channels
AND PE.id = P.evr_id
AND PA.id = P.package_arch_id
AND P.org_id = :org_id
-ORDER BY nvre_upper
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
<elaborator name="package_channels" />
</mode>
@@ -943,8 +932,7 @@ ORDER BY nvre_upper
<query params="org_id">
SELECT DISTINCT P.id, PN.name || '-' || PE.version || '-' ||
PE.release || (CASE WHEN PE.epoch IS NULL THEN '' ELSE ':' ||
- PE.epoch END) || '-' || PA.label package_nvre,
- UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE WHEN
PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label) AS nvre_upper
+ PE.epoch END) || '-' || PA.label package_nvre
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -956,7 +944,7 @@ ORDER BY nvre_upper
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
-ORDER BY nvre_upper
+ORDER BY UPPER(PN.name || '-' || PE.version || '-' || PE.release || (CASE
WHEN PE.epoch IS NULL THEN '' ELSE ':' || PE.epoch END) || '-' ||
PA.label)
</query>
</mode>
@@ -964,7 +952,6 @@ ORDER BY nvre_upper
<query params="source_cid, target_cid">
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PACKAGE_NVREA,
- UPPER(PACKAGE_NVREA) as nvrea_upper,
P.summary
FROM rhnPackageName PN,
rhnPackageEVR PE,
@@ -986,7 +973,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -995,7 +982,6 @@ ORDER BY nvrea_upper
<query params="source_cid, target_cid">
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PACKAGE_NVREA,
- UPPER(PACKAGE_NVREA) as nvrea_upper,
P.summary
FROM rhnPackageName PN,
rhnPackageEVR PE,
@@ -1019,7 +1005,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1028,7 +1014,6 @@ ORDER BY nvrea_upper
<query params="source_cid, target_cid">
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PATCH_NVREA,
- UPPER(PATCH_NVREA) as nvrea_upper,
P.summary
FROM rhnPackageName PN,
rhnPackageEVR PE,
@@ -1052,7 +1037,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PATCH_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1060,8 +1045,7 @@ ORDER BY nvrea_upper
<mode name="unused_patches_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCH_NVREA,
- UPPER(PATCH_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCH_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1080,15 +1064,14 @@ ORDER BY nvrea_upper
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PATCH_NVREA)
</query>
</mode>
<mode name="all_patches_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCH_NVREA,
- UPPER(PATCH_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCH_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1104,7 +1087,7 @@ ORDER BY nvrea_upper
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
-ORDER BY nvrea_upper
+ORDER BY UPPER(PATCH_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1113,7 +1096,6 @@ ORDER BY nvrea_upper
<query params="source_cid, target_cid">
SELECT DISTINCT P.id,
PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '.' || PA.label
AS PATCHSET_NVREA,
- UPPER(PATCHSET_NVREA) as nvrea_upper,
P.summary
FROM rhnPackageName PN,
rhnPackageEVR PE,
@@ -1137,7 +1119,7 @@ SELECT DISTINCT P.id,
FROM rhnChannelPackage CP2
WHERE CP2.channel_id = C2.id
AND CP2.package_id = P.id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PATCHSET_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1145,8 +1127,7 @@ ORDER BY nvrea_upper
<mode name="unused_patchsets_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCHSET_NVREA,
- UPPER(PATCHSET_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCHSET_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1165,15 +1146,14 @@ ORDER BY nvrea_upper
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PATCHSET_NVREA)
</query>
</mode>
<mode name="all_patchsets_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCHSET_NVREA,
- UPPER(PATCHSET_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PATCHSET_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1189,7 +1169,7 @@ ORDER BY nvrea_upper
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
-ORDER BY nvrea_upper
+ORDER BY UPPER(PATCHSET_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1197,8 +1177,7 @@ ORDER BY nvrea_upper
<mode name="unused_packages_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA,
- UPPER(PACKAGE_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1215,15 +1194,14 @@ ORDER BY nvrea_upper
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PACKAGE_NVREA)
</query>
</mode>
<mode name="all_packages_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA,
- UPPER(PACKAGE_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1237,7 +1215,7 @@ ORDER BY nvrea_upper
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
-ORDER BY nvrea_upper
+ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1245,8 +1223,7 @@ ORDER BY nvrea_upper
<mode name="unused_solaris_packages_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA,
- UPPER(PACKAGE_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1265,15 +1242,14 @@ ORDER BY nvrea_upper
AND NOT EXISTS (SELECT 1
FROM rhnChannelPackage CP
WHERE P.id = CP.package_id)
-ORDER BY nvrea_upper
+ORDER BY UPPER(PACKAGE_NVREA)
</query>
</mode>
<mode name="all_solaris_packages_available_to_channel">
<query params="org_id, cid">
SELECT DISTINCT P.id,
- PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA,
- UPPER(PACKAGE_NVREA) as nvrea_upper
+ PN.name || '-' || evr_t_as_vre_simple(PE.evr) || '-' || PA.label
PACKAGE_NVREA
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnPackageArch PA,
@@ -1289,7 +1265,7 @@ ORDER BY nvrea_upper
AND C.id = :cid
AND CPAC.channel_arch_id = C.channel_arch_id
AND CPAC.package_arch_id = P.package_arch_id
-ORDER BY nvrea_upper
+ORDER BY UPPER(PACKAGE_NVREA)
</query>
<elaborator name="package_channels" />
</mode>
@@ -1583,14 +1559,14 @@ ORDER BY CHANNEL_NAME DESC, EVR DESC
<mode name="packages_associated_with_action">
<query params="aid">
-SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE, UPPER(NVRE) AS nvre_upper
+SELECT DISTINCT PN.id || '|' || PE.id AS ID, PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE
FROM rhnPackageName PN,
rhnPackageEVR PE,
rhnActionPackage AP
WHERE AP.action_id = :aid
AND PN.id = AP.name_id
AND PE.id = AP.evr_id
-ORDER BY nvre_upper
+ORDER BY UPPER(NVRE)
</query>
</mode>