spacewalk-commits July 2013

spacewalk-commits@lists.fedorahosted.org

12 participants
455 discussions

7 commits - rel-eng/packages schema/spacewalk
by Tomas Lestach 24 Jul '13

24 Jul '13

rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/common/tables/log.sql | 4 schema/spacewalk/oracle/packages/logging.pkb | 156 +++--- schema/spacewalk/oracle/packages/logging.pks | 3 schema/spacewalk/postgres/packages/logging.pkb | 98 ++-- schema/spacewalk/postgres/packages/logging.pks | 2 schema/spacewalk/spacewalk-schema.spec | 10 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle | 4 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql | 4 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle | 1 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql | 5 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle | 25 + schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql | 2 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle | 147 ++++++ schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql | 236 ++++++++++ schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql | 2 16 files changed, 602 insertions(+), 99 deletions(-) New commits: commit 7ae88a56be7af25fb89b2916974c844aa2d3823a Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Jul 24 12:14:23 2013 +0200 Automatic commit of package [spacewalk-schema] release [2.1.2-1]. diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index 1a3cde3..feca68d 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -2.1.1-1 schema/spacewalk/ +2.1.2-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index f8ade67..cbdb6db 100644 --- a/schema/spacewalk/spacewalk-schema.spec +++ b/schema/spacewalk/spacewalk-schema.spec @@ -4,7 +4,7 @@ Name: spacewalk-schema Group: Applications/Internet Summary: Oracle SQL schema for Spacewalk server -Version: 2.1.1 +Version: 2.1.2 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz @@ -71,6 +71,14 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql* %changelog +* Wed Jul 24 2013 Tomas Lestach <tlestach(a)redhat.com> 2.1.2-1 +- fix oracle equivalent source sha1 +- fix_f624b843 +- extract logging.recreate_trigger from logging.enable_logging +- replace integer with number/ric in logging package +- change log_id type on logged tables +- change log table integer -> number + * Tue Jul 23 2013 Michael Mraka <michael.mraka(a)redhat.com> 2.1.1-1 - 876019 - schema upgrade scripts - made serverchannels cursor properly parametrized commit ed94fcc2c0f301baf3b4915936d192bcf06585be Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Jul 24 12:13:54 2013 +0200 fix oracle equivalent source sha1 diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql index c02ee78..1ec95e3 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 b34e5effdc23dd2bc7bb9c805c71a0396d947848 +-- oracle equivalent source sha1 49a42a42768d83a188a939412c353bf6de4013ab -- -- Copyright (c) 2008--2012 Red Hat, Inc. -- commit 6bc65e46f0b6693764c0562f30a2dabb69d3e1d7 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Jul 24 12:12:05 2013 +0200 fix_f624b843 diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql index 3c5f689..b207494 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 db59999d5662a6f91b00e119e676577fc054e02e +-- oracle equivalent source sha1 4ef54d7b4132976de79f3b6a202ab85b6fe507ed ALTER TABLE log ALTER id TYPE numeric; ALTER TABLE log ALTER user_id TYPE numeric; commit 42ab5d86f1679b65ae322f892c1209d33316a6c1 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Jul 24 09:13:44 2013 +0200 extract logging.recreate_trigger from logging.enable_logging + fix lower issues on PG diff --git a/schema/spacewalk/oracle/packages/logging.pkb b/schema/spacewalk/oracle/packages/logging.pkb index 797c203..78566a7 100644 --- a/schema/spacewalk/oracle/packages/logging.pkb +++ b/schema/spacewalk/oracle/packages/logging.pkb @@ -18,6 +18,7 @@ is the_log_id number; the_user_id number; the_stamp timestamp with local time zone; + procedure clear_log_id is begin @@ -25,6 +26,7 @@ is the_user_id := null; the_stamp := current_timestamp; end clear_log_id; + procedure set_log_auth(user_id in number) is begin @@ -34,6 +36,7 @@ is the_user_id := user_id; the_stamp := current_timestamp; end set_log_auth; + function get_log_id return number is begin @@ -48,72 +51,97 @@ is return the_log_id; end get_log_id; -procedure enable_logging(table_name_in in varchar) -is - pk_column varchar(512); - ddl_columns varchar(4000); - the_insert varchar(4000); - already_not_null exception; - pragma exception_init(already_not_null, -1442); -begin - select column_name into pk_column - from user_cons_columns - where constraint_name = ( - select constraint_name - from user_constraints - where table_name = upper(table_name_in) - and constraint_type = 'P' - and owner = user - ) - and owner = user ; - ddl_columns := ''; - for rec in ( - select column_name - from user_tab_columns - where table_name = upper(table_name_in) - and column_name not in ( pk_column, 'CREATED', 'MODIFIED' ) - order by column_id - ) loop - ddl_columns := ddl_columns || ', ' || rec.column_name; - end loop; + function get_pk_column(table_name_in in varchar) return varchar + is + pk_column varchar(512); + begin + select column_name into pk_column + from user_cons_columns + where constraint_name = ( + select constraint_name + from user_constraints + where table_name = upper(table_name_in) + and constraint_type = 'P' + and owner = user + ) + and owner = user ; + return pk_column; + end get_pk_column; - execute immediate 'create table ' || table_name_in || '_log - as select ' || pk_column || ', logging.get_log_id() as log_id, ''A'' as action' || ddl_columns - || ' from ' || table_name_in; + function get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) return varchar + is + ddl_columns varchar(4000); + begin + ddl_columns := ''; + for rec in ( + select column_name + from user_tab_columns + where table_name = upper(table_name_in) + and column_name not in ( pk_column_in, 'CREATED', 'MODIFIED' ) + order by column_id + ) loop + ddl_columns := ddl_columns || ', ' || rec.column_name; + end loop; + return ddl_columns; + end get_ddl_columns; + + procedure recreate_trigger(table_name_in in varchar) + is + pk_column varchar(512); + ddl_columns varchar(4000); + the_insert varchar(4000); + begin + pk_column := get_pk_column(table_name_in); + ddl_columns := get_ddl_columns(table_name_in, pk_column); + the_insert := 'insert into ' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') + values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', :old.') || ');'; + execute immediate 'create or replace trigger ' || table_name_in || '_log_trig + after insert or update or delete on ' || table_name_in || ' + for each row + declare + log_id_v number; + tg_op char(1); + begin + log_id_v := logging.get_log_id(); + if updating then + if :old.' || pk_column || ' <> :new.' || pk_column || ' then raise_application_error(-20298, ''Cannot update column ' || table_name_in || '.' || pk_column || '.''); end if; + tg_op := ''U''; + end if; + if deleting then + tg_op := ''D''; + ' || the_insert || ' + else + if inserting then tg_op := ''I''; end if; + ' || replace(the_insert, ':old.', ':new.') || ' + end if; + end; + '; + end recreate_trigger; + + procedure enable_logging(table_name_in in varchar) + is + pk_column varchar(512); + ddl_columns varchar(4000); + already_not_null exception; + pragma exception_init(already_not_null, -1442); begin - execute immediate 'alter table ' || table_name_in || '_log modify ' || pk_column || ' not null'; - exception when already_not_null then - null; - end; - execute immediate 'create index ' || table_name_in || '_log_idx on ' || table_name_in || '_log(' || pk_column || ')'; - execute immediate 'alter table ' || table_name_in || '_log modify log_id not null'; - execute immediate 'alter table ' || table_name_in || '_log add foreign key (log_id) references log(id)'; - execute immediate 'alter table ' || table_name_in || '_log modify action not null'; + pk_column := get_pk_column(table_name_in); + ddl_columns := get_ddl_columns(table_name_in, pk_column); - the_insert := 'insert into ' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') - values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', :old.') || ');'; - execute immediate 'create or replace trigger ' || table_name_in || '_log_trig - after insert or update or delete on ' || table_name_in || ' - for each row - declare - log_id_v number; - tg_op char(1); + execute immediate 'create table ' || table_name_in || '_log + as select ' || pk_column || ', logging.get_log_id() as log_id, ''A'' as action' || ddl_columns + || ' from ' || table_name_in; begin - log_id_v := logging.get_log_id(); - if updating then - if :old.' || pk_column || ' <> :new.' || pk_column || ' then raise_application_error(-20298, ''Cannot update column ' || table_name_in || '.' || pk_column || '.''); end if; - tg_op := ''U''; - end if; - if deleting then - tg_op := ''D''; - ' || the_insert || ' - else - if inserting then tg_op := ''I''; end if; - ' || replace(the_insert, ':old.', ':new.') || ' - end if; + execute immediate 'alter table ' || table_name_in || '_log modify ' || pk_column || ' not null'; + exception when already_not_null then + null; end; - '; -end enable_logging; + execute immediate 'create index ' || table_name_in || '_log_idx on ' || table_name_in || '_log(' || pk_column || ')'; + execute immediate 'alter table ' || table_name_in || '_log modify log_id not null'; + execute immediate 'alter table ' || table_name_in || '_log add foreign key (log_id) references log(id)'; + execute immediate 'alter table ' || table_name_in || '_log modify action not null'; + recreate_trigger(table_name_in); + end enable_logging; end logging; / show errors diff --git a/schema/spacewalk/oracle/packages/logging.pks b/schema/spacewalk/oracle/packages/logging.pks index 7dac79c..adcd248 100644 --- a/schema/spacewalk/oracle/packages/logging.pks +++ b/schema/spacewalk/oracle/packages/logging.pks @@ -18,6 +18,7 @@ is procedure clear_log_id; procedure set_log_auth(user_id in number); function get_log_id return number; + procedure recreate_trigger(table_name_in in varchar); procedure enable_logging(table_name_in in varchar); end logging; / diff --git a/schema/spacewalk/postgres/packages/logging.pkb b/schema/spacewalk/postgres/packages/logging.pkb index 4304e46..084b3a5 100644 --- a/schema/spacewalk/postgres/packages/logging.pkb +++ b/schema/spacewalk/postgres/packages/logging.pkb @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e +-- oracle equivalent source sha1 386fd52474c4e2ea59ec9a9fc6271bc3a1126adb -- -- Copyright (c) 2013 Red Hat, Inc. -- @@ -106,21 +106,18 @@ begin end; $$ language plpgsql set search_path from current; -create or replace function enable_logging(table_name_in in varchar) -returns void +create or replace function _get_primary_key_column(table_name_in in varchar) +returns varchar as $$ declare pk_column varchar; - ddl_columns varchar; - the_insert varchar; - rec record; begin select constraint_column_usage.column_name into strict pk_column from information_schema.table_constraints, information_schema.constraint_column_usage where constraint_column_usage.table_catalog = current_catalog and constraint_column_usage.table_schema = 'public' - and constraint_column_usage.table_name = table_name_in + and constraint_column_usage.table_name = lower(table_name_in) and constraint_column_usage.constraint_catalog = current_catalog and constraint_column_usage.constraint_schema = 'public' and constraint_column_usage.table_catalog = table_constraints.constraint_catalog @@ -130,27 +127,47 @@ begin and constraint_column_usage.constraint_catalog = table_constraints.constraint_catalog and constraint_column_usage.constraint_name = table_constraints.constraint_name and table_constraints.constraint_type = 'PRIMARY KEY'; + return pk_column; +end; +$$ +language plpgsql set search_path from current; + +create or replace function _get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) +returns varchar +as +$$ +declare + ddl_columns varchar; + rec record; +begin ddl_columns := ''; for rec in ( select column_name from information_schema.columns where table_catalog = current_catalog and table_schema = 'public' - and table_name = table_name_in - and column_name not in ( pk_column, 'created', 'modified' ) + and table_name = lower(table_name_in) + and column_name not in (pk_column_in, 'created', 'modified' ) ) loop ddl_columns := ddl_columns || ', ' || rec.column_name; end loop; + return ddl_columns; +end; +$$ +language plpgsql set search_path from current; - execute 'create table public.' || table_name_in || '_log - as select ' || pk_column || ', logging.get_log_id()::numeric as log_id, ''A''::char as action' || ddl_columns - || ' from ' || table_name_in; - execute 'alter table public.' || table_name_in || '_log alter ' || pk_column || ' set not null'; - execute 'create index ' || table_name_in || '_log_idx on public.' || table_name_in || '_log(' || pk_column || ')'; - execute 'alter table public.' || table_name_in || '_log alter log_id set not null'; - execute 'alter table public.' || table_name_in || '_log add foreign key (log_id) references log(id)'; - execute 'alter table public.' || table_name_in || '_log alter action set not null'; +create or replace function recreate_trigger(table_name_in in varchar) +returns void +as +$$ +declare + pk_column varchar; + ddl_columns varchar; + the_insert varchar; +begin + pk_column := _get_primary_key_column(table_name_in); + ddl_columns := _get_ddl_columns(table_name_in, pk_column); the_insert := 'insert into public.' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', old.') || ');'; execute 'create or replace function public.' || table_name_in || '_log_trig_fun() returns trigger as @@ -172,6 +189,7 @@ begin '' language plpgsql;'; + execute 'drop trigger if exists ' || table_name_in || '_log_trig' || ' on ' || table_name_in; execute 'create trigger ' || table_name_in || '_log_trig after insert or update or delete on public.' || table_name_in || ' for each row @@ -179,6 +197,30 @@ begin end; $$ -language plpgsql; +language plpgsql set search_path from current; + +create or replace function enable_logging(table_name_in in varchar) +returns void +as +$$ +declare + pk_column varchar; + ddl_columns varchar; +begin + pk_column := _get_primary_key_column(table_name_in); + ddl_columns := _get_ddl_columns(table_name_in, pk_column); + + execute 'create table public.' || table_name_in || '_log + as select ' || pk_column || ', logging.get_log_id()::numeric as log_id, ''A''::char as action' || ddl_columns + || ' from ' || table_name_in; + execute 'alter table public.' || table_name_in || '_log alter ' || pk_column || ' set not null'; + execute 'create index ' || table_name_in || '_log_idx on public.' || table_name_in || '_log(' || pk_column || ')'; + execute 'alter table public.' || table_name_in || '_log alter log_id set not null'; + execute 'alter table public.' || table_name_in || '_log add foreign key (log_id) references log(id)'; + execute 'alter table public.' || table_name_in || '_log alter action set not null'; + perform recreate_trigger(table_name_in); +end; +$$ +language plpgsql set search_path from current; update pg_settings set setting = overlay( setting placing '' from 1 for (length('logging')+1) ) where name = 'search_path'; diff --git a/schema/spacewalk/postgres/packages/logging.pks b/schema/spacewalk/postgres/packages/logging.pks index fcbb09a..8076191 100644 --- a/schema/spacewalk/postgres/packages/logging.pks +++ b/schema/spacewalk/postgres/packages/logging.pks @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7 +-- oracle equivalent source sha1 bf830c8fe841b043caab71aa49bef3903c5df39b -- -- Copyright (c) 2013 Red Hat, Inc. -- diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle index 7dac79c..adcd248 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle @@ -18,6 +18,7 @@ is procedure clear_log_id; procedure set_log_auth(user_id in number); function get_log_id return number; + procedure recreate_trigger(table_name_in in varchar); procedure enable_logging(table_name_in in varchar); end logging; / diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql index 73c181c..c1ac623 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql @@ -1,2 +1,2 @@ --- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7 +-- oracle equivalent source sha1 bf830c8fe841b043caab71aa49bef3903c5df39b -- This file has intentionally been left empty. diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle index 797c203..78566a7 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle @@ -18,6 +18,7 @@ is the_log_id number; the_user_id number; the_stamp timestamp with local time zone; + procedure clear_log_id is begin @@ -25,6 +26,7 @@ is the_user_id := null; the_stamp := current_timestamp; end clear_log_id; + procedure set_log_auth(user_id in number) is begin @@ -34,6 +36,7 @@ is the_user_id := user_id; the_stamp := current_timestamp; end set_log_auth; + function get_log_id return number is begin @@ -48,72 +51,97 @@ is return the_log_id; end get_log_id; -procedure enable_logging(table_name_in in varchar) -is - pk_column varchar(512); - ddl_columns varchar(4000); - the_insert varchar(4000); - already_not_null exception; - pragma exception_init(already_not_null, -1442); -begin - select column_name into pk_column - from user_cons_columns - where constraint_name = ( - select constraint_name - from user_constraints - where table_name = upper(table_name_in) - and constraint_type = 'P' - and owner = user - ) - and owner = user ; - ddl_columns := ''; - for rec in ( - select column_name - from user_tab_columns - where table_name = upper(table_name_in) - and column_name not in ( pk_column, 'CREATED', 'MODIFIED' ) - order by column_id - ) loop - ddl_columns := ddl_columns || ', ' || rec.column_name; - end loop; + function get_pk_column(table_name_in in varchar) return varchar + is + pk_column varchar(512); + begin + select column_name into pk_column + from user_cons_columns + where constraint_name = ( + select constraint_name + from user_constraints + where table_name = upper(table_name_in) + and constraint_type = 'P' + and owner = user + ) + and owner = user ; + return pk_column; + end get_pk_column; - execute immediate 'create table ' || table_name_in || '_log - as select ' || pk_column || ', logging.get_log_id() as log_id, ''A'' as action' || ddl_columns - || ' from ' || table_name_in; + function get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) return varchar + is + ddl_columns varchar(4000); + begin + ddl_columns := ''; + for rec in ( + select column_name + from user_tab_columns + where table_name = upper(table_name_in) + and column_name not in ( pk_column_in, 'CREATED', 'MODIFIED' ) + order by column_id + ) loop + ddl_columns := ddl_columns || ', ' || rec.column_name; + end loop; + return ddl_columns; + end get_ddl_columns; + + procedure recreate_trigger(table_name_in in varchar) + is + pk_column varchar(512); + ddl_columns varchar(4000); + the_insert varchar(4000); + begin + pk_column := get_pk_column(table_name_in); + ddl_columns := get_ddl_columns(table_name_in, pk_column); + the_insert := 'insert into ' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') + values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', :old.') || ');'; + execute immediate 'create or replace trigger ' || table_name_in || '_log_trig + after insert or update or delete on ' || table_name_in || ' + for each row + declare + log_id_v number; + tg_op char(1); + begin + log_id_v := logging.get_log_id(); + if updating then + if :old.' || pk_column || ' <> :new.' || pk_column || ' then raise_application_error(-20298, ''Cannot update column ' || table_name_in || '.' || pk_column || '.''); end if; + tg_op := ''U''; + end if; + if deleting then + tg_op := ''D''; + ' || the_insert || ' + else + if inserting then tg_op := ''I''; end if; + ' || replace(the_insert, ':old.', ':new.') || ' + end if; + end; + '; + end recreate_trigger; + + procedure enable_logging(table_name_in in varchar) + is + pk_column varchar(512); + ddl_columns varchar(4000); + already_not_null exception; + pragma exception_init(already_not_null, -1442); begin - execute immediate 'alter table ' || table_name_in || '_log modify ' || pk_column || ' not null'; - exception when already_not_null then - null; - end; - execute immediate 'create index ' || table_name_in || '_log_idx on ' || table_name_in || '_log(' || pk_column || ')'; - execute immediate 'alter table ' || table_name_in || '_log modify log_id not null'; - execute immediate 'alter table ' || table_name_in || '_log add foreign key (log_id) references log(id)'; - execute immediate 'alter table ' || table_name_in || '_log modify action not null'; + pk_column := get_pk_column(table_name_in); + ddl_columns := get_ddl_columns(table_name_in, pk_column); - the_insert := 'insert into ' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') - values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', :old.') || ');'; - execute immediate 'create or replace trigger ' || table_name_in || '_log_trig - after insert or update or delete on ' || table_name_in || ' - for each row - declare - log_id_v number; - tg_op char(1); + execute immediate 'create table ' || table_name_in || '_log + as select ' || pk_column || ', logging.get_log_id() as log_id, ''A'' as action' || ddl_columns + || ' from ' || table_name_in; begin - log_id_v := logging.get_log_id(); - if updating then - if :old.' || pk_column || ' <> :new.' || pk_column || ' then raise_application_error(-20298, ''Cannot update column ' || table_name_in || '.' || pk_column || '.''); end if; - tg_op := ''U''; - end if; - if deleting then - tg_op := ''D''; - ' || the_insert || ' - else - if inserting then tg_op := ''I''; end if; - ' || replace(the_insert, ':old.', ':new.') || ' - end if; + execute immediate 'alter table ' || table_name_in || '_log modify ' || pk_column || ' not null'; + exception when already_not_null then + null; end; - '; -end enable_logging; + execute immediate 'create index ' || table_name_in || '_log_idx on ' || table_name_in || '_log(' || pk_column || ')'; + execute immediate 'alter table ' || table_name_in || '_log modify log_id not null'; + execute immediate 'alter table ' || table_name_in || '_log add foreign key (log_id) references log(id)'; + execute immediate 'alter table ' || table_name_in || '_log modify action not null'; + recreate_trigger(table_name_in); + end enable_logging; end logging; / show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql index 409f761..80a0a31 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e +-- oracle equivalent source sha1 386fd52474c4e2ea59ec9a9fc6271bc3a1126adb -- -- Copyright (c) 2013 Red Hat, Inc. -- @@ -19,12 +19,12 @@ update pg_settings set setting = 'logging,' || setting where name = 'search_path'; drop function clear_log_id(); -drop function _set_log_auth(user_id in numeric, stamp in varchar); -drop function set_log_auth(user_id in numeric); +drop function _set_log_auth(user_id in integer, stamp in varchar); +drop function set_log_auth(user_id in integer); drop function _get_log_auth(); drop function _get_log_stamp(); drop function _get_log_id(); -drop function _set_log_id(log_id in numeric); +drop function _set_log_id(log_id in integer); drop function get_log_id(); drop function enable_logging(table_name_in in varchar); @@ -116,21 +116,18 @@ begin end; $$ language plpgsql set search_path from current; -create or replace function enable_logging(table_name_in in varchar) -returns void +create or replace function _get_primary_key_column(table_name_in in varchar) +returns varchar as $$ declare pk_column varchar; - ddl_columns varchar; - the_insert varchar; - rec record; begin select constraint_column_usage.column_name into strict pk_column from information_schema.table_constraints, information_schema.constraint_column_usage where constraint_column_usage.table_catalog = current_catalog and constraint_column_usage.table_schema = 'public' - and constraint_column_usage.table_name = table_name_in + and constraint_column_usage.table_name = lower(table_name_in) and constraint_column_usage.constraint_catalog = current_catalog and constraint_column_usage.constraint_schema = 'public' and constraint_column_usage.table_catalog = table_constraints.constraint_catalog @@ -140,27 +137,47 @@ begin and constraint_column_usage.constraint_catalog = table_constraints.constraint_catalog and constraint_column_usage.constraint_name = table_constraints.constraint_name and table_constraints.constraint_type = 'PRIMARY KEY'; + return pk_column; +end; +$$ +language plpgsql set search_path from current; + +create or replace function _get_ddl_columns(table_name_in in varchar, pk_column_in in varchar) +returns varchar +as +$$ +declare + ddl_columns varchar; + rec record; +begin ddl_columns := ''; for rec in ( select column_name from information_schema.columns where table_catalog = current_catalog and table_schema = 'public' - and table_name = table_name_in - and column_name not in ( pk_column, 'created', 'modified' ) + and table_name = lower(table_name_in) + and column_name not in (pk_column_in, 'created', 'modified' ) ) loop ddl_columns := ddl_columns || ', ' || rec.column_name; end loop; + return ddl_columns; +end; +$$ +language plpgsql set search_path from current; - execute 'create table public.' || table_name_in || '_log - as select ' || pk_column || ', logging.get_log_id()::numeric as log_id, ''A''::char as action' || ddl_columns - || ' from ' || table_name_in; - execute 'alter table public.' || table_name_in || '_log alter ' || pk_column || ' set not null'; - execute 'create index ' || table_name_in || '_log_idx on public.' || table_name_in || '_log(' || pk_column || ')'; - execute 'alter table public.' || table_name_in || '_log alter log_id set not null'; - execute 'alter table public.' || table_name_in || '_log add foreign key (log_id) references log(id)'; - execute 'alter table public.' || table_name_in || '_log alter action set not null'; +create or replace function recreate_trigger(table_name_in in varchar) +returns void +as +$$ +declare + pk_column varchar; + ddl_columns varchar; + the_insert varchar; +begin + pk_column := _get_primary_key_column(table_name_in); + ddl_columns := _get_ddl_columns(table_name_in, pk_column); the_insert := 'insert into public.' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', old.') || ');'; execute 'create or replace function public.' || table_name_in || '_log_trig_fun() returns trigger as @@ -182,6 +199,7 @@ begin '' language plpgsql;'; + execute 'drop trigger if exists ' || table_name_in || '_log_trig' || ' on ' || table_name_in; execute 'create trigger ' || table_name_in || '_log_trig after insert or update or delete on public.' || table_name_in || ' for each row @@ -189,6 +207,30 @@ begin end; $$ -language plpgsql; +language plpgsql set search_path from current; + +create or replace function enable_logging(table_name_in in varchar) +returns void +as +$$ +declare + pk_column varchar; + ddl_columns varchar; +begin + pk_column := _get_primary_key_column(table_name_in); + ddl_columns := _get_ddl_columns(table_name_in, pk_column); + + execute 'create table public.' || table_name_in || '_log + as select ' || pk_column || ', logging.get_log_id()::numeric as log_id, ''A''::char as action' || ddl_columns + || ' from ' || table_name_in; + execute 'alter table public.' || table_name_in || '_log alter ' || pk_column || ' set not null'; + execute 'create index ' || table_name_in || '_log_idx on public.' || table_name_in || '_log(' || pk_column || ')'; + execute 'alter table public.' || table_name_in || '_log alter log_id set not null'; + execute 'alter table public.' || table_name_in || '_log add foreign key (log_id) references log(id)'; + execute 'alter table public.' || table_name_in || '_log alter action set not null'; + perform recreate_trigger(table_name_in); +end; +$$ +language plpgsql set search_path from current; update pg_settings set setting = overlay( setting placing '' from 1 for (length('logging')+1) ) where name = 'search_path'; commit 6dc9f81c3ba4dd8b6fcf7994b66108857a9e5f22 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Tue Jul 23 11:55:30 2013 +0200 replace integer with number/ric in logging package diff --git a/schema/spacewalk/oracle/packages/logging.pkb b/schema/spacewalk/oracle/packages/logging.pkb index 946e0d1..797c203 100644 --- a/schema/spacewalk/oracle/packages/logging.pkb +++ b/schema/spacewalk/oracle/packages/logging.pkb @@ -15,8 +15,8 @@ create or replace package body logging is - the_log_id integer; - the_user_id integer; + the_log_id number; + the_user_id number; the_stamp timestamp with local time zone; procedure clear_log_id is @@ -25,7 +25,7 @@ is the_user_id := null; the_stamp := current_timestamp; end clear_log_id; - procedure set_log_auth(user_id in integer) + procedure set_log_auth(user_id in number) is begin if the_stamp is null then @@ -96,7 +96,7 @@ begin after insert or update or delete on ' || table_name_in || ' for each row declare - log_id_v integer; + log_id_v number; tg_op char(1); begin log_id_v := logging.get_log_id(); diff --git a/schema/spacewalk/oracle/packages/logging.pks b/schema/spacewalk/oracle/packages/logging.pks index 1a324ef..7dac79c 100644 --- a/schema/spacewalk/oracle/packages/logging.pks +++ b/schema/spacewalk/oracle/packages/logging.pks @@ -16,7 +16,7 @@ create or replace package logging is procedure clear_log_id; - procedure set_log_auth(user_id in integer); + procedure set_log_auth(user_id in number); function get_log_id return number; procedure enable_logging(table_name_in in varchar); end logging; diff --git a/schema/spacewalk/postgres/packages/logging.pkb b/schema/spacewalk/postgres/packages/logging.pkb index 0697c41..4304e46 100644 --- a/schema/spacewalk/postgres/packages/logging.pkb +++ b/schema/spacewalk/postgres/packages/logging.pkb @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 5cac57e2eb784ae8a4f66d945ca38110ce6497b4 +-- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e -- -- Copyright (c) 2013 Red Hat, Inc. -- @@ -31,7 +31,7 @@ $$ return $$ language pltclu set search_path from current; -create or replace function _set_log_auth(user_id in integer, stamp in varchar) +create or replace function _set_log_auth(user_id in numeric, stamp in varchar) returns void as $$ @@ -41,7 +41,7 @@ $$ set the_stamp $2 $$ language pltclu set search_path from current; -create or replace function set_log_auth(user_id in integer) +create or replace function set_log_auth(user_id in numeric) returns void as $$ @@ -51,7 +51,7 @@ end $$ language plpgsql set search_path from current; create or replace function _get_log_auth() -returns integer +returns numeric as $$ global the_user_id @@ -67,14 +67,14 @@ $$ $$ language pltclu set search_path from current; create or replace function _get_log_id() -returns integer +returns numeric as $$ global the_log_id return $the_log_id $$ language pltclu set search_path from current; -create or replace function _set_log_id(log_id in integer) +create or replace function _set_log_id(log_id in numeric) returns void as $$ @@ -84,12 +84,12 @@ $$ $$ language pltclu set search_path from current; create or replace function get_log_id() -returns integer +returns numeric as $$ declare - the_log_id integer; - the_user_id integer; + the_log_id numeric; + the_user_id numeric; the_stamp varchar; begin the_log_id := _get_log_id(); @@ -100,7 +100,7 @@ begin insert into public.log (id, stamp, user_id) values (the_log_id, case when the_stamp = '' then current_timestamp else the_stamp::timestamp with time zone end, - case when the_user_id = '0' then null else the_user_id::integer end); + case when the_user_id = '0' then null else the_user_id::numeric end); perform _set_log_id(the_log_id); return the_log_id; end; @@ -143,7 +143,7 @@ begin end loop; execute 'create table public.' || table_name_in || '_log - as select ' || pk_column || ', logging.get_log_id()::integer as log_id, ''A''::char as action' || ddl_columns + as select ' || pk_column || ', logging.get_log_id()::numeric as log_id, ''A''::char as action' || ddl_columns || ' from ' || table_name_in; execute 'alter table public.' || table_name_in || '_log alter ' || pk_column || ' set not null'; execute 'create index ' || table_name_in || '_log_idx on public.' || table_name_in || '_log(' || pk_column || ')'; @@ -156,7 +156,7 @@ begin execute 'create or replace function public.' || table_name_in || '_log_trig_fun() returns trigger as '' declare - log_id_v integer; + log_id_v numeric; begin log_id_v := logging.get_log_id(); if tg_op = ''''UPDATE'''' then diff --git a/schema/spacewalk/postgres/packages/logging.pks b/schema/spacewalk/postgres/packages/logging.pks index 7385974..fcbb09a 100644 --- a/schema/spacewalk/postgres/packages/logging.pks +++ b/schema/spacewalk/postgres/packages/logging.pks @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 5f0b00361ac0a36d5ec4c2ddae6d0f7de583407d +-- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7 -- -- Copyright (c) 2013 Red Hat, Inc. -- diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle new file mode 100644 index 0000000..7dac79c --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.oracle @@ -0,0 +1,24 @@ +-- +-- Copyright (c) 2013 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- implied, including the implied warranties of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +-- along with this software; if not, see +-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +-- +-- Red Hat trademarks are not licensed under GPLv2. No permission is +-- granted to use or replicate Red Hat trademarks that are incorporated +-- in this software or its documentation. +-- + +create or replace package logging +is + procedure clear_log_id; + procedure set_log_auth(user_id in number); + function get_log_id return number; + procedure enable_logging(table_name_in in varchar); +end logging; +/ +show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql new file mode 100644 index 0000000..73c181c --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/003-logging.pks.sql.postgresql @@ -0,0 +1,2 @@ +-- oracle equivalent source sha1 ab8d4ef90c80fa905a5dcb815e9d722f5a11f8b7 +-- This file has intentionally been left empty. diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle new file mode 100644 index 0000000..797c203 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.oracle @@ -0,0 +1,119 @@ +-- +-- Copyright (c) 2013 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- implied, including the implied warranties of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +-- along with this software; if not, see +-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +-- +-- Red Hat trademarks are not licensed under GPLv2. No permission is +-- granted to use or replicate Red Hat trademarks that are incorporated +-- in this software or its documentation. +-- + +create or replace package body logging +is + the_log_id number; + the_user_id number; + the_stamp timestamp with local time zone; + procedure clear_log_id + is + begin + the_log_id := null; + the_user_id := null; + the_stamp := current_timestamp; + end clear_log_id; + procedure set_log_auth(user_id in number) + is + begin + if the_stamp is null then + raise_application_error(-20299, 'Call set_log_auth need to follow clear_log_id.'); + end if; + the_user_id := user_id; + the_stamp := current_timestamp; + end set_log_auth; + function get_log_id return number + is + begin + if the_stamp is null then + raise_application_error(-20297, 'Call get_log_id need to follow set_log_auth.'); + end if; + if the_log_id is null then + insert into log (id, stamp, user_id) + values (log_seq.nextval, the_stamp, the_user_id) + returning id into the_log_id; + end if; + return the_log_id; + end get_log_id; + +procedure enable_logging(table_name_in in varchar) +is + pk_column varchar(512); + ddl_columns varchar(4000); + the_insert varchar(4000); + already_not_null exception; + pragma exception_init(already_not_null, -1442); +begin + select column_name into pk_column + from user_cons_columns + where constraint_name = ( + select constraint_name + from user_constraints + where table_name = upper(table_name_in) + and constraint_type = 'P' + and owner = user + ) + and owner = user ; + ddl_columns := ''; + for rec in ( + select column_name + from user_tab_columns + where table_name = upper(table_name_in) + and column_name not in ( pk_column, 'CREATED', 'MODIFIED' ) + order by column_id + ) loop + ddl_columns := ddl_columns || ', ' || rec.column_name; + end loop; + + execute immediate 'create table ' || table_name_in || '_log + as select ' || pk_column || ', logging.get_log_id() as log_id, ''A'' as action' || ddl_columns + || ' from ' || table_name_in; + begin + execute immediate 'alter table ' || table_name_in || '_log modify ' || pk_column || ' not null'; + exception when already_not_null then + null; + end; + execute immediate 'create index ' || table_name_in || '_log_idx on ' || table_name_in || '_log(' || pk_column || ')'; + execute immediate 'alter table ' || table_name_in || '_log modify log_id not null'; + execute immediate 'alter table ' || table_name_in || '_log add foreign key (log_id) references log(id)'; + execute immediate 'alter table ' || table_name_in || '_log modify action not null'; + + the_insert := 'insert into ' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') + values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', :old.') || ');'; + execute immediate 'create or replace trigger ' || table_name_in || '_log_trig + after insert or update or delete on ' || table_name_in || ' + for each row + declare + log_id_v number; + tg_op char(1); + begin + log_id_v := logging.get_log_id(); + if updating then + if :old.' || pk_column || ' <> :new.' || pk_column || ' then raise_application_error(-20298, ''Cannot update column ' || table_name_in || '.' || pk_column || '.''); end if; + tg_op := ''U''; + end if; + if deleting then + tg_op := ''D''; + ' || the_insert || ' + else + if inserting then tg_op := ''I''; end if; + ' || replace(the_insert, ':old.', ':new.') || ' + end if; + end; + '; +end enable_logging; +end logging; +/ +show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql new file mode 100644 index 0000000..409f761 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/004-logging.pkb.sql.postgresql @@ -0,0 +1,194 @@ +-- oracle equivalent source sha1 5516322a531c100e303d185db192624b7046dd6e +-- +-- Copyright (c) 2013 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 logging; + +update pg_settings set setting = 'logging,' || setting where name = 'search_path'; + +drop function clear_log_id(); +drop function _set_log_auth(user_id in numeric, stamp in varchar); +drop function set_log_auth(user_id in numeric); +drop function _get_log_auth(); +drop function _get_log_stamp(); +drop function _get_log_id(); +drop function _set_log_id(log_id in numeric); +drop function get_log_id(); +drop function enable_logging(table_name_in in varchar); + +create or replace function clear_log_id() +returns void +as +$$ + global the_log_id + set the_log_id 0 + global the_user_id + set the_user_id 0 + global the_stamp + set the_stamp "" + return +$$ language pltclu set search_path from current; + +create or replace function _set_log_auth(user_id in numeric, stamp in varchar) +returns void +as +$$ + global the_user_id + set the_user_id $1 + global the_stamp + set the_stamp $2 +$$ language pltclu set search_path from current; + +create or replace function set_log_auth(user_id in numeric) +returns void +as +$$ +begin + perform _set_log_auth(user_id, current_timestamp::varchar); +end +$$ language plpgsql set search_path from current; + +create or replace function _get_log_auth() +returns numeric +as +$$ + global the_user_id + return $the_user_id +$$ language pltclu set search_path from current; + +create or replace function _get_log_stamp() +returns varchar +as +$$ + global the_stamp + return $the_stamp +$$ language pltclu set search_path from current; + +create or replace function _get_log_id() +returns numeric +as +$$ + global the_log_id + return $the_log_id +$$ language pltclu set search_path from current; + +create or replace function _set_log_id(log_id in numeric) +returns void +as +$$ + global the_log_id + set the_log_id $1 + return +$$ language pltclu set search_path from current; + +create or replace function get_log_id() +returns numeric +as +$$ +declare + the_log_id numeric; + the_user_id numeric; + the_stamp varchar; +begin + the_log_id := _get_log_id(); + if the_log_id is not null and the_log_id > 0 then return the_log_id ; end if; + the_log_id := nextval('log_seq'); + the_user_id := _get_log_auth(); + the_stamp := _get_log_stamp(); + insert into public.log (id, stamp, user_id) + values (the_log_id, + case when the_stamp = '' then current_timestamp else the_stamp::timestamp with time zone end, + case when the_user_id = '0' then null else the_user_id::numeric end); + perform _set_log_id(the_log_id); + return the_log_id; +end; +$$ language plpgsql set search_path from current; + +create or replace function enable_logging(table_name_in in varchar) +returns void +as +$$ +declare + pk_column varchar; + ddl_columns varchar; + the_insert varchar; + rec record; +begin + select constraint_column_usage.column_name into strict pk_column + from information_schema.table_constraints, information_schema.constraint_column_usage + where constraint_column_usage.table_catalog = current_catalog + and constraint_column_usage.table_schema = 'public' + and constraint_column_usage.table_name = table_name_in + and constraint_column_usage.constraint_catalog = current_catalog + and constraint_column_usage.constraint_schema = 'public' + and constraint_column_usage.table_catalog = table_constraints.constraint_catalog + and constraint_column_usage.table_catalog = table_constraints.table_catalog + and constraint_column_usage.table_schema = table_constraints.constraint_schema + and constraint_column_usage.table_schema = table_constraints.table_schema + and constraint_column_usage.constraint_catalog = table_constraints.constraint_catalog + and constraint_column_usage.constraint_name = table_constraints.constraint_name + and table_constraints.constraint_type = 'PRIMARY KEY'; + ddl_columns := ''; + for rec in ( + select column_name + from information_schema.columns + where table_catalog = current_catalog + and table_schema = 'public' + and table_name = table_name_in + and column_name not in ( pk_column, 'created', 'modified' ) + ) loop + ddl_columns := ddl_columns || ', ' || rec.column_name; + end loop; + + execute 'create table public.' || table_name_in || '_log + as select ' || pk_column || ', logging.get_log_id()::numeric as log_id, ''A''::char as action' || ddl_columns + || ' from ' || table_name_in; + execute 'alter table public.' || table_name_in || '_log alter ' || pk_column || ' set not null'; + execute 'create index ' || table_name_in || '_log_idx on public.' || table_name_in || '_log(' || pk_column || ')'; + execute 'alter table public.' || table_name_in || '_log alter log_id set not null'; + execute 'alter table public.' || table_name_in || '_log add foreign key (log_id) references log(id)'; + execute 'alter table public.' || table_name_in || '_log alter action set not null'; + + the_insert := 'insert into public.' || table_name_in || '_log (log_id, action, ' || pk_column || ddl_columns ||') + values (log_id_v, substr(tg_op, 1, 1)' || replace(', ' || pk_column || ddl_columns, ', ', ', old.') || ');'; + execute 'create or replace function public.' || table_name_in || '_log_trig_fun() returns trigger as + '' + declare + log_id_v numeric; + begin + log_id_v := logging.get_log_id(); + if tg_op = ''''UPDATE'''' then + if old.' || pk_column || ' <> new.' || pk_column || ' then raise exception ''''Cannot update column ' || table_name_in || '.' || pk_column || '.''''; end if; + end if; + if tg_op = ''''DELETE'''' then + ' || the_insert || ' + else + ' || replace(the_insert, 'old.', 'new.') || ' + end if; + return new; + end; + '' + language plpgsql;'; + + execute 'create trigger ' || table_name_in || '_log_trig + after insert or update or delete on public.' || table_name_in || ' + for each row + execute procedure ' || table_name_in || '_log_trig_fun()'; + +end; +$$ +language plpgsql; + +update pg_settings set setting = overlay( setting placing '' from 1 for (length('logging')+1) ) where name = 'search_path'; commit 19766208b7c491c1e5db65a3f990d37ccef9f68c Author: Tomas Lestach <tlestach(a)redhat.com> Date: Tue Jul 23 11:45:54 2013 +0200 change log_id type on logged tables diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle new file mode 100644 index 0000000..b7b00b1 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.oracle @@ -0,0 +1 @@ +-- This file is intentionally left empty. diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql new file mode 100644 index 0000000..b757a3c --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/002-log-tables.sql.postgresql @@ -0,0 +1,5 @@ +-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8 + +ALTER TABLE web_contact_log ALTER log_id TYPE numeric; +ALTER TABLE rhnServer_log ALTER log_id TYPE numeric; +ALTER TABLE rhnServerGroup_log ALTER log_id TYPE numeric; commit f624b843fa9577edbb138b64e614544248edb7e6 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Tue Jul 23 11:25:45 2013 +0200 change log table integer -> number diff --git a/schema/spacewalk/common/tables/log.sql b/schema/spacewalk/common/tables/log.sql index 13d2d20..e2b4469 100644 --- a/schema/spacewalk/common/tables/log.sql +++ b/schema/spacewalk/common/tables/log.sql @@ -16,9 +16,9 @@ CREATE TABLE log ( - id INTEGER NOT NULL PRIMARY KEY, + id NUMBER NOT NULL PRIMARY KEY, stamp TIMESTAMP WITH TIME ZONE, - user_id INTEGER REFERENCES web_contact_all(id) + user_id NUMBER REFERENCES web_contact_all(id) ) ENABLE ROW MOVEMENT ; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle new file mode 100644 index 0000000..a179a5c --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.oracle @@ -0,0 +1,4 @@ +ALTER TABLE log MODIFY ( + id number, + user_id number +); diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql new file mode 100644 index 0000000..3c5f689 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/001-log.sql.postgresql @@ -0,0 +1,4 @@ +-- oracle equivalent source sha1 db59999d5662a6f91b00e119e676577fc054e02e + +ALTER TABLE log ALTER id TYPE numeric; +ALTER TABLE log ALTER user_id TYPE numeric;

