This | 79 ++-------- rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/common/views/rhnServerNeededView.sql | 2 schema/spacewalk/spacewalk-schema.spec | 6 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/002-rhnServerNeededView.sql | 31 +++ 5 files changed, 60 insertions(+), 60 deletions(-)
New commits: commit 1a80311ee8f169d31d0ee9a6385ff7ac3177c44f Author: Tomas Lestach tlestach@redhat.com Date: Thu Aug 11 11:33:32 2011 +0200
Automatic commit of package [spacewalk-schema] release [1.6.4-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index 8e1063a..0b95ca4 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -1.6.3-1 schema/spacewalk/ +1.6.4-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index 9d65a0b..e93b306 100644 --- a/schema/spacewalk/spacewalk-schema.spec +++ b/schema/spacewalk/spacewalk-schema.spec @@ -2,7 +2,7 @@ Name: spacewalk-schema Group: Applications/Internet Summary: Oracle SQL schema for Spacewalk server
-Version: 1.6.3 +Version: 1.6.4 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz
@@ -66,6 +66,10 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql*
%changelog +* Thu Aug 11 2011 Tomas Lestach tlestach@redhat.com 1.6.4-1 +- 722189 - rewrite rhnServerNeededView to reflect all available errata + (tlestach@redhat.com) + * Wed Aug 10 2011 Jan Pazdziora 1.6.3-1 - We missed ON DELETE CASCADE in the past schema upgrade scripts -- fixing now.
commit 0679ccfd39e5a3118c9eac10e33d8407bd3cfc92 Author: Tomas Lestach tlestach@redhat.com Date: Wed Aug 10 16:44:53 2011 +0200
722189 - rewrite rhnServerNeededView to reflect all available errata
not only those with associated latest pacakages
diff --git a/schema/spacewalk/common/views/rhnServerNeededView.sql b/schema/spacewalk/common/views/rhnServerNeededView.sql index ee10e96..3655574 100644 --- a/schema/spacewalk/common/views/rhnServerNeededView.sql +++ b/schema/spacewalk/common/views/rhnServerNeededView.sql @@ -1,5 +1,5 @@ -- --- Copyright (c) 2010 Red Hat, Inc. +-- Copyright (c) 2008-2011 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 @@ -14,10 +14,9 @@ --
--- A view that displays packages which can be updated on selected client. --- This is an uncached version of rhnServerNeededCache. +-- A view that displays an uncached version of rhnServerNeededCache
-create or replace view +CREATE OR REPLACE VIEW rhnServerNeededView ( org_id, @@ -27,60 +26,24 @@ rhnServerNeededView package_name_id, channel_id ) -as -select s.org_id, - s.id as server_id, - pce.errata_id, - pkg.id as package_id, - neededpkg.name_id as package_name_id, - scp.min_channel_id as channel_id - from (select sc.server_id, np.name_id, np.package_arch_id, max(np.evr) max_evr - from (-- list of newest packages in channels with EVR - select np_np.*, np_pe.evr - from rhnChannelNewestPackage np_np - join rhnPackageEVR np_pe - on np_pe.id = np_np.evr_id) np - join (-- list of packages on the server with EVR - select sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id, max(sp_pe.evr) as max_evr - from rhnServerPackage sp_sp - join rhnPackageEVR sp_pe - on sp_pe.id = sp_sp.evr_id - group by sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id) sp - on -- at first - we want only newer (=higher EVR) packages than there are on the server - sp.name_id = np.name_id and sp.max_evr < np.evr - join -- secondly - packages must be upgrade compatible - rhnPackageUpgradeArchCompat puac - on puac.package_arch_id = sp.package_arch_id and puac.package_upgrade_arch_id = np.package_arch_id - join -- thirdly - packages must be in channel where server is subscribed to - rhnServerChannel sc - on sc.server_id = sp.server_id and sc.channel_id = np.channel_id - group by sc.server_id, np.name_id, np.package_arch_id - ) neededpkg - join -- lookup org_id by server - rhnServer s - on neededpkg.server_id = s.id - join (--lookup package_id by max_evr, name and package_arch - select p.*, p_evr.evr - -- - , p_evr.release, p_evr.version, p_evr.epoch - from rhnPackage p - join rhnPackageEVR p_evr - on p_evr.id = p.evr_id) pkg - on pkg.evr = neededpkg.max_evr - and pkg.name_id = neededpkg.name_id - and pkg.package_arch_id = neededpkg.package_arch_id - join (-- lookup channel_id - we want only one id eve if package is in more channels - -- so pick lowest one - select cp.package_id, csc.server_id, min(cp.channel_id) as min_channel_id - from rhnChannelPackage cp - join rhnServerChannel csc - on csc.channel_id = cp.channel_id - group by cp.package_id, csc.server_id) scp - on scp.package_id = pkg.id and scp.server_id = neededpkg.server_id - left join (-- lookup errata id (can be NULL) - select ep.package_id, ce.errata_id, ce.channel_id - from rhnErrataPackage ep - join rhnChannelErrata ce - on ce.errata_id = ep.errata_id) pce - on pce.package_id = pkg.id and pce.channel_id = scp.min_channel_id +AS +SELECT s.org_id, + sp.server_id, + x.errata_id, + up.id, + up.name_id, + x.channel_id + FROM rhnServer s + join rhnServerPackage sp ON sp.server_id = s.id + join rhnPackageEvr pe ON pe.id = sp.evr_id + join rhnPackage up ON up.name_id = sp.name_id + join rhnPackageEvr upe ON upe.id = up.evr_id AND pe.evr < upe.evr + join rhnPackageUpgradeArchCompat puac ON puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = up.package_arch_id + join rhnServerChannel sc ON sc.server_id = sp.server_id + join rhnChannelPackage cp ON cp.package_id = up.id AND cp.channel_id = sc.channel_id + left join + (SELECT ep.errata_id, cp.channel_id, ep.package_id + FROM rhnChannelErrata cp + join rhnErrataPackage ep ON ep.errata_id = cp.errata_id) x + ON x.channel_id = sc.channel_id AND x.package_id = cp.package_id ; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/002-rhnServerNeededView.sql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/002-rhnServerNeededView.sql new file mode 100644 index 0000000..5223497 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/002-rhnServerNeededView.sql @@ -0,0 +1,31 @@ +CREATE OR REPLACE VIEW +rhnServerNeededView +( + org_id, + server_id, + errata_id, + package_id, + package_name_id, + channel_id +) +AS +SELECT s.org_id, + sp.server_id, + x.errata_id, + up.id, + up.name_id, + x.channel_id + FROM rhnServer s + join rhnServerPackage sp ON sp.server_id = s.id + join rhnPackageEvr pe ON pe.id = sp.evr_id + join rhnPackage up ON up.name_id = sp.name_id + join rhnPackageEvr upe ON upe.id = up.evr_id AND pe.evr < upe.evr + join rhnPackageUpgradeArchCompat puac ON puac.package_arch_id = sp.package_arch_id AND puac.package_upgrade_arch_id = up.package_arch_id + join rhnServerChannel sc ON sc.server_id = sp.server_id + join rhnChannelPackage cp ON cp.package_id = up.id AND cp.channel_id = sc.channel_id + left join + (SELECT ep.errata_id, cp.channel_id, ep.package_id + FROM rhnChannelErrata cp + join rhnErrataPackage ep ON ep.errata_id = cp.errata_id) x + ON x.channel_id = sc.channel_id AND x.package_id = cp.package_id +;
spacewalk-commits@lists.fedorahosted.org