schema/spacewalk/oracle/procs/insert_xccdf_benchmark.sql | 26 +++++ schema/spacewalk/oracle/procs/lookup_xccdf_benchmark.sql | 48 ++++----- schema/spacewalk/oracle/procs/procs.deps | 2 schema/spacewalk/postgres/procs/insert_xccdf_benchmark.sql | 2 schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql | 52 +++++----- schema/spacewalk/postgres/procs/procs.deps | 1 schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.oracle | 38 +++++++ schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.postgresql | 34 ++++++ 8 files changed, 156 insertions(+), 47 deletions(-)
New commits: commit 0ded4b65685a039710d04c2607ba6dc5e5d5814b Author: Milan Zazrivec mzazrivec@redhat.com Date: Wed Feb 29 17:28:41 2012 +0100
lookup_xccdf_benchmark: schema upgrade
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.oracle new file mode 100644 index 0000000..45c6c80 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.oracle @@ -0,0 +1,38 @@ +create or replace function insert_xccdf_benchmark(identifier_in in varchar2, version_in in varchar2) +return number +is + pragma autonomous_transaction; + benchmark_id number; +begin + insert into rhnXccdfBenchmark (id, identifier, version) + values (rhn_xccdf_benchmark_id_seq.nextval, identifier_in, version_in) returning id into benchmark_id; + commit; + return benchmark_id; +end; +/ +show errors + +create or replace function +lookup_xccdf_benchmark(identifier_in in varchar2, version_in in varchar2) +return number +is + benchmark_id number; +begin + select id + into benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + return benchmark_id; +exception when no_data_found then + begin + benchmark_id := insert_xccdf_benchmark(identifier_in, version_in); + exception when dup_val_on_index then + select id + into benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + end; + return benchmark_id; +end lookup_xccdf_benchmark; +/ +show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.postgresql new file mode 100644 index 0000000..a042695 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/106-lookup_xccdf_benchmark.sql.postgresql @@ -0,0 +1,34 @@ +-- oracle equivalent source sha1 3dabe623b63fab475d78d78d1169b2b380f14655 + +create or replace function +lookup_xccdf_benchmark(identifier_in in varchar, version_in in varchar) +returns numeric +as +$$ +declare + benchmark_id numeric; +begin + select id + into benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + + if not found then + benchmark_id := nextval('rhn_xccdf_benchmark_id_seq'); + begin + perform pg_dblink_exec( + 'insert into rhnXccdfBenchmark (id, identifier, version) values (' || + benchmark_id || ', ' || + coalesce(quote_literal(identifier_in), 'NULL') || ', ' || + coalesce(quote_literal(version_in), 'NULL') || ')'); + exception when unique_violation then + select id + into strict benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + end; + end if; + + return benchmark_id; +end; +$$ language plpgsql immutable;
commit 70974d5081e791d19f25782aecc0da18a899ff84 Author: Milan Zazrivec mzazrivec@redhat.com Date: Wed Feb 29 17:26:55 2012 +0100
use pg_dblink_exec to execute insert inside lookup_xccdf_benchmark
diff --git a/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql b/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql index 8407454..0b168b2 100644 --- a/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql +++ b/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql @@ -14,27 +14,35 @@ -- in this software or its documentation. --
-CREATE OR REPLACE FUNCTION -lookup_xccdf_benchmark(identifier_in IN VARCHAR, version_in IN VARCHAR) -RETURNS NUMERIC -AS +create or replace function +lookup_xccdf_benchmark(identifier_in in varchar, version_in in varchar) +returns numeric +as $$ -DECLARE - benchmark_id NUMERIC; -BEGIN - SELECT id - INTO benchmark_id - FROM rhnXccdfBenchmark - WHERE identifier = identifier_in - AND version = version_in; +declare + benchmark_id numeric; +begin + select id + into benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in;
- IF NOT FOUND THEN - INSERT INTO rhnXccdfBenchmark (id, identifier, version) - VALUES (nextval('rhn_xccdf_benchmark_id_seq'), - identifier_in, version_in) - RETURNING id INTO benchmark_id; - END IF; + if not found then + benchmark_id := nextval('rhn_xccdf_benchmark_id_seq'); + begin + perform pg_dblink_exec( + 'insert into rhnXccdfBenchmark (id, identifier, version) values (' || + benchmark_id || ', ' || + coalesce(quote_literal(identifier_in), 'NULL') || ', ' || + coalesce(quote_literal(version_in), 'NULL') || ')'); + exception when unique_violation then + select id + into strict benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + end; + end if;
- RETURN benchmark_id; -END; -$$ LANGUAGE PLPGSQL; + return benchmark_id; +end; +$$ language plpgsql immutable; diff --git a/schema/spacewalk/postgres/procs/procs.deps b/schema/spacewalk/postgres/procs/procs.deps index 5fa70c3..6e8989a 100644 --- a/schema/spacewalk/postgres/procs/procs.deps +++ b/schema/spacewalk/postgres/procs/procs.deps @@ -104,6 +104,7 @@ lookup_transaction_package :: rhnTransactionOperation lookup_package_name \ rhnTransactionPackage rhn_exception.pks \ pg_dblink_exec lookup_virt_sub_level :: rhnVirtSubLevel +lookup_xccdf_benchmark :: rhnXccdfBenchmark pg_dblink_exec pxt_session_cleanup :: PXTSessions queue_server :: rhnServerNeededPackageCache rhnTaskQueue \ rhnServer \
commit 6de2bc15684a36cd5efbcf75e0e8b9ee14595ad0 Author: Milan Zazrivec mzazrivec@redhat.com Date: Wed Feb 29 17:21:17 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_xccdf_benchmark.sql b/schema/spacewalk/oracle/procs/insert_xccdf_benchmark.sql new file mode 100644 index 0000000..8eefde0 --- /dev/null +++ b/schema/spacewalk/oracle/procs/insert_xccdf_benchmark.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_benchmark(identifier_in in varchar2, version_in in varchar2) +return number +is + pragma autonomous_transaction; + benchmark_id number; +begin + insert into rhnXccdfBenchmark (id, identifier, version) + values (rhn_xccdf_benchmark_id_seq.nextval, identifier_in, version_in) returning id into benchmark_id; + commit; + return benchmark_id; +end; +/ +show errors diff --git a/schema/spacewalk/oracle/procs/lookup_xccdf_benchmark.sql b/schema/spacewalk/oracle/procs/lookup_xccdf_benchmark.sql index 71addd5..e4a584a 100644 --- a/schema/spacewalk/oracle/procs/lookup_xccdf_benchmark.sql +++ b/schema/spacewalk/oracle/procs/lookup_xccdf_benchmark.sql @@ -1,4 +1,3 @@ --- -- Copyright (c) 2012 Red Hat, Inc. -- -- This software is licensed to you under the GNU General Public License, @@ -11,29 +10,28 @@ -- 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_benchmark(identifier_in IN VARCHAR2, version_in IN VARCHAR2) -RETURN NUMBER -IS - PRAGMA AUTONOMOUS_TRANSACTION; - benchmark_id NUMBER; -BEGIN - SELECT id - INTO benchmark_id - FROM rhnXccdfBenchmark - WHERE identifier = identifier_in - AND version = version_in; - RETURN benchmark_id; -EXCEPTION - WHEN NO_DATA_FOUND THEN - INSERT INTO rhnXccdfBenchmark (id, identifier, version) - VALUES (rhn_xccdf_benchmark_id_seq.nextval, - identifier_in, version_in) - RETURNING id INTO benchmark_id; - COMMIT; - RETURN benchmark_id; -END lookup_xccdf_benchmark; +create or replace function +lookup_xccdf_benchmark(identifier_in in varchar2, version_in in varchar2) +return number +is + benchmark_id number; +begin + select id + into benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + return benchmark_id; +exception when no_data_found then + begin + benchmark_id := insert_xccdf_benchmark(identifier_in, version_in); + exception when dup_val_on_index then + select id + into benchmark_id + from rhnXccdfBenchmark + where identifier = identifier_in and version = version_in; + end; + return benchmark_id; +end lookup_xccdf_benchmark; / -SHOW ERRORS +show errors diff --git a/schema/spacewalk/oracle/procs/procs.deps b/schema/spacewalk/oracle/procs/procs.deps index 26c1676..83314a3 100644 --- a/schema/spacewalk/oracle/procs/procs.deps +++ b/schema/spacewalk/oracle/procs/procs.deps @@ -68,6 +68,7 @@ insert_source_name :: rhnSourceRPM insert_tag :: rhnTag insert_tag_name :: rhnTagName insert_transaction_package :: rhnTransactionPackage +insert_xccdf_benchmark :: rhnXccdfBenchmark is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \ rhnUserGroupMembers lookup_arch_type :: data/rhnArchType rhn_exception.pks @@ -113,6 +114,7 @@ lookup_transaction_package :: rhnTransactionOperation lookup_package_name \ rhnTransactionPackage rhn_exception.pks \ insert_transaction_package lookup_virt_sub_level :: rhnVirtSubLevel +lookup_xccdf_benchmark :: insert_xccdf_benchmark rhnXccdfBenchmark pxt_session_cleanup :: PXTSessions queue_server :: rhnServerNeededPackageCache rhnTaskQueue \ rhnServer \ diff --git a/schema/spacewalk/postgres/procs/insert_xccdf_benchmark.sql b/schema/spacewalk/postgres/procs/insert_xccdf_benchmark.sql new file mode 100644 index 0000000..377d76b --- /dev/null +++ b/schema/spacewalk/postgres/procs/insert_xccdf_benchmark.sql @@ -0,0 +1,2 @@ +-- oracle equivalent source sha1 4202a766e12fa3137a822d20683ccabe897631a1 +-- This file is intentionally left empty. diff --git a/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql b/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql index cb89ba0..8407454 100644 --- a/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql +++ b/schema/spacewalk/postgres/procs/lookup_xccdf_benchmark.sql @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 85e65cbb76b55a91cae4f2103f5c95b4785062e0 +-- oracle equivalent source sha1 9491baee899b1ae99ac039939eed51f7b5bc0342 -- -- Copyright (c) 2012 Red Hat, Inc. --
spacewalk-commits@lists.fedorahosted.org