spacewalk-commits March 2011

spacewalk-commits@lists.fedorahosted.org

8 participants
316 discussions

rel-eng/packages spacewalk/setup
by Michael Mraka 30 Mar '11

30 Mar '11

rel-eng/packages/spacewalk-setup | 2 +- spacewalk/setup/spacewalk-setup.spec | 6 +++++- 2 files changed, 6 insertions(+), 2 deletions(-) New commits: commit e11935fdfcb554c6dd8f4efe623d4127dfc64d1b Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Wed Mar 30 21:32:01 2011 +0200 Automatic commit of package [spacewalk-setup] release [1.4.4-1]. diff --git a/rel-eng/packages/spacewalk-setup b/rel-eng/packages/spacewalk-setup index 2280a5f..429bf55 100644 --- a/rel-eng/packages/spacewalk-setup +++ b/rel-eng/packages/spacewalk-setup @@ -1 +1 @@ -1.4.3-1 spacewalk/setup/ +1.4.4-1 spacewalk/setup/ diff --git a/spacewalk/setup/spacewalk-setup.spec b/spacewalk/setup/spacewalk-setup.spec index 389e81a..a325c03 100644 --- a/spacewalk/setup/spacewalk-setup.spec +++ b/spacewalk/setup/spacewalk-setup.spec @@ -1,5 +1,5 @@ Name: spacewalk-setup -Version: 1.4.3 +Version: 1.4.4 Release: 1%{?dist} Summary: Initial setup tools for Red Hat Spacewalk @@ -101,6 +101,10 @@ rm -rf %{buildroot} %attr(755, apache, root) %{_var}/spacewalk %changelog +* Wed Mar 30 2011 Michael Mraka <michael.mraka(a)redhat.com> 1.4.4-1 +- fixed missing output redirection +- oracle_sqlplus_t is not able to write to logs + * Wed Mar 30 2011 Miroslav Suchý <msuchy(a)redhat.com> 1.4.3-1 - 683200 - convert db-host from IDN to ascii

1 0

spacewalk/setup
by Michael Mraka 30 Mar '11

30 Mar '11

spacewalk/setup/share/oracle/upgrade-db.sh | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) New commits: commit cce577882126483637541a3fcf2aa024197e74c1 Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Wed Mar 30 21:27:58 2011 +0200 fixed missing output redirection diff --git a/spacewalk/setup/share/oracle/upgrade-db.sh b/spacewalk/setup/share/oracle/upgrade-db.sh index 3715fc1..8372c2e 100644 --- a/spacewalk/setup/share/oracle/upgrade-db.sh +++ b/spacewalk/setup/share/oracle/upgrade-db.sh @@ -113,6 +113,6 @@ m4 $UPGRADE_TMPL -I$ORACLE_ADMIN_DIR \ --define RHNORA_DATA_PATH=/rhnsat/data/rhnsat \ --define RHNORA_DB_USER=$DB_USER \ | $ORACLE_HOME/bin/sqlplus /nolog \ - | cat /rhnsat/admin/rhnsat/logs/create_$ORACLE_SID.log + | cat > /rhnsat/admin/rhnsat/logs/create_$ORACLE_SID.log set +x

1 0

Changes to 'refs/tags/spacewalk-schema-1.4.13-1'
by Tomas Lestach 30 Mar '11

30 Mar '11

Tag 'spacewalk-schema-1.4.13-1' created by Tomas Lestach <tlestach(a)redhat.com> at 2011-03-30 16:18 +0000 Tagging package [spacewalk-schema] version [1.4.13-1] in directory [schema/spacewalk/]. Changes since yum-rhn-plugin-1.4.8-1: Tomas Lestach (3): 671450 - delete rhel private channel families, if there're no certificate entitlements available 671450 - delete rhel private channel families, if there're no certificate entitlements available PG Automatic commit of package [spacewalk-schema] release [1.4.13-1]. --- rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/oracle/packages/rhn_entitlements.pkb | 25 schema/spacewalk/postgres/packages/rhn_entitlements.pkb | 24 schema/spacewalk/spacewalk-schema.spec | 6 schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.oracle | 1861 ++++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.postgresql | 1749 +++++++++ 6 files changed, 3646 insertions(+), 21 deletions(-) ---

1 0

rel-eng/packages schema/spacewalk
by Tomas Lestach 30 Mar '11

30 Mar '11

rel-eng/packages/spacewalk-schema | 2 +- schema/spacewalk/spacewalk-schema.spec | 6 +++++- 2 files changed, 6 insertions(+), 2 deletions(-) New commits: commit 1a58ab3bca35e3c40ccbb90dafa9ff278c4b6fb3 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Mar 30 18:18:19 2011 +0200 Automatic commit of package [spacewalk-schema] release [1.4.13-1]. diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index 91d5ae8..117d60a 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -1.4.12-1 schema/spacewalk/ +1.4.13-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index 153fc70..a0659a6 100644 --- a/schema/spacewalk/spacewalk-schema.spec +++ b/schema/spacewalk/spacewalk-schema.spec @@ -2,7 +2,7 @@ Name: spacewalk-schema Group: Applications/Internet Summary: Oracle SQL schema for Spacewalk server -Version: 1.4.12 +Version: 1.4.13 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz @@ -66,6 +66,10 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql* %changelog +* Wed Mar 30 2011 Tomas Lestach <tlestach(a)redhat.com> 1.4.13-1 +- 671450 - delete rhel private channel families, if there're no certificate + entitlements available (tlestach(a)redhat.com) + * Wed Mar 23 2011 Jan Pazdziora 1.4.12-1 - In update, the column names should not contain table name (PostgreSQL).

1 0

2 commits - schema/spacewalk
by Tomas Lestach 30 Mar '11

30 Mar '11

