rel-eng/packages/spacewalk-schema
| 2
schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
| 58 +++++-----
schema/spacewalk/spacewalk-schema.spec
| 7 +
schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
| 56 +++++++++
4 files changed, 96 insertions(+), 27 deletions(-)
New commits:
commit 79b24325ec84a00b6b2591afc7a3cf8d60fbbda1
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 10 14:10:52 2012 +0200
Automatic commit of package [spacewalk-schema] release [1.8.70-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema
index 8f298a9..0128278 100644
--- a/rel-eng/packages/spacewalk-schema
+++ b/rel-eng/packages/spacewalk-schema
@@ -1 +1 @@
-1.8.69-1 schema/spacewalk/
+1.8.70-1 schema/spacewalk/
diff --git a/schema/spacewalk/spacewalk-schema.spec
b/schema/spacewalk/spacewalk-schema.spec
index 72a2c47..edcbd0f 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.8.69
+Version: 1.8.70
Release: 1%{?dist}
Source0: %{name}-%{version}.tar.gz
@@ -68,6 +68,11 @@ rm -rf $RPM_BUILD_ROOT
%{_mandir}/man1/spacewalk-sql*
%changelog
+* Fri Aug 10 2012 Michael Mraka <michael.mraka(a)redhat.com> 1.8.70-1
+- 843374 - fixed subquery in FROM must have an alias
+- 843374 - upgrade script for rhnChannelNewestPackageView
+- 843374 - make list of newest packages unique
+
* Tue Jul 31 2012 Tomas Lestach <tlestach(a)redhat.com> 1.8.69-1
- fix PG upgrade script
commit ad4647ef6281e83fa27d5ca0c4850a7365461f24
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 10 14:06:43 2012 +0200
843374 - fixed subquery in FROM must have an alias
diff --git a/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
b/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
index b35f80f..7100b32 100644
--- a/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
+++ b/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
@@ -51,7 +51,7 @@ FROM (
AND p.evr_id = pe.id
AND chp.package_id = p.id
AND chp.channel_id = m.channel_id
-)
+) latest_packages
group by channel_id, name_id, evr_id, package_arch_id
;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
index 4a00c0a..f845d2f 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
@@ -51,6 +51,6 @@ FROM (
AND p.evr_id = pe.id
AND chp.package_id = p.id
AND chp.channel_id = m.channel_id
-)
+) latest_packages
group by channel_id, name_id, evr_id, package_arch_id
;
commit 342fda667bf9eaa6c04458958954c49ab6abebd7
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 10 14:02:44 2012 +0200
843374 - upgrade script for rhnChannelNewestPackageView
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
new file mode 100644
index 0000000..4a00c0a
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/123-rhnChannelNewestPackageView.sql
@@ -0,0 +1,56 @@
+--
+-- Copyright (c) 2008--2010 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.
+--
+--
+--
+--
+-- this is much more readable with ts=4, enjoy!
+
+create or replace view
+rhnChannelNewestPackageView
+as
+SELECT channel_id,
+ name_id,
+ evr_id,
+ package_arch_id,
+ max(package_id) as package_id
+FROM (
+ SELECT m.channel_id as channel_id,
+ p.name_id as name_id,
+ p.evr_id as evr_id,
+ m.package_arch_id as package_arch_id,
+ p.id as package_id
+ FROM
+ (select max(pe.evr) AS max_evr,
+ cp.channel_id,
+ p.name_id,
+ p.package_arch_id
+ from rhnPackageEVR pe,
+ rhnPackage p,
+ rhnChannelPackage cp
+ where p.evr_id = pe.id
+ and cp.package_id = p.id
+ group by cp.channel_id, p.name_id, p.package_arch_id) m,
+ rhnPackageEVR pe,
+ rhnPackage p,
+ rhnChannelPackage chp
+ WHERE m.max_evr = pe.evr
+ AND m.name_id = p.name_id
+ AND m.package_arch_id = p.package_arch_id
+ AND p.evr_id = pe.id
+ AND chp.package_id = p.id
+ AND chp.channel_id = m.channel_id
+)
+group by channel_id, name_id, evr_id, package_arch_id
+;
commit a4505264511ff975fa64599ed04789300d47fdf2
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 10 14:00:06 2012 +0200
fixed indentation
diff --git a/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
b/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
index ad6c755..b35f80f 100644
--- a/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
+++ b/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
@@ -26,31 +26,31 @@ SELECT channel_id,
package_arch_id,
max(package_id) as package_id
FROM (
-SELECT m.channel_id as channel_id,
- p.name_id as name_id,
- p.evr_id as evr_id,
- m.package_arch_id as package_arch_id,
- p.id as package_id
-FROM
- (select max(pe.evr) AS max_evr,
- cp.channel_id,
- p.name_id,
- p.package_arch_id
- from rhnPackageEVR pe,
- rhnPackage p,
- rhnChannelPackage cp
- where p.evr_id = pe.id
- and cp.package_id = p.id
- group by cp.channel_id, p.name_id, p.package_arch_id) m,
- rhnPackageEVR pe,
- rhnPackage p,
- rhnChannelPackage chp
-WHERE m.max_evr = pe.evr
-AND m.name_id = p.name_id
-AND m.package_arch_id = p.package_arch_id
-AND p.evr_id = pe.id
-AND chp.package_id = p.id
-AND chp.channel_id = m.channel_id
+ SELECT m.channel_id as channel_id,
+ p.name_id as name_id,
+ p.evr_id as evr_id,
+ m.package_arch_id as package_arch_id,
+ p.id as package_id
+ FROM
+ (select max(pe.evr) AS max_evr,
+ cp.channel_id,
+ p.name_id,
+ p.package_arch_id
+ from rhnPackageEVR pe,
+ rhnPackage p,
+ rhnChannelPackage cp
+ where p.evr_id = pe.id
+ and cp.package_id = p.id
+ group by cp.channel_id, p.name_id, p.package_arch_id) m,
+ rhnPackageEVR pe,
+ rhnPackage p,
+ rhnChannelPackage chp
+ WHERE m.max_evr = pe.evr
+ AND m.name_id = p.name_id
+ AND m.package_arch_id = p.package_arch_id
+ AND p.evr_id = pe.id
+ AND chp.package_id = p.id
+ AND chp.channel_id = m.channel_id
)
group by channel_id, name_id, evr_id, package_arch_id
;
commit e8cb44dfbf3d9f5cd6d673335cc932894d5cabf3
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Aug 10 13:55:49 2012 +0200
843374 - make list of newest packages unique
there can be two packages with the same NEVRA but different checksum
diff --git a/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
b/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
index 2f497cb..ad6c755 100644
--- a/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
+++ b/schema/spacewalk/common/views/rhnChannelNewestPackageView.sql
@@ -20,6 +20,12 @@
create or replace view
rhnChannelNewestPackageView
as
+SELECT channel_id,
+ name_id,
+ evr_id,
+ package_arch_id,
+ max(package_id) as package_id
+FROM (
SELECT m.channel_id as channel_id,
p.name_id as name_id,
p.evr_id as evr_id,
@@ -45,5 +51,7 @@ AND m.package_arch_id = p.package_arch_id
AND p.evr_id = pe.id
AND chp.package_id = p.id
AND chp.channel_id = m.channel_id
+)
+group by channel_id, name_id, evr_id, package_arch_id
;