schema/spacewalk/oracle/procs/insert_xccdf_ident.sql | 25 +++ schema/spacewalk/oracle/procs/insert_xccdf_ident_system.sql | 26 +++ schema/spacewalk/oracle/procs/lookup_xccdf_ident.sql | 76 +++++---- schema/spacewalk/oracle/procs/procs.deps | 5 schema/spacewalk/postgres/procs/insert_xccdf_ident.sql | 2 schema/spacewalk/postgres/procs/insert_xccdf_ident_system.sql | 2 schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql | 79 +++++----- schema/spacewalk/postgres/procs/procs.deps | 3 schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.oracle | 69 ++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.postgresql | 50 ++++++ 10 files changed, 269 insertions(+), 68 deletions(-)
New commits: commit e54c598dd63c30249ab57ccc2a9e47e45ac67ba0 Author: Milan Zazrivec mzazrivec@redhat.com Date: Wed Feb 29 18:04:24 2012 +0100
lookup_xccdf_ident: schema upgrade
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.oracle new file mode 100644 index 0000000..8862f3b --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.oracle @@ -0,0 +1,69 @@ +create or replace function insert_xccdf_ident_system(system_in varchar2) +return number +is + pragma autonomous_transaction; + ident_sys_id number; +begin + insert into rhnXccdfIdentSystem (id, system) + values (rhn_xccdf_identsytem_id_seq.nextval, system_in) returning id into ident_sys_id; + commit; + return ident_sys_id; +end; +/ +show errors + +create or replace function insert_xccdf_ident(ident_sys_id number, identifier_in in varchar2) +return number +is + pragma autonomous_transaction; + xccdf_ident_id number; +begin + insert into rhnXccdfIdent (id, identsystem_id, identifier) + values (rhn_xccdf_ident_id_seq.nextval, ident_sys_id, identifier_in) returning id into xccdf_ident_id; + commit; + return xccdf_ident_id; +end; +/ + +create or replace function +lookup_xccdf_ident(system_in in varchar2, identifier_in in varchar2) +return number +is + pragma autonomous_transaction; + xccdf_ident_id number; + ident_sys_id number; +begin + begin + select id + into ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + exception when no_data_found then + begin + ident_sys_id := insert_xccdf_ident_system(system_in); + exception when dup_val_on_index then + select id + into ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + end; + end; + + select id + into xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + return xccdf_ident_id; +exception when no_data_found then + begin + xccdf_ident_id := insert_xccdf_ident(ident_sys_id, identifier_in); + exception when dup_val_on_index then + select id + into xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + end; + return xccdf_ident_id; +end lookup_xccdf_ident; +/ +show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.postgresql new file mode 100644 index 0000000..aae5a97 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/107-lookup_xccdf_ident.sql.postgresql @@ -0,0 +1,50 @@ +-- oracle equivalent source sha1 32fa76082aad7e0505467f86e5ed19f5586debe0 + +create or replace function +lookup_xccdf_ident(system_in in varchar, identifier_in in varchar) +returns numeric +as +$$ +declare + xccdf_ident_id numeric; + ident_sys_id numeric; +begin + select id + into ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + if not found then + ident_sys_id := nextval('rhn_xccdf_identsytem_id_seq'); + begin + perform pg_dblink_exec( + 'insert into rhnXccdfIdentSystem (id, system) values (' || + ident_sys_id || ', ' || coalesce(quote_literal(system_in)) || ')'); + exception when unique_violation then + select id + into strict ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + end; + end if; + + select id + into xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + if not found then + xccdf_ident_id := nextval('rhn_xccdf_ident_id_seq'); + begin + perform pg_dblink_exec( + 'insert into rhnXccdfIdent (id, identsystem_id, identifier) values (' || + xccdf_ident_id || ', ' || ident_sys_id || ', ' || + coalesce(quote_literal( identifier_in)) || ')'); + exception when unique_violation then + select id + into strict xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + end; + end if; + return xccdf_ident_id; +end; +$$ language plpgsql immutable;
commit 5c3f958f4bbba92f9e77d963ad60c557202fb861 Author: Milan Zazrivec mzazrivec@redhat.com Date: Wed Feb 29 18:00:37 2012 +0100
use pg_dblink_exec to execute inserts inside lookup_xccdf_ident
diff --git a/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql b/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql index fd82742..3b1ce10 100644 --- a/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql +++ b/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql @@ -12,37 +12,52 @@ -- 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 FUNCTION -lookup_xccdf_ident(system_in IN VARCHAR, identifier_in IN VARCHAR) -RETURNS NUMERIC -AS +create or replace function +lookup_xccdf_ident(system_in in varchar, identifier_in in varchar) +returns numeric +as $$ -DECLARE - xccdf_ident_id NUMERIC; - ident_sys_id NUMERIC; -BEGIN - SELECT id - INTO ident_sys_id - FROM rhnXccdfIdentsystem - WHERE system = system_in; - IF NOT FOUND THEN - INSERT INTO rhnXccdfIdentsystem (id, system) - VALUES (nextval('rhn_xccdf_identsytem_id_seq'), system_in) - RETURNING id INTO ident_sys_id; - END IF; +declare + xccdf_ident_id numeric; + ident_sys_id numeric; +begin + select id + into ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + if not found then + ident_sys_id := nextval('rhn_xccdf_identsytem_id_seq'); + begin + perform pg_dblink_exec( + 'insert into rhnXccdfIdentSystem (id, system) values (' || + ident_sys_id || ', ' || coalesce(quote_literal(system_in)) || ')'); + exception when unique_violation then + select id + into strict ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + end; + end if;
- SELECT id - INTO xccdf_ident_id - FROM rhnXccdfIdent - WHERE identsystem_id = ident_sys_id - AND identifier = identifier_in; - IF NOT FOUND THEN - INSERT INTO rhnXccdfIdent (id, identsystem_id, identifier) - VALUES (nextval('rhn_xccdf_ident_id_seq'), ident_sys_id, identifier_in) - RETURNING id INTO xccdf_ident_id; - END IF; - RETURN xccdf_ident_id; -END; -$$ LANGUAGE PLPGSQL; + select id + into xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + if not found then + xccdf_ident_id := nextval('rhn_xccdf_ident_id_seq'); + begin + perform pg_dblink_exec( + 'insert into rhnXccdfIdent (id, identsystem_id, identifier) values (' || + xccdf_ident_id || ', ' || ident_sys_id || ', ' || + coalesce(quote_literal( identifier_in)) || ')'); + exception when unique_violation then + select id + into strict xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + end; + end if; + return xccdf_ident_id; +end; +$$ language plpgsql immutable; diff --git a/schema/spacewalk/postgres/procs/procs.deps b/schema/spacewalk/postgres/procs/procs.deps index 6e8989a..bd5e893 100644 --- a/schema/spacewalk/postgres/procs/procs.deps +++ b/schema/spacewalk/postgres/procs/procs.deps @@ -105,6 +105,9 @@ lookup_transaction_package :: rhnTransactionOperation lookup_package_name \ pg_dblink_exec lookup_virt_sub_level :: rhnVirtSubLevel lookup_xccdf_benchmark :: rhnXccdfBenchmark pg_dblink_exec +lookup_xccdf_ident :: rhnXccdfIdentSystem \ + rhnXccdfIdent \ + pg_dblink_exec pxt_session_cleanup :: PXTSessions queue_server :: rhnServerNeededPackageCache rhnTaskQueue \ rhnServer \
commit 87a133c568f171bf8baf24a61c077e10b6f90ecd Author: Milan Zazrivec mzazrivec@redhat.com Date: Wed Feb 29 17:53:15 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_xccdf_ident.sql b/schema/spacewalk/oracle/procs/insert_xccdf_ident.sql new file mode 100644 index 0000000..83c9a84 --- /dev/null +++ b/schema/spacewalk/oracle/procs/insert_xccdf_ident.sql @@ -0,0 +1,25 @@ +-- Copyright (c) 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 function insert_xccdf_ident(ident_sys_id number, identifier_in in varchar2) +return number +is + pragma autonomous_transaction; + xccdf_ident_id number; +begin + insert into rhnXccdfIdent (id, identsystem_id, identifier) + values (rhn_xccdf_ident_id_seq.nextval, ident_sys_id, identifier_in) returning id into xccdf_ident_id; + commit; + return xccdf_ident_id; +end; +/ diff --git a/schema/spacewalk/oracle/procs/insert_xccdf_ident_system.sql b/schema/spacewalk/oracle/procs/insert_xccdf_ident_system.sql new file mode 100644 index 0000000..2463311 --- /dev/null +++ b/schema/spacewalk/oracle/procs/insert_xccdf_ident_system.sql @@ -0,0 +1,26 @@ +-- Copyright (c) 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 function insert_xccdf_ident_system(system_in varchar2) +return number +is + pragma autonomous_transaction; + ident_sys_id number; +begin + insert into rhnXccdfIdentSystem (id, system) + values (rhn_xccdf_identsytem_id_seq.nextval, system_in) returning id into ident_sys_id; + commit; + return ident_sys_id; +end; +/ +show errors diff --git a/schema/spacewalk/oracle/procs/lookup_xccdf_ident.sql b/schema/spacewalk/oracle/procs/lookup_xccdf_ident.sql index c63af38..0adb322 100644 --- a/schema/spacewalk/oracle/procs/lookup_xccdf_ident.sql +++ b/schema/spacewalk/oracle/procs/lookup_xccdf_ident.sql @@ -1,4 +1,3 @@ --- -- Copyright (c) 2012 Red Hat, Inc. -- -- This software is licensed to you under the GNU General Public License, @@ -11,41 +10,46 @@ -- 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 FUNCTION -lookup_xccdf_ident(system_in IN VARCHAR2, identifier_in IN VARCHAR2) -RETURN NUMBER -IS - PRAGMA AUTONOMOUS_TRANSACTION; - xccdf_ident_id NUMBER; - ident_sys_id NUMBER; -BEGIN - BEGIN - SELECT id - INTO ident_sys_id - FROM rhnXccdfIdentsystem - WHERE system = system_in; - EXCEPTION - WHEN NO_DATA_FOUND THEN - INSERT INTO rhnXccdfIdentsystem (id, system) - VALUES (rhn_xccdf_identsytem_id_seq.nextval, system_in) - RETURNING id INTO ident_sys_id; - END; +create or replace function +lookup_xccdf_ident(system_in in varchar2, identifier_in in varchar2) +return number +is + pragma autonomous_transaction; + xccdf_ident_id number; + ident_sys_id number; +begin + begin + select id + into ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + exception when no_data_found then + begin + ident_sys_id := insert_xccdf_ident_system(system_in); + exception when dup_val_on_index then + select id + into ident_sys_id + from rhnXccdfIdentSystem + where system = system_in; + end; + end;
- SELECT id - INTO xccdf_ident_id - FROM rhnXccdfIdent - WHERE identsystem_id = ident_sys_id - AND identifier = identifier_in; - RETURN xccdf_ident_id; -EXCEPTION - WHEN NO_DATA_FOUND THEN - INSERT INTO rhnXccdfIdent (id, identsystem_id, identifier) - VALUES (rhn_xccdf_ident_id_seq.nextval, ident_sys_id, identifier_in) - RETURNING id INTO xccdf_ident_id; - COMMIT; - RETURN xccdf_ident_id; -END lookup_xccdf_ident; + select id + into xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + return xccdf_ident_id; +exception when no_data_found then + begin + xccdf_ident_id := insert_xccdf_ident(ident_sys_id, identifier_in); + exception when dup_val_on_index then + select id + into xccdf_ident_id + from rhnXccdfIdent + where identsystem_id = ident_sys_id and identifier = identifier_in; + end; + return xccdf_ident_id; +end lookup_xccdf_ident; / -SHOW ERRORS +show errors diff --git a/schema/spacewalk/oracle/procs/procs.deps b/schema/spacewalk/oracle/procs/procs.deps index 83314a3..b55f698 100644 --- a/schema/spacewalk/oracle/procs/procs.deps +++ b/schema/spacewalk/oracle/procs/procs.deps @@ -69,6 +69,7 @@ insert_tag :: rhnTag insert_tag_name :: rhnTagName insert_transaction_package :: rhnTransactionPackage insert_xccdf_benchmark :: rhnXccdfBenchmark +insert_xccdf_ident_system :: rhnXccdfIdentSystem is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \ rhnUserGroupMembers lookup_arch_type :: data/rhnArchType rhn_exception.pks @@ -115,6 +116,10 @@ lookup_transaction_package :: rhnTransactionOperation lookup_package_name \ insert_transaction_package lookup_virt_sub_level :: rhnVirtSubLevel lookup_xccdf_benchmark :: insert_xccdf_benchmark rhnXccdfBenchmark +lookup_xccdf_ident :: insert_xccdf_ident \ + insert_xccdf_ident_system \ + rhnXccdfIdentSystem \ + rhnXccdfIdent pxt_session_cleanup :: PXTSessions queue_server :: rhnServerNeededPackageCache rhnTaskQueue \ rhnServer \ diff --git a/schema/spacewalk/postgres/procs/insert_xccdf_ident.sql b/schema/spacewalk/postgres/procs/insert_xccdf_ident.sql new file mode 100644 index 0000000..8aa16d2 --- /dev/null +++ b/schema/spacewalk/postgres/procs/insert_xccdf_ident.sql @@ -0,0 +1,2 @@ +-- oracle equivalent source sha1 346f0f7fb2cfcff4b50d79095f640bfe64a4cf54 +-- This file is intentionally left empty. diff --git a/schema/spacewalk/postgres/procs/insert_xccdf_ident_system.sql b/schema/spacewalk/postgres/procs/insert_xccdf_ident_system.sql new file mode 100644 index 0000000..e941e8f --- /dev/null +++ b/schema/spacewalk/postgres/procs/insert_xccdf_ident_system.sql @@ -0,0 +1,2 @@ +-- oracle equivalent source sha1 ec9ba49c1f25d81a100625db72795bba81976913 +-- This file is intentionally left empty. diff --git a/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql b/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql index 663f825..fd82742 100644 --- a/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql +++ b/schema/spacewalk/postgres/procs/lookup_xccdf_ident.sql @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 690bbd47c01d39c135da9a54dbaacb77f7f206bc +-- oracle equivalent source sha1 cf499cee6f4107ee1ae27312cf1b92b8650333eb -- -- Copyright (c) 2012 Red Hat, Inc. --