schema/spacewalk/oracle/packages/rhn_entitlements.pkb | 25 schema/spacewalk/postgres/packages/rhn_entitlements.pkb | 24 schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.oracle | 1861 ++++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.postgresql | 1749 +++++++++ 4 files changed, 3640 insertions(+), 19 deletions(-) New commits: commit f39492327788288c106b6a8fc7db5000a9b72eee Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Mar 30 18:10:02 2011 +0200 671450 - delete rhel private channel families, if there're no certificate entitlements available PG diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb index fbccdbc..8c80a23 100644 --- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -1609,16 +1609,22 @@ as $$ quantity, flex ); - update rhnPrivateChannelFamily - set max_members = quantity - where org_id = customer_id_in - and channel_family_id = channel_family_id_in; - - update rhnPrivateChannelFamily - set fve_max_members = flex - where org_id = customer_id_in - and channel_family_id = channel_family_id_in; + if quantity is null and flex is null then + delete from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + else + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + update rhnPrivateChannelFamily + set fve_max_members = flex + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + end if; return; end loop; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.postgresql new file mode 100644 index 0000000..fd18231 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.postgresql @@ -0,0 +1,1749 @@ +-- Note: this file is not a full equivalent of the Oracle sources. +-- Neither the flex stuff nor the update_family_countsYN was fully migrated. +-- Migrated functions or code paths: +-- +-- can_entitle_server +-- entitle_server +-- code path of simple rhn-satellite-activate +-- +-- +-- Copyright (c) 2008--2010 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 schema rhn_entitlements; + +-- setup search_path so that these functions are created in appropriate schema. +update pg_settings set setting = 'rhn_entitlements,' || setting where name = 'search_path'; + + create or replace function find_compatible_sg ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ + declare + servergroups cursor for + select sg.id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerServerGroupArchCompat ssgac + where s.id = server_id_in + and s.org_id = sg.org_id + and sgt.label = type_label_in + and sg.group_type = sgt.id + and ssgac.server_group_type = sgt.id + and ssgac.server_arch_id = s.server_arch_id + and not exists ( + select 1 + from rhnServerGroupMembers sgm + where sgm.server_group_id = sg.id + and sgm.server_id = s.id); + + + begin + for servergroup in servergroups loop + return servergroup.id; + end loop; + + --no servergroup found + return NULL; + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: remove_org_entitlements + -- + -- Removes both system entitlements and channel subscriptions + -- that are currently assigned to an org and re-assigns to the + -- master org (org_id = 1). + -- + -- When we call this we expect everything to already be unentitled + -- which shoul be handled by delete_org. + -- + -- Called by: delete_org + -- ******************************************************************* + create or replace function remove_org_entitlements ( + org_id_in numeric + ) returns void +as $$ + declare + system_ents cursor for + select sg.id, sg.max_members, sg.group_type + from rhnServerGroup sg + where group_type is not null + and org_id = org_id_in; + + channel_subs cursor for + select pcf.channel_family_id, pcf.max_members + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.org_id is null; + + begin + + for system_ent in system_ents loop + update rhnServerGroup + set max_members = max_members + system_ent.max_members + where org_id = 1 + and group_type = system_ent.group_type; + end loop; + + update rhnServerGroup + set max_members = 0 + where org_id = org_id_in; + + for channel_sub in channel_subs loop + update rhnPrivateChannelFamily + set max_members = max_members + channel_sub.max_members + where org_id = 1 + and channel_family_id = channel_sub.channel_family_id; + end loop; + + update rhnPrivateChannelFamily + set max_members = 0 + where org_id = org_id_in; + + end$$ +language plpgsql; + + create or replace function entitlement_grants_service ( + entitlement_in in varchar, + service_level_in in varchar + ) returns numeric +as $$ + begin + if service_level_in = 'provisioning' then + if entitlement_in = 'provisioning_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'management' then + if entitlement_in = 'enterprise_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'monitoring' then + if entitlement_in = 'monitoring_entitled' then + return 1; + end if; + elsif service_level_in = 'updates' then + return 1; + else + return 0; + end if; + end$$ +language plpgsql; + + create or replace function lookup_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ + declare + server_groups cursor for + select sg.id server_group_id + from rhnServerGroup sg, + rhnServerGroupType sgt + where sgt.label = type_label_in + and sgt.id = sg.group_type + and sg.org_id = org_id_in; + begin + for sg in server_groups loop + return sg.server_group_id; + end loop; + return rhn_entitlements.create_entitlement_group( + org_id_in, + type_label_in + ); + end$$ +language plpgsql; + + create or replace function create_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ + declare + sg_id_val numeric; + begin + select nextval('rhn_server_group_id_seq') + into sg_id_val; + + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id + ) ( + select sg_id_val, sgt.label, sgt.label, + 0, 0, sgt.id, org_id_in + from rhnServerGroupType sgt + where sgt.label = type_label_in + ); + + return sg_id_val; + end$$ +language plpgsql; + + create or replace function can_entitle_server ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ + declare + addon_servergroups cursor (base_label_in varchar, + addon_label_in varchar) for + select + addon_id + from + rhnSGTypeBaseAddonCompat + where base_id = lookup_sg_type (base_label_in) + and addon_id = lookup_sg_type (addon_label_in); + + previous_ent varchar[]; + is_base_in char := 'N'; + is_base_current char := 'N'; + i numeric := 0; + sgid numeric := 0; + + begin + + previous_ent := rhn_entitlements.get_server_entitlement(server_id_in); + + select distinct is_base + into is_base_in + from rhnServerGroupType + where label = type_label_in; + + if array_upper(previous_ent, 1) is null or array_upper(previous_ent, 1) = 0 then + if is_base_in = 'Y' then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- there are previous ents, first make sure we're not trying to entitle a base ent + elsif is_base_in = 'Y' then + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + + -- it must be an addon, so proceed with the entitlement + else + + -- find the servers base ent + is_base_current := 'N'; + i := 0; + while is_base_current = 'N' and i < array_upper(previous_ent, 1) + loop + i := i + 1; + select is_base + into is_base_current + from rhnServerGroupType + where label = previous_ent[i]; + end loop; + + -- never found a base ent, that would be strange + if is_base_current = 'N' then + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- this for loop verifies the validity of the addon path + for addon_servergroup in addon_servergroups (previous_ent[i], type_label_in) loop + -- find an appropriate sgid for the addon and entitle the server + sgid := rhn_entitlements.find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + end if; + end loop; + + end if; + + return 0; + + end$$ +language plpgsql; + + create or replace function can_switch_base ( + server_id_in in integer, + type_label_in in varchar + ) + returns numeric +as $$ + declare + type_label_in_is_base char(1); + sgid numeric; + + begin + + select is_base into type_label_in_is_base + from rhnServerGroupType + where label = type_label_in; + + if not found then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + end if; + + if type_label_in_is_base = 'N' then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + else + sgid := rhn_entitlements.find_compatible_sg ( server_id_in, + type_label_in ); + if sgid is not null then + return 1; + else + return 0; + end if; + end if; + + end$$ +language plpgsql; + + create or replace function entitle_server ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns void +as $$ + declare + sgid numeric := 0; + is_virt numeric := 0; + + begin + + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then + is_virt := 0; + end if; + + if is_virt = 0 and (type_label_in = 'virtualization_host' or + type_label_in = 'virtualization_host_platform') then + + is_virt := 1; + end if; + + if rhn_entitlements.can_entitle_server(server_id_in, + type_label_in) = 1 then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, + type_label_in); + if sgid is not null then + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'added system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + perform rhn_server.insert_into_servergroup (server_id_in, sgid); + + if is_virt = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + else + perform rhn_exception.raise_exception ('no_available_server_group'); + end if; + else + perform rhn_exception.raise_exception ('invalid_entitlement'); + end if; + end$$ +language plpgsql; + + create or replace function remove_server_entitlement ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled', + repoll_virt_guests in numeric default 1 + ) returns void +as $$ + declare + group_id numeric; + type_is_base char; + is_virt numeric := 0; + begin + -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones... + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + if not found then + is_virt := 0; + end if; + + select sg.id, sgt.is_base + into group_id, type_is_base + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnServer s + where s.id = server_id_in + and s.id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.org_id = s.org_id + and sgt.label = type_label_in + and sgt.id = sg.group_type; + + if not found then + perform rhn_exception.raise_exception('invalid_server_group_member'); + end if; + + if ( type_is_base = 'Y' ) then + -- unentitle_server should handle everything, don't really need to do anything else special here + perform rhn_entitlements.unentitle_server ( server_id_in ); + else + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'removed system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platforrm' end ); + + perform rhn_server.delete_from_servergroup(server_id_in, group_id); + + -- special case: clean up related monitoring data + if type_label_in = 'monitoring_entitled' then + DELETE + FROM state_change + WHERE o_id IN (SELECT probe_id::varchar + FROM rhn_check_probe + WHERE host_id = server_id_in); + DELETE + FROM time_series + WHERE substring(o_id FROM position('-' IN o_id) + 1 + FOR position('-' IN substring(o_id FROM position('-' IN o_id) + 1)) - 1) + IN (SELECT probe_id::text + FROM rhn_check_probe + WHERE host_id = server_id_in); + DELETE + FROM rhn_probe + WHERE recid IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + end if; + + if is_virt = 1 and repoll_virt_guests = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + end if; + + end$$ +language plpgsql; + + create or replace function unentitle_server ( + server_id_in in numeric + ) returns void +as $$ + declare + servergroups cursor for + select distinct sgt.label, sg.id server_group_id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerGroupMembers sgm + where s.id = server_id_in + and s.org_id = sg.org_id + and sg.group_type = sgt.id + and sgm.server_group_id = sg.id + and sgm.server_id = s.id; + + is_virt numeric := 0; + + begin + + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then + is_virt := 0; + end if; + + for servergroup in servergroups loop + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'removed system entitlement ', + case servergroup.label + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + perform rhn_server.delete_from_servergroup(server_id_in, + servergroup.server_group_id ); + end loop; + + if is_virt = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + end$$ +language plpgsql; + + + -- ******************************************************************* + -- PROCEDURE: repoll_virt_guest_entitlements + -- + -- Whenever we add/remove a virtualization_host* entitlement from + -- a host, we can call this procedure to update what type of slots + -- the guests are consuming. + -- + -- If you're removing the entitlement, it's + -- possible the guests will become unentitled if you don't have enough + -- physical slots to cover them. + -- + -- If you're adding the entitlement, you end up freeing up physical + -- slots for other systems. + -- + -- ******************************************************************* + create or replace function repoll_virt_guest_entitlements( + server_id_in in numeric + ) returns void +as $$ + declare + -- All channel families associated with the guests of server_id_in + families cursor for + select distinct cfs.channel_family_id + from + rhnChannelFamilyServers cfs, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = cfs.server_id; + + -- All of server group types associated with the guests of + -- server_id_in + group_types cursor for + select distinct sg.group_type, sgt.label + from + rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + + -- Virtual servers from a certain family belonging to a specific + -- host that are consuming physical channel slots over the limit. + virt_servers_cfam cursor(family_id_in numeric, quantity_in numeric) for + select vi.virtual_system_id + from + rhnChannelFamilyMembers cfm, + rhnServerChannel sc, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sc.server_id + and sc.channel_id = cfm.channel_id + and cfm.channel_family_id = family_id_in + order by sc.modified desc + limit quantity_in; + + -- Virtual servers from a certain family belonging to a specific + -- host that are consuming physical system slots over the limit. + virt_servers_sgt cursor(group_type_in numeric, quantity_in numeric) for + select vi.virtual_system_id + from + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = group_type_in + order by sgm.modified desc + limit quantity_in; + + org_id_val numeric; + max_members_val numeric; + current_members_calc numeric; + sg_id numeric; + + begin + + select org_id + into org_id_val + from rhnServer + where id = server_id_in; + + -- deal w/ channel entitlements first ... + for family in families loop + -- get the current (physical) members of the family + current_members_calc := + rhn_channel.channel_family_current_members(family.channel_family_id, + org_id_val); -- fixed transposed args + + -- get the max members of the family + select max_members + into max_members_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, so we'll + -- unsubscribe guests from the host first. + + -- hm, i don't think max_members - current_members_calc yielding a negative number + -- will work w/ rownum, swaping 'em in the body of this if... + for virt_server in virt_servers_cfam(family.channel_family_id, + current_members_calc - max_members_val) loop + + perform rhn_channel.unsubscribe_server_from_family( + virt_server.virtual_system_id, + family.channel_family_id); + end loop; + + -- if we're still over the limit, which would be odd, + -- just prune the group to max_members + -- + -- er... wouldn't we actually have to refresh the values of + -- current_members_calc and max_members_val to actually ever + -- *skip this?? + if current_members_calc > max_members_val then + -- argh, transposed again?! + perform rhn_entitlements.set_family_count(org_id_val, + family.channel_family_id, + max_members_val); + end if; + + end if; + + -- update current_members for the family. This will set the value + -- to reflect adding/removing the entitlement. + -- + -- what's the difference of doing this vs the unavoidable set_family_count above? + perform rhn_channel.update_family_counts(family.channel_family_id, + org_id_val); + end loop; + + for a_group_type in group_types loop + -- get the current *physical* members of the system entitlement type for the org... + -- + -- unlike channel families, it appears the standard rhnServerGroup.max_members represents + -- *physical* slots, vs physical+virt ... boy that's confusing... + + select max_members, id + into max_members_val, sg_id + from rhnServerGroup + where group_type = a_group_type.group_type + and org_id = org_id_val; + + + select count(sep.server_id) into current_members_calc + from rhnServerEntitlementPhysical sep + where sep.server_group_id = sg_id + and sep.server_group_type_id = a_group_type.group_type; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, and we're over the limit, so unsubscribe guests + for virt_server in virt_servers_sgt(a_group_type.group_type, + current_members_calc - max_members_val) loop + perform rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label); + + -- decrement current_members_calc, we'll use it to reset current_members for the group at the end... + current_members_calc := current_members_calc - 1; + end loop; + + end if; + + update rhnServerGroup set current_members = current_members_calc + where org_id = org_id_val + and group_type = a_group_type.group_type; + + -- I think that's all the house-keeping we have to do... + end loop; + + end$$ +language plpgsql; + + create or replace function get_server_entitlement ( + server_id_in in numeric + ) returns varchar[] +as $$ + declare + server_groups cursor for + select sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_id = server_id_in + and sg.id = sgm.server_group_id + and sgt.id = sg.group_type + and sgt.label in ( + 'sw_mgr_entitled','enterprise_entitled', + 'provisioning_entitled', 'nonlinux_entitled', + 'monitoring_entitled', 'virtualization_host', + 'virtualization_host_platform' + ); + + ent_array varchar[]; + + begin + + ent_array := '{}'; + + for sg in server_groups loop + ent_array := ent_array || sg.label; + end loop; + + return ent_array; + + end$$ +language plpgsql; + + -- this desperately needs to be table driven. + create or replace function modify_org_service ( + org_id_in in numeric, + service_label_in in varchar, + enable_in in char + ) returns void +as $$ + declare + roles_to_process varchar[]; + roles cursor(role_label_in varchar) for + select label, id + from rhnUserGroupType + where label = role_label_in; + org_roles cursor(role_label_in varchar) for + select 1 + from rhnUserGroup ug, + rhnUserGroupType ugt + where ugt.label = role_label_in + and ug.org_id = org_id_in + and ugt.id = ug.group_type; + + ents_to_process varchar[]; + ents cursor(ent_label_in varchar) for + select label, id + from rhnOrgEntitlementType + where label = ent_label_in; + org_ents cursor(ent_label_in varchar) for + select 1 + from rhnOrgEntitlements oe, + rhnOrgEntitlementType oet + where oet.label = ent_label_in + and oe.org_id = org_id_in + and oet.id = oe.entitlement_id; + create_row char(1); + begin + ents_to_process := '{}'; + roles_to_process := '{}'; + -- a bit kludgy, but only for 3.4 really. Certainly no + -- worse than the old code... + if service_label_in = 'enterprise' or + service_label_in = 'management' then + ents_to_process := array_append(ents_to_process, 'sw_mgr_enterprise'); + + roles_to_process := array_append(roles_to_process, 'org_admin'); + + roles_to_process := array_append(roles_to_process, 'system_group_admin'); + + roles_to_process := array_append(roles_to_process, 'activation_key_admin'); + + roles_to_process := array_append(roles_to_process, 'org_applicant'); + elsif service_label_in = 'provisioning' then + ents_to_process := array_append(ents_to_process, 'rhn_provisioning'); + + roles_to_process := array_append(roles_to_process, 'system_group_admin'); + + roles_to_process := array_append(roles_to_process, 'activation_key_admin'); + + roles_to_process := array_append(roles_to_process, 'config_admin'); + -- another nasty special case... + if enable_in = 'Y' then + ents_to_process := array_append(ents_to_process, 'sw_mgr_enterprise'); + end if; + elsif service_label_in = 'monitoring' then + ents_to_process := array_append(ents_to_process, 'rhn_monitor'); + + roles_to_process := array_append(roles_to_process, 'monitoring_admin'); + elsif service_label_in = 'virtualization' then + ents_to_process := array_append(ents_to_process, 'rhn_virtualization'); + + roles_to_process := array_append(roles_to_process, 'config_admin'); + elsif service_label_in = 'virtualization_platform' then + ents_to_process := array_append(ents_to_process, 'rhn_virtualization_platform'); + roles_to_process := array_append(roles_to_process, 'config_admin'); + elsif service_label_in = 'nonlinux' then + ents_to_process := array_append(ents_to_process, 'rhn_nonlinux'); + roles_to_process := array_append(roles_to_process, 'config_admin'); + end if; + + if enable_in = 'Y' then + for i in 1..array_upper(ents_to_process, 1) loop + for ent in ents(ents_to_process[i]) loop + create_row := 'Y'; + for oe in org_ents(ent.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnOrgEntitlements(org_id, entitlement_id) + values (org_id_in, ent.id); + end if; + end loop; + end loop; + for i in 1..array_upper(roles_to_process, 1) loop + for role in roles(roles_to_process[i]) loop + create_row := 'Y'; + for o_r in org_roles(role.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnUserGroup( + id, name, description, current_members, + group_type, org_id + ) ( + select nextval('rhn_user_group_id_seq'), + ugt.name || 's', + ugt.name || 's for Org ' || + o.name || ' ('|| o.id ||')', + 0, ugt.id, o.id + from rhnUserGroupType ugt, + web_customer o + where o.id = org_id_in + and ugt.id = role.id + ); + end if; + end loop; + end loop; + else + for i in 1..coalesce(array_upper(ents_to_process, 1), 0) loop + for ent in ents(ents_to_process[i]) loop + delete from rhnOrgEntitlements + where org_id = org_id_in + and entitlement_id = ent.id; + end loop; + end loop; + end if; + end$$ +language plpgsql; + + create or replace function set_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'enterprise', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'provisioning', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_monitoring ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'monitoring', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'nonlinux', 'Y'); + end$$ +language plpgsql; + + create or replace function unset_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'enterprise', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'provisioning', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_monitoring ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'monitoring', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'nonlinux', 'N'); + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: prune_group + -- Unsubscribes servers consuming physical slots that over the org's + -- limit. + -- Called by: set_group_count, repoll_virt_guest_entitlements + -- ******************************************************************* + create or replace function prune_group ( + group_id_in in numeric, + type_in in char, + quantity_in in numeric, + update_family_countsYN in numeric default 1 + ) returns void +as $$ + declare + ugrecord record; + sgrecord record; + type_is_base char; + begin + if type_in = 'U' then + update rhnUserGroup + set max_members = quantity_in + where id = group_id_in; + + for ugrecord in ( + select user_id, user_group_id, ugt.label + from rhnUserGroupType ugt, + rhnUserGroup ug, + rhnUserGroupMembers ugm + where 1=1 + and ugm.user_group_id = group_id_in + and ugm.user_id in ( + select user_id + from rhnUserGroupMembers + where user_group_id = group_id_in + order by modified asc + offset quantity_in + ) + and ugm.user_group_id = ug.id + and ug.group_type = ugt.id + ) loop + perform rhn_user.remove_from_usergroup(ugrecord.user_id, ugrecord.user_group_id); + end loop; + elsif type_in = 'S' then + update rhnServerGroup + set max_members = quantity_in + where id = group_id_in; + + for sgrecord in ( + select server_id, server_group_id, sgt.id as group_type_id, sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_group_id = group_id_in + and sgm.server_id in ( + select sep.server_id + from + rhnServerEntitlementPhysical sep + where + sep.server_group_id = group_id_in + order by sep.modified asc + offset quantity_in + ) + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id + ) loop + perform rhn_entitlements.remove_server_entitlement(sgrecord.server_id, sgrecord.label); + + select is_base + into type_is_base + from rhnServerGroupType sgt + where sgt.id = sgrecord.group_type_id; + + -- if we're removing a base ent, then be sure to + -- remove the server's channel subscriptions. + if ( type_is_base = 'Y' ) then + perform rhn_channel.clear_subscriptions(sgrecord.server_id, update_family_countsYN); + end if; + + end loop; + end if; + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: assign_system_entitlement + -- + -- Moves system entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_group_count + -- ******************************************************************* + create or replace function assign_system_entitlement( + group_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + to_org_prev_ent_count numeric; + new_ent_count numeric; + new_quantity numeric; + group_type numeric; + begin + + select max_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = from_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + select max_members + into to_org_prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = to_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + + if not found then + to_org_prev_ent_count := 0; + end if; + + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_server_group'); + end if; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if new_ent_count < 0 then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + + perform rhn_entitlements.set_group_count(from_org_id_in, + 'S', + group_type, + new_ent_count); + + perform rhn_entitlements.set_group_count(to_org_id_in, + 'S', + group_type, + new_quantity); + + -- Create or delete the entries in rhnOrgEntitlementType + if group_label_in = 'enterprise_entitled' then + if new_quantity > 0 then + perform rhn_entitlements.set_customer_enterprise(to_org_id_in); + else + perform rhn_entitlements.unset_customer_enterprise(to_org_id_in); + end if; + end if; + + if group_label_in = 'provisioning_entitled' then + if new_quantity > 0 then + perform rhn_entitlements.set_customer_provisioning(to_org_id_in); + else + perform rhn_entitlements.unset_customer_provisioning(to_org_id_in); + end if; + end if; + + if group_label_in = 'monitoring_entitled' then + if new_quantity > 0 then + perform rhn_entitlements.set_customer_monitoring(to_org_id_in); + else + perform rhn_entitlements.unset_customer_monitoring(to_org_id_in); + end if; + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: assign_channel_entitlement + -- + -- Moves channel entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_family_count + -- ******************************************************************* + create or replace function assign_channel_entitlement( + channel_family_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + new_ent_count numeric; + to_org_prev_ent_count numeric; + new_quantity numeric; + cfam_id numeric; + begin + + select max_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + select max_members + into to_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + to_org_prev_ent_count := 0; + end if; + + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_channel_family'); + end if; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + + if new_ent_count < 0 then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + perform rhn_entitlements.set_family_count(from_org_id_in, + cfam_id, + new_ent_count); + + perform rhn_entitlements.set_family_count(to_org_id_in, + cfam_id, + new_quantity); + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: activate_system_entitlement + -- + -- Sets the values in rhnServerGroup for a given rhnServerGroupType. + -- + -- Calls: set_group_count to update, prune, or create the group. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if all entitlements + -- in the org are used so the free entitlements would not cover + -- the difference when descreasing the number of entitlements. + -- ******************************************************************* + create or replace function activate_system_entitlement( + org_id_in in numeric, + group_label_in in varchar, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + prev_ent_count_sum numeric; + group_type numeric; + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + + select current_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.group_type = sgt.id + and sgt.label = group_label_in + and sg.org_id = org_id_in; + + if not found then + prev_ent_count := 0; + end if; + + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_server_group'); + end if; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the allocated count in this org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + perform rhn_entitlements.set_group_count(org_id_in, + 'S', + group_type, + quantity_in); + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: activate_channel_entitlement + -- + -- Calls: set_family_count to update, prune, or create the family + -- permission bucket. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if there are not enough + -- entitlements in the org to cover the difference when you are + -- descreasing the number of entitlements. + -- + -- The backend code in Python is expected to do whatever arithmetics + -- is needed. + -- ******************************************************************* + create or replace function activate_channel_entitlement( + org_id_in in numeric, + channel_family_label_in in varchar, + quantity_in in numeric, + flex_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + prev_flex_count numeric; + prev_ent_count_sum numeric; + cfam_id numeric; + reduce_quantity numeric; + total_flex_capable numeric; + to_convert_reg cursor (channel_family_id_val numeric, org_id_in numeric, quantity numeric) for + select SC.server_id + from rhnServerChannel SC inner join + rhnServer S on S.id = SC.server_id + where + is_fve = 'Y' + and S.org_id = org_id_in + and SC.channel_id in + (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = channel_family_id_val) + limit quantity; + to_convert_flex cursor (channel_family_id_val numeric, org_id_in numeric, quantity numeric) for + select server_id + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id; + + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + select current_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + prev_ent_count := 0; + end if; + + select pcf.fve_current_members + into prev_flex_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + prev_flex_count := 0; + end if; + + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_channel_family'); + end if; + + -- if there are too few flex entitlements + if flex_in < prev_flex_count then + -- and if the extra we need is less than or equal to our extra regular entitlements + if prev_flex_count - flex_in <= quantity_in - prev_ent_count then + reduce_quantity := prev_flex_count - flex_in; + -- We need to convert some systems from flex guest to regular + for system in to_convert_reg(cfam_id, org_id_in, reduce_quantity) loop + --rhn_channel.convert_to_regular(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set sc.is_fve = 'N' + where sc.server_id = system.server_id; + end loop; + + --reset previous counts + prev_ent_count := prev_ent_count + reduce_quantity; + prev_flex_count := prev_flex_count - reduce_quantity; + else + perform rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + end if; + + -- if there are too few regular entitlements, and extra flex entitlements + if quantity_in < prev_ent_count and prev_flex_count < flex_in then + -- how many flex-capable systems (that aren't using flex) do we have + select count(*) + into total_flex_capable + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id; + -- if we have enough flex capable machines that is at least + -- as many as what we are over on + if total_flex_capable >= prev_ent_count - quantity_in then + reduce_quantity := prev_ent_count - quantity_in; + for system in to_convert_flex(cfam_id, org_id_in, reduce_quantity) loop +-- rhn_channel.convert_to_fve(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set sc.is_fve = 'Y' + where sc.server_id = system.server_id; + end loop; + prev_ent_count := prev_ent_count - reduce_quantity; + prev_flex_count := prev_flex_count + reduce_quantity; + end if; + end if; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the count in that one org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- even if we've manually converted systems to/from flex above + -- set_family_count should call update_family_counts, to reset + -- current used slots + perform rhn_entitlements.set_family_count(org_id_in, cfam_id, + quantity_in, flex_in); + end if; + + end$$ +language plpgsql; + + create or replace function set_group_count ( + customer_id_in in numeric, -- customer_id + type_in in char, -- 'U' or 'S' + group_type_in in numeric, -- rhn[User|Server]GroupType.id + quantity_in in numeric, -- quantity + update_family_countsYN in numeric default 1 + ) returns void +as $$ + declare + group_id numeric; + quantity numeric; + wasfound boolean; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + + if type_in = 'U' then + select rug.id + into group_id + from rhnUserGroup rug + where 1=1 + and rug.org_id = customer_id_in + and rug.group_type = group_type_in; + elsif type_in = 'S' then + select rsg.id + into group_id + from rhnServerGroup rsg + where 1=1 + and rsg.org_id = customer_id_in + and rsg.group_type = group_type_in; + end if; + + -- preserve the not found status across the rhn_entitlements.prune_group invocation + wasfound := true; + if not found then + wasfound := false; + end if; + + perform rhn_entitlements.prune_group( + group_id, + type_in, + quantity, + update_family_countsYN + ); + + if not wasfound then + if type_in = 'U' then + insert into rhnUserGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select nextval('rhn_user_group_id_seq'), name, name, + quantity, 0, id, customer_id_in, + current_timestamp, current_timestamp + from rhnUserGroupType + where id = group_type_in + ); + elsif type_in = 'S' then + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select nextval('rhn_server_group_id_seq'), name, name, + quantity, 0, id, customer_id_in, + current_timestamp, current_timestamp + from rhnServerGroupType + where id = group_type_in + ); + end if; + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: prune_family + -- Unsubscribes servers consuming physical slots from the channel family + -- that are over the org's limit. + -- Called by: set_family_count + -- ******************************************************************* + create or replace function prune_family ( + customer_id_in in numeric, + channel_family_id_in in numeric, + quantity_in in numeric, + flex_in in numeric + ) returns void +as $$ + declare + is_fve_in char; + tmp_quantity numeric; + + serverchannels cursor for + select sc.server_id, + sc.channel_id + from rhnServerChannel sc, + rhnChannelFamilyMembers cfm + where 1=1 + and cfm.channel_family_id = channel_family_id_in + and cfm.channel_id = sc.channel_id + and server_id in ( + select rs.id as server_id + from + rhnServerChannel rsc, + rhnChannelFamilyMembers rcfm, + rhnServer rs + where 1=1 + and rs.org_id = customer_id_in + and rs.id = rsc.server_id + and rsc.channel_id = rcfm.channel_id + and rcfm.channel_family_id = + channel_family_id_in + -- we only want to grab servers consuming + -- physical slots. + and exists ( + select 1 + from rhnChannelFamilyServerPhysical cfsp + where cfsp.server_id = rs.id + and cfsp.channel_family_id = + channel_family_id_in + ) + order by rcfm.modified asc + offset quantity_in + ); + begin + -- if we get a null customer_id, this is completely bogus. + if customer_id_in is null then + return; + end if; + + tmp_quantity := quantity_in; + is_fve_in := 'N'; + + for sc in serverchannels loop + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1); + + tmp_quantity := flex_in; + is_fve_in := 'Y'; + for sc in serverchannels loop + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1); + end loop; + end loop; + perform rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); + end$$ +language plpgsql; + + create or replace function set_family_count ( + customer_id_in in numeric, -- customer_id + channel_family_id_in in numeric, -- 246 + quantity_in in numeric, -- 3 + flex_in in numeric + ) returns void +as $$ + declare + privperms cursor for + select 1 + from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + pubperms cursor for + select o.id org_id + from web_customer o, + rhnPublicChannelFamily pcf + where pcf.channel_family_id = channel_family_id_in; + quantity numeric; + done numeric := 0; + flex numeric; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + flex := flex_in; + if flex is not null and flex < 0 then + flex := 0; + end if; + + if customer_id_in is not null then + for perm in privperms loop + perform rhn_entitlements.prune_family( + customer_id_in, + channel_family_id_in, + quantity, + flex + ); + + if quantity is null and flex is null then + delete from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + else + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + update rhnPrivateChannelFamily + set fve_max_members = flex + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + end if; + return; + end loop; + + insert into rhnPrivateChannelFamily ( + channel_family_id, org_id, max_members, current_members, fve_max_members, fve_current_members + ) values ( + channel_family_id_in, customer_id_in, quantity, 0, flex, 0 ); + return; + end if; + + for perm in pubperms loop + if quantity = 0 then + perform rhn_entitlements.prune_family( + perm.org_id, + channel_family_id_in, + quantity, + flex + ); + if done = 0 then + delete from rhnPublicChannelFamily + where channel_family_id = channel_family_id_in; + end if; + end if; + done := 1; + end loop; + -- if done's not 1, then we don't have any entitlements + if done != 1 then + insert into rhnPublicChannelFamily ( + channel_family_id + ) values ( + channel_family_id_in + ); + end if; + end$$ +language plpgsql; + + -- this expects quantity_in to be the number of available slots, not the + -- max_members of the server group. If you give it too many, it'll fail + -- and raise servergroup_max_members. + -- We should NEVER run this unless we're SURE that we won't + -- be violating the max. + create or replace function entitle_last_modified_servers ( + customer_id_in in numeric, -- customer_id + type_label_in in varchar, -- 'enterprise_entitled' + quantity_in in numeric -- 3 + ) returns void +as $$ + declare + -- find the servers that aren't currently in slots + servers cursor(cid_in numeric, quant_in numeric) for + select rs.id as server_id + from rhnServer rs + where 1=1 + and rs.org_id = cid_in + and not exists ( + select 1 + from rhnServerGroup sg, + rhnServerGroupMembers rsgm + where rsgm.server_id = rs.id + and rsgm.server_group_id = sg.id + and sg.group_type is not null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = + rs.id + ) + order by modified desc + limit quant_in; + begin + for server in servers(customer_id_in, quantity_in) loop + perform rhn_entitlements.entitle_server(server.server_id, type_label_in); + end loop; + end$$ +language plpgsql; + + create or replace function subscribe_newest_servers ( + customer_id_in in numeric + ) returns void +as $$ + declare + -- find servers without base channels + servers cursor(cid_in numeric) for + select s.id + from rhnServer s + where 1=1 + and s.org_id = cid_in + and not exists ( + select 1 + from rhnChannel c, + rhnServerChannel sc + where sc.server_id = s.id + and sc.channel_id = c.id + and c.parent_channel is null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = s.id + ) + order by s.modified desc; + channel_id numeric; + begin + for server in servers(customer_id_in) loop + channel_id := rhn_channel.guess_server_base(server.id); + if channel_id is not null then + begin + perform rhn_channel.subscribe_server(server.id, channel_id); + -- exception is really channel_family_no_subscriptions + exception + when others then + null; + end; + end if; + end loop; + end$$ +language plpgsql; + + +-- restore the original setting +update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_entitlements')+1) ) where name = 'search_path'; commit 31be7a0ff19f81bfddff054677b52a92fc156de0 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Mar 30 17:28:09 2011 +0200 671450 - delete rhel private channel families, if there're no certificate entitlements available diff --git a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb index b1bafb4..a187b7f 100644 --- a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb @@ -1723,16 +1723,21 @@ is rhn_entitlements.prune_family(customer_id_in, channel_family_id_in, quantity, flex); - update rhnPrivateChannelFamily - set max_members = quantity - where org_id = customer_id_in - and channel_family_id = channel_family_id_in; - - update rhnPrivateChannelFamily - set fve_max_members = flex - where org_id = customer_id_in - and channel_family_id = channel_family_id_in; - + if quantity is null and flex is null then + delete from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + else + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + update rhnPrivateChannelFamily + set fve_max_members = flex + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + end if; return; end loop; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.oracle new file mode 100644 index 0000000..72b68a0 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/022-rhn_entitlements.sql.oracle @@ -0,0 +1,1861 @@ +-- +-- Copyright (c) 2008--2011 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_entitlements +is + body_version varchar2(100) := ''; + + function find_compatible_sg ( + server_id_in in number, + type_label_in in varchar2 + ) return number is + + cursor servergroups is + select sg.id id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerServerGroupArchCompat ssgac + where s.id = server_id_in + and s.org_id = sg.org_id + and sgt.label = type_label_in + and sg.group_type = sgt.id + and ssgac.server_group_type = sgt.id + and ssgac.server_arch_id = s.server_arch_id + and not exists ( + select 1 + from rhnServerGroupMembers sgm + where sgm.server_group_id = sg.id + and sgm.server_id = s.id); + + + begin + for servergroup in servergroups loop + return servergroup.id; + end loop; + + --no servergroup found + return null; + end find_compatible_sg; + + + -- ******************************************************************* + -- PROCEDURE: remove_org_entitlements + -- + -- Removes both system entitlements and channel subscriptions + -- that are currently assigned to an org and re-assigns to the + -- master org (org_id = 1). + -- + -- When we call this we expect everything to already be unentitled + -- which shoul be handled by delete_org. + -- + -- Called by: delete_org + -- ******************************************************************* + procedure remove_org_entitlements( + org_id_in in number + ) + is + + cursor system_ents is + select sg.id, sg.max_members, sg.group_type + from rhnServerGroup sg + where group_type is not null + and org_id = org_id_in; + + cursor channel_subs is + select pcf.channel_family_id, pcf.max_members + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.org_id is null; + + begin + + for system_ent in system_ents loop + update rhnServerGroup + set max_members = max_members + system_ent.max_members + where org_id = 1 + and group_type = system_ent.group_type; + end loop; + + update rhnServerGroup + set max_members = 0 + where org_id = org_id_in; + + for channel_sub in channel_subs loop + update rhnPrivateChannelFamily + set max_members = max_members + channel_sub.max_members + where org_id = 1 + and channel_family_id = channel_sub.channel_family_id; + end loop; + + update rhnPrivateChannelFamily + set max_members = 0 + where org_id = org_id_in; + + end remove_org_entitlements; + + function entitlement_grants_service ( + entitlement_in in varchar2, + service_level_in in varchar2 + ) return number is + begin + if service_level_in = 'provisioning' then + if entitlement_in = 'provisioning_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'management' then + if entitlement_in = 'enterprise_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'monitoring' then + if entitlement_in = 'monitoring_entitled' then + return 1; + end if; + elsif service_level_in = 'updates' then + return 1; + else + return 0; + end if; + end entitlement_grants_service; + + function lookup_entitlement_group ( + org_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled' + ) return number is + cursor server_groups is + select sg.id server_group_id + from rhnServerGroup sg, + rhnServerGroupType sgt + where sgt.label = type_label_in + and sgt.id = sg.group_type + and sg.org_id = org_id_in; + begin + for sg in server_groups loop + return sg.server_group_id; + end loop; + return rhn_entitlements.create_entitlement_group( + org_id_in, + type_label_in + ); + end lookup_entitlement_group; + + function create_entitlement_group ( + org_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled' + ) return number is + sg_id_val number; + begin + select rhn_server_group_id_seq.nextval + into sg_id_val + from dual; + + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id + ) ( + select sg_id_val, sgt.label, sgt.label, + 0, 0, sgt.id, org_id_in + from rhnServerGroupType sgt + where sgt.label = type_label_in + ); + + return sg_id_val; + end create_entitlement_group; + + function can_entitle_server ( + server_id_in in number, + type_label_in in varchar2 ) + return number is + cursor addon_servergroups (base_label_in in varchar2, + addon_label_in in varchar2) is + select + addon_id + from + rhnSGTypeBaseAddonCompat + where base_id = lookup_sg_type (base_label_in) + and addon_id = lookup_sg_type (addon_label_in); + + previous_ent rhn_entitlements.ents_array; + is_base_in char := 'N'; + is_base_current char := 'N'; + i number := 0; + sgid number := 0; + + begin + + previous_ent := rhn_entitlements.ents_array(); + previous_ent := rhn_entitlements.get_server_entitlement(server_id_in); + + select distinct is_base + into is_base_in + from rhnServerGroupType + where label = type_label_in; + + if previous_ent.count = 0 then + sgid := find_compatible_sg (server_id_in, type_label_in); + if (is_base_in = 'Y' and sgid is not null) then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- there are previous ents, first make sure we're not trying to entitle a base ent + elsif is_base_in = 'Y' then + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + + -- it must be an addon, so proceed with the entitlement + else + + -- find the servers base ent + is_base_current := 'N'; + i := 0; + while is_base_current = 'N' and i <= previous_ent.count + loop + i := i + 1; + select is_base + into is_base_current + from rhnServerGroupType + where label = previous_ent(i); + end loop; + + -- never found a base ent, that would be strange + if is_base_current = 'N' then + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- this for loop verifies the validity of the addon path + for addon_servergroup in addon_servergroups (previous_ent(i), type_label_in) loop + -- find an appropriate sgid for the addon and entitle the server + sgid := find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + end if; + end loop; + + end if; + + return 0; + + end can_entitle_server; + + function can_switch_base ( + server_id_in in integer, + type_label_in in varchar2 + ) return number is + + type_label_in_is_base char(1); + sgid number; + + begin + + begin + select is_base into type_label_in_is_base + from rhnServerGroupType + where label = type_label_in; + exception + when no_data_found then + rhn_exception.raise_exception ( 'invalid_entitlement' ); + end; + + if type_label_in_is_base = 'N' then + rhn_exception.raise_exception ( 'invalid_entitlement' ); + else + sgid := find_compatible_sg ( server_id_in, type_label_in ); + if sgid is not null then + return 1; + else + return 0; + end if; + end if; + + end can_switch_base; + + + procedure entitle_server ( + server_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled' + ) is + sgid number := 0; + is_virt number := 0; + + begin + + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + if is_virt = 0 and (type_label_in = 'virtualization_host' or + type_label_in = 'virtualization_host_platform') then + + is_virt := 1; + end if; + + + + if rhn_entitlements.can_entitle_server(server_id_in, + type_label_in) = 1 then + sgid := find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( rhn_event_id_seq.nextval, server_id_in, + 'added system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + rhn_server.insert_into_servergroup (server_id_in, sgid); + + if is_virt = 1 then + rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + else + rhn_exception.raise_exception ('no_available_server_group'); + end if; + else + rhn_exception.raise_exception ('invalid_entitlement'); + end if; + end entitle_server; + + procedure remove_server_entitlement ( + server_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled', + repoll_virt_guests in number := 1 + ) is + group_id number; + type_is_base char; + is_virt number := 0; + begin + begin + + + -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones... + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + select sg.id, sgt.is_base + into group_id, type_is_base + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnServer s + where s.id = server_id_in + and s.id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.org_id = s.org_id + and sgt.label = type_label_in + and sgt.id = sg.group_type; + + if ( type_is_base = 'Y' ) then + -- unentitle_server should handle everything, don't really need to do anything else special here + unentitle_server ( server_id_in ); + else + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( rhn_event_id_seq.nextval, server_id_in, + 'removed system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platforrm' end ); + + rhn_server.delete_from_servergroup(server_id_in, group_id); + + -- special case: clean up related monitornig data + if type_label_in = 'monitoring_entitled' then + DELETE + FROM state_change + WHERE o_id IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + DELETE /*+index(time_series time_series_probe_id_idx)*/ + FROM time_series + WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1, + (INSTR(o_id, '-', INSTR(o_id, '-') + 1) - INSTR(o_id, '-')) - 1) + IN (SELECT to_char(probe_id) + FROM rhn_check_probe + WHERE host_id = server_id_in); + DELETE + FROM rhn_probe + WHERE recid IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + end if; + + if is_virt = 1 and repoll_virt_guests = 1 then + rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + end if; + + exception + when no_data_found then + rhn_exception.raise_exception('invalid_server_group_member'); + end; + + end remove_server_entitlement; + + + procedure unentitle_server (server_id_in in number) is + + cursor servergroups is + select distinct sgt.label, sg.id server_group_id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerGroupMembers sgm + where s.id = server_id_in + and s.org_id = sg.org_id + and sg.group_type = sgt.id + and sgm.server_group_id = sg.id + and sgm.server_id = s.id; + + is_virt number := 0; + + begin + + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + for servergroup in servergroups loop + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( rhn_event_id_seq.nextval, server_id_in, + 'removed system entitlement ', + case servergroup.label + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + rhn_server.delete_from_servergroup(server_id_in, + servergroup.server_group_id ); + end loop; + + if is_virt = 1 then + rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + end unentitle_server; + + + -- ******************************************************************* + -- PROCEDURE: repoll_virt_guest_entitlements + -- + -- Whenever we add/remove a virtualization_host* entitlement from + -- a host, we can call this procedure to update what type of slots + -- the guests are consuming. + -- + -- If you're removing the entitlement, it's + -- possible the guests will become unentitled if you don't have enough + -- physical slots to cover them. + -- + -- If you're adding the entitlement, you end up freeing up physical + -- slots for other systems. + -- + -- ******************************************************************* + procedure repoll_virt_guest_entitlements(server_id_in in number) + is + + -- All channel families associated with the guests of server_id_in + cursor families is + select distinct cfs.channel_family_id + from + rhnChannelFamilyServers cfs, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = cfs.server_id; + + -- All of server group types associated with the guests of + -- server_id_in + cursor group_types is + select distinct sg.group_type, sgt.label + from + rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + + -- Virtual servers from a certain family belonging to a speicifc + -- host that are consuming physical channel slots over the limit. + cursor virt_servers_cfam(family_id_in in number, quantity_in in number) is + select virtual_system_id + from ( + select rownum, vi.virtual_system_id + from + rhnChannelFamilyMembers cfm, + rhnServerChannel sc, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sc.server_id + and sc.channel_id = cfm.channel_id + and cfm.channel_family_id = family_id_in + order by sc.modified desc + ) + where rownum <= quantity_in; + + -- Virtual servers from a certain family belonging to a speicifc + -- host that are consuming physical system slots over the limit. + cursor virt_servers_sgt(group_type_in in number, quantity_in in number) is + select virtual_system_id + from ( + select rownum, vi.virtual_system_id + from + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = group_type_in + order by sgm.modified desc + ) + where rownum <= quantity_in; + -- Get the orgs of Virtual guests + -- Since they may belong to different orgs + cursor virt_guest_orgs is + select distinct (s.org_id) + from rhnServer s + inner join rhnVirtualInstance vi on vi.virtual_system_id = s.id + where + vi.host_system_id = server_id_in + and s.org_id <> (select s1.org_id from rhnServer s1 where s1.id = vi.host_system_id) ; + + + org_id_val number; + max_members_val number; + max_flex_val number; + current_members_calc number; + sg_id number; + is_virt number := 0; + begin + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + select org_id + into org_id_val + from rhnServer + where id = server_id_in; + + -- deal w/ channel entitlements first ... + for family in families loop + if is_virt = 0 then + -- if the host_server does not have virt + --- find all possible flex slots + -- and set each of the flex eligible guests to Y + UPDATE rhnServerChannel sc set sc.is_fve = 'Y' + where sc.server_id in ( + select virtual_system_id from ( + select rownum, vi.virtual_system_id, sfc.max_members - sfc.current_members as free_slots + from rhnServerFveCapable sfc + inner join rhnVirtualInstance vi on vi.virtual_system_id = sfc.server_id + where vi.host_system_id = server_id_in + and sfc.channel_family_id = family.channel_family_id + order by vi.modified desc + ) + where rownum <= free_slots + ); + else + -- if the host_server has virt + -- set all its flex guests to N + UPDATE rhnServerChannel sc set sc.is_fve = 'N' + where + sc.channel_id in (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = family.channel_family_id) + and sc.is_fve = 'Y' + and sc.server_id in + (select vi.virtual_system_id from rhnVirtualInstance vi + where vi.host_system_id = server_id_in); + end if; + + -- get the current (physical) members of the family + current_members_calc := + rhn_channel.channel_family_current_members(family.channel_family_id, + org_id_val); -- fixed transposed args + + -- get the max members of the family + select max_members + into max_members_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + select fve_max_members + into max_flex_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, so we'll + -- unsubscribe guests from the host first. + + -- hm, i don't think max_members - current_members_calc yielding a negative number + -- will work w/ rownum, swaping 'em in the body of this if... + for virt_server in virt_servers_cfam(family.channel_family_id, + current_members_calc - max_members_val) loop + + rhn_channel.unsubscribe_server_from_family( + virt_server.virtual_system_id, + family.channel_family_id); + end loop; + + -- if we're still over the limit, which would be odd, + -- just prune the group to max_members + -- + -- er... wouldn't we actually have to refresh the values of + -- current_members_calc and max_members_val to actually ever + -- *skip this?? + if current_members_calc > max_members_val then + -- argh, transposed again?! + set_family_count(org_id_val, + family.channel_family_id, + max_members_val, max_flex_val); + --TODO calculate this correctly + end if; + + end if; + + -- update current_members for the family. This will set the value + -- to reflect adding/removing the entitlement. + -- + -- what's the difference of doing this vs the unavoidable set_family_count above? + rhn_channel.update_family_counts(family.channel_family_id, + org_id_val); + + -- It is possible that the guests belong to a different org than the host + -- so we are going to update the family counts in the guests orgs also + for org in virt_guest_orgs loop + rhn_channel.update_family_counts(family.channel_family_id, + org.org_id); + end loop; + end loop; + + for a_group_type in group_types loop + -- get the current *physical* members of the system entitlement type for the org... + -- + -- unlike channel families, it appears the standard rhnServerGroup.max_members represents + -- *physical* slots, vs physical+virt ... boy that's confusing... + + select max_members, id + into max_members_val, sg_id + from rhnServerGroup + where group_type = a_group_type.group_type + and org_id = org_id_val; + + + select count(sep.server_id) into current_members_calc + from rhnServerEntitlementPhysical sep + where sep.server_group_id = sg_id + and sep.server_group_type_id = a_group_type.group_type; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, and we're over the limit, so unsubscribe guests + for virt_server in virt_servers_sgt(a_group_type.group_type, + current_members_calc - max_members_val) loop + rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label); + + -- decrement current_members_calc, we'll use it to reset current_members for the group at the end... + current_members_calc := current_members_calc - 1; + end loop; + + end if; + + update rhnServerGroup set current_members = current_members_calc + where org_id = org_id_val + and group_type = a_group_type.group_type; + + -- I think that's all the house-keeping we have to do... + end loop; + + end repoll_virt_guest_entitlements; + + + function get_server_entitlement ( + server_id_in in number + ) return ents_array is + + cursor server_groups is + select sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_id = server_id_in + and sg.id = sgm.server_group_id + and sgt.id = sg.group_type + and sgt.label in ( + 'sw_mgr_entitled','enterprise_entitled', + 'provisioning_entitled', 'nonlinux_entitled', + 'monitoring_entitled', 'virtualization_host', + 'virtualization_host_platform' + ); + + ent_array ents_array; + + begin + + ent_array := ents_array(); + + for sg in server_groups loop + ent_array.extend; + ent_array(ent_array.count) := sg.label; + end loop; + + return ent_array; + + end get_server_entitlement; + + + -- this desperately needs to be table driven. + procedure modify_org_service ( + org_id_in in number, + service_label_in in varchar2, + enable_in in char + ) is + type roles_v is varray(10) of rhnUserGroupType.label%TYPE; + roles_to_process roles_v; + cursor roles(role_label_in in varchar2) is + select label, id + from rhnUserGroupType + where label = role_label_in; + cursor org_roles(role_label_in in varchar2) is + select 1 + from rhnUserGroup ug, + rhnUserGroupType ugt + where ugt.label = role_label_in + and ug.org_id = org_id_in + and ugt.id = ug.group_type; + + type ents_v is varray(10) of rhnOrgEntitlementType.label%TYPE; + ents_to_process ents_v; + cursor ents(ent_label_in in varchar2) is + select label, id + from rhnOrgEntitlementType + where label = ent_label_in; + cursor org_ents(ent_label_in in varchar2) is + select 1 + from rhnOrgEntitlements oe, + rhnOrgEntitlementType oet + where oet.label = ent_label_in + and oe.org_id = org_id_in + and oet.id = oe.entitlement_id; + create_row char(1); + begin + ents_to_process := ents_v(); + roles_to_process := roles_v(); + -- a bit kludgy, but only for 3.4 really. Certainly no + -- worse than the old code... + if service_label_in = 'enterprise' or + service_label_in = 'management' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'org_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'system_group_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'activation_key_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'org_applicant'; + elsif service_label_in = 'provisioning' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_provisioning'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'system_group_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'activation_key_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + -- another nasty special case... + if enable_in = 'Y' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise'; + end if; + elsif service_label_in = 'monitoring' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_monitor'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'monitoring_admin'; + elsif service_label_in = 'virtualization' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_virtualization'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + elsif service_label_in = 'virtualization_platform' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_virtualization_platform'; + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + elsif service_label_in = 'nonlinux' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_nonlinux'; + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + end if; + + if enable_in = 'Y' then + for i in 1..ents_to_process.count loop + for ent in ents(ents_to_process(i)) loop + create_row := 'Y'; + for oe in org_ents(ent.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnOrgEntitlements(org_id, entitlement_id) + values (org_id_in, ent.id); + end if; + end loop; + end loop; + for i in 1..roles_to_process.count loop + for role in roles(roles_to_process(i)) loop + create_row := 'Y'; + for o_r in org_roles(role.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnUserGroup( + id, name, description, current_members, + group_type, org_id + ) ( + select rhn_user_group_id_seq.nextval, + ugt.name || 's', + ugt.name || 's for Org ' || + o.name || ' ('|| o.id ||')', + 0, ugt.id, o.id + from rhnUserGroupType ugt, + web_customer o + where o.id = org_id_in + and ugt.id = role.id + ); + end if; + end loop; + end loop; + else + for i in 1..ents_to_process.count loop + for ent in ents(ents_to_process(i)) loop + delete from rhnOrgEntitlements + where org_id = org_id_in + and entitlement_id = ent.id; + end loop; + end loop; + end if; + end modify_org_service; + + procedure set_customer_enterprise ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'enterprise', 'Y'); + end set_customer_enterprise; + + procedure set_customer_provisioning ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'provisioning', 'Y'); + end set_customer_provisioning; + + procedure set_customer_monitoring ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'monitoring', 'Y'); + end set_customer_monitoring; + + procedure set_customer_nonlinux ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'nonlinux', 'Y'); + end set_customer_nonlinux; + + procedure unset_customer_enterprise ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'enterprise', 'N'); + end unset_customer_enterprise; + + procedure unset_customer_provisioning ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'provisioning', 'N'); + end unset_customer_provisioning; + + procedure unset_customer_monitoring ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'monitoring', 'N'); + end unset_customer_monitoring; + + procedure unset_customer_nonlinux ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'nonlinux', 'N'); + end unset_customer_nonlinux; + + -- ******************************************************************* + -- PROCEDURE: prune_group + -- Unsubscribes servers consuming physical slots that over the org's + -- limit. + -- Called by: set_group_count, repoll_virt_guest_entitlements + -- ******************************************************************* + procedure prune_group ( + group_id_in in number, + type_in in char, + quantity_in in number, + update_family_countsYN in number := 1 + ) is + cursor usergroups is + select user_id, user_group_id, ugt.label + from rhnUserGroupType ugt, + rhnUserGroup ug, + rhnUserGroupMembers ugm + where 1=1 + and ugm.user_group_id = group_id_in + and ugm.user_id in ( + select user_id + from ( + select rownum row_number, + user_id, + time + from ( + select user_id, + modified time + from rhnUserGroupMembers + where user_group_id = group_id_in + order by time asc + ) + ) + where row_number > quantity_in + ) + and ugm.user_group_id = ug.id + and ug.group_type = ugt.id; + cursor servergroups is + select server_id, server_group_id, sgt.id as group_type_id, sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_group_id = group_id_in + and sgm.server_id in ( + select server_id + from ( + select rownum row_number, + server_id, + time + from ( + select sep.server_id, + sep.modified time + from + rhnServerEntitlementPhysical sep + where + sep.server_group_id = group_id_in + order by time asc + ) + ) + where row_number > quantity_in + ) + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + type_is_base char; + begin + if type_in = 'U' then + update rhnUserGroup + set max_members = quantity_in + where id = group_id_in; + + for ug in usergroups loop + rhn_user.remove_from_usergroup(ug.user_id, ug.user_group_id); + end loop; + elsif type_in = 'S' then + update rhnServerGroup + set max_members = quantity_in + where id = group_id_in; + + for sg in servergroups loop + remove_server_entitlement(sg.server_id, sg.label); + + select is_base + into type_is_base + from rhnServerGroupType sgt + where sgt.id = sg.group_type_id; + + -- if we're removing a base ent, then be sure to + -- remove the server's channel subscriptions. + if ( type_is_base = 'Y' ) then + rhn_channel.clear_subscriptions(sg.server_id, + update_family_countsYN => update_family_countsYN); + end if; + + end loop; + end if; + end prune_group; + + -- ******************************************************************* + -- PROCEDURE: assign_system_entitlement + -- + -- Moves system entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_group_count + -- ******************************************************************* + procedure assign_system_entitlement( + group_label_in in varchar2, + from_org_id_in in number, + to_org_id_in in number, + quantity_in in number + ) + is + prev_ent_count number; + to_org_prev_ent_count number; + new_ent_count number; + new_quantity number; + group_type number; + begin + + begin + select max_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = from_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end; + + begin + select max_members + into to_org_prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = to_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count := 0; + end; + + begin + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_server_group'); + end; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if new_ent_count < 0 then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + + rhn_entitlements.set_group_count(from_org_id_in, + 'S', + group_type, + new_ent_count); + + rhn_entitlements.set_group_count(to_org_id_in, + 'S', + group_type, + new_quantity); + + -- Create or delete the entries in rhnOrgEntitlementType + if group_label_in = 'enterprise_entitled' then + if new_quantity > 0 then + set_customer_enterprise(to_org_id_in); + else + unset_customer_enterprise(to_org_id_in); + end if; + end if; + + if group_label_in = 'provisioning_entitled' then + if new_quantity > 0 then + set_customer_provisioning(to_org_id_in); + else + unset_customer_provisioning(to_org_id_in); + end if; + end if; + + if group_label_in = 'monitoring_entitled' then + if new_quantity > 0 then + set_customer_monitoring(to_org_id_in); + else + unset_customer_monitoring(to_org_id_in); + end if; + end if; + + end assign_system_entitlement; + + -- ******************************************************************* + -- PROCEDURE: assign_channel_entitlement + -- + -- Moves channel entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_family_count + -- ******************************************************************* + procedure assign_channel_entitlement( + channel_family_label_in in varchar2, + from_org_id_in in number, + to_org_id_in in number, + quantity_in in number, + flex_in in number + ) + is + from_org_prev_ent_count number; + from_org_prev_ent_count_flex number; + new_ent_count number; + new_ent_count_flex number; + to_org_prev_ent_count number; + to_org_prev_ent_count_flex number; + new_quantity number; + new_flex number; + cfam_id number; + begin + + begin + select max_members + into from_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end; + + begin + select max_members + into to_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count := 0; + end; + + begin + select fve_max_members + into from_org_prev_ent_count_flex + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end; + + begin + select fve_max_members + into to_org_prev_ent_count_flex + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count_flex := 0; + end; + + begin + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_channel_family'); + end; + + new_ent_count := from_org_prev_ent_count - quantity_in; + new_ent_count_flex := from_org_prev_ent_count_flex - flex_in; + + if from_org_prev_ent_count >= new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if from_org_prev_ent_count_flex >= new_ent_count_flex then + new_flex := to_org_prev_ent_count_flex + flex_in; + end if; + + + if new_ent_count < 0 then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + if new_ent_count_flex < 0 then + rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + + + + rhn_entitlements.set_family_count(from_org_id_in, cfam_id, + new_ent_count, new_ent_count_flex); + + rhn_entitlements.set_family_count(to_org_id_in, + cfam_id, + new_quantity, new_flex); + + end assign_channel_entitlement; + + -- ******************************************************************* + -- PROCEDURE: activate_system_entitlement + -- + -- Sets the values in rhnServerGroup for a given rhnServerGroupType. + -- + -- Calls: set_group_count to update, prune, or create the group. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if all entitlements + -- in the org are used so the free entitlements would not cover + -- the difference when descreasing the number of entitlements. + -- ******************************************************************* + procedure activate_system_entitlement( + org_id_in in number, + group_label_in in varchar2, + quantity_in in number + ) + is + prev_ent_count number; + prev_ent_count_sum number; + group_type number; + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + begin + select current_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.group_type = sgt.id + and sgt.label = group_label_in + and sg.org_id = org_id_in; + exception + when NO_DATA_FOUND then + prev_ent_count := 0; + end; + + begin + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_server_group'); + end; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the allocated count in this org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- don't update family counts after every server + -- will do bulk update afterwards + rhn_entitlements.set_group_count(org_id_in, + 'S', + group_type, + quantity_in, + update_family_countsYN => 0); + -- bulk update family counts + rhn_channel.update_group_family_counts(group_label_in, org_id_in); + end if; + + + end activate_system_entitlement; + + -- ******************************************************************* + -- PROCEDURE: activate_channel_entitlement + -- + -- Calls: set_family_count to update, prune, or create the family + -- permission bucket. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if there are not enough + -- entitlements in the org to cover the difference when you are + -- descreasing the number of entitlements. + -- + -- The backend code in Python is expected to do whatever arithmetics + -- is needed. + -- ******************************************************************* + procedure activate_channel_entitlement( + org_id_in in number, + channel_family_label_in in varchar2, + quantity_in in number, + flex_in in number + + ) + is + prev_ent_count number; + prev_flex_count number; + prev_ent_count_sum number; + cfam_id number; + reduce_quantity number; + total_flex_capable number; + cursor to_convert_reg (channel_family_id_val in number, org_id_in in number, quantity in number) + is + select SC.server_id + from rhnServerChannel SC inner join + rhnServer S on S.id = SC.server_id + where + is_fve = 'Y' + and S.org_id = org_id_in + and SC.channel_id in + (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = channel_family_id_val) + and rownum <= quantity; + cursor to_convert_flex (channel_family_id_val in number, org_id_in in number, quantity in number) + is + select server_id + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id and + rownum <= quantity; + + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + begin + select current_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + prev_ent_count := 0; + end; + + begin + select pcf.fve_current_members + into prev_flex_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + prev_flex_count := 0; + end; + + + begin + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_channel_family'); + end; + + -- if there are too few flex entitlements + if flex_in < prev_flex_count then + -- and if the extra we need is less than or equal to our extra regular entitlements + if prev_flex_count - flex_in <= quantity_in - prev_ent_count then + reduce_quantity := prev_flex_count - flex_in; + -- We need to convert some systems from flex guest to regular + for system in to_convert_reg(cfam_id, org_id_in, reduce_quantity) loop + --rhn_channel.convert_to_regular(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set sc.is_fve = 'N' + where sc.server_id = system.server_id + and sc.channel_id in (select cfm.channel_id + from rhnChannelFamilyMembers cfm + where cfm.channel_family_id = cfam_id); + end loop; + + --reset previous counts + prev_ent_count := prev_ent_count + reduce_quantity; + prev_flex_count := prev_flex_count - reduce_quantity; + else + rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + end if; + + -- if there are too few regular entitlements, and extra flex entitlements + if quantity_in < prev_ent_count and prev_flex_count < flex_in then + -- how many flex-capable systems (that aren't using flex) do we have + select count(*) + into total_flex_capable + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id; + -- if we have enough flex capable machines that is at least + -- as many as what we are over on + if total_flex_capable >= prev_ent_count - quantity_in then + reduce_quantity := prev_ent_count - quantity_in; + for system in to_convert_flex(cfam_id, org_id_in, reduce_quantity) loop +-- rhn_channel.convert_to_fve(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set sc.is_fve = 'Y' + where sc.server_id = system.server_id + and sc.channel_id in (select cfm.channel_id + from rhnChannelFamilyMembers cfm + where cfm.channel_family_id = cfam_id); + end loop; + prev_ent_count := prev_ent_count - reduce_quantity; + prev_flex_count := prev_flex_count + reduce_quantity; + end if; + end if; + + + + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the count in that one org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- even if we've manually converted systems to/from flex above + -- set_family_count should call update_family_counts, to reset + -- current used slots + rhn_entitlements.set_family_count(org_id_in, cfam_id, + quantity_in, flex_in); + end if; + + end activate_channel_entitlement; + + + procedure set_group_count ( + customer_id_in in number, + type_in in char, + group_type_in in number, + quantity_in in number, + update_family_countsYN in number := 1 + ) is + group_id number; + quantity number; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + + if type_in = 'U' then + select rug.id + into group_id + from rhnUserGroup rug + where 1=1 + and rug.org_id = customer_id_in + and rug.group_type = group_type_in; + elsif type_in = 'S' then + select rsg.id + into group_id + from rhnServerGroup rsg + where 1=1 + and rsg.org_id = customer_id_in + and rsg.group_type = group_type_in; + end if; + + rhn_entitlements.prune_group( + group_id, + type_in, + quantity, + update_family_countsYN + ); + exception + when no_data_found then + if type_in = 'U' then + insert into rhnUserGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select rhn_user_group_id_seq.nextval, name, name, + quantity, 0, id, customer_id_in, + sysdate, sysdate + from rhnUserGroupType + where id = group_type_in + ); + elsif type_in = 'S' then + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select rhn_server_group_id_seq.nextval, name, name, + quantity, 0, id, customer_id_in, + sysdate, sysdate + from rhnServerGroupType + where id = group_type_in + ); + end if; + end set_group_count; + + -- ******************************************************************* + -- PROCEDURE: prune_family + -- Unsubscribes servers consuming physical slots from the channel family + -- that are over the org's limit. + -- Called by: set_family_count + -- ******************************************************************* + procedure prune_family ( + customer_id_in in number, + channel_family_id_in in number, + quantity_in in number, + flex_in in number + ) is + is_fve_in char; + tmp_quantity number; + + cursor serverchannels is + select sc.server_id, + sc.channel_id + from rhnServerChannel sc, + rhnChannelFamilyMembers cfm + where 1=1 + and cfm.channel_family_id = channel_family_id_in + and cfm.channel_id = sc.channel_id + and server_id in ( + select server_id + from ( + select server_id, time, rownum row_number + from ( + select rs.id server_id, rcfm.modified time + from rhnServerChannel rsc, + rhnChannelFamilyMembers rcfm, + rhnServer rs + where 1=1 + and rs.org_id = customer_id_in + and rs.id = rsc.server_id + and rsc.channel_id = rcfm.channel_id + and rcfm.channel_family_id = channel_family_id_in + and rsc.is_fve = is_fve_in + -- we only want to grab servers consuming + -- physical slots. + and exists ( + select 1 + from rhnChannelFamilyServerPhysical cfsp + where cfsp.server_id = rs.id + and cfsp.channel_family_id = + channel_family_id_in + ) + order by time asc + ) + ) + where row_number > tmp_quantity + ); + begin + -- if we get a null customer_id, this is completely bogus. + if customer_id_in is null then + return; + end if; + + tmp_quantity := quantity_in; + is_fve_in := 'N'; + + for sc in serverchannels loop + rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, + update_family_countsYN => 0); + + tmp_quantity := flex_in; + is_fve_in := 'Y'; + for sc in serverchannels loop + rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, + update_family_countsYN => 0); + end loop; + + + + end loop; + rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); + end prune_family; + + procedure set_family_count ( + customer_id_in in number, + channel_family_id_in in number, + quantity_in in number, + flex_in in number + ) is + cursor privperms is + select 1 + from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + cursor pubperms is + select o.id org_id + from web_customer o, + rhnPublicChannelFamily pcf + where pcf.channel_family_id = channel_family_id_in; + quantity number; + done number := 0; + flex number; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + flex := flex_in; + if flex is not null and flex < 0 then + flex := 0; + end if; + + + if customer_id_in is not null then + for perm in privperms loop + rhn_entitlements.prune_family(customer_id_in, channel_family_id_in, + quantity, flex); + + if quantity is null and flex is null then + delete from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + else + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + update rhnPrivateChannelFamily + set fve_max_members = flex + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + end if; + return; + end loop; + + insert into rhnPrivateChannelFamily ( + channel_family_id, org_id, max_members, current_members, fve_max_members, fve_current_members + ) values ( + channel_family_id_in, customer_id_in, quantity, 0, flex, 0 ); + return; + end if; + + for perm in pubperms loop + if quantity = 0 then + rhn_entitlements.prune_family( + perm.org_id, + channel_family_id_in, + quantity, + flex + ); + if done = 0 then + delete from rhnPublicChannelFamily + where channel_family_id = channel_family_id_in; + end if; + end if; + done := 1; + end loop; + -- if done's not 1, then we don't have any entitlements + if done != 1 then + insert into rhnPublicChannelFamily ( + channel_family_id + ) values ( + channel_family_id_in + ); + end if; + end set_family_count; + + -- this expects quantity_in to be the number of available slots, not the + -- max_members of the server group. If you give it too many, it'll fail + -- and raise servergroup_max_members. + -- We should NEVER run this unless we're SURE that we won't + -- be violating the max. + procedure entitle_last_modified_servers ( + customer_id_in in number, + type_label_in in varchar2, + quantity_in in number + ) is + -- find the servers that aren't currently in slots + cursor servers(cid_in in number, quant_in in number) is + select server_id + from ( + select rownum row_number, + server_id + from ( + select rs.id server_iD + from rhnServer rs + where 1=1 + and rs.org_id = cid_in + and not exists ( + select 1 + from rhnServerGroup sg, + rhnServerGroupMembers rsgm + where rsgm.server_id = rs.id + and rsgm.server_group_id = sg.id + and sg.group_type is not null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = + rs.id + ) order by modified desc + ) + ) + where row_number <= quant_in; + begin + for server in servers(customer_id_in, quantity_in) loop + rhn_entitlements.entitle_server(server.server_id, type_label_in); + end loop; + end entitle_last_modified_servers; + + procedure subscribe_newest_servers ( + customer_id_in in number + ) is + -- find servers without base channels + cursor servers(cid_in in number) is + select s.id + from rhnServer s + where 1=1 + and s.org_id = cid_in + and not exists ( + select 1 + from rhnChannel c, + rhnServerChannel sc + where sc.server_id = s.id + and sc.channel_id = c.id + and c.parent_channel is null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = s.id + ) + order by s.modified desc; + channel_id number; + begin + for server in servers(customer_id_in) loop + channel_id := rhn_channel.guess_server_base(server.id); + if channel_id is not null then + begin + rhn_channel.subscribe_server(server.id, channel_id); + commit; + -- exception is really channel_family_no_subscriptions + exception + when others then + null; + end; + end if; + end loop; + end subscribe_newest_servers; +end rhn_entitlements; +/ +show errors

