schema/spacewalk/postgres/packages/rhn_entitlements.pkb
| 10
schema/spacewalk/postgres/procs/delete_server.sql
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
| 10
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/091-rhn_channel.sql.postgresql
| 6
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.oracle
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.postgresql
| 242 ++++++++++
6 files changed, 257 insertions(+), 14 deletions(-)
New commits:
commit c3b07890d9efd2725a51cb078d8d7e60c489062b
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Tue Sep 27 11:29:09 2011 +0200
drop function must be inside package body
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/091-rhn_channel.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/091-rhn_channel.sql.postgresql
index f7e37ad..ae539aa 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/091-rhn_channel.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/091-rhn_channel.sql.postgresql
@@ -1,12 +1,12 @@
-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8
+--update pg_setting
+update pg_settings set setting = 'rhn_channel,' || setting where name =
'search_path';
+
drop function get_license_path(channel_id_in in numeric);
drop function license_consent(channel_id_in in numeric, user_id_in in numeric,
server_id_in in numeric);
drop function clear_subscriptions(server_id_in in numeric, deleting_server in numeric);
---update pg_setting
-update pg_settings set setting = 'rhn_channel,' || setting where name =
'search_path';
-
create or replace function obtain_read_lock(channel_family_id_in in numeric,
org_id_in in numeric)
returns void as $$
declare
commit 6e2db659bd6a13a6c568e64469658f9e6c5f4efc
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Tue Sep 27 11:24:40 2011 +0200
fixed table alias in update query
fixing
ProgrammingError('column "sc" of relation "rhnserverchannel"
does not exist\nLINE 1: UPDATE rhnServerChannel sc set sc.is_fve = \'Y\' where
sc.serv...\n ^\nQUERY: UPDATE rhnServerChannel sc
set sc.is_fve = \'Y\' where sc.server_id = $1 and sc.channel_id in (select
cfm.channel_id from rhnChannelFamilyMembers cfm where cfm.channel_family_id = $2
)\nCONTEXT: PL/pgSQL function "activate_channel_entitlement" line 102 at SQL
statement\n',)
column "sc" of relation "rhnserverchannel" does not exist
LINE 1: UPDATE rhnServerChannel sc set sc.is_fve = 'Y' where sc.serv...
^
QUERY: UPDATE rhnServerChannel sc set sc.is_fve = 'Y' where sc.server_id =
$1 and sc.channel_id in (select cfm.channel_id from rhnChannelFamilyMembers cfm where
cfm.channel_family_id = $2 )
CONTEXT: PL/pgSQL function "activate_channel_entitlement" line 102 at SQL
statement
diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
index f6367f9..10477a5 100644
--- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
@@ -632,7 +632,7 @@ as $$
-- if the host_server does not have virt
--- find all possible flex slots
-- and set each of the flex eligible guests to Y
- UPDATE rhnServerChannel sc set sc.is_fve = 'Y'
+ UPDATE rhnServerChannel sc set is_fve = 'Y'
where sc.server_id in (
select vi.virtual_system_id
from rhnServerFveCapable sfc
@@ -645,7 +645,7 @@ as $$
else
-- if the host_server has virt
-- set all its flex guests to N
- UPDATE rhnServerChannel sc set sc.is_fve = 'N'
+ UPDATE rhnServerChannel sc set is_fve = 'N'
where
sc.channel_id in (select cfm.channel_id from rhnChannelFamilyMembers
cfm
where cfm.CHANNEL_FAMILY_ID =
family.channel_family_id)
@@ -1473,7 +1473,7 @@ as $$
-- We need to convert some systems from flex guest to regular
for system in to_convert_reg(cfam_id, org_id_in, reduce_quantity)
loop
--rhn_channel.convert_to_regular(system.server_id, cfam_id);
- UPDATE rhnServerChannel sc set sc.is_fve = 'N'
+ UPDATE rhnServerChannel sc set is_fve = 'N'
where sc.server_id = system.server_id
and sc.channel_id in (select cfm.channel_id
from rhnChannelFamilyMembers
cfm
@@ -1503,7 +1503,7 @@ as $$
reduce_quantity := prev_ent_count - quantity_in;
for system in to_convert_flex(cfam_id, org_id_in, reduce_quantity)
loop
-- rhn_channel.convert_to_fve(system.server_id, cfam_id);
- UPDATE rhnServerChannel sc set sc.is_fve = 'Y'
+ UPDATE rhnServerChannel sc set is_fve = 'Y'
where sc.server_id = system.server_id
and sc.channel_id in (select cfm.channel_id
from rhnChannelFamilyMembers
cfm
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
index 0d5ce48..3d267f3 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
@@ -618,7 +618,7 @@ as $$
-- if the host_server does not have virt
--- find all possible flex slots
-- and set each of the flex eligible guests to Y
- UPDATE rhnServerChannel sc set sc.is_fve = 'Y'
+ UPDATE rhnServerChannel sc set is_fve = 'Y'
where sc.server_id in (
select vi.virtual_system_id
from rhnServerFveCapable sfc
@@ -631,7 +631,7 @@ as $$
else
-- if the host_server has virt
-- set all its flex guests to N
- UPDATE rhnServerChannel sc set sc.is_fve = 'N'
+ UPDATE rhnServerChannel sc set is_fve = 'N'
where
sc.channel_id in (select cfm.channel_id from rhnChannelFamilyMembers
cfm
where cfm.CHANNEL_FAMILY_ID =
family.channel_family_id)
@@ -1459,7 +1459,7 @@ as $$
-- We need to convert some systems from flex guest to regular
for system in to_convert_reg(cfam_id, org_id_in, reduce_quantity)
loop
--rhn_channel.convert_to_regular(system.server_id, cfam_id);
- UPDATE rhnServerChannel sc set sc.is_fve = 'N'
+ UPDATE rhnServerChannel sc set is_fve = 'N'
where sc.server_id = system.server_id
and sc.channel_id in (select cfm.channel_id
from rhnChannelFamilyMembers
cfm
@@ -1489,7 +1489,7 @@ as $$
reduce_quantity := prev_ent_count - quantity_in;
for system in to_convert_flex(cfam_id, org_id_in, reduce_quantity)
loop
-- rhn_channel.convert_to_fve(system.server_id, cfam_id);
- UPDATE rhnServerChannel sc set sc.is_fve = 'Y'
+ UPDATE rhnServerChannel sc set is_fve = 'Y'
where sc.server_id = system.server_id
and sc.channel_id in (select cfm.channel_id
from rhnChannelFamilyMembers
cfm
commit cc1d4fcd2b461c23534c12789044eeb34a79dc3f
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Tue Sep 27 11:22:58 2011 +0200
upgrade script for optional parameters
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
index 1c6f509..0d5ce48 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/090-rhn_entitlements.sql.postgresql
@@ -1055,7 +1055,7 @@ as $$
-- if we're removing a base ent, then be sure to
-- remove the server's channel subscriptions.
if ( type_is_base = 'Y' ) then
- perform rhn_channel.clear_subscriptions(sgrecord.server_id,
update_family_countsYN);
+ perform rhn_channel.clear_subscriptions(sgrecord.server_id, 0,
update_family_countsYN);
end if;
end loop;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.oracle
new file mode 100644
index 0000000..b7b00b1
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.oracle
@@ -0,0 +1 @@
+-- This file is intentionally left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.postgresql
new file mode 100644
index 0000000..2c67f51
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/092-delete_server.sql.postgresql
@@ -0,0 +1,242 @@
+-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8
+
+create or replace
+function delete_server (
+ server_id_in in numeric
+) returns void
+as
+$$
+declare
+ servergroups cursor for
+ select server_id, server_group_id
+ from rhnServerGroupMembers sgm
+ where sgm.server_id = server_id_in;
+
+ configchannels cursor for
+ select cc.id
+ from rhnConfigChannel cc,
+ rhnConfigChannelType cct,
+ rhnServerConfigChannel scc
+ where 1=1
+ and scc.server_id = server_id_in
+ and scc.config_channel_id = cc.id
+ -- these config channel types are reserved
+ -- for use by a single server, so we don't
+ -- need to check for other servers subscribed
+ and cct.label in
+ ('local_override','server_import')
+ and cct.id = cc.confchan_type_id;
+
+ is_virt boolean;
+begin
+ perform rhn_channel.delete_server_channels(server_id_in);
+ -- rhn_channel.clear_subscriptions(server_id_in);
+
+ -- filelists
+ delete from rhnFileList where id in (
+ select spfl.file_list_id
+ from rhnServerPreserveFileList spfl
+ where spfl.server_id = server_id_in
+ and not exists (
+ select 1
+ from rhnServerPreserveFileList
+ where file_list_id = spfl.file_list_id
+ and server_id != server_id_in
+ union all
+ select 1
+ from rhnKickstartPreserveFileList
+ where file_list_id = spfl.file_list_id
+ )
+ );
+
+ for configchannel in configchannels loop
+ perform rhn_config.delete_channel(configchannel.id);
+ end loop;
+
+ is_virt := exists (
+ select 1
+ from rhnServerEntitlementView
+ where server_id = server_id_in
+ and label in ('virtualization_host',
'virtualization_host_platform')
+ );
+
+ for sgm in servergroups loop
+ perform rhn_server.delete_from_servergroup(
+ sgm.server_id, sgm.server_group_id);
+ end loop;
+
+ if is_virt then
+ perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
+ end if;
+
+ -- we're handling this instead of letting an "on delete
+ -- set null" do it so that we don't run the risk
+ -- of setting off the triggers and killing us with a
+ -- mutating table
+
+ -- this is merge of two single updates:
+ -- update ... set old_server_id = null where old_server_id = server_id_in;
+ -- update ... set new_server_id = null where new_server_id = server_id_in;
+ -- so we scan rhnKickstartSession table only once
+ update rhnKickstartSession
+ set old_server_id = case when old_server_id = server_id_in then null else
old_server_id end,
+ new_server_id = case when new_server_id = server_id_in then null else
new_server_id end
+ where old_server_id = server_id_in
+ or new_server_id = server_id_in;
+
+ perform rhn_channel.clear_subscriptions(server_id_in, 1, 1);
+
+ -- A little complicated here, but the goal is to
+ -- delete records from rhnVirtualInstace only if we don't
+ -- care about them anymore. We don't care about records
+ -- in rhnVirtualInstance if we are deleting the host
+ -- system and the virtual system is already null, or
+ -- vice-versa. We *do* care about them if either the
+ -- host or virtual system is still registered because we
+ -- still want them to show up in the UI.
+ -- If there's a newer row in rhnVirtualInstance with the same
+ -- uuid, this guest must have been re-registered, so we can clean
+ -- this data up.
+
+ delete from rhnVirtualInstance vi
+ where (host_system_id = server_id_in and virtual_system_id is null)
+ or (virtual_system_id = server_id_in and host_system_id is null)
+ or (vi.virtual_system_id = server_id_in and vi.modified < (select
max(vi2.modified)
+ from rhnVirtualInstance vi2 where vi2.uuid = vi.uuid));
+
+ -- this is merge of two single updates:
+ -- update ... set host_system_id = null where host_system_id = server_id_in;
+ -- update ... set virtual_system_id = null where virtual_system_id =
server_id_in;
+ -- so we scan rhnVirtualInstance table only once
+ update rhnVirtualInstance
+ set host_system_id = case when host_system_id = server_id_in then null else
host_system_id end,
+ virtual_system_id = case when virtual_system_id = server_id_in then null
else virtual_system_id end
+ where host_system_id = server_id_in
+ or virtual_system_id = server_id_in;
+
+ -- this is merge of two single updates:
+ -- update ... set old_host_system_id = null when old_host_system_id =
server_id_in;
+ -- update ... set new_host_system_id = null when new_host_system_id =
server_id_in;
+ -- so we scan rhnVirtualInstanceEventLog table only once
+ update rhnVirtualInstanceEventLog
+ set old_host_system_id = case when old_host_system_id = server_id_in then null
else old_host_system_id end,
+ new_host_system_id = case when new_host_system_id = server_id_in then null
else new_host_system_id end
+ where old_host_system_id = server_id_in
+ or new_host_system_id = server_id_in;
+
+ -- We're deleting everything with a foreign key to rhnServer
+ -- here, now. I'm hoping this will help aleviate our deadlock
+ -- problem.
+
+ delete from rhnActionConfigChannel where server_id = server_id_in;
+ delete from rhnActionConfigRevision where server_id = server_id_in;
+ delete from rhnActionPackageRemovalFailure where server_id = server_id_in;
+ delete from rhnClientCapability where server_id = server_id_in;
+ delete from rhnCpu where server_id = server_id_in;
+ -- there's still a cascade here, because the constraint keeps the
+ -- table locked for too long to rebuild it. Ugh...
+ delete from rhnDevice where server_id = server_id_in;
+ delete from rhnProxyInfo where server_id = server_id_in;
+ delete from rhnRam where server_id = server_id_in;
+ delete from rhnRegToken where server_id = server_id_in;
+ delete from rhnSatelliteInfo where server_id = server_id_in;
+ -- this cascades to rhnActionConfigChannel and rhnActionConfigFileName
+ delete from rhnServerAction where server_id = server_id_in;
+ delete from rhnServerActionPackageResult where server_id = server_id_in;
+ delete from rhnServerActionScriptResult where server_id = server_id_in;
+ delete from rhnServerActionVerifyResult where server_id = server_id_in;
+ delete from rhnServerActionVerifyMissing where server_id = server_id_in;
+ -- counts are handled above. this should be a delete_ function.
+ delete from rhnServerChannel where server_id = server_id_in;
+ delete from rhnServerConfigChannel where server_id = server_id_in;
+ delete from rhnServerCustomDataValue where server_id = server_id_in;
+ delete from rhnServerDMI where server_id = server_id_in;
+ delete from rhnServerEvent where server_id = server_id_in;
+ delete from rhnServerHistory where server_id = server_id_in;
+ delete from rhnServerInfo where server_id = server_id_in;
+ delete from rhnServerInstallInfo where server_id = server_id_in;
+ delete from rhnServerLocation where server_id = server_id_in;
+ delete from rhnServerLock where server_id = server_id_in;
+ delete from rhnServerNeededCache where server_id = server_id_in;
+ delete from rhnServerNetwork where server_id = server_id_in;
+ delete from rhnServerNotes where server_id = server_id_in;
+ -- I'm not removing the foreign key from rhnServerPackage; that'll
+ -- take forever. Do the delete anyway.
+ delete from rhnServerPackage where server_id = server_id_in;
+ delete from rhnServerTokenRegs where server_id = server_id_in;
+ delete from rhnSnapshotTag where server_id = server_id_in;
+ -- this cascades to:
+ -- rhnSnapshotChannel, rhnSnapshotConfigChannel, rhnSnapshotPackage,
+ -- rhnSnapshotConfigRevision, rhnSnapshotServerGroup,
+ -- rhnSnapshotTag.
+ -- We may want to consider delete_snapshot() at some point, but
+ -- I don't think we need to yet.
+ delete from rhnSnapshot where server_id = server_id_in;
+ delete from rhnUserServerPrefs where server_id = server_id_in;
+ -- hrm, this one's interesting... we _probably_ should delete
+ -- everything for the parent server_id when we delete the proxy,
+ -- but we don't currently.
+ delete from rhnServerPath where server_id_in in (server_id, proxy_server_id);
+ delete from rhnUserServerPerms where server_id = server_id_in;
+
+ delete from rhnServerNetInterface where server_id = server_id_in;
+ delete from rhn_server_monitoring_info where recid = server_id_in;
+
+ delete from rhnAppInstallSession where server_id = server_id_in;
+ delete from rhnServerUuid where server_id = server_id_in;
+
+ -- We delete all the probes running directly against this system
+ -- and any probes that were using this Server as a Proxy Scout.
+ DELETE FROM rhn_probe_state PS WHERE PS.probe_id IN (
+ SELECT CP.probe_id
+ FROM rhn_check_probe CP
+ WHERE CP.host_id = server_id_in
+ OR CP.sat_cluster_id in
+ (SELECT SN.sat_cluster_id
+ FROM rhn_sat_node SN
+ WHERE SN.server_id = server_id_in)
+ );
+
+ DELETE FROM rhn_probe P WHERE P.recid IN (
+ SELECT CP.probe_id
+ FROM rhn_check_probe CP
+ WHERE CP.host_id = server_id_in
+ OR CP.sat_cluster_id in
+ (SELECT SN.sat_cluster_id
+ FROM rhn_sat_node SN
+ WHERE SN.server_id = server_id_in)
+ );
+
+ -- delete from time_series TODO
+
+ delete from rhn_check_probe where host_id = server_id_in;
+ delete from rhn_host_probe where host_id = server_id_in;
+
+ delete from rhn_sat_cluster where recid in
+ ( select sat_cluster_id from rhn_sat_node where server_id = server_id_in );
+
+ delete from rhn_sat_node where server_id = server_id_in;
+
+ delete from rhnPushClient where server_id = server_id_in;
+
+ -- now get rhnServer itself.
+ delete
+ from rhnServer
+ where id = server_id_in;
+
+ delete
+ from rhnSet
+ where 1=1
+ and user_id in (
+ select wc.id
+ from rhnServer rs,
+ web_contact wc
+ where rs.id = server_id_in
+ and rs.org_id = wc.org_id
+ )
+ and label = 'system_list'
+ and element = server_id_in;
+end;
+$$
+language plpgsql;
+
commit b1a163656232e7464243cfaa1165db1288ff297f
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Tue Sep 27 10:19:08 2011 +0200
fixed clear_subscriptions() call with optional parameters
fixing
javax.servlet.ServletException: com.redhat.rhn.common.db.WrappedSQLException: ERROR:
fun
ction rhn_channel.clear_subscriptions(numeric, integer) is not unique
Hint: Could not choose a best candidate function. You might need to add explicit
type
casts.
Where: PL/pgSQL function "delete_server" line 79 at PERFORM
diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
index 13bfdf2..f6367f9 100644
--- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
@@ -1069,7 +1069,7 @@ as $$
-- if we're removing a base ent, then be sure to
-- remove the server's channel subscriptions.
if ( type_is_base = 'Y' ) then
- perform rhn_channel.clear_subscriptions(sgrecord.server_id,
update_family_countsYN);
+ perform rhn_channel.clear_subscriptions(sgrecord.server_id, 0,
update_family_countsYN);
end if;
end loop;
diff --git a/schema/spacewalk/postgres/procs/delete_server.sql
b/schema/spacewalk/postgres/procs/delete_server.sql
index ad35b39..0d6c269 100644
--- a/schema/spacewalk/postgres/procs/delete_server.sql
+++ b/schema/spacewalk/postgres/procs/delete_server.sql
@@ -103,7 +103,7 @@ begin
where old_server_id = server_id_in
or new_server_id = server_id_in;
- perform rhn_channel.clear_subscriptions(server_id_in,1);
+ perform rhn_channel.clear_subscriptions(server_id_in, 1, 1);
-- A little complicated here, but the goal is to
-- delete records from rhnVirtualInstace only if we don't