java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
| 32 ++-
schema/spacewalk/common/views/rhnServerNeededView.sql
| 32 +++
schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/002-rhnServerNeededView.sql
| 81 ++++++++++
3 files changed, 132 insertions(+), 13 deletions(-)
New commits:
commit 68ff7beacd9da5ab561393d46faf233528fb4d04
Author: Justin Sherrill <jsherril(a)redhat.com>
Date: Thu May 20 11:13:54 2010 -0400
585965 - adding upgrade schema for bug
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/002-rhnServerNeededView.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/002-rhnServerNeededView.sql
new file mode 100644
index 0000000..c0f3555
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/002-rhnServerNeededView.sql
@@ -0,0 +1,81 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+
+
+-- A view that displays an uncached version of rhnServerNeededCache
+
+CREATE OR REPLACE VIEW
+rhnServerNeededView
+(
+ org_id,
+ server_id,
+ errata_id,
+ package_id,
+ package_name_id,
+ channel_id
+)
+AS
+SELECT DISTINCT S.org_id,
+ S.id as server_id,
+ ep.errata_id as errata_id,
+ P.id as package_id,
+ P.name_id as package_name_id,
+ CP.channel_id as channel_id
+FROM
+ rhnPackage P
+ inner join rhnPackageEVR P_EVR on P_EVR.id = P.evr_id
+ inner join rhnPackageEVR SP_EVR on SP_EVR.evr < P_EVR.evr
+ inner join rhnServerPackage SP on SP.name_id = P.name_id
+ and SP.evr_id = SP_EVR.id
+ AND SP.evr_id != P.evr_id
+ inner join rhnServer S on SP.server_id = S.id
+ inner join rhnServerPackageArchCompat SPAC on spac.server_arch_id = s.server_arch_id
+ AND p.package_arch_id = spac.package_arch_id
+ inner join rhnServerChannel SC on SC.server_id = S.id
+ inner join rhnChannelPackage CP on CP.package_id = P.id
+ and SC.channel_id = CP.channel_id
+ left outer join rhnErrataPackage EP on EP.package_id = P.id
+ AND EXISTS
+ (SELECT 1 from rhnChannelErrata CE where ce.channel_id =
SC.channel_id
+ AND CE.errata_id = EP.errata_id)
+ where
+ --- If the channel has more than 1 package with the same NVRE but different
arches
+ --- Then we need to add an additional join condition (the server's
package arch id)
+ P.package_arch_id = NVL2(
+ (select distinct 1
+ from rhnPackage P2 inner join rhnChannelPackage
CP2
+ on P2.id = CP2.package_id
+ where CP2.channel_id = SC.channel_id and
+ P2.id = CP2.package_id and
+ P2.name_id = P.name_id
+ group by P2.evr_id having count(*) > 1),
+ NVL(SP.package_arch_id, P.package_arch_id),
+ P.package_arch_id
+ )
+ AND
+ --- If we can use arch to find the MAX EVR, use that
+ --- Otherwise just use whatever they have
+ SP_EVR.evr =
+ NVL(
+ (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE
+ WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND
+ SP2.name_id = SP.name_id
+ AND NVL(SP2.package_arch_id, P.package_arch_id) =
P.package_arch_id
+ ),
+ (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE
+ WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND
+ SP2.name_id = SP.name_id)
+ )
+/
commit 7ff5c9d737d679513732e0b570e3ed8b5147aaed
Author: Justin Sherrill <jsherril(a)redhat.com>
Date: Thu May 20 11:12:25 2010 -0400
585965 - fixing issue with multilib packages and errata-cache generation, where
updating one arch of a package would indicate that the other one was updated as well
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 fae7400..3a00d2f 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
@@ -758,20 +758,32 @@ SELECT PN.id AS id,
INNER JOIN rhnPackageEVR PE on SP.evr_id = PE.id
LEFT JOIN rhnPackageArch PA on SP.package_arch_id = PA.id,
(
- SELECT SOP.package_name_id AS name_id, MAX(PE.evr) evr,
- SP.package_arch_id as arch_id, PA.label as arch_label
- FROM rhnServerOutdatedPackages SOP
- INNER JOIN rhnServerPackage SP on SP.server_id = SOP.server_id
- INNER JOIN rhnPackageEVR PE on SOP.package_evr_id = PE.id
- LEFT JOIN rhnPackageArch PA on PA.id = SP.package_arch_id
- WHERE SOP.server_id = :sid
- AND SOP.package_name_id = SP.name_id
- GROUP BY SOP.package_name_id, SP.package_arch_id, PA.label
+ SELECT P.name_id AS name_id, MAX(PE.evr) evr,
+ P.package_arch_id as arch_id, PA.label as arch_label
+ FROM rhnServerNeededCache SNC inner join
+ rhnPackage P on SNC.package_Id = P.id inner join
+ rhnPackageEVR PE on P.evr_id = PE.id inner join
+ rhnPackageArch PA on PA.id = P.package_arch_id
+ WHERE SNC.server_id = :sid
+ GROUP BY P.name_id, P.package_arch_id, PA.label
) latest
WHERE latest.name_id = PN.id
AND SP.server_id = :sid
AND SP.name_id = PN.id
- AND (SP.package_arch_id IS NULL OR SP.package_arch_id = latest.arch_id)
+ AND (SP.package_arch_id IS NULL OR SP.package_arch_id =
+ -- If the server has more than one package of this name installed
+ -- Then group by arch, otherwise just use whever is there
+ NVL2(
+ (select 1 from rhnServerPackage sp2
+ where
+ sp2.name_id = latest.name_id and
+ sp2.server_id = :sid
+ group by sp2.name_id having count(*) > 1
+ ),
+ latest.arch_id,
+ SP.package_arch_id)
+
+ )
ORDER BY UPPER(PN.name)
</query>
<elaborator multiple="t" params="sid">
diff --git a/schema/spacewalk/common/views/rhnServerNeededView.sql
b/schema/spacewalk/common/views/rhnServerNeededView.sql
index 7baaa14..c0f3555 100644
--- a/schema/spacewalk/common/views/rhnServerNeededView.sql
+++ b/schema/spacewalk/common/views/rhnServerNeededView.sql
@@ -50,6 +50,32 @@ FROM
AND EXISTS
(SELECT 1 from rhnChannelErrata CE where ce.channel_id =
SC.channel_id
AND CE.errata_id = EP.errata_id)
- where SP_EVR.evr = (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE
- WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND
- SP2.name_id = SP.name_id);
+ where
+ --- If the channel has more than 1 package with the same NVRE but different
arches
+ --- Then we need to add an additional join condition (the server's
package arch id)
+ P.package_arch_id = NVL2(
+ (select distinct 1
+ from rhnPackage P2 inner join rhnChannelPackage
CP2
+ on P2.id = CP2.package_id
+ where CP2.channel_id = SC.channel_id and
+ P2.id = CP2.package_id and
+ P2.name_id = P.name_id
+ group by P2.evr_id having count(*) > 1),
+ NVL(SP.package_arch_id, P.package_arch_id),
+ P.package_arch_id
+ )
+ AND
+ --- If we can use arch to find the MAX EVR, use that
+ --- Otherwise just use whatever they have
+ SP_EVR.evr =
+ NVL(
+ (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE
+ WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND
+ SP2.name_id = SP.name_id
+ AND NVL(SP2.package_arch_id, P.package_arch_id) =
P.package_arch_id
+ ),
+ (SELECT MAX(PE.evr) FROM rhnServerPackage SP2, rhnPackageEvr PE
+ WHERE PE.id = SP2.evr_id AND SP2.server_id = SP.server_id AND
+ SP2.name_id = SP.name_id)
+ )
+/