1 0

Changes to 'refs/tags/yum-rhn-plugin-1.4.8-1'
by Miroslav Suchý 30 Mar '11

30 Mar '11

Tag 'yum-rhn-plugin-1.4.8-1' created by Miroslav Suchý <msuchy(a)redhat.com> at 2011-03-30 15:18 +0000 Tagging package [yum-rhn-plugin] version [1.4.8-1] in directory [client/rhel/yum-rhn-plugin/]. Changes since oracle-server-scripts-10.2.0.54-1: Miroslav Suchý (4): older yum do not have _repos_persistdir fix variable typo 683200 - ssl cert can not be unicode string Automatic commit of package [yum-rhn-plugin] release [1.4.8-1]. --- client/rhel/yum-rhn-plugin/rhnplugin.py | 9 +++++---- client/rhel/yum-rhn-plugin/yum-rhn-plugin.spec | 7 ++++++- rel-eng/packages/yum-rhn-plugin | 2 +- 3 files changed, 12 insertions(+), 6 deletions(-) ---

1 0

2 commits - client/rhel rel-eng/packages
by Miroslav Suchý 30 Mar '11

30 Mar '11

client/rhel/yum-rhn-plugin/rhnplugin.py | 4 ++-- client/rhel/yum-rhn-plugin/yum-rhn-plugin.spec | 7 ++++++- rel-eng/packages/yum-rhn-plugin | 2 +- 3 files changed, 9 insertions(+), 4 deletions(-) New commits: commit b3753abaad072289421c78d88d1aa941db54158f Author: Miroslav Suchý <msuchy(a)redhat.com> Date: Wed Mar 30 17:18:41 2011 +0200 Automatic commit of package [yum-rhn-plugin] release [1.4.8-1]. diff --git a/client/rhel/yum-rhn-plugin/yum-rhn-plugin.spec b/client/rhel/yum-rhn-plugin/yum-rhn-plugin.spec index ebd3d33..b4989b3 100644 --- a/client/rhel/yum-rhn-plugin/yum-rhn-plugin.spec +++ b/client/rhel/yum-rhn-plugin/yum-rhn-plugin.spec @@ -1,6 +1,6 @@ Summary: RHN support for yum Name: yum-rhn-plugin -Version: 1.4.7 +Version: 1.4.8 Release: 1%{?dist} License: GPLv2 Group: System Environment/Base @@ -72,6 +72,11 @@ fi %doc LICENSE %changelog +* Wed Mar 30 2011 Miroslav Suchý 1.4.8-1 +- 683200 - ssl cert can not be unicode string +- fix variable typo +- older yum do not have _repos_persistdir + * Wed Mar 30 2011 Miroslav Suchý <msuchy(a)redhat.com> 1.4.7-1 - 683200 - support IDN diff --git a/rel-eng/packages/yum-rhn-plugin b/rel-eng/packages/yum-rhn-plugin index b5bfbae..4862fbd 100644 --- a/rel-eng/packages/yum-rhn-plugin +++ b/rel-eng/packages/yum-rhn-plugin @@ -1 +1 @@ -1.4.7-1 client/rhel/yum-rhn-plugin/ +1.4.8-1 client/rhel/yum-rhn-plugin/ commit 70357c3b0bf587b40d092ce4ba467c3901321323 Author: Miroslav Suchý <msuchy(a)redhat.com> Date: Wed Mar 30 17:15:45 2011 +0200 683200 - ssl cert can not be unicode string addressing: Traceback (most recent call last): File "/usr/bin/yum", line 29, in <module> yummain.user_main(sys.argv[1:], exit_code=True) File "/usr/share/yum-cli/yummain.py", line 274, in user_main errcode = main(args) File "/usr/share/yum-cli/yummain.py", line 129, in main result, resultmsgs = base.doCommands() File "/usr/share/yum-cli/cli.py", line 428, in doCommands self._getTs(needTsRemove) File "/usr/lib/python2.6/site-packages/yum/depsolve.py", line 99, in _getTs self._getTsInfo(remove_only) File "/usr/lib/python2.6/site-packages/yum/depsolve.py", line 110, in _getTsInfo pkgSack = self.pkgSack File "/usr/lib/python2.6/site-packages/yum/__init__.py", line 864, in <lambda> pkgSack = property(fget=lambda self: self._getSacks(), File "/usr/lib/python2.6/site-packages/yum/__init__.py", line 650, in _getSacks self.repos.populateSack(which=repos) File "/usr/lib/python2.6/site-packages/yum/repos.py", line 289, in populateSack sack.populate(repo, mdtype, callback, cacheonly) File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 164, in populate if self._check_db_version(repo, mydbtype): File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 222, in _check_db_version return repo._check_db_version(mdtype) File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1255, in _check_db_version repoXML = self.repoXML File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1454, in <lambda> repoXML = property(fget=lambda self: self._getRepoXML(), File "/usr/share/yum-plugins/rhnplugin.py", line 482, in _getRepoXML return YumRepository._getRepoXML(self) File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1446, in _getRepoXML self._loadRepoXML(text=self) File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1436, in _loadRepoXML return self._groupLoadRepoXML(text, self._mdpolicy2mdtypes()) File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1411, in _groupLoadRepoXML if self._commonLoadRepoXML(text): File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1229, in _commonLoadRepoXML result = self._getFileRepoXML(local, text) File "/usr/lib/python2.6/site-packages/yum/yumRepo.py", line 1015, in _getFileRepoXML size=102400) # setting max size as 100K File "/usr/share/yum-plugins/rhnplugin.py", line 303, in _getFile start, end, copy_local, checkfunc, text, reget, cache, size) File "/usr/share/yum-plugins/rhnplugin.py", line 406, in _noExceptionWrappingGet size = size File "/usr/lib/python2.6/site-packages/urlgrabber/grabber.py", line 985, in urlgrab return self._retry(opts, retryfunc, url, filename) File "/usr/lib/python2.6/site-packages/urlgrabber/grabber.py", line 886, in _retry r = apply(func, (opts,) + args, {}) File "/usr/lib/python2.6/site-packages/urlgrabber/grabber.py", line 971, in retryfunc fo = PyCurlFileObject(url, filename, opts) File "/usr/lib/python2.6/site-packages/urlgrabber/grabber.py", line 1066, in __init__ self._do_open() File "/usr/lib/python2.6/site-packages/urlgrabber/grabber.py", line 1355, in _do_open self._set_opts() File "/usr/lib/python2.6/site-packages/urlgrabber/grabber.py", line 1195, in _set_opts self.curl_obj.setopt(pycurl.CAPATH, opts.ssl_ca_cert) TypeError: invalid arguments to setopt diff --git a/client/rhel/yum-rhn-plugin/rhnplugin.py b/client/rhel/yum-rhn-plugin/rhnplugin.py index 48df830..025a909 100644 --- a/client/rhel/yum-rhn-plugin/rhnplugin.py +++ b/client/rhel/yum-rhn-plugin/rhnplugin.py @@ -377,7 +377,7 @@ class RhnRepo(YumRepository): reget = reget, checkfunc=checkfunc, http_headers=headers, - ssl_ca_cert = self.sslcacert, + ssl_ca_cert = self.sslcacert.encode('utf-8'), timeout=self.timeout, size = size ) @@ -401,7 +401,7 @@ class RhnRepo(YumRepository): reget = reget, checkfunc=checkfunc, http_headers=headers, - ssl_ca_cert = self.sslcacert, + ssl_ca_cert = self.sslcacert.encode('utf-8'), timeout=self.timeout, size = size )

1 0

client/rhel
by Miroslav Suchý 30 Mar '11

30 Mar '11

client/rhel/yum-rhn-plugin/rhnplugin.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) New commits: commit 0daa72c1a285e1daab1ecaf08e8b2cac457728c1 Author: Miroslav Suchý <msuchy(a)redhat.com> Date: Wed Mar 30 17:10:15 2011 +0200 fix variable typo diff --git a/client/rhel/yum-rhn-plugin/rhnplugin.py b/client/rhel/yum-rhn-plugin/rhnplugin.py index cf43372..48df830 100644 --- a/client/rhel/yum-rhn-plugin/rhnplugin.py +++ b/client/rhel/yum-rhn-plugin/rhnplugin.py @@ -63,7 +63,7 @@ def init_hook(conduit): cachefile = open(cachefilename, 'r') repolist = [ line.rstrip().split(' ', 1) for line in cachefile.readlines()] cachefile.close() - for repoitem in repolist: + for repo_item in repolist: if len(repo_item) == 1: repo_item.append('') (repoid, reponame) = repo_item