1 0

Changes to 'refs/tags/spacewalk-setup-2.1.2-1'
by Michael Mraka 24 Jul '13

24 Jul '13

Tag 'spacewalk-setup-2.1.2-1' created by Michael Mraka <michael.mraka(a)redhat.com> at 2013-07-24 07:44 +0000 Tagging package [spacewalk-setup] version [2.1.2-1] in directory [spacewalk/setup/]. Changes since spacewalk-reports-2.1.1-1: Michael Mraka (2): single parameter system_debug is not supported Automatic commit of package [spacewalk-setup] release [2.1.2-1]. --- rel-eng/packages/spacewalk-setup | 2 +- spacewalk/setup/bin/spacewalk-setup | 3 ++- spacewalk/setup/spacewalk-setup.spec | 5 ++++- 3 files changed, 7 insertions(+), 3 deletions(-) ---

1 0

2 commits - rel-eng/packages spacewalk/setup
by Michael Mraka 24 Jul '13

24 Jul '13

rel-eng/packages/spacewalk-setup | 2 +- spacewalk/setup/bin/spacewalk-setup | 3 ++- spacewalk/setup/spacewalk-setup.spec | 5 ++++- 3 files changed, 7 insertions(+), 3 deletions(-) New commits: commit c7778b5db9f760ce7ce91f12d2f7897603c408ab Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Wed Jul 24 09:44:08 2013 +0200 Automatic commit of package [spacewalk-setup] release [2.1.2-1]. diff --git a/rel-eng/packages/spacewalk-setup b/rel-eng/packages/spacewalk-setup index e5d5976..f9356e4 100644 --- a/rel-eng/packages/spacewalk-setup +++ b/rel-eng/packages/spacewalk-setup @@ -1 +1 @@ -2.1.1-1 spacewalk/setup/ +2.1.2-1 spacewalk/setup/ diff --git a/spacewalk/setup/spacewalk-setup.spec b/spacewalk/setup/spacewalk-setup.spec index 3e823d0..9b43b29 100644 --- a/spacewalk/setup/spacewalk-setup.spec +++ b/spacewalk/setup/spacewalk-setup.spec @@ -1,7 +1,7 @@ %{!?fedora: %global sbinpath /sbin}%{?fedora: %global sbinpath %{_sbindir}} Name: spacewalk-setup -Version: 2.1.1 +Version: 2.1.2 Release: 1%{?dist} Summary: Initial setup tools for Red Hat Spacewalk @@ -102,6 +102,9 @@ rm -rf %{buildroot} %doc LICENSE %changelog +* Wed Jul 24 2013 Michael Mraka <michael.mraka(a)redhat.com> 2.1.2-1 +- single parameter system_debug is not supported + * Tue Jul 23 2013 Michael Mraka <michael.mraka(a)redhat.com> 2.1.1-1 - make sure selinux is working and files are labeled commit d9b46aacd35c5c954329250c20c01c06cf885a5f Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Wed Jul 24 09:39:10 2013 +0200 single parameter system_debug is not supported diff --git a/spacewalk/setup/bin/spacewalk-setup b/spacewalk/setup/bin/spacewalk-setup index a14f4c8..6dcc4c7 100755 --- a/spacewalk/setup/bin/spacewalk-setup +++ b/spacewalk/setup/bin/spacewalk-setup @@ -56,7 +56,8 @@ if (not $opts{"skip-logfile-init"}) { } if (Spacewalk::Setup::have_selinux()) { - Spacewalk::Setup::system_or_exit(['/usr/sbin/spacewalk-selinux-enable'], 42, + print Spacewalk::Setup::loc("* Setting up Selinux..\n"); + Spacewalk::Setup::system_or_exit(['/usr/sbin/spacewalk-selinux-enable', ''], 42, 'Could not enable selinux policy.'); }

1 0

Changes to 'refs/tags/spacewalk-reports-2.1.1-1'
by StephenHerr 23 Jul '13

23 Jul '13

Tag 'spacewalk-reports-2.1.1-1' created by Stephen Herr <sherr(a)redhat.com> at 2013-07-23 19:48 +0000 Tagging package [spacewalk-reports] version [2.1.1-1] in directory [reporting/]. Changes since spacewalk-schema-2.1.1-1: Christopher Duryee (1): 987640 - Add new field to splice-export report Stephen Herr (1): Automatic commit of package [spacewalk-reports] release [2.1.1-1]. --- rel-eng/packages/spacewalk-reports | 2 +- reporting/reports/data/splice-export | 7 +++++-- reporting/spacewalk-reports.spec | 6 +++++- 3 files changed, 11 insertions(+), 4 deletions(-) ---

1 0

2 commits - rel-eng/packages reporting/reports reporting/spacewalk-reports.spec
by StephenHerr 23 Jul '13

23 Jul '13

rel-eng/packages/spacewalk-reports | 2 +- reporting/reports/data/splice-export | 7 +++++-- reporting/spacewalk-reports.spec | 6 +++++- 3 files changed, 11 insertions(+), 4 deletions(-) New commits: commit 66fa19fd680a4220429f40d60cef4fafe64351af Author: Stephen Herr <sherr(a)redhat.com> Date: Tue Jul 23 15:48:18 2013 -0400 Automatic commit of package [spacewalk-reports] release [2.1.1-1]. diff --git a/rel-eng/packages/spacewalk-reports b/rel-eng/packages/spacewalk-reports index aabbdb9..f373fe6 100644 --- a/rel-eng/packages/spacewalk-reports +++ b/rel-eng/packages/spacewalk-reports @@ -1 +1 @@ -2.0.1-1 reporting/ +2.1.1-1 reporting/ diff --git a/reporting/spacewalk-reports.spec b/reporting/spacewalk-reports.spec index 334fae4..98a3bcc 100644 --- a/reporting/spacewalk-reports.spec +++ b/reporting/spacewalk-reports.spec @@ -2,7 +2,7 @@ Name: spacewalk-reports Summary: Script based reporting Group: Applications/Internet License: GPLv2 -Version: 2.1.0 +Version: 2.1.1 Release: 1%{?dist} URL: https://fedorahosted.org/spacewalk Source0: https://fedorahosted.org/releases/s/p/spacewalk/%{name}-%{version}.tar.gz @@ -43,6 +43,10 @@ rm -rf $RPM_BUILD_ROOT %doc COPYING %changelog +* Tue Jul 23 2013 Stephen Herr <sherr(a)redhat.com> 2.1.1-1 +- 987640 - Add new field to splice-export report +- Bumping package versions for 2.1. + * Wed Jul 17 2013 Tomas Kasparek <tkasparek(a)redhat.com> 2.0.1-1 - Bumping package versions for 2.0. commit 96cc6685990697aac54f69e8d5a044f02d0619b5 Author: Christopher Duryee <cduryee(a)redhat.com> Date: Tue Jul 23 15:48:03 2013 -0400 987640 - Add new field to splice-export report diff --git a/reporting/reports/data/splice-export b/reporting/reports/data/splice-export index 9e487d2..acb6ceb 100644 --- a/reporting/reports/data/splice-export +++ b/reporting/reports/data/splice-export @@ -30,6 +30,7 @@ columns: hardware Hardware information memory Memory info sockets Socket count + is_virtualized Virtualization status (irrespective of host/guest mapping) multival_columns: @@ -44,7 +45,7 @@ sql: select server_id, organization, org_id, name, hostname, ip_address, ipv6_address, registered_by, registration_time, last_checkin_time, software_channel, - entitlements, system_group, virtual_host, architecture, hardware, memory, sockets + entitlements, system_group, virtual_host, architecture, hardware, memory, sockets, is_virtualized from ( select rhnserver.id as server_id, rhnserver.name as name, @@ -74,7 +75,9 @@ sql: ) as memory, ( select rhncpu.nrsocket from rhncpu where rhncpu.server_id = rhnserver.id - ) as sockets + ) as sockets, + (case when (select rhnVirtualInstance.virtual_system_id from rhnVirtualInstance where rhnVirtualInstance.virtual_system_id=rhnserver.id) is null then 'No' else 'Yes' end + ) as is_virtualized from rhnserver left outer join ( select server_id, hostname, ipaddr, ip6addr

1 0

Changes to 'refs/tags/spacewalk-schema-2.1.1-1'
by Michael Mraka 23 Jul '13

23 Jul '13

Tag 'spacewalk-schema-2.1.1-1' created by Michael Mraka <michael.mraka(a)redhat.com> at 2013-07-23 14:54 +0000 Tagging package [spacewalk-schema] version [2.1.1-1] in directory [schema/spacewalk/]. Changes since spacewalk-java-2.1.5-1: Michael Mraka (5): 876019 - fixed misplaced end loop fixed indentation made serverchannels cursor properly parametrized 876019 - schema upgrade scripts Automatic commit of package [spacewalk-schema] release [2.1.1-1]. --- rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/oracle/packages/rhn_entitlements.pkb | 20 schema/spacewalk/postgres/packages/rhn_entitlements.pkb | 27 schema/spacewalk/spacewalk-schema.spec | 7 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.oracle | 1777 +++++++++ schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql | 1787 ++++++++++ 6 files changed, 3587 insertions(+), 33 deletions(-) ---

1 0

5 commits - rel-eng/packages schema/spacewalk
by Michael Mraka 23 Jul '13

23 Jul '13

rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/oracle/packages/rhn_entitlements.pkb | 20 schema/spacewalk/postgres/packages/rhn_entitlements.pkb | 27 schema/spacewalk/spacewalk-schema.spec | 7 schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.oracle | 1777 +++++++++ schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql | 1787 ++++++++++ 6 files changed, 3587 insertions(+), 33 deletions(-) New commits: commit fc358c3218b5e02a1ce812eddba280c9e1a3c180 Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Tue Jul 23 16:54:50 2013 +0200 Automatic commit of package [spacewalk-schema] release [2.1.1-1]. diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index 7430cd0..1a3cde3 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -2.0.2-1 schema/spacewalk/ +2.1.1-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index ba69023..f8ade67 100644 --- a/schema/spacewalk/spacewalk-schema.spec +++ b/schema/spacewalk/spacewalk-schema.spec @@ -4,7 +4,7 @@ Name: spacewalk-schema Group: Applications/Internet Summary: Oracle SQL schema for Spacewalk server -Version: 2.1.0 +Version: 2.1.1 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz @@ -71,6 +71,11 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql* %changelog +* Tue Jul 23 2013 Michael Mraka <michael.mraka(a)redhat.com> 2.1.1-1 +- 876019 - schema upgrade scripts +- made serverchannels cursor properly parametrized +- 876019 - fixed misplaced end loop + * Thu Jul 18 2013 Tomas Kasparek <tkasparek(a)redhat.com> 2.0.2-1 - fixing oracle specific sha1 - add upgrade path from 1.10 to 2.0 commit c0e5e8e1d7e69c2e843549d9b6ca4d18c818c93d Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Tue Jul 23 16:51:19 2013 +0200 876019 - schema upgrade scripts diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb index aa2b30e..0ccc4f1 100644 --- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 dae6f07736360c86d247fbb1e40d2ae90d16e830 +-- oracle equivalent source sha1 b34e5effdc23dd2bc7bb9c805c71a0396d947848 -- -- Copyright (c) 2008--2012 Red Hat, Inc. -- diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.oracle b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.oracle new file mode 100644 index 0000000..d694b34 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.oracle @@ -0,0 +1,1777 @@ +-- +-- Copyright (c) 2008--2012 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- implied, including the implied warranties of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +-- along with this software; if not, see +-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +-- +-- Red Hat trademarks are not licensed under GPLv2. No permission is +-- granted to use or replicate Red Hat trademarks that are incorporated +-- in this software or its documentation. +-- +-- +-- +-- + +create or replace +package body rhn_entitlements +is + body_version varchar2(100) := ''; + + function find_compatible_sg ( + server_id_in in number, + type_label_in in varchar2 + ) return number is + + cursor servergroups is + select sg.id id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerServerGroupArchCompat ssgac + where s.id = server_id_in + and s.org_id = sg.org_id + and sgt.label = type_label_in + and sg.group_type = sgt.id + and ssgac.server_group_type = sgt.id + and ssgac.server_arch_id = s.server_arch_id + and not exists ( + select 1 + from rhnServerGroupMembers sgm + where sgm.server_group_id = sg.id + and sgm.server_id = s.id); + + + begin + for servergroup in servergroups loop + return servergroup.id; + end loop; + + --no servergroup found + return null; + end find_compatible_sg; + + + -- ******************************************************************* + -- PROCEDURE: remove_org_entitlements + -- + -- Removes both system entitlements and channel subscriptions + -- that are currently assigned to an org and re-assigns to the + -- master org (org_id = 1). + -- + -- When we call this we expect everything to already be unentitled + -- which shoul be handled by delete_org. + -- + -- Called by: delete_org + -- ******************************************************************* + procedure remove_org_entitlements( + org_id_in in number + ) + is + + cursor system_ents is + select sg.id, sg.max_members, sg.group_type + from rhnServerGroup sg + where group_type is not null + and org_id = org_id_in; + + cursor channel_subs is + select pcf.channel_family_id, pcf.max_members + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.org_id is null; + + begin + + for system_ent in system_ents loop + update rhnServerGroup + set max_members = max_members + system_ent.max_members + where org_id = 1 + and group_type = system_ent.group_type; + end loop; + + update rhnServerGroup + set max_members = 0 + where org_id = org_id_in; + + for channel_sub in channel_subs loop + update rhnPrivateChannelFamily + set max_members = max_members + channel_sub.max_members + where org_id = 1 + and channel_family_id = channel_sub.channel_family_id; + end loop; + + update rhnPrivateChannelFamily + set max_members = 0 + where org_id = org_id_in; + + end remove_org_entitlements; + + function entitlement_grants_service ( + entitlement_in in varchar2, + service_level_in in varchar2 + ) return number is + begin + if service_level_in = 'provisioning' then + if entitlement_in = 'provisioning_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'management' then + if entitlement_in = 'enterprise_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'monitoring' then + if entitlement_in = 'monitoring_entitled' then + return 1; + end if; + elsif service_level_in = 'updates' then + return 1; + else + return 0; + end if; + end entitlement_grants_service; + + function lookup_entitlement_group ( + org_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled' + ) return number is + cursor server_groups is + select sg.id server_group_id + from rhnServerGroup sg, + rhnServerGroupType sgt + where sgt.label = type_label_in + and sgt.id = sg.group_type + and sg.org_id = org_id_in; + begin + for sg in server_groups loop + return sg.server_group_id; + end loop; + return rhn_entitlements.create_entitlement_group( + org_id_in, + type_label_in + ); + end lookup_entitlement_group; + + function create_entitlement_group ( + org_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled' + ) return number is + sg_id_val number; + begin + select rhn_server_group_id_seq.nextval + into sg_id_val + from dual; + + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id + ) ( + select sg_id_val, sgt.label, sgt.label, + 0, 0, sgt.id, org_id_in + from rhnServerGroupType sgt + where sgt.label = type_label_in + ); + + return sg_id_val; + end create_entitlement_group; + + function can_entitle_server ( + server_id_in in number, + type_label_in in varchar2 ) + return number is + cursor addon_servergroups (base_label_in in varchar2, + addon_label_in in varchar2) is + select + addon_id + from + rhnSGTypeBaseAddonCompat + where base_id = lookup_sg_type (base_label_in) + and addon_id = lookup_sg_type (addon_label_in); + + previous_ent rhn_entitlements.ents_array; + is_base_in char := 'N'; + is_base_current char := 'N'; + i number := 0; + sgid number := 0; + + begin + + previous_ent := rhn_entitlements.ents_array(); + previous_ent := rhn_entitlements.get_server_entitlement(server_id_in); + + select distinct is_base + into is_base_in + from rhnServerGroupType + where label = type_label_in; + + if previous_ent.count = 0 then + sgid := find_compatible_sg (server_id_in, type_label_in); + if (is_base_in = 'Y' and sgid is not null) then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- there are previous ents, first make sure we're not trying to entitle a base ent + elsif is_base_in = 'Y' then + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + + -- it must be an addon, so proceed with the entitlement + else + + -- find the servers base ent + is_base_current := 'N'; + i := 0; + while is_base_current = 'N' and i <= previous_ent.count + loop + i := i + 1; + select is_base + into is_base_current + from rhnServerGroupType + where label = previous_ent(i); + end loop; + + -- never found a base ent, that would be strange + if is_base_current = 'N' then + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- this for loop verifies the validity of the addon path + for addon_servergroup in addon_servergroups (previous_ent(i), type_label_in) loop + -- find an appropriate sgid for the addon and entitle the server + sgid := find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + end if; + end loop; + + end if; + + return 0; + + end can_entitle_server; + + function can_switch_base ( + server_id_in in integer, + type_label_in in varchar2 + ) return number is + + type_label_in_is_base char(1); + sgid number; + + begin + + begin + select is_base into type_label_in_is_base + from rhnServerGroupType + where label = type_label_in; + exception + when no_data_found then + rhn_exception.raise_exception ( 'invalid_entitlement' ); + end; + + if type_label_in_is_base = 'N' then + rhn_exception.raise_exception ( 'invalid_entitlement' ); + else + sgid := find_compatible_sg ( server_id_in, type_label_in ); + if sgid is not null then + return 1; + else + return 0; + end if; + end if; + + end can_switch_base; + + + procedure entitle_server ( + server_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled' + ) is + sgid number := 0; + is_virt number := 0; + + begin + + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + if is_virt = 0 and (type_label_in = 'virtualization_host' or + type_label_in = 'virtualization_host_platform') then + + is_virt := 1; + end if; + + + + if rhn_entitlements.can_entitle_server(server_id_in, + type_label_in) = 1 then + sgid := find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( rhn_event_id_seq.nextval, server_id_in, + 'added system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + rhn_server.insert_into_servergroup (server_id_in, sgid); + + if is_virt = 1 then + rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + else + rhn_exception.raise_exception ('no_available_server_group'); + end if; + else + rhn_exception.raise_exception ('invalid_entitlement'); + end if; + end entitle_server; + + procedure remove_server_entitlement ( + server_id_in in number, + type_label_in in varchar2 := 'sw_mgr_entitled', + repoll_virt_guests in number := 1 + ) is + group_id number; + type_is_base char; + is_virt number := 0; + begin + begin + + + -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones... + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + select sg.id, sgt.is_base + into group_id, type_is_base + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnServer s + where s.id = server_id_in + and s.id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.org_id = s.org_id + and sgt.label = type_label_in + and sgt.id = sg.group_type; + + if ( type_is_base = 'Y' ) then + -- unentitle_server should handle everything, don't really need to do anything else special here + unentitle_server ( server_id_in ); + else + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( rhn_event_id_seq.nextval, server_id_in, + 'removed system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platforrm' end ); + + rhn_server.delete_from_servergroup(server_id_in, group_id); + + if type_label_in = 'monitoring_entitled' then + DELETE + FROM rhn_probe + WHERE recid IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + end if; + + if is_virt = 1 and repoll_virt_guests = 1 then + rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + end if; + + exception + when no_data_found then + rhn_exception.raise_exception('invalid_server_group_member'); + end; + + end remove_server_entitlement; + + + procedure unentitle_server (server_id_in in number) is + + cursor servergroups is + select distinct sgt.label, sg.id server_group_id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerGroupMembers sgm + where s.id = server_id_in + and s.org_id = sg.org_id + and sg.group_type = sgt.id + and sgm.server_group_id = sg.id + and sgm.server_id = s.id; + + is_virt number := 0; + + begin + + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + for servergroup in servergroups loop + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( rhn_event_id_seq.nextval, server_id_in, + 'removed system entitlement ', + case servergroup.label + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + rhn_server.delete_from_servergroup(server_id_in, + servergroup.server_group_id ); + end loop; + + if is_virt = 1 then + rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + end unentitle_server; + + + -- ******************************************************************* + -- PROCEDURE: repoll_virt_guest_entitlements + -- + -- Whenever we add/remove a virtualization_host* entitlement from + -- a host, we can call this procedure to update what type of slots + -- the guests are consuming. + -- + -- If you're removing the entitlement, it's + -- possible the guests will become unentitled if you don't have enough + -- physical slots to cover them. + -- + -- If you're adding the entitlement, you end up freeing up physical + -- slots for other systems. + -- + -- ******************************************************************* + procedure repoll_virt_guest_entitlements(server_id_in in number) + is + + -- All channel families associated with the guests of server_id_in + cursor families is + select distinct cfs.channel_family_id + from + rhnChannelFamilyServers cfs, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = cfs.server_id; + + -- All of server group types associated with the guests of + -- server_id_in + cursor group_types is + select distinct sg.group_type, sgt.label + from + rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + + -- Virtual servers from a certain family belonging to a speicifc + -- host that are consuming physical channel slots over the limit. + cursor virt_servers_cfam(family_id_in in number, quantity_in in number) is + select virtual_system_id + from ( + select rownum, vi.virtual_system_id + from + rhnChannelFamilyMembers cfm, + rhnServerChannel sc, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sc.server_id + and sc.channel_id = cfm.channel_id + and cfm.channel_family_id = family_id_in + order by sc.modified desc + ) + where rownum <= quantity_in; + + -- Virtual servers from a certain family belonging to a speicifc + -- host that are consuming physical system slots over the limit. + cursor virt_servers_sgt(group_type_in in number, quantity_in in number) is + select virtual_system_id + from ( + select rownum, vi.virtual_system_id + from + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = group_type_in + order by sgm.modified desc + ) + where rownum <= quantity_in; + -- Get the orgs of Virtual guests + -- Since they may belong to different orgs + cursor virt_guest_orgs is + select distinct (s.org_id) + from rhnServer s + inner join rhnVirtualInstance vi on vi.virtual_system_id = s.id + where + vi.host_system_id = server_id_in + and s.org_id <> (select s1.org_id from rhnServer s1 where s1.id = vi.host_system_id) ; + + + org_id_val number; + max_members_val number; + max_flex_val number; + current_members_calc number; + sg_id number; + is_virt number := 0; + begin + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + select org_id + into org_id_val + from rhnServer + where id = server_id_in; + + -- deal w/ channel entitlements first ... + for family in families loop + if is_virt = 0 then + -- if the host_server does not have virt + --- find all possible flex slots + -- and set each of the flex eligible guests to Y + UPDATE rhnServerChannel sc set sc.is_fve = 'Y' + where sc.server_id in ( + select virtual_system_id from ( + select rownum, vi.virtual_system_id, sfc.max_members - sfc.current_members as free_slots + from rhnServerFveCapable sfc + inner join rhnVirtualInstance vi on vi.virtual_system_id = sfc.server_id + where vi.host_system_id = server_id_in + and sfc.channel_family_id = family.channel_family_id + order by vi.modified desc + ) + where rownum <= free_slots + ); + else + -- if the host_server has virt + -- set all its flex guests to N + UPDATE rhnServerChannel sc set sc.is_fve = 'N' + where + sc.channel_id in (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = family.channel_family_id) + and sc.is_fve = 'Y' + and sc.server_id in + (select vi.virtual_system_id from rhnVirtualInstance vi + where vi.host_system_id = server_id_in); + end if; + + -- get the current (physical) members of the family + current_members_calc := + rhn_channel.channel_family_current_members(family.channel_family_id, + org_id_val); -- fixed transposed args + + begin + + -- get the max members of the family + select max_members + into max_members_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + select fve_max_members + into max_flex_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, so we'll + -- unsubscribe guests from the host first. + + -- hm, i don't think max_members - current_members_calc yielding a negative number + -- will work w/ rownum, swaping 'em in the body of this if... + for virt_server in virt_servers_cfam(family.channel_family_id, + current_members_calc - max_members_val) loop + + rhn_channel.unsubscribe_server_from_family( + virt_server.virtual_system_id, + family.channel_family_id); + end loop; + + -- if we're still over the limit, which would be odd, + -- just prune the group to max_members + -- + -- er... wouldn't we actually have to refresh the values of + -- current_members_calc and max_members_val to actually ever + -- *skip this?? + if current_members_calc > max_members_val then + -- argh, transposed again?! + set_family_count(org_id_val, + family.channel_family_id, + max_members_val, max_flex_val); + --TODO calculate this correctly + end if; + + end if; + + -- update current_members for the family. This will set the value + -- to reflect adding/removing the entitlement. + -- + -- what's the difference of doing this vs the unavoidable set_family_count above? + rhn_channel.update_family_counts(family.channel_family_id, + org_id_val); + + exception when no_data_found then null; + end; + + -- It is possible that the guests belong to a different org than the host + -- so we are going to update the family counts in the guests orgs also + for org in virt_guest_orgs loop + rhn_channel.update_family_counts(family.channel_family_id, + org.org_id); + end loop; + end loop; + + for a_group_type in group_types loop + -- get the current *physical* members of the system entitlement type for the org... + -- + -- unlike channel families, it appears the standard rhnServerGroup.max_members represents + -- *physical* slots, vs physical+virt ... boy that's confusing... + + select max_members, id + into max_members_val, sg_id + from rhnServerGroup + where group_type = a_group_type.group_type + and org_id = org_id_val; + + + select count(sep.server_id) into current_members_calc + from rhnServerEntitlementPhysical sep + where sep.server_group_id = sg_id + and sep.server_group_type_id = a_group_type.group_type; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, and we're over the limit, so unsubscribe guests + for virt_server in virt_servers_sgt(a_group_type.group_type, + current_members_calc - max_members_val) loop + rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label); + + -- decrement current_members_calc, we'll use it to reset current_members for the group at the end... + current_members_calc := current_members_calc - 1; + end loop; + + end if; + + update rhnServerGroup set current_members = current_members_calc + where org_id = org_id_val + and group_type = a_group_type.group_type; + + -- I think that's all the house-keeping we have to do... + end loop; + + end repoll_virt_guest_entitlements; + + + function get_server_entitlement ( + server_id_in in number + ) return ents_array is + + cursor server_groups is + select sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_id = server_id_in + and sg.id = sgm.server_group_id + and sgt.id = sg.group_type + and sgt.label in ( + 'sw_mgr_entitled','enterprise_entitled', + 'provisioning_entitled', 'nonlinux_entitled', + 'monitoring_entitled', 'virtualization_host', + 'virtualization_host_platform' + ); + + ent_array ents_array; + + begin + + ent_array := ents_array(); + + for sg in server_groups loop + ent_array.extend; + ent_array(ent_array.count) := sg.label; + end loop; + + return ent_array; + + end get_server_entitlement; + + + -- this desperately needs to be table driven. + procedure modify_org_service ( + org_id_in in number, + service_label_in in varchar2, + enable_in in char + ) is + type roles_v is varray(10) of rhnUserGroupType.label%TYPE; + roles_to_process roles_v; + cursor roles(role_label_in in varchar2) is + select label, id + from rhnUserGroupType + where label = role_label_in; + cursor org_roles(role_label_in in varchar2) is + select 1 + from rhnUserGroup ug, + rhnUserGroupType ugt + where ugt.label = role_label_in + and ug.org_id = org_id_in + and ugt.id = ug.group_type; + + type ents_v is varray(10) of rhnOrgEntitlementType.label%TYPE; + ents_to_process ents_v; + cursor ents(ent_label_in in varchar2) is + select label, id + from rhnOrgEntitlementType + where label = ent_label_in; + cursor org_ents(ent_label_in in varchar2) is + select 1 + from rhnOrgEntitlements oe, + rhnOrgEntitlementType oet + where oet.label = ent_label_in + and oe.org_id = org_id_in + and oet.id = oe.entitlement_id; + create_row char(1); + begin + ents_to_process := ents_v(); + roles_to_process := roles_v(); + -- a bit kludgy, but only for 3.4 really. Certainly no + -- worse than the old code... + if service_label_in = 'enterprise' or + service_label_in = 'management' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'org_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'system_group_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'activation_key_admin'; + elsif service_label_in = 'provisioning' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_provisioning'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'system_group_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'activation_key_admin'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + -- another nasty special case... + if enable_in = 'Y' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise'; + end if; + elsif service_label_in = 'monitoring' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_monitor'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'monitoring_admin'; + elsif service_label_in = 'virtualization' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_virtualization'; + + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + elsif service_label_in = 'virtualization_platform' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_virtualization_platform'; + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + elsif service_label_in = 'nonlinux' then + ents_to_process.extend; + ents_to_process(ents_to_process.count) := 'rhn_nonlinux'; + roles_to_process.extend; + roles_to_process(roles_to_process.count) := 'config_admin'; + end if; + + if enable_in = 'Y' then + for i in 1..ents_to_process.count loop + for ent in ents(ents_to_process(i)) loop + create_row := 'Y'; + for oe in org_ents(ent.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnOrgEntitlements(org_id, entitlement_id) + values (org_id_in, ent.id); + end if; + end loop; + end loop; + for i in 1..roles_to_process.count loop + for role in roles(roles_to_process(i)) loop + create_row := 'Y'; + for o_r in org_roles(role.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnUserGroup( + id, name, description, current_members, + group_type, org_id + ) ( + select rhn_user_group_id_seq.nextval, + ugt.name || 's', + ugt.name || 's for Org ' || + o.name || ' ('|| o.id ||')', + 0, ugt.id, o.id + from rhnUserGroupType ugt, + web_customer o + where o.id = org_id_in + and ugt.id = role.id + ); + end if; + end loop; + end loop; + else + for i in 1..ents_to_process.count loop + for ent in ents(ents_to_process(i)) loop + delete from rhnOrgEntitlements + where org_id = org_id_in + and entitlement_id = ent.id; + end loop; + end loop; + end if; + end modify_org_service; + + procedure set_customer_enterprise ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'enterprise', 'Y'); + end set_customer_enterprise; + + procedure set_customer_provisioning ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'provisioning', 'Y'); + end set_customer_provisioning; + + procedure set_customer_monitoring ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'monitoring', 'Y'); + end set_customer_monitoring; + + procedure set_customer_nonlinux ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'nonlinux', 'Y'); + end set_customer_nonlinux; + + procedure unset_customer_enterprise ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'enterprise', 'N'); + end unset_customer_enterprise; + + procedure unset_customer_provisioning ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'provisioning', 'N'); + end unset_customer_provisioning; + + procedure unset_customer_monitoring ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'monitoring', 'N'); + end unset_customer_monitoring; + + procedure unset_customer_nonlinux ( + customer_id_in in number + ) is + begin + modify_org_service(customer_id_in, 'nonlinux', 'N'); + end unset_customer_nonlinux; + + -- ******************************************************************* + -- PROCEDURE: prune_group + -- Unsubscribes servers consuming physical slots that over the org's + -- limit. + -- Called by: set_server_group_count, repoll_virt_guest_entitlements + -- ******************************************************************* + procedure prune_group ( + group_id_in in number, + quantity_in in number, + update_family_countsYN in number := 1 + ) is + cursor servergroups is + select server_id, server_group_id, sgt.id as group_type_id, sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_group_id = group_id_in + and sgm.server_id in ( + select server_id + from ( + select rownum row_number, + server_id, + time + from ( + select sep.server_id, + sep.modified time + from + rhnServerEntitlementPhysical sep + where + sep.server_group_id = group_id_in + order by time asc + ) + ) + where row_number > quantity_in + ) + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + type_is_base char; + begin + update rhnServerGroup + set max_members = quantity_in + where id = group_id_in; + + for sg in servergroups loop + remove_server_entitlement(sg.server_id, sg.label); + + select is_base + into type_is_base + from rhnServerGroupType sgt + where sgt.id = sg.group_type_id; + + -- if we're removing a base ent, then be sure to + -- remove the server's channel subscriptions. + if ( type_is_base = 'Y' ) then + rhn_channel.clear_subscriptions(sg.server_id, + update_family_countsYN => update_family_countsYN); + end if; + + end loop; + end prune_group; + + procedure set_server_group_count ( + customer_id_in in number, + group_type_in in number, + quantity_in in number, + update_family_countsYN in number := 1 + ) is + group_id number; + quantity number; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + + 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; + + rhn_entitlements.prune_group( + group_id, + quantity, + update_family_countsYN + ); + exception + when no_data_found then + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select rhn_server_group_id_seq.nextval, name, name, + quantity, 0, id, customer_id_in, + current_timestamp, current_timestamp + from rhnServerGroupType + where id = group_type_in + ); + end set_server_group_count; + + -- ******************************************************************* + -- 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_server_group_count + -- ******************************************************************* + procedure assign_system_entitlement( + group_label_in in varchar2, + from_org_id_in in number, + to_org_id_in in number, + quantity_in in number + ) + is + prev_ent_count number; + to_org_prev_ent_count number; + new_ent_count number; + new_quantity number; + group_type number; + begin + + begin + select max_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = from_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end; + + begin + select max_members + into to_org_prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = to_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count := 0; + end; + + begin + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_server_group'); + end; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if new_ent_count < 0 then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + + set_server_group_count(from_org_id_in, + group_type, + new_ent_count); + + set_server_group_count(to_org_id_in, + group_type, + new_quantity); + + -- Create or delete the entries in rhnOrgEntitlementType + if group_label_in = 'enterprise_entitled' then + if new_quantity > 0 then + set_customer_enterprise(to_org_id_in); + else + unset_customer_enterprise(to_org_id_in); + end if; + end if; + + if group_label_in = 'provisioning_entitled' then + if new_quantity > 0 then + set_customer_provisioning(to_org_id_in); + else + unset_customer_provisioning(to_org_id_in); + end if; + end if; + + if group_label_in = 'monitoring_entitled' then + if new_quantity > 0 then + set_customer_monitoring(to_org_id_in); + else + unset_customer_monitoring(to_org_id_in); + end if; + end if; + + end assign_system_entitlement; + + -- ******************************************************************* + -- PROCEDURE: assign_channel_entitlement + -- + -- Moves channel entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_family_count + -- ******************************************************************* + procedure assign_channel_entitlement( + channel_family_label_in in varchar2, + from_org_id_in in number, + to_org_id_in in number, + quantity_in in number, + flex_in in number + ) + is + from_org_prev_ent_count number; + from_org_prev_ent_count_flex number; + new_ent_count number; + new_ent_count_flex number; + to_org_prev_ent_count number; + to_org_prev_ent_count_flex number; + new_quantity number; + new_flex number; + cfam_id number; + begin + + begin + select max_members + into from_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end; + + begin + select max_members + into to_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count := 0; + end; + + begin + select fve_max_members + into from_org_prev_ent_count_flex + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end; + + begin + select fve_max_members + into to_org_prev_ent_count_flex + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count_flex := 0; + end; + + begin + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_channel_family'); + end; + + new_ent_count := from_org_prev_ent_count - quantity_in; + new_ent_count_flex := from_org_prev_ent_count_flex - flex_in; + + if from_org_prev_ent_count >= new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if from_org_prev_ent_count_flex >= new_ent_count_flex then + new_flex := to_org_prev_ent_count_flex + flex_in; + end if; + + + if new_ent_count < 0 then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + if new_ent_count_flex < 0 then + rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + + + + rhn_entitlements.set_family_count(from_org_id_in, cfam_id, + new_ent_count, new_ent_count_flex); + + rhn_entitlements.set_family_count(to_org_id_in, + cfam_id, + new_quantity, new_flex); + + end assign_channel_entitlement; + + -- ******************************************************************* + -- PROCEDURE: activate_system_entitlement + -- + -- Sets the values in rhnServerGroup for a given rhnServerGroupType. + -- + -- Calls: set_server_group_count to update, prune, or create the group. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if all entitlements + -- in the org are used so the free entitlements would not cover + -- the difference when descreasing the number of entitlements. + -- ******************************************************************* + procedure activate_system_entitlement( + org_id_in in number, + group_label_in in varchar2, + quantity_in in number + ) + is + prev_ent_count number; + prev_ent_count_sum number; + group_type number; + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + begin + select current_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.group_type = sgt.id + and sgt.label = group_label_in + and sg.org_id = org_id_in; + exception + when NO_DATA_FOUND then + prev_ent_count := 0; + end; + + begin + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_server_group'); + end; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the allocated count in this org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- don't update family counts after every server + -- will do bulk update afterwards + set_server_group_count(org_id_in, + group_type, + quantity_in, + update_family_countsYN => 0); + -- bulk update family counts + rhn_channel.update_group_family_counts(group_label_in, org_id_in); + end if; + + + end activate_system_entitlement; + + -- ******************************************************************* + -- PROCEDURE: activate_channel_entitlement + -- + -- Calls: set_family_count to update, prune, or create the family + -- permission bucket. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if there are not enough + -- entitlements in the org to cover the difference when you are + -- descreasing the number of entitlements. + -- + -- The backend code in Python is expected to do whatever arithmetics + -- is needed. + -- ******************************************************************* + procedure activate_channel_entitlement( + org_id_in in number, + channel_family_label_in in varchar2, + quantity_in in number, + flex_in in number + + ) + is + prev_ent_count number; + prev_flex_count number; + prev_ent_count_sum number; + cfam_id number; + reduce_quantity number; + total_flex_capable number; + cursor to_convert_reg (channel_family_id_val in number, org_id_in in number, quantity in number) + is + select SC.server_id + from rhnServerChannel SC inner join + rhnServer S on S.id = SC.server_id + where + is_fve = 'Y' + and S.org_id = org_id_in + and SC.channel_id in + (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = channel_family_id_val) + and rownum <= quantity; + cursor to_convert_flex (channel_family_id_val in number, org_id_in in number, quantity in number) + is + select server_id + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id and + rownum <= quantity; + + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + begin + select current_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + prev_ent_count := 0; + end; + + begin + select pcf.fve_current_members + into prev_flex_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + prev_flex_count := 0; + end; + + + begin + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + exception + when NO_DATA_FOUND then + rhn_exception.raise_exception( + 'invalid_channel_family'); + end; + + -- if there are too few flex entitlements + if flex_in < prev_flex_count then + -- and if the extra we need is less than or equal to our extra regular entitlements + if prev_flex_count - flex_in <= quantity_in - prev_ent_count then + reduce_quantity := prev_flex_count - flex_in; + -- We need to convert some systems from flex guest to regular + for system in to_convert_reg(cfam_id, org_id_in, reduce_quantity) loop + --rhn_channel.convert_to_regular(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set sc.is_fve = 'N' + where sc.server_id = system.server_id + and sc.channel_id in (select cfm.channel_id + from rhnChannelFamilyMembers cfm + where cfm.channel_family_id = cfam_id); + end loop; + + --reset previous counts + prev_ent_count := prev_ent_count + reduce_quantity; + prev_flex_count := prev_flex_count - reduce_quantity; + else + rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + end if; + + -- if there are too few regular entitlements, and extra flex entitlements + if quantity_in < prev_ent_count and prev_flex_count < flex_in then + -- how many flex-capable systems (that aren't using flex) do we have + select count(*) + into total_flex_capable + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id; + -- if we have enough flex capable machines that is at least + -- as many as what we are over on + if total_flex_capable >= prev_ent_count - quantity_in then + reduce_quantity := prev_ent_count - quantity_in; + for system in to_convert_flex(cfam_id, org_id_in, reduce_quantity) loop +-- rhn_channel.convert_to_fve(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set sc.is_fve = 'Y' + where sc.server_id = system.server_id + and sc.channel_id in (select cfm.channel_id + from rhnChannelFamilyMembers cfm + where cfm.channel_family_id = cfam_id); + end loop; + prev_ent_count := prev_ent_count - reduce_quantity; + prev_flex_count := prev_flex_count + reduce_quantity; + end if; + end if; + + + + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the count in that one org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- even if we've manually converted systems to/from flex above + -- set_family_count should call update_family_counts, to reset + -- current used slots + rhn_entitlements.set_family_count(org_id_in, cfam_id, + quantity_in, flex_in); + end if; + + end activate_channel_entitlement; + + + -- ******************************************************************* + -- PROCEDURE: prune_family + -- Unsubscribes servers consuming physical slots from the channel family + -- that are over the org's limit. + -- Called by: set_family_count + -- ******************************************************************* + procedure prune_family ( + customer_id_in in number, + channel_family_id_in in number, + quantity_in in number, + flex_in in number + ) is + cursor serverchannels(tmp_quantity number, is_fve_in char) is + select sc.server_id, + sc.channel_id + from rhnServerChannel sc, + rhnChannelFamilyMembers cfm + where 1=1 + and cfm.channel_family_id = channel_family_id_in + and cfm.channel_id = sc.channel_id + and server_id in ( + select server_id + from ( + select server_id, time, rownum row_number + from ( + select rs.id server_id, rcfm.modified time + from rhnServerChannel rsc, + rhnChannelFamilyMembers rcfm, + rhnServer rs + where 1=1 + and rs.org_id = customer_id_in + and rs.id = rsc.server_id + and rsc.channel_id = rcfm.channel_id + and rcfm.channel_family_id = channel_family_id_in + and rsc.is_fve = is_fve_in + -- we only want to grab servers consuming + -- physical slots. + and exists ( + select 1 + from rhnChannelFamilyServerPhysical cfsp + where cfsp.server_id = rs.id + and cfsp.channel_family_id = + channel_family_id_in + ) + order by time asc + ) + ) + where row_number > tmp_quantity + ); + begin + -- if we get a null customer_id, this is completely bogus. + if customer_id_in is null then + return; + end if; + + for sc in serverchannels(quantity_in, 'N') loop + rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, + update_family_countsYN => 0); + end loop; + + for sc in serverchannels(flex_in, 'Y') loop + rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, + update_family_countsYN => 0); + end loop; + + rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); + end prune_family; + + procedure set_family_count ( + customer_id_in in number, + channel_family_id_in in number, + quantity_in in number, + flex_in in number + ) is + cursor privperms is + select 1 + from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + cursor pubperms is + select o.id org_id + from web_customer o, + rhnPublicChannelFamily pcf + where pcf.channel_family_id = channel_family_id_in; + quantity number; + done number := 0; + flex number; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + flex := flex_in; + if flex is not null and flex < 0 then + flex := 0; + end if; + + + if customer_id_in is not null then + for perm in privperms loop + rhn_entitlements.prune_family(customer_id_in, channel_family_id_in, + quantity, flex); + + if quantity is null and flex is null then + delete from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + else + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + update rhnPrivateChannelFamily + set fve_max_members = flex + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + end if; + return; + end loop; + + insert into rhnPrivateChannelFamily ( + channel_family_id, org_id, max_members, current_members, fve_max_members, fve_current_members + ) values ( + channel_family_id_in, customer_id_in, quantity, 0, flex, 0 ); + return; + end if; + + for perm in pubperms loop + if quantity = 0 then + rhn_entitlements.prune_family( + perm.org_id, + channel_family_id_in, + quantity, + flex + ); + if done = 0 then + delete from rhnPublicChannelFamily + where channel_family_id = channel_family_id_in; + end if; + end if; + done := 1; + end loop; + -- if done's not 1, then we don't have any entitlements + if done != 1 then + insert into rhnPublicChannelFamily ( + channel_family_id + ) values ( + channel_family_id_in + ); + end if; + end set_family_count; + + -- this expects quantity_in to be the number of available slots, not the + -- max_members of the server group. If you give it too many, it'll fail + -- and raise servergroup_max_members. + -- We should NEVER run this unless we're SURE that we won't + -- be violating the max. + procedure entitle_last_modified_servers ( + customer_id_in in number, + type_label_in in varchar2, + quantity_in in number + ) is + -- find the servers that aren't currently in slots + cursor servers(cid_in in number, quant_in in number) is + select server_id + from ( + select rownum row_number, + server_id + from ( + select rs.id server_iD + from rhnServer rs + where 1=1 + and rs.org_id = cid_in + and not exists ( + select 1 + from rhnServerGroup sg, + rhnServerGroupMembers rsgm + where rsgm.server_id = rs.id + and rsgm.server_group_id = sg.id + and sg.group_type is not null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = + rs.id + ) order by modified desc + ) + ) + where row_number <= quant_in; + begin + for server in servers(customer_id_in, quantity_in) loop + rhn_entitlements.entitle_server(server.server_id, type_label_in); + end loop; + end entitle_last_modified_servers; + + procedure subscribe_newest_servers ( + customer_id_in in number + ) is + -- find servers without base channels + cursor servers(cid_in in number) is + select s.id + from rhnServer s + where 1=1 + and s.org_id = cid_in + and not exists ( + select 1 + from rhnChannel c, + rhnServerChannel sc + where sc.server_id = s.id + and sc.channel_id = c.id + and c.parent_channel is null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = s.id + ) + order by s.modified desc; + channel_id number; + begin + for server in servers(customer_id_in) loop + channel_id := rhn_channel.guess_server_base(server.id); + if channel_id is not null then + begin + rhn_channel.subscribe_server(server.id, channel_id); + commit; + -- exception is really channel_family_no_subscriptions + exception + when others then + null; + end; + end if; + end loop; + end subscribe_newest_servers; +end rhn_entitlements; +/ +show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql new file mode 100644 index 0000000..c02ee78 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/010-rhn_entitlements.pkb.postgresql @@ -0,0 +1,1787 @@ +-- oracle equivalent source sha1 b34e5effdc23dd2bc7bb9c805c71a0396d947848 +-- +-- Copyright (c) 2008--2012 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- implied, including the implied warranties of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +-- along with this software; if not, see +-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +-- +-- Red Hat trademarks are not licensed under GPLv2. No permission is +-- granted to use or replicate Red Hat trademarks that are incorporated +-- in this software or its documentation. +-- + +-- create schema rhn_entitlements; + +-- setup search_path so that these functions are created in appropriate schema. +update pg_settings set setting = 'rhn_entitlements,' || setting where name = 'search_path'; + + create or replace function find_compatible_sg ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ + declare + servergroups cursor for + select sg.id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerServerGroupArchCompat ssgac + where s.id = server_id_in + and s.org_id = sg.org_id + and sgt.label = type_label_in + and sg.group_type = sgt.id + and ssgac.server_group_type = sgt.id + and ssgac.server_arch_id = s.server_arch_id + and not exists ( + select 1 + from rhnServerGroupMembers sgm + where sgm.server_group_id = sg.id + and sgm.server_id = s.id); + + + begin + for servergroup in servergroups loop + return servergroup.id; + end loop; + + --no servergroup found + return NULL; + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: remove_org_entitlements + -- + -- Removes both system entitlements and channel subscriptions + -- that are currently assigned to an org and re-assigns to the + -- master org (org_id = 1). + -- + -- When we call this we expect everything to already be unentitled + -- which shoul be handled by delete_org. + -- + -- Called by: delete_org + -- ******************************************************************* + create or replace function remove_org_entitlements ( + org_id_in numeric + ) returns void +as $$ + declare + system_ents cursor for + select sg.id, sg.max_members, sg.group_type + from rhnServerGroup sg + where group_type is not null + and org_id = org_id_in; + + channel_subs cursor for + select pcf.channel_family_id, pcf.max_members + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.org_id is null; + + begin + + for system_ent in system_ents loop + update rhnServerGroup + set max_members = max_members + system_ent.max_members + where org_id = 1 + and group_type = system_ent.group_type; + end loop; + + update rhnServerGroup + set max_members = 0 + where org_id = org_id_in; + + for channel_sub in channel_subs loop + update rhnPrivateChannelFamily + set max_members = max_members + channel_sub.max_members + where org_id = 1 + and channel_family_id = channel_sub.channel_family_id; + end loop; + + update rhnPrivateChannelFamily + set max_members = 0 + where org_id = org_id_in; + + end$$ +language plpgsql; + + create or replace function entitlement_grants_service ( + entitlement_in in varchar, + service_level_in in varchar + ) returns numeric +as $$ + begin + if service_level_in = 'provisioning' then + if entitlement_in = 'provisioning_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'management' then + if entitlement_in = 'enterprise_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'monitoring' then + if entitlement_in = 'monitoring_entitled' then + return 1; + end if; + elsif service_level_in = 'updates' then + return 1; + else + return 0; + end if; + end$$ +language plpgsql; + + create or replace function lookup_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ + declare + server_groups cursor for + select sg.id server_group_id + from rhnServerGroup sg, + rhnServerGroupType sgt + where sgt.label = type_label_in + and sgt.id = sg.group_type + and sg.org_id = org_id_in; + begin + for sg in server_groups loop + return sg.server_group_id; + end loop; + return rhn_entitlements.create_entitlement_group( + org_id_in, + type_label_in + ); + end$$ +language plpgsql; + + create or replace function create_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ + declare + sg_id_val numeric; + begin + select nextval('rhn_server_group_id_seq') + into sg_id_val; + + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id + ) ( + select sg_id_val, sgt.label, sgt.label, + 0, 0, sgt.id, org_id_in + from rhnServerGroupType sgt + where sgt.label = type_label_in + ); + + return sg_id_val; + end$$ +language plpgsql; + + create or replace function can_entitle_server ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ + declare + addon_servergroups cursor (base_label_in varchar, + addon_label_in varchar) for + select + addon_id + from + rhnSGTypeBaseAddonCompat + where base_id = lookup_sg_type (base_label_in) + and addon_id = lookup_sg_type (addon_label_in); + + previous_ent varchar[]; + is_base_in char := 'N'; + is_base_current char := 'N'; + i numeric := 0; + sgid numeric := 0; + + begin + + previous_ent := rhn_entitlements.get_server_entitlement(server_id_in); + + select distinct is_base + into is_base_in + from rhnServerGroupType + where label = type_label_in; + + if array_upper(previous_ent, 1) is null or array_upper(previous_ent, 1) = 0 then + if is_base_in = 'Y' then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- there are previous ents, first make sure we're not trying to entitle a base ent + elsif is_base_in = 'Y' then + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + + -- it must be an addon, so proceed with the entitlement + else + + -- find the servers base ent + is_base_current := 'N'; + i := 0; + while is_base_current = 'N' and i < array_upper(previous_ent, 1) + loop + i := i + 1; + select is_base + into is_base_current + from rhnServerGroupType + where label = previous_ent[i]; + end loop; + + -- never found a base ent, that would be strange + if is_base_current = 'N' then + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- this for loop verifies the validity of the addon path + for addon_servergroup in addon_servergroups (previous_ent[i], type_label_in) loop + -- find an appropriate sgid for the addon and entitle the server + sgid := rhn_entitlements.find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + end if; + end loop; + + end if; + + return 0; + + end$$ +language plpgsql; + + create or replace function can_switch_base ( + server_id_in in integer, + type_label_in in varchar + ) + returns numeric +as $$ + declare + type_label_in_is_base char(1); + sgid numeric; + + begin + + select is_base into type_label_in_is_base + from rhnServerGroupType + where label = type_label_in; + + if not found then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + end if; + + if type_label_in_is_base = 'N' then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + else + sgid := rhn_entitlements.find_compatible_sg ( server_id_in, + type_label_in ); + if sgid is not null then + return 1; + else + return 0; + end if; + end if; + + end$$ +language plpgsql; + + create or replace function entitle_server ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns void +as $$ + declare + sgid numeric := 0; + is_virt numeric := 0; + + begin + + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then + is_virt := 0; + end if; + + if is_virt = 0 and (type_label_in = 'virtualization_host' or + type_label_in = 'virtualization_host_platform') then + + is_virt := 1; + end if; + + if rhn_entitlements.can_entitle_server(server_id_in, + type_label_in) = 1 then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, + type_label_in); + if sgid is not null then + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'added system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + perform rhn_server.insert_into_servergroup (server_id_in, sgid); + + if is_virt = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + else + perform rhn_exception.raise_exception ('no_available_server_group'); + end if; + else + perform rhn_exception.raise_exception ('invalid_entitlement'); + end if; + end$$ +language plpgsql; + + create or replace function remove_server_entitlement ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled', + repoll_virt_guests in numeric default 1 + ) returns void +as $$ + declare + group_id numeric; + type_is_base char; + is_virt numeric := 0; + begin + -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones... + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + if not found then + is_virt := 0; + end if; + + select sg.id, sgt.is_base + into group_id, type_is_base + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnServer s + where s.id = server_id_in + and s.id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.org_id = s.org_id + and sgt.label = type_label_in + and sgt.id = sg.group_type; + + if not found then + perform rhn_exception.raise_exception('invalid_server_group_member'); + end if; + + if ( type_is_base = 'Y' ) then + -- unentitle_server should handle everything, don't really need to do anything else special here + perform rhn_entitlements.unentitle_server ( server_id_in ); + else + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'removed system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platforrm' end ); + + perform rhn_server.delete_from_servergroup(server_id_in, group_id); + + if type_label_in = 'monitoring_entitled' then + DELETE + FROM rhn_probe + WHERE recid IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + end if; + + if is_virt = 1 and repoll_virt_guests = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + end if; + + end$$ +language plpgsql; + + create or replace function unentitle_server ( + server_id_in in numeric + ) returns void +as $$ + declare + servergroups cursor for + select distinct sgt.label, sg.id server_group_id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerGroupMembers sgm + where s.id = server_id_in + and s.org_id = sg.org_id + and sg.group_type = sgt.id + and sgm.server_group_id = sg.id + and sgm.server_id = s.id; + + is_virt numeric := 0; + + begin + + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then + is_virt := 0; + end if; + + for servergroup in servergroups loop + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'removed system entitlement ', + case servergroup.label + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + perform rhn_server.delete_from_servergroup(server_id_in, + servergroup.server_group_id ); + end loop; + + if is_virt = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + end$$ +language plpgsql; + + + -- ******************************************************************* + -- PROCEDURE: repoll_virt_guest_entitlements + -- + -- Whenever we add/remove a virtualization_host* entitlement from + -- a host, we can call this procedure to update what type of slots + -- the guests are consuming. + -- + -- If you're removing the entitlement, it's + -- possible the guests will become unentitled if you don't have enough + -- physical slots to cover them. + -- + -- If you're adding the entitlement, you end up freeing up physical + -- slots for other systems. + -- + -- ******************************************************************* + create or replace function repoll_virt_guest_entitlements( + server_id_in in numeric + ) returns void +as $$ + declare + -- All channel families associated with the guests of server_id_in + families cursor for + select distinct cfs.channel_family_id + from + rhnChannelFamilyServers cfs, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = cfs.server_id; + + -- All of server group types associated with the guests of + -- server_id_in + group_types cursor for + select distinct sg.group_type, sgt.label + from + rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + + -- Virtual servers from a certain family belonging to a specific + -- host that are consuming physical channel slots over the limit. + virt_servers_cfam cursor(family_id_in numeric, quantity_in numeric) for + select vi.virtual_system_id + from + rhnChannelFamilyMembers cfm, + rhnServerChannel sc, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sc.server_id + and sc.channel_id = cfm.channel_id + and cfm.channel_family_id = family_id_in + order by sc.modified desc + limit quantity_in; + + -- Virtual servers from a certain family belonging to a specific + -- host that are consuming physical system slots over the limit. + virt_servers_sgt cursor(group_type_in numeric, quantity_in numeric) for + select vi.virtual_system_id + from + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = group_type_in + order by sgm.modified desc + limit quantity_in; + + -- Get the orgs of Virtual guests + -- Since they may belong to different orgs + virt_guest_orgs cursor for + select distinct (s.org_id) + from rhnServer s + inner join rhnVirtualInstance vi on vi.virtual_system_id = s.id + where + vi.host_system_id = server_id_in + and s.org_id <> (select s1.org_id from rhnServer s1 where s1.id = vi.host_system_id) ; + + org_id_val numeric; + max_members_val numeric; + max_flex_val numeric; + current_members_calc numeric; + sg_id numeric; + is_virt numeric := 0; + free_slots numeric := 0; + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then + is_virt := 0; + end if; + + select org_id + into org_id_val + from rhnServer + where id = server_id_in; + + -- deal w/ channel entitlements first ... + for family in families loop + if is_virt = 0 then + -- if the host_server does not have virt + --- find all possible flex slots + -- and set each of the flex eligible guests to Y + select sfc.max_members - sfc.current_members + into free_slots + from rhnServerFveCapable sfc + where sfc.channel_family_id = family.channel_family_id; + UPDATE rhnServerChannel sc set is_fve = 'Y' + where sc.server_id in ( + select vi.virtual_system_id + from rhnServerFveCapable sfc + inner join rhnVirtualInstance vi on vi.virtual_system_id = sfc.server_id + where vi.host_system_id = server_id_in + and sfc.channel_family_id = family.channel_family_id + order by vi.modified desc + limit free_slots + ); + else + -- if the host_server has virt + -- set all its flex guests to N + UPDATE rhnServerChannel sc set is_fve = 'N' + where + sc.channel_id in (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = family.channel_family_id) + and sc.is_fve = 'Y' + and sc.server_id in + (select vi.virtual_system_id from rhnVirtualInstance vi + where vi.host_system_id = server_id_in); + end if; + + -- get the current (physical) members of the family + current_members_calc := + rhn_channel.channel_family_current_members(family.channel_family_id, + org_id_val); -- fixed transposed args + + -- get the max members of the family + select max_members + into max_members_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + select fve_max_members + into max_flex_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, so we'll + -- unsubscribe guests from the host first. + + -- hm, i don't think max_members - current_members_calc yielding a negative number + -- will work w/ rownum, swaping 'em in the body of this if... + for virt_server in virt_servers_cfam(family.channel_family_id, + current_members_calc - max_members_val) loop + + 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, max_flex_val); + --TODO calculate this correctly + end if; + + end if; + + -- update current_members for the family. This will set the value + -- to reflect adding/removing the entitlement. + -- + -- what's the difference of doing this vs the unavoidable set_family_count above? + perform rhn_channel.update_family_counts(family.channel_family_id, + org_id_val); + + -- It is possible that the guests belong to a different org than the host + -- so we are going to update the family counts in the guests orgs also + for org in virt_guest_orgs loop + perform rhn_channel.update_family_counts(family.channel_family_id, + org.org_id); + end loop; + end loop; + + for a_group_type in group_types loop + -- get the current *physical* members of the system entitlement type for the org... + -- + -- unlike channel families, it appears the standard rhnServerGroup.max_members represents + -- *physical* slots, vs physical+virt ... boy that's confusing... + + select max_members, id + into max_members_val, sg_id + from rhnServerGroup + where group_type = a_group_type.group_type + and org_id = org_id_val; + + + select count(sep.server_id) into current_members_calc + from rhnServerEntitlementPhysical sep + where sep.server_group_id = sg_id + and sep.server_group_type_id = a_group_type.group_type; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, and we're over the limit, so unsubscribe guests + for virt_server in virt_servers_sgt(a_group_type.group_type, + current_members_calc - max_members_val) loop + perform rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label); + + -- decrement current_members_calc, we'll use it to reset current_members for the group at the end... + current_members_calc := current_members_calc - 1; + end loop; + + end if; + + update rhnServerGroup set current_members = current_members_calc + where org_id = org_id_val + and group_type = a_group_type.group_type; + + -- I think that's all the house-keeping we have to do... + end loop; + + end$$ +language plpgsql; + + create or replace function get_server_entitlement ( + server_id_in in numeric + ) returns varchar[] +as $$ + declare + server_groups cursor for + select sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_id = server_id_in + and sg.id = sgm.server_group_id + and sgt.id = sg.group_type + and sgt.label in ( + 'sw_mgr_entitled','enterprise_entitled', + 'provisioning_entitled', 'nonlinux_entitled', + 'monitoring_entitled', 'virtualization_host', + 'virtualization_host_platform' + ); + + ent_array varchar[]; + + begin + + ent_array := '{}'; + + for sg in server_groups loop + ent_array := ent_array || sg.label; + end loop; + + return ent_array; + + end$$ +language plpgsql; + + -- this desperately needs to be table driven. + create or replace function modify_org_service ( + org_id_in in numeric, + service_label_in in varchar, + enable_in in char + ) returns void +as $$ + declare + roles_to_process varchar[]; + roles cursor(role_label_in varchar) for + select label, id + from rhnUserGroupType + where label = role_label_in; + org_roles cursor(role_label_in varchar) for + select 1 + from rhnUserGroup ug, + rhnUserGroupType ugt + where ugt.label = role_label_in + and ug.org_id = org_id_in + and ugt.id = ug.group_type; + + ents_to_process varchar[]; + ents cursor(ent_label_in varchar) for + select label, id + from rhnOrgEntitlementType + where label = ent_label_in; + org_ents cursor(ent_label_in varchar) for + select 1 + from rhnOrgEntitlements oe, + rhnOrgEntitlementType oet + where oet.label = ent_label_in + and oe.org_id = org_id_in + and oet.id = oe.entitlement_id; + create_row char(1); + begin + ents_to_process := '{}'; + roles_to_process := '{}'; + -- a bit kludgy, but only for 3.4 really. Certainly no + -- worse than the old code... + if service_label_in = 'enterprise' or + service_label_in = 'management' then + ents_to_process := array_append(ents_to_process, 'sw_mgr_enterprise'); + + roles_to_process := array_append(roles_to_process, 'org_admin'); + + roles_to_process := array_append(roles_to_process, 'system_group_admin'); + + roles_to_process := array_append(roles_to_process, 'activation_key_admin'); + elsif service_label_in = 'provisioning' then + ents_to_process := array_append(ents_to_process, 'rhn_provisioning'); + + roles_to_process := array_append(roles_to_process, 'system_group_admin'); + + roles_to_process := array_append(roles_to_process, 'activation_key_admin'); + + roles_to_process := array_append(roles_to_process, 'config_admin'); + -- another nasty special case... + if enable_in = 'Y' then + ents_to_process := array_append(ents_to_process, 'sw_mgr_enterprise'); + end if; + elsif service_label_in = 'monitoring' then + ents_to_process := array_append(ents_to_process, 'rhn_monitor'); + + roles_to_process := array_append(roles_to_process, 'monitoring_admin'); + elsif service_label_in = 'virtualization' then + ents_to_process := array_append(ents_to_process, 'rhn_virtualization'); + + roles_to_process := array_append(roles_to_process, 'config_admin'); + elsif service_label_in = 'virtualization_platform' then + ents_to_process := array_append(ents_to_process, 'rhn_virtualization_platform'); + roles_to_process := array_append(roles_to_process, 'config_admin'); + elsif service_label_in = 'nonlinux' then + ents_to_process := array_append(ents_to_process, 'rhn_nonlinux'); + roles_to_process := array_append(roles_to_process, 'config_admin'); + end if; + + if enable_in = 'Y' then + for i in 1..array_upper(ents_to_process, 1) loop + for ent in ents(ents_to_process[i]) loop + create_row := 'Y'; + for oe in org_ents(ent.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnOrgEntitlements(org_id, entitlement_id) + values (org_id_in, ent.id); + end if; + end loop; + end loop; + for i in 1..array_upper(roles_to_process, 1) loop + for role in roles(roles_to_process[i]) loop + create_row := 'Y'; + for o_r in org_roles(role.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnUserGroup( + id, name, description, current_members, + group_type, org_id + ) ( + select nextval('rhn_user_group_id_seq'), + ugt.name || 's', + ugt.name || 's for Org ' || + o.name || ' ('|| o.id ||')', + 0, ugt.id, o.id + from rhnUserGroupType ugt, + web_customer o + where o.id = org_id_in + and ugt.id = role.id + ); + end if; + end loop; + end loop; + else + for i in 1..coalesce(array_upper(ents_to_process, 1), 0) loop + for ent in ents(ents_to_process[i]) loop + delete from rhnOrgEntitlements + where org_id = org_id_in + and entitlement_id = ent.id; + end loop; + end loop; + end if; + end$$ +language plpgsql; + + create or replace function set_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'enterprise', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'provisioning', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_monitoring ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'monitoring', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'nonlinux', 'Y'); + end$$ +language plpgsql; + + create or replace function unset_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'enterprise', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'provisioning', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_monitoring ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'monitoring', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform rhn_entitlements.modify_org_service(customer_id_in, 'nonlinux', 'N'); + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: prune_group + -- Unsubscribes servers consuming physical slots that over the org's + -- limit. + -- Called by: set_server_group_count, repoll_virt_guest_entitlements + -- ******************************************************************* + create or replace function prune_group ( + group_id_in in numeric, + quantity_in in numeric, + update_family_countsYN in numeric default 1 + ) returns void +as $$ + declare + sgrecord record; + type_is_base char; + begin + update rhnServerGroup + set max_members = quantity_in + where id = group_id_in; + + for sgrecord in ( + select server_id, server_group_id, sgt.id as group_type_id, sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_group_id = group_id_in + and sgm.server_id in ( + select sep.server_id + from + rhnServerEntitlementPhysical sep + where + sep.server_group_id = group_id_in + order by sep.modified asc + offset quantity_in + ) + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id + ) loop + perform rhn_entitlements.remove_server_entitlement(sgrecord.server_id, sgrecord.label); + + select is_base + into type_is_base + from rhnServerGroupType sgt + where sgt.id = sgrecord.group_type_id; + + -- if we're removing a base ent, then be sure to + -- remove the server's channel subscriptions. + if ( type_is_base = 'Y' ) then + perform rhn_channel.clear_subscriptions(sgrecord.server_id, 0, update_family_countsYN); + end if; + + end loop; + 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_server_group_count + -- ******************************************************************* + create or replace function assign_system_entitlement( + group_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + to_org_prev_ent_count numeric; + new_ent_count numeric; + new_quantity numeric; + group_type numeric; + begin + + select max_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = from_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + select max_members + into to_org_prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = to_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + + if not found then + to_org_prev_ent_count := 0; + end if; + + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_server_group'); + end if; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if new_ent_count < 0 then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + + perform rhn_entitlements.set_server_group_count(from_org_id_in, + group_type, + new_ent_count); + + perform rhn_entitlements.set_server_group_count(to_org_id_in, + group_type, + new_quantity); + + -- Create or delete the entries in rhnOrgEntitlementType + if group_label_in = 'enterprise_entitled' then + if new_quantity > 0 then + perform rhn_entitlements.set_customer_enterprise(to_org_id_in); + else + perform rhn_entitlements.unset_customer_enterprise(to_org_id_in); + end if; + end if; + + if group_label_in = 'provisioning_entitled' then + if new_quantity > 0 then + perform rhn_entitlements.set_customer_provisioning(to_org_id_in); + else + perform rhn_entitlements.unset_customer_provisioning(to_org_id_in); + end if; + end if; + + if group_label_in = 'monitoring_entitled' then + if new_quantity > 0 then + perform rhn_entitlements.set_customer_monitoring(to_org_id_in); + else + perform rhn_entitlements.unset_customer_monitoring(to_org_id_in); + end if; + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: assign_channel_entitlement + -- + -- Moves channel entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_family_count + -- ******************************************************************* + create or replace function assign_channel_entitlement( + channel_family_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric, + flex_in in numeric + ) returns void +as $$ + declare + from_org_prev_ent_count numeric; + from_org_prev_ent_count_flex numeric; + new_ent_count numeric; + new_ent_count_flex numeric; + to_org_prev_ent_count numeric; + to_org_prev_ent_count_flex numeric; + new_quantity numeric; + new_flex numeric; + cfam_id numeric; + begin + + select max_members + into from_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + select max_members + into to_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + to_org_prev_ent_count := 0; + end if; + + select fve_max_members + into from_org_prev_ent_count_flex + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + + select fve_max_members + into to_org_prev_ent_count_flex + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + to_org_prev_ent_count_flex := 0; + end if; + + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_channel_family'); + end if; + + new_ent_count := from_org_prev_ent_count - quantity_in; + new_ent_count_flex := from_org_prev_ent_count_flex - flex_in; + + if from_org_prev_ent_count >= new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if from_org_prev_ent_count_flex >= new_ent_count_flex then + new_flex := to_org_prev_ent_count_flex + flex_in; + end if; + + if new_ent_count < 0 then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + if new_ent_count_flex < 0 then + perform rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + + + + perform rhn_entitlements.set_family_count(from_org_id_in, + cfam_id, + new_ent_count, + new_ent_count_flex); + + perform rhn_entitlements.set_family_count(to_org_id_in, + cfam_id, + new_quantity, + new_flex); + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: activate_system_entitlement + -- + -- Sets the values in rhnServerGroup for a given rhnServerGroupType. + -- + -- Calls: set_server_group_count to update, prune, or create the group. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if all entitlements + -- in the org are used so the free entitlements would not cover + -- the difference when descreasing the number of entitlements. + -- ******************************************************************* + create or replace function activate_system_entitlement( + org_id_in in numeric, + group_label_in in varchar, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + prev_ent_count_sum numeric; + group_type numeric; + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + + select current_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.group_type = sgt.id + and sgt.label = group_label_in + and sg.org_id = org_id_in; + + if not found then + prev_ent_count := 0; + end if; + + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_server_group'); + end if; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the allocated count in this org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- don't update family counts after every server + -- will do bulk update afterwards + perform rhn_entitlements.set_server_group_count(org_id_in, + group_type, + quantity_in, + 0); + -- bulk update family counts + perform rhn_channel.update_group_family_counts(group_label_in, org_id_in); + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: activate_channel_entitlement + -- + -- Calls: set_family_count to update, prune, or create the family + -- permission bucket. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if there are not enough + -- entitlements in the org to cover the difference when you are + -- descreasing the number of entitlements. + -- + -- The backend code in Python is expected to do whatever arithmetics + -- is needed. + -- ******************************************************************* + create or replace function activate_channel_entitlement( + org_id_in in numeric, + channel_family_label_in in varchar, + quantity_in in numeric, + flex_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + prev_flex_count numeric; + prev_ent_count_sum numeric; + cfam_id numeric; + reduce_quantity numeric; + total_flex_capable numeric; + to_convert_reg cursor (channel_family_id_val numeric, org_id_in numeric, quantity numeric) for + select SC.server_id + from rhnServerChannel SC inner join + rhnServer S on S.id = SC.server_id + where + is_fve = 'Y' + and S.org_id = org_id_in + and SC.channel_id in + (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = channel_family_id_val) + limit quantity; + to_convert_flex cursor (channel_family_id_val numeric, org_id_in numeric, quantity numeric) for + select server_id + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id + limit quantity; + + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + select current_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + prev_ent_count := 0; + end if; + + select pcf.fve_current_members + into prev_flex_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + + if not found then + prev_flex_count := 0; + end if; + + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + + if not found then + perform rhn_exception.raise_exception( + 'invalid_channel_family'); + end if; + + -- if there are too few flex entitlements + if flex_in < prev_flex_count then + -- and if the extra we need is less than or equal to our extra regular entitlements + if prev_flex_count - flex_in <= quantity_in - prev_ent_count then + reduce_quantity := prev_flex_count - flex_in; + -- We need to convert some systems from flex guest to regular + for system in to_convert_reg(cfam_id, org_id_in, reduce_quantity) loop + --rhn_channel.convert_to_regular(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set is_fve = 'N' + where sc.server_id = system.server_id + and sc.channel_id in (select cfm.channel_id + from rhnChannelFamilyMembers cfm + where cfm.channel_family_id = cfam_id); + end loop; + + --reset previous counts + prev_ent_count := prev_ent_count + reduce_quantity; + prev_flex_count := prev_flex_count - reduce_quantity; + else + perform rhn_exception.raise_exception( + 'not_enough_flex_entitlements_in_base_org'); + end if; + end if; + + -- if there are too few regular entitlements, and extra flex entitlements + if quantity_in < prev_ent_count and prev_flex_count < flex_in then + -- how many flex-capable systems (that aren't using flex) do we have + select count(*) + into total_flex_capable + from rhnServerFveCapable + where SERVER_ORG_ID = org_id_in and + channel_family_id = cfam_id; + -- if we have enough flex capable machines that is at least + -- as many as what we are over on + if total_flex_capable >= prev_ent_count - quantity_in then + reduce_quantity := prev_ent_count - quantity_in; + for system in to_convert_flex(cfam_id, org_id_in, reduce_quantity) loop +-- rhn_channel.convert_to_fve(system.server_id, cfam_id); + UPDATE rhnServerChannel sc set is_fve = 'Y' + where sc.server_id = system.server_id + and sc.channel_id in (select cfm.channel_id + from rhnChannelFamilyMembers cfm + where cfm.channel_family_id = cfam_id); + end loop; + prev_ent_count := prev_ent_count - reduce_quantity; + prev_flex_count := prev_flex_count + reduce_quantity; + end if; + end if; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the count in that one org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + -- even if we've manually converted systems to/from flex above + -- set_family_count should call update_family_counts, to reset + -- current used slots + perform rhn_entitlements.set_family_count(org_id_in, cfam_id, + quantity_in, flex_in); + end if; + + end$$ +language plpgsql; + + create or replace function set_server_group_count ( + customer_id_in in numeric, -- customer_id + group_type_in in numeric, -- rhn[User|Server]GroupType.id + quantity_in in numeric, -- quantity + update_family_countsYN in numeric default 1 + ) returns void +as $$ + declare + group_id numeric; + quantity numeric; + wasfound boolean; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + + 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; + + -- preserve the not found status across the rhn_entitlements.prune_group invocation + wasfound := true; + if not found then + wasfound := false; + end if; + + perform rhn_entitlements.prune_group( + group_id, + quantity, + update_family_countsYN + ); + + if not wasfound then + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select nextval('rhn_server_group_id_seq'), name, name, + quantity, 0, id, customer_id_in, + current_timestamp, current_timestamp + from rhnServerGroupType + where id = group_type_in + ); + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: prune_family + -- Unsubscribes servers consuming physical slots from the channel family + -- that are over the org's limit. + -- Called by: set_family_count + -- ******************************************************************* + create or replace function prune_family ( + customer_id_in in numeric, + channel_family_id_in in numeric, + quantity_in in numeric, + flex_in in numeric + ) returns void +as $$ + declare + serverchannels cursor(tmp_quantity numeric, is_fve_in char) 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 + and rsc.is_fve = is_fve_in + -- we only want to grab servers consuming + -- physical slots. + and exists ( + select 1 + from rhnChannelFamilyServerPhysical cfsp + where cfsp.server_id = rs.id + and cfsp.channel_family_id = + channel_family_id_in + ) + order by 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; + + for sc in serverchannels(quantity_in, 'N') loop + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, + 1, 1, 0, 0); + end loop; + + for sc in serverchannels(flex_in, 'Y') loop + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, + 1, 1, 0, 0); + end loop; + + perform rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); + end$$ +language plpgsql; + + create or replace function set_family_count ( + customer_id_in in numeric, -- customer_id + channel_family_id_in in numeric, -- 246 + quantity_in in numeric, -- 3 + flex_in in numeric + ) returns void +as $$ + declare + privperms cursor for + select 1 + from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + pubperms cursor for + select o.id org_id + from web_customer o, + rhnPublicChannelFamily pcf + where pcf.channel_family_id = channel_family_id_in; + quantity numeric; + done numeric := 0; + flex numeric; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + flex := flex_in; + if flex is not null and flex < 0 then + flex := 0; + end if; + + if customer_id_in is not null then + for perm in privperms loop + perform rhn_entitlements.prune_family( + customer_id_in, + channel_family_id_in, + quantity, + flex + ); + + if quantity is null and flex is null then + delete from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + else + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + update rhnPrivateChannelFamily + set fve_max_members = flex + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + end if; + return; + end loop; + + insert into rhnPrivateChannelFamily ( + channel_family_id, org_id, max_members, current_members, fve_max_members, fve_current_members + ) values ( + channel_family_id_in, customer_id_in, quantity, 0, flex, 0 ); + return; + end if; + + for perm in pubperms loop + if quantity = 0 then + perform rhn_entitlements.prune_family( + perm.org_id, + channel_family_id_in, + quantity, + flex + ); + if done = 0 then + delete from rhnPublicChannelFamily + where channel_family_id = channel_family_id_in; + end if; + end if; + done := 1; + end loop; + -- if done's not 1, then we don't have any entitlements + if done != 1 then + insert into rhnPublicChannelFamily ( + channel_family_id + ) values ( + channel_family_id_in + ); + end if; + end$$ +language plpgsql; + + -- this expects quantity_in to be the number of available slots, not the + -- max_members of the server group. If you give it too many, it'll fail + -- and raise servergroup_max_members. + -- We should NEVER run this unless we're SURE that we won't + -- be violating the max. + create or replace function entitle_last_modified_servers ( + customer_id_in in numeric, -- customer_id + type_label_in in varchar, -- 'enterprise_entitled' + quantity_in in numeric -- 3 + ) returns void +as $$ + declare + -- find the servers that aren't currently in slots + servers cursor(cid_in numeric, quant_in numeric) for + select rs.id as server_id + from rhnServer rs + where 1=1 + and rs.org_id = cid_in + and not exists ( + select 1 + from rhnServerGroup sg, + rhnServerGroupMembers rsgm + where rsgm.server_id = rs.id + and rsgm.server_group_id = sg.id + and sg.group_type is not null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = + rs.id + ) + order by modified desc + limit quant_in; + begin + for server in servers(customer_id_in, quantity_in) loop + perform rhn_entitlements.entitle_server(server.server_id, type_label_in); + end loop; + end$$ +language plpgsql; + + create or replace function subscribe_newest_servers ( + customer_id_in in numeric + ) returns void +as $$ + declare + -- find servers without base channels + servers cursor(cid_in numeric) for + select s.id + from rhnServer s + where 1=1 + and s.org_id = cid_in + and not exists ( + select 1 + from rhnChannel c, + rhnServerChannel sc + where sc.server_id = s.id + and sc.channel_id = c.id + and c.parent_channel is null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = s.id + ) + order by s.modified desc; + channel_id numeric; + begin + for server in servers(customer_id_in) loop + channel_id := rhn_channel.guess_server_base(server.id); + if channel_id is not null then + begin + perform rhn_channel.subscribe_server(server.id, channel_id); + -- exception is really channel_family_no_subscriptions + exception + when others then + null; + end; + end if; + end loop; + end$$ +language plpgsql; + + +-- restore the original setting +update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_entitlements')+1) ) where name = 'search_path'; commit 10025297e140b81f266f2ae126a2f568147e4200 Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Tue Jul 23 16:38:24 2013 +0200 made serverchannels cursor properly parametrized so it doesn't look like we set random unused variables before loops diff --git a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb index 08e8062..8525816 100644 --- a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb @@ -1548,10 +1548,7 @@ is quantity_in in number, flex_in in number ) is - is_fve_in char; - tmp_quantity number; - - cursor serverchannels is + cursor serverchannels(tmp_quantity number, is_fve_in char) is select sc.server_id, sc.channel_id from rhnServerChannel sc, @@ -1594,17 +1591,12 @@ is return; end if; - tmp_quantity := quantity_in; - is_fve_in := 'N'; - - for sc in serverchannels loop + for sc in serverchannels(quantity_in, 'N') loop rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, update_family_countsYN => 0); end loop; - tmp_quantity := flex_in; - is_fve_in := 'Y'; - for sc in serverchannels loop + for sc in serverchannels(flex_in, 'Y') loop rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, update_family_countsYN => 0); end loop; diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb index 35b98a3..aa2b30e 100644 --- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -1556,10 +1556,7 @@ language plpgsql; ) returns void as $$ declare - is_fve_in char; - tmp_quantity numeric; - - serverchannels cursor for + serverchannels cursor(tmp_quantity numeric, is_fve_in char) for select sc.server_id, sc.channel_id from rhnServerChannel sc, @@ -1597,17 +1594,12 @@ as $$ return; end if; - tmp_quantity := quantity_in; - is_fve_in := 'N'; - - for sc in serverchannels loop + for sc in serverchannels(quantity_in, 'N') loop perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, 0, 0); end loop; - tmp_quantity := flex_in; - is_fve_in := 'Y'; - for sc in serverchannels loop + for sc in serverchannels(flex_in, 'Y') loop perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, 0, 0); end loop; commit 4fd5c3737a7730bfc0410972a76a1b8e0dfb3185 Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Tue Jul 23 16:34:07 2013 +0200 fixed indentation diff --git a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb index 5dc4d67..08e8062 100644 --- a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb @@ -1609,7 +1609,7 @@ is update_family_countsYN => 0); end loop; - rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); + rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); end prune_family; procedure set_family_count ( diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb index 6fcd03d..35b98a3 100644 --- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -1601,16 +1601,18 @@ as $$ is_fve_in := 'N'; for sc in serverchannels loop - perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, 0, 0); + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, + 1, 1, 0, 0); end loop; tmp_quantity := flex_in; is_fve_in := 'Y'; for sc in serverchannels loop - perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, 0, 0); + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, + 1, 1, 0, 0); end loop; - perform rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); + perform rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); end$$ language plpgsql; commit 0e4cc0c069b0cdbf0db8a905efe29ceaecfaadb4 Author: Michael Mraka <michael.mraka(a)redhat.com> Date: Tue Jul 23 16:30:41 2013 +0200 876019 - fixed misplaced end loop fixing [error] Execution of /var/www/html/network/systems/details/delete_confirm.pxt failed at Tue Oct 30 10:00:08 2012: RHN::Exception: DBD::Oracle::st execute failed: ORA-06511: PL/SQL: cursor already open\nORA-06512: at "RHNSAT.RHN_ENTITLEMENTS", line 1613\nORA-06512: at "RHNSAT.RHN_ENTITLEMENTS", line 1664\nORA-06512: at "RHNSAT.RHN_ENTITLEMENTS", line 1707\nORA-06512: at "RHNSAT.RHN_ENTITLEMENTS", line 669\nORA-06512: at "RHNSAT.DELETE_SERVER", line 68\nORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) diff --git a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb index f6bdef0..5dc4d67 100644 --- a/schema/spacewalk/oracle/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/oracle/packages/rhn_entitlements.pkb @@ -1600,6 +1600,7 @@ is for sc in serverchannels loop rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, update_family_countsYN => 0); + end loop; tmp_quantity := flex_in; is_fve_in := 'Y'; @@ -1608,9 +1609,6 @@ is update_family_countsYN => 0); end loop; - - - end loop; rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); end prune_family; diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb index c633ad6..6fcd03d 100644 --- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -1602,13 +1602,14 @@ as $$ for sc in serverchannels loop perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, 0, 0); + end loop; tmp_quantity := flex_in; is_fve_in := 'Y'; for sc in serverchannels loop perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1, 0, 0); end loop; - end loop; + perform rhn_channel.update_family_counts(channel_family_id_in, customer_id_in); end$$ language plpgsql;

