schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.oracle
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.postgresql
| 79 ++++++++++
2 files changed, 80 insertions(+)
New commits:
commit ced436451a79cacf59314872d30def8fdfc742c0
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Tue Jan 25 18:01:14 2011 +0100
Modify the type of rhnPackage.build_time upon upgrade, on PostgreSQL.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.oracle
new file mode 100644
index 0000000..b7b00b1
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.oracle
@@ -0,0 +1 @@
+-- This file is intentionally left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.postgresql
new file mode 100644
index 0000000..1229ce0
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.2-to-spacewalk-schema-1.3/016-rhnpackage-build_time-type.sql.postgresql
@@ -0,0 +1,79 @@
+-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8
+
+-- The drop is needed to avoid
+-- ERROR: cannot alter type of a column used by a view or rule
+-- DETAIL: rule _RETURN on view rhnserverneededview depends on column
"build_time"
+-- in the alter table command.
+
+drop view rhnserverneededview;
+
+alter table rhnpackage alter build_time type timestamp without time zone ;
+
+-- recreating the view
+create or replace view
+rhnServerNeededView
+(
+ org_id,
+ server_id,
+ errata_id,
+ package_id,
+ 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
+;
Show replies by date