1 0

client/rhel
by Miroslav Suchý 30 Mar '11

30 Mar '11

client/rhel/yum-rhn-plugin/rhnplugin.py | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) New commits: commit 31627c63b7ccf7598fa0c13d1cfb25d109a253d5 Author: Miroslav Suchý <msuchy(a)redhat.com> Date: Wed Mar 30 16:58:20 2011 +0200 older yum do not have _repos_persistdir this attribute is new in yum-3.2.29 if yum is <= 3.2.27 just ignore it, because it does not use base_persistdir either diff --git a/client/rhel/yum-rhn-plugin/rhnplugin.py b/client/rhel/yum-rhn-plugin/rhnplugin.py index 8a9c113..cf43372 100644 --- a/client/rhel/yum-rhn-plugin/rhnplugin.py +++ b/client/rhel/yum-rhn-plugin/rhnplugin.py @@ -169,7 +169,8 @@ def prereposetup_hook(conduit): repo.sslcacert = sslcacert repo.enablegroups = enablegroups repo.metadata_expire = metadata_expire - repo.base_persistdir = conduit.getConf()._repos_persistdir + if hasattr(conduit.getConf(), _repos_persistdir): + repo.base_persistdir = conduit.getConf()._repos_persistdir repoOptions = getRHNRepoOptions(conduit, repo.id) if repoOptions: for o in repoOptions:

