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