schema/spacewalk/common/tables/rhnConfigRevision.sql
| 4
schema/spacewalk/oracle/packages/rhn_config.pkb
| 51 --
schema/spacewalk/oracle/triggers/rhnConfigRevision.sql
| 22
schema/spacewalk/postgres/packages/rhn_config.pkb
| 52 --
schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
| 28 -
schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.oracle
| 11
schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.postgresql
| 3
schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.oracle
| 248 ++++++++++
schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.postgresql
| 235 +++++++++
9 files changed, 547 insertions(+), 107 deletions(-)
New commits:
commit 1f83b7cb1e42f22df6a86d295468af70306daf85
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Jun 21 15:36:31 2013 +0200
906157 - upgrade scripts for rhn_config.delete_file() and rhn_confrevision_ccid_idx
index
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.oracle
new file mode 100644
index 0000000..4c20100
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.oracle
@@ -0,0 +1,11 @@
+declare
+ name_already_used exception;
+ pragma exception_init(name_already_used, -00955);
+begin
+ execute immediate 'create index rhn_confrevision_ccid_idx on
rhnConfigRevision (config_content_id) tablespace [[2m_tbs]]';
+ dbms_output.put_line('Index rhn_confrevision_ccid_idx successfully
created.');
+exception
+ when name_already_used then
+ dbms_output.put_line('Index rhn_confrevision_ccid_idx already
exists.');
+end;
+/
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.postgresql
new file mode 100644
index 0000000..da10970
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/020-rhnConfigRevision-index.sql.postgresql
@@ -0,0 +1,3 @@
+-- oracle equivalent source sha1 5382865750405facc547315329a739b8ee1fe238
+
+create index rhn_confrevision_ccid_idx on rhnConfigRevision (config_content_id);
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.oracle
new file mode 100644
index 0000000..2812d55
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.oracle
@@ -0,0 +1,248 @@
+--
+-- 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_config
+is
+ -- just a stub for now
+ procedure prune_org_configs (
+ org_id_in in number,
+ total_in in number
+ ) is
+ begin
+ null;
+ end prune_org_configs;
+
+ function insert_revision (
+ revision_in in number,
+ config_file_id_in in number,
+ config_content_id_in in number,
+ config_info_id_in in number,
+ config_file_type_id_in in number := 1
+ ) return number is
+ retval number;
+
+ begin
+
+ insert into rhnConfigRevision(id, revision, config_file_id,
+ config_content_id, config_info_id, config_file_type_id
+ ) values (
+ rhn_confrevision_id_seq.nextval, revision_in, config_file_id_in,
+ config_content_id_in, config_info_id_in, config_file_type_id_in
+ )
+ returning id into retval;
+
+ return retval;
+ end insert_revision;
+
+ procedure delete_revision (
+ config_revision_id_in in number,
+ org_id_in in number := -1
+ ) is
+ cfid number;
+ ccid number;
+ latest_crid number;
+ others number := 0;
+ begin
+ select cr.config_content_id
+ into ccid
+ from rhnConfigRevision cr
+ where cr.id = config_revision_id_in;
+
+ -- right now this will set rhnActionConfigFileName.config_revision_id
+ -- to null, and will remove an entry from rhnActionConfigRevision.
+ -- might we want to prune and/or kill the action in some way? maybe
+ -- mark it failed or something?
+ -- this delete also triggers rhn_confrevision_del_trig() which
+ -- a) updates rhnSnapshot.invalid to 'cr_removed'
+ -- and b) deletes snapshots from rhnSnapshotConfigRevision
+ delete from rhnConfigRevision where id = config_revision_id_in;
+
+ -- now prune away content if there aren't any other revisions pointing
+ -- at it
+ select count(*)
+ into others
+ from rhnConfigRevision
+ where config_content_id = ccid;
+ if others = 0 then
+ delete from rhnConfigContent where id = ccid;
+ end if;
+
+ -- now make sure rhnConfigFile points at a valid revision;
+ -- if there isn't one, we're deleting it, _unless_ org_id_in is
+ -- >= 0, in which case we're in the delete trigger anyway
+ if org_id_in < 0 then
+ select cf.latest_config_revision_id,
+ cf.id
+ into latest_crid,
+ cfid
+ from rhnConfigFile cf,
+ rhnConfigRevision cr
+ where cr.id = config_revision_id_in
+ and cr.config_file_id = cf.id;
+
+ if latest_crid = config_revision_id_in then
+ latest_crid := rhn_config.get_latest_revision(cfid);
+ if latest_crid is not null then
+ update rhnConfigFile set latest_config_revision_id = latest_crid
+ where id = cfid;
+ else
+ delete from rhnConfigFile where id = cfid;
+ end if;
+ end if;
+
+ end if;
+ end delete_revision;
+
+ function get_latest_revision (
+ config_file_id_in in number
+ ) return number is
+ cursor revisions is
+ select cr.id
+ from rhnConfigRevision cr
+ where cr.config_file_id = config_file_id_in
+ order by revision desc;
+ begin
+ for revision in revisions loop
+ return revision.id;
+ end loop;
+ return null;
+ end get_latest_revision;
+
+ function insert_file (
+ config_channel_id_in in number,
+ name_in in varchar2
+ ) return number is
+ retval number;
+ begin
+ select rhn_conffile_id_seq.nextval
+ into retval
+ from dual;
+
+ insert into rhnConfigFile(id, config_channel_id, config_file_name_id,
+ state_id
+ ) (
+ select retval,
+ config_channel_id_in,
+ lookup_config_filename(name_in),
+ id
+ from rhnConfigFileState
+ where label = 'alive'
+ );
+
+ return retval;
+ end insert_file;
+
+ procedure delete_file (
+ config_file_id_in in number
+ ) is
+ cursor revisions is
+ select cr.id, cc.org_id
+ from rhnConfigChannel cc,
+ rhnConfigRevision cr,
+ rhnConfigFile cf
+ where cf.id = config_file_id_in
+ and cf.config_channel_id = cc.id
+ and cr.config_file_id = cf.id;
+ begin
+ for revision in revisions loop
+ rhn_config.delete_revision(revision.id, revision.org_id);
+ end loop;
+ delete from rhnConfigFile where id = config_file_id_in;
+ end delete_file;
+
+ function insert_channel (
+ org_id_in in number,
+ type_in in varchar2,
+ name_in in varchar2,
+ label_in in varchar2,
+ description_in in varchar2
+ ) return number is
+ retval number;
+ begin
+ select rhn_confchan_id_seq.nextval
+ into retval
+ from dual;
+
+ insert into rhnConfigChannel(id, org_id, confchan_type_id,
+ name, label, description
+ ) (
+ select retval,
+ org_id_in,
+ cct.id,
+ name_in,
+ label_in,
+ description_in
+ from rhnConfigChannelType cct
+ where label = type_in
+ );
+ return retval;
+ end insert_channel;
+
+ procedure delete_channel (
+ config_channel_id_in in number
+ ) is
+ cursor config_files is
+ select id
+ from rhnConfigFile
+ where config_channel_id = config_channel_id_in;
+ begin
+ for config_file in config_files loop
+ rhn_config.delete_file(config_file.id);
+ end loop;
+ delete from rhnConfigChannel where id = config_channel_id_in;
+ end delete_channel;
+end rhn_config;
+/
+show errors
+
+--
+--
+-- Revision 1.9 2005/02/16 14:03:35 jslagle
+-- bz #148844
+-- Changed insert_revision function to take a config_file_type_id instead of label
+--
+-- Revision 1.8 2005/02/15 02:42:59 jslagle
+-- bz #147860
+-- insert_revision function now takes a rhnConfigFileType label as a parameter instead of
an id
+--
+-- Revision 1.7 2005/02/14 22:45:23 jslagle
+-- bz#147860
+-- Update rhn_config package body and specification for additional column to
rhnConfigRevision
+--
+-- Revision 1.6 2004/10/11 14:02:53 pjones
+-- bugzilla: 133169 -- somehow, we just never update the quota in this case.
+-- Amazingly, I thought this worked _and_ QA passed it...
+--
+-- Revision 1.5 2004/01/09 17:39:45 pjones
+-- bugzilla: 113029 -- need to do functions for deleting rhnConfigChannel,
+-- too, or we can't prune rhnConfigFile when we do.
+--
+-- Revision 1.4 2004/01/08 19:46:31 pjones
+-- bugzilla: 113029 -- insert/delete for rhnConfigFile and rhnConfigRevision
+--
+-- Revision 1.3 2004/01/08 00:30:10 pjones
+-- bugzilla: 113029 -- more deletion of config files and revisions
+--
+-- Revision 1.2 2004/01/08 00:03:37 pjones
+-- bugzilla: 113029 -- rhn_config.delete_revision() and delete trigger on
+-- rhnConfigFile
+--
+-- Revision 1.1 2003/12/19 22:07:30 pjones
+-- bugzilla: 112392 -- quota support for config files
+--
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.postgresql
new file mode 100644
index 0000000..bc388d3
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.9-to-spacewalk-schema-1.10/021-rhn_config.pkb.sql.postgresql
@@ -0,0 +1,235 @@
+-- oracle equivalent source sha1 c6a5bd3288aa8dc1d2836b4d123be2c3f77413f3
+--
+-- 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.
+--
+--
+--
+--
+
+-- setup search_path so that these functions are created in appropriate schema.
+update pg_settings set setting = 'rhn_config,' || setting where name =
'search_path';
+
+
+ -- just a stub for now
+create or replace function prune_org_configs (
+ org_id_in in numeric,
+ total_in in numeric
+ )
+returns void
+as $$
+begin
+end;
+$$ LANGUAGE 'plpgsql';
+
+
+ create or replace function insert_revision (
+ revision_in in numeric,
+ config_file_id_in in numeric,
+ config_content_id_in in numeric,
+ config_info_id_in in numeric,
+ config_file_type_id_in in numeric default 1
+ ) returns numeric as $$
+ declare
+ retval numeric;
+
+ begin
+
+ insert into rhnConfigRevision(id, revision, config_file_id,
+ config_content_id, config_info_id, config_file_type_id
+ ) values (
+ nextval('rhn_confrevision_id_seq'), revision_in, config_file_id_in,
+ config_content_id_in, config_info_id_in, config_file_type_id_in
+ )
+ returning id into retval;
+
+ return retval;
+ end$$ language 'plpgsql';
+
+ create or replace function delete_revision (
+ config_revision_id_in in numeric,
+ org_id_in in numeric default -1
+ ) returns void as $$
+ declare
+ cfid numeric;
+ ccid numeric;
+ latest_crid numeric;
+ others numeric := 0;
+ begin
+ select cr.config_content_id
+ into ccid
+ from rhnConfigRevision cr
+ where cr.id = config_revision_id_in;
+
+ -- right now this will set rhnActionConfigFileName.config_revision_id
+ -- to null, and will remove an entry from rhnActionConfigRevision.
+ -- might we want to prune and/or kill the action in some way? maybe
+ -- mark it failed or something?
+ -- this delete also triggers rhn_confrevision_del_trig() which
+ -- a) updates rhnSnapshot.invalid to 'cr_removed'
+ -- and b) deletes snapshots from rhnSnapshotConfigRevision
+ delete from rhnConfigRevision where id = config_revision_id_in;
+
+ -- now prune away content if there aren't any other revisions pointing
+ -- at it
+ select count(*)
+ into others
+ from rhnConfigRevision
+ where config_content_id = ccid;
+ if others = 0 then
+ delete from rhnConfigContent where id = ccid;
+ end if;
+
+ -- now make sure rhnConfigFile points at a valid revision;
+ -- if there isn't one, we're deleting it, _unless_ org_id_in is
+ -- >= 0, in which case we're in the delete trigger anyway
+ if org_id_in < 0 then
+ select cf.latest_config_revision_id,
+ cf.id
+ into latest_crid,
+ cfid
+ from rhnConfigFile cf,
+ rhnConfigRevision cr
+ where cr.id = config_revision_id_in
+ and cr.config_file_id = cf.id;
+
+ if latest_crid = config_revision_id_in then
+ latest_crid := rhn_config.get_latest_revision(cfid);
+ if latest_crid is not null then
+ update rhnConfigFile set latest_config_revision_id = latest_crid
+ where id = cfid;
+ else
+ delete from rhnConfigFile where id = cfid;
+ end if;
+ end if;
+
+ end if;
+
+ end ;
+$$ LANGUAGE 'plpgsql';
+
+ create or replace function get_latest_revision (
+ config_file_id_in in numeric
+ )
+ returns numeric as $$
+ declare
+ revision1 record;
+ begin
+ for revision1 in
+ select cr.id
+ from rhnConfigRevision cr
+ where cr.config_file_id = config_file_id_in
+ order by revision desc
+ loop
+ return revision1.id;
+ end loop;
+ return null;
+end;
+$$ LANGUAGE 'plpgsql';
+
+ create or replace function insert_file (
+ config_channel_id_in in numeric,
+ name_in in varchar
+ ) returns numeric as $$
+ declare
+ retval numeric;
+ begin
+ select nextval('rhn_conffile_id_seq')
+ into retval;
+
+ insert into rhnConfigFile(id, config_channel_id, config_file_name_id,
+ state_id
+ ) (
+ select retval,
+ config_channel_id_in,
+ lookup_config_filename(name_in),
+ id
+ from rhnConfigFileState
+ where label = 'alive'
+ );
+
+ return retval;
+ end;
+$$ LANGUAGE 'plpgsql';
+
+ create or replace function delete_file (
+ config_file_id_in in numeric
+ ) returns void as $$
+declare
+revision record;
+
+ begin
+ for revision in
+ select cr.id, cc.org_id
+ from rhnConfigChannel cc,
+ rhnConfigRevision cr,
+ rhnConfigFile cf
+ where cf.id = config_file_id_in
+ and cf.config_channel_id = cc.id
+ and cr.config_file_id = cf.id
+
+ loop
+ perform rhn_config.delete_revision(revision.id, revision.org_id);
+ end loop;
+ delete from rhnConfigFile where id = config_file_id_in;
+ end;
+$$ LANGUAGE 'plpgsql';
+
+ create or replace function insert_channel (
+ org_id_in in numeric,
+ type_in in varchar,
+ name_in in varchar,
+ label_in in varchar,
+ description_in in varchar
+ ) returns numeric as $$
+declare
+ retval numeric;
+ begin
+ select nextval ('rhn_confchan_id_seq')
+ into retval;
+
+ insert into rhnConfigChannel(id, org_id, confchan_type_id,
+ name, label, description
+ ) (
+ select retval,
+ org_id_in,
+ cct.id,
+ name_in,
+ label_in,
+ description_in
+ from rhnConfigChannelType cct
+ where label = type_in
+ );
+ return retval;
+ end;
+$$ LANGUAGE 'plpgsql';
+
+ create or replace function delete_channel (
+ config_channel_id_in in numeric
+ ) returns void as $$
+declare
+ config_file record;
+ begin
+ for config_file in
+ select id
+ from rhnConfigFile
+ where config_channel_id = config_channel_id_in
+ loop
+ perform rhn_config.delete_file(config_file.id);
+ end loop;
+ delete from rhnConfigChannel where id = config_channel_id_in;
+
+end;
+$$ LANGUAGE 'plpgsql';
+
+update pg_settings set setting = overlay( setting placing '' from 1 for
(length('rhn_config')+1) ) where name = 'search_path';
commit 1f67e977eb2d93dd918ef17c62fe806868394ad7
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Jun 21 16:15:34 2013 +0200
906157 - index columns by which we select
and make 'others' logic a bit more readable
diff --git a/schema/spacewalk/common/tables/rhnConfigRevision.sql
b/schema/spacewalk/common/tables/rhnConfigRevision.sql
index 3970834..895c190 100644
--- a/schema/spacewalk/common/tables/rhnConfigRevision.sql
+++ b/schema/spacewalk/common/tables/rhnConfigRevision.sql
@@ -49,5 +49,9 @@ CREATE UNIQUE INDEX rhn_confrevision_cfid_rev_uq
ON rhnConfigRevision (config_file_id, revision)
TABLESPACE [[2m_tbs]];
+CREATE INDEX rhn_confrevision_ccid_idx
+ ON rhnConfigRevision (config_content_id)
+ TABLESPACE [[2m_tbs]];
+
CREATE SEQUENCE rhn_confrevision_id_seq;
diff --git a/schema/spacewalk/oracle/packages/rhn_config.pkb
b/schema/spacewalk/oracle/packages/rhn_config.pkb
index 30fdb9c..2812d55 100644
--- a/schema/spacewalk/oracle/packages/rhn_config.pkb
+++ b/schema/spacewalk/oracle/packages/rhn_config.pkb
@@ -58,10 +58,6 @@ is
ccid number;
latest_crid number;
others number := 0;
- cursor other_revisions(config_content_id_in in number) is
- select 1
- from rhnConfigRevision
- where config_content_id = config_content_id_in;
begin
select cr.config_content_id
into ccid
@@ -79,10 +75,10 @@ is
-- now prune away content if there aren't any other revisions pointing
-- at it
- for other_revision in other_revisions(ccid) loop
- others := 1;
- exit;
- end loop;
+ select count(*)
+ into others
+ from rhnConfigRevision
+ where config_content_id = ccid;
if others = 0 then
delete from rhnConfigContent where id = ccid;
end if;
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pkb
b/schema/spacewalk/postgres/packages/rhn_config.pkb
index 5f68c31..bc388d3 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_config.pkb
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 448ad1d6ea4d0eec5d067961b584d6f4e0be22b3
+-- oracle equivalent source sha1 c6a5bd3288aa8dc1d2836b4d123be2c3f77413f3
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
@@ -65,10 +65,6 @@ $$ LANGUAGE 'plpgsql';
ccid numeric;
latest_crid numeric;
others numeric := 0;
- other_revisions cursor (config_content_id_in numeric) is
- select 1
- from rhnConfigRevision
- where config_content_id = config_content_id_in;
begin
select cr.config_content_id
into ccid
@@ -86,10 +82,10 @@ $$ LANGUAGE 'plpgsql';
-- now prune away content if there aren't any other revisions pointing
-- at it
- for other_revision in other_revisions(ccid) loop
- others := 1;
- exit;
- end loop;
+ select count(*)
+ into others
+ from rhnConfigRevision
+ where config_content_id = ccid;
if others = 0 then
delete from rhnConfigContent where id = ccid;
end if;
commit 1136d82388eea02f9c7cce9e0569bb793225c8c5
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Jun 21 16:11:44 2013 +0200
906157 - removed duplicate update
the very same update is run from delete trigger
rhn_confrevision_del_trig on rhnConfigRevision table
diff --git a/schema/spacewalk/oracle/packages/rhn_config.pkb
b/schema/spacewalk/oracle/packages/rhn_config.pkb
index 631ad08..30fdb9c 100644
--- a/schema/spacewalk/oracle/packages/rhn_config.pkb
+++ b/schema/spacewalk/oracle/packages/rhn_config.pkb
@@ -58,25 +58,11 @@ is
ccid number;
latest_crid number;
others number := 0;
- cursor snapshots is
- select scr.snapshot_id id
- from rhnSnapshot s,
- rhnSnapshotConfigRevision scr
- where scr.config_revision_id = config_revision_id_in
- and scr.snapshot_id = s.id
- and s.invalid is null;
cursor other_revisions(config_content_id_in in number) is
select 1
from rhnConfigRevision
where config_content_id = config_content_id_in;
begin
- for snapshot in snapshots loop
- update rhnSnapshot s
- set s.invalid =
- lookup_snapshot_invalid_reason('cr_removed')
- where s.id = snapshot.id;
- end loop;
-
select cr.config_content_id
into ccid
from rhnConfigRevision cr
@@ -86,6 +72,9 @@ is
-- to null, and will remove an entry from rhnActionConfigRevision.
-- might we want to prune and/or kill the action in some way? maybe
-- mark it failed or something?
+ -- this delete also triggers rhn_confrevision_del_trig() which
+ -- a) updates rhnSnapshot.invalid to 'cr_removed'
+ -- and b) deletes snapshots from rhnSnapshotConfigRevision
delete from rhnConfigRevision where id = config_revision_id_in;
-- now prune away content if there aren't any other revisions pointing
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pkb
b/schema/spacewalk/postgres/packages/rhn_config.pkb
index 2b35457..5f68c31 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_config.pkb
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 7d1fc2f28241349883a386404ec51659f428c61c
+-- oracle equivalent source sha1 448ad1d6ea4d0eec5d067961b584d6f4e0be22b3
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
@@ -65,24 +65,11 @@ $$ LANGUAGE 'plpgsql';
ccid numeric;
latest_crid numeric;
others numeric := 0;
- snapshots cursor is
- select scr.snapshot_id as id
- from rhnSnapshot s,
- rhnSnapshotConfigRevision scr
- where scr.config_revision_id = config_revision_id_in
- and scr.snapshot_id = s.id
- and s.invalid is null;
other_revisions cursor (config_content_id_in numeric) is
select 1
from rhnConfigRevision
where config_content_id = config_content_id_in;
begin
- for snapshot in snapshots loop
- update rhnSnapshot
- set invalid =
lookup_snapshot_invalid_reason('cr_removed')
- where id = snapshot.id;
- end loop;
-
select cr.config_content_id
into ccid
from rhnConfigRevision cr
@@ -92,6 +79,9 @@ $$ LANGUAGE 'plpgsql';
-- to null, and will remove an entry from rhnActionConfigRevision.
-- might we want to prune and/or kill the action in some way? maybe
-- mark it failed or something?
+ -- this delete also triggers rhn_confrevision_del_trig() which
+ -- a) updates rhnSnapshot.invalid to 'cr_removed'
+ -- and b) deletes snapshots from rhnSnapshotConfigRevision
delete from rhnConfigRevision where id = config_revision_id_in;
-- now prune away content if there aren't any other revisions pointing
commit 7f112c30b395637f9ef3c833bbab1b1c777f3bbe
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Fri Jun 21 13:34:49 2013 +0200
906157 - replace number of small updates/deletes in loop by a global update/delete
also call lookup_snapshot_invalid_reason() just once
diff --git a/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql
b/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql
index 575abe3..f26885b 100644
--- a/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql
+++ b/schema/spacewalk/oracle/triggers/rhnConfigRevision.sql
@@ -31,18 +31,18 @@ rhn_confrevision_del_trig
before delete on rhnConfigRevision
for each row
declare
- cursor snapshots is
- select snapshot_id id
- from rhnSnapshotConfigRevision
- where config_revision_id = :old.id;
+ cr_removed number := lookup_snapshot_invalid_reason('cr_removed');
begin
- for snapshot in snapshots loop
- update rhnSnapshot
- set invalid = lookup_snapshot_invalid_reason('cr_removed')
- where id = snapshot.id;
- delete from rhnSnapshotConfigRevision
- where snapshot_id = snapshot.id
- and config_revision_id = :old.id;
+ update rhnSnapshot
+ set invalid = cr_removed
+ where id in (select snapshot_id
+ from rhnSnapshotConfigRevision
+ where config_revision_id = :old.id);
+ delete from rhnSnapshotConfigRevision
+ where config_revision_id = :old.id
+ and snapshot_id in (select snapshot_id
+ from rhnSnapshotConfigRevision
+ where config_revision_id = :old.id);
end loop;
end;
/
diff --git a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
index e58fd18..aeb59cb 100644
--- a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
+++ b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 f5d6eab1e44d06ab27c8169b7fdd2070e3465ad1
+-- oracle equivalent source sha1 54ce1d758b270102418b62da8393d992d5ff3552
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
@@ -37,22 +37,18 @@ create or replace function rhn_confrevision_del_trig_fun() returns
trigger
as
$$
declare
- snapshot_curs_id numeric;
+ cr_removed numeric := lookup_snapshot_invalid_reason('cr_removed');
begin
- for snapshot_curs_id in
- select snapshot_id
- from rhnSnapshotConfigRevision
- where config_revision_id = old.id
- loop
- update rhnSnapshot
- set invalid =
lookup_snapshot_invalid_reason('cr_removed')
- where id = snapshot_curs_id;
- delete from rhnSnapshotConfigRevision
- where snapshot_id = snapshot_curs_id
- and config_revision_id = old.id;
-
- end loop;
-
+ update rhnSnapshot
+ set invalid = cr_removed
+ where id in (select snapshot_id
+ from rhnSnapshotConfigRevision
+ where config_revision_id = :old.id);
+ delete from rhnSnapshotConfigRevision
+ where config_revision_id = old.id;
+ and snapshot_id in (select snapshot_id
+ from rhnSnapshotConfigRevision
+ where config_revision_id = :old.id);
return old;
end;
$$ language plpgsql;
commit 578b014f88159d444f98ef0090569d983d110852
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Thu Jun 20 13:08:47 2013 +0200
removed unused variable oid and org_id
diff --git a/schema/spacewalk/oracle/packages/rhn_config.pkb
b/schema/spacewalk/oracle/packages/rhn_config.pkb
index 38123ad..631ad08 100644
--- a/schema/spacewalk/oracle/packages/rhn_config.pkb
+++ b/schema/spacewalk/oracle/packages/rhn_config.pkb
@@ -56,7 +56,6 @@ is
) is
cfid number;
ccid number;
- oid number;
latest_crid number;
others number := 0;
cursor snapshots is
@@ -78,21 +77,10 @@ is
where s.id = snapshot.id;
end loop;
- if org_id_in < 0 then
- select cr.config_content_id, cc.org_id
- into ccid, oid
- from rhnConfigChannel cc,
- rhnConfigFile cf,
- rhnConfigRevision cr
- where cr.id = config_revision_id_in
- and cr.config_file_id = cf.id
- and cf.config_channel_id = cc.id;
- else
- select cr.config_content_id, org_id_in
- into ccid, oid
- from rhnConfigRevision cr
- where cr.id = config_revision_id_in;
- end if;
+ select cr.config_content_id
+ into ccid
+ from rhnConfigRevision cr
+ where cr.id = config_revision_id_in;
-- right now this will set rhnActionConfigFileName.config_revision_id
-- to null, and will remove an entry from rhnActionConfigRevision.
@@ -186,11 +174,9 @@ is
where cf.id = config_file_id_in
and cf.config_channel_id = cc.id
and cr.config_file_id = cf.id;
- org_id number;
begin
for revision in revisions loop
rhn_config.delete_revision(revision.id, revision.org_id);
- org_id := revision.org_id;
end loop;
delete from rhnConfigFile where id = config_file_id_in;
end delete_file;
diff --git a/schema/spacewalk/postgres/packages/rhn_config.pkb
b/schema/spacewalk/postgres/packages/rhn_config.pkb
index c51d447..2b35457 100644
--- a/schema/spacewalk/postgres/packages/rhn_config.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_config.pkb
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 e757b7ad78599342abae51421c490f364bc8187f
+-- oracle equivalent source sha1 7d1fc2f28241349883a386404ec51659f428c61c
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
@@ -63,7 +63,6 @@ $$ LANGUAGE 'plpgsql';
declare
cfid numeric;
ccid numeric;
- oid numeric;
latest_crid numeric;
others numeric := 0;
snapshots cursor is
@@ -84,21 +83,10 @@ $$ LANGUAGE 'plpgsql';
where id = snapshot.id;
end loop;
- if org_id_in < 0 then
- select cr.config_content_id, cc.org_id
- into ccid, oid
- from rhnConfigChannel cc,
- rhnConfigFile cf,
- rhnConfigRevision cr
- where cr.id = config_revision_id_in
- and cr.config_file_id = cf.id
- and cf.config_channel_id = cc.id;
- else
- select cr.config_content_id, org_id_in
- into ccid, oid
- from rhnConfigRevision cr
- where cr.id = config_revision_id_in;
- end if;
+ select cr.config_content_id
+ into ccid
+ from rhnConfigRevision cr
+ where cr.id = config_revision_id_in;
-- right now this will set rhnActionConfigFileName.config_revision_id
-- to null, and will remove an entry from rhnActionConfigRevision.
@@ -192,7 +180,6 @@ $$ LANGUAGE 'plpgsql';
config_file_id_in in numeric
) returns void as $$
declare
-org_id numeric;
revision record;
begin
@@ -207,7 +194,6 @@ revision record;
loop
perform rhn_config.delete_revision(revision.id, revision.org_id);
- org_id := revision.org_id;
end loop;
delete from rhnConfigFile where id = config_file_id_in;
end;