1 0

Changes to 'refs/tags/oracle-server-scripts-10.2.0.54-1'
by Michael Mraka 30 Mar '11

30 Mar '11

Tag 'oracle-server-scripts-10.2.0.54-1' created by Michael Mraka <michael.mraka(a)redhat.com> at 2011-03-30 14:11 +0000 Tagging package [oracle-server-scripts] version [10.2.0.54-1] in directory [projects/oracle-server-scripts/]. Changes since oracle-selinux-0.1.23.25-1: Michael Mraka (2): oracle_sqlplus_t is not able to write to logs Automatic commit of package [oracle-server-scripts] release [10.2.0.54-1]. --- projects/oracle-server-scripts/create-db.sh | 3 ++- projects/oracle-server-scripts/embedded-createdb.tmpl | 13 ------------- projects/oracle-server-scripts/embedded-upgradedb-10g.tmpl | 1 - projects/oracle-server-scripts/embedded-upgradedb.tmpl | 1 - projects/oracle-server-scripts/oracle-server-scripts.spec | 5 ++++- rel-eng/packages/oracle-server-scripts | 2 +- spacewalk/setup/share/oracle/upgrade-db.sh | 5 +++-- 7 files changed, 10 insertions(+), 20 deletions(-) ---

1 0

Jump to page:

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

spacewalk-commits March 2011