schema/spacewalk/common/data/rhnException.sql | 4
schema/spacewalk/postgres/packages/rhn_bel.pkb | 20
schema/spacewalk/postgres/packages/rhn_cache.pkb | 17
schema/spacewalk/postgres/packages/rhn_channel.pkb | 211 -
schema/spacewalk/postgres/packages/rhn_config.pkb | 77
schema/spacewalk/postgres/packages/rhn_config.pks | 21
schema/spacewalk/postgres/packages/rhn_config_channel.pkb | 40
schema/spacewalk/postgres/packages/rhn_config_channel.pks | 37
schema/spacewalk/postgres/packages/rhn_date_manip.pkb | 59
schema/spacewalk/postgres/packages/rhn_date_manip.pks | 23
schema/spacewalk/postgres/packages/rhn_entitlements.pkb | 1684 ++++++++++
schema/spacewalk/postgres/packages/rhn_entitlements.pks | 308 +
schema/spacewalk/postgres/packages/rhn_exception.pks | 3
schema/spacewalk/postgres/packages/rhn_org.pkb | 212 -
schema/spacewalk/postgres/packages/rhn_org.pks | 9
schema/spacewalk/postgres/packages/rhn_package.pkb | 24
schema/spacewalk/postgres/packages/rhn_package.pks | 10
schema/spacewalk/postgres/packages/rhn_qos.pkb | 93
schema/spacewalk/postgres/packages/rhn_qos.pks | 74
schema/spacewalk/postgres/packages/rhn_quota.pkb | 19
schema/spacewalk/postgres/packages/rhn_server.pkb | 61
schema/spacewalk/postgres/packages/rhn_user.pkb | 4
schema/spacewalk/postgres/procs/pxt_session_cleanup.sql | 4
schema/spacewalk/postgres/procs/queue_server.sql | 9
schema/spacewalk/postgres/procs/queue_server_overloaded.sql | 32
schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql | 39
schema/spacewalk/postgres/procs/set_ks_session_history_message.sql | 10
schema/spacewalk/postgres/procs/truncateCacheQueue.sql | 2
schema/spacewalk/postgres/triggers/rhnErrata.sql | 28
29 files changed, 2359 insertions(+), 775 deletions(-)
New commits:
commit b7388a10ea8ae0d7f966a37f8b9a27908c5ff453
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 17:44:24 2009 -0400
First pass of package code fixes based on manual testing.
Mostly getting rid of NO_DATA_FOUND exceptions in favor of
cheaper IF NOT FOUND tests, using LIMIT/OFFSET in place of
rownum, and qualifying function names where needed.
diff --git a/schema/spacewalk/postgres/packages/rhn_bel.pkb
b/schema/spacewalk/postgres/packages/rhn_bel.pkb
index a48f6c2..0c5d868 100644
--- a/schema/spacewalk/postgres/packages/rhn_bel.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_bel.pkb
@@ -25,23 +25,15 @@ update pg_settings set setting = 'rhn_bel,' || setting where
name = 'search_path
create or replace function is_org_paid (org_id_in in numeric) returns numeric as
$$
-declare
- paids cursor for
+ begin
+ if exists(
select 1
from rhnPaidOrgs
- where org_id = org_id_in;
-
- paids_curs_1 numeric;
- begin
-
- open paids;
- loop
- fetch paids into paids_curs_1;
- exit when not found;
+ where org_id = org_id_in ) then
return 1;
- end loop;
-
- return 0;
+ else
+ return 0;
+ end if;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/packages/rhn_cache.pkb
b/schema/spacewalk/postgres/packages/rhn_cache.pkb
index d8a15ef..8abc68b 100644
--- a/schema/spacewalk/postgres/packages/rhn_cache.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_cache.pkb
@@ -64,6 +64,8 @@ begin
end;
$$ language plpgsql;
+ -- this means a server got added or removed, so we
+ -- can't key off of a server anywhere.
create or replace function update_perms_for_server_group(
server_group_id_in in numeric
) returns void
@@ -72,7 +74,7 @@ $$
declare
users cursor for
-- org admins aren't affected, so don't test for them
- select usgp.user_id
+ select usgp.user_id as id
from rhnUserServerGroupPerms usgp
where usgp.server_group_id = server_group_id_in
and not exists (
@@ -87,21 +89,12 @@ declare
and ug.org_id = sg.org_id
and ugm.user_group_id = ug.id
);
-
- users_curs_uid numeric;
begin
- open users;
- loop
- fetch users into users_curs_uid;
- exit when not found;
- perform update_perms_for_user(users_curs_uid);
+ for u in users loop
+ perform update_perms_for_user(u.id);
end loop;
-
end;
$$ language plpgsql;
-
-
-- restore the original setting
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_cache')+1) ) where name = 'search_path';
-
diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pkb
b/schema/spacewalk/postgres/packages/rhn_channel.pkb
index c8b7331..fdb35e6 100644
--- a/schema/spacewalk/postgres/packages/rhn_channel.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_channel.pkb
@@ -46,13 +46,12 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
FROM rhnChannelFamilyLicense CFL, rhnChannelFamilyMembers CFM
WHERE CFM.channel_id = channel_id_in
AND CFM.channel_family_id = CFL.channel_family_id;
+
+ IF NOT FOUND THEN
+ RETURN NULL;
+ END IF;
RETURN license_val;
-
- EXCEPTION
- WHEN NO_DATA_FOUND
- THEN
- RETURN NULL;
END$$ language plpgsql;
@@ -87,7 +86,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where
name = 'search_
server_org_id_val NUMERIC;
available_subscriptions NUMERIC;
consenting_user NUMERIC;
- allowed numeric := 0;
+ allowed numeric;
current_members_val numeric;
BEGIN
if user_id_in is not null then
@@ -106,7 +105,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
IF channel_parent_val IS NOT NULL
THEN
-- child channel; if attempting to cross-subscribe a child to the wrong base,
silently ignore
- parent_subscribed := check_server_subscription(server_id_in,
channel_parent_val);
+ parent_subscribed := rhn_channel.check_server_subscription(server_id_in,
channel_parent_val);
IF NOT parent_subscribed
THEN
@@ -114,7 +113,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
END IF;
ELSE
-- base channel
- server_has_base_chan := server_base_subscriptions(server_id_in);
+ server_has_base_chan := rhn_channel.server_base_subscriptions(server_id_in);
IF server_has_base_chan
THEN
@@ -122,7 +121,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
END IF;
END IF;
- server_already_in_chan := check_server_subscription(server_id_in,
channel_id_in);
+ server_already_in_chan := rhn_channel.check_server_subscription(server_id_in,
channel_id_in);
IF server_already_in_chan
THEN
@@ -139,7 +138,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
-- Use the org_id of the server only if the org_id of the channel = NULL.
-- This is required for subscribing to shared channels.
--
- SELECT NVL(org_id, (SELECT org_id FROM rhnServer WHERE id = server_id_in))
+ SELECT COALESCE(org_id, (SELECT org_id FROM rhnServer WHERE id = server_id_in))
INTO server_org_id_val
FROM rhnChannel
WHERE id = channel_id_in;
@@ -148,28 +147,25 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
into current_members_val
from rhnPrivateChannelFamily
where org_id = server_org_id_val and channel_family_id = channel_family_id_val
- for update of current_members;
+ for update of rhnPrivateChannelFamily;
available_subscriptions :=
rhn_channel.available_family_subscriptions(channel_family_id_val, server_org_id_val);
IF available_subscriptions IS NULL OR
available_subscriptions > 0 or
- can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 1
+ rhn_channel.can_server_consume_virt_channl(server_id_in,
channel_family_id_val) = 1
THEN
IF rhn_channel.get_license_path(channel_id_in) IS NOT NULL
THEN
- BEGIN
-
SELECT user_id INTO consenting_user
FROM rhnChannelFamilyLicenseConsent
WHERE channel_family_id = channel_family_id_val
AND server_id = server_id_in;
-
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
+
+ IF NOT FOUND THEN
perform
rhn_exception.raise_exception('channel_subscribe_no_consent');
- END;
+ END IF;
END IF;
insert into rhnServerHistory (id,server_id,summary,details) (
@@ -196,9 +192,9 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
family_id_in in numeric )
returns numeric
as $$
- declare
- server_virt_families cursor for
- select vi.virtual_system_id, cfvsl.channel_family_id
+ begin
+ if exists(
+ select 1
from
rhnChannelFamilyVirtSubLevel cfvsl,
rhnSGTypeVirtSubLevel sgtvsl,
@@ -211,15 +207,12 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
select 1
from rhnServerEntitlementView sev
where vi.host_system_id = sev.server_id
- and sev.server_group_type_id = sgtvsl.server_group_type_id );
- begin
-
- for server_virt_family in server_virt_families loop
- return 1;
- end loop;
-
+ and sev.server_group_type_id = sgtvsl.server_group_type_id ))
+ then
+ return 1;
+ else
return 0;
-
+ end if;
end$$ language plpgsql;
@@ -251,7 +244,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
LOOP
IF rhn_server.can_change_base_channel(server.element) = 1
THEN
- perform rhn_channel.clear_subscriptions(TO_NUMERIC(server.element));
+ perform rhn_channel.clear_subscriptions(server.element);
perform rhn_channel.subscribe_server(server.element, channel_id_in, 0,
set_uid_in);
END IF;
END LOOP;
@@ -299,7 +292,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
s.id
);
end loop;
- perform bulk_server_base_change(new_channel_id_in,
+ perform rhn_channel.bulk_server_base_change(new_channel_id_in,
set_label_in || 'basechange',
set_uid_in);
delete from rhnSet
@@ -326,9 +319,9 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
begin
if rhn_server.can_change_base_channel(server.element) = 1
then
- channel_id := guess_server_base(TO_NUMERIC(server.element));
- perform rhn_channel.clear_subscriptions(TO_NUMERIC(server.element));
- perform rhn_channel.subscribe_server(TO_NUMERIC(server.element),
channel_id, 0, set_uid_in);
+ channel_id := rhn_channel.guess_server_base(server.element);
+ perform rhn_channel.clear_subscriptions(server.element);
+ perform rhn_channel.subscribe_server(server.element, channel_id, 0,
set_uid_in);
end if;
exception when others then
null;
@@ -383,12 +376,11 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
as $$
declare
suffix VARCHAR(128) := '-redhat-linux';
- suffix_len NUMERIC := length(suffix);
begin
if server_arch_in is NULL then
return NULL;
end if;
- if instr(server_arch_in, '-') > 0
+ if position('-' IN server_arch_in) > 0
then
-- Suffix already present
return server_arch_in;
@@ -407,20 +399,19 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
user_id_in in numeric default null
) returns numeric as $$
declare
- server_arch varchar(256) := normalize_server_arch(server_arch_in);
+ server_arch varchar(256) := rhn_channel.normalize_server_arch(server_arch_in);
server_arch_id numeric;
begin
-- Look up the server arch
- begin
select id
into server_arch_id
from rhnServerArch
where label = server_arch;
- exception
- when no_data_found then
+ if not found then
perform rhn_exception.raise_exception('server_arch_not_found');
- end;
- return base_channel_rel_archid(release_in, server_arch_id,
+ end if;
+
+ return rhn_channel.base_channel_rel_archid(release_in, server_arch_id,
org_id_in, user_id_in);
end$$ language plpgsql;
@@ -459,18 +450,18 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
begin
if org_id_in = -1 and user_id_in is not null then
-- Get the org id from the user id
- begin
+
select org_id
into valid_org_id
from web_contact
where id = user_id_in;
- exception
- when no_data_found then
+
+ if not found then
-- User doesn't exist
-- XXX Only list public stuff for now
valid_user_id := null;
valid_org_id := -1;
- end;
+ end if;
end if;
for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id)
@@ -483,9 +474,8 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
end if;
-- Check user to channel permissions
- select loose_user_role_check(c.id, user_id_in, 'subscribe')
- into channel_subscribable
- from dual;
+ select rhn_channel.loose_user_role_check(c.id, user_id_in,
'subscribe')
+ into channel_subscribable;
if channel_subscribable = 1 then
return c.id;
@@ -532,7 +522,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
end if;
end loop;
end loop;
- perform bulk_guess_server_base(set_label_in||'baseguess',set_uid_in);
+ perform
rhn_channel.bulk_guess_server_base(set_label_in||'baseguess',set_uid_in);
delete from rhnSet where label = set_label_in||'baseguess' and user_id =
set_uid_in;
end$$ language plpgsql;
@@ -551,7 +541,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
BEGIN
for channel in server_channels(server_id_in)
loop
- perform unsubscribe_server(server_id_in, channel.channel_id, 1, 1,
deleting_server);
+ perform rhn_channel.unsubscribe_server(server_id_in, channel.channel_id,
1, 1, deleting_server);
perform rhn_channel.update_family_counts(channel.channel_family_id,
channel.org_id);
end loop;
END$$ language plpgsql;
@@ -588,17 +578,17 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in)
LOOP
if unsubscribe_children_in = 1 then
- perform unsubscribe_server(server_id_in => server_id_in,
- channel_id_in => child.id,
- immediate_in => immediate_in,
- unsubscribe_children_in =>
unsubscribe_children_in,
- deleting_server => deleting_server);
+ perform rhn_channel.unsubscribe_server(server_id_in,
+ child.id,
+ immediate_in,
+ unsubscribe_children_in,
+ deleting_server);
else
perform
rhn_exception.raise_exception('channel_unsubscribe_child_exists');
end if;
END LOOP;
- server_already_in_chan := check_server_subscription(server_id_in,
channel_id_in);
+ server_already_in_chan := rhn_channel.check_server_subscription(server_id_in,
channel_id_in);
IF NOT server_already_in_chan
THEN
@@ -675,12 +665,12 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
SELECT channel_family_id INTO channel_family_id_val
FROM rhnChannelFamilyMembers
WHERE channel_id = channel_id_in;
+
+ IF NOT FOUND THEN
+ RETURN NULL;
+ END IF;
RETURN channel_family_id_val;
- EXCEPTION
- WHEN NO_DATA_FOUND
- THEN
- RETURN NULL;
END$$ language plpgsql;
CREATE OR REPLACE FUNCTION available_family_subscriptions(channel_family_id_in IN
NUMERIC, org_id_in IN NUMERIC)
@@ -757,9 +747,8 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
AS $$
BEGIN
update rhnPrivateChannelFamily
- set current_members = (
- channel_family_current_members(channel_family_id_in, org_id_in)
- )
+ set current_members =
+ rhn_channel.channel_family_current_members(channel_family_id_in,
org_id_in)
where org_id = org_id_in
and channel_family_id = channel_family_id_in;
END$$ language plpgsql;
@@ -771,7 +760,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
declare
channel_family_id_val NUMERIC;
BEGIN
- SELECT channel_family_id INTO channel_family_id_val
+ SELECT channel_family_id INTO STRICT channel_family_id_val
FROM rhnChannelFamilyMembers
WHERE channel_id = channel_id_in;
@@ -798,7 +787,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
and pcf.channel_family_id = channel_family_id_in;
begin
for perm in permissions loop
- perform set_family_maxmembers(
+ perform rhn_channel.set_family_maxmembers(
customer_id_in,
channel_family_id_in,
quantity_in
@@ -830,17 +819,13 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
as $$
declare
servers cursor for
- select server_id from (
- select rownum row_number, server_id, modified from (
select rcfsp.server_id,
rcfsp.modified
from rhnChannelFamilyServerPhysical rcfsp
where rcfsp.customer_id = customer_id_in
and rcfsp.channel_family_id = channel_family_id_in
order by modified
- ) ss
- where rownum > quantity_in
- ) ss2;
+ offset quantity_in;
begin
-- prune subscribed servers
for server in servers loop
@@ -848,10 +833,10 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
channel_family_id_in);
end loop;
- update rhnPrivateChannelFamily pcf
- set pcf.max_members = quantity_in
- where pcf.org_id = customer_id_in
- and pcf.channel_family_id = channel_family_id_in;
+ update rhnPrivateChannelFamily
+ set max_members = quantity_in
+ where org_id = customer_id_in
+ and channel_family_id = channel_family_id_in;
end$$ language plpgsql;
create or replace function unsubscribe_server_from_family(server_id_in in numeric,
@@ -874,7 +859,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
declare
org_id_out numeric;
begin
- select org_id into org_id_out
+ select org_id into strict org_id_out
from rhnChannel
where id = channel_id_in;
@@ -884,41 +869,37 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
create or replace function get_cfam_org_access(cfam_id_in in numeric, org_id_in in
numeric)
returns numeric
as $$
- declare
- families cursor for
+ begin
+ if exists(
select 1
from rhnOrgChannelFamilyPermissions cfp
- where cfp.org_id = org_id_in;
- begin
- -- the idea: if we get past this query,
- -- the user has the role, else catch the exception and return 0
- for family in families loop
+ where cfp.org_id = org_id_in
+ ) then
return 1;
- end loop;
+ else
return 0;
+ end if;
end$$ language plpgsql;
create or replace function get_org_access(channel_id_in in numeric, org_id_in in
numeric)
- returns numeric
+ returns integer
as $$
- declare
- throwaway numeric;
begin
-- the idea: if we get past this query,
- -- the org has access to the channel, else catch the exception and return 0
- select distinct 1 into throwaway
+ -- the org has access to the channel, else not
+ if exists(
+ select 1
from rhnChannelFamilyMembers CFM,
rhnOrgChannelFamilyPermissions CFP
where cfp.org_id = org_id_in
and CFM.channel_family_id = CFP.channel_family_id
and CFM.channel_id = channel_id_in
- and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1);
-
- return 1;
- exception
- when no_data_found
- then
- return 0;
+ and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1) )
+ then
+ return 1;
+ else
+ return 0;
+ end if;
end$$ language plpgsql;
-- check if a user has a given role, or if such a role is inferrable
@@ -941,7 +922,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
end if;
if role_in = 'manage' and
- NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then
+ COALESCE(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then
reason_out := 'channel_not_owned';
status := 0;
return;
@@ -1007,10 +988,12 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
select 1 into n
from rhnSharedChannelView s
where s.id = channel_id and s.org_trust_id = oid;
+
+ if not found then
+ return 0;
+ end if;
+
return 1;
- exception
- when no_data_found then
- return 0;
end$$ language plpgsql;
-- same as above, but returns 1 if user_id_in is null
@@ -1022,7 +1005,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
if user_id_in is null then
return 1;
end if;
- return user_role_check(channel_id_in, user_id_in, role_in);
+ return rhn_channel.user_role_check(channel_id_in, user_id_in, role_in);
end$$ language plpgsql;
-- directly checks the table, no inferred permissions
@@ -1032,7 +1015,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
declare
throwaway numeric;
begin
- -- the idea: if we get past this query, the user has the role, else catch the
exception and return 0
+ -- the idea: if we get past this query, the user has the role, else no
select 1 into throwaway
from rhnChannelPermissionRole CPR,
rhnChannelPermission CP
@@ -1040,12 +1023,12 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
and CP.channel_id = channel_id_in
and CPR.label = role_in
and CP.role_id = CPR.id;
+
+ if not found then
+ return 0;
+ end if;
- return 1;
- exception
- when no_data_found
- then
- return 0;
+ return 1;
end$$ language plpgsql;
-- check if an org has a certain setting
@@ -1055,7 +1038,7 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
declare
throwaway numeric;
begin
- -- the idea: if we get past this query, the org has the setting, else catch the
exception and return 0
+ -- the idea: if we get past this query, the org has the setting
select 1 into throwaway
from rhnOrgChannelSettingsType OCST,
rhnOrgChannelSettings OCS
@@ -1063,12 +1046,12 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
and OCS.channel_id = channel_id_in
and OCST.label = setting_in
and OCS.setting_id = OCST.id;
-
- return 1;
- exception
- when no_data_found
- then
- return 0;
+
+ if not found then
+ return 0;
+ end if;
+
+ return 1;
end$$ language plpgsql;
CREATE OR REPLACE FUNCTION channel_priority(channel_id_in IN numeric)
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pkb
b/schema/spacewalk/postgres/packages/rhn_config.pkb
index 6621d8d..8d5d78a 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_config.pkb
@@ -20,16 +20,15 @@
update pg_settings set setting = 'rhn_config,' || setting where name =
'search_path';
-Create or replace function prune_org_configs (
+ -- just a stub for now
+create or replace function prune_org_configs (
org_id_in in numeric,
total_in in numeric
)
-returns numeric
+returns void
as $$
begin
- null;
-return 0;
- end;
+end;
$$ LANGUAGE 'plpgsql';
@@ -37,10 +36,10 @@ $$ LANGUAGE 'plpgsql';
revision_in in numeric,
config_file_id_in in numeric,
config_content_id_in in numeric,
- config_info_id_in in numeric
--- delim_start_in in varchar := '{@',
--- delim_end_in in varchar := '@}',
--- config_file_type_id_in in numeric := 1
+ config_info_id_in in numeric,
+ delim_start_in in varchar default '{@',
+ delim_end_in in varchar default '@}',
+ config_file_type_id_in in numeric default 1
) returns numeric as $$
declare
retval numeric;
@@ -49,11 +48,6 @@ $$ LANGUAGE 'plpgsql';
rhnConfigFile cf
where cf.id = config_file_id_in
and cf.config_channel_id = cc.id;
- delim_start_in varchar(10) := '{@';
- delim_end_in varchar(10) := '@}';
- config_file_type_id_in numeric := 1;
- org record;
-
begin
insert into rhnConfigRevision(id, revision, config_file_id,
@@ -66,28 +60,18 @@ $$ LANGUAGE 'plpgsql';
)
returning id into retval;
- for org in select cc.org_id as id
- from rhnConfigChannel cc, rhnConfigFile cf
- where cf.id = config_file_id_in
- and cf.config_channel_id = cc.id
- loop
+ for org in affected_orgs loop
perform rhn_quota.update_org_quota(org.id);
end loop;
return retval;
- end ;
- $$ language 'plpgsql';
-
---------------------------------------------------------------------
+ end$$ language 'plpgsql';
create or replace function delete_revision (
config_revision_id_in in numeric,
- org_id_in in numeric
+ org_id_in in numeric default -1
) returns void as $$
-declare
-org_id_in numeric = 1;
-other_revision record;
-snapshot record;
+ declare
cfid numeric;
ccid numeric;
oid numeric;
@@ -105,13 +89,7 @@ snapshot record;
from rhnConfigRevision
where config_content_id = config_content_id_in;
begin
- for snapshot in select scr.snapshot_id as id
- from rhnSnapshot s,
- rhnSnapshotConfigRevision scr
- where scr.config_revision_id = config_revision_id_in
- and scr.snapshot_id = s.id
- and s.invalid is null
- loop
+ for snapshot in snapshots loop
update rhnSnapshot s
set s.invalid =
lookup_snapshot_invalid_reason('cr_removed')
where s.id = snapshot.id;
@@ -141,11 +119,7 @@ snapshot record;
-- now prune away content if there aren't any other revisions pointing
-- at it
--- for other_revision in other_revisions(ccid) loop
-open other_revisions(ccid);
-fetch other_revisions into other_revision;
-exit when not found;
-loop
+ for other_revision in other_revisions(ccid) loop
others := 1;
exit;
end loop;
@@ -181,7 +155,6 @@ loop
end ;
$$ LANGUAGE 'plpgsql';
------------------------------------------------------------
create or replace function get_latest_revision (
config_file_id_in in numeric
@@ -193,16 +166,15 @@ $$ LANGUAGE 'plpgsql';
for revision1 in
select cr.id
from rhnConfigRevision cr
- where cr.config_file_id = config_file_id_in order by revision desc
+ where cr.config_file_id = config_file_id_in
+ order by revision desc
loop
- return revision1.id;
- end loop;
- return null;
+ return revision1.id;
+ end loop;
+ return null;
end;
$$ LANGUAGE 'plpgsql';
--------------------------------------------------------------------
-
create or replace function insert_file (
config_channel_id_in in numeric,
name_in in varchar
@@ -227,7 +199,7 @@ $$ LANGUAGE 'plpgsql';
return retval;
end;
$$ LANGUAGE 'plpgsql';
---------------------------------------------------
+
create or replace function delete_file (
config_file_id_in in numeric
) returns void as $$
@@ -246,14 +218,14 @@ revision record;
and cr.config_file_id = cf.id
loop
- perform delete_revision(revision.id, revision.org_id);
+ perform rh_config.delete_revision(revision.id, revision.org_id);
org_id := revision.org_id;
end loop;
perform rhn_quota.update_org_quota(org_id);
delete from rhnConfigFile where id = config_file_id_in;
end;
$$ LANGUAGE 'plpgsql';
-------------------------------------------------
+
create or replace function insert_channel (
org_id_in in numeric,
type_in in varchar,
@@ -282,7 +254,7 @@ declare
return retval;
end;
$$ LANGUAGE 'plpgsql';
---------------------------------------------
+
create or replace function delete_channel (
config_channel_id_in in numeric
) returns void as $$
@@ -294,7 +266,7 @@ declare
from rhnConfigFile
where config_channel_id = config_channel_id_in
loop
- perform delete_file(config_file.id);
+ perform rhn_config.delete_file(config_file.id);
end loop;
delete from rhnConfigChannel where id = config_channel_id_in;
@@ -302,39 +274,3 @@ end;
$$ LANGUAGE 'plpgsql';
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_config')+1) ) where name = 'search_path';
-
---
---
--- Revision 1.9 2005/02/16 14:03:35 jslagle
--- bz #148844
--- Changed insert_revision function to take a config_file_type_id instead of label
---
--- Revision 1.8 2005/02/15 02:42:59 jslagle
--- bz #147860
--- insert_revision function now takes a rhnConfigFileType label as a parameter instead of
an id
---
--- Revision 1.7 2005/02/14 22:45:23 jslagle
--- bz#147860
--- Update rhn_config package body and specification for additional column to
rhnConfigRevision
---
--- Revision 1.6 2004/10/11 14:02:53 pjones
--- bugzilla: 133169 -- somehow, we just never update the quota in this case.
--- Amazingly, I thought this worked _and_ QA passed it...
---
--- Revision 1.5 2004/01/09 17:39:45 pjones
--- bugzilla: 113029 -- need to do functions for deleting rhnConfigChannel,
--- too, or we can't prune rhnConfigFile when we do.
---
--- Revision 1.4 2004/01/08 19:46:31 pjones
--- bugzilla: 113029 -- insert/delete for rhnConfigFile and rhnConfigRevision
---
--- Revision 1.3 2004/01/08 00:30:10 pjones
--- bugzilla: 113029 -- more deletion of config files and revisions
---
--- Revision 1.2 2004/01/08 00:03:37 pjones
--- bugzilla: 113029 -- rhn_config.delete_revision() and delete trigger on
--- rhnConfigFile
---
--- Revision 1.1 2003/12/19 22:07:30 pjones
--- bugzilla: 112392 -- quota support for config files
---
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pks
b/schema/spacewalk/postgres/packages/rhn_config.pks
index ff1986d..647bdbb 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pks
+++ b/schema/spacewalk/postgres/packages/rhn_config.pks
@@ -19,12 +19,27 @@ create schema rhn_config;
-- setup search_path so that these functions are created in appropriate schema.
update pg_settings set setting = 'rhn_config,' || setting where name =
'search_path';
-Create or replace function prune_org_configs
+create or replace function prune_org_configs
(
org_id_in in numeric,
total_in in numeric
)
-returns numeric
+returns void
+as $$
+begin
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+end;
+$$ LANGUAGE 'plpgsql';
+
+create or replace function insert_revision (
+ revision_in in numeric,
+ config_file_id_in in numeric,
+ config_content_id_in in numeric,
+ config_info_id_in in numeric,
+ delim_start_in in varchar default '{@',
+ delim_end_in in varchar default '@}',
+ config_file_type_id_in numeric default 1
+ ) returns numeric
as $$
begin
RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
@@ -34,7 +49,7 @@ $$ LANGUAGE 'plpgsql';
create or replace function delete_revision
(
config_revision_id_in in numeric,
- org_id_in in numeric
+ org_id_in in numeric default -1
)
returns void
as $$
diff --git a/schema/spacewalk/postgres/packages/rhn_config_channel.pkb
b/schema/spacewalk/postgres/packages/rhn_config_channel.pkb
index c7be25c..180a9f2 100644
--- a/schema/spacewalk/postgres/packages/rhn_config_channel.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_config_channel.pkb
@@ -1,9 +1,9 @@
-- setup search_path so that these functions are created in appropriate schema.
-update pg_settings set setting = 'rhn_channel_config,' || setting where name =
'search_path';
+update pg_settings set setting = 'rhn_config_channel,' || setting where name =
'search_path';
CREATE OR REPLACE FUNCTION action_diff_revision_status(action_config_revision_id_in
numeric)
-RETURNS VARCHAR(255)
+RETURNS VARCHAR
-- result_is_null obviously wants NVL2, but stupid 8.1.7.3.0 doesn't
-- have that. Or case. So we're using union, instead.
AS $$
@@ -41,7 +41,7 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
-Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC,
user_id_in IN NUMERIC)
+CREATE OR REPLACE FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC,
user_id_in IN NUMERIC)
RETURNS NUMERIC as $$
Declare
server_id NUMERIC;
@@ -50,19 +50,16 @@ Create or replace FUNCTION get_user_chan_access(config_channel_id_in
IN NUMERIC,
any_visible_servers_subscribed NUMERIC;
BEGIN
- org_matches := 0;
- BEGIN
SELECT 1 INTO org_matches
FROM rhnConfigChannel CC,
web_contact WC
WHERE WC.id = user_id_in
AND CC.id = config_channel_id_in
AND WC.org_id = CC.org_id;
- --EXCEPTION
- if noT found then
+
+ IF NOT FOUND THEN
RETURN 0;
- end if;
- END;
+ END IF;
global_channel := 'unknown';
SELECT CCT.label INTO global_channel
@@ -70,13 +67,12 @@ Create or replace FUNCTION get_user_chan_access(config_channel_id_in
IN NUMERIC,
rhnConfigChannelType CCT
WHERE CC.id = config_channel_id_in
AND CCT.id = CC.confchan_type_id;
+
IF (rhn_user.check_role_implied(user_id_in, 'config_admin') = 1) AND
(global_channel = 'normal')
THEN
RETURN 1;
END IF;
- any_visible_servers_subscribed := 0;
- BEGIN
SELECT 1 INTO any_visible_servers_subscribed
FROM DUAL
WHERE EXISTS (
@@ -87,11 +83,10 @@ Create or replace FUNCTION get_user_chan_access(config_channel_id_in
IN NUMERIC,
AND USPD.server_id = SCC.server_id
AND SCC.config_channel_id = config_channel_id_in
);
- --EXCEPTION
- if not found
+
+ IF NOT FOUND
THEN RETURN 0;
- end if;
- END;
+ END IF;
RETURN any_visible_servers_subscribed;
@@ -103,36 +98,35 @@ CREATE OR REPLACE FUNCTION
get_user_revision_access(config_revision_id_in IN NUM
DECLARE
config_channel_id NUMERIC;
BEGIN
- BEGIN
+
SELECT CF.config_channel_id INTO config_channel_id
FROM rhnConfigFile CF,
rhnConfigRevision CR
WHERE CF.id = CR.config_file_id
AND CR.id = config_revision_id_in;
+
if not found then
RETURN 0;
end if;
- END;
RETURN rhn_config_channel.get_user_chan_access(config_channel_id, user_id_in);
END;
$$ language 'plpgsql';
- Create or replace FUNCTION get_user_file_access(config_file_id_in IN NUMERIC,
user_id_in IN NUMERIC)
+ CREATE OR REPLACE FUNCTION get_user_file_access(config_file_id_in IN NUMERIC,
user_id_in IN NUMERIC)
RETURNS NUMERIC as $$
declare
config_channel_id NUMERIC;
BEGIN
- BEGIN
SELECT CF.config_channel_id INTO config_channel_id
FROM rhnConfigFile CF
WHERE CF.id = config_file_id_in;
- if not found then
- RETURN 0;
- end if;
- END;
+
+ IF NOT FOUND THEN
+ RETURN 0;
+ END IF;
RETURN rhn_config_channel.get_user_chan_access(config_channel_id, user_id_in);
END ;
diff --git a/schema/spacewalk/postgres/packages/rhn_config_channel.pks
b/schema/spacewalk/postgres/packages/rhn_config_channel.pks
index 4113e19..1cd2c2a 100644
--- a/schema/spacewalk/postgres/packages/rhn_config_channel.pks
+++ b/schema/spacewalk/postgres/packages/rhn_config_channel.pks
@@ -1,22 +1,12 @@
-create schema rhn_channel_config;
+create schema rhn_config_channel;
-- setup search_path so that these functions are created in appropriate schema.
-update pg_settings set setting = 'rhn_channel_config,' || setting where name =
'search_path';
+update pg_settings set setting = 'rhn_config_channel,' || setting where name =
'search_path';
-CREATE OR REPLACE FUNCTION action_diff_revision_status(action_config_revision_id_in
numeric)
-RETURNS VARCHAR(255)
-AS $$
-BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC,
user_id_in IN NUMERIC)
+CREATE OR REPLACE FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC,
user_id_in IN NUMERIC)
RETURNS NUMERIC as $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';
@@ -24,11 +14,26 @@ CREATE OR REPLACE FUNCTION
get_user_revision_access(config_revision_id_in IN NUM
RETURNS NUMERIC
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language 'plpgsql';
+
+CREATE OR REPLACE FUNCTION get_user_file_access(config_file_id_in IN NUMERIC, user_id_in
IN NUMERIC)
+RETURNS NUMERIC
+AS $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';
+CREATE OR REPLACE FUNCTION action_diff_revision_status(action_config_revision_id_in
numeric)
+RETURNS VARCHAR
+AS $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ LANGUAGE plpgsql;
+
-- restore the original setting
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_config_channel')+1) ) where name = 'search_path';
diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
index c6fdcf0..c6fe71b 100644
--- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
@@ -187,9 +187,15 @@ as $$
where label = type_label_in;
if array_upper(previous_ent, 1) = 0 then
- if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg
(server_id_in, type_label_in, sgid)) then
- -- rhn_server.insert_into_servergroup (server_id_in, sgid);
- return 1;
+ 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;
@@ -224,7 +230,8 @@ as $$
-- 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
- if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)
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
@@ -252,22 +259,24 @@ as $$
begin
- begin
- select is_base into type_label_in_is_base
- from rhnServerGroupType
- where label = type_label_in;
- exception
- when no_data_found then
- perform rhn_exception.raise_exception ( 'invalid_entitlement' );
- end;
+ 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' );
- elsif rhn_entitlements.find_compatible_sg ( server_id_in,
- type_label_in, sgid ) then
- return 1;
else
- return 0;
+ 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$$
@@ -321,15 +330,14 @@ as $$
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 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
@@ -337,12 +345,11 @@ as $$
is_virt := 1;
end if;
-
-
if rhn_entitlements.can_entitle_server(server_id_in,
type_label_in) = 1 then
- if rhn_entitlements.find_compatible_sg (server_id_in,
- type_label_in, sgid) 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 ',
@@ -381,22 +388,17 @@ as $$
type_is_base char;
is_virt numeric := 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
+ 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;
+ end if;
select sg.id, sgt.is_base
- into group_id, type_is_base
+ into group_id, type_is_base
from rhnServerGroupType sgt,
rhnServerGroup sg,
rhnServerGroupMembers sgm,
@@ -408,9 +410,13 @@ as $$
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 unentitle_server ( server_id_in );
+ perform rhn_entitlements.unentitle_server ( server_id_in );
else
insert into rhnServerHistory ( id, server_id, summary, details )
@@ -436,8 +442,8 @@ as $$
WHERE host_id = server_id_in);
DELETE
FROM time_series
- WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1,
- (INSTR(o_id, '-', INSTR(o_id, '-') + 1) -
INSTR(o_id, '-')) - 1)
+ 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
FROM rhn_check_probe
WHERE host_id = server_id_in);
@@ -453,11 +459,6 @@ as $$
end if;
end if;
- exception
- when no_data_found then
- perform
rhn_exception.raise_exception('invalid_server_group_member');
- end;
-
end$$
language plpgsql;
@@ -482,15 +483,14 @@ as $$
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
+ 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;
+ end if;
for servergroup in servergroups loop
@@ -1038,7 +1038,6 @@ as $$
group_type numeric;
begin
- begin
select max_members
into prev_ent_count
from rhnServerGroupType sgt,
@@ -1046,13 +1045,12 @@ as $$
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
+
+ if not found then
perform rhn_exception.raise_exception(
'not_enough_entitlements_in_base_org');
- end;
+ end if;
- begin
select max_members
into to_org_prev_ent_count
from rhnServerGroupType sgt,
@@ -1060,21 +1058,20 @@ as $$
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
+
+ if not found then
to_org_prev_ent_count := 0;
- end;
+ end if;
- begin
select id
into group_type
from rhnServerGroupType
where label = group_label_in;
- exception
- when NO_DATA_FOUND then
+
+ if not found then
perform rhn_exception.raise_exception(
'invalid_server_group');
- end;
+ end if;
new_ent_count := prev_ent_count - quantity_in;
@@ -1101,25 +1098,25 @@ as $$
-- Create or delete the entries in rhnOrgEntitlementType
if group_label_in = 'enterprise_entitled' then
if new_quantity > 0 then
- perform set_customer_enterprise(to_org_id_in);
+ perform rhn_entitlements.set_customer_enterprise(to_org_id_in);
else
- perform unset_customer_enterprise(to_org_id_in);
+ 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 set_customer_provisioning(to_org_id_in);
+ perform rhn_entitlements.set_customer_provisioning(to_org_id_in);
else
- perform unset_customer_provisioning(to_org_id_in);
+ 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 set_customer_monitoring(to_org_id_in);
+ perform rhn_entitlements.set_customer_monitoring(to_org_id_in);
else
- perform unset_customer_monitoring(to_org_id_in);
+ perform rhn_entitlements.unset_customer_monitoring(to_org_id_in);
end if;
end if;
@@ -1150,7 +1147,6 @@ as $$
cfam_id numeric;
begin
- begin
select max_members
into prev_ent_count
from rhnChannelFamily cf,
@@ -1158,13 +1154,12 @@ as $$
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
+
+ if not found then
perform rhn_exception.raise_exception(
'not_enough_entitlements_in_base_org');
- end;
+ end if;
- begin
select max_members
into to_org_prev_ent_count
from rhnChannelFamily cf,
@@ -1172,22 +1167,20 @@ as $$
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
+
+ if not found then
to_org_prev_ent_count := 0;
- end;
-
+ end if;
- begin
select id
into cfam_id
from rhnChannelFamily
where label = channel_family_label_in;
- exception
- when NO_DATA_FOUND then
+
+ if not found then
perform rhn_exception.raise_exception(
'invalid_channel_family');
- end;
+ end if;
new_ent_count := prev_ent_count - quantity_in;
@@ -1238,7 +1231,7 @@ as $$
-- Fetch the current entitlement count for the org
-- into prev_ent_count
- begin
+
select current_members
into prev_ent_count
from rhnServerGroupType sgt,
@@ -1246,21 +1239,20 @@ as $$
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
+
+ if not found then
prev_ent_count := 0;
- end;
+ end if;
- begin
select id
into group_type
from rhnServerGroupType
where label = group_label_in;
- exception
- when NO_DATA_FOUND then
+
+ if not found then
perform rhn_exception.raise_exception(
'invalid_server_group');
- end;
+ 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,
@@ -1306,7 +1298,6 @@ as $$
-- Fetch the current entitlement count for the org
-- into prev_ent_count
- begin
select current_members
into prev_ent_count
from rhnChannelFamily cf,
@@ -1314,21 +1305,20 @@ as $$
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
+
+ if not found then
prev_ent_count := 0;
- end;
+ end if;
- begin
select id
into cfam_id
from rhnChannelFamily
where label = channel_family_label_in;
- exception
- when NO_DATA_FOUND then
+
+ if not found then
perform rhn_exception.raise_exception(
'invalid_channel_family');
- end;
+ 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,
@@ -1368,45 +1358,47 @@ as $$
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;
- perform rhn_entitlements.prune_group(
- group_id,
- type_in,
- quantity
- );
- exception
- when no_data_found then
- if type_in = 'U' then
+ if not found 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,
- sysdate, sysdate
+ current_timestamp, current_timestamp
from rhnUserGroupType
where id = group_type_in
);
- elsif type_in = 'S' then
+ end if;
+
+ 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;
+
+ if not found 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,
- sysdate, sysdate
+ current_timestamp, current_timestamp
from rhnServerGroupType
where id = group_type_in
);
end if;
+ end if;
+
+ perform rhn_entitlements.prune_group(
+ group_id,
+ type_in,
+ quantity
+ );
end$$
language plpgsql;
diff --git a/schema/spacewalk/postgres/packages/rhn_exception.pks
b/schema/spacewalk/postgres/packages/rhn_exception.pks
index ec991c2..f9ccc69 100644
--- a/schema/spacewalk/postgres/packages/rhn_exception.pks
+++ b/schema/spacewalk/postgres/packages/rhn_exception.pks
@@ -3,8 +3,7 @@ create schema rhn_exception;
-- setup search_path so that these functions are created in appropriate schema.
update pg_settings set setting = 'rhn_exception,' || setting where name =
'search_path';
-CREATE OR REPLACE FUNCTION is_org_paid (org_id_in IN NUMERIC)
-RETURNS NUMERIC
+CREATE OR REPLACE FUNCTION lookup_exception(exception_label_in IN VARCHAR,
exception_id_out OUT NUMERIC, exception_message_out OUT VARCHAR)
AS $$
BEGIN
RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
diff --git a/schema/spacewalk/postgres/packages/rhn_org.pkb
b/schema/spacewalk/postgres/packages/rhn_org.pkb
index 1f532d9..ce774a0 100644
--- a/schema/spacewalk/postgres/packages/rhn_org.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_org.pkb
@@ -31,13 +31,8 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC,
group_la
AND SGT.label = group_label_in
AND SG.org_id = org_id_in;
-
- server_group record; --server_group_by_label%ROWTYPE;
-
-
+ server_group record;
BEGIN
-
-
OPEN server_group_by_label(org_id_in, group_label_in);
FETCH server_group_by_label INTO server_group;
CLOSE server_group_by_label;
@@ -47,9 +42,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC,
group_la
$$
LANGUAGE PLPGSQL;
-
-
- create or replace function delete_org (
+create or replace function delete_org (
org_id_in in numeric
) returns void
as
@@ -60,36 +53,26 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
from web_contact
where org_id = org_id_in;
- user_curs_id numeric;
-
servers cursor (org_id_in numeric) for
select id
from rhnServer
where org_id = org_id_in;
- servers_curs_id numeric;
-
config_channels cursor for
select id
from rhnConfigChannel
where org_id = org_id_in;
- conf_channel_curs_id numeric;
-
custom_channels cursor for
select id
from rhnChannel
where org_id = org_id_in;
- cust_channel_curs_id numeric;
-
errata cursor for
select id
from rhnErrata
where org_id = org_id_in;
- errata_curs_id numeric;
-
begin
if org_id_in = 1 then
@@ -97,57 +80,33 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
end if;
-- Delete all users.
- open users;
- loop
- fetch users into user_curs_id;
- exit when not found;
- perform rhn_org.delete_user(user_curs_id, 1);
+ for u in users loop
+ perform rhn_org.delete_user(u.id, 1);
end loop;
- close users;
-
-- Delete all servers.
- open servers(org_id_in);
- loop
- fetch servers into servers_curs_id;
- exit when not found;
- perform delete_server(servers_curs_id);
+ for s in servers(org_id_in) loop
+ perform delete_server(s.id);
end loop;
- close servers;
-
+
-- Delete all config channels.
- open config_channels;
- loop
- fetch config_channels into conf_channel_curs_id;
- exit when not found;
- perform rhn_config.delete_channel(conf_channel_curs_id);
- end loop;
- close config_channels;
-
+ for c in config_channels loop
+ perform rhn_config.delete_channel(c.id);
+ end loop;
-- Delete all custom channels.
- open custom_channels;
- loop
- fetch custom_channels into cust_channel_curs_id;
- exit when not found;
- delete from rhnServerChannel where channel_id = cust_channel_curs_id;
+ for cc in custom_channels loop
+ delete from rhnServerChannel where channel_id = cc.id;
delete from rhnServerProfilePackage where server_profile_id in (
- select id from rhnServerProfile where base_channel = cust_channel_curs_id
+ select id from rhnServerProfile where base_channel = cc.id
);
- delete from rhnServerProfile where base_channel = cust_channel_curs_id;
-
+ delete from rhnServerProfile where base_channel = cc.id;
+ end loop;
+
+ -- Delete all errata packages
+ for e in errata loop
+ delete from rhnErrataPackage where errata_id = e.id;
end loop;
- close custom_channels;
-
- -- Delete all errata packages
- open errata;
- loop
- fetch errata into errata_curs_id;
- exit when not found;
- delete from rhnErrataPackage where errata_id = errata_curs_id;
- end loop;
- close errata;
-
-- Give the org's entitlements back to the main org.
perform rhn_entitlements.remove_org_entitlements(org_id_in);
@@ -175,9 +134,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
$$
language plpgsql;
- -- ////////////////////////////////////////////////////////
-
- create or replace function delete_user(user_id_in in numeric,
deleting_org in numeric) returns void
+create or replace function delete_user(user_id_in in numeric, deleting_org in numeric
default 0) returns void
as
$$
declare
@@ -191,11 +148,8 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
and ug.group_type = ugt.id
and ugt.label = 'org_admin';
- iadmin_curs_counter numeric;
-
-
servergroups_needing_admins cursor for
- select usgp.server_group_id
+ select usgp.server_group_id
from rhnUserServerGroupPerms usgp
where 1=1
and usgp.user_id = user_id_in
@@ -206,23 +160,17 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
and sq_usgp.server_group_id =
usgp.server_group_id
and sq_usgp.user_id != user_id_in
);
-
- sg_curs_id numeric;
messages cursor for
select message_id
from rhnUserMessage
where user_id = user_id_in;
-
- msg_curs_id numeric;
users numeric;
our_org_id numeric;
other_users numeric;
other_org_admin numeric;
-
-
- other_user_id numeric;
+ other_user_id numeric;
begin
select wc.org_id
into our_org_id
@@ -230,87 +178,63 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
where id = user_id_in;
-- find any other users
- begin
- select id, 1
- into other_user_id, other_users
- from web_contact
- where 1=1
- and org_id = our_org_id
- and id != user_id_in
- and rownum = 1;
- exception
- when division_by_zero then
- other_users := 0;
- end;
+ select id, 1
+ into other_user_id, other_users
+ from web_contact
+ where 1=1
+ and org_id = our_org_id
+ and id != user_id_in
+ limit 1;
+
+ if not found then
+ other_users := 0;
+ end if;
-- now do org admin stuff
if other_users != 0 then
- open is_admin;
- --for ignore in is_admin loop
- loop
- fetch is_admin into iadmin_curs_counter;
- exit when not found;
- begin
- select new_ugm.user_id
- into other_org_admin
- from rhnUserGroupMembers new_ugm,
- rhnUserGroupType ugt,
- rhnUserGroup ug,
- rhnUserGroupMembers ugm
- where ugm.user_id = user_id_in
- and ugm.user_group_id = ug.id
- and ug.group_type = ugt.id
- and ugt.label = 'org_admin'
- and ug.id = new_ugm.user_group_id
- and new_ugm.user_id != user_id_in
- and rownum = 1;
- if not found then
+ for ignore in is_admin loop
+ select new_ugm.user_id
+ into other_org_admin
+ from rhnUserGroupMembers new_ugm,
+ rhnUserGroupType ugt,
+ rhnUserGroup ug,
+ rhnUserGroupMembers ugm
+ where ugm.user_id = user_id_in
+ and ugm.user_group_id = ug.id
+ and ug.group_type = ugt.id
+ and ugt.label = 'org_admin'
+ and ug.id = new_ugm.user_group_id
+ and new_ugm.user_id != user_id_in
+ limit 1;
+
+ if not found then
+ -- If we're deleting the org, we don't want
+ -- to raise the exception.
+ if deleting_org = 0 then
+ perform
rhn_exception.raise_exception('cannot_delete_user');
end if;
-
- --end if;
- -- If we're deleting the org, we don't want to raise
- -- the exception.
- if deleting_org = 0 then
- perform
rhn_exception.raise_exception('cannot_delete_user');
- end if;
- end;
- open servergroups_needing_admins;
- loop
- fetch servergroups_needing_admins into sg_curs_id;
- exit when not found;
- --for sg in servergroups_needing_admins loop
- perform
rhn_user.add_servergroup_perm(other_org_admin,sg_curs_id);
- end loop;
-
- close servergroups_needing_admins;
- end loop;
+ end if;
- close is_admin;
+ for sg in servergroups_needing_admins loop
+ perform
rhn_user.add_servergroup_perm(other_org_admin,sg.server_group_id);
+ end loop;
+ end loop;
end if;
-- and now things for every user
- open messages;
- loop
- fetch messages into msg_curs_id;
- exit when not found;
- --for message in messages loop
+ for message in messages loop
delete
from rhnUserMessage
where user_id = user_id_in
- and message_id = msg_curs_id;
- begin
- select 1
- into users
+ and message_id = message.id;
+
+ if exists(select 1
from rhnUserMessage
- where message_id = msg_curs_id
- and rownum = 1;
- delete
+ where message_id = message.id) then
+ delete
from rhnMessage
- where id = msgs_curs_id;
- if not founf then
- null;
- end if;
- end;
+ where id = message.id;
+ end if;
end loop;
@@ -337,7 +261,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
delete from rhnAppInstallSession where user_id = user_id_in;
if other_users != 0 then
update rhnRegToken
- set user_id = nvl(other_org_admin,
other_user_id)
+ set user_id = coalesce(other_org_admin,
other_user_id)
where org_id = our_org_id
and user_id = user_id_in;
begin
@@ -363,5 +287,3 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in
NUMERIC, group_la
-- restore the original setting
update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_org')+1) ) where name = 'search_path';
-
-
diff --git a/schema/spacewalk/postgres/packages/rhn_org.pks
b/schema/spacewalk/postgres/packages/rhn_org.pks
index 8327364..cdbd452 100644
--- a/schema/spacewalk/postgres/packages/rhn_org.pks
+++ b/schema/spacewalk/postgres/packages/rhn_org.pks
@@ -22,8 +22,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC,
group_la
RETURNS NUMERIC
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN 0;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ LANGUAGE PLPGSQL;
@@ -31,16 +30,16 @@ CREATE OR REPLACE FUNCTION delete_org (org_id_in in numeric)
RETURNS VOID
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language plpgsql;
-CREATE OR REPLACE FUNCTION delete_user(user_id_in in numeric, deleting_org in numeric)
+CREATE OR REPLACE FUNCTION delete_user(user_id_in in numeric, deleting_org in numeric
default 0)
RETURNS VOID
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/packages/rhn_package.pkb
b/schema/spacewalk/postgres/packages/rhn_package.pkb
index 69419e4..ce110bd 100644
--- a/schema/spacewalk/postgres/packages/rhn_package.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_package.pkb
@@ -6,15 +6,15 @@ update pg_settings set setting = 'rhn_package,' || setting where
name = 'search_
create or replace FUNCTION canonical_name(name_in IN VARCHAR, evr_in IN EVR_T,
- arch_in IN VARCHAR(400))
- RETURNs VARCHAR as $$
+ arch_in IN VARCHAR DEFAULT NULL)
+ RETURNS VARCHAR as $$
declare
name_out VARCHAR(256);
BEGIN
- name_out := name_in || '-' ||evr_t_as_vre_simple(evr_in);
+ name_out := name_in || '-' || evr_t_as_vre_simple(evr_in);
- IF arch_in IS NOT NULL
+ IF arch_in <> ''
THEN
name_out := name_out || '-' || arch_in;
END IF;
@@ -24,28 +24,20 @@ create or replace FUNCTION canonical_name(name_in IN VARCHAR, evr_in
IN EVR_T,
$$ language 'plpgsql';
-create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC,
separator_in VARCHAR)
+create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC,
separator_in VARCHAR DEFAULT ', ')
RETURNS VARCHAR as $$
declare
list_out VARCHAR(4000);
- channel_occupancy_cursor CURSOR (package_id_in NUMeric) for
+ channel_occupancy_cursor CURSOR (package_id_in numeric) for
SELECT C.id AS channel_id, C.name AS channel_name
FROM rhnChannel C,
rhnChannelPackage CP
WHERE C.id = CP.channel_id
AND CP.package_id = package_id_in
ORDER BY C.name DESC;
- channel record;
- separator_in varchar := ', ';
-
BEGIN
-
- --FOR channel IN channel_occupancy_cursor(package_id_in)
- open channel_occupancy_cursor(package_id_in);
- LOOP
-fetch channel_occupancy_cursor into channel;
-exit when not found;
-
+ FOR channel IN channel_occupancy_cursor(package_id_in)
+ LOOP
IF list_out IS NULL
THEN
list_out := channel.channel_name;
diff --git a/schema/spacewalk/postgres/packages/rhn_package.pks
b/schema/spacewalk/postgres/packages/rhn_package.pks
index 30faeb8..7676c7f 100644
--- a/schema/spacewalk/postgres/packages/rhn_package.pks
+++ b/schema/spacewalk/postgres/packages/rhn_package.pks
@@ -21,24 +21,22 @@ create or replace FUNCTION canonical_name
(
name_in IN VARCHAR,
evr_in IN EVR_T,
- arch_in IN VARCHAR(400)
+ arch_in IN VARCHAR DEFAULT NULL
)
RETURNS VARCHAR
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN NULL;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$
language 'plpgsql';
-create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC,
separator_in VARCHAR)
+create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC,
separator_in VARCHAR DEFAULT ', ')
RETURNS VARCHAR
AS $$
BEGIN
- RAISE EXCEPTION 'Stub called, must be replace by .pkb';
- RETURN NULL;
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
END;
$$ language 'plpgsql';
diff --git a/schema/spacewalk/postgres/packages/rhn_quota.pkb
b/schema/spacewalk/postgres/packages/rhn_quota.pkb
index 6ae8983..8cd481a 100644
--- a/schema/spacewalk/postgres/packages/rhn_quota.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_quota.pkb
@@ -24,10 +24,9 @@ create or replace function recompute_org_quota_used (
) returns numeric as
$$
declare
- retval numeric := 0;
+ retval numeric;
begin
- begin
- select NVL(sum(a.file_size),0)
+ select COALESCE(sum(a.file_size),0)
into retval
from (
select distinct content.id, content.file_size
@@ -40,10 +39,6 @@ create or replace function recompute_org_quota_used (
and cf.id = cr.config_file_id
and cr.config_content_id = content.id
) a;
- exception
- when others then
- null;
- end;
return retval;
end;
@@ -87,16 +82,18 @@ create or replace function set_org_quota_total (
from rhnOrgQuota oq
where oq.org_id = org_id_in;
+ if not found then
+ insert into rhnOrgQuota ( org_id, total )
+ values (org_id_in, total_in);
+ return;
+ end if;
+
perform rhn_config.prune_org_configs(org_id_in, available);
update rhnOrgQuota
set total = total_in
where org_id = org_id_in;
- if not found then
- insert into rhnOrgQuota ( org_id, total )
- values (org_id_in, total_in);
- end if;
-- right now, we completely ignore failure in setting the total to a
-- lower number than is subscribed, because we have no prune. prune
-- will be in the next version, sometime in the not too distant future,
diff --git a/schema/spacewalk/postgres/packages/rhn_server.pkb
b/schema/spacewalk/postgres/packages/rhn_server.pkb
index 0534010..777699b 100644
--- a/schema/spacewalk/postgres/packages/rhn_server.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_server.pkb
@@ -59,12 +59,12 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
where S.id = server_id_in
and not exists (select 1 from rhnSatelliteInfo SI where SI.server_id = S.id)
and not exists (select 1 from rhnProxyInfo PI where PI.server_id = S.id);
+
+ if not found then
+ return 0;
+ end if;
return 1;
- exception
- when no_data_found
- then
- return 0;
end$$ language plpgsql;
create or replace function set_custom_value(
@@ -77,7 +77,7 @@ update pg_settings set setting = 'rhn_server,' || setting where
name = 'search_p
declare
key_id_val numeric;
begin
- select CDK.id into key_id_val
+ select CDK.id into strict key_id_val
from rhnCustomDataKey CDK,
rhnServer S
where S.id = server_id_in
@@ -144,18 +144,17 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
AND user_id = set_uid_in
loop
if rhn_server.system_service_level(server.element, 'provisioning') = 1 then
- begin
select max(id) into snapshot_id
from rhnSnapshot
where server_id = server.element;
- exception
- when NO_DATA_FOUND then
+
+ if snapshot_id is null then
perform rhn_server.snapshot_server(server.element, 'tagging system: ' ||
tagname_in);
select max(id) into snapshot_id
from rhnSnapshot
where server_id = server.element;
- end;
+ end if;
-- now have a snapshot_id to work with...
begin
@@ -279,6 +278,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
where sgm.server_id = server_id_in
);
locked := 0;
+ <<iloop>>
while true loop
begin
insert into rhnPackageNEVRA (id, name_id, evr_id, package_arch_id)
@@ -293,7 +293,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
and (nevra.package_arch_id = sp.package_arch_id
or (nevra.package_arch_id is null
and sp.package_arch_id is null)));
- exit;
+ exit iloop;
exception when unique_violation then
if locked = 1 then
raise;
@@ -341,7 +341,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
-- (an array instead of a table maybe? who knows...)
-- but I've got code to do this handy that I can look at ;)
chained_actions cursor is
- with r(id, prerequisite) as (
+ with recursive r(id, prerequisite) as (
select id, prerequisite
from rhnAction
where id = action_id_in
@@ -420,6 +420,10 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
and s.id = server_id_in
and wc.id = user_id_in;
+ if not found then
+ return 0;
+ end if;
+
-- okay, so they're in the same org. if we have an org admin, they get a free pass
if rhn_user.check_role(user_id_in, 'org_admin') = 1
then
@@ -431,14 +435,13 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
rhnUserServerGroupPerms USG
where SGM.server_group_id = USG.server_group_id
and SGM.server_id = server_id_in
- and USG.user_id = user_id_in
- and rownum = 1;
+ and USG.user_id = user_id_in;
+
+ if not found then
+ return 0;
+ end if;
return 1;
- exception
- when no_data_found
- then
- return 0;
end$$ language plpgsql;
-- *******************************************************************
@@ -448,8 +451,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
-- Called by: insert_into_servergroup, delete_from_servergroup
-- *******************************************************************
create or replace function can_server_consume_virt_slot(server_id_in in numeric,
- group_type_in in
- rhnServerGroupType.label%TYPE)
+ group_type_in in varchar)
returns numeric
as $$
declare
@@ -499,10 +501,10 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
prov_available numeric;
prov_upgrade numeric;
prov_sgid numeric;
- group_label rhnServerGroupType.label%TYPE;
+ group_label varchar;
group_type numeric;
begin
- -- frist, group_type = null, because it's easy...
+ -- first, group_type = null, because it's easy...
-- this will rowlock the servergroup we're trying to change;
-- we probably need to lock the other one, but I think the chances
@@ -511,7 +513,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
into group_type, org_id, used_slots, max_slots
from rhnServerGroup sg
where sg.id = server_group_id_in
- for update of sg.current_members;
+ for update of sg;
if group_type is null then
if used_slots >= max_slots then
@@ -548,7 +550,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
'virtualization_host',
'virtualization_host_platform') then
if used_slots >= max_slots and
- (can_server_consume_virt_slot(server_id_in, group_label) != 1)
+ (rhn_server.can_server_consume_virt_slot(server_id_in, group_label) != 1)
then
perform rhn_exception.raise_exception('servergroup_max_members');
end if;
@@ -561,7 +563,7 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
-- Only update current members if the system in consuming a
-- physical slot.
- if can_server_consume_virt_slot(server_id_in, group_label) = 0 then
+ if rhn_server.can_server_consume_virt_slot(server_id_in, group_label) = 0
then
update rhnServerGroup
set current_members = current_members + 1
where id = server_group_id_in;
@@ -648,10 +650,9 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
oid numeric;
mgmt_sgid numeric;
- label rhnServerGroupType.label%TYPE;
+ label varchar;
group_type numeric;
begin
- begin
select sg.group_type, sg.org_id
into group_type, oid
from rhnServerGroupMembers sgm,
@@ -659,11 +660,11 @@ update pg_settings set setting = 'rhn_server,' || setting
where name = 'search_p
where sg.id = server_group_id_in
and sg.id = sgm.server_group_id
and sgm.server_id = server_id_in
- for update of sg.current_members;
- exception
- when no_data_found then
+ for update of sg;
+
+ if not found then
perform rhn_exception.raise_exception('server_not_in_group');
- end;
+ end if;
-- do group_type is null first
if group_type is null then
diff --git a/schema/spacewalk/postgres/packages/rhn_user.pkb
b/schema/spacewalk/postgres/packages/rhn_user.pkb
index 25058ff..42ab1c9 100644
--- a/schema/spacewalk/postgres/packages/rhn_user.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_user.pkb
@@ -274,9 +274,9 @@ create or replace
rhnUserGroupMembers ugm,
rhnUserGroup ug
where 1=1
- and ugr.id = user_group_id_in
+ and ug.id = user_group_id_in
and ugm.user_group_id = user_group_id_in
- and ugr.group_type = ugt.id
+ and ug.group_type = ugt.id
and ugm.user_id = user_id_in
loop
delete from rhnUserGroupMembers
commit 94bfa8b2132b80e5f1c1fe4c758333af3198ad2d
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 15:13:35 2009 -0400
Remove no-longer-used rhn_qos package.
Jeff confirms this is dead code.
diff --git a/schema/spacewalk/postgres/packages/rhn_qos.pkb
b/schema/spacewalk/postgres/packages/rhn_qos.pkb
deleted file mode 100644
index 67d6b3e..0000000
--- a/schema/spacewalk/postgres/packages/rhn_qos.pkb
+++ /dev/null
@@ -1,93 +0,0 @@
---
--- Copyright (c) 2008 Red Hat, Inc.
---
--- This software is licensed to you under the GNU General Public License,
--- version 2 (GPLv2). There is NO WARRANTY for this software, express or
--- implied, including the implied warranties of MERCHANTABILITY or FITNESS
--- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
--- along with this software; if not, see
---
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
---
--- Red Hat trademarks are not licensed under GPLv2. No permission is
--- granted to use or replicate Red Hat trademarks that are incorporated
--- in this software or its documentation.
---
-
--- create schema rhn_qos;
-
---update in pg_setting
-update pg_settings set setting = 'rhn_qos,' || setting where name =
'search_path';
-
-create or replace function slot_count
- (org_id_in in numeric,
- label_in in varchar)
- returns numeric as $$
- Declare
- tally numeric;
- begin
- select max_members
- into tally
- from rhnServerGroupType rsgt,
- rhnServerGroup rsg
- where 1=1
- and rsgt.label = label_in
- and rsgt.id = rsg.group_type
- and rsg.org_id = org_id_in;
-
- if not found then
- return 0;
- end if;
- return tally;
-
- end ;
-$$ language plpgsql;
-
-
-Create or replace function basic_slot_count(org_id_in in numeric)
- returns numeric as $$
- begin
- return slot_count(org_id_in, 'sw_mgr_entitled');
- end ;
- $$ language plpgsql;
-
-
-
-Create or replace function workgroup_slot_count(org_id_in in numeric)
- returns numeric as $$
- begin
- return slot_count(org_id_in, 'enterprise_entitled');
- end;
- $$ language plpgsql;
-
-
- create or replace function channel_slot_count(org_id_in in numeric, label_in in numeric)
- returns numeric as $$
- declare
- tally numeric;
- begin
- select max_members
- into tally
- from rhnChannelFamily rcf,
- rhnOrgChannelFamilyPermissions rcfp
- where 1=1
- and rcf.label = label_in
- and rcf.id = rcfp.channel_family_id
- and rcfp.org_id = org_id_in;
-
- if not found then
- return 0;
- end if;
- return tally;
- end;
- $$ language plpgsql;
-
-Create or replace function as_slot_count(org_id_in in numeric)
- returns numeric as $$
- begin
- return channel_slot_count(org_id_in, 'rh-advanced-server');
- end ;
- $$ language plpgsql;
-
--- restore the original setting
-update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_qos')+1) ) where name = 'search_path';
-
diff --git a/schema/spacewalk/postgres/packages/rhn_qos.pks
b/schema/spacewalk/postgres/packages/rhn_qos.pks
deleted file mode 100644
index d359a61..0000000
--- a/schema/spacewalk/postgres/packages/rhn_qos.pks
+++ /dev/null
@@ -1,74 +0,0 @@
---
--- Copyright (c) 2008 Red Hat, Inc.
---
--- This software is licensed to you under the GNU General Public License,
--- version 2 (GPLv2). There is NO WARRANTY for this software, express or
--- implied, including the implied warranties of MERCHANTABILITY or FITNESS
--- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
--- along with this software; if not, see
---
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
---
--- Red Hat trademarks are not licensed under GPLv2. No permission is
--- granted to use or replicate Red Hat trademarks that are incorporated
--- in this software or its documentation.
---
-
-create schema rhn_qos;
-
---update in pg_setting
-update pg_settings set setting = 'rhn_qos,' || setting where name =
'search_path';
-
-create or replace function slot_count
-(
- org_id_in in numeric,
- label_in in varchar
-)
-returns numeric
-as $$
-begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
-end;
-$$ language plpgsql;
-
-
-create or replace function basic_slot_count(org_id_in in numeric)
-returns numeric
-as $$
-begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
-end;
-$$ language plpgsql;
-
-create or replace function workgroup_slot_count(org_id_in in numeric)
- returns numeric
-as $$
-begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
-end;
-$$ language plpgsql;
-
-
-create or replace function channel_slot_count(org_id_in in numeric, label_in in numeric)
-returns numeric
-as $$
-begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
-end;
-$$ language plpgsql;
-
-create or replace function as_slot_count(org_id_in in numeric)
-returns numeric
-as $$
-begin
- raise exception 'Stub called, must be replace by .pkb';
- return 0;
-end;
-$$ language plpgsql;
-
--- restore the original setting
-update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_qos')+1) ) where name = 'search_path';
-
commit 47754b3014b8f6ac8ed6c312c871a08f9801b763
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 15:11:12 2009 -0400
Fix spelling errors.
diff --git a/schema/spacewalk/common/data/rhnException.sql
b/schema/spacewalk/common/data/rhnException.sql
index 69b12d9..77c1cc9 100644
--- a/schema/spacewalk/common/data/rhnException.sql
+++ b/schema/spacewalk/common/data/rhnException.sql
@@ -42,7 +42,7 @@ insert into rhnException values (-20238,
'channel_unsubscribe_no_family','Attemp
insert into rhnException values (-20239, 'arch_not_found','Architecture could
not be found');
insert into rhnException values (-20240, 'channel_consent_no_license','No
license agreement exists for that channel');
-insert into rhnException values (-20241,
'channel_subscrib_no_consent','Channel requires consent to license for
subscription');
+insert into rhnException values (-20241,
'channel_subscribe_no_consent','Channel requires consent to license for
subscription');
insert into rhnException values (-20242, 'channel_arch_not_found','Channel
architecture could not be found');
insert into rhnException values (-20243, 'package_arch_not_found','Package
architecture could not be found');
@@ -87,7 +87,7 @@ insert into rhnException values (-20278, 'webreg_duplicate',
'Registration numbe
insert into rhnException values (-20279, 'webreg_not_active', 'Registration
number is not active');
insert into rhnException values (-20280, 'webreg_sync_error', 'Error
synchronizing, xxrh_oai_wrapper.sync_registration_uber');
insert into rhnException values (-20281, 'webreg_not_found', 'Registration
number does not exist');
-insert into rhnException values (-20282, 'webreg_unkown_error', 'Unknown
error during web registration');
+insert into rhnException values (-20282, 'webreg_unknown_error', 'Unknown
error during web registration');
insert into rhnException values (-20283, 'invalid_feature', 'The specified
feature does not exist');
insert into rhnException values (-20284, 'invalid_base_entitlement', 'The
base entitlement is not valid for adding on other entitlements');
insert into rhnException values (-20285, 'invalid_addon_entitlement', 'The
addon entitlement is not valid for adding onto other entitlements');
commit af64bdc44b3e9dadb8e72fed215109cd3d722d09
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 15:10:03 2009 -0400
First-order translation of rhn_entitlements package.
Passes syntax checks, but untested.
diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
new file mode 100644
index 0000000..c6fdcf0
--- /dev/null
+++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb
@@ -0,0 +1,1692 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+
+-- create 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';
+
+ -- *******************************************************************
+ -- 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) = 0 then
+ if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg
(server_id_in, type_label_in, sgid)) 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 < 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
+ if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)
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
+
+ begin
+ select is_base into type_label_in_is_base
+ from rhnServerGroupType
+ where label = type_label_in;
+ exception
+ when no_data_found then
+ perform rhn_exception.raise_exception ( 'invalid_entitlement' );
+ end;
+
+ if type_label_in_is_base = 'N' then
+ perform rhn_exception.raise_exception ( 'invalid_entitlement' );
+ elsif rhn_entitlements.find_compatible_sg ( server_id_in,
+ type_label_in, sgid ) then
+ return 1;
+ else
+ return 0;
+ end if;
+
+ end$$
+language plpgsql;
+
+ -- returns NULL if no match found; this is different from the Oracle coding
+ 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;
+
+ 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
+
+ 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
+ if rhn_entitlements.find_compatible_sg (server_id_in,
+ type_label_in, sgid) 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
+ 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
+ perform 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 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
+ FROM time_series
+ WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1,
+ (INSTR(o_id, '-', INSTR(o_id, '-') + 1) -
INSTR(o_id, '-')) - 1)
+ IN (SELECT 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
+ perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
+ end if;
+ end if;
+
+ exception
+ when no_data_found then
+ perform
rhn_exception.raise_exception('invalid_server_group_member');
+ end;
+
+ 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
+
+ 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 ( 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 := ents_to_process || 'sw_mgr_enterprise';
+
+ roles_to_process := roles_to_process || 'org_admin';
+
+ roles_to_process := roles_to_process || 'system_group_admin';
+
+ roles_to_process := roles_to_process || 'activation_key_admin';
+
+ roles_to_process := roles_to_process || 'org_applicant';
+ elsif service_label_in = 'provisioning' then
+ ents_to_process := ents_to_process || 'rhn_provisioning';
+
+ roles_to_process := roles_to_process || 'system_group_admin';
+
+ roles_to_process := roles_to_process || 'activation_key_admin';
+
+ roles_to_process := roles_to_process || 'config_admin';
+ -- another nasty special case...
+ if enable_in = 'Y' then
+ ents_to_process := ents_to_process || 'sw_mgr_enterprise';
+ end if;
+ elsif service_label_in = 'monitoring' then
+ ents_to_process := ents_to_process || 'rhn_monitor';
+
+ roles_to_process := roles_to_process || 'monitoring_admin';
+ elsif service_label_in = 'virtualization' then
+ ents_to_process := ents_to_process || 'rhn_virtualization';
+
+ roles_to_process := roles_to_process || 'config_admin';
+ elsif service_label_in = 'virtualization_platform' then
+ ents_to_process := ents_to_process || 'rhn_virtualization_platform';
+ roles_to_process := roles_to_process || 'config_admin';
+ elsif service_label_in = 'nonlinux' then
+ ents_to_process := ents_to_process || 'rhn_nonlinux';
+ roles_to_process := 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..array_upper(ents_to_process, 1) 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 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 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 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 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 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 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 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 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, prune_everything, repoll_virt_guest_entitlements
+ -- *******************************************************************
+ create or replace function prune_group (
+ group_id_in in numeric,
+ type_in in char,
+ quantity_in in numeric
+ ) returns void
+as $$
+ declare
+ usergroups cursor for
+ 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;
+ servergroups cursor for
+ select server_id, server_group_id, sgt.id 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;
+ 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
+ perform 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
+ perform rhn_entitlements.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
+ perform rhn_channel.clear_subscriptions(sg.server_id);
+ 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
+
+ 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
+ perform 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
+ perform 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
+ 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 set_customer_enterprise(to_org_id_in);
+ else
+ perform unset_customer_enterprise(to_org_id_in);
+ end if;
+ end if;
+
+ if group_label_in = 'provisioning_entitled' then
+ if new_quantity > 0 then
+ perform set_customer_provisioning(to_org_id_in);
+ else
+ perform unset_customer_provisioning(to_org_id_in);
+ end if;
+ end if;
+
+ if group_label_in = 'monitoring_entitled' then
+ if new_quantity > 0 then
+ perform set_customer_monitoring(to_org_id_in);
+ else
+ perform 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
+
+ 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;
+ exception
+ when NO_DATA_FOUND then
+ perform 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 id
+ into cfam_id
+ from rhnChannelFamily
+ where label = channel_family_label_in;
+ exception
+ when NO_DATA_FOUND then
+ perform rhn_exception.raise_exception(
+ 'invalid_channel_family');
+ 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
+ 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
+ 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
+ perform 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
+ 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
+ ) returns void
+as $$
+ declare
+ prev_ent_count numeric;
+ prev_ent_count_sum numeric;
+ cfam_id numeric;
+ 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 id
+ into cfam_id
+ from rhnChannelFamily
+ where label = channel_family_label_in;
+ exception
+ when NO_DATA_FOUND then
+ perform rhn_exception.raise_exception(
+ 'invalid_channel_family');
+ end;
+
+ -- 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
+ perform rhn_entitlements.set_family_count(org_id_in,
+ cfam_id,
+ quantity_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
+ ) returns void
+as $$
+ declare
+ group_id numeric;
+ quantity numeric;
+ 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;
+
+ perform rhn_entitlements.prune_group(
+ group_id,
+ type_in,
+ quantity
+ );
+ 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 nextval('rhn_user_group_id_seq'), 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 nextval('rhn_server_group_id_seq'), name, name,
+ quantity, 0, id, customer_id_in,
+ sysdate, sysdate
+ from rhnServerGroupType
+ where id = group_type_in
+ );
+ 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, prune_everything
+ -- *******************************************************************
+ create or replace function prune_family (
+ customer_id_in in numeric,
+ channel_family_id_in in numeric,
+ quantity_in in numeric
+ ) returns void
+as $$
+ declare
+ 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;
+
+ update rhnPrivateChannelFamily
+ set max_members = quantity_in
+ where 1=1
+ and org_id = customer_id_in
+ and channel_family_id = channel_family_id_in;
+
+ for sc in serverchannels loop
+ perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1);
+ end loop;
+ 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
+ ) 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;
+ begin
+ quantity := quantity_in;
+ if quantity is not null and quantity < 0 then
+ quantity := 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
+ );
+ update rhnPrivateChannelFamily
+ set max_members = quantity
+ where org_id = customer_id_in
+ and channel_family_id = channel_family_id_in;
+ return;
+ end loop;
+
+ insert into rhnPrivateChannelFamily (
+ channel_family_id, org_id, max_members, current_members
+ ) values (
+ channel_family_id_in, customer_id_in, quantity, 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
+ );
+ 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 prune_everything (
+ customer_id_in in numeric
+ ) returns void
+as $$
+ declare
+ everything cursor for
+ -- all our server groups
+ select sg.id id,
+ 'S' as type,
+ sg.max_members quantity
+ from rhnServerGroup sg
+ where sg.org_id = customer_id_in
+ union
+ -- all our user groups
+ select ug.id id,
+ 'U' as type,
+ ug.max_members quantity
+ from rhnUserGroup ug
+ where ug.org_id = customer_id_in
+ union (
+ -- all the channel families we have perms to
+ select cfp.channel_family_id id,
+ 'C' as type,
+ cfp.max_members quantity
+ from rhnOrgChannelFamilyPermissions cfp
+ where cfp.org_id = customer_id_in
+ union
+ -- plus all the ones we're using that we have no perms for
+ select cfm.channel_family_id id,
+ 'C' as type,
+ 0 quantity
+ from rhnChannelFamily cf,
+ rhnChannelFamilyMembers cfm,
+ rhnServerChannel sc,
+ rhnServer s
+ where s.org_id = customer_id_in
+ and s.id = sc.server_id
+ and sc.channel_id = cfm.channel_id
+ and cfm.channel_family_id = cf.id
+ and cf.org_id is not null
+ and cf.org_id != customer_id_in
+ and not exists (
+ select 1
+ from rhnOrgChannelFamilyPermissions cfp
+ where cfp.org_id = customer_id_in
+ and cfp.channel_family_id = cfm.channel_family_id
+ )
+ );
+ begin
+ for one in everything loop
+ if one.type in ('U','S') then
+ perform rhn_entitlements.prune_group(one.id, one.type, one.quantity);
+ else
+ perform rhn_entitlements.prune_family(customer_id_in, one.id,
one.quantity);
+ end if;
+ 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';
diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pks
b/schema/spacewalk/postgres/packages/rhn_entitlements.pks
new file mode 100644
index 0000000..f00d3b6
--- /dev/null
+++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pks
@@ -0,0 +1,308 @@
+--
+-- Copyright (c) 2008 Red Hat, Inc.
+--
+-- This software is licensed to you under the GNU General Public License,
+-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
+-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
+-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
+-- along with this software; if not, see
+--
http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
+--
+-- Red Hat trademarks are not licensed under GPLv2. No permission is
+-- granted to use or replicate Red Hat trademarks that are incorporated
+-- in this software or its documentation.
+--
+
+create 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 remove_org_entitlements (
+ org_id_in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function entitlement_grants_service (
+ entitlement_in in varchar,
+ service_level_in in varchar
+ ) returns numeric
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function can_entitle_server (
+ server_id_in in numeric,
+ type_label_in in varchar
+ )
+ returns numeric
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function can_switch_base (
+ server_id_in in integer,
+ type_label_in in varchar
+ )
+ returns numeric
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function find_compatible_sg (
+ server_id_in in numeric,
+ type_label_in in varchar
+ )
+ returns numeric
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function unentitle_server (
+ server_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function repoll_virt_guest_entitlements(
+ server_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function get_server_entitlement (
+ server_id_in in numeric
+ ) returns varchar[]
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function modify_org_service (
+ org_id_in in numeric,
+ service_label_in in varchar,
+ enable_in in char
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function set_customer_enterprise (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function set_customer_provisioning (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function set_customer_nonlinux (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function unset_customer_enterprise (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function unset_customer_provisioning (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function unset_customer_nonlinux (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ 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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ 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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function activate_system_entitlement(
+ org_id_in in numeric,
+ group_label_in in varchar,
+ quantity_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function activate_channel_entitlement(
+ org_id_in in numeric,
+ channel_family_label_in in varchar,
+ quantity_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ -- this makes NO checks that the quantity is within max,
+ -- so we should NEVER run this unless we KNOW 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 $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function prune_everything (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+END;
+$$ language plpgsql;
+
+ create or replace function subscribe_newest_servers (
+ customer_id_in in numeric
+ ) returns void
+as $$
+BEGIN
+ RAISE EXCEPTION 'Stub called, must be replaced by .pkb';
+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 f48310463f7ed55b24b4e742b9d58182167057f8
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 13:20:08 2009 -0400
The rhn_date_manip package is not used anywhere.
Rather than try to think about how to fix its API for Postgres
(which has not got package variables), I'm just removing it.
The code still exists in the oracle subdirectory if anyone
ever makes a start at using it.
diff --git a/schema/spacewalk/postgres/packages/rhn_date_manip.pkb
b/schema/spacewalk/postgres/packages/rhn_date_manip.pkb
deleted file mode 100644
index deaeef0..0000000
--- a/schema/spacewalk/postgres/packages/rhn_date_manip.pkb
+++ /dev/null
@@ -1,59 +0,0 @@
-
---create schema rhn_date_manip;
-
-update pg_settings set setting = 'rhn_date_manip,' || setting where name =
'search_path';
-
-create or replace function get_reporting_period_start()
- returns timestamptz as $$
- declare
- months_ago numeric;
- weeks_ago numeric;
- target_date timestamptz;
- day_number numeric;
- periods_ago numeric;
- begin
- months_ago := periods_ago/2;
- weeks_ago := mod(periods_ago,2);
- -- target_date :=
trunc(add_months(current_timestamp::date,-months_ago)-(7*weeks_ago));
- target_date := trunc((current_timestamp + (-months_ago||'
months')::interval)-((7*weeks_ago)||' days')::interval);
- day_number := to_char(target_date,'DD')::numeric;
- -- squish the date to the 1st or the 16th
- if day_number > 16 then
- target_date := target_date - ((day_number||'
day')::interval - (16||'day')::interval);
- else
- target_date := target_date - ((day_number||'
day')::interval - (1||'day')::interval);
- end if;
- return target_date;
- end ;
- $$ language 'plpgsql';
-
-
-
- create or replace function get_reporting_period_end()
- returns timestamptz as $$
- declare
- months_ago numeric;
- weeks_ago numeric;
- target_date timestamptz;
- day_number numeric;
- periods_ago numeric;
- begin
- months_ago := periods_ago/2;
- weeks_ago := mod(periods_ago,2);
-
- --target_date :=
trunc(add_months(current_timestamp,-months_ago)-(7*weeks_ago));
- target_date := trunc((current_timestamp + (-months_ago||'
months')::interval)-((7*weeks_ago)||' days')::interval);
- day_number := to_char(target_date,'DD')::numeric;
- -- squish the date to the 1st or the 16th
- if day_number > 16 then
- target_date := last_day(target_date);
- else
- target_date := target_date + ((-day_number||'
day')::interval + (15||' day')::interval) + ((1||'
day')::interval-(1/86400||' day')::interval);
- end if;
- return target_date;
- end ;
- $$ language 'plpgsql';
-
-
--- restore the original setting
-update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_date_manip')+1) ) where name = 'search_path';
diff --git a/schema/spacewalk/postgres/packages/rhn_date_manip.pks
b/schema/spacewalk/postgres/packages/rhn_date_manip.pks
deleted file mode 100644
index 89f657d..0000000
--- a/schema/spacewalk/postgres/packages/rhn_date_manip.pks
+++ /dev/null
@@ -1,23 +0,0 @@
-
-create schema rhn_date_manip;
-
-update pg_settings set setting = 'rhn_date_manip,' || setting where name =
'search_path';
-
-create or replace function get_reporting_period_start()
-returns timestamptz
-as $$
-begin
- raise exception 'Stub called, must be replaced by .pkb';
-end;
-$$ language 'plpgsql';
-
-create or replace function get_reporting_period_end()
-returns timestamptz as
-$$
-begin
- raise exception 'Stub called, must be replaced by .pkb';
-end;
-$$ language 'plpgsql';
-
--- restore the original setting
-update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_date_manip')+1) ) where name = 'search_path';
commit 984a347f2afbd47756e90584364799dd670b62db
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 01:49:20 2009 -0400
Some more fixes from manual function testing.
diff --git a/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
b/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
index d5e83fa..6de2349 100644
--- a/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
+++ b/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
@@ -30,7 +30,7 @@ as
$$
declare
sessions cursor (bound_val numeric) for
- select rowid from PXTSessions
+ select ctid from PXTSessions
where expires < bound_val;
counter numeric := 0;
@@ -38,7 +38,7 @@ begin
for session in sessions (bound_in) loop
- delete from PXTSessions where rowid = session.rowid;
+ delete from PXTSessions where ctid = session.ctid;
counter := counter + 1;
diff --git a/schema/spacewalk/postgres/procs/queue_server.sql
b/schema/spacewalk/postgres/procs/queue_server.sql
index 0436bb7..81e4d41 100644
--- a/schema/spacewalk/postgres/procs/queue_server.sql
+++ b/schema/spacewalk/postgres/procs/queue_server.sql
@@ -22,16 +22,14 @@
CREATE OR REPLACE FUNCTION
-queue_server(server_id_in IN NUMERIC, immediate_in IN NUMERIC)
+queue_server(server_id_in IN NUMERIC, immediate_in IN NUMERIC DEFAULT 1)
RETURNS VOID
AS
$$
DECLARE
org_id_tmp NUMERIC;
- immediate_in_tmp NUMERIC;
BEGIN
- immediate_in_tmp := immediate_in;
- IF immediate_in_tmp > 0
+ IF immediate_in > 0
THEN
DELETE FROM rhnServerNeededCache WHERE server_id = server_id_in;
INSERT INTO rhnServerNeededCache
@@ -40,7 +38,8 @@ BEGIN
WHERE server_id = server_id_in);
ELSE
- SELECT org_id INTO org_id_tmp FROM rhnServer WHERE id = server_id_in;
+ SELECT org_id INTO STRICT org_id_tmp
+ FROM rhnServer WHERE id = server_id_in;
INSERT
INTO rhnTaskQueue
diff --git a/schema/spacewalk/postgres/procs/queue_server_overloaded.sql
b/schema/spacewalk/postgres/procs/queue_server_overloaded.sql
deleted file mode 100644
index 304eb1f..0000000
--- a/schema/spacewalk/postgres/procs/queue_server_overloaded.sql
+++ /dev/null
@@ -1,32 +0,0 @@
--- Creating an overloaded function for queue_server so that it can be called from app
with one parameter.
-
-CREATE OR REPLACE FUNCTION
-queue_server(server_id_in IN NUMERIC)
-RETURNS VOID
-AS
-$$
-DECLARE
- org_id_tmp NUMERIC;
- immediate_in_tmp NUMERIC;
-BEGIN
- immediate_in_tmp := 1;
- IF immediate_in_tmp > 0
- THEN
- DELETE FROM rhnServerNeededCache WHERE server_id = server_id_in;
- INSERT INTO rhnServerNeededCache
- (SELECT server_id, errata_id, package_id
- FROM rhnServerNeededView
- WHERE server_id = server_id_in);
-
- ELSE
- SELECT org_id INTO org_id_tmp FROM rhnServer WHERE id = server_id_in;
-
- INSERT
- INTO rhnTaskQueue
- (org_id, task_name, task_data)
- VALUES (org_id_tmp,
- 'update_server_errata_cache',
- server_id_in);
- END IF;
-END;
-$$ LANGUAGE plpgsql;
diff --git a/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql
b/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql
index ef3aa07..7f57a0c 100644
--- a/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql
+++ b/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql
@@ -29,34 +29,33 @@ begin
set state = 'PENDING',
output = 'Awaiting update'
where last_check < (
- select (
- current_timestamp - greatest(15 / 60 / 24,
- ((3 * rhn_deployed_probe.check_interval_minutes) / 60 / 24)))
+ select
+ current_timestamp - interval '1 minute' * greatest(15,
+ (3 * rhn_deployed_probe.check_interval_minutes))
from rhn_deployed_probe
where rhn_deployed_probe.recid = rhn_probe_state.probe_id
);
-
- update rhn_multi_scout_threshold
- set scout_warning_threshold = (select
- decode(scout_warning_threshold_is_all,0,
- scout_warning_threshold,count(scout_id))
- from rhn_probe_state p, rhn_multi_scout_threshold t
+ update rhn_multi_scout_threshold t
+ set scout_warning_threshold = (
+ select
+ case scout_warning_threshold_is_all
+ when '0' then scout_warning_threshold
+ else count(scout_id) end
+ from rhn_probe_state p
where t.probe_id=p.probe_id
and state in ('OK', 'WARNING', 'CRITICAL')
- group by t.probe_id
-) ,
-
-
- scout_critical_threshold=(
- select
- decode(scout_crit_threshold_is_all,0,
- scout_critical_threshold,count(scout_id))
- from rhn_probe_state p, rhn_multi_scout_threshold t
+ group by t.probe_id
+ ),
+ scout_critical_threshold = (
+ select
+ case scout_crit_threshold_is_all
+ when '0' then scout_critical_threshold
+ else count(scout_id) end
+ from rhn_probe_state p
where t.probe_id=p.probe_id
and state in ('OK', 'WARNING', 'CRITICAL')
- group by t.probe_id
-
+ group by t.probe_id
);
end;
$$
diff --git a/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
b/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
index 3535917..582c814 100644
--- a/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
+++ b/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql
@@ -44,20 +44,16 @@ declare
id_history_items_curs numeric;
begin
- open states;
+ for id_states_curs in states
loop
- fetch states into id_states_curs;
- exit when not found;
- open history_items(id_states_curs);
+ for id_history_items_curs in history_items(id_states_curs)
loop
- fetch history_items into id_history_items_curs;
- exit when not found;
update rhnKickstartSessionHistory
set message = message_in
where id = id_history_items;
return;
end loop;
- close history_items;
+
insert into rhnKickstartSessionHistory (
id, kickstart_session_id, state_id, message
) values (
diff --git a/schema/spacewalk/postgres/procs/truncateCacheQueue.sql
b/schema/spacewalk/postgres/procs/truncateCacheQueue.sql
index c741990..3e95df1 100644
--- a/schema/spacewalk/postgres/procs/truncateCacheQueue.sql
+++ b/schema/spacewalk/postgres/procs/truncateCacheQueue.sql
@@ -19,7 +19,7 @@
create or replace function truncateCacheQueue() returns void as
$$
begin
- execute 'Truncate Table rhnOrgErrataCacheQueue';
+ Truncate Table rhnOrgErrataCacheQueue;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnErrata.sql
b/schema/spacewalk/postgres/triggers/rhnErrata.sql
index 2461d46..57d875a 100644
--- a/schema/spacewalk/postgres/triggers/rhnErrata.sql
+++ b/schema/spacewalk/postgres/triggers/rhnErrata.sql
@@ -16,11 +16,23 @@
--
--
-
-create or replace function rhn_errata_mod_trig_fun() returns trigger
+create or replace function rhn_errata_ins_trig_fun() returns trigger
as
$$
+begin
+ if ( new.last_modified is null ) then
+ new.last_modified := current_timestamp;
+ end if;
+ new.modified := current_timestamp;
+
+ return new;
+end;
+$$ language plpgsql;
+
+create or replace function rhn_errata_upd_trig_fun() returns trigger
+as
+$$
begin
if ( new.last_modified = old.last_modified ) or
( new.last_modified is null ) then
@@ -35,7 +47,13 @@ $$ language plpgsql;
create trigger
-rhn_errata_mod_trig
-before insert or update on rhnErrata
+rhn_errata_ins_trig
+before insert on rhnErrata
+for each row
+execute procedure rhn_errata_ins_trig_fun();
+
+create trigger
+rhn_errata_upd_trig
+before update on rhnErrata
for each row
-execute procedure rhn_errata_mod_trig_fun();
+execute procedure rhn_errata_upd_trig_fun();