[Spacewalk-list] question about upgrade 0.4 - 0.5 : Oracle schema upgrade failure
Michiel van Es
michiele at info.nl
Tue May 12 11:45:15 UTC 2009
The upgrade logfile from 0.3-0.4 (a couple of months old):
sds
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/112-rhnKSData-drop-name.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/112-RHNKSDATA-DROP-NAME.SQ
------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/112-rhnKSData-drop-name.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnKSData
2 DROP CONSTRAINT rhn_ks_oid_name_uq;
Table altered.
SQL>
SQL> ALTER TABLE rhnKSData
2 DROP COLUMN name;
Table altered.
SQL>
SQL> ALTER TABLE rhnKsData
2 ADD cobbler_id varchar(64);
Table altered.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 2 2008/12/02 16:51:05.2 jsherrill
SQL> -- Add cobbler_id column
SQL> --
SQL> -- Revision 1 2008/10/01 7:01:05 mmccune
SQL> -- Removed the unused name column
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/113-rhnActionKickstart-add-kshost.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/113-RHNACTIONKICKSTART-ADD-KSHOST.
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/113-rhnActionKickstart-add-kshost.s
ql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL>
SQL> ALTER TABLE rhnActionKickstart
2 ADD kickstart_host varchar2(256);
Table altered.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2008/10/29 7:01:05 mmccune
SQL> -- add new kickstart_host for koan usage
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/114-rhnChannelTrust.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/114-RHNCHANNELTRUST.SQ
--------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/114-rhnChannelTrust.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> create table
2 rhnChannelTrust
3 (
4 channel_id number
5 constraint rhn_channel_trust_cid_nn not null
6 constraint rhn_channel_trust_cid_fk
7 references rhnChannel(id)
8 on delete cascade,
9 org_trust_id number
10 constraint rhn_channel_trust_otid_nn not null
11 constraint rhn_channel_trust_otid_fk
12 references web_customer(id)
13 on delete cascade,
14 created date default (sysdate)
15 constraint rhn_channel_trust_created_nn not
null,
16 modified date default (sysdate)
17 constraint rhn_channel_trust_modified_nn
not null
18 )
19 storage( pctincrease 1 freelists 16)
20 enable row movement
21 initrans 32;
Table created.
SQL>
SQL> create unique index rhn_channel_trust_cid_uq
2 on rhnChannelTrust(channel_id,org_trust_id)
3 tablespace SPACEWALK_DATA
4 storage( pctincrease 1 freelists 16)
5 initrans 32;
Index created.
SQL>
SQL> create index rhn_channel_trust_org_trust
2 on rhnChannelTrust(org_trust_id)
3 tablespace SPACEWALK_DATA
4 storage( pctincrease 1 freelists 16)
5 initrans 32;
Index created.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/115-rhnChannel-add-maint-columns.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/115-RHNCHANNEL-ADD-MAINT-COLUMNS.S
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/115-rhnChannel-add-maint-columns.sq
l
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL> ALTER TABLE rhnChannel
2 ADD channel_access varchar2(10) default 'private';
Table altered.
SQL>
SQL> ALTER TABLE rhnChannel
2 ADD maint_name varchar2(128);
Table altered.
SQL>
SQL> ALTER TABLE rhnChannel
2 ADD maint_email varchar2(128);
Table altered.
SQL>
SQL> ALTER TABLE rhnChannel
2 ADD maint_phone varchar2(128);
Table altered.
SQL>
SQL> ALTER TABLE rhnChannel
2 ADD support_policy varchar2(256);
Table altered.
SQL>
SQL>
SQL>
SQL> create index rhn_channel_access_idx
2 on rhnChannel(channel_access)
3 tablespace SPACEWALK_DATA
4 storage ( freelists 16 )
5 initrans 32
6 nologging;
Index created.
SQL>
SQL> create or replace trigger rhn_channel_access_trig
2 after update on rhnChannel
3 for each row
4 begin
5 if :old.channel_access = 'protected' and
6 :new.channel_access != 'protected'
7 then
8 delete from rhnChannelTrust where channel_id = :old.id;
9 end if;
10 end;
11 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/116-rhnTrustedOrgs.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/116-RHNTRUSTEDORGS.SQ
-------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/116-rhnTrustedOrgs.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> create table
2 rhnTrustedOrgs
3 (
4 org_id number
5 constraint rhn_trusted_orgs_oid_nn not null
6 constraint rhn_trusted_orgs_oid_fk
7 references web_customer(id)
8 on delete cascade,
9 org_trust_id number
10 constraint rhn_trusted_orgs_otid_nn not null
11 constraint rhn_trusted_orgs_otid_fk
12 references web_customer(id)
13 on delete cascade,
14 created date default (sysdate)
15 constraint rhn_trusted_orgs_created_nn not
null,
16 modified date default (sysdate)
17 constraint rhn_trusted_orgs_modified_nn not
null
18 )
19 storage( pctincrease 1 freelists 16)
20 enable row movement
21 initrans 32;
Table created.
SQL>
SQL> create unique index rhn_trusted_orgs_oid_uq
2 on rhnTrustedOrgs(org_id,org_trust_id)
3 tablespace SPACEWALK_DATA
4 storage( pctincrease 1 freelists 16)
5 initrans 32;
Index created.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/117-rhnAllowTrust.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/117-RHNALLOWTRUST.SQ
------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/117-rhnAllowTrust.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> create table
2 rhnAllowTrust
3 (
4 org_id number
5 constraint rhn_allow_trust_oid_nn not null
6 constraint rhn_allow_trust_oid_fk
7 references web_customer(id)
8 on delete cascade,
9 channel_flag char(1) default('N')
10 constraint rhn_allow_trust_channelflg_nn
not null
11 constraint rhn_allow_trust_channelflg_ck
12 check (channel_flag in ('N','Y')),
13 migration_flag char(1) default('N')
14 constraint rhn_allow_trust_migrflg_nn not null
15 constraint rhn_allow_trust_migrflg_ck
16 check (migration_flag in ('N','Y')),
17 created date default (sysdate)
18 constraint rhn_allow_trust_created_nn not null,
19 modified date default (sysdate)
20 constraint rhn_allow_trust_modified_nn not null
21 )
22 storage( pctincrease 1 freelists 16)
23 enable row movement
24 initrans 32;
Table created.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/118-rhnSystemMigrations.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/118-RHNSYSTEMMIGRATIONS.SQ
------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/118-rhnSystemMigrations.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> create table
2 rhnSystemMigrations
3 (
4 org_id_to number
5 constraint rhn_sys_mig_oidto_nn not null
6 constraint rhn_sys_mig_oidto_fk
7 references web_customer(id),
8 org_id_from number
9 constraint rhn_sys_mig_oidfrm_nn not null
10 constraint rhn_sys_mig_oidfrm_fk
11 references web_customer(id),
12 server_id number
13 constraint rhn_sys_mig_sid_nn not null
14 constraint rhn_sys_mig_sid_fk
15 references rhnServer(id)
16 on delete cascade,
17 migrated date default (sysdate)
18 constraint rhn_sys_mig_migrated_nn not null
19 )
20 storage ( pctincrease 1 freelists 16 )
21 enable row movement
22 initrans 32;
Table created.
SQL>
SQL> create index rsm_org_id_to_idx
2 on rhnSystemMigrations ( org_id_to )
3 storage ( freelists 16 )
4 initrans 32;
Index created.
SQL>
SQL>
SQL> create index rsm_org_id_from_idx
2 on rhnSystemMigrations ( org_id_from )
3 storage ( freelists 16 )
4 initrans 32;
Index created.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/119-rhnSharedChannelView.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/119-RHNSHAREDCHANNELVIEW.SQ
-------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/119-rhnSharedChannelView.sql
SQL> CREATE OR REPLACE VIEW RHNSHAREDCHANNELVIEW
2 AS
3 SELECT
4 CH.ID,
5 CH.PARENT_CHANNEL,
6 CH.ORG_ID,
7 CH.CHANNEL_ARCH_ID,
8 CH.LABEL,
9 CH.BASEDIR,
10 CH.NAME,
11 CH.SUMMARY,
12 CH.DESCRIPTION,
13 CH.PRODUCT_NAME_ID,
14 CH.GPG_KEY_URL,
15 CH.GPG_KEY_ID,
16 CH.GPG_KEY_FP,
17 CH.END_OF_LIFE,
18 CH.RECEIVING_UPDATES,
19 CH.LAST_MODIFIED,
20 CH.CHANNEL_PRODUCT_ID,
21 CH.CREATED,
22 CH.MODIFIED,
23 CH.CHANNEL_ACCESS,
24 TR.ORG_TRUST_ID
25 FROM RHNCHANNEL CH,
26 RHNTRUSTEDORGS TR
27 WHERE CH.ORG_ID = TR.ORG_ID AND
28 CH.CHANNEL_ACCESS = 'public'
29 UNION
30 SELECT
31 CH.ID,
32 CH.PARENT_CHANNEL,
33 CH.ORG_ID,
34 CH.CHANNEL_ARCH_ID,
35 CH.LABEL,
36 CH.BASEDIR,
37 CH.NAME,
38 CH.SUMMARY,
39 CH.DESCRIPTION,
40 CH.PRODUCT_NAME_ID,
41 CH.GPG_KEY_URL,
42 CH.GPG_KEY_ID,
43 CH.GPG_KEY_FP,
44 CH.END_OF_LIFE,
45 CH.RECEIVING_UPDATES,
46 CH.LAST_MODIFIED,
47 CH.CHANNEL_PRODUCT_ID,
48 CH.CREATED,
49 CH.MODIFIED,
50 CH.CHANNEL_ACCESS,
51 TR.ORG_TRUST_ID
52 FROM RHNCHANNEL CH,
53 RHNCHANNELTRUST TR
54 WHERE CH.ID = TR.CHANNEL_ID
55 /
View created.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/120-rhnUserChannel.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/120-RHNUSERCHANNEL.SQ
-------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/120-rhnUserChannel.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL>
SQL> create or replace view rhnUserChannel
2 as
3 select
4 cfp.user_id,
5 cfp.org_id,
6 cfm.channel_id,
7 'manage' role
8 from rhnChannelFamilyMembers cfm,
9 rhnUserChannelFamilyPerms cfp
10 where
11 cfp.channel_family_id = cfm.channel_family_id and
12 rhn_channel.user_role_check(cfm.channel_id, cfp.user_id,
'manage') = 1
13 union all
14 select
15 cfp.user_id,
16 cfp.org_id,
17 cfm.channel_id,
18 'subscribe' role
19 from rhnChannelFamilyMembers cfm,
20 rhnUserChannelFamilyPerms cfp
21 where
22 cfp.channel_family_id = cfm.channel_family_id and
23 rhn_channel.user_role_check(cfm.channel_id, cfp.user_id,
'subscribe') = 1
24 union all
25 select
26 w.id as user_id,
27 w.org_id,
28 s.id as channel_id,
29 'subscribe' role
30 from rhnSharedChannelView s,
31 web_contact w
32 where
33 w.org_id = s.org_trust_id and
34 rhn_channel.user_role_check(s.id, w.id, 'subscribe') = 1;
View created.
SQL>
SQL> --
SQL> --
SQL> -- Revision 1.15 2004/04/28 14:57:02 pjones
SQL> -- bugzilla: 119698 -- Go back to a split version of this, like in
1.13. We
SQL> -- don't need the distinct though; nothing can show up in either
table twice.
SQL> --
SQL> -- Revision 1.13 2004/04/14 15:58:39 pjones
SQL> -- bugzilla: none -- make rhnUserChannel work without org_id...
(duh...)
SQL> --
SQL> -- Revision 1.12 2004/04/14 00:09:24 pjones
SQL> -- bugzilla: 120761 -- split rhnChannelPermissions into two tables,
eliminating
SQL> -- a frequent full table scan
SQL> --
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/121-rhn_channel.pks.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/121-RHN_CHANNEL.PKS.SQ
--------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/121-rhn_channel.pks.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL>
SQL> CREATE OR REPLACE
2 PACKAGE rhn_channel
3 IS
4 version varchar2(100) := '';
5
6 CURSOR server_base_subscriptions(server_id_in NUMBER) IS
7 SELECT C.id
8 FROM rhnChannel C, rhnServerChannel SC
9 WHERE C.id = SC.channel_id
10 AND SC.server_id = server_id_in
11 AND C.parent_channel IS NULL;
12
13 CURSOR check_server_subscription(server_id_in NUMBER,
channel_id_in NUMBER) IS
14 SELECT channel_id
15 FROM rhnServerChannel
16 WHERE server_id = server_id_in
17 AND channel_id = channel_id_in;
18
19 CURSOR check_server_parent_membership(server_id_in NUMBER,
channel_id_in NUMBER) IS
20 SELECT C.id
21 FROM rhnChannel C, rhnServerChannel SC
22 WHERE C.parent_channel = channel_id_in
23 AND C.id = SC.channel_id
24 AND SC.server_id = server_id_in;
25
26 CURSOR channel_family_perm_cursor(channel_family_id_in NUMBER,
org_id_in NUMBER) IS
27 SELECT *
28 FROM rhnOrgChannelFamilyPermissions
29 WHERE channel_family_id = channel_family_id_in
30 AND org_id = org_id_in;
31
32 PROCEDURE license_consent(channel_id_in IN NUMBER, user_id_in
IN NUMBER, server_id_in IN NUMBER);
33 FUNCTION get_license_path(channel_id_in IN NUMBER) RETURN VARCHAR2;
34
35 PROCEDURE unsubscribe_server(server_id_in IN NUMBER,
channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in
number := 0,
36 deleting_server in number := 0 );
37 PROCEDURE subscribe_server(server_id_in IN NUMBER,
channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in number := null);
38
39 function can_server_consume_virt_channl(
40 server_id_in IN NUMBER,
41 family_id_in in number)
42 return number;
43
44 FUNCTION guess_server_base(server_id_in IN NUMBER) RETURN NUMBER;
45
46 FUNCTION base_channel_for_release_arch(release_in in varchar2,
47 server_arch_in in varchar2, org_id_in in number := -1,
48 user_id_in in number := null) RETURN number;
49
50 FUNCTION base_channel_rel_archid(release_in in varchar2,
51 server_arch_id_in in number, org_id_in in number := -1,
52 user_id_in in number := null) RETURN number;
53
54 FUNCTION channel_priority(channel_id_in in number) RETURN number;
55
56 PROCEDURE bulk_subscribe_server(channel_id_in IN NUMBER,
set_label_in IN VARCHAR2, set_uid_in IN NUMBER);
57 PROCEDURE bulk_unsubscribe_server(channel_id_in IN NUMBER,
set_label_in IN VARCHAR2, set_uid_in IN NUMBER);
58
59 PROCEDURE bulk_server_base_change(channel_id_in IN NUMBER,
set_label_in IN VARCHAR2, set_uid_in IN NUMBER);
60 procedure bulk_server_basechange_from(
61 set_label_in in varchar2,
62 set_uid_in in number,
63 old_channel_id_in in number,
64 new_channel_id_in in number);
65
66 procedure bulk_guess_server_base(
67 set_label_in in varchar2,
68 set_uid_in in number);
69
70 procedure bulk_guess_server_base_from(
71 set_label_in in varchar2,
72 set_uid_in in number,
73 channel_id_in in number);
74
75 PROCEDURE clear_subscriptions(server_id_in IN NUMBER,
deleting_server in number := 0 );
76
77 FUNCTION available_family_subscriptions(channel_family_id_in IN
NUMBER, org_id_in IN NUMBER) RETURN NUMBER;
78
79 function channel_family_current_members(channel_family_id_in IN
NUMBER,
80 org_id_in IN NUMBER)
81 return number;
82
83 PROCEDURE update_family_counts(channel_family_id_in IN NUMBER,
org_id_in IN NUMBER);
84 FUNCTION family_for_channel(channel_id_in IN NUMBER) RETURN NUMBER;
85
86 FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER,
org_id_in IN NUMBER) RETURN NUMBER;
87
88 procedure entitle_customer(customer_id_in in number,
channel_family_id_in in number, quantity_in in number);
89 procedure set_family_maxmembers(customer_id_in in number,
channel_family_id_in in number, quantity_in in number);
90 procedure unsubscribe_server_from_family(server_id_in in
number, channel_family_id_in in number);
91
92 procedure delete_server_channels(server_id_in in number);
93 procedure refresh_newest_package(channel_id_in in number,
caller_in in varchar2 := '(unknown)');
94
95 function get_org_id(channel_id_in in number) return number;
96 PRAGMA RESTRICT_REFERENCES(get_org_id, WNDS, RNPS, WNPS);
97
98 function get_org_access(channel_id_in in number, org_id_in in
number) return number;
99 PRAGMA RESTRICT_REFERENCES(get_org_access, WNDS, RNPS, WNPS);
100
101 function get_cfam_org_access(cfam_id_in in number, org_id_in in
number) return number;
102
103 function user_role_check_debug(channel_id_in in number,
user_id_in in number, role_in in varchar2, reason_out out varchar2)
104 RETURN NUMBER;
105 PRAGMA RESTRICT_REFERENCES(user_role_check_debug, WNDS, RNPS,
WNPS);
106
107 function user_role_check(channel_id_in in number, user_id_in in
number, role_in in varchar2)
108 RETURN NUMBER;
109 PRAGMA RESTRICT_REFERENCES(user_role_check, WNDS, RNPS, WNPS);
110
111 function loose_user_role_check(channel_id_in in number,
user_id_in in number, role_in in varchar2)
112 RETURN NUMBER;
113 PRAGMA RESTRICT_REFERENCES(loose_user_role_check, WNDS, RNPS,
WNPS);
114
115 function direct_user_role_check(channel_id_in in number,
user_id_in in number, role_in in varchar2)
116 RETURN NUMBER;
117 PRAGMA RESTRICT_REFERENCES(direct_user_role_check, WNDS, RNPS,
WNPS);
118
119 function shared_user_role_check(channel_id in number, user_id
in number, role in varchar2)
120 RETURN NUMBER;
121 PRAGMA RESTRICT_REFERENCES(shared_user_role_check, WNDS, RNPS,
WNPS);
122
123 function org_channel_setting(channel_id_in in number, org_id_in
in number, setting_in in varchar2)
124 RETURN NUMBER;
125
126 PROCEDURE update_channel ( channel_id_in in number,
invalidate_ss in number := 0,
127 date_to_use in date := sysdate );
128
129 PROCEDURE update_channels_by_package ( package_id_in in
number, date_to_use in date := sysdate );
130
131 PROCEDURE update_channels_by_errata ( errata_id_in number,
date_to_use in date := sysdate );
132
133
134 PRAGMA RESTRICT_REFERENCES(org_channel_setting, WNDS, RNPS, WNPS);
135
136 END rhn_channel;
137 /
Package created.
SQL> SHOW ERRORS
No errors.
SQL>
SQL> --
SQL> -- Revision 1.37 2004/04/14 00:09:24 pjones
SQL> -- bugzilla: 120761 -- split rhnChannelPermissions into two tables,
eliminating
SQL> -- a frequent full table scan
SQL> --
SQL> -- Revision 1.36 2004/03/26 18:11:32 rbb
SQL> -- Bugzilla: 114057
SQL> --
SQL> -- Add a script to determine channel priority.
SQL> --
SQL> -- Revision 1.35 2004/02/17 20:16:52 pjones
SQL> -- bugzilla: none -- add cvs tags into the package as long as we're
touching
SQL> -- it anyway
SQL> --
SQL> -- Revision 1.34 2003/11/13 18:13:09 cturner
SQL> -- pragmas can now return now that rhn_user pragmas are fixed
SQL> --
SQL> -- Revision 1.32 2003/09/22 21:00:40 cturner
SQL> -- add method for easy acl check
SQL> --
SQL> -- Revision 1.31 2003/09/17 22:14:11 misa
SQL> -- bugzilla: 103639 Changes to allow me to move the base channel
guess into plsql
SQL> --
SQL> -- Revision 1.30 2003/07/24 16:44:16 misa
SQL> -- bugzilla: none A function more usable on the rhnapp side
SQL> --
SQL> -- Revision 1.29 2003/07/24 14:00:17 misa
SQL> -- bugzilla: none PRAGMA RESTRICT_REFERENCES good
SQL> --
SQL> -- Revision 1.28 2003/07/23 21:59:19 cturner
SQL> -- rework how rhnUserChannel works; move to plsql for speed and
maintenance
SQL> --
SQL> -- Revision 1.27 2003/07/21 17:49:12 pjones
SQL> -- bugzilla: none
SQL> --
SQL> -- add optional user for subscribe_server
SQL> --
SQL> -- Revision 1.26 2002/12/19 18:13:42 misa
SQL> -- Added caller with a default value
SQL> --
SQL> -- Revision 1.25 2002/12/11 22:18:46 pjones
SQL> -- rhnChannelNewestPackage
SQL> --
SQL> -- Revision 1.24 2002/11/21 22:08:11 pjones
SQL> -- make unsubscribe_channels have a "unsubscribe_children_in number
:= 0"
SQL> -- argument so that you can tell it to unsubscribe children.
SQL> --
SQL> -- Also, make it raise an exception instead of silent failure in the
SQL> -- other case.
SQL> --
SQL> -- Revision 1.23 2002/11/18 17:20:50 pjones
SQL> -- this should have gone back too
SQL> --
SQL> -- Revision 1.22 2002/11/13 23:16:18 pjones
SQL> -- lookup_*_arch()
SQL> --
SQL> -- Revision 1.21 2002/10/07 20:01:59 rnorwood
SQL> -- guess base channel for ssm and single system
SQL> --
SQL> -- Revision 1.20 2002/10/02 19:21:03 bretm
SQL> -- o 3rd party channel schema changes, no more clobs...
SQL> --
SQL> -- Revision 1.19 2002/09/20 19:21:58 bretm
SQL> -- o more 3rd party channel stuff...
SQL> --
SQL> -- Revision 1.18 2002/06/12 22:33:03 pjones
SQL> -- procedure bulk_guess_server_base_from(
SQL> -- set_label_in in varchar2,
SQL> -- set_uid_in in number,
SQL> -- channel_id_in in number);
SQL> --
SQL> -- for bretm
SQL> --
SQL> -- Revision 1.17 2002/06/12 22:12:25 pjones
SQL> -- procedure bulk_guess_server_base(
SQL> -- set_label_in in varchar2,
SQL> -- set_uid_in in number);
SQL> --
SQL> -- for bretm
SQL> --
SQL> -- Revision 1.16 2002/06/12 19:37:55 pjones
SQL> -- bulk_server_basechange_from(
SQL> -- set_label_in in varchar2,
SQL> -- set_uid_in in number,
SQL> -- old_channel_id_in in number,
SQL> -- new_channel_id_in in number
SQL> -- );
SQL> --
SQL> -- for bretm
SQL> --
SQL> -- Revision 1.15 2002/05/10 22:08:22 pjones
SQL> -- id/log
SQL> --
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/122-rhn_channel.pkb.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/122-RHN_CHANNEL.PKB.SQ
--------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/122-rhn_channel.pkb.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL>
SQL> CREATE OR REPLACE
2 PACKAGE BODY rhn_channel
3 IS
4 body_version varchar2(100) := '';
5
6 -- Cursor that fetches all the possible base channels for a
7 -- (server_arch_id, release, org_id) combination
8 cursor base_channel_cursor(
9 release_in in varchar2,
10 server_arch_id_in in number,
11 org_id_in in number
12 ) return rhnChannel%ROWTYPE is
13 select distinct c.*
14 from rhnDistChannelMap dcm,
15 rhnServerChannelArchCompat scac,
16 rhnChannel
c,
17 rhnChannelPermissions cp
18 where cp.org_id = org_id_in
19 and cp.channel_id = c.id
20 and c.parent_channel is null
21 and c.id = dcm.channel_id
22 and c.channel_arch_id = dcm.channel_arch_id
23 and dcm.release = release_in
24 and scac.server_arch_id = server_arch_id_in
25 and scac.channel_arch_id = c.channel_arch_id;
26
27 FUNCTION get_license_path(channel_id_in IN NUMBER)
28 RETURN VARCHAR2
29 IS
30 license_val VARCHAR2(1000);
31 BEGIN
32 SELECT CFL.license_path INTO license_val
33 FROM rhnChannelFamilyLicense CFL, rhnChannelFamilyMembers CFM
34 WHERE CFM.channel_id = channel_id_in
35 AND CFM.channel_family_id = CFL.channel_family_id;
36
37 RETURN license_val;
38
39 EXCEPTION
40 WHEN NO_DATA_FOUND
41 THEN
42 RETURN NULL;
43 END get_license_path;
44
45
46 PROCEDURE license_consent(channel_id_in IN NUMBER, user_id_in
IN NUMBER, server_id_in IN NUMBER)
47 IS
48 channel_family_id_val NUMBER;
49 BEGIN
50 channel_family_id_val :=
rhn_channel.family_for_channel(channel_id_in);
51 IF channel_family_id_val IS NULL
52 THEN
53
rhn_exception.raise_exception('channel_subscribe_no_family');
54 END IF;
55
56 IF rhn_channel.get_license_path(channel_id_in) IS NULL
57 THEN
58
rhn_exception.raise_exception('channel_consent_no_license');
59 END IF;
60
61 INSERT INTO rhnChannelFamilyLicenseConsent
(channel_family_id, user_id, server_id)
62 VALUES (channel_family_id_val, user_id_in, server_id_in);
63 END license_consent;
64
65 PROCEDURE subscribe_server(server_id_in IN NUMBER,
channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in in number :=
null)
66 IS
67 channel_parent_val rhnChannel.parent_channel%TYPE;
68 parent_subscribed BOOLEAN;
69 server_has_base_chan BOOLEAN;
70 server_already_in_chan BOOLEAN;
71 channel_family_id_val NUMBER;
72 server_org_id_val NUMBER;
73 available_subscriptions NUMBER;
74 consenting_user NUMBER;
75 allowed number := 0;
76 current_members_val number;
77 BEGIN
78 if user_id_in is not null then
79 allowed := rhn_channel.user_role_check(channel_id_in,
user_id_in, 'subscribe');
80 else
81 allowed := 1;
82 end if;
83
84 if allowed = 0 then
85 rhn_exception.raise_exception('no_subscribe_permissions');
86 end if;
87
88
89 SELECT parent_channel INTO channel_parent_val FROM
rhnChannel WHERE id = channel_id_in;
90
91 IF channel_parent_val IS NOT NULL
92 THEN
93 -- child channel; if attempting to cross-subscribe a
child to the wrong base, silently ignore
94 parent_subscribed := FALSE;
95
96 FOR check_subscription IN
check_server_subscription(server_id_in, channel_parent_val)
97 LOOP
98 parent_subscribed := TRUE;
99 END LOOP check_subscription;
100
101 IF NOT parent_subscribed
102 THEN
103 RETURN;
104 END IF;
105 ELSE
106 -- base channel
107 server_has_base_chan := FALSE;
108 FOR base IN server_base_subscriptions(server_id_in)
109 LOOP
110 server_has_base_chan := TRUE;
111 END LOOP base;
112
113 IF server_has_base_chan
114 THEN
115
rhn_exception.raise_exception('channel_server_one_base');
116 END IF;
117 END IF;
118
119 FOR check_subscription IN
check_server_subscription(server_id_in, channel_id_in)
120 LOOP
121 server_already_in_chan := TRUE;
122 END LOOP check_subscription;
123
124 IF server_already_in_chan
125 THEN
126 RETURN;
127 END IF;
128
129 channel_family_id_val :=
rhn_channel.family_for_channel(channel_id_in);
130 IF channel_family_id_val IS NULL
131 THEN
132
rhn_exception.raise_exception('channel_subscribe_no_family');
133 END IF;
134
135 --
136 -- Use the org_id of the server only if the org_id of the
channel = NULL.
137 -- This is required for subscribing to shared channels.
138 --
139 SELECT NVL(org_id, (SELECT org_id FROM rhnServer WHERE id =
server_id_in))
140 INTO server_org_id_val
141 FROM rhnChannel
142 WHERE id = channel_id_in;
143
144 select current_members
145 into current_members_val
146 from rhnPrivateChannelFamily
147 where org_id = server_org_id_val and channel_family_id =
channel_family_id_val
148 for update of current_members;
149
150 available_subscriptions :=
rhn_channel.available_family_subscriptions(channel_family_id_val,
server_org_id_val);
151
152 IF available_subscriptions IS NULL OR
153 available_subscriptions > 0 or
154 can_server_consume_virt_channl(server_id_in,
channel_family_id_val) = 1
155 THEN
156
157 IF rhn_channel.get_license_path(channel_id_in) IS NOT NULL
158 THEN
159 BEGIN
160
161 SELECT user_id INTO consenting_user
162 FROM rhnChannelFamilyLicenseConsent
163 WHERE channel_family_id = channel_family_id_val
164 AND server_id = server_id_in;
165
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168
rhn_exception.raise_exception('channel_subscribe_no_consent');
169 END;
170 END IF;
171
172 insert into rhnServerHistory
(id,server_id,summary,details) (
173 select rhn_event_id_seq.nextval,
174 server_id_in,
175 'subscribed to channel ' || SUBSTR(c.label,
0, 106),
176 c.label
177 from rhnChannel c
178 where c.id = channel_id_in
179 );
180 UPDATE rhnServer SET channels_changed = sysdate WHERE
id = server_id_in;
181 INSERT INTO rhnServerChannel (server_id, channel_id)
VALUES (server_id_in, channel_id_in);
182
183 rhn_channel.update_family_counts(channel_family_id_val,
server_org_id_val);
184 queue_server(server_id_in, immediate_in);
185 ELSE
186
rhn_exception.raise_exception('channel_family_no_subscriptions');
187 END IF;
188
189 END subscribe_server;
190
191 function can_server_consume_virt_channl(
192 server_id_in in number,
193 family_id_in in number )
194 return number
195 is
196
197 cursor server_virt_families is
198 select vi.virtual_system_id, cfvsl.channel_family_id
199 from
200 rhnChannelFamilyVirtSubLevel cfvsl,
201 rhnSGTypeVirtSubLevel sgtvsl,
202 rhnVirtualInstance vi
203 where
204 vi.virtual_system_id = server_id_in
205 and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id
206 and cfvsl.channel_family_id = family_id_in
207 and exists (
208 select 1
209 from rhnServerEntitlementView sev
210 where vi.host_system_id = sev.server_id
211 and sev.server_group_type_id =
sgtvsl.server_group_type_id );
212 begin
213
214 for server_virt_family in server_virt_families loop
215 return 1;
216 end loop;
217
218 return 0;
219
220 end;
221
222
223 PROCEDURE bulk_subscribe_server(channel_id_in IN NUMBER,
set_label_in IN VARCHAR2, set_uid_in IN NUMBER)
224 IS
225 BEGIN
226 FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
227 LOOP
228 rhn_channel.subscribe_server(server.element,
channel_id_in, 0, set_uid_in);
229 END LOOP server;
230 END bulk_subscribe_server;
231
232 PROCEDURE bulk_server_base_change(channel_id_in IN NUMBER,
set_label_in IN VARCHAR2, set_uid_in IN NUMBER)
233 IS
234 BEGIN
235 FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
236 LOOP
237 IF rhn_server.can_change_base_channel(server.element) = 1
238 THEN
239
rhn_channel.clear_subscriptions(TO_NUMBER(server.element));
240 rhn_channel.subscribe_server(server.element,
channel_id_in, 0, set_uid_in);
241 END IF;
242 END LOOP server;
243 END bulk_server_base_change;
244
245 procedure bulk_server_basechange_from(
246 set_label_in in varchar2,
247 set_uid_in in number,
248 old_channel_id_in in number,
249 new_channel_id_in in number
250 ) is
251 cursor servers is
252 select sc.server_id id
253 from rhnChannel nc,
254 rhnServerChannelArchCompat scac,
255 rhnServer s,
256 rhnChannel oc,
257 rhnServerChannel sc,
258 rhnSet st
259 where 1=1
260 -- first, find the servers we're looking for.
261 and st.label = set_label_in
262 and st.user_id = set_uid_in
263 and st.element = sc.server_id
264 -- now, filter out anything that's not in the
265 -- old base channel.
266 and sc.channel_id = old_channel_id_in
267 and sc.channel_id = oc.id
268 and oc.parent_channel is null
269 -- now, see if it's compatible with the new base channel
270 and nc.id = new_channel_id_in
271 and nc.parent_channel is null
272 and sc.server_id = s.id
273 and s.server_arch_id = scac.server_arch_id
274 and scac.channel_arch_id = nc.channel_arch_id;
275 begin
276 for s in servers loop
277 insert into rhnSet (
278 user_id, label, element
279 ) values (
280 set_uid_in,
281 set_label_in || 'basechange',
282 s.id
283 );
284 end loop channel;
285 bulk_server_base_change(new_channel_id_in,
286 set_label_in || 'basechange',
287 set_uid_in);
288 delete from rhnSet
289 where label = set_label_in||'basechange'
290 and user_id = set_uid_in;
291 end bulk_server_basechange_from;
292
293 procedure bulk_guess_server_base(
294 set_label_in in varchar2,
295 set_uid_in in number
296 ) is
297 channel_id number;
298 begin
299 for server in rhn_set.set_iterator(set_label_in, set_uid_in)
300 loop
301 -- anything that doesn't work, we just ignore
302 begin
303 if
rhn_server.can_change_base_channel(server.element) = 1
304 then
305 channel_id :=
guess_server_base(TO_NUMBER(server.element));
306
rhn_channel.clear_subscriptions(TO_NUMBER(server.element));
307
rhn_channel.subscribe_server(TO_NUMBER(server.element), channel_id, 0,
set_uid_in);
308 end if;
309 exception when others then
310 null;
311 end;
312 end loop server;
313 end;
314
315 function guess_server_base(
316 server_id_in in number
317 ) RETURN number is
318 cursor server_cursor is
319 select s.server_arch_id, s.release, s.org_id
320 from rhnServer s
321 where s.id = server_id_in;
322 begin
323 for s in server_cursor loop
324 for channel in base_channel_cursor(s.release,
325 s.server_arch_id, s.org_id)
326 loop
327 return channel.id;
328 end loop base_channel_cursor;
329 end loop server_cursor;
330 -- Server not found, or no base channel applies to it
331 return null;
332 end;
333
334 -- Private function
335 function normalize_server_arch(server_arch_in in varchar2)
336 return varchar2
337 deterministic
338 is
339 suffix VARCHAR2(128) := '-redhat-linux';
340 suffix_len NUMBER := length(suffix);
341 begin
342 if server_arch_in is NULL then
343 return NULL;
344 end if;
345 if instr(server_arch_in, '-') > 0
346 then
347 -- Suffix already present
348 return server_arch_in;
349 end if;
350 return server_arch_in || suffix;
351 end normalize_server_arch;
352
353 --
354 --
355 -- Raises:
356 -- server_arch_not_found
357 -- no_subscribe_permissions
358 function base_channel_for_release_arch(
359 release_in in varchar2,
360 server_arch_in in varchar2,
361 org_id_in in number := -1,
362 user_id_in in number := null
363 ) return number is
364 server_arch varchar2(256) :=
normalize_server_arch(server_arch_in);
365 server_arch_id number;
366 begin
367 -- Look up the server arch
368 begin
369 select id
370 into server_arch_id
371 from rhnServerArch
372 where label = server_arch;
373 exception
374 when no_data_found then
375 rhn_exception.raise_exception('server_arch_not_found');
376 end;
377 return base_channel_rel_archid(release_in, server_arch_id,
378 org_id_in, user_id_in);
379 end base_channel_for_release_arch;
380
381 function base_channel_rel_archid(
382 release_in in varchar2,
383 server_arch_id_in in number,
384 org_id_in in number := -1,
385 user_id_in in number := null
386 ) return number is
387 denied_channel_id number := null;
388 valid_org_id number := org_id_in;
389 valid_user_id number := user_id_in;
390 channel_subscribable number;
391 begin
392 if org_id_in = -1 and user_id_in is not null then
393 -- Get the org id from the user id
394 begin
395 select org_id
396 into valid_org_id
397 from web_contact
398 where id = user_id_in;
399 exception
400 when no_data_found then
401 -- User doesn't exist
402 -- XXX Only list public stuff for now
403 valid_user_id := null;
404 valid_org_id := -1;
405 end;
406 end if;
407
408 for c in base_channel_cursor(release_in, server_arch_id_in,
valid_org_id)
409 loop
410 -- This row is a possible match
411 if valid_user_id is null then
412 -- User ID not specified, so no user to channel
permissions to
413 -- check
414 return c.id;
415 end if;
416
417 -- Check user to channel permissions
418 select loose_user_role_check(c.id, user_id_in, 'subscribe')
419 into channel_subscribable
420 from dual;
421
422 if channel_subscribable = 1 then
423 return c.id;
424 end if;
425
426 -- Base channel exists, but is not subscribable; keep
trying
427 denied_channel_id := c.id;
428 end loop base_channel_fetch;
429
430 if denied_channel_id is not null then
431 rhn_exception.raise_exception('no_subscribe_permissions');
432 end if;
433 -- No base channel applies
434 return NULL;
435 end base_channel_rel_archid;
436
437 procedure bulk_guess_server_base_from(
438 set_label_in in varchar2,
439 set_uid_in in number,
440 channel_id_in in number
441 ) is
442 cursor channels(server_id_in in number) is
443 select rsc.channel_id
444 from rhnServerChannel rsc,
445 rhnChannel rc
446 where server_id_in = rsc.server_id
447 and rsc.channel_id = rc.id
448 and rc.parent_channel is null;
449 begin
450 for server in rhn_set.set_iterator(set_label_in, set_uid_in)
451 loop
452 for channel in channels(server.element)
453 loop
454 if channel.channel_id = channel_id_in
455 then
456 insert into rhnSet (user_id, label, element)
values (set_uid_in, set_label_in || 'baseguess', server.element);
457 end if;
458 end loop channel;
459 end loop server;
460 bulk_guess_server_base(set_label_in||'baseguess',set_uid_in);
461 delete from rhnSet where label = set_label_in||'baseguess'
and user_id = set_uid_in;
462 end;
463
464
465 PROCEDURE clear_subscriptions(server_id_in IN NUMBER,
deleting_server IN NUMBER := 0 )
466 IS
467 cursor server_channels(server_id_in in number) is
468 select s.org_id, sc.channel_id, cfm.channel_family_id
469 from rhnServer s,
470 rhnServerChannel sc,
471 rhnChannelFamilyMembers cfm
472 where s.id = server_id_in
473 and s.id = sc.server_id
474 and sc.channel_id = cfm.channel_id;
475 BEGIN
476 for channel in server_channels(server_id_in)
477 loop
478 unsubscribe_server(server_id_in,
channel.channel_id, 1, 1, deleting_server);
479
rhn_channel.update_family_counts(channel.channel_family_id, channel.org_id);
480 end loop channel;
481 END clear_subscriptions;
482
483 PROCEDURE unsubscribe_server(server_id_in IN NUMBER,
channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in
number := 0,
484 deleting_server IN NUMBER := 0 )
485 IS
486 channel_family_id_val NUMBER;
487 server_org_id_val NUMBER;
488 available_subscriptions NUMBER;
489 server_already_in_chan BOOLEAN;
490 cursor channel_family_is_proxy(channel_family_id_in in
number) is
491 select 1
492 from rhnChannelFamily
493 where id = channel_family_id_in
494 and label = 'rhn-proxy';
495 cursor channel_family_is_satellite(channel_family_id_in in
number) is
496 select 1
497 from rhnChannelFamily
498 where id = channel_family_id_in
499 and label = 'rhn-satellite';
500 -- this is *EXACTLY* like check_server_parent_membership,
but if we recurse
501 -- with the package-level one, we get a "cursor already
open", so we need a
502 -- copy on our call stack instead. GROAN.
503 cursor local_chk_server_parent_memb (
504 server_id_in number,
505 channel_id_in number ) is
506 select c.id
507 from rhnChannel c,
508 rhnServerChannel sc
509 where 1=1
510 and c.parent_channel = channel_id_in
511 and c.id = sc.channel_id
512 and sc.server_id = server_id_in;
513 BEGIN
514 FOR child IN local_chk_server_parent_memb(server_id_in,
channel_id_in)
515 LOOP
516 if unsubscribe_children_in = 1 then
517 unsubscribe_server(server_id_in => server_id_in,
518
channel_id_in => child.id,
519
immediate_in => immediate_in,
520
unsubscribe_children_in => unsubscribe_children_in,
521 deleting_server => deleting_server);
522 else
523
rhn_exception.raise_exception('channel_unsubscribe_child_exists');
524 end if;
525 END LOOP child;
526
527 server_already_in_chan := FALSE;
528
529 FOR check_subscription IN
check_server_subscription(server_id_in, channel_id_in)
530 LOOP
531 server_already_in_chan := TRUE;
532 END LOOP check_subscription;
533
534 IF NOT server_already_in_chan
535 THEN
536 RETURN;
537 END IF;
538
539 if deleting_server = 0 then
540
541 insert into rhnServerHistory (id,server_id,summary,details) (
542 select rhn_event_id_seq.nextval,
543 server_id_in,
544 'unsubscribed from channel ' || SUBSTR(c.label, 0, 106),
545 c.label
546 from rhnChannel c
547 where c.id = channel_id_in
548 );
549
550 UPDATE rhnServer SET channels_changed = sysdate WHERE id =
server_id_in;
551 end if;
552
553 DELETE FROM rhnServerChannel WHERE server_id = server_id_in AND
channel_id = channel_id_in;
554
555 if deleting_server = 0 then
556 queue_server(server_id_in, immediate_in);
557 end if;
558
559 channel_family_id_val :=
rhn_channel.family_for_channel(channel_id_in);
560 IF channel_family_id_val IS NULL
561 THEN
562
rhn_exception.raise_exception('channel_unsubscribe_no_family');
563 END IF;
564
565 for ignore in
channel_family_is_satellite(channel_family_id_val) loop
566 delete from rhnSatelliteInfo where server_id =
server_id_in;
567 delete from rhnSatelliteChannelFamily where
server_id = server_id_in;
568 end loop;
569
570 for ignore in
channel_family_is_proxy(channel_family_id_val) loop
571 delete from rhnProxyInfo where server_id =
server_id_in;
572 end loop;
573
574 DELETE FROM rhnChannelFamilyLicenseConsent
575 WHERE channel_family_id = channel_family_id_val
576 AND server_id = server_id_in;
577
578 SELECT org_id INTO server_org_id_val
579 FROM rhnServer
580 WHERE id = server_id_in;
581
582 rhn_channel.update_family_counts(channel_family_id_val,
server_org_id_val);
583 END unsubscribe_server;
584
585 PROCEDURE bulk_unsubscribe_server(channel_id_in IN NUMBER,
set_label_in IN VARCHAR2, set_uid_in IN NUMBER)
586 IS
587 BEGIN
588 FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
589 LOOP
590 rhn_channel.unsubscribe_server(server.element,
channel_id_in, 0);
591 END LOOP server;
592 END bulk_unsubscribe_server;
593
594 FUNCTION family_for_channel(channel_id_in IN NUMBER)
595 RETURN NUMBER
596 IS
597 channel_family_id_val NUMBER;
598 BEGIN
599 SELECT channel_family_id INTO channel_family_id_val
600 FROM rhnChannelFamilyMembers
601 WHERE channel_id = channel_id_in;
602
603 RETURN channel_family_id_val;
604 EXCEPTION
605 WHEN NO_DATA_FOUND
606 THEN
607 RETURN NULL;
608 END family_for_channel;
609
610 FUNCTION available_family_subscriptions(channel_family_id_in IN
NUMBER, org_id_in IN NUMBER)
611 RETURN NUMBER
612 IS
613 cfp channel_family_perm_cursor%ROWTYPE;
614 current_members_val NUMBER;
615 max_members_val NUMBER;
616 found NUMBER;
617 BEGIN
618 IF NOT channel_family_perm_cursor%ISOPEN
619 THEN
620 OPEN channel_family_perm_cursor(channel_family_id_in,
org_id_in);
621 END IF;
622
623 FETCH channel_family_perm_cursor INTO cfp;
624
625 WHILE channel_family_perm_cursor%FOUND
626 LOOP
627 found := 1;
628
629 current_members_val := cfp.current_members;
630 max_members_val := cfp.max_members;
631
632 FETCH channel_family_perm_cursor INTO cfp;
633 END LOOP;
634
635 IF channel_family_perm_cursor%ISOPEN
636 THEN
637 CLOSE channel_family_perm_cursor;
638 END IF;
639
640 -- not found: either the channel fam doesn't have an entry
in cfp, or the org doesn't have access to it.
641 -- either way, there are no available subscriptions
642
643 IF found IS NULL
644 THEN
645 RETURN 0;
646 END IF;
647
648 -- null max members? in that case, pass it on; NULL means
infinite
649 IF max_members_val IS NULL
650 THEN
651 RETURN NULL;
652 END IF;
653
654 -- otherwise, return the delta
655 RETURN max_members_val - current_members_val;
656 END available_family_subscriptions;
657
658 --
*******************************************************************
659 -- FUNCTION: channel_family_current_members
660 -- Calculates and returns the actual count of systems consuming
661 -- physical channel subscriptions.
662 -- Called by: update_family_counts
663 -- rhn_entitlements.repoll_virt_guest_entitlements
664 --
*******************************************************************
665 function channel_family_current_members(channel_family_id_in IN
NUMBER,
666 org_id_in IN NUMBER)
667 return number
668 is
669 current_members_count number := 0;
670 begin
671 select count(sc.server_id)
672 into current_members_count
673 from rhnChannelFamilyMembers cfm,
674 rhnServerChannel sc,
675 rhnServer s
676 where s.org_id = org_id_in
677 and s.id = sc.server_id
678 and cfm.channel_family_id = channel_family_id_in
679 and cfm.channel_id = sc.channel_id
680 and exists (
681 select 1
682 from rhnChannelFamilyServerPhysical cfsp
683 where cfsp.CHANNEL_FAMILY_ID = channel_family_id_in
684 and cfsp.server_id = s.id
685 );
686
687 return current_members_count;
688 end;
689
690 PROCEDURE update_family_counts(channel_family_id_in IN NUMBER,
691 org_id_in IN NUMBER)
692 IS
693 BEGIN
694 update rhnPrivateChannelFamily
695 set current_members = (
696
channel_family_current_members(channel_family_id_in, org_id_in)
697 )
698 where org_id = org_id_in
699 and channel_family_id =
channel_family_id_in;
700 END update_family_counts;
701
702 FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER,
703 org_id_in IN NUMBER)
704 RETURN NUMBER
705 IS
706 channel_family_id_val NUMBER;
707 BEGIN
708 SELECT channel_family_id INTO channel_family_id_val
709 FROM rhnChannelFamilyMembers
710 WHERE channel_id = channel_id_in;
711
712 RETURN rhn_channel.available_family_subscriptions(
713 channel_family_id_val, org_id_in);
714 END available_chan_subscriptions;
715
716 --
*******************************************************************
717 -- PROCEDURE: entitle_customer
718 -- Creates a chan fam bucket, or sets max_members for an
existing bucket
719 -- Called by: rhn_ep.poll_customer_internal
720 -- Calls: set_family_maxmembers + update_family_counts if the row
721 -- already exists, else it creates it in
rhnPrivateChannelFamily.
722 --
*******************************************************************
723 procedure entitle_customer(customer_id_in in number,
724 channel_family_id_in in number,
725 quantity_in in number)
726 is
727 cursor permissions is
728 select 1
729 from rhnPrivateChannelFamily pcf
730 where pcf.org_id = customer_id_in
731 and pcf.channel_family_id =
channel_family_id_in;
732 begin
733 for perm in permissions loop
734 set_family_maxmembers(
735 customer_id_in,
736 channel_family_id_in,
737 quantity_in
738 );
739 rhn_channel.update_family_counts(
740 channel_family_id_in,
741 customer_id_in
742 );
743 return;
744 end loop;
745
746 insert into rhnPrivateChannelFamily pcf (
747 channel_family_id, org_id,
max_members, current_members
748 ) values (
749 channel_family_id_in,
customer_id_in, quantity_in, 0
750 );
751 end;
752
753 --
*******************************************************************
754 -- PROCEDURE: set_family_maxmembers
755 -- Prunes an existing channel family bucket by unsubscribing the
756 -- necessary servers and sets max_members.
757 -- Called by: rhn_channel.entitle_customer
758 -- Calls: unsubscribe_server_from_family
759 --
*******************************************************************
760 procedure set_family_maxmembers(customer_id_in in number,
761 channel_family_id_in in number,
762 quantity_in in number)
763 is
764 cursor servers is
765 select server_id from (
766 select rownum row_number, server_id, modified from (
767 select rcfsp.server_id,
768 rcfsp.modified
769 from rhnChannelFamilyServerPhysical rcfsp
770 where rcfsp.customer_id = customer_id_in
771 and rcfsp.channel_family_id = channel_family_id_in
772 order by modified
773 )
774 where rownum > quantity_in
775 );
776 begin
777 -- prune subscribed servers
778 for server in servers loop
779
rhn_channel.unsubscribe_server_from_family(server.server_id,
780
channel_family_id_in);
781 end loop;
782
783 update rhnPrivateChannelFamily pcf
784 set pcf.max_members = quantity_in
785 where pcf.org_id = customer_id_in
786 and pcf.channel_family_id = channel_family_id_in;
787 end;
788
789 procedure unsubscribe_server_from_family(server_id_in in number,
790 channel_family_id_in
in number)
791 is
792 begin
793 delete
794 from rhnServerChannel rsc
795 where rsc.server_id = server_id_in
796 and channel_id in (
797 select rcfm.channel_id
798 from rhnChannelFamilyMembers rcfm
799 where rcfm.channel_family_id = channel_family_id_in);
800 end;
801
802 function get_org_id(channel_id_in in number)
803 return number
804 is
805 org_id_out number;
806 begin
807 select org_id into org_id_out
808 from rhnChannel
809 where id = channel_id_in;
810
811 return org_id_out;
812 end get_org_id;
813
814 function get_cfam_org_access(cfam_id_in in number, org_id_in in
number)
815 return number
816 is
817 cursor families is
818 select 1
819 from rhnOrgChannelFamilyPermissions cfp
820 where cfp.org_id = org_id_in;
821 begin
822 -- the idea: if we get past this query,
823 -- the user has the role, else catch the exception and return 0
824 for family in families loop
825 return 1;
826 end loop;
827 return 0;
828 end;
829
830 function get_org_access(channel_id_in in number, org_id_in in
number)
831 return number
832 is
833 throwaway number;
834 begin
835 -- the idea: if we get past this query,
836 -- the org has access to the channel, else catch the
exception and return 0
837 select distinct 1 into throwaway
838 from rhnChannelFamilyMembers CFM,
839 rhnOrgChannelFamilyPermissions CFP
840 where cfp.org_id = org_id_in
841 and CFM.channel_family_id = CFP.channel_family_id
842 and CFM.channel_id = channel_id_in
843 and (CFP.max_members > 0 or CFP.max_members is null or
CFP.org_id = 1);
844
845 return 1;
846 exception
847 when no_data_found
848 then
849 return 0;
850 end;
851
852 -- check if a user has a given role, or if such a role is
inferrable
853 function user_role_check_debug(channel_id_in in number,
854 user_id_in in number,
855 role_in in varchar2,
856 reason_out out varchar2)
857 return number
858 is
859 org_id number;
860 begin
861 org_id := rhn_user.get_org_id(user_id_in);
862
863 -- channel might be shared
864 if role_in = 'subscribe' and
865 rhn_channel.shared_user_role_check(channel_id_in,
user_id_in, role_in) = 1 then
866 return 1;
867 end if;
868
869 if role_in = 'manage' and
870 NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id
then
871 reason_out := 'channel_not_owned';
872 return 0;
873 end if;
874
875 if role_in = 'subscribe' and
876 rhn_channel.get_org_access(channel_id_in, org_id) = 0 then
877 reason_out := 'channel_not_available';
878 return 0;
879 end if;
880
881 -- channel admins have all roles
882 if rhn_user.check_role_implied(user_id_in, 'channel_admin')
= 1 then
883 reason_out := 'channel_admin';
884 return 1;
885 end if;
886
887 -- the subscribe permission is inferred
888 -- UNLESS the not_globally_subscribable flag is set
889 if role_in = 'subscribe'
890 then
891 if rhn_channel.org_channel_setting(channel_id_in,
892 org_id,
893 'not_globally_subscribable') = 0 then
894 reason_out := 'globally_subscribable';
895 return 1;
896 end if;
897 end if;
898
899 -- all other roles (manage right now) are explicitly granted
900 reason_out := 'direct_permission';
901 return rhn_channel.direct_user_role_check(channel_id_in,
902 user_id_in, role_in);
903 end;
904
905 -- same as above, but with no OUT param; useful in views, etc
906 function user_role_check(channel_id_in in number, user_id_in in
number, role_in in varchar2)
907 return number
908 is
909 throwaway varchar2(256);
910 begin
911 return rhn_channel.user_role_check_debug(channel_id_in,
user_id_in, role_in, throwaway);
912 end;
913
914 --
915 -- For multiorg phase II, this function simply checks to see if
the user's
916 -- has a trust relationship that includes this channel by id.
917 --
918 function shared_user_role_check(channel_id in number, user_id
in number, role in varchar2)
919 return number
920 is
921 n number;
922 oid number;
923 begin
924 oid := rhn_user.get_org_id(user_id);
925 select 1 into n
926 from rhnSharedChannelView s
927 where s.id = channel_id and s.org_trust_id = oid;
928 return 1;
929 exception
930 when no_data_found then
931 return 0;
932 end;
933
934 -- same as above, but returns 1 if user_id_in is null
935 -- This is useful in queries where user_id is not specified
936 function loose_user_role_check(channel_id_in in number,
user_id_in in number, role_in in varchar2)
937 return number
938 is
939 begin
940 if user_id_in is null then
941 return 1;
942 end if;
943 return user_role_check(channel_id_in, user_id_in, role_in);
944 end loose_user_role_check;
945
946 -- directly checks the table, no inferred permissions
947 function direct_user_role_check(channel_id_in in number,
user_id_in in number, role_in in varchar2)
948 return number
949 is
950 throwaway number;
951 begin
952 -- the idea: if we get past this query, the user has the
role, else catch the exception and return 0
953 select 1 into throwaway
954 from rhnChannelPermissionRole CPR,
955 rhnChannelPermission CP
956 where CP.user_id = user_id_in
957 and CP.channel_id = channel_id_in
958 and CPR.label = role_in
959 and CP.role_id = CPR.id;
960
961 return 1;
962 exception
963 when no_data_found
964 then
965 return 0;
966 end;
967
968 -- check if an org has a certain setting
969 function org_channel_setting(channel_id_in in number, org_id_in
in number, setting_in in varchar2)
970 return number
971 is
972 throwaway number;
973 begin
974 -- the idea: if we get past this query, the org has the
setting, else catch the exception and return 0
975 select 1 into throwaway
976 from rhnOrgChannelSettingsType OCST,
977 rhnOrgChannelSettings OCS
978 where OCS.org_id = org_id_in
979 and OCS.channel_id = channel_id_in
980 and OCST.label = setting_in
981 and OCS.setting_id = OCST.id;
982
983 return 1;
984 exception
985 when no_data_found
986 then
987 return 0;
988 end;
989
990 FUNCTION channel_priority(channel_id_in IN number)
991 RETURN number
992 IS
993 channel_name varchar2(64);
994 priority number;
995 end_of_life_val date;
996 org_id_val number;
997 BEGIN
998
999 select name, end_of_life, org_id
1000 into channel_name, end_of_life_val, org_id_val
1001 from rhnChannel
1002 where id = channel_id_in;
1003
1004 if end_of_life_val is not null then
1005 return -400;
1006 end if;
1007
1008 if channel_name like 'Red Hat Enterprise Linux%' or
channel_name like 'RHEL%' then
1009 priority := 1000;
1010 if channel_name not like '%Beta%' then
1011 priority := priority + 1000;
1012 end if;
1013
1014 priority := priority +
1015 case
1016 when channel_name like '%v. 5%' then 600
1017 when channel_name like '%v. 4%' then 500
1018 when channel_name like '%v. 3%' then 400
1019 when channel_name like '%v. 2%' then 300
1020 when channel_name like '%v. 1%' then 200
1021 else 0
1022 end;
1023
1024 priority := priority +
1025 case
1026 when channel_name like 'Red Hat Enterprise Linux (v.
5%' then 60
1027 when (channel_name like '%AS%' and channel_name not
like '%Extras%') then 50
1028 when (channel_name like '%ES%' and channel_name not
like '%Extras%') then 40
1029 when (channel_name like '%WS%' and channel_name not
like '%Extras%') then 30
1030 when (channel_name like '%Desktop%' and channel_name
not like '%Extras%') then 20
1031 when channel_name like '%Extras%' then 10
1032 else 0
1033 end;
1034
1035 priority := priority +
1036 case
1037 when channel_name like '%)' then 5
1038 else 0
1039 end;
1040
1041 priority := priority +
1042 case
1043 when channel_name like '%32-bit x86%' then 4
1044 when channel_name like '%64-bit Intel Itanium%' then 3
1045 when channel_name like '%64-bit AMD64/Intel EM64T%'
then 2
1046 else 0
1047 end;
1048 elsif channel_name like 'Red Hat Desktop%' then
1049 priority := 900;
1050
1051 if channel_name not like '%Beta%' then
1052 priority := priority + 50;
1053 end if;
1054
1055 priority := priority +
1056 case
1057 when channel_name like '%v. 4%' then 40
1058 when channel_name like '%v. 3%' then 30
1059 when channel_name like '%v. 2%' then 20
1060 when channel_name like '%v. 1%' then 10
1061 else 0
1062 end;
1063
1064 priority := priority +
1065 case
1066 when channel_name like '%32-bit x86%' then 4
1067 when channel_name like '%64-bit Intel Itanium%' then 3
1068 when channel_name like '%64-bit AMD64/Intel EM64T%'
then 2
1069 else 0
1070 end;
1071
1072 elsif org_id_val is not null then
1073 priority := 600;
1074 else
1075 priority := 500;
1076 end if;
1077
1078 return -priority;
1079
1080 end channel_priority;
1081
1082 -- right now this only does the accounting changes; the cascade
1083 -- actually does the rhnServerChannel delete.
1084 procedure delete_server_channels(server_id_in in number)
1085 is
1086 begin
1087 update rhnPrivateChannelFamily
1088 set current_members = current_members -1
1089 where org_id in (
1090 select org_id
1091 from rhnServer
1092 where id = server_id_in
1093 )
1094 and channel_family_id in (
1095 select rcfm.channel_family_id
1096 from rhnChannelFamilyMembers rcfm,
1097 rhnServerChannel rsc
1098 where rsc.server_id = server_id_in
1099 and rsc.channel_id = rcfm.channel_id
1100 and not exists (
1101 select 1
1102 from
1103 rhnChannelFamilyVirtSubLevel cfvsl,
1104 rhnSGTypeVirtSubLevel sgtvsl,
1105 rhnServerEntitlementView sev,
1106 rhnVirtualInstance vi
1107 where
1108 -- system is a virtual instance
1109 vi.virtual_system_id = server_id_in
1110 and vi.host_system_id = sev.server_id
1111 -- system's host has a virt ent
1112 and sev.label in ('virtualization_host',
1113
'virtualization_host_platform')
1114 and sev.server_group_type_id =
1115 sgtvsl.server_group_type_id
1116 -- the host's virt ent grants a cf virt
sub level
1117 and sgtvsl.virt_sub_level_id =
cfvsl.virt_sub_level_id
1118 -- the cf is in that virt sub level
1119 and cfvsl.channel_family_id =
rcfm.channel_family_id
1120 )
1121 );
1122 end;
1123
1124 -- this could certainly be optimized to do updates if needs be
1125 procedure refresh_newest_package(channel_id_in in number,
caller_in in varchar2 := '(unknown)')
1126 is
1127 begin
1128 delete from rhnChannelNewestPackage where
channel_id = channel_id_in;
1129 insert into rhnChannelNewestPackage
1130 ( channel_id, name_id, evr_id, package_id,
package_arch_id )
1131 ( select channel_id,
1132 name_id, evr_id,
1133 package_id,
package_arch_id
1134 from rhnChannelNewestPackageView
1135 where channel_id = channel_id_in
1136 );
1137 insert into rhnChannelNewestPackageAudit
(channel_id, caller)
1138 values (channel_id_in, caller_in);
1139 update rhnChannel
1140 set last_modified = greatest(sysdate,
last_modified + 1/86400)
1141 where id = channel_id_in;
1142 end;
1143
1144 procedure update_channel ( channel_id_in in number,
invalidate_ss in number := 0,
1145 date_to_use in date := sysdate )
1146 is
1147
1148 channel_last_modified date;
1149 last_modified_value date;
1150
1151 cursor snapshots is
1152 select snapshot_id id
1153 from rhnSnapshotChannel
1154 where channel_id = channel_id_in;
1155
1156 begin
1157
1158 select last_modified
1159 into channel_last_modified
1160 from rhnChannel
1161 where id = channel_id_in;
1162
1163 last_modified_value := date_to_use;
1164
1165 if last_modified_value <= channel_last_modified then
1166 last_modified_value := last_modified_value + 1/86400;
1167 end if;
1168
1169 update rhnChannel set last_modified = last_modified_value
1170 where id = channel_id_in;
1171
1172 if invalidate_ss = 1 then
1173 for snapshot in snapshots loop
1174 update rhnSnapshot
1175 set invalid =
lookup_snapshot_invalid_reason('channel_modified')
1176 where id = snapshot.id;
1177 end loop;
1178 end if;
1179
1180 end update_channel;
1181
1182 procedure update_channels_by_package ( package_id_in in number,
date_to_use in date := sysdate )
1183 is
1184
1185 cursor channels is
1186 select channel_id
1187 from rhnChannelPackage
1188 where package_id = package_id_in
1189 order by channel_id;
1190
1191 begin
1192 for channel in channels loop
1193 -- we want to invalidate the snapshot assocated with the
channel when we
1194 -- do this b/c we know we've added or removed or packages
1195 rhn_channel.update_channel ( channel.channel_id, 1,
date_to_use );
1196 end loop;
1197 end update_channels_by_package;
1198
1199
1200 procedure update_channels_by_errata ( errata_id_in number,
date_to_use in date := sysdate )
1201 is
1202
1203 cursor channels is
1204 select channel_id
1205 from rhnChannelErrata
1206 where errata_id = errata_id_in
1207 order by channel_id;
1208
1209 begin
1210 for channel in channels loop
1211 -- we won't invalidate snapshots, b/c just changing the
errata associated with
1212 -- a channel shouldn't invalidate snapshots
1213 rhn_channel.update_channel ( channel.channel_id, 0,
date_to_use );
1214 end loop;
1215 end update_channels_by_errata;
1216
1217 END rhn_channel;
1218 /
Package body created.
SQL> SHOW ERRORS
No errors.
SQL>
SQL> --
SQL> -- Revision 1.75 2005/03/04 00:04:19 jslagle
SQL> -- bz #147617
SQL> -- Made Red Hat Desktop sort a little better.
SQL> --
SQL> -- Revision 1.74 2005/02/22 03:24:47 jslagle
SQL> -- bz #147617
SQL> -- Improve channel_priority function to order channels better.
SQL> --
SQL> -- Revision 1.73 2004/08/16 20:39:30 pjones
SQL> -- bugzilla: 129889 -- make bulk_server_basechange_from() actually
work.
SQL> --
SQL> -- Revision 1.72 2004/04/14 00:09:24 pjones
SQL> -- bugzilla: 120761 -- split rhnChannelPermissions into two tables,
eliminating
SQL> -- a frequent full table scan
SQL> --
SQL> -- Revision 1.71 2004/04/13 16:28:36 bretm
SQL> -- bugzilla: 119871
SQL> --
SQL> -- keep track of rhnServer.channels_changed through the pl/sql fns
SQL> --
SQL> -- Revision 1.70 2004/03/26 18:11:32 rbb
SQL> -- Bugzilla: 114057
SQL> --
SQL> -- Add a script to determine channel priority.
SQL> --
SQL> -- Revision 1.69 2004/02/17 20:16:52 pjones
SQL> -- bugzilla: none -- add cvs tags into the package as long as we're
touching
SQL> -- it anyway
SQL> --
SQL> -- Revision 1.68 2004/02/17 20:05:38 pjones
SQL> -- bugzilla: 115782 -- make bulk_server_basechange_from() filter
out servers
SQL> -- with arches incompatible with the target channel
SQL> --
SQL> -- Revision 1.67 2004/02/06 02:36:10 misa
SQL> -- Changed normalize_server_arch to allow for solaris arches
SQL> --
SQL> -- Revision 1.66 2003/11/13 16:58:34 cturner
SQL> -- make use of new rhn_user.check_role_implied call; pragmas broke,
removed them for now since I have no idea how to fix them
SQL> --
SQL> -- Revision 1.65 2003/10/23 20:26:24 bretm
SQL> -- bugzilla: none
SQL> --
SQL> -- note the channel label when we unsubscribe, too
SQL> --
SQL> -- Revision 1.64 2003/10/15 14:47:17 bretm
SQL> -- bugzilla: none
SQL> --
SQL> -- add the channel label to the server history summary line when we
log a channel change
SQL> --
SQL> -- Revision 1.63 2003/09/24 19:25:56 pjones
SQL> -- this wasn't the right fix, put it back
SQL> --
SQL> -- Revision 1.62 2003/09/24 17:42:19 pjones
SQL> -- bugzilla: none
SQL> --
SQL> -- limit our server base channel guess to channels with available
permissions
SQL> --
SQL> -- Revision 1.61 2003/09/22 21:00:40 cturner
SQL> -- add method for easy acl check
SQL> --
SQL> -- Revision 1.60 2003/09/17 22:14:11 misa
SQL> -- bugzilla: 103639 Changes to allow me to move the base channel
guess into plsql
SQL> --
SQL> -- Revision 1.59 2003/08/21 13:41:17 cturner
SQL> -- bugzilla: 99187. properly test for satellite and proxy in
bulk_guess_server_base; reorg code for better reuse
SQL> --
SQL> -- Revision 1.58 2003/07/24 16:46:22 cturner
SQL> -- bugzilla: 100723, the perm check was returning duplicates, so
now it just calls the function it should have called anyway
SQL> --
SQL> -- Revision 1.57 2003/07/24 16:44:16 misa
SQL> -- bugzilla: none A function more usable on the rhnapp side
SQL> --
SQL> -- Revision 1.56 2003/07/23 22:36:51 cturner
SQL> -- argh, max returns null even when now rows; use distinct. how
revolting
SQL> --
SQL> -- Revision 1.55 2003/07/23 22:01:31 cturner
SQL> -- oops, this one can return multiple rows; eliminate that in a
lazy way
SQL> --
SQL> -- Revision 1.54 2003/07/23 21:59:19 cturner
SQL> -- rework how rhnUserChannel works; move to plsql for speed and
maintenance
SQL> --
SQL> -- Revision 1.53 2003/07/21 17:49:12 pjones
SQL> -- bugzilla: none
SQL> --
SQL> -- add optional user for subscribe_server
SQL> --
SQL> -- Revision 1.52 2003/07/14 22:19:29 misa
SQL> -- bugzilla: none Updating guess_base_channel to work more like
the rhnapp server code
SQL> --
SQL> -- Revision 1.51 2003/06/26 22:09:04 pjones
SQL> -- bugzilla: none
SQL> --
SQL> -- log subscribe and unsubscribe
SQL> --
SQL> -- Revision 1.50 2003/06/05 19:31:15 pjones
SQL> -- bugzilla: 88278 -- make the cursor name smaller
SQL> --
SQL> -- Revision 1.49 2003/06/05 19:18:21 pjones
SQL> -- bugzilla: 88278
SQL> --
SQL> -- unsubscribe_server() opens the package-level cursor when it
invokes itself,
SQL> -- so we're using a local copy instead.
SQL> --
SQL> -- Revision 1.48 2003/06/04 16:41:39 pjones
SQL> -- bugzilla: none
SQL> --
SQL> -- make bulk_guess_server_base() silently ignore unguessables
SQL> --
SQL> -- Revision 1.47 2003/06/04 16:27:03 pjones
SQL> -- bugzilla: 88822
SQL> --
SQL> -- eliminate the last outliers that remove things from channels
without using
SQL> -- unsubscribe_server, I think.
SQL> --
SQL> -- Revision 1.46 2003/06/03 20:49:37 pjones
SQL> -- bugzilla: 88822
SQL> -- unsubscribing from rhn-satellite now clears
rhnSatelliteChannelFamily
SQL> -- for the server in question
SQL> --
SQL> -- Revision 1.45 2003/06/02 20:41:45 pjones
SQL> -- bugzilla: none - fix rhnProxyInfo/rhnSatelliteInfo channel
unsubscribe
SQL> -- problem. Basicly, if you're out of the channel for any reason,
you're
SQL> -- also out of rhnProxyInfo/rhnProxyInfo
SQL> --
SQL> -- Revision 1.44 2003/03/24 15:26:28 pjones
SQL> -- bugzilla: 85812
SQL> --
SQL> -- bulk_server_base_change silently ignores servers that are satellites
SQL> -- or proxies, as requested.
SQL> --
SQL> -- Revision 1.43 2003/02/26 20:28:17 pjones
SQL> -- rhn_channel.update_family_counts() in rhn_channel.entitle_customer()
SQL> -- the old codepath is:
SQL> --
SQL> -- ep ->
SQL> -- rhn_ep.entitlement_run_me() ->
SQL> -- rhn_ep.poll_customer() ->
SQL> -- rhn_channel.entitle_customer()
SQL> --
SQL> -- which doesn't change current_members, even though it may remove
servers
SQL> -- from the family.
SQL> --
SQL> -- There's another bug here: currently, we don't try to order forced
SQL> -- unsubscribes in any way; we just use
SQL> -- rhn_channel.unsubscribe_server_from_family . If there are any child
SQL> -- channel subscriptions, this will leave them subscribed. We
really need
SQL> -- to iterate across the channels again, and subscribe any channel
for which
SQL> -- there are no parent channel subscriptions.
SQL> --
SQL> -- Ugh.
SQL> --
SQL> -- Revision 1.42 2003/01/28 00:19:45 pjones
SQL> -- fix clear_subscriptions; AFAICT, this is only hit on the
SQL> -- bulk_server_base_change / bulk_server_base_guess codepaths,
which puts
SQL> -- it infrequent enough that it could be our "bad count" culprit.
SQL> --
SQL> -- Revision 1.41 2003/01/14 19:51:45 pjones
SQL> -- make setting current_members on rhnChannelFamilyPermissions work
when
SQL> -- a server is in more than one channel in a single family.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/123-rhnServerActionVerify-chg-indexes.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/123-RHNSERVERACTIONVERIFY-CHG-INDE
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/123-rhnServerActionVerify-chg-index
es.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> drop index rhn_sactionvm_sanec_uq;
Index dropped.
SQL> create unique index rhn_sactionvm_sanec_uq
2 on rhnServerActionVerifyMissing(
3 server_id, action_id,
4 package_name_id, package_evr_id, package_arch_id,
5 package_capability_id )
6 tablespace SPACEWALK_DATA
7 storage ( freelists 16 )
8 initrans 32;
Index created.
SQL>
SQL> drop index rhn_sactionvr_sanec_uq;
Index dropped.
SQL> create unique index rhn_sactionvr_sanec_uq
2 on rhnServerActionVerifyResult(
3 server_id, action_id,
4 package_name_id, package_evr_id, package_arch_id,
5 package_capability_id )
6 tablespace SPACEWALK_DATA
7 storage ( freelists 16 )
8 initrans 32;
Index created.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2008/11/03
SQL> -- bugzilla: 456539 -- adding package_arch_id to indexes
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/130-delete_server.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/130-DELETE_SERVER.SQ
------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/130-delete_server.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> -- This deletes a server. All codepaths which delete servers
should hit this
SQL> -- or delete_server_bulk()
SQL>
SQL> create or replace
2 procedure delete_server (
3 server_id_in in number
4 ) is
5 cursor servergroups is
6 select server_id, server_group_id
7 from rhnServerGroupMembers sgm
8 where sgm.server_id = server_id_in;
9 cursor configchannels is
10 select cc.id
11 from rhnConfigChannel cc,
12 rhnConfigChannelType cct,
13 rhnServerConfigChannel scc
14 where 1=1
15 and scc.server_id = server_id_in
16 and scc.config_channel_id = cc.id
17 -- these config channel types are reserved
18 -- for use by a single server, so we don't
19 -- need to check for other servers subscribed
20 and cct.label in
21 ('local_override','server_import')
22 and cct.id = cc.confchan_type_id;
23 type filelistsid_t is table of
rhnServerPreserveFileList.file_list_id%type;
24 filelistsid_c filelistsid_t;
25
26 type probesid_t is table of rhn_check_probe.probe_id%type;
27 probesid_c probesid_t;
28
29 is_virt number := 0;
30 begin
31 rhn_channel.delete_server_channels(server_id_in);
32 -- rhn_channel.clear_subscriptions(server_id_in);
33
34 -- filelists
35 select spfl.file_list_id id bulk collect into filelistsid_c
36 from rhnServerPreserveFileList spfl
37 where spfl.server_id = server_id_in
38 and not exists (
39 select 1
40 from rhnServerPreserveFileList
41 where file_list_id = spfl.file_list_id
42 and server_id != server_id_in
43 union
44 select 1
45 from rhnKickstartPreserveFileList
46 where file_list_id = spfl.file_list_id
47 );
48 if filelistsid_c.first is not null then
49 forall i in filelistsid_c.first..filelistsid_c.last
50 delete from rhnFileList where id = filelistsid_c(i);
51 end if;
52
53 for configchannel in configchannels loop
54 rhn_config.delete_channel(configchannel.id);
55 end loop;
56
57 select count(1) into is_virt
58 from rhnServerEntitlementView
59 where server_id = server_id_in
60 and label in ('virtualization_host',
'virtualization_host_platform')
61 and rownum <= 1;
62
63 for sgm in servergroups loop
64 rhn_server.delete_from_servergroup(
65 sgm.server_id, sgm.server_group_id);
66 end loop;
67
68 if is_virt = 1 then
69 rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
70 end if;
71
72 -- we're handling this instead of letting an "on delete
73 -- set null" do it so that we don't run the risk
74 -- of setting off the triggers and killing us with a
75 -- mutating table
76
77 -- this is merge of two single updates:
78 -- update ... set old_server_id = null where old_server_id
= server_id_in;
79 -- update ... set new_server_id = null where new_server_id
= server_id_in;
80 -- so we scan rhnKickstartSession table only once
81 update rhnKickstartSession
82 set old_server_id = case when old_server_id = server_id_in then
null else old_server_id end,
83 new_server_id = case when new_server_id = server_id_in then
null else new_server_id end
84 where old_server_id = server_id_in
85 or new_server_id = server_id_in;
86
87 rhn_channel.clear_subscriptions(server_id_in,1);
88
89 -- A little complicated here, but the goal is to
90 -- delete records from rhnVirtualInstace only if we don't
91 -- care about them anymore. We don't care about records
92 -- in rhnVirtualInstance if we are deleting the host
93 -- system and the virtual system is already null, or
94 -- vice-versa. We *do* care about them if either the
95 -- host or virtual system is still registered because we
96 -- still want them to show up in the UI.
97 -- If there's a newer row in rhnVirtualInstance with the same
98 -- uuid, this guest must have been re-registered, so we can clean
99 -- this data up.
100
101 delete from rhnVirtualInstance vi
102 where (host_system_id = server_id_in and virtual_system_id
is null)
103 or (virtual_system_id = server_id_in and
host_system_id is null)
104 or (vi.virtual_system_id = server_id_in and
vi.modified < (select max(vi2.modified)
105 from rhnVirtualInstance vi2 where vi2.uuid =
vi.uuid));
106
107 -- this is merge of two single updates:
108 -- update ... set host_system_id = null where
host_system_id = server_id_in;
109 -- update ... set virtual_system_id = null where
virtual_system_id = server_id_in;
110 -- so we scan rhnVirtualInstance table only once
111 update rhnVirtualInstance
112 set host_system_id = case when host_system_id = server_id_in
then null else host_system_id end,
113 virtual_system_id = case when virtual_system_id =
server_id_in then null else virtual_system_id end
114 where host_system_id = server_id_in
115 or virtual_system_id = server_id_in;
116
117 -- this is merge of two single updates:
118 -- update ... set old_host_system_id = null when
old_host_system_id = server_id_in;
119 -- update ... set new_host_system_id = null when
new_host_system_id = server_id_in;
120 -- so we scan rhnVirtualInstanceEventLog table only once
121 update rhnVirtualInstanceEventLog
122 set old_host_system_id = case when old_host_system_id =
server_id_in then null else old_host_system_id end,
123 new_host_system_id = case when new_host_system_id =
server_id_in then null else new_host_system_id end
124 where old_host_system_id = server_id_in
125 or new_host_system_id = server_id_in;
126
127 -- We're deleting everything with a foreign key to rhnServer
128 -- here, now. I'm hoping this will help aleviate our deadlock
129 -- problem.
130
131 delete from rhnActionConfigChannel where server_id = server_id_in;
132 delete from rhnActionConfigRevision where server_id = server_id_in;
133 delete from rhnActionPackageRemovalFailure where server_id =
server_id_in;
134 delete from rhnChannelFamilyLicenseConsent where server_id =
server_id_in;
135 delete from rhnClientCapability where server_id = server_id_in;
136 delete from rhnCpu where server_id = server_id_in;
137 -- there's still a cascade here, because the constraint keeps the
138 -- table locked for too long to rebuild it. Ugh...
139 delete from rhnDevice where server_id = server_id_in;
140 delete from rhnProxyInfo where server_id = server_id_in;
141 delete from rhnRam where server_id = server_id_in;
142 delete from rhnRegToken where server_id = server_id_in;
143 delete from rhnSNPServerQueue where server_id = server_id_in;
144 delete from rhnSatelliteChannelFamily where server_id = server_id_in;
145 delete from rhnSatelliteInfo where server_id = server_id_in;
146 -- this cascades to rhnActionConfigChannel and rhnActionConfigFileName
147 delete from rhnServerAction where server_id = server_id_in;
148 delete from rhnServerActionPackageResult where server_id =
server_id_in;
149 delete from rhnServerActionScriptResult where server_id =
server_id_in;
150 delete from rhnServerActionVerifyResult where server_id =
server_id_in;
151 delete from rhnServerActionVerifyMissing where server_id =
server_id_in;
152 -- counts are handled above. this should be a delete_ function.
153 delete from rhnServerChannel where server_id = server_id_in;
154 delete from rhnServerConfigChannel where server_id = server_id_in;
155 delete from rhnServerCustomDataValue where server_id = server_id_in;
156 delete from rhnServerDMI where server_id = server_id_in;
157 delete from rhnServerMessage where server_id = server_id_in;
158 -- this gets rhnServerMessage (only) on cascade; it's handled just
above
159 delete from rhnServerEvent where server_id = server_id_in;
160 delete from rhnServerHistory where server_id = server_id_in;
161 delete from rhnServerInfo where server_id = server_id_in;
162 delete from rhnServerInstallInfo where server_id = server_id_in;
163 delete from rhnServerLocation where server_id = server_id_in;
164 delete from rhnServerLock where server_id = server_id_in;
165 delete from rhnServerNeededPackageCache where server_id =
server_id_in;
166 delete from rhnServerNeededErrataCache where server_id = server_id_in;
167 delete from rhnServerNetwork where server_id = server_id_in;
168 delete from rhnServerNotes where server_id = server_id_in;
169 -- I'm not removing the foreign key from rhnServerPackage; that'll
170 -- take forever. Do the delete anyway.
171 delete from rhnServerPackage where server_id = server_id_in;
172 delete from rhnServerTokenRegs where server_id = server_id_in;
173 delete from rhnSnapshotTag where server_id = server_id_in;
174 -- this cascades to:
175 -- rhnSnapshotChannel, rhnSnapshotConfigChannel, rhnSnapshotPackage,
176 -- rhnSnapshotConfigRevision, rhnSnapshotServerGroup,
177 -- rhnSnapshotTag.
178 -- We may want to consider delete_snapshot() at some point, but
179 -- I don't think we need to yet.
180 delete from rhnSnapshot where server_id = server_id_in;
181 delete from rhnTransaction where server_id = server_id_in;
182 delete from rhnUserServerPrefs where server_id = server_id_in;
183 -- hrm, this one's interesting... we _probably_ should delete
184 -- everything for the parent server_id when we delete the proxy,
185 -- but we don't currently.
186 delete from rhnServerPath where server_id_in in (server_id,
proxy_server_id);
187 delete from rhnUserServerPerms where server_id = server_id_in;
188
189 delete from rhn_interface_monitoring where server_id = server_id_in;
190 delete from rhnServerNetInterface where server_id = server_id_in;
191 delete from rhn_server_monitoring_info where recid = server_id_in;
192
193 delete from rhnAppInstallSession where server_id = server_id_in;
194 delete from rhnServerUuid where server_id = server_id_in;
195 -- We delete all the probes running directly against this system
196 -- and any probes that were using this Server as a Proxy Scout.
197 SELECT CP.probe_id bulk collect into probesid_c
198 FROM rhn_check_probe CP
199 WHERE CP.host_id = server_id_in
200 OR CP.sat_cluster_id in
201 (SELECT SN.sat_cluster_id
202 FROM rhn_sat_node SN
203 WHERE SN.server_id = server_id_in);
204
205 if probesid_c.first is not null then
206 FORALL i IN probesid_c.first..probesid_c.last
207 DELETE FROM rhn_probe_state PS WHERE PS.probe_id =
probesid_c(i);
208 FORALL i IN probesid_c.first..probesid_c.last
209 DELETE FROM rhn_probe P WHERE P.recid = probesid_c(i);
210 end if;
211
212 delete from rhn_check_probe where host_id = server_id_in;
213 delete from rhn_host_probe where host_id = server_id_in;
214
215 delete from rhn_sat_cluster where recid in
216 ( select sat_cluster_id from rhn_sat_node where server_id =
server_id_in );
217
218 delete from rhn_sat_node where server_id = server_id_in;
219
220 -- now get rhnServer itself.
221 delete
222 from rhnServer
223 where id = server_id_in;
224
225 delete
226 from rhnSet
227 where 1=1
228 and user_id in (
229 select wc.id
230 from rhnServer rs,
231 web_contact wc
232 where rs.id = server_id_in
233 and rs.org_id = wc.org_id
234 )
235 and label = 'system_list'
236 and element = server_id_in;
237 end delete_server;
238 /
Procedure created.
SQL> show errors;
No errors.
SQL>
SQL> --
SQL> -- Revision 1.25 2004/11/01 17:53:03 pjones
SQL> -- bugzilla: 136124 -- Fix the "no data found" when deleting
rhn_sat_cluster
SQL> --
SQL> --
SQL> --
SQL> --
SQL> -- This deletes a list of server.
SQL> --
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/131-delete_server_bulk.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/131-DELETE_SERVER_BULK.SQ
-----------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/131-delete_server_bulk.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- Revision 1.25 2004/11/01 17:53:03 pjones
SQL> -- bugzilla: 136124 -- Fix the "no data found" when deleting
rhn_sat_cluster
SQL> --
SQL> --
SQL> --
SQL> --
SQL> -- This deletes a list of server.
SQL> --
SQL>
SQL> create or replace
2 procedure delete_server_bulk (
3 user_id_in in number
4 ) is
5 cursor systems is
6 select s.element id
7 from rhnSet s
8 where s.user_id = user_id_in
9 and s.label = 'system_list';
10 begin
11 for s in systems loop
12 delete_server(s.id);
13 end loop;
14 end delete_server_bulk;
15 /
Procedure created.
SQL> show errors;
No errors.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/132-rhnActionKickstartGuest-add-kshost.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/132-RHNACTIONKICKSTARTGUEST-ADD-KS
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/132-rhnActionKickstartGuest-add-ksh
ost.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnActionKickstartGuest
2 ADD kickstart_host varchar2(256);
Table altered.
SQL>
SQL> ALTER TABLE rhnActionKickstartGuest
2 ADD disk_path varchar2(256);
Table altered.
SQL>
SQL> ALTER TABLE rhnActionKickstartGuest
2 ADD virt_bridge varchar2(256);
Table altered.
SQL>
SQL> ALTER TABLE rhnActionKickstartGuest
2 ADD cobbler_system_name varchar2(256);
Table altered.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2008/10/29 7:01:05 mmccune
SQL> -- add new kickstart_host for koan usage
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/133-rhnKickstartVirtualizationType-update.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/133-RHNKICKSTARTVIRTUALIZATIONTYPE
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/133-rhnKickstartVirtualizationType-
update.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> update rhnKickstartVirtualizationType
2 set label = 'xenpv' where label = 'para_guest';
1 row updated.
SQL>
SQL> update rhnKickstartVirtualizationType
2 set label = 'auto' where label = 'none';
1 row updated.
SQL>
SQL> update rhnKickstartVirtualizationType
2 set name = 'Auto' where label = 'auto';
1 row updated.
SQL>
SQL> update rhnKickstartVirtualizationType
2 set name = 'XEN Para-Virtualized Guest' where label = 'xenpv';
1 row updated.
SQL>
SQL> insert into rhnKickstartVirtualizationType (id, name, label)
2 values (rhn_kvt_id_seq.nextval, 'KVM Virtualized Guest', 'qemu');
1 row created.
SQL>
SQL> insert into rhnKickstartVirtualizationType (id, name, label)
2 values (rhn_kvt_id_seq.nextval, 'XEN Fully-Virtualized Guest',
'xenfv');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2008/10/29 7:01:05 mmccune
SQL> -- add new kickstart_host for koan usage
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/135-rhn_config_macro_data.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/135-RHN_CONFIG_MACRO_DATA.SQ
--------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/135-rhn_config_macro_data.sql
SQL>
SQL> update rhn_config_macro set definition='/usr/bin' where name='NPBIN';
1 row updated.
SQL> update rhn_config_macro set definition='/etc/nocpulse' where
name='NPETC';
1 row updated.
SQL> update rhn_config_macro set definition='/var/lib/%{USER}' where
name='NPHOME';
1 row updated.
SQL> update rhn_config_macro set definition='/var/log/%{USER}' where
name='NPVAR';
1 row updated.
SQL>
SQL> insert into
rhn_config_macro(environment,name,definition,description,editable,last_update_user,last_update_date)
values ( 'ALL', 'NPLIB', '/var/lib/%{USER}', 'Production user data
directory', '0', 'system',sysdate);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/136-rhn_config_parameter_data.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/136-RHN_CONFIG_PARAMETER_DATA.SQ
------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/136-rhn_config_parameter_data.sql
SQL>
SQL>
SQL> update rhn_config_parameter set
value='/var/lib/notification/queue/ack_queue' where name='ack_queue_dir';
1 row updated.
SQL> update rhn_config_parameter set
value='/var/lib/notification/queue/alert_queue' where
name='alert_queue_dir';
1 row updated.
SQL>
SQL> update rhn_config_parameter set value='/etc/notification' where
group_name='notification' and name='config_dir';
1 row updated.
SQL>
SQL>
SQL> update rhn_config_parameter set
value='%{NPLIB}/trapReceiver/config' where group_name='trapReceiver' and
name='config';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/trapReceiver/mibs'
where group_name='trapReceiver' and name='cust_mibdir';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/trapReceiver/traps'
where group_name='trapReceiver' and name='trapdir';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/ProbeState' where
group_name='ProbeFramework' and name='databaseDirectory';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/.gripes.gdbm' where
group_name='queues' and name='gritchdb';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/queue' where
group_name='queues' and name='queuedir';
1 row updated.
SQL> update rhn_config_parameter set
value='%{NPLIB}/queue/snmp/LAST_SENT' where group_name='queues' and
name='snmplast';
1 row updated.
SQL> update rhn_config_parameter set
value='%{NPLIB}/commands/.gripes.gdbm' where group_name='CommandQueue'
and name='gritchdb';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/commands/heartbeat'
where group_name='CommandQueue' and name='heartbeatFile';
1 row updated.
SQL> update rhn_config_parameter set
value='%{NPLIB}/commands/last_completed' where group_name='CommandQueue'
and name='lastCompletedFile';
1 row updated.
SQL> update rhn_config_parameter set
value='%{NPLIB}/commands/last_started' where group_name='CommandQueue'
and name='lastStartedFile';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/last_state_push'
where group_name='current_state' and name='last_success';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/events.frozen'
where group_name='satellite' and name='eventsFile';
1 row updated.
SQL> update rhn_config_parameter set
value='%{NPLIB}/.gripes-probe-code.gdbm' where group_name='satellite'
and name='gritchdb';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/scheduler.xml'
where group_name='satellite' and name='schedulerConfigFile';
1 row updated.
SQL> update rhn_config_parameter set value='%{NPLIB}/reload.please'
where group_name='satellite' and name='schedulerReloadFlagFile';
1 row updated.
SQL> update rhn_config_parameter set
value='%{NPLIB}/commands/execute_commands.log' where
group_name='CommandQueue' and name='exelog';
1 row updated.
SQL>
SQL> commit;
Commit complete.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/137-rhnKickstartableTree.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/137-RHNKICKSTARTABLETREE.SQ
-------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/137-rhnKickstartableTree.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL>
SQL>
SQL> ALTER TABLE rhnKickstartableTree
2 ADD cobbler_id varchar2(64);
Table altered.
SQL>
SQL> ALTER TABLE rhnKickstartableTree
2 ADD cobbler_xen_id varchar2(64);
Table altered.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2008/12/02 16:51 jsherrill
SQL> -- Add cobbler_id column
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/138-rhnRegTokenPackages.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/138-RHNREGTOKENPACKAGES.SQ
------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/138-rhnRegTokenPackages.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL>
SQL> ALTER TABLE rhnRegTokenPackages ADD id number;
Table altered.
SQL>
SQL> CREATE SEQUENCE rhn_reg_tok_pkg_id_seq;
Sequence created.
SQL>
SQL> UPDATE rhnRegTokenPackages SET id = rhn_reg_tok_pkg_id_seq.nextval;
0 rows updated.
SQL>
SQL> ALTER TABLE rhnRegTokenPackages ADD CONSTRAINT
rhn_reg_tok_pkg_id_nn CHECK ("ID" IS NOT NULL);
Table altered.
SQL>
SQL> ALTER TABLE rhnRegTokenPackages ADD CONSTRAINT
rhn_reg_tok_pkg_id_pk primary key ( id );
Table altered.
SQL>
SQL> ALTER TABLE rhnRegTokenPackages ADD (arch_id number);
Table altered.
SQL>
SQL> ALTER TABLE rhnRegTokenPackages ADD CONSTRAINT
rhn_reg_tok_pkg_aid_fk FOREIGN KEY (arch_id) REFERENCES rhnPackageArch
(id) ON DELETE CASCADE;
Table altered.
SQL>
SQL> DROP index rhn_reg_tok_pkg_uq;
Index dropped.
SQL>
SQL> create unique index rhn_reg_tok_pkg_uq
2 on rhnRegTokenPackages(id, token_id, name_id, arch_id)
3 tablespace SPACEWALK_DATA
4 storage( freelists 16 )
5 initrans 32;
Index created.
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2008/12/04 10:44 pkilambi
SQL> -- Arch support for activation key based package installs
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/139-rhnKickstartableTrees.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/139-RHNKICKSTARTABLETREES.SQ
--------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/139-rhnKickstartableTrees.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> update rhnKickstartableTree set boot_image='spacewalk-koan';
0 rows updated.
SQL>
SQL> ALTER TABLE rhnKickstartableTree
2 MODIFY( boot_image varchar2(128) default('spacewalk-koan'));
Table altered.
SQL>
SQL> show errors
No errors.
SQL> -- $Log$
SQL> -- Revision 1 2008/12/04 6:38 pkilambi
SQL> -- Added support to create boot image...
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/140-rhnActionKickstart-drop-kstree_id.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/140-RHNACTIONKICKSTART-DROP-KSTREE
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/140-rhnActionKickstart-drop-kstree_
id.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnActionKickstart
2 DROP COLUMN kstree_id;
Table altered.
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> --
SQL> -- Revision 1 2008/12/05 6:56:45 paji
SQL> -- Removed the unused kstree_id column
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/141-rhnActionKickstartGuest-drop-kstree_id.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/141-RHNACTIONKICKSTARTGUEST-DROP-K
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/141-rhnActionKickstartGuest-drop-ks
tree_id.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnActionKickstartGuest
2 DROP COLUMN kstree_id;
Table altered.
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> --
SQL> -- Revision 1 2008/12/05 6:56:45 paji
SQL> -- Removed the unused kstree_id column
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/142-rhnSet-add-element3.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/142-RHNSET-ADD-ELEMENT3.SQ
------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/142-rhnSet-add-element3.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> alter table rhnSet add element_three NUMBER;
Table altered.
SQL> alter table rhnSet drop constraint rhn_set_user_label_elem_unq drop
index;
Table altered.
SQL> alter table rhnSet add constraint rhn_set_user_label_elem_unq
2 unique(user_id, label, element, element_two, element_three);
Table altered.
SQL>
SQL>
SQL> -- $Log$
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/143-rhnServerProfilePackage-add-arch.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/143-RHNSERVERPROFILEPACKAGE-ADD-AR
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/143-rhnServerProfilePackage-add-arc
h.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL> alter table rhnServerProfilePackage
2 add package_arch_id number
3 constraint rhn_sprofile_package_fk
4 references rhnPackageArch(id);
Table altered.
SQL>
SQL> show errors
No errors.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/144-rhnSharedChannelTreeView.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/144-RHNSHAREDCHANNELTREEVIEW.SQ
-----------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/144-rhnSharedChannelTreeView.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL>
SQL> CREATE OR REPLACE VIEW RHNSHAREDCHANNELTREEVIEW
2 (
3 ORG_ID,
4 ID,
5 DEPTH,
6 NAME,
7 PADDED_NAME,
8 CHANNEL_ARCH_ID,
9 LAST_MODIFIED,
10 LABEL,
11 PARENT_OR_SELF_LABEL,
12 PARENT_OR_SELF_ID,
13 END_OF_LIFE
14 )
15 AS
16 SELECT * FROM (
17 SELECT
18 C.ORG_TRUST_ID AS ORG_ID,
19 C.ID,
20 1 AS DEPTH,
21 C.NAME,
22 ' '||C.NAME AS PADDED_NAME,
23 C.CHANNEL_ARCH_ID,
24 C.LAST_MODIFIED,
25 C.LABEL,
26 C.LABEL AS PARENT_OR_SELF_LABEL,
27 C.ID AS PARENT_OR_SELF_ID,
28 C.END_OF_LIFE
29 FROM RHNSHAREDCHANNELVIEW C
30 WHERE C.PARENT_CHANNEL IS NULL
31 UNION
32 SELECT
33 C.ORG_TRUST_ID AS ORG_ID,
34 C.ID,
35 2 AS DEPTH,
36 c.name,
37 ''||C.NAME AS PADDED_NAME,
38 C.CHANNEL_ARCH_ID,
39 C.LAST_MODIFIED,
40 C.LABEL,
41 C.LABEL AS PARENT_OR_SELF_LABEL,
42 C.ID AS PARENT_OR_SELF_ID,
43 C.END_OF_LIFE
44 FROM RHNSHAREDCHANNELVIEW PC,
45 RHNSHAREDCHANNELVIEW C
46 WHERE C.PARENT_CHANNEL = PC.ID
47 ) ORDER BY PARENT_OR_SELF_LABEL, PARENT_OR_SELF_ID;
View created.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/145-rhnAvailableChannels.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/145-RHNAVAILABLECHANNELS.SQ
-------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/145-rhnAvailableChannels.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> -- tricky view. it explodes to a full cartesian product when
SQL> -- not queried via org_id, so DO NOT DO THAT :)
SQL>
SQL> create or replace view
2 rhnAvailableChannels
3 (
4 org_id,
5 channel_id,
6 channel_depth,
7 channel_name,
8 channel_arch_id,
9 padded_name,
10 current_members,
11 available_members,
12 last_modified,
13 channel_label,
14 parent_or_self_label,
15 parent_or_self_id
16 )
17 as
18 select
19 ct.org_id,
20 ct.id,
21 CT.depth,
22 CT.name,
23 CT.channel_arch_id,
24 CT.padded_name,
25 (SELECT COUNT(1)
26 FROM rhnServer S
27 WHERE S.org_id = ct.org_id
28 AND EXISTS (SELECT 1 FROM rhnServerChannel WHERE
channel_id = ct.id AND server_id = S.id)),
29 rhn_channel.available_chan_subscriptions(ct.id, ct.org_id),
30 CT.last_modified,
31 CT.label,
32 CT.parent_or_self_label,
33 CT.parent_or_self_id
34 from
35 rhnOrgChannelTreeView CT
36 UNION
37 select
38 ct.org_id,
39 ct.id,
40 CT.depth,
41 CT.name,
42 CT.channel_arch_id,
43 CT.padded_name,
44 (SELECT COUNT(1)
45 FROM rhnServer S
46 WHERE S.org_id = ct.org_id
47 AND EXISTS (SELECT 1 FROM rhnServerChannel WHERE
channel_id = ct.id AND server_id = S.id)),
48 NULL,
49 CT.last_modified,
50 CT.label,
51 CT.parent_or_self_label,
52 CT.parent_or_self_id
53 from
54 rhnSharedChannelTreeView CT
55 /
View created.
SQL>
SQL> --
SQL> --
SQL> -- Revision 1.17 2004/04/14 00:09:24 pjones
SQL> -- bugzilla: 120761 -- split rhnChannelPermissions into two tables,
eliminating
SQL> -- a frequent full table scan
SQL> --
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/146-rhnKSData-add-type.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/146-RHNKSDATA-ADD-TYPE.SQ
-----------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/146-rhnKSData-add-type.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnKsData
2 ADD ks_type varchar(8);
Table altered.
SQL>
SQL> UPDATE rhnKsData SET ks_type = 'wizard';
0 rows updated.
SQL>
SQL> ALTER TABLE rhnKsData
2 MODIFY ks_type CONSTRAINT rhn_ks_type_nn NOT NULL;
Table altered.
SQL>
SQL> ALTER TABLE rhnKsData
2 ADD CONSTRAINT rhn_ks_type_ck CHECK (ks_type in ('wizard', 'raw'));
Table altered.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 2 2008/12/02 16:51:05.2 jsherrill
SQL> -- Add cobbler_id column
SQL> --
SQL> -- Revision 1 2008/10/01 7:01:05 mmccune
SQL> -- Removed the unused name column
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/150-rhn_org.pkb.sql' from
dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/150-RHN_ORG.PKB.SQ
----------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/150-rhn_org.pkb.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL>
SQL> CREATE OR REPLACE
2 PACKAGE BODY rhn_org
3 IS
4 body_version varchar2(100) := '';
5
6 FUNCTION find_server_group_by_type(org_id_in NUMBER,
group_label_in VARCHAR2)
7 RETURN NUMBER
8 IS
9 server_group server_group_by_label%ROWTYPE;
10 BEGIN
11 OPEN server_group_by_label(org_id_in, group_label_in);
12 FETCH server_group_by_label INTO server_group;
13 CLOSE server_group_by_label;
14
15 RETURN server_group.id;
16 END find_server_group_by_type;
17
18 procedure delete_org (
19 org_id_in in number
20 )
21 is
22
23 cursor users is
24 select id
25 from web_contact
26 where org_id = org_id_in;
27
28 cursor servers(org_id_in in number) is
29 select id
30 from rhnServer
31 where org_id = org_id_in;
32
33 cursor config_channels is
34 select id
35 from rhnConfigChannel
36 where org_id = org_id_in;
37
38 begin
39
40 if org_id_in = 1 then
41 rhn_exception.raise_exception('cannot_delete_base_org');
42 end if;
43
44 -- Delete all users.
45 for u in users loop
46 rhn_org.delete_user(u.id, 1);
47 end loop;
48
49 -- Delete all servers.
50 for s in servers(org_id_in) loop
51 delete_server(s.id);
52 end loop;
53
54 -- Delete all config channels.
55 for c in config_channels loop
56 rhn_config.delete_channel(c.id);
57 end loop;
58
59 -- Give the org's entitlements back to the main org.
60 rhn_entitlements.remove_org_entitlements(org_id_in);
61
62 -- Clean up tables where we don't have a cascading delete.
63 delete from rhnChannel where org_id = org_id_in;
64 delete from rhnDailySummaryQueue where org_id = org_id_in;
65 delete from rhnOrgQuota where org_id = org_id_in;
66 delete from rhnOrgInfo where org_id = org_id_in;
67 delete from rhnFileList where org_id = org_id_in;
68 delete from rhnServerGroup where org_id = org_id_in;
69 delete from rhn_check_suites where customer_id = org_id_in;
70 delete from rhn_command_target where customer_id = org_id_in;
71 delete from rhn_contact_groups where customer_id = org_id_in;
72 delete from rhn_notification_formats where customer_id =
org_id_in;
73 delete from rhn_probe where customer_id = org_id_in;
74 delete from rhn_redirects where customer_id = org_id_in;
75 delete from rhn_sat_cluster where customer_id = org_id_in;
76 delete from rhn_schedules where customer_id = org_id_in;
77
78 -- Delete the org.
79 delete from web_customer where id = org_id_in;
80
81 end delete_org;
82
83 procedure delete_user(user_id_in in number, deleting_org in number
:= 0) is
84 cursor is_admin is
85 select 1
86 from rhnUserGroupType ugt,
87 rhnUserGroup ug,
88 rhnUserGroupMembers ugm
89 where ugm.user_id = user_id_in
90 and ugm.user_group_id = ug.id
91 and ug.group_type = ugt.id
92 and ugt.label = 'org_admin';
93 cursor servergroups_needing_admins is
94 select usgp.server_group_id server_group_id
95 from rhnUserServerGroupPerms usgp
96 where 1=1
97 and usgp.user_id = user_id_in
98 and not exists (
99 select 1
100 from rhnUserServerGroupPerms sq_usgp
101 where 1=1
102 and sq_usgp.server_group_id = usgp.server_group_id
103 and sq_usgp.user_id != user_id_in
104 );
105 cursor messages is
106 select message_id id
107 from rhnUserMessage
108 where user_id = user_id_in;
109 users number;
110 our_org_id number;
111 other_users number;
112 other_org_admin number;
113 other_user_id number;
114 begin
115 select wc.org_id
116 into our_org_id
117 from web_contact wc
118 where id = user_id_in;
119
120 -- find any other users
121 begin
122 select id, 1
123 into other_user_id, other_users
124 from web_contact
125 where 1=1
126 and org_id = our_org_id
127 and id != user_id_in
128 and rownum = 1;
129 exception
130 when no_data_found then
131 other_users := 0;
132 end;
133
134 -- now do org admin stuff
135 if other_users != 0 then
136 for ignore in is_admin loop
137 begin
138 select new_ugm.user_id
139 into other_org_admin
140 from rhnUserGroupMembers new_ugm,
141 rhnUserGroupType ugt,
142 rhnUserGroup ug,
143 rhnUserGroupMembers ugm
144 where ugm.user_id = user_id_in
145 and ugm.user_group_id = ug.id
146 and ug.group_type = ugt.id
147 and ugt.label = 'org_admin'
148 and ug.id = new_ugm.user_group_id
149 and new_ugm.user_id != user_id_in
150 and rownum = 1;
151 exception
152 when no_data_found then
153 -- If we're deleting the org, we don't want
to raise
154 -- the exception.
155 if deleting_org = 0 then
156 rhn_exception.raise_exception('cannot_delete_user');
157 end if;
158 end;
159
160 for sg in servergroups_needing_admins loop
161 rhn_user.add_servergroup_perm(other_org_admin,
162 sg.server_group_id);
163 end loop;
164 end loop;
165 end if;
166
167 -- and now things for every user
168 for message in messages loop
169 delete
170 from rhnUserMessage
171 where user_id = user_id_in
172 and message_id = message.id;
173 begin
174 select 1
175 into users
176 from rhnUserMessage
177 where message_id = message.id
178 and rownum = 1;
179 delete
180 from rhnMessage
181 where id = message.id;
182 exception
183 when no_data_found then
184 null;
185 end;
186 end loop;
187 delete from rhn_command_queue_sessions where contact_id = user_id_in;
188 delete from rhn_contact_groups
189 where recid in (
190 select contact_group_id
191 from rhn_contact_group_members
192 where member_contact_method_id in (
193 select recid from rhn_contact_methods
194 where contact_id = user_id_in
195 )
196 )
197 and not exists (
198 select 1
199 from rhn_contact_group_members, rhn_contact_methods
200 where rhn_contact_groups.recid =
rhn_contact_group_members.contact_group_id
201 and rhn_contact_group_members.member_contact_method_id =
rhn_contact_methods.recid
202 and rhn_contact_methods.contact_id <> user_id_in
203 );
204 delete from rhn_contact_methods where contact_id = user_id_in;
205 delete from rhn_redirects where contact_id = user_id_in;
206 delete from rhnUserServerPerms where user_id = user_id_in;
207 delete from rhnAppInstallSession where user_id =
user_id_in;
208 if other_users != 0 then
209 update rhnRegToken
210 set user_id = nvl(other_org_admin, other_user_id)
211 where org_id = our_org_id
212 and user_id = user_id_in;
213 begin
214 delete from web_contact where id = user_id_in;
215 exception
216 when others then
217 rhn_exception.raise_exception('cannot_delete_user');
218 end;
219 -- Just Delete the user
220 else
221 begin
222 delete from web_contact where id = user_id_in;
223 exception
224 when others then
225 rhn_exception.raise_exception('cannot_delete_user');
226 end;
227 end if;
228 return;
229 end delete_user;
230
231 END rhn_org;
232 /
Package body created.
SQL> SHOW ERRORS
No errors.
SQL>
SQL> --
SQL> -- Revision 1.22 2004/10/29 04:50:49 pjones
SQL> -- bugzilla: 135179 -- fix org admin swapping during user deletion
SQL> --
SQL> -- Revision 1.21 2004/07/13 22:46:04 pjones
SQL> -- bugzilla: 125938 -- nothing uses update_errata_cache() any more,
remove it
SQL> --
SQL> -- Revision 1.20 2004/07/02 22:24:38 pjones
SQL> -- bugzilla: none -- typo fix
SQL> --
SQL> -- Revision 1.19 2004/07/02 19:19:02 pjones
SQL> -- bugzilla: 125937 -- use rhn_user to grant roles to users
SQL> --
SQL> -- Revision 1.18 2004/05/28 22:21:36 pjones
SQL> -- bugzilla: none -- update for monitoring schema
SQL> --
SQL> -- Revision 1.17 2004/04/05 16:31:07 pjones
SQL> -- bugzilla: 120032 -- raise "cannot_delete_user" if web_contact or
web_customer
SQL> -- has a cascade problem
SQL> --
SQL> -- Revision 1.16 2004/03/15 17:10:28 pjones
SQL> -- bugzilla: 118244 -- delete servers explicitly while deleting
lone users
SQL> --
SQL> -- Revision 1.15 2004/02/09 17:14:54 pjones
SQL> -- bugzilla: none -- fix log garbage
SQL> --
SQL> -- Revision 1.14 2004/01/22 19:44:42 pjones
SQL> -- bugzilla: 106562 -- fix exceptions on delete_user()
SQL> --
SQL> -- Revision 1.13 2004/01/20 17:00:48 pjones
SQL> -- bugzilla: none -- try to make delete_user() succeed when there are
SQL> -- server groups that have been snapshotted
SQL> --
SQL> -- Revision 1.12 2004/01/14 20:22:03 pjones
SQL> -- bugzilla: 113344 -- no deleting from rhnServerGroup, use the api
instead
SQL> --
SQL> -- Revision 1.11 2003/03/20 17:08:16 pjones
SQL> -- avoid the server group members trigger that updates
last_modified; it'll
SQL> -- cause a mutating table error
SQL> --
SQL> -- Revision 1.10 2003/03/17 16:31:25 pjones
SQL> -- use "on delete set null" where applicable
SQL> --
SQL> -- Revision 1.9 2003/03/15 00:31:07 pjones
SQL> -- bugzilla: none
SQL> --
SQL> -- tested wrong table for perms on a server group
SQL> --
SQL> -- Revision 1.8 2003/03/15 00:23:36 pjones
SQL> -- bugzilla: 83631
SQL> --
SQL> -- working delete_user
SQL> --
SQL> -- Revision 1.7 2003/03/03 23:39:36 pjones
SQL> -- different delete_user; this one might actually work.
SQL> --
SQL> -- Takes about 18 seconds, which seems kindof slow, but isn't
SQL> -- intolerable I don't think...
SQL> --
SQL> -- Revision 1.6 2003/03/02 18:07:00 pjones
SQL> -- make it use marty's test instead of is_satellite(); in the sat env,
SQL> -- marty's test _is_ is_satellite()
SQL> --
SQL> -- Revision 1.5 2003/02/18 16:35:45 pjones
SQL> -- delete_user
SQL> --
SQL> -- Revision 1.4 2002/05/10 22:08:23 pjones
SQL> -- id/log
SQL> --
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/151-rhnServer-add-cobbler_id.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/151-RHNSERVER-ADD-COBBLER_ID.SQ
-----------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/151-rhnServer-add-cobbler_id.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL>
SQL>
SQL> ALTER TABLE rhnServer
2 ADD cobbler_id varchar(64);
Table altered.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2009/01/06 16:51 mmccune
SQL> -- Add cobbler_id column
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/152-rhnKSInstallType-add-generic.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/152-RHNKSINSTALLTYPE-ADD-GENERIC.S
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/152-rhnKSInstallType-add-generic.sq
l
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> insert into rhnKSInstallType (id, label, name)
2 values (rhn_ksinstalltype_id_seq.nextval,
3 'fedora_8','Fedora 8'
4 );
1 row created.
SQL>
SQL> insert into rhnKSInstallType (id, label, name)
2 values (rhn_ksinstalltype_id_seq.nextval,
3 'fedora_10','Fedora 10'
4 );
1 row created.
SQL>
SQL> insert into rhnKSInstallType (id, label, name)
2 values (rhn_ksinstalltype_id_seq.nextval,
3 'generic_rpm','Generic RPM'
4 );
1 row created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> -- $Log$
SQL> -- Revision 1 2009/01/06 16:51 mmccune
SQL> -- Add cobbler_id column
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/153-rhnErrata-increase-advisory.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/153-RHNERRATA-INCREASE-ADVISORY.SQ
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/153-rhnErrata-increase-advisory.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnErrata MODIFY advisory varchar2(37);
Table altered.
SQL> show errors
No errors.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/154-rhnErrataTmp-increase-advisory.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/154-RHNERRATATMP-INCREASE-ADVISORY
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/154-rhnErrataTmp-increase-advisory.
sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnErrataTmp MODIFY advisory varchar2(37);
Table altered.
SQL> show errors
No errors.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/155-rhnPackageCapability-increase-name.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/155-RHNPACKAGECAPABILITY-INCREASE-
--------------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/155-rhnPackageCapability-increase-n
ame.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> ALTER TABLE rhnPackageCapability MODIFY name varchar2(4000);
Table altered.
SQL> show errors
No errors.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20090119-165316-to-spacewalk-schema-0.4.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.3-to-spacewalk-schema-0.4/156-rhnPackage-compat-number.sql'
from dual;
'SPACEWALK-SCHEMA-0.3-TO-SPACEWALK-SCHEMA-0.4/156-RHNPACKAGE-COMPAT-NUMBER.SQ
-----------------------------------------------------------------------------
spacewalk-schema-0.3-to-spacewalk-schema-0.4/156-rhnPackage-compat-number.sql
SQL> --
SQL> -- Copyright (c) 2008 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software,
express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- $Id$
SQL> --
SQL>
SQL> alter TABLE rhnPackage MODIFY (compat number(1));
alter TABLE rhnPackage MODIFY (compat number(1))
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or
scale
Michiel van Es wrote:
>
> Milan Zazrivec wrote:
>> On Tuesday 12 May 2009 13:34:15 Milan Zazrivec wrote:
>>> On Tuesday 12 May 2009 13:25:49 Michiel van Es wrote:
>>>> Hi,
>>>>
>>>> I am running Oracle XE on a diffirent server then the spacewalk server.
>>>> When I run the oracle schema upgrade script, I get:
>>>>
>>>> [root at devmx01 tmp]# /usr/bin/spacewalk-schema-upgrade
>>>> Unknown schema name [no rows selected] found.
>>>>
>>>> I am following the https://fedorahosted.org/spacewalk/wiki/HowToUpgrade
>>>> manual.
>>>>
>>>> What am I doing wrong?
>>> The problem here is that the previous schema upgrade (something -> 0.4)
>>> probably failed.
>> ... in which case you should have a log from the upgrade somewhere in
>> /var/log/spacewalk/schema-upgrade/
>>
>> If that's your case, can you please post the failed part here ...
>>
>> Thanks
>> -Milan
>>
>
> Well I had the bug on https://bugzilla.redhat.com/show_bug.cgi?id=480618
> and manually fixed it.
>
> Hmm there is no logfile written in /var/log/spacewalk/schema-upgrade:
>
> [root at devmx01 tmp]# cd /var/log/spacewalk/schema-upgrade/
> [root at devmx01 schema-upgrade]# ls
> 20081010-123642-begin.log 20081114-142217-end.log
> 20081010-123642-end.log 20081114-142217-script.sql
> 20081010-123642-script.sql
> 20081114-142217-to-spacewalk-schema-0.3.log
> 20081010-123642-to-spacewalk-schema-0.2.log 20090119-165316-begin.log
> 20081114-140312-begin.log 20090119-165316-script.sql
> 20081114-140312-script.sql
> 20090119-165316-to-spacewalk-schema-0.4.log
> 20081114-142217-begin.log
> [root at devmx01 schema-upgrade]# ls -rot
> total 1188
> -rw-r--r-- 1 root 376515 Oct 10 2008 20081010-123642-script.sql
> -rw-r--r-- 1 root 332 Oct 10 2008 20081010-123642-begin.log
> -rw-r--r-- 1 root 476432 Oct 10 2008
> 20081010-123642-to-spacewalk-schema-0.2.log
> -rw-r--r-- 1 root 334 Oct 10 2008 20081010-123642-end.log
> -rw-r--r-- 1 root 4678 Nov 14 14:03 20081114-140312-script.sql
> -rw-r--r-- 1 root 277 Nov 14 14:03 20081114-140312-begin.log
> -rw-r--r-- 1 root 4678 Nov 14 14:22 20081114-142217-script.sql
> -rw-r--r-- 1 root 332 Nov 14 14:22 20081114-142217-begin.log
> -rw-r--r-- 1 root 8232 Nov 14 14:22
> 20081114-142217-to-spacewalk-schema-0.3.log
> -rw-r--r-- 1 root 334 Nov 14 14:22 20081114-142217-end.log
> -rw-r--r-- 1 root 127915 Jan 19 16:53 20090119-165316-script.sql
> -rw-r--r-- 1 root 381 Jan 19 16:53 20090119-165316-begin.log
> -rw-r--r-- 1 root 157806 Jan 19 16:53
> 20090119-165316-to-spacewalk-schema-0.4.log
>
> What can be done now?
>
> Kind regards,
>
> Michiel
>
>> _______________________________________________
>> Spacewalk-list mailing list
>> Spacewalk-list at redhat.com
>> https://www.redhat.com/mailman/listinfo/spacewalk-list
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 260 bytes
Desc: OpenPGP digital signature
URL: <http://listman.redhat.com/archives/spacewalk-list/attachments/20090512/b1ebea0f/attachment.sig>
More information about the Spacewalk-list
mailing list