schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb
| 102
schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
| 103
schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/161-rhn_server.pkb.sql
| 735 +++++
schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/162-rhn_channel.pkb.sql
| 1229 ++++++++++
4 files changed, 2067 insertions(+), 102 deletions(-)
New commits:
commit e0b600c88bfe448868e2a7d5db38ef72b59c7adf
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Mon Nov 30 16:22:33 2009 +0100
add missing package body upgrade scripts
What we were missing were upgrades for following two package bodies:
- rhn_server
- rhn_channel
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/161-rhn_server.pkb.sql
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/161-rhn_server.pkb.sql
new file mode 100644
index 0000000..293bf8b
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/161-rhn_server.pkb.sql
@@ -0,0 +1,735 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+--
+--
+--
+
+create or replace
+package body rhn_server
+is
+ function system_service_level(
+ server_id_in in number,
+ service_level_in in varchar2
+ ) return number is
+
+ cursor ents is
+ select label from rhnServerEntitlementView
+ where server_id = server_id_in;
+
+ retval number := 0;
+
+ begin
+ for ent in ents loop
+ retval := rhn_entitlements.entitlement_grants_service (ent.label,
service_level_in);
+ if retval = 1 then
+ return retval;
+ end if;
+ end loop;
+
+ return retval;
+
+ end system_service_level;
+
+
+ function can_change_base_channel(server_id_in IN NUMBER)
+ return number
+ is
+ throwaway number;
+ begin
+ -- the idea: if we get past this query, the server is
+ -- neither sat nor proxy, so base channel is changeable
+
+ select 1 into throwaway
+ from rhnServer S
+ where S.id = server_id_in
+ and not exists (select 1 from rhnSatelliteInfo SI where SI.server_id = S.id)
+ and not exists (select 1 from rhnProxyInfo PI where PI.server_id = S.id);
+
+ return 1;
+ exception
+ when no_data_found
+ then
+ return 0;
+ end can_change_base_channel;
+
+ procedure set_custom_value(
+ server_id_in in number,
+ user_id_in in number,
+ key_label_in in varchar2,
+ value_in in varchar2
+ ) is
+ key_id_val number;
+ begin
+ select CDK.id into key_id_val
+ from rhnCustomDataKey CDK,
+ rhnServer S
+ where S.id = server_id_in
+ and S.org_id = CDK.org_id
+ and CDK.label = key_label_in;
+
+ begin
+ insert into rhnServerCustomDataValue (server_id, key_id, value, created_by,
last_modified_by)
+ values (server_id_in, key_id_val, value_in, user_id_in, user_id_in);
+ exception
+ when DUP_VAL_ON_INDEX
+ then
+ update rhnServerCustomDataValue
+ set value = value_in,
+ last_modified_by = user_id_in
+ where server_id = server_id_in
+ and key_id = key_id_val;
+ end;
+
+ end set_custom_value;
+
+ function bulk_set_custom_value(
+ key_label_in in varchar2,
+ value_in in varchar2,
+ set_label_in in varchar2,
+ set_uid_in in number
+ )
+ return integer
+ is
+ i integer := 0;
+ begin
+ i := 0;
+ for server in rhn_set.set_iterator(set_label_in, set_uid_in)
+ loop
+ if rhn_server.system_service_level(server.element, 'provisioning') = 1 then
+ rhn_server.set_custom_value(server.element, set_uid_in, key_label_in, value_in);
+ i := i + 1;
+ end if;
+ end loop server;
+ return i;
+ end bulk_set_custom_value;
+
+ procedure bulk_snapshot_tag(
+ org_id_in in number,
+ tagname_in in varchar2,
+ set_label_in in varchar2,
+ set_uid_in in number
+ ) is
+ snapshot_id number;
+ begin
+ for server in rhn_set.set_iterator(set_label_in, set_uid_in)
+ loop
+ if rhn_server.system_service_level(server.element, 'provisioning') = 1 then
+ begin
+ select max(id) into snapshot_id
+ from rhnSnapshot
+ where server_id = server.element;
+ exception
+ when NO_DATA_FOUND then
+ rhn_server.snapshot_server(server.element, 'tagging system: ' ||
tagname_in);
+
+ select max(id) into snapshot_id
+ from rhnSnapshot
+ where server_id = server.element;
+ end;
+
+ -- now have a snapshot_id to work with...
+ begin
+ rhn_server.tag_snapshot(snapshot_id, org_id_in, tagname_in);
+ exception
+ when DUP_VAL_ON_INDEX
+ then
+ -- do nothing, be forgiving...
+ null;
+ end;
+ end if;
+ end loop server;
+ end bulk_snapshot_tag;
+
+ procedure tag_delete(
+ server_id_in in number,
+ tag_id_in in number
+ ) is
+ cursor snapshots is
+ select snapshot_id
+ from rhnSnapshotTag
+ where tag_id = tag_id_in;
+ tag_id_tmp number;
+ begin
+ select id into tag_id_tmp
+ from rhnTag
+ where id = tag_id_in
+ for update;
+
+ delete
+ from rhnSnapshotTag
+ where server_id = server_id_in
+ and tag_id = tag_id_in;
+ for snapshot in snapshots loop
+ return;
+ end loop;
+ delete
+ from rhnTag
+ where id = tag_id_in;
+ end tag_delete;
+
+ procedure tag_snapshot(
+ snapshot_id_in in number,
+ org_id_in in number,
+ tagname_in in varchar2
+ ) is
+ begin
+ insert into rhnSnapshotTag (snapshot_id, server_id, tag_id)
+ select snapshot_id_in, server_id, lookup_tag(org_id_in, tagname_in)
+ from rhnSnapshot
+ where id = snapshot_id_in;
+ end tag_snapshot;
+
+ procedure bulk_snapshot(
+ reason_in in varchar2,
+ set_label_in in varchar2,
+ set_uid_in in number
+ ) is
+ begin
+ for server in rhn_set.set_iterator(set_label_in, set_uid_in)
+ loop
+ if rhn_server.system_service_level(server.element, 'provisioning') = 1
then
+ rhn_server.snapshot_server(server.element, reason_in);
+ end if;
+ end loop server;
+ end bulk_snapshot;
+
+ procedure snapshot_server(
+ server_id_in in number,
+ reason_in in varchar2
+ ) is
+ snapshot_id number;
+ cursor revisions is
+ select distinct
+ cr.id
+ from rhnConfigRevision cr,
+ rhnConfigFileName cfn,
+ rhnConfigFile cf,
+ rhnConfigChannel cc,
+ rhnServerConfigChannel scc
+ where 1=1
+ and scc.server_id = server_id_in
+ and scc.config_channel_id = cc.id
+ and cc.id = cf.config_channel_id
+ and cf.id = cr.config_file_id
+ and cr.id = cf.latest_config_revision_id
+ and cf.config_file_name_id = cfn.id
+ and cf.id = lookup_first_matching_cf(scc.server_id, cfn.path);
+ locked integer;
+ begin
+ select rhn_snapshot_id_seq.nextval into snapshot_id from dual;
+
+ insert into rhnSnapshot (id, org_id, server_id, reason) (
+ select snapshot_id,
+ s.org_id,
+ server_id_in,
+ reason_in
+ from rhnServer s
+ where s.id = server_id_in
+ );
+ insert into rhnSnapshotChannel (snapshot_id, channel_id) (
+ select snapshot_id, sc.channel_id
+ from rhnServerChannel sc
+ where sc.server_id = server_id_in
+ );
+ insert into rhnSnapshotServerGroup (snapshot_id, server_group_id) (
+ select snapshot_id, sgm.server_group_id
+ from rhnServerGroupMembers sgm
+ where sgm.server_id = server_id_in
+ );
+ locked := 0;
+ while true loop
+ begin
+ insert into rhnPackageNEVRA (id, name_id, evr_id, package_arch_id)
+ select rhn_pkgnevra_id_seq.nextval, sp.name_id, sp.evr_id,
sp.package_arch_id
+ from rhnServerPackage sp
+ where sp.server_id = server_id_in
+ and not exists
+ (select 1
+ from rhnPackageNEVRA nevra
+ where nevra.name_id = sp.name_id
+ and nevra.evr_id = sp.evr_id
+ and (nevra.package_arch_id = sp.package_arch_id
+ or (nevra.package_arch_id is null
+ and sp.package_arch_id is null)));
+ exit;
+ exception when dup_val_on_index then
+ if locked = 1 then
+ raise;
+ else
+ lock table rhnPackageNEVRA in exclusive mode;
+ locked := 1;
+ end if;
+ end;
+ end loop;
+ insert into rhnSnapshotPackage (snapshot_id, nevra_id) (
+ select distinct snapshot_id, nevra.id
+ from rhnServerPackage sp, rhnPackageNEVRA nevra
+ where sp.server_id = server_id_in
+ and nevra.name_id = sp.name_id
+ and nevra.evr_id = sp.evr_id
+ and (nevra.package_arch_id = sp.package_arch_id
+ or (nevra.package_arch_id is null
+ and sp.package_arch_id is null))
+ );
+
+ insert into rhnSnapshotConfigChannel ( snapshot_id, config_channel_id ) (
+ select snapshot_id, scc.config_channel_id
+ from rhnServerConfigChannel scc
+ where server_id = server_id_in
+ );
+
+ for revision in revisions loop
+ insert into rhnSnapshotConfigRevision (
+ snapshot_id, config_revision_id
+ ) values (
+ snapshot_id, revision.id
+ );
+ end loop;
+ end snapshot_server;
+
+ procedure remove_action(
+ server_id_in in number,
+ action_id_in in number
+ ) is
+ -- this really wants "nulls last", but 8.1.7.3.0 sucks ass.
+ -- instead, we make a local table that holds our
+ -- list of ids with null prereqs. There's surely a better way
+ -- (an array instead of a table maybe? who knows...)
+ -- but I've got code to do this handy that I can look at ;)
+ cursor chained_actions is
+ select id, prerequisite
+ from rhnAction
+ start with id = action_id_in
+ connect by prior id = prerequisite
+ order by prerequisite desc;
+ cursor sessions is
+ select s.id
+ from rhnKickstartSession s
+ where server_id_in in (s.old_server_id, s.new_server_id)
+ and s.action_id = action_id_in
+ and not exists (
+ select 1
+ from rhnKickstartSessionState ss
+ where ss.id = s.state_id
+ and ss.label in ('failed','complete')
+ );
+ type chain_end_type is table of number index by binary_integer;
+ chain_ends chain_end_type;
+ i number;
+ prereq number := 1;
+ begin
+ select prerequisite
+ into prereq
+ from rhnAction
+ where id = action_id_in;
+
+ if prereq is not null then
+ rhn_exception.raise_exception('action_is_child');
+ end if;
+
+ i := 0;
+ for action in chained_actions loop
+ if action.prerequisite is null then
+ chain_ends(i) := action.id;
+ i := i + 1;
+ else
+ delete from rhnServerAction
+ where server_id = server_id_in
+ and action_id = action.id;
+ end if;
+ end loop;
+ i := chain_ends.first;
+ while i is not null loop
+ delete from rhnServerAction
+ where server_id = server_id_in
+ and action_id = chain_ends(i);
+ i := chain_ends.next(i);
+ end loop;
+ for s in sessions loop
+ update rhnKickstartSession
+ set state_id = (
+ select id
+ from rhnKickstartSessionState
+ where label = 'failed'
+ ),
+ action_id = null
+ where id = s.id;
+ set_ks_session_history_message(s.id, 'failed', 'Kickstart cancelled due to
action removal');
+ end loop;
+ end remove_action;
+
+ function check_user_access(server_id_in in number, user_id_in in number)
+ return number
+ is
+ has_access number;
+ begin
+ -- first check; if this returns no rows, then the server/user are in different orgs,
and we bail
+ select 1 into has_access
+ from rhnServer S,
+ web_contact wc
+ where wc.org_id = s.org_id
+ and s.id = server_id_in
+ and wc.id = user_id_in;
+
+ -- okay, so they're in the same org. if we have an org admin, they get a free pass
+ if rhn_user.check_role(user_id_in, 'org_admin') = 1
+ then
+ return 1;
+ end if;
+
+ select 1 into has_access
+ from rhnServerGroupMembers SGM,
+ rhnUserServerGroupPerms USG
+ where SGM.server_group_id = USG.server_group_id
+ and SGM.server_id = server_id_in
+ and USG.user_id = user_id_in
+ and rownum = 1;
+
+ return 1;
+ exception
+ when no_data_found
+ then
+ return 0;
+ end check_user_access;
+
+ -- *******************************************************************
+ -- FUNCTION: can_server_consume_virt_slot
+ -- Returns 1 if the server id is eligible to consume a virtual slot,
+ -- else returns 0.
+ -- Called by: insert_into_servergroup, delete_from_servergroup
+ -- *******************************************************************
+ function can_server_consume_virt_slot(server_id_in in number,
+ group_type_in in
+ rhnServerGroupType.label%TYPE)
+ return number
+ is
+
+ cursor server_virt_slots is
+ select vi.VIRTUAL_SYSTEM_ID
+ from
+ rhnVirtualInstance vi
+ where
+ -- server id is a virtual instance
+ vi.VIRTUAL_SYSTEM_ID = server_id_in
+ -- server id's host is virt entitled
+ and exists ( select 1
+ from rhnServerEntitlementView sev
+ where vi.HOST_SYSTEM_ID = sev.server_id
+ and sev.label in ('virtualization_host',
+ 'virtualization_host_platform') )
+ -- server id's host also has the ent we want
+ and exists ( select 1
+ from rhnServerEntitlementView sev2
+ where vi.HOST_SYSTEM_ID = sev2.server_id
+ and sev2.label = group_type_in );
+
+ begin
+ for server_virt_slot in server_virt_slots loop
+ return 1;
+ end loop;
+ return 0;
+ end can_server_consume_virt_slot;
+
+
+ procedure insert_into_servergroup (
+ server_id_in in number,
+ server_group_id_in in number
+ ) is
+ used_slots number;
+ max_slots number;
+ org_id number;
+ mgmt_available number;
+ mgmt_upgrade number;
+ mgmt_sgid number;
+ prov_available number;
+ prov_upgrade number;
+ prov_sgid number;
+ group_label rhnServerGroupType.label%TYPE;
+ group_type number;
+ begin
+ -- frist, group_type = null, because it's easy...
+
+ -- this will rowlock the servergroup we're trying to change;
+ -- we probably need to lock the other one, but I think the chances
+ -- of it being a real issue are very small for now...
+ select sg.group_type, sg.org_id, sg.current_members, sg.max_members
+ into group_type, org_id, used_slots, max_slots
+ from rhnServerGroup sg
+ where sg.id = server_group_id_in
+ for update of sg.current_members;
+
+ if group_type is null then
+ if used_slots >= max_slots then
+ rhn_exception.raise_exception('servergroup_max_members');
+ end if;
+
+ insert into rhnServerGroupMembers(
+ server_id, server_group_id
+ ) values (
+ server_id_in, server_group_id_in
+ );
+ update rhnServerGroup
+ set current_members = current_members + 1
+ where id = server_group_id_in;
+
+ rhn_cache.update_perms_for_server_group(server_group_id_in);
+ return;
+ end if;
+
+ -- now for group_type != null
+ --
+ select label
+ into group_label
+ from rhnServerGroupType sgt
+ where sgt.id = group_type;
+
+ -- the naive easy path that gets hit most often and has to be quickest.
+ if group_label in ('sw_mgr_entitled',
+ 'enterprise_entitled',
+ 'monitoring_entitled',
+ 'provisioning_entitled',
+ 'virtualization_host',
+ 'virtualization_host_platform') then
+ if used_slots >= max_slots and
+ (can_server_consume_virt_slot(server_id_in, group_label) != 1)
+ then
+ rhn_exception.raise_exception('servergroup_max_members');
+ end if;
+
+ insert into rhnServerGroupMembers(
+ server_id, server_group_id
+ ) values (
+ server_id_in, server_group_id_in
+ );
+
+ -- Only update current members if the system in consuming a
+ -- physical slot.
+ if can_server_consume_virt_slot(server_id_in, group_label) = 0 then
+ update rhnServerGroup
+ set current_members = current_members + 1
+ where id = server_group_id_in;
+ end if;
+
+ return;
+ end if;
+ end;
+
+ function insert_into_servergroup_maybe (
+ server_id_in in number,
+ server_group_id_in in number
+ ) return number is
+ retval number := 0;
+ cursor servergroups is
+ select s.id server_id,
+ sg.id server_group_id
+ from rhnServerGroup sg,
+ rhnServer s
+ where s.id = server_id_in
+ and sg.id = server_group_id_in
+ and s.org_id = sg.org_id
+ and not exists (
+ select 1
+ from rhnServerGroupMembers sgm
+ where sgm.server_id = s.id
+ and sgm.server_group_id = sg.id
+ );
+ begin
+ for sgm in servergroups loop
+ rhn_server.insert_into_servergroup(sgm.server_id, sgm.server_group_id);
+ retval := retval + 1;
+ end loop;
+ return retval;
+ end insert_into_servergroup_maybe;
+
+ procedure insert_set_into_servergroup (
+ server_group_id_in in number,
+ user_id_in in number,
+ set_label_in in varchar2
+ ) is
+ cursor servers is
+ select st.element id
+ from rhnSet st
+ where st.user_id = user_id_in
+ and st.label = set_label_in
+ and exists (
+ select 1
+ from rhnUserManagedServerGroups umsg
+ where umsg.server_group_id = server_group_id_in
+ and umsg.user_id = user_id_in
+ )
+ and not exists (
+ select 1
+ from rhnServerGroupMembers sgm
+ where sgm.server_id = st.element
+ and sgm.server_group_id = server_group_id_in
+ );
+ begin
+ for s in servers loop
+ rhn_server.insert_into_servergroup(s.id, server_group_id_in);
+ end loop;
+ end insert_set_into_servergroup;
+
+ procedure delete_from_servergroup (
+ server_id_in in number,
+ server_group_id_in in number
+ ) is
+ cursor server_virt_groups is
+ select 1
+ from rhnServerEntitlementVirtual sev
+ where sev.server_id = server_id_in
+ and sev.server_group_id = server_group_id_in;
+
+ oid number;
+ mgmt_sgid number;
+ label rhnServerGroupType.label%TYPE;
+ group_type number;
+ begin
+ begin
+ select sg.group_type, sg.org_id
+ into group_type, oid
+ from rhnServerGroupMembers sgm,
+ rhnServerGroup sg
+ where sg.id = server_group_id_in
+ and sg.id = sgm.server_group_id
+ and sgm.server_id = server_id_in
+ for update of sg.current_members;
+ exception
+ when no_data_found then
+ rhn_exception.raise_exception('server_not_in_group');
+ end;
+
+ -- do group_type is null first
+ if group_type is null then
+ delete from rhnServerGroupMembers
+ where server_group_id = server_group_id_in
+ and server_id = server_id_in;
+ update rhnServerGroup
+ set current_members = current_members - 1
+ where id = server_group_id_in;
+ rhn_cache.update_perms_for_server_group(server_group_id_in);
+ return;
+ end if;
+
+ select sgt.label
+ into label
+ from rhnServerGroupType sgt
+ where sgt.id = group_type;
+
+ if label in ('sw_mgr_entitled',
+ 'enterprise_entitled',
+ 'provisioning_entitled',
+ 'monitoring_entitled',
+ 'virtualization_host',
+ 'virtualization_host_platform') then
+
+ -- Only update current members if the system is consuming
+ -- a physical slot.
+ for server_virt_group in server_virt_groups loop
+ delete from rhnServerGroupMembers
+ where server_group_id = server_group_id_in
+ and server_id = server_id_in;
+ return;
+ end loop;
+
+ delete from rhnServerGroupMembers
+ where server_group_id = server_group_id_in
+ and server_id = server_id_in;
+
+ update rhnServerGroup
+ set current_members = current_members - 1
+ where id = server_group_id_in;
+
+ end if;
+ end;
+
+ procedure delete_set_from_servergroup (
+ server_group_id_in in number,
+ user_id_in in number,
+ set_label_in in varchar2
+ ) is
+ cursor servergroups is
+ select sgm.server_id, sgm.server_group_id
+ from rhnSet st,
+ rhnServerGroupMembers sgm
+ where sgm.server_group_id = server_group_id_in
+ and st.user_id = user_id_in
+ and st.label = set_label_in
+ and sgm.server_id = st.element
+ and exists (
+ select 1
+ from rhnUserManagedServerGroups usgp
+ where usgp.server_group_id = server_group_id_in
+ and usgp.user_id = user_id_in
+ );
+ begin
+ for sgm in servergroups loop
+ rhn_server.delete_from_servergroup(sgm.server_id, server_group_id_in);
+ end loop;
+ end delete_set_from_servergroup;
+
+ procedure clear_servergroup (
+ server_group_id_in in number
+ ) is
+ cursor servers is
+ select sgm.server_id id
+ from rhnServerGroupMembers sgm
+ where sgm.server_group_id = server_group_id_in;
+ begin
+ for s in servers loop
+ rhn_server.delete_from_servergroup(s.id, server_group_id_in);
+ end loop;
+ end clear_servergroup;
+
+ procedure delete_from_org_servergroups (
+ server_id_in in number
+ ) is
+ cursor servergroups is
+ select sgm.server_group_id id
+ from rhnServerGroup sg,
+ rhnServerGroupMembers sgm
+ where sgm.server_id = server_id_in
+ and sgm.server_group_id = sg.id
+ and sg.group_type is null;
+ begin
+ for sg in servergroups loop
+ rhn_server.delete_from_servergroup(server_id_in, sg.id);
+ end loop;
+ end delete_from_org_servergroups;
+
+ function get_ip_address (
+ server_id_in in number
+ ) return varchar2 is
+ cursor interfaces is
+ select name, ip_addr
+ from rhnServerNetInterface
+ where server_id = server_id_in
+ and ip_addr != '127.0.0.1';
+ cursor addresses is
+ select ipaddr ip_addr
+ from rhnServerNetwork
+ where server_id = server_id_in
+ and ipaddr != '127.0.0.1';
+ begin
+ for addr in addresses loop
+ return addr.ip_addr;
+ end loop;
+ for iface in interfaces loop
+ return iface.ip_addr;
+ end loop;
+ return NULL;
+ end get_ip_address;
+end rhn_server;
+/
+SHOW ERRORS
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/162-rhn_channel.pkb.sql
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/162-rhn_channel.pkb.sql
new file mode 100644
index 0000000..028a8e9
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/162-rhn_channel.pkb.sql
@@ -0,0 +1,1229 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+--
+--
+--
+
+CREATE OR REPLACE
+PACKAGE BODY rhn_channel
+IS
+ body_version varchar2(100) := '';
+
+ -- Cursor that fetches all the possible base channels for a
+ -- (server_arch_id, release, org_id) combination
+ cursor base_channel_cursor(
+ release_in in varchar2,
+ server_arch_id_in in number,
+ org_id_in in number
+ ) return rhnChannel%ROWTYPE is
+ select distinct c.*
+ from rhnDistChannelMap dcm,
+ rhnServerChannelArchCompat scac,
+ rhnChannel c,
+ rhnChannelPermissions cp
+ where cp.org_id = org_id_in
+ and cp.channel_id = c.id
+ and c.parent_channel is null
+ and c.id = dcm.channel_id
+ and c.channel_arch_id = dcm.channel_arch_id
+ and dcm.release = release_in
+ and scac.server_arch_id = server_arch_id_in
+ and scac.channel_arch_id = c.channel_arch_id;
+
+ FUNCTION get_license_path(channel_id_in IN NUMBER)
+ RETURN VARCHAR2
+ IS
+ license_val VARCHAR2(1000);
+ BEGIN
+ SELECT CFL.license_path INTO license_val
+ FROM rhnChannelFamilyLicense CFL, rhnChannelFamilyMembers CFM
+ WHERE CFM.channel_id = channel_id_in
+ AND CFM.channel_family_id = CFL.channel_family_id;
+
+ RETURN license_val;
+
+ EXCEPTION
+ WHEN NO_DATA_FOUND
+ THEN
+ RETURN NULL;
+ END get_license_path;
+
+
+ PROCEDURE license_consent(channel_id_in IN NUMBER, user_id_in IN NUMBER, server_id_in
IN NUMBER)
+ IS
+ channel_family_id_val NUMBER;
+ BEGIN
+ channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
+ IF channel_family_id_val IS NULL
+ THEN
+ rhn_exception.raise_exception('channel_subscribe_no_family');
+ END IF;
+
+ IF rhn_channel.get_license_path(channel_id_in) IS NULL
+ THEN
+ rhn_exception.raise_exception('channel_consent_no_license');
+ END IF;
+
+ INSERT INTO rhnChannelFamilyLicenseConsent (channel_family_id, user_id,
server_id)
+ VALUES (channel_family_id_val, user_id_in, server_id_in);
+ END license_consent;
+
+ PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in
NUMBER := 1, user_id_in in number := null, recalcfamily_in NUMBER := 1)
+ IS
+ channel_parent_val rhnChannel.parent_channel%TYPE;
+ parent_subscribed BOOLEAN;
+ server_has_base_chan BOOLEAN;
+ server_already_in_chan BOOLEAN;
+ channel_family_id_val NUMBER;
+ server_org_id_val NUMBER;
+ available_subscriptions NUMBER;
+ consenting_user NUMBER;
+ allowed number := 0;
+ current_members_val number;
+ BEGIN
+ if user_id_in is not null then
+ allowed := rhn_channel.user_role_check(channel_id_in, user_id_in,
'subscribe');
+ else
+ allowed := 1;
+ end if;
+
+ if allowed = 0 then
+ rhn_exception.raise_exception('no_subscribe_permissions');
+ end if;
+
+
+ SELECT parent_channel INTO channel_parent_val FROM rhnChannel WHERE id =
channel_id_in;
+
+ IF channel_parent_val IS NOT NULL
+ THEN
+ -- child channel; if attempting to cross-subscribe a child to the wrong base,
silently ignore
+ parent_subscribed := FALSE;
+
+ FOR check_subscription IN check_server_subscription(server_id_in,
channel_parent_val)
+ LOOP
+ parent_subscribed := TRUE;
+ END LOOP check_subscription;
+
+ IF NOT parent_subscribed
+ THEN
+ RETURN;
+ END IF;
+ ELSE
+ -- base channel
+ server_has_base_chan := FALSE;
+ FOR base IN server_base_subscriptions(server_id_in)
+ LOOP
+ server_has_base_chan := TRUE;
+ END LOOP base;
+
+ IF server_has_base_chan
+ THEN
+ rhn_exception.raise_exception('channel_server_one_base');
+ END IF;
+ END IF;
+
+ FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in)
+ LOOP
+ server_already_in_chan := TRUE;
+ END LOOP check_subscription;
+
+ IF server_already_in_chan
+ THEN
+ RETURN;
+ END IF;
+
+ channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
+ IF channel_family_id_val IS NULL
+ THEN
+ rhn_exception.raise_exception('channel_subscribe_no_family');
+ END IF;
+
+ --
+ -- Use the org_id of the server only if the org_id of the channel = NULL.
+ -- This is required for subscribing to shared channels.
+ --
+ SELECT NVL(org_id, (SELECT org_id FROM rhnServer WHERE id = server_id_in))
+ INTO server_org_id_val
+ FROM rhnChannel
+ WHERE id = channel_id_in;
+
+ select current_members
+ into current_members_val
+ from rhnPrivateChannelFamily
+ where org_id = server_org_id_val and channel_family_id = channel_family_id_val
+ for update of current_members;
+
+ available_subscriptions :=
rhn_channel.available_family_subscriptions(channel_family_id_val, server_org_id_val);
+
+ IF available_subscriptions IS NULL OR
+ available_subscriptions > 0 or
+ can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 1
+ THEN
+
+ IF rhn_channel.get_license_path(channel_id_in) IS NOT NULL
+ THEN
+ BEGIN
+
+ SELECT user_id INTO consenting_user
+ FROM rhnChannelFamilyLicenseConsent
+ WHERE channel_family_id = channel_family_id_val
+ AND server_id = server_id_in;
+
+ EXCEPTION
+ WHEN NO_DATA_FOUND THEN
+
rhn_exception.raise_exception('channel_subscribe_no_consent');
+ END;
+ END IF;
+
+ insert into rhnServerHistory (id,server_id,summary,details) (
+ select rhn_event_id_seq.nextval,
+ server_id_in,
+ 'subscribed to channel ' || SUBSTR(c.label, 0, 106),
+ c.label
+ from rhnChannel c
+ where c.id = channel_id_in
+ );
+ UPDATE rhnServer SET channels_changed = sysdate WHERE id = server_id_in;
+ INSERT INTO rhnServerChannel (server_id, channel_id) VALUES (server_id_in,
channel_id_in);
+ IF recalcfamily_in > 0
+ THEN
+ rhn_channel.update_family_counts(channel_family_id_val,
server_org_id_val);
+ END IF;
+ queue_server(server_id_in, immediate_in);
+ ELSE
+ rhn_exception.raise_exception('channel_family_no_subscriptions');
+ END IF;
+
+ END subscribe_server;
+
+ function can_server_consume_virt_channl(
+ server_id_in in number,
+ family_id_in in number )
+ return number
+ is
+
+ cursor server_virt_families is
+ select vi.virtual_system_id, cfvsl.channel_family_id
+ from
+ rhnChannelFamilyVirtSubLevel cfvsl,
+ rhnSGTypeVirtSubLevel sgtvsl,
+ rhnVirtualInstance vi
+ where
+ vi.virtual_system_id = server_id_in
+ and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id
+ and cfvsl.channel_family_id = family_id_in
+ and exists (
+ select 1
+ from rhnServerEntitlementView sev
+ where vi.host_system_id = sev.server_id
+ and sev.server_group_type_id = sgtvsl.server_group_type_id );
+ begin
+
+ for server_virt_family in server_virt_families loop
+ return 1;
+ end loop;
+
+ return 0;
+
+ end;
+
+
+ PROCEDURE bulk_subscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2,
set_uid_in IN NUMBER)
+ IS
+ BEGIN
+ FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
+ LOOP
+ rhn_channel.subscribe_server(server.element, channel_id_in, 0, set_uid_in);
+ END LOOP server;
+ END bulk_subscribe_server;
+
+ PROCEDURE bulk_server_base_change(channel_id_in IN NUMBER, set_label_in IN VARCHAR2,
set_uid_in IN NUMBER)
+ IS
+ BEGIN
+ FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
+ LOOP
+ IF rhn_server.can_change_base_channel(server.element) = 1
+ THEN
+ rhn_channel.clear_subscriptions(TO_NUMBER(server.element));
+ rhn_channel.subscribe_server(server.element, channel_id_in, 0,
set_uid_in);
+ END IF;
+ END LOOP server;
+ END bulk_server_base_change;
+
+ procedure bulk_server_basechange_from(
+ set_label_in in varchar2,
+ set_uid_in in number,
+ old_channel_id_in in number,
+ new_channel_id_in in number
+ ) is
+ cursor servers is
+ select sc.server_id id
+ from rhnChannel nc,
+ rhnServerChannelArchCompat scac,
+ rhnServer s,
+ rhnChannel oc,
+ rhnServerChannel sc,
+ rhnSet st
+ where 1=1
+ -- first, find the servers we're looking for.
+ and st.label = set_label_in
+ and st.user_id = set_uid_in
+ and st.element = sc.server_id
+ -- now, filter out anything that's not in the
+ -- old base channel.
+ and sc.channel_id = old_channel_id_in
+ and sc.channel_id = oc.id
+ and oc.parent_channel is null
+ -- now, see if it's compatible with the new base channel
+ and nc.id = new_channel_id_in
+ and nc.parent_channel is null
+ and sc.server_id = s.id
+ and s.server_arch_id = scac.server_arch_id
+ and scac.channel_arch_id = nc.channel_arch_id;
+ begin
+ for s in servers loop
+ insert into rhnSet (
+ user_id, label, element
+ ) values (
+ set_uid_in,
+ set_label_in || 'basechange',
+ s.id
+ );
+ end loop channel;
+ bulk_server_base_change(new_channel_id_in,
+ set_label_in || 'basechange',
+ set_uid_in);
+ delete from rhnSet
+ where label = set_label_in||'basechange'
+ and user_id = set_uid_in;
+ end bulk_server_basechange_from;
+
+ procedure bulk_guess_server_base(
+ set_label_in in varchar2,
+ set_uid_in in number
+ ) is
+ channel_id number;
+ begin
+ for server in rhn_set.set_iterator(set_label_in, set_uid_in)
+ loop
+ -- anything that doesn't work, we just ignore
+ begin
+ if rhn_server.can_change_base_channel(server.element) = 1
+ then
+ channel_id := guess_server_base(TO_NUMBER(server.element));
+ rhn_channel.clear_subscriptions(TO_NUMBER(server.element));
+ rhn_channel.subscribe_server(TO_NUMBER(server.element), channel_id,
0, set_uid_in);
+ end if;
+ exception when others then
+ null;
+ end;
+ end loop server;
+ end;
+
+ function guess_server_base(
+ server_id_in in number
+ ) RETURN number is
+ cursor server_cursor is
+ select s.server_arch_id, s.release, s.org_id
+ from rhnServer s
+ where s.id = server_id_in;
+ begin
+ for s in server_cursor loop
+ for channel in base_channel_cursor(s.release,
+ s.server_arch_id, s.org_id)
+ loop
+ return channel.id;
+ end loop base_channel_cursor;
+ end loop server_cursor;
+ -- Server not found, or no base channel applies to it
+ return null;
+ end;
+
+ -- Private function
+ function normalize_server_arch(server_arch_in in varchar2)
+ return varchar2
+ deterministic
+ is
+ suffix VARCHAR2(128) := '-redhat-linux';
+ suffix_len NUMBER := length(suffix);
+ begin
+ if server_arch_in is NULL then
+ return NULL;
+ end if;
+ if instr(server_arch_in, '-') > 0
+ then
+ -- Suffix already present
+ return server_arch_in;
+ end if;
+ return server_arch_in || suffix;
+ end normalize_server_arch;
+
+ --
+ --
+ -- Raises:
+ -- server_arch_not_found
+ -- no_subscribe_permissions
+ function base_channel_for_release_arch(
+ release_in in varchar2,
+ server_arch_in in varchar2,
+ org_id_in in number := -1,
+ user_id_in in number := null
+ ) return number is
+ server_arch varchar2(256) := normalize_server_arch(server_arch_in);
+ server_arch_id number;
+ begin
+ -- Look up the server arch
+ begin
+ select id
+ into server_arch_id
+ from rhnServerArch
+ where label = server_arch;
+ exception
+ when no_data_found then
+ rhn_exception.raise_exception('server_arch_not_found');
+ end;
+ return base_channel_rel_archid(release_in, server_arch_id,
+ org_id_in, user_id_in);
+ end base_channel_for_release_arch;
+
+ function base_channel_rel_archid(
+ release_in in varchar2,
+ server_arch_id_in in number,
+ org_id_in in number := -1,
+ user_id_in in number := null
+ ) return number is
+ denied_channel_id number := null;
+ valid_org_id number := org_id_in;
+ valid_user_id number := user_id_in;
+ channel_subscribable number;
+ begin
+ if org_id_in = -1 and user_id_in is not null then
+ -- Get the org id from the user id
+ begin
+ select org_id
+ into valid_org_id
+ from web_contact
+ where id = user_id_in;
+ exception
+ when no_data_found then
+ -- User doesn't exist
+ -- XXX Only list public stuff for now
+ valid_user_id := null;
+ valid_org_id := -1;
+ end;
+ end if;
+
+ for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id)
+ loop
+ -- This row is a possible match
+ if valid_user_id is null then
+ -- User ID not specified, so no user to channel permissions to
+ -- check
+ return c.id;
+ end if;
+
+ -- Check user to channel permissions
+ select loose_user_role_check(c.id, user_id_in, 'subscribe')
+ into channel_subscribable
+ from dual;
+
+ if channel_subscribable = 1 then
+ return c.id;
+ end if;
+
+ -- Base channel exists, but is not subscribable; keep trying
+ denied_channel_id := c.id;
+ end loop base_channel_fetch;
+
+ if denied_channel_id is not null then
+ rhn_exception.raise_exception('no_subscribe_permissions');
+ end if;
+ -- No base channel applies
+ return NULL;
+ end base_channel_rel_archid;
+
+ procedure bulk_guess_server_base_from(
+ set_label_in in varchar2,
+ set_uid_in in number,
+ channel_id_in in number
+ ) is
+ cursor channels(server_id_in in number) is
+ select rsc.channel_id
+ from rhnServerChannel rsc,
+ rhnChannel rc
+ where server_id_in = rsc.server_id
+ and rsc.channel_id = rc.id
+ and rc.parent_channel is null;
+ begin
+ for server in rhn_set.set_iterator(set_label_in, set_uid_in)
+ loop
+ for channel in channels(server.element)
+ loop
+ if channel.channel_id = channel_id_in
+ then
+ insert into rhnSet (user_id, label, element) values (set_uid_in,
set_label_in || 'baseguess', server.element);
+ end if;
+ end loop channel;
+ end loop server;
+ bulk_guess_server_base(set_label_in||'baseguess',set_uid_in);
+ delete from rhnSet where label = set_label_in||'baseguess' and user_id =
set_uid_in;
+ end;
+
+
+ PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server IN NUMBER := 0
)
+ IS
+ cursor server_channels(server_id_in in number) is
+ select s.org_id, sc.channel_id, cfm.channel_family_id
+ from rhnServer s,
+ rhnServerChannel sc,
+ rhnChannelFamilyMembers cfm
+ where s.id = server_id_in
+ and s.id = sc.server_id
+ and sc.channel_id = cfm.channel_id;
+ BEGIN
+ for channel in server_channels(server_id_in)
+ loop
+ unsubscribe_server(server_id_in, channel.channel_id, 1, 1,
deleting_server);
+ rhn_channel.update_family_counts(channel.channel_family_id,
channel.org_id);
+ end loop channel;
+ END clear_subscriptions;
+
+ PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER,
immediate_in NUMBER := 1, unsubscribe_children_in number := 0,
+ deleting_server IN NUMBER := 0 )
+ IS
+ channel_family_id_val NUMBER;
+ server_org_id_val NUMBER;
+ available_subscriptions NUMBER;
+ server_already_in_chan BOOLEAN;
+ cursor channel_family_is_proxy(channel_family_id_in in number) is
+ select 1
+ from rhnChannelFamily
+ where id = channel_family_id_in
+ and label = 'rhn-proxy';
+ cursor channel_family_is_satellite(channel_family_id_in in number) is
+ select 1
+ from rhnChannelFamily
+ where id = channel_family_id_in
+ and label = 'rhn-satellite';
+ -- this is *EXACTLY* like check_server_parent_membership, but if we recurse
+ -- with the package-level one, we get a "cursor already open", so we
need a
+ -- copy on our call stack instead. GROAN.
+ cursor local_chk_server_parent_memb (
+ server_id_in number,
+ channel_id_in number ) is
+ select c.id
+ from rhnChannel c,
+ rhnServerChannel sc
+ where 1=1
+ and c.parent_channel = channel_id_in
+ and c.id = sc.channel_id
+ and sc.server_id = server_id_in;
+ BEGIN
+ FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in)
+ LOOP
+ if unsubscribe_children_in = 1 then
+ unsubscribe_server(server_id_in => server_id_in,
+ channel_id_in =>
child.id,
+ immediate_in =>
immediate_in,
+ unsubscribe_children_in
=> unsubscribe_children_in,
+ deleting_server => deleting_server);
+ else
+
rhn_exception.raise_exception('channel_unsubscribe_child_exists');
+ end if;
+ END LOOP child;
+
+ server_already_in_chan := FALSE;
+
+ FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in)
+ LOOP
+ server_already_in_chan := TRUE;
+ END LOOP check_subscription;
+
+ IF NOT server_already_in_chan
+ THEN
+ RETURN;
+ END IF;
+
+ if deleting_server = 0 then
+
+ insert into rhnServerHistory (id,server_id,summary,details) (
+ select rhn_event_id_seq.nextval,
+ server_id_in,
+ 'unsubscribed from channel ' || SUBSTR(c.label, 0, 106),
+ c.label
+ from rhnChannel c
+ where c.id = channel_id_in
+ );
+
+ UPDATE rhnServer SET channels_changed = sysdate WHERE id = server_id_in;
+ end if;
+
+ DELETE FROM rhnServerChannel WHERE server_id = server_id_in AND channel_id =
channel_id_in;
+
+ if deleting_server = 0 then
+ queue_server(server_id_in, immediate_in);
+ end if;
+
+ channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
+ IF channel_family_id_val IS NULL
+ THEN
+ rhn_exception.raise_exception('channel_unsubscribe_no_family');
+ END IF;
+
+ for ignore in channel_family_is_satellite(channel_family_id_val) loop
+ delete from rhnSatelliteInfo where server_id = server_id_in;
+ delete from rhnSatelliteChannelFamily where server_id = server_id_in;
+ end loop;
+
+ for ignore in channel_family_is_proxy(channel_family_id_val) loop
+ delete from rhnProxyInfo where server_id = server_id_in;
+ end loop;
+
+ DELETE FROM rhnChannelFamilyLicenseConsent
+ WHERE channel_family_id = channel_family_id_val
+ AND server_id = server_id_in;
+
+ SELECT org_id INTO server_org_id_val
+ FROM rhnServer
+ WHERE id = server_id_in;
+
+ rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val);
+ END unsubscribe_server;
+
+ PROCEDURE bulk_unsubscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2,
set_uid_in IN NUMBER)
+ IS
+ BEGIN
+ FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
+ LOOP
+ rhn_channel.unsubscribe_server(server.element, channel_id_in, 0);
+ END LOOP server;
+ END bulk_unsubscribe_server;
+
+ FUNCTION family_for_channel(channel_id_in IN NUMBER)
+ RETURN NUMBER
+ IS
+ channel_family_id_val NUMBER;
+ BEGIN
+ SELECT channel_family_id INTO channel_family_id_val
+ FROM rhnChannelFamilyMembers
+ WHERE channel_id = channel_id_in;
+
+ RETURN channel_family_id_val;
+ EXCEPTION
+ WHEN NO_DATA_FOUND
+ THEN
+ RETURN NULL;
+ END family_for_channel;
+
+ FUNCTION available_family_subscriptions(channel_family_id_in IN NUMBER, org_id_in IN
NUMBER)
+ RETURN NUMBER
+ IS
+ cfp channel_family_perm_cursor%ROWTYPE;
+ current_members_val NUMBER;
+ max_members_val NUMBER;
+ found NUMBER;
+ BEGIN
+ IF NOT channel_family_perm_cursor%ISOPEN
+ THEN
+ OPEN channel_family_perm_cursor(channel_family_id_in, org_id_in);
+ END IF;
+
+ FETCH channel_family_perm_cursor INTO cfp;
+
+ WHILE channel_family_perm_cursor%FOUND
+ LOOP
+ found := 1;
+
+ current_members_val := cfp.current_members;
+ max_members_val := cfp.max_members;
+
+ FETCH channel_family_perm_cursor INTO cfp;
+ END LOOP;
+
+ IF channel_family_perm_cursor%ISOPEN
+ THEN
+ CLOSE channel_family_perm_cursor;
+ END IF;
+
+ -- not found: either the channel fam doesn't have an entry in cfp, or the org
doesn't have access to it.
+ -- either way, there are no available subscriptions
+
+ IF found IS NULL
+ THEN
+ RETURN 0;
+ END IF;
+
+ -- null max members? in that case, pass it on; NULL means infinite
+ IF max_members_val IS NULL
+ THEN
+ RETURN NULL;
+ END IF;
+
+ -- otherwise, return the delta
+ RETURN max_members_val - current_members_val;
+ END available_family_subscriptions;
+
+ -- *******************************************************************
+ -- FUNCTION: channel_family_current_members
+ -- Calculates and returns the actual count of systems consuming
+ -- physical channel subscriptions.
+ -- Called by: update_family_counts
+ -- rhn_entitlements.repoll_virt_guest_entitlements
+ -- *******************************************************************
+ function channel_family_current_members(channel_family_id_in IN NUMBER,
+ org_id_in IN NUMBER)
+ return number
+ is
+ current_members_count number := 0;
+ begin
+ select count(distinct server_id)
+ into current_members_count
+ from rhnChannelFamilyServerPhysical cfsp
+ where cfsp.channel_family_id = channel_family_id_in
+ and cfsp.customer_id = org_id_in;
+ return current_members_count;
+ end;
+
+ PROCEDURE update_family_counts(channel_family_id_in IN NUMBER,
+ org_id_in IN NUMBER)
+ IS
+ BEGIN
+ update rhnPrivateChannelFamily
+ set current_members = (
+ channel_family_current_members(channel_family_id_in, org_id_in)
+ )
+ where org_id = org_id_in
+ and channel_family_id = channel_family_id_in;
+
+ END update_family_counts;
+
+ FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER,
+ org_id_in IN NUMBER)
+ RETURN NUMBER
+ IS
+ channel_family_id_val NUMBER;
+ BEGIN
+ SELECT channel_family_id INTO channel_family_id_val
+ FROM rhnChannelFamilyMembers
+ WHERE channel_id = channel_id_in;
+
+ RETURN rhn_channel.available_family_subscriptions(
+ channel_family_id_val, org_id_in);
+ END available_chan_subscriptions;
+
+ -- *******************************************************************
+ -- PROCEDURE: entitle_customer
+ -- Creates a chan fam bucket, or sets max_members for an existing bucket
+ -- Called by: rhn_ep.poll_customer_internal
+ -- Calls: set_family_maxmembers + update_family_counts if the row
+ -- already exists, else it creates it in rhnPrivateChannelFamily.
+ -- *******************************************************************
+ procedure entitle_customer(customer_id_in in number,
+ channel_family_id_in in number,
+ quantity_in in number)
+ is
+ cursor permissions is
+ select 1
+ from rhnPrivateChannelFamily pcf
+ where pcf.org_id = customer_id_in
+ and pcf.channel_family_id = channel_family_id_in;
+ begin
+ for perm in permissions loop
+ set_family_maxmembers(
+ customer_id_in,
+ channel_family_id_in,
+ quantity_in
+ );
+ rhn_channel.update_family_counts(
+ channel_family_id_in,
+ customer_id_in
+ );
+ return;
+ end loop;
+
+ insert into rhnPrivateChannelFamily pcf (
+ channel_family_id, org_id, max_members, current_members
+ ) values (
+ channel_family_id_in, customer_id_in, quantity_in, 0
+ );
+ end;
+
+ -- *******************************************************************
+ -- PROCEDURE: set_family_maxmembers
+ -- Prunes an existing channel family bucket by unsubscribing the
+ -- necessary servers and sets max_members.
+ -- Called by: rhn_channel.entitle_customer
+ -- Calls: unsubscribe_server_from_family
+ -- *******************************************************************
+ procedure set_family_maxmembers(customer_id_in in number,
+ channel_family_id_in in number,
+ quantity_in in number)
+ is
+ cursor servers is
+ select server_id from (
+ select rownum row_number, server_id, modified from (
+ select rcfsp.server_id,
+ rcfsp.modified
+ from rhnChannelFamilyServerPhysical rcfsp
+ where rcfsp.customer_id = customer_id_in
+ and rcfsp.channel_family_id = channel_family_id_in
+ order by modified
+ )
+ where rownum > quantity_in
+ );
+ begin
+ -- prune subscribed servers
+ for server in servers loop
+ rhn_channel.unsubscribe_server_from_family(server.server_id,
+ channel_family_id_in);
+ end loop;
+
+ update rhnPrivateChannelFamily pcf
+ set pcf.max_members = quantity_in
+ where pcf.org_id = customer_id_in
+ and pcf.channel_family_id = channel_family_id_in;
+ end;
+
+ procedure unsubscribe_server_from_family(server_id_in in number,
+ channel_family_id_in in number)
+ is
+ begin
+ delete
+ from rhnServerChannel rsc
+ where rsc.server_id = server_id_in
+ and channel_id in (
+ select rcfm.channel_id
+ from rhnChannelFamilyMembers rcfm
+ where rcfm.channel_family_id = channel_family_id_in);
+ end;
+
+ function get_org_id(channel_id_in in number)
+ return number
+ is
+ org_id_out number;
+ begin
+ select org_id into org_id_out
+ from rhnChannel
+ where id = channel_id_in;
+
+ return org_id_out;
+ end get_org_id;
+
+ function get_cfam_org_access(cfam_id_in in number, org_id_in in number)
+ return number
+ is
+ cursor families is
+ select 1
+ from rhnOrgChannelFamilyPermissions cfp
+ where cfp.org_id = org_id_in;
+ begin
+ -- the idea: if we get past this query,
+ -- the user has the role, else catch the exception and return 0
+ for family in families loop
+ return 1;
+ end loop;
+ return 0;
+ end;
+
+ function get_org_access(channel_id_in in number, org_id_in in number)
+ return number
+ is
+ throwaway number;
+ begin
+ -- the idea: if we get past this query,
+ -- the org has access to the channel, else catch the exception and return 0
+ select distinct 1 into throwaway
+ from rhnChannelFamilyMembers CFM,
+ rhnOrgChannelFamilyPermissions CFP
+ where cfp.org_id = org_id_in
+ and CFM.channel_family_id = CFP.channel_family_id
+ and CFM.channel_id = channel_id_in
+ and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1);
+
+ return 1;
+ exception
+ when no_data_found
+ then
+ return 0;
+ end;
+
+ -- check if a user has a given role, or if such a role is inferrable
+ function user_role_check_debug(channel_id_in in number,
+ user_id_in in number,
+ role_in in varchar2,
+ reason_out out varchar2)
+ return number
+ is
+ org_id number;
+ begin
+ org_id := rhn_user.get_org_id(user_id_in);
+
+ -- channel might be shared
+ if role_in = 'subscribe' and
+ rhn_channel.shared_user_role_check(channel_id_in, user_id_in, role_in) = 1
then
+ return 1;
+ end if;
+
+ if role_in = 'manage' and
+ NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then
+ reason_out := 'channel_not_owned';
+ return 0;
+ end if;
+
+ if role_in = 'subscribe' and
+ rhn_channel.get_org_access(channel_id_in, org_id) = 0 then
+ reason_out := 'channel_not_available';
+ return 0;
+ end if;
+
+ -- channel admins have all roles
+ if rhn_user.check_role_implied(user_id_in, 'channel_admin') = 1 then
+ reason_out := 'channel_admin';
+ return 1;
+ end if;
+
+ -- the subscribe permission is inferred
+ -- UNLESS the not_globally_subscribable flag is set
+ if role_in = 'subscribe'
+ then
+ if rhn_channel.org_channel_setting(channel_id_in,
+ org_id,
+ 'not_globally_subscribable') = 0 then
+ reason_out := 'globally_subscribable';
+ return 1;
+ end if;
+ end if;
+
+ -- all other roles (manage right now) are explicitly granted
+ reason_out := 'direct_permission';
+ return rhn_channel.direct_user_role_check(channel_id_in,
+ user_id_in, role_in);
+ end;
+
+ -- same as above, but with no OUT param; useful in views, etc
+ function user_role_check(channel_id_in in number, user_id_in in number, role_in in
varchar2)
+ return number
+ is
+ throwaway varchar2(256);
+ begin
+ return rhn_channel.user_role_check_debug(channel_id_in, user_id_in, role_in,
throwaway);
+ end;
+
+ --
+ -- For multiorg phase II, this function simply checks to see if the user's
+ -- has a trust relationship that includes this channel by id.
+ --
+ function shared_user_role_check(channel_id in number, user_id in number, role in
varchar2)
+ return number
+ is
+ n number;
+ oid number;
+ begin
+ oid := rhn_user.get_org_id(user_id);
+ select 1 into n
+ from rhnSharedChannelView s
+ where s.id = channel_id and s.org_trust_id = oid;
+ return 1;
+ exception
+ when no_data_found then
+ return 0;
+ end;
+
+ -- same as above, but returns 1 if user_id_in is null
+ -- This is useful in queries where user_id is not specified
+ function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in
in varchar2)
+ return number
+ is
+ begin
+ if user_id_in is null then
+ return 1;
+ end if;
+ return user_role_check(channel_id_in, user_id_in, role_in);
+ end loose_user_role_check;
+
+ -- directly checks the table, no inferred permissions
+ function direct_user_role_check(channel_id_in in number, user_id_in in number,
role_in in varchar2)
+ return number
+ is
+ throwaway number;
+ begin
+ -- the idea: if we get past this query, the user has the role, else catch the
exception and return 0
+ select 1 into throwaway
+ from rhnChannelPermissionRole CPR,
+ rhnChannelPermission CP
+ where CP.user_id = user_id_in
+ and CP.channel_id = channel_id_in
+ and CPR.label = role_in
+ and CP.role_id = CPR.id;
+
+ return 1;
+ exception
+ when no_data_found
+ then
+ return 0;
+ end;
+
+ -- check if an org has a certain setting
+ function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in
in varchar2)
+ return number
+ is
+ throwaway number;
+ begin
+ -- the idea: if we get past this query, the org has the setting, else catch the
exception and return 0
+ select 1 into throwaway
+ from rhnOrgChannelSettingsType OCST,
+ rhnOrgChannelSettings OCS
+ where OCS.org_id = org_id_in
+ and OCS.channel_id = channel_id_in
+ and OCST.label = setting_in
+ and OCS.setting_id = OCST.id;
+
+ return 1;
+ exception
+ when no_data_found
+ then
+ return 0;
+ end;
+
+ FUNCTION channel_priority(channel_id_in IN number)
+ RETURN number
+ IS
+ channel_name varchar2(256);
+ priority number;
+ end_of_life_val date;
+ org_id_val number;
+ BEGIN
+
+ select name, end_of_life, org_id
+ into channel_name, end_of_life_val, org_id_val
+ from rhnChannel
+ where id = channel_id_in;
+
+ if end_of_life_val is not null then
+ return -400;
+ end if;
+
+ if channel_name like 'Red Hat Enterprise Linux%' or channel_name like
'RHEL%' then
+ priority := 1000;
+ if channel_name not like '%Beta%' then
+ priority := priority + 1000;
+ end if;
+
+ priority := priority +
+ case
+ when channel_name like '%v. 5%' then 600
+ when channel_name like '%v. 4%' then 500
+ when channel_name like '%v. 3%' then 400
+ when channel_name like '%v. 2%' then 300
+ when channel_name like '%v. 1%' then 200
+ else 0
+ end;
+
+ priority := priority +
+ case
+ when channel_name like 'Red Hat Enterprise Linux (v. 5%' then 60
+ when (channel_name like '%AS%' and channel_name not like
'%Extras%') then 50
+ when (channel_name like '%ES%' and channel_name not like
'%Extras%') then 40
+ when (channel_name like '%WS%' and channel_name not like
'%Extras%') then 30
+ when (channel_name like '%Desktop%' and channel_name not like
'%Extras%') then 20
+ when channel_name like '%Extras%' then 10
+ else 0
+ end;
+
+ priority := priority +
+ case
+ when channel_name like '%)' then 5
+ else 0
+ end;
+
+ priority := priority +
+ case
+ when channel_name like '%32-bit x86%' then 4
+ when channel_name like '%64-bit Intel Itanium%' then 3
+ when channel_name like '%64-bit AMD64/Intel EM64T%' then 2
+ else 0
+ end;
+ elsif channel_name like 'Red Hat Desktop%' then
+ priority := 900;
+
+ if channel_name not like '%Beta%' then
+ priority := priority + 50;
+ end if;
+
+ priority := priority +
+ case
+ when channel_name like '%v. 4%' then 40
+ when channel_name like '%v. 3%' then 30
+ when channel_name like '%v. 2%' then 20
+ when channel_name like '%v. 1%' then 10
+ else 0
+ end;
+
+ priority := priority +
+ case
+ when channel_name like '%32-bit x86%' then 4
+ when channel_name like '%64-bit Intel Itanium%' then 3
+ when channel_name like '%64-bit AMD64/Intel EM64T%' then 2
+ else 0
+ end;
+
+ elsif org_id_val is not null then
+ priority := 600;
+ else
+ priority := 500;
+ end if;
+
+ return -priority;
+
+ end channel_priority;
+
+ -- right now this only does the accounting changes; the cascade
+ -- actually does the rhnServerChannel delete.
+ procedure delete_server_channels(server_id_in in number)
+ is
+ begin
+ update rhnPrivateChannelFamily
+ set current_members = current_members -1
+ where org_id in (
+ select org_id
+ from rhnServer
+ where id = server_id_in
+ )
+ and channel_family_id in (
+ select rcfm.channel_family_id
+ from rhnChannelFamilyMembers rcfm,
+ rhnServerChannel rsc
+ where rsc.server_id = server_id_in
+ and rsc.channel_id = rcfm.channel_id
+ and not exists (
+ select 1
+ from
+ rhnChannelFamilyVirtSubLevel cfvsl,
+ rhnSGTypeVirtSubLevel sgtvsl,
+ rhnServerEntitlementView sev,
+ rhnVirtualInstance vi
+ where
+ -- system is a virtual instance
+ vi.virtual_system_id = server_id_in
+ and vi.host_system_id = sev.server_id
+ -- system's host has a virt ent
+ and sev.label in ('virtualization_host',
+ 'virtualization_host_platform')
+ and sev.server_group_type_id =
+ sgtvsl.server_group_type_id
+ -- the host's virt ent grants a cf virt sub level
+ and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id
+ -- the cf is in that virt sub level
+ and cfvsl.channel_family_id = rcfm.channel_family_id
+ )
+ );
+ end;
+
+ -- this could certainly be optimized to do updates if needs be
+ procedure refresh_newest_package(channel_id_in in number, caller_in in varchar2
:= '(unknown)')
+ is
+ begin
+ delete from rhnChannelNewestPackage where channel_id = channel_id_in;
+ insert into rhnChannelNewestPackage
+ ( channel_id, name_id, evr_id, package_id, package_arch_id )
+ ( select channel_id,
+ name_id, evr_id,
+ package_id, package_arch_id
+ from rhnChannelNewestPackageView
+ where channel_id = channel_id_in
+ );
+ insert into rhnChannelNewestPackageAudit (channel_id, caller)
+ values (channel_id_in, caller_in);
+ update rhnChannel
+ set last_modified = greatest(sysdate, last_modified + 1/86400)
+ where id = channel_id_in;
+ end;
+
+ procedure update_channel ( channel_id_in in number, invalidate_ss in number := 0,
+ date_to_use in date := sysdate )
+ is
+
+ channel_last_modified date;
+ last_modified_value date;
+
+ cursor snapshots is
+ select snapshot_id id
+ from rhnSnapshotChannel
+ where channel_id = channel_id_in;
+
+ begin
+
+ select last_modified
+ into channel_last_modified
+ from rhnChannel
+ where id = channel_id_in;
+
+ last_modified_value := date_to_use;
+
+ if last_modified_value <= channel_last_modified then
+ last_modified_value := last_modified_value + 1/86400;
+ end if;
+
+ update rhnChannel set last_modified = last_modified_value
+ where id = channel_id_in;
+
+ if invalidate_ss = 1 then
+ for snapshot in snapshots loop
+ update rhnSnapshot
+ set invalid = lookup_snapshot_invalid_reason('channel_modified')
+ where id = snapshot.id;
+ end loop;
+ end if;
+
+ end update_channel;
+
+ procedure update_channels_by_package ( package_id_in in number, date_to_use in date :=
sysdate )
+ is
+
+ cursor channels is
+ select channel_id
+ from rhnChannelPackage
+ where package_id = package_id_in
+ order by channel_id;
+
+ begin
+ for channel in channels loop
+ -- we want to invalidate the snapshot assocated with the channel when we
+ -- do this b/c we know we've added or removed or packages
+ rhn_channel.update_channel ( channel.channel_id, 1, date_to_use );
+ end loop;
+ end update_channels_by_package;
+
+
+ procedure update_channels_by_errata ( errata_id_in number, date_to_use in date :=
sysdate )
+ is
+
+ cursor channels is
+ select channel_id
+ from rhnChannelErrata
+ where errata_id = errata_id_in
+ order by channel_id;
+
+ begin
+ for channel in channels loop
+ -- we won't invalidate snapshots, b/c just changing the errata associated
with
+ -- a channel shouldn't invalidate snapshots
+ rhn_channel.update_channel ( channel.channel_id, 0, date_to_use );
+ end loop;
+ end update_channels_by_errata;
+
+END rhn_channel;
+/
+SHOW ERRORS
commit f942de95d9fe259e09ea809f61af9ab95769f015
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Mon Nov 30 16:19:21 2009 +0100
add missing relationship
From commit 9615725006221e9feb74bf827f7a15c363fe2cc6
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
index 7c895db..9c1dcf1 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
@@ -87,6 +87,7 @@ is
rhnServerGroup sg,
rhnUserGroupType ugt
where ugt.label = 'org_admin'
+ and ugt.id = ug.group_type
and sg.id = server_group_id_in
and ugm.user_id = usgp.user_id
and ug.org_id = sg.org_id
commit c31883ad2159a806c3b6ac27fcb88c94c3fc1737
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Mon Nov 30 16:16:22 2009 +0100
rename schema upgrade script to use uniform .sql extension
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb
deleted file mode 100644
index 7c895db..0000000
---
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb
+++ /dev/null
@@ -1,102 +0,0 @@
---
--- Copyright (c) 2008 Red Hat, Inc.
---
--- This software is licensed to you under the GNU General Public License,
--- version 2 (GPLv2). There is NO WARRANTY for this software, express or
--- implied, including the implied warranties of MERCHANTABILITY or FITNESS
--- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
--- along with this software; if not, see
---
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
---
--- Red Hat trademarks are not licensed under GPLv2. No permission is
--- granted to use or replicate Red Hat trademarks that are incorporated
--- in this software or its documentation.
---
---
---
---
-
-create or replace package body
-rhn_cache
-is
- body_version varchar2(100) := '';
-
- -- this searches out all users who get perms...
- procedure update_perms_for_server(
- server_id_in in number
- ) is
- begin
- -- delete rows which are no more valid
- delete from rhnUserServerPerms p
- where server_id = server_id_in
- and user_id not in (select user_id
- from rhnUserServerPermsDupes d
- where p.server_id = d.server_id);
- -- insert newly added rows
- insert into rhnUserServerPerms(user_id, server_id) (
- select distinct user_id, server_id_in
- from rhnUserServerPermsDupes d
- where server_id = server_id_in
- and user_id not in (
- select user_id
- from rhnUserServerPerms p
- where p.server_id = d.server_id)
- );
- end update_perms_for_server;
-
- -- update rhnUserServerPerms cache from rhnUserServerPermsDupes
- procedure update_perms_for_user(
- user_id_in in number
- ) is
- begin
- -- first delete rows which are not in rhnUserServerPermsDupes
- delete from rhnUserServerPerms up
- where user_id = user_id_in
- and not exists (
- select 1
- from rhnUserServerPermsDupes uspd
- where uspd.user_id = up.user_id
- and uspd.server_id = up.server_id);
-
- -- then insert rest of rows from rhnUserServerPermsDupes
- insert into rhnUserServerPerms (user_id, server_id)
- select distinct user_id_in, server_id
- from rhnUserServerPermsDupes uspd
- where uspd.user_id = user_id_in
- and not exists (
- select 1
- from rhnUserServerPerms usp
- where usp.user_id = user_id_in
- and usp.server_id = uspd.server_id);
- end update_perms_for_user;
-
- -- this means a server got added or removed, so we
- -- can't key off of a server anywhere.
- procedure update_perms_for_server_group(
- server_group_id_in in number
- ) is
- cursor users is
- -- org admins aren't affected, so don't test for them
- select usgp.user_id id
- from rhnUserServerGroupPerms usgp
- where usgp.server_group_id = server_group_id_in
- and not exists (
- select 1
- from rhnUserGroup ug,
- rhnUserGroupMembers ugm,
- rhnServerGroup sg,
- rhnUserGroupType ugt
- where ugt.label = 'org_admin'
- and sg.id = server_group_id_in
- and ugm.user_id = usgp.user_id
- and ug.org_id = sg.org_id
- and ugm.user_group_id = ug.id
- );
- begin
- for u in users loop
- update_perms_for_user(u.id);
- end loop;
- end update_perms_for_server_group;
-end rhn_cache;
-/
-show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
new file mode 100644
index 0000000..7c895db
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-0.6-to-spacewalk-schema-0.7/160-rhn_cache.pkb.sql
@@ -0,0 +1,102 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+--
+--
+--
+
+create or replace package body
+rhn_cache
+is
+ body_version varchar2(100) := '';
+
+ -- this searches out all users who get perms...
+ procedure update_perms_for_server(
+ server_id_in in number
+ ) is
+ begin
+ -- delete rows which are no more valid
+ delete from rhnUserServerPerms p
+ where server_id = server_id_in
+ and user_id not in (select user_id
+ from rhnUserServerPermsDupes d
+ where p.server_id = d.server_id);
+ -- insert newly added rows
+ insert into rhnUserServerPerms(user_id, server_id) (
+ select distinct user_id, server_id_in
+ from rhnUserServerPermsDupes d
+ where server_id = server_id_in
+ and user_id not in (
+ select user_id
+ from rhnUserServerPerms p
+ where p.server_id = d.server_id)
+ );
+ end update_perms_for_server;
+
+ -- update rhnUserServerPerms cache from rhnUserServerPermsDupes
+ procedure update_perms_for_user(
+ user_id_in in number
+ ) is
+ begin
+ -- first delete rows which are not in rhnUserServerPermsDupes
+ delete from rhnUserServerPerms up
+ where user_id = user_id_in
+ and not exists (
+ select 1
+ from rhnUserServerPermsDupes uspd
+ where uspd.user_id = up.user_id
+ and uspd.server_id = up.server_id);
+
+ -- then insert rest of rows from rhnUserServerPermsDupes
+ insert into rhnUserServerPerms (user_id, server_id)
+ select distinct user_id_in, server_id
+ from rhnUserServerPermsDupes uspd
+ where uspd.user_id = user_id_in
+ and not exists (
+ select 1
+ from rhnUserServerPerms usp
+ where usp.user_id = user_id_in
+ and usp.server_id = uspd.server_id);
+ end update_perms_for_user;
+
+ -- this means a server got added or removed, so we
+ -- can't key off of a server anywhere.
+ procedure update_perms_for_server_group(
+ server_group_id_in in number
+ ) is
+ cursor users is
+ -- org admins aren't affected, so don't test for them
+ select usgp.user_id id
+ from rhnUserServerGroupPerms usgp
+ where usgp.server_group_id = server_group_id_in
+ and not exists (
+ select 1
+ from rhnUserGroup ug,
+ rhnUserGroupMembers ugm,
+ rhnServerGroup sg,
+ rhnUserGroupType ugt
+ where ugt.label = 'org_admin'
+ and sg.id = server_group_id_in
+ and ugm.user_id = usgp.user_id
+ and ug.org_id = sg.org_id
+ and ugm.user_group_id = ug.id
+ );
+ begin
+ for u in users loop
+ update_perms_for_user(u.id);
+ end loop;
+ end update_perms_for_server_group;
+end rhn_cache;
+/
+show errors