schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.oracle | 238 --------- schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.postgresql | 240 ---------- schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.oracle | 238 +++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.postgresql | 240 ++++++++++ 4 files changed, 478 insertions(+), 478 deletions(-)
New commits: commit 0462cdf43aae93d8b7d3ad3ab73f09b0664bb2c0 Author: Simon Lukasik slukasik@redhat.com Date: Thu Aug 11 15:29:32 2011 +0200
Do not confuse people by filename, this is not an upgrade of package
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.oracle deleted file mode 100644 index 4e1cc4d..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.oracle +++ /dev/null @@ -1,238 +0,0 @@ -create or replace -procedure delete_server ( - server_id_in in number -) is - cursor servergroups is - select server_id, server_group_id - from rhnServerGroupMembers sgm - where sgm.server_id = server_id_in; - cursor configchannels is - 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; - type filelistsid_t is table of rhnServerPreserveFileList.file_list_id%type; - filelistsid_c filelistsid_t; - - type probesid_t is table of rhn_check_probe.probe_id%type; - probesid_c probesid_t; - - is_virt number := 0; -begin - rhn_channel.delete_server_channels(server_id_in); - -- rhn_channel.clear_subscriptions(server_id_in); - - -- filelists - select spfl.file_list_id id bulk collect into filelistsid_c - 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 - select 1 - from rhnKickstartPreserveFileList - where file_list_id = spfl.file_list_id - ); - if filelistsid_c.first is not null then - forall i in filelistsid_c.first..filelistsid_c.last - delete from rhnFileList where id = filelistsid_c(i); - end if; - - for configchannel in configchannels loop - rhn_config.delete_channel(configchannel.id); - end loop; - - select count(1) into is_virt - from rhnServerEntitlementView - where server_id = server_id_in - and label in ('virtualization_host', 'virtualization_host_platform') - and rownum <= 1; - - for sgm in servergroups loop - rhn_server.delete_from_servergroup( - sgm.server_id, sgm.server_group_id); - end loop; - - if is_virt = 1 then - 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; - - rhn_channel.clear_subscriptions(server_id_in,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. - SELECT CP.probe_id bulk collect into probesid_c - 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); - - if probesid_c.first is not null then - FORALL i IN probesid_c.first..probesid_c.last - DELETE FROM rhn_probe_state PS WHERE PS.probe_id = probesid_c(i); - FORALL i IN probesid_c.first..probesid_c.last - DELETE FROM rhn_probe P WHERE P.recid = probesid_c(i); - FORALL i IN probesid_c.first..probesid_c.last - DELETE /*+index(time_series time_series_probe_id_idx)*/ - FROM time_series - WHERE substr(o_id, instr(o_id, '-') + 1, - (instr(o_id, '-', instr(o_id, '-') + 1) - instr(o_id, '-')) - 1) = probesid_c(i); - end if; - - 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 delete_server; -/ -show errors; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.postgresql deleted file mode 100644 index 55c6067..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.pkb.sql.postgresql +++ /dev/null @@ -1,240 +0,0 @@ -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); - - -- 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; - diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.oracle new file mode 100644 index 0000000..4e1cc4d --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.oracle @@ -0,0 +1,238 @@ +create or replace +procedure delete_server ( + server_id_in in number +) is + cursor servergroups is + select server_id, server_group_id + from rhnServerGroupMembers sgm + where sgm.server_id = server_id_in; + cursor configchannels is + 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; + type filelistsid_t is table of rhnServerPreserveFileList.file_list_id%type; + filelistsid_c filelistsid_t; + + type probesid_t is table of rhn_check_probe.probe_id%type; + probesid_c probesid_t; + + is_virt number := 0; +begin + rhn_channel.delete_server_channels(server_id_in); + -- rhn_channel.clear_subscriptions(server_id_in); + + -- filelists + select spfl.file_list_id id bulk collect into filelistsid_c + 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 + select 1 + from rhnKickstartPreserveFileList + where file_list_id = spfl.file_list_id + ); + if filelistsid_c.first is not null then + forall i in filelistsid_c.first..filelistsid_c.last + delete from rhnFileList where id = filelistsid_c(i); + end if; + + for configchannel in configchannels loop + rhn_config.delete_channel(configchannel.id); + end loop; + + select count(1) into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform') + and rownum <= 1; + + for sgm in servergroups loop + rhn_server.delete_from_servergroup( + sgm.server_id, sgm.server_group_id); + end loop; + + if is_virt = 1 then + 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; + + rhn_channel.clear_subscriptions(server_id_in,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. + SELECT CP.probe_id bulk collect into probesid_c + 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); + + if probesid_c.first is not null then + FORALL i IN probesid_c.first..probesid_c.last + DELETE FROM rhn_probe_state PS WHERE PS.probe_id = probesid_c(i); + FORALL i IN probesid_c.first..probesid_c.last + DELETE FROM rhn_probe P WHERE P.recid = probesid_c(i); + FORALL i IN probesid_c.first..probesid_c.last + DELETE /*+index(time_series time_series_probe_id_idx)*/ + FROM time_series + WHERE substr(o_id, instr(o_id, '-') + 1, + (instr(o_id, '-', instr(o_id, '-') + 1) - instr(o_id, '-')) - 1) = probesid_c(i); + end if; + + 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 delete_server; +/ +show errors; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.postgresql new file mode 100644 index 0000000..55c6067 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.4-to-spacewalk-schema-1.5/017-rhn_server.sql.postgresql @@ -0,0 +1,240 @@ +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); + + -- 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; +