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@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@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@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
spacewalk-commits@lists.fedorahosted.org