1 0

Changes to 'refs/tags/spacewalk-java-2.1.5-1'
by Dimitar Yordanov 23 Jul '13

23 Jul '13

Tag 'spacewalk-java-2.1.5-1' created by Dimitar Yordanov <dyordano(a)redhat.com> at 2013-07-23 13:29 +0000 Tagging package [spacewalk-java] version [2.1.5-1] in directory [java/]. Changes since spacewalk-setup-2.1.1-1: Dimitar Yordanov (2): new api call updateRepoLabel(key, label, new_label Automatic commit of package [spacewalk-java] release [2.1.5-1]. --- java/code/src/com/redhat/rhn/frontend/xmlrpc/channel/software/ChannelSoftwareHandler.java | 25 +++++++++- java/spacewalk-java.spec | 5 +- rel-eng/packages/spacewalk-java | 2 3 files changed, 28 insertions(+), 4 deletions(-) ---

1 0

java/spacewalk-java.spec rel-eng/packages
by Dimitar Yordanov 23 Jul '13

23 Jul '13

java/spacewalk-java.spec | 5 ++++- rel-eng/packages/spacewalk-java | 2 +- 2 files changed, 5 insertions(+), 2 deletions(-) New commits: commit ae553ae38a326bdf8665f0690eff874249696a3c Author: Dimitar Yordanov <dyordano(a)redhat.com> Date: Tue Jul 23 15:29:04 2013 +0200 Automatic commit of package [spacewalk-java] release [2.1.5-1]. diff --git a/java/spacewalk-java.spec b/java/spacewalk-java.spec index 300fbc1..f6deb43 100644 --- a/java/spacewalk-java.spec +++ b/java/spacewalk-java.spec @@ -28,7 +28,7 @@ Name: spacewalk-java Summary: Spacewalk Java site packages Group: Applications/Internet License: GPLv2 -Version: 2.1.4 +Version: 2.1.5 Release: 1%{?dist} URL: https://fedorahosted.org/spacewalk Source0: https://fedorahosted.org/releases/s/p/spacewalk/%{name}-%{version}.tar.gz @@ -771,6 +771,9 @@ fi %{jardir}/postgresql-jdbc.jar %changelog +* Tue Jul 23 2013 Dimitar Yordanov <dyordano(a)redhat.com> 2.1.5-1 +- new api call updateRepoLabel(key, label, new_label + * Mon Jul 22 2013 Tomas Lestach <tlestach(a)redhat.com> 2.1.4-1 - 986527 - removing extra semicolon diff --git a/rel-eng/packages/spacewalk-java b/rel-eng/packages/spacewalk-java index 119940d..313545d 100644 --- a/rel-eng/packages/spacewalk-java +++ b/rel-eng/packages/spacewalk-java @@ -1 +1 @@ -2.1.4-1 java/ +2.1.5-1 java/

1 0

java/code
by Dimitar Yordanov 23 Jul '13

23 Jul '13

java/code/src/com/redhat/rhn/frontend/xmlrpc/channel/software/ChannelSoftwareHandler.java | 25 +++++++++- 1 file changed, 23 insertions(+), 2 deletions(-) New commits: commit 2e9f629c03dee742385f4e85c53d905e1cb9c4cf Author: Dimitar Yordanov <dyordano(a)redhat.com> Date: Tue Jul 23 14:51:28 2013 +0200 new api call updateRepoLabel(key, label, new_label diff --git a/java/code/src/com/redhat/rhn/frontend/xmlrpc/channel/software/ChannelSoftwareHandler.java b/java/code/src/com/redhat/rhn/frontend/xmlrpc/channel/software/ChannelSoftwareHandler.java index 1fe66d5..9f30026 100644 --- a/java/code/src/com/redhat/rhn/frontend/xmlrpc/channel/software/ChannelSoftwareHandler.java +++ b/java/code/src/com/redhat/rhn/frontend/xmlrpc/channel/software/ChannelSoftwareHandler.java @@ -2543,7 +2543,7 @@ public class ChannelSoftwareHandler extends BaseHandler { * @xmlrpc.doc Updates repository source URL * @xmlrpc.param #session_key() * @xmlrpc.param #param_desc("int", "id", "repository id") - * @xmlrpc.param #param_desc("string", "url", "repository url") + * @xmlrpc.param #param_desc("string", "url", "new repository url") * @xmlrpc.returntype $ContentSourceSerializer **/ public ContentSource updateRepoUrl(String sessionKey, Integer id, String url) { @@ -2564,7 +2564,7 @@ public class ChannelSoftwareHandler extends BaseHandler { * @xmlrpc.doc Updates repository source URL * @xmlrpc.param #session_key() * @xmlrpc.param #param_desc("string", "label", "repository label") - * @xmlrpc.param #param_desc("string", "url", "repository url") + * @xmlrpc.param #param_desc("string", "url", "new repository url") * @xmlrpc.returntype $ContentSourceSerializer **/ public ContentSource updateRepoUrl(String sessionKey, String label, String url) { @@ -2596,6 +2596,27 @@ public class ChannelSoftwareHandler extends BaseHandler { return repo; } + /** + * Updates repository label + * @param sessionKey Session containing user information. + * @param id ID of the repo + * @param label new label + * @return the updated repo + * + * @xmlrpc.doc Updates repository label + * @xmlrpc.param #session_key() + * @xmlrpc.param #param_desc("string", "label", "repository label") + * @xmlrpc.param #param_desc("string", "label", "new repository label") + * @xmlrpc.returntype $ContentSourceSerializer + **/ + public ContentSource updateRepoLabel(String sessionKey, String label, String new_label) { + User user = getLoggedInUser(sessionKey); + ContentSource repo = lookupContentSourceByLabel(label, user.getOrg()); + setRepoLabel(repo, new_label); + ChannelFactory.save(repo); + return repo; + } + /** * Updates a repository * @param sessionKey Session containing user information.

1 0

← Newer
1
2
3
4
5
6
7
8
9
...
46
Older →

Jump to page:

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

spacewalk-commits July 2013