rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/oracle/packages/rhn_org.pkb | 1 schema/spacewalk/postgres/packages/rhn_org.pkb | 3 schema/spacewalk/spacewalk-schema.spec | 8 schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.oracle | 235 ++++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.postgresql | 98 ++++ 6 files changed, 344 insertions(+), 3 deletions(-)
New commits: commit ede0e5f9b745bdd776b02033127c05e48e63ee0e Author: Tomas Lestach tlestach@redhat.com Date: Fri Apr 27 12:08:12 2012 +0200
Automatic commit of package [spacewalk-schema] release [1.8.22-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index db2b839..56437dc 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -1.8.21-1 schema/spacewalk/ +1.8.22-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index d8774cb..6490f60 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.21 +Version: 1.8.22 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz
@@ -69,6 +69,12 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql*
%changelog +* Fri Apr 27 2012 Tomas Lestach tlestach@redhat.com 1.8.22-1 +- 807283 - fixing postgresql schema and adding appropriate schema upgrade + scripts +- 807283 - remove rhnContentSource row when delete row in oracle pkg +- add unique constraint on rhnChannelArch.name + * Fri Apr 27 2012 Miroslav Suchý msuchy@redhat.com 1.8.21-1 - 630953 - make sure that rows are unique before creating unique index
commit 2a4dccd5d80203c3af914d9adc89bb4109267708 Author: Tomas Lestach tlestach@redhat.com Date: Fri Apr 27 12:04:33 2012 +0200
807283 - fixing postgresql schema and adding appropriate schema upgrade scripts
diff --git a/schema/spacewalk/postgres/packages/rhn_org.pkb b/schema/spacewalk/postgres/packages/rhn_org.pkb index 17e998a..31a7d96 100644 --- a/schema/spacewalk/postgres/packages/rhn_org.pkb +++ b/schema/spacewalk/postgres/packages/rhn_org.pkb @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 e8e55338cd17ae9a27047273569e9aa4ab86e4a0 +-- oracle equivalent source sha1 48f766d7e2ec61890c48645c66978f81eab2cdbe -- -- Copyright (c) 2008--2012 Red Hat, Inc. -- @@ -103,6 +103,7 @@ create or replace function delete_org ( delete from rhn_redirects where customer_id = org_id_in; delete from rhn_sat_cluster where customer_id = org_id_in; delete from rhn_schedules where customer_id = org_id_in; + delete from rhnContentSource where org_id = org_id_in;
-- Delete the org. delete from web_customer where id = org_id_in; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.oracle new file mode 100644 index 0000000..46f98f5 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.oracle @@ -0,0 +1,235 @@ +-- +-- Copyright (c) 2008--2012 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. +-- +-- +-- +-- + +CREATE OR REPLACE +PACKAGE BODY rhn_org +IS + procedure delete_org ( + org_id_in in number + ) + is + cursor users is + select id + from web_contact + where org_id = org_id_in; + + cursor servers(org_id_in in number) is + select id + from rhnServer + where org_id = org_id_in; + + cursor config_channels is + select id + from rhnConfigChannel + where org_id = org_id_in; + + cursor custom_channels is + select id + from rhnChannel + where org_id = org_id_in; + + cursor errata is + select id + from rhnErrata + where org_id = org_id_in; + + begin + + if org_id_in = 1 then + rhn_exception.raise_exception('cannot_delete_base_org'); + end if; + + -- Delete all users. + for u in users loop + rhn_org.delete_user(u.id, 1); + end loop; + + -- Delete all servers. + for s in servers(org_id_in) loop + delete_server(s.id); + end loop; + + -- Delete all config channels. + for c in config_channels loop + rhn_config.delete_channel(c.id); + end loop; + + -- Delete all custom channels. + for cc in custom_channels loop + delete from rhnServerChannel where channel_id = cc.id; + delete from rhnServerProfilePackage where server_profile_id in ( + select id from rhnServerProfile where base_channel = cc.id + ); + delete from rhnServerProfile where base_channel = cc.id; + end loop; + + -- Delete all errata packages + for e in errata loop + delete from rhnErrataPackage where errata_id = e.id; + end loop; + + -- Give the org's entitlements back to the main org. + rhn_entitlements.remove_org_entitlements(org_id_in); + + -- Clean up tables where we don't have a cascading delete. + delete from rhnChannel where org_id = org_id_in; + delete from rhnDailySummaryQueue where org_id = org_id_in; + delete from rhnOrgQuota where org_id = org_id_in; + delete from rhnFileList where org_id = org_id_in; + delete from rhnServerGroup where org_id = org_id_in; + delete from rhn_check_suites where customer_id = org_id_in; + delete from rhn_command_target where customer_id = org_id_in; + delete from rhn_contact_groups where customer_id = org_id_in; + delete from rhn_notification_formats where customer_id = org_id_in; + delete from rhn_probe where customer_id = org_id_in; + delete from rhn_redirects where customer_id = org_id_in; + delete from rhn_sat_cluster where customer_id = org_id_in; + delete from rhn_schedules where customer_id = org_id_in; + delete from rhnContentSource where org_id = org_id_in; + + -- Delete the org. + delete from web_customer where id = org_id_in; + + end delete_org; + + procedure delete_user(user_id_in in number, deleting_org in number := 0) is + cursor servergroups_needing_admins is + select usgp.server_group_id server_group_id + from rhnUserServerGroupPerms usgp + where 1=1 + and usgp.user_id = user_id_in + and not exists ( + select 1 + from rhnUserServerGroupPerms sq_usgp + where 1=1 + and sq_usgp.server_group_id = usgp.server_group_id + and sq_usgp.user_id != user_id_in + ); + users number; + our_org_id number; + other_users number; + other_org_admin number; + other_user_id number; + is_admin number; + begin + select wc.org_id + into our_org_id + from web_contact wc + where id = user_id_in; + + -- find any other users + begin + select id, 1 + into other_user_id, other_users + from web_contact + where 1=1 + and org_id = our_org_id + and id != user_id_in + and rownum = 1; + exception + when no_data_found then + other_users := 0; + end; + + -- now do org admin stuff + if other_users != 0 then + -- is user admin? + select count(1) + into is_admin + from rhnUserGroupType ugt, + rhnUserGroup ug, + rhnUserGroupMembers ugm + where ugm.user_id = user_id_in + and ugm.user_group_id = ug.id + and ug.group_type = ugt.id + and ugt.label = 'org_admin'; + if is_admin > 0 then + begin + select new_ugm.user_id + into other_org_admin + from rhnUserGroupMembers new_ugm, + rhnUserGroupType ugt, + rhnUserGroup ug, + rhnUserGroupMembers ugm + where ugm.user_id = user_id_in + and ugm.user_group_id = ug.id + and ug.group_type = ugt.id + and ugt.label = 'org_admin' + and ug.id = new_ugm.user_group_id + and new_ugm.user_id != user_id_in + and rownum = 1; + exception + when no_data_found then + -- If we're deleting the org, we don't want to raise + -- the exception. + if deleting_org = 0 then + rhn_exception.raise_exception('cannot_delete_user'); + end if; + end; + + for sg in servergroups_needing_admins loop + rhn_user.add_servergroup_perm(other_org_admin, + sg.server_group_id); + end loop; + end if; + end if; + + -- and now things for every user + delete from rhn_command_queue_sessions where contact_id = user_id_in; + delete from rhn_contact_groups + where recid in ( + select contact_group_id + from rhn_contact_group_members + where member_contact_method_id in ( + select recid from rhn_contact_methods + where contact_id = user_id_in + ) + ) + and not exists ( + select 1 + from rhn_contact_group_members, rhn_contact_methods + where rhn_contact_groups.recid = rhn_contact_group_members.contact_group_id + and rhn_contact_group_members.member_contact_method_id = rhn_contact_methods.recid + and rhn_contact_methods.contact_id <> user_id_in + ); + delete from rhn_contact_methods where contact_id = user_id_in; + delete from rhn_redirects where contact_id = user_id_in; + delete from rhnUserServerPerms where user_id = user_id_in; + update rhnConfigRevision + set changed_by_id = NULL + where changed_by_id = user_id_in; + if other_users != 0 then + update rhnRegToken + set user_id = nvl(other_org_admin, other_user_id) + where org_id = our_org_id + and user_id = user_id_in; + end if; + + begin + delete from web_contact where id = user_id_in; + exception + when others then + rhn_exception.raise_exception('cannot_delete_user'); + end; + return; + end delete_user; + +END rhn_org; +/ +SHOW ERRORS + diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.postgresql new file mode 100644 index 0000000..bc3c148 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/035-rhn_org.pkb.sql.postgresql @@ -0,0 +1,98 @@ +-- oracle equivalent source sha1 48f766d7e2ec61890c48645c66978f81eab2cdbe + +--update pg_setting +update pg_settings set setting = 'rhn_org,' || setting where name = 'search_path'; + + +create or replace + procedure delete_org ( + org_id_in in number + ) + is + cursor users is + select id + from web_contact + where org_id = org_id_in; + + cursor servers(org_id_in in number) is + select id + from rhnServer + where org_id = org_id_in; + + cursor config_channels is + select id + from rhnConfigChannel + where org_id = org_id_in; + + cursor custom_channels is + select id + from rhnChannel + where org_id = org_id_in; + + cursor errata is + select id + from rhnErrata + where org_id = org_id_in; + + begin + + if org_id_in = 1 then + rhn_exception.raise_exception('cannot_delete_base_org'); + end if; + + -- Delete all users. + for u in users loop + rhn_org.delete_user(u.id, 1); + end loop; + + -- Delete all servers. + for s in servers(org_id_in) loop + delete_server(s.id); + end loop; + + -- Delete all config channels. + for c in config_channels loop + rhn_config.delete_channel(c.id); + end loop; + + -- Delete all custom channels. + for cc in custom_channels loop + delete from rhnServerChannel where channel_id = cc.id; + delete from rhnServerProfilePackage where server_profile_id in ( + select id from rhnServerProfile where base_channel = cc.id + ); + delete from rhnServerProfile where base_channel = cc.id; + end loop; + + -- Delete all errata packages + for e in errata loop + delete from rhnErrataPackage where errata_id = e.id; + end loop; + + -- Give the org's entitlements back to the main org. + rhn_entitlements.remove_org_entitlements(org_id_in); + + -- Clean up tables where we don't have a cascading delete. + delete from rhnChannel where org_id = org_id_in; + delete from rhnDailySummaryQueue where org_id = org_id_in; + delete from rhnOrgQuota where org_id = org_id_in; + delete from rhnFileList where org_id = org_id_in; + delete from rhnServerGroup where org_id = org_id_in; + delete from rhn_check_suites where customer_id = org_id_in; + delete from rhn_command_target where customer_id = org_id_in; + delete from rhn_contact_groups where customer_id = org_id_in; + delete from rhn_notification_formats where customer_id = org_id_in; + delete from rhn_probe where customer_id = org_id_in; + delete from rhn_redirects where customer_id = org_id_in; + delete from rhn_sat_cluster where customer_id = org_id_in; + delete from rhn_schedules where customer_id = org_id_in; + delete from rhnContentSource where org_id = org_id_in; + + -- Delete the org. + delete from web_customer where id = org_id_in; + + end delete_org; + +-- restore the original setting +update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_channel')+1) ) where name = 'search_path'; +
commit 7fa3c2c04cd6d09bcf1682797638ed26fe5fc73c Author: root root@sat541-rhel5.shughes.sat Date: Wed Apr 25 12:53:04 2012 -0400
807283 - remove rhnContentSource row when delete row in oracle pkg
diff --git a/schema/spacewalk/oracle/packages/rhn_org.pkb b/schema/spacewalk/oracle/packages/rhn_org.pkb index d0dda5c..46f98f5 100644 --- a/schema/spacewalk/oracle/packages/rhn_org.pkb +++ b/schema/spacewalk/oracle/packages/rhn_org.pkb @@ -100,6 +100,7 @@ IS delete from rhn_redirects where customer_id = org_id_in; delete from rhn_sat_cluster where customer_id = org_id_in; delete from rhn_schedules where customer_id = org_id_in; + delete from rhnContentSource where org_id = org_id_in;
-- Delete the org. delete from web_customer where id = org_id_in;
spacewalk-commits@lists.fedorahosted.org