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(a)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(a)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(a)redhat.com> 1.8.21-1
- 630953 - make sure that rows are unique before creating unique index
commit 2a4dccd5d80203c3af914d9adc89bb4109267708
Author: Tomas Lestach <tlestach(a)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(a)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;