schema/spacewalk/oracle/procs/insert_xccdf_profile.sql
| 27 +++++
schema/spacewalk/oracle/procs/lookup_xccdf_profile.sql
| 48 ++++-----
schema/spacewalk/oracle/procs/procs.deps
| 3
schema/spacewalk/postgres/procs/insert_xccdf_profile.sql
| 2
schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
| 52 +++++-----
schema/spacewalk/postgres/procs/procs.deps
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.oracle
| 39 +++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.postgresql
| 34 ++++++
8 files changed, 160 insertions(+), 47 deletions(-)
New commits:
commit 989bf5ca4a5edfe0b716100f4e39f182d878b3d0
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Thu Mar 1 11:23:05 2012 +0100
lookup_xccdf_profile: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.oracle
new file mode 100644
index 0000000..af34125
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.oracle
@@ -0,0 +1,39 @@
+create or replace function
+insert_xccdf_profile(identifier_in in varchar2, title_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ profile_id number;
+begin
+ insert into rhnXccdfProfile (id, identifier, title)
+ values (rhn_xccdf_profile_id_seq.nextval, identifier_in, title_in) returning id into
profile_id;
+ commit;
+ return profile_id;
+end;
+/
+show errors
+
+create or replace function
+lookup_xccdf_profile(identifier_in in varchar2, title_in in varchar2)
+return number
+is
+ profile_id number;
+begin
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+ return profile_id;
+exception when no_data_found then
+ begin
+ profile_id := insert_xccdf_profile(identifier_in, title_in);
+ exception when dup_val_on_index then
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+ end;
+ return profile_id;
+end lookup_xccdf_profile;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.postgresql
new file mode 100644
index 0000000..1cb8a02
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/108-lookup_xccdf_profile.sql.postgresql
@@ -0,0 +1,34 @@
+-- oracle equivalent source sha1 cbf75718e61e14bc1e042d56b023ed99ff74ff1c
+
+create or replace function
+lookup_xccdf_profile(identifier_in in varchar, title_in in varchar)
+returns numeric
+as
+$$
+declare
+ profile_id numeric;
+begin
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+
+ if not found then
+ profile_id := nextval('rhn_xccdf_profile_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnXccdfProfile (id, identifier, title) values ('
||
+ profile_id || ', ' ||
+ coalesce(quote_literal(identifier_in), 'NULL') || ', '
||
+ coalesce(quote_literal(title_in), 'NULL') || ')' );
+ exception when unique_violation then
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+ end;
+ end if;
+
+ return profile_id;
+end;
+$$ language plpgsql;
commit cbbbc3f97ecf5b343427074d579e9ccbc22e68a3
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Thu Mar 1 11:18:21 2012 +0100
use pg_dblink_exec to execute insert inside lookup_xccdf_profile
diff --git a/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
b/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
index a930f44..8644be8 100644
--- a/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
+++ b/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
@@ -14,27 +14,35 @@
-- in this software or its documentation.
--
-CREATE OR REPLACE FUNCTION
-lookup_xccdf_profile(identifier_in IN VARCHAR, title_in IN VARCHAR)
-RETURNS NUMERIC
-AS
+create or replace function
+lookup_xccdf_profile(identifier_in in varchar, title_in in varchar)
+returns numeric
+as
$$
-DECLARE
- profile_id NUMERIC;
-BEGIN
- SELECT id
- INTO profile_id
- FROM rhnXccdfProfile
- WHERE identifier = identifier_in
- AND title = title_in;
+declare
+ profile_id numeric;
+begin
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
- IF NOT FOUND THEN
- INSERT INTO rhnXccdfProfile (id, identifier, title)
- VALUES (nextval('rhn_xccdf_profile_id_seq'),
- identifier_in, title_in)
- RETURNING id INTO profile_id;
- END IF;
+ if not found then
+ profile_id := nextval('rhn_xccdf_profile_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnXccdfProfile (id, identifier, title) values ('
||
+ profile_id || ', ' ||
+ coalesce(quote_literal(identifier_in), 'NULL') || ', '
||
+ coalesce(quote_literal(title_in), 'NULL') || ')' );
+ exception when unique_violation then
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+ end;
+ end if;
- RETURN profile_id;
-END;
-$$ LANGUAGE PLPGSQL;
+ return profile_id;
+end;
+$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index bd5e893..06f303d 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -108,6 +108,8 @@ lookup_xccdf_benchmark :: rhnXccdfBenchmark pg_dblink_exec
lookup_xccdf_ident :: rhnXccdfIdentSystem \
rhnXccdfIdent \
pg_dblink_exec
+lookup_xccdf_profile :: rhnXccdfProfile \
+ pg_dblink_exec
pxt_session_cleanup :: PXTSessions
queue_server :: rhnServerNeededPackageCache rhnTaskQueue \
rhnServer \
commit 6811a60ea283408078f1ca9d80f4830c143c0098
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Thu Mar 1 11:10:27 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_xccdf_profile.sql
b/schema/spacewalk/oracle/procs/insert_xccdf_profile.sql
new file mode 100644
index 0000000..9bb528d
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_xccdf_profile.sql
@@ -0,0 +1,27 @@
+-- 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_profile(identifier_in in varchar2, title_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ profile_id number;
+begin
+ insert into rhnXccdfProfile (id, identifier, title)
+ values (rhn_xccdf_profile_id_seq.nextval, identifier_in, title_in) returning id into
profile_id;
+ commit;
+ return profile_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/lookup_xccdf_profile.sql
b/schema/spacewalk/oracle/procs/lookup_xccdf_profile.sql
index abbf837..1ccacdf 100644
--- a/schema/spacewalk/oracle/procs/lookup_xccdf_profile.sql
+++ b/schema/spacewalk/oracle/procs/lookup_xccdf_profile.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_profile(identifier_in IN VARCHAR2, title_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- profile_id NUMBER;
-BEGIN
- SELECT id
- INTO profile_id
- FROM rhnXccdfProfile
- WHERE identifier = identifier_in
- AND title = title_in;
- RETURN profile_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- INSERT INTO rhnXccdfProfile (id, identifier, title)
- VALUES (rhn_xccdf_profile_id_seq.nextval,
- identifier_in, title_in)
- RETURNING id INTO profile_id;
- COMMIT;
- RETURN profile_id;
-END lookup_xccdf_profile;
+create or replace function
+lookup_xccdf_profile(identifier_in in varchar2, title_in in varchar2)
+return number
+is
+ profile_id number;
+begin
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+ return profile_id;
+exception when no_data_found then
+ begin
+ profile_id := insert_xccdf_profile(identifier_in, title_in);
+ exception when dup_val_on_index then
+ select id
+ into profile_id
+ from rhnXccdfProfile
+ where identifier = identifier_in and title = title_in;
+ end;
+ return profile_id;
+end lookup_xccdf_profile;
/
-SHOW ERRORS
+show errors
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index b55f698..eb44188 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -70,6 +70,7 @@ insert_tag_name :: rhnTagName
insert_transaction_package :: rhnTransactionPackage
insert_xccdf_benchmark :: rhnXccdfBenchmark
insert_xccdf_ident_system :: rhnXccdfIdentSystem
+insert_xccdf_profile :: rhnXccdfProfile
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -120,6 +121,8 @@ lookup_xccdf_ident :: insert_xccdf_ident \
insert_xccdf_ident_system \
rhnXccdfIdentSystem \
rhnXccdfIdent
+lookup_xccdf_profile :: insert_xccdf_profile \
+ rhnXccdfProfile
pxt_session_cleanup :: PXTSessions
queue_server :: rhnServerNeededPackageCache rhnTaskQueue \
rhnServer \
diff --git a/schema/spacewalk/postgres/procs/insert_xccdf_profile.sql
b/schema/spacewalk/postgres/procs/insert_xccdf_profile.sql
new file mode 100644
index 0000000..7d74f15
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_xccdf_profile.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 6890e6ed27e8fae8e53cc2f6648efe05d6cf5340
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
b/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
index 4c0e49d..a930f44 100644
--- a/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
+++ b/schema/spacewalk/postgres/procs/lookup_xccdf_profile.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 883bbbca80021e8954b5ecc532bed962416807ad
+-- oracle equivalent source sha1 9d091d9787a9a3353e6e1b6d13b430f836dfad89
--
-- Copyright (c) 2012 Red Hat, Inc.
--