rel-eng/packages/spacewalk-schema
| 2
schema/spacewalk/common/views/rhnServerFveCapable.sql
| 56 ++++++++
schema/spacewalk/oracle/packages/rhn_channel.pkb
| 25 ---
schema/spacewalk/oracle/packages/rhn_entitlements.pkb
| 68 +++++++++-
schema/spacewalk/spacewalk-schema.spec
| 6
schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/013-rhnServerFveCapable.sql
| 56 ++++++++
schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/015-rhn_channel-pkb.sql
| 25 ---
schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/017-rhn_entitlements-pkb.sql
| 68 +++++++++-
8 files changed, 260 insertions(+), 46 deletions(-)
New commits:
commit fad67a52ba4c2befa38cba59c364d347243e80ad
Author: Justin Sherrill <jsherril(a)redhat.com>
Date: Wed Jul 7 17:24:24 2010 -0400
Automatic commit of package [spacewalk-schema] release [1.1.10-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema
index 700b327..540f54e 100644
--- a/rel-eng/packages/spacewalk-schema
+++ b/rel-eng/packages/spacewalk-schema
@@ -1 +1 @@
-1.1.9-1 schema/spacewalk/
+1.1.10-1 schema/spacewalk/
diff --git a/schema/spacewalk/spacewalk-schema.spec
b/schema/spacewalk/spacewalk-schema.spec
index 56df9e6..9eb37c0 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.1.9
+Version: 1.1.10
Release: 1%{?dist}
Source0: %{name}-%{version}.tar.gz
@@ -58,6 +58,10 @@ rm -rf $RPM_BUILD_ROOT
%{_mandir}/man1/spacewalk-schema-upgrade*
%changelog
+* Wed Jul 07 2010 Justin Sherrill <jsherril(a)redhat.com> 1.1.10-1
+- allowing satellite-sync to juggle entitlements between flex guest and regular
+ entitlement slots if it can (jsherril(a)redhat.com)
+
* Fri Jul 02 2010 Jan Pazdziora 1.1.9-1
- bug fixes to repo sync schema upgrades and sql (shughes(a)redhat.com)
- 526864 - don't allow duplicated rows in rhnServerPackage
commit 43e936a53b1783ef2e95681b2b38c4b9995442b1
Author: Justin Sherrill <jsherril(a)redhat.com>
Date: Wed Jul 7 17:22:30 2010 -0400
allowing satellite-sync to juggle entitlements between flex guest and regular
entitlement slots if it can
diff --git a/schema/spacewalk/common/views/rhnServerFveCapable.sql
b/schema/spacewalk/common/views/rhnServerFveCapable.sql
new file mode 100644
index 0000000..bc28434
--- /dev/null
+++ b/schema/spacewalk/common/views/rhnServerFveCapable.sql
@@ -0,0 +1,56 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+--
+--
+--
+-- A view to list systems that fve capable but that are not actually using
+-- and fve entitlement
+create or replace view
+rhnServerFveCapable (
+ server_id,
+ server_org_id,
+ channel_family_id,
+ current_members,
+ max_members,
+ channel_family_name
+)
+as
+select S.id as server_id,
+ S.org_id as server_org_id,
+ cf.id as channel_family_id,
+ pcf.FVE_CURRENT_MEMBERS as current_members,
+ pcf.FVE_MAX_MEMBERS as max_members,
+ cf.name as channel_family_name
+from
+ RhnVirtualInstance vi
+ inner join rhnServer s on vi.virtual_system_id = s.id
+ inner join rhnServerChannel sc on sc.server_id = s.id
+ inner join rhnChannelFamilyMembers cfm on cfm.channel_id = sc.channel_id
+ inner join rhnChannelFamily cf on cf.id = cfm.channel_family_id
+ inner join rhnPrivateChannelFamily pcf on pcf.channel_family_id = cf.id and
pcf.org_id = s.org_id
+where sc.is_fve = 'N'
+ AND (vi.host_system_id is null OR
+ exists (
+ select sg.id
+ from rhnServerGroupMembers sgm
+ inner join rhnServerGroup sg on sgm.server_group_id = sg.id
+ inner join rhnServerGroupType sgt on sgt.id = sg.group_type
+ inner join rhnServer s2 on s2.id = sgm.server_id
+ where
+ s2.org_id = s.org_id
+ and s2.id = vi.host_system_id
+ and sgt.label not in ('virtualization_host'
,'virtualization_host_platform') )
+ );
+
diff --git a/schema/spacewalk/oracle/packages/rhn_channel.pkb
b/schema/spacewalk/oracle/packages/rhn_channel.pkb
index a251e1d..f9358d8 100644
--- a/schema/spacewalk/oracle/packages/rhn_channel.pkb
+++ b/schema/spacewalk/oracle/packages/rhn_channel.pkb
@@ -180,27 +180,9 @@ IS
IS
CURSOR fve_convertible_entries IS
select 1 from
- RhnVirtualInstance vi
- inner join rhnServer s on vi.virtual_system_id = s.id
- inner join rhnServerChannel sc on sc.server_id = s.id
- inner join rhnChannelFamilyMembers cfm on cfm.channel_id =
sc.channel_id
- inner join rhnChannelFamily cf on cf.id = cfm.channel_family_id
- inner join rhnPrivateChannelFamily pcf on pcf.channel_family_id =
cf.id and pcf.org_id = s.org_id
- where sc.is_fve = 'N'
- AND sc.server_id = server_id_in
- AND cf.id = channel_family_id_val
- AND (vi.host_system_id is null OR
- exists (
- select sg.id from rhnServerGroupMembers sgm
- inner join rhnServerGroup sg on sgm.server_group_id = sg.id
- inner join rhnServerGroupType sgt on sgt.id = sg.group_type
- inner join rhnServer s2 on s2.id = sgm.server_id
- where
- s2.org_id = s.org_id
- and s2.id = vi.host_system_id
- and sgt.label not in ('virtualization_host'
,'virtualization_host_platform') )
- );
-
+ rhnServerFveCapable cap
+ where cap.server_id = server_id_in
+ AND cap.channel_family_id = channel_family_id_val;
BEGIN
FOR entry IN fve_convertible_entries LOOP
return 1;
@@ -209,6 +191,7 @@ IS
END can_convert_to_fve;
+
-- Converts server channel_family to use a flex entitlement
PROCEDURE convert_to_fve(server_id_in IN NUMBER, channel_family_id_val IN NUMBER)
IS
diff --git a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb
b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb
index b26fdf9..8ea66da 100644
--- a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb
+++ b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb
@@ -1350,12 +1350,34 @@ is
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;
+
begin
-- Fetch the current entitlement count for the org
@@ -1398,11 +1420,52 @@ is
'invalid_channel_family');
end;
+ -- if there are too few flex entitlements
if flex_in < prev_flex_count then
- rhn_exception.raise_exception(
+ -- 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
+ 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.
@@ -1410,6 +1473,9 @@ is
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;
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/013-rhnServerFveCapable.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/013-rhnServerFveCapable.sql
new file mode 100644
index 0000000..bc28434
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/013-rhnServerFveCapable.sql
@@ -0,0 +1,56 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+--
+--
+--
+-- A view to list systems that fve capable but that are not actually using
+-- and fve entitlement
+create or replace view
+rhnServerFveCapable (
+ server_id,
+ server_org_id,
+ channel_family_id,
+ current_members,
+ max_members,
+ channel_family_name
+)
+as
+select S.id as server_id,
+ S.org_id as server_org_id,
+ cf.id as channel_family_id,
+ pcf.FVE_CURRENT_MEMBERS as current_members,
+ pcf.FVE_MAX_MEMBERS as max_members,
+ cf.name as channel_family_name
+from
+ RhnVirtualInstance vi
+ inner join rhnServer s on vi.virtual_system_id = s.id
+ inner join rhnServerChannel sc on sc.server_id = s.id
+ inner join rhnChannelFamilyMembers cfm on cfm.channel_id = sc.channel_id
+ inner join rhnChannelFamily cf on cf.id = cfm.channel_family_id
+ inner join rhnPrivateChannelFamily pcf on pcf.channel_family_id = cf.id and
pcf.org_id = s.org_id
+where sc.is_fve = 'N'
+ AND (vi.host_system_id is null OR
+ exists (
+ select sg.id
+ from rhnServerGroupMembers sgm
+ inner join rhnServerGroup sg on sgm.server_group_id = sg.id
+ inner join rhnServerGroupType sgt on sgt.id = sg.group_type
+ inner join rhnServer s2 on s2.id = sgm.server_id
+ where
+ s2.org_id = s.org_id
+ and s2.id = vi.host_system_id
+ and sgt.label not in ('virtualization_host'
,'virtualization_host_platform') )
+ );
+
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/015-rhn_channel-pkb.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/015-rhn_channel-pkb.sql
index a251e1d..f9358d8 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/015-rhn_channel-pkb.sql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/015-rhn_channel-pkb.sql
@@ -180,27 +180,9 @@ IS
IS
CURSOR fve_convertible_entries IS
select 1 from
- RhnVirtualInstance vi
- inner join rhnServer s on vi.virtual_system_id = s.id
- inner join rhnServerChannel sc on sc.server_id = s.id
- inner join rhnChannelFamilyMembers cfm on cfm.channel_id =
sc.channel_id
- inner join rhnChannelFamily cf on cf.id = cfm.channel_family_id
- inner join rhnPrivateChannelFamily pcf on pcf.channel_family_id =
cf.id and pcf.org_id = s.org_id
- where sc.is_fve = 'N'
- AND sc.server_id = server_id_in
- AND cf.id = channel_family_id_val
- AND (vi.host_system_id is null OR
- exists (
- select sg.id from rhnServerGroupMembers sgm
- inner join rhnServerGroup sg on sgm.server_group_id = sg.id
- inner join rhnServerGroupType sgt on sgt.id = sg.group_type
- inner join rhnServer s2 on s2.id = sgm.server_id
- where
- s2.org_id = s.org_id
- and s2.id = vi.host_system_id
- and sgt.label not in ('virtualization_host'
,'virtualization_host_platform') )
- );
-
+ rhnServerFveCapable cap
+ where cap.server_id = server_id_in
+ AND cap.channel_family_id = channel_family_id_val;
BEGIN
FOR entry IN fve_convertible_entries LOOP
return 1;
@@ -209,6 +191,7 @@ IS
END can_convert_to_fve;
+
-- Converts server channel_family to use a flex entitlement
PROCEDURE convert_to_fve(server_id_in IN NUMBER, channel_family_id_val IN NUMBER)
IS
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/017-rhn_entitlements-pkb.sql
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/017-rhn_entitlements-pkb.sql
index b26fdf9..8ea66da 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/017-rhn_entitlements-pkb.sql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.0-to-spacewalk-schema-1.1/017-rhn_entitlements-pkb.sql
@@ -1350,12 +1350,34 @@ is
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;
+
begin
-- Fetch the current entitlement count for the org
@@ -1398,11 +1420,52 @@ is
'invalid_channel_family');
end;
+ -- if there are too few flex entitlements
if flex_in < prev_flex_count then
- rhn_exception.raise_exception(
+ -- 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
+ 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.
@@ -1410,6 +1473,9 @@ is
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;