rel-eng/packages/spacewalk-schema
| 2
schema/spacewalk/common/views/rhnServerNeededView.sql
| 13 +-
schema/spacewalk/spacewalk-schema.spec
| 6 +
schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/016-rhnServerNeededView.sql
| 48 ++++++++++
4 files changed, 59 insertions(+), 10 deletions(-)
New commits:
commit bd523c95573faaf1d44463e76714d0e387201cf5
Author: Stephen Herr <sherr(a)redhat.com>
Date: Thu Aug 29 14:00:11 2013 -0400
Automatic commit of package [spacewalk-schema] release [2.1.14-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema
index c86cc3b..1d06682 100644
--- a/rel-eng/packages/spacewalk-schema
+++ b/rel-eng/packages/spacewalk-schema
@@ -1 +1 @@
-2.1.13-1 schema/spacewalk/
+2.1.14-1 schema/spacewalk/
diff --git a/schema/spacewalk/spacewalk-schema.spec
b/schema/spacewalk/spacewalk-schema.spec
index 12d1d6e..cf10118 100644
--- a/schema/spacewalk/spacewalk-schema.spec
+++ b/schema/spacewalk/spacewalk-schema.spec
@@ -4,7 +4,7 @@ Name: spacewalk-schema
Group: Applications/Internet
Summary: Oracle SQL schema for Spacewalk server
-Version: 2.1.13
+Version: 2.1.14
Release: 1%{?dist}
Source0: %{name}-%{version}.tar.gz
@@ -71,6 +71,10 @@ rm -rf $RPM_BUILD_ROOT
%{_mandir}/man1/spacewalk-sql*
%changelog
+* Thu Aug 29 2013 Stephen Herr <sherr(a)redhat.com> 2.1.14-1
+- 999453 - update rhnServerNeededView to make it easier to optimize for fast
+ execution
+
* Wed Aug 21 2013 Stephen Herr <sherr(a)redhat.com> 2.1.13-1
- 998424 - removing bad initial condition from max(evr_t)
- Revert "998424 - rpm version comparison function was broken for alphanumeric
commit a1f165cd894dd4d5affbc3d62ae49e034f979a6b
Author: Stephen Herr <sherr(a)redhat.com>
Date: Thu Aug 29 13:59:45 2013 -0400
999453 - update rhnServerNeededView to make it easier to optimize for fast execution
diff --git a/schema/spacewalk/common/views/rhnServerNeededView.sql
b/schema/spacewalk/common/views/rhnServerNeededView.sql
index fdeb694..867eca3 100644
--- a/schema/spacewalk/common/views/rhnServerNeededView.sql
+++ b/schema/spacewalk/common/views/rhnServerNeededView.sql
@@ -27,12 +27,12 @@ rhnServerNeededView
channel_id
)
AS
-SELECT s.org_id,
+SELECT DISTINCT s.org_id,
sp.server_id,
- x.errata_id,
+ ce.errata_id,
up.id,
up.name_id,
- x.channel_id
+ ce.channel_id
FROM rhnServer s
join (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
@@ -43,9 +43,6 @@ SELECT s.org_id,
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
+ left join rhnErrataPackage ep ON cp.package_id = ep.package_id
+ left join rhnChannelErrata ce ON sc.channel_id = ce.channel_id AND ep.errata_id =
ce.errata_id
;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/016-rhnServerNeededView.sql
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/016-rhnServerNeededView.sql
new file mode 100644
index 0000000..867eca3
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/016-rhnServerNeededView.sql
@@ -0,0 +1,48 @@
+--
+-- 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
+-- 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,
+ sp.server_id,
+ ce.errata_id,
+ up.id,
+ up.name_id,
+ ce.channel_id
+ FROM rhnServer s
+ join (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
sp.server_id = s.id
+ join rhnPackage up ON up.name_id = sp.name_id
+ join rhnPackageEvr upe ON upe.id = up.evr_id AND sp.max_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 rhnErrataPackage ep ON cp.package_id = ep.package_id
+ left join rhnChannelErrata ce ON sc.channel_id = ce.channel_id AND ep.errata_id =
ce.errata_id
+;