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(a)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(a)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(a)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.
--