schema/spacewalk/oracle/procs/insert_transaction_package.sql
| 30 ++
schema/spacewalk/oracle/procs/lookup_transaction_package.sql
| 122 ++++------
schema/spacewalk/oracle/procs/procs.deps
| 4
schema/spacewalk/postgres/procs/insert_transaction_package.sql
| 2
schema/spacewalk/postgres/procs/lookup_transaction_package.sql
| 111 ++++-----
schema/spacewalk/postgres/procs/procs.deps
| 3
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.oracle
| 76 ++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.postgresql
| 67 +++++
8 files changed, 294 insertions(+), 121 deletions(-)
New commits:
commit 264dd7c1cd60e51e508db74fcbeb2dce797976e5
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 15:40:44 2012 +0100
lookup_transaction_package: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.oracle
new file mode 100644
index 0000000..8baed87
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.oracle
@@ -0,0 +1,76 @@
+create or replace function insert_transaction_package(
+ o_id in number,
+ n_id in number,
+ e_id in number,
+ p_arch_id in number)
+return number
+is
+ pragma autonomous_transaction;
+ tp_id number;
+begin
+ insert into rhnTransactionPackage (id, operation, name_id, evr_id, package_arch_id)
+ values (rhn_transpack_id_seq.nextval, o_id, n_id, e_id, p_arch_id) returning id into
tp_id;
+ commit;
+ return tp_id;
+end;
+/
+show errors
+
+create or replace function
+lookup_transaction_package(
+ o_in in varchar2,
+ n_in in varchar2,
+ e_in in varchar2,
+ v_in in varchar2,
+ r_in in varchar2,
+ a_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ o_id number;
+ n_id number;
+ e_id number;
+ p_arch_id number;
+ tp_id number;
+begin
+ begin
+ select id
+ into o_id
+ from rhnTransactionOperation
+ where label = o_in;
+ exception when no_data_found then
+ rhn_exception.raise_exception('invalid_transaction_operation');
+ end;
+
+ n_id := lookup_package_name(n_in);
+ e_id := lookup_evr(e_in, v_in, r_in);
+ p_arch_id := null;
+
+ if a_in is not null then
+ p_arch_id := lookup_package_arch(a_in);
+ end if;
+
+ select id
+ into tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id is
null));
+ return tp_id;
+exception when no_data_found then
+ begin
+ tp_id := insert_transaction_package(o_id, n_id, e_id, p_arch_id);
+ exception when dup_val_on_index then
+ select id
+ into tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id is
null));
+ end;
+ return tp_id;
+end;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.postgresql
new file mode 100644
index 0000000..de8acfb
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/105-lookup_transaction_package.sql.postgresql
@@ -0,0 +1,67 @@
+-- oracle equivalent source sha1 95bcebe5c82f6192aff713976e6b77cffbba54c3
+
+create or replace function
+lookup_transaction_package(
+ o_in in varchar,
+ n_in in varchar,
+ e_in in varchar,
+ v_in in varchar,
+ r_in in varchar,
+ a_in in varchar)
+returns numeric
+as
+$$
+declare
+ o_id numeric;
+ n_id numeric;
+ e_id numeric;
+ p_arch_id numeric;
+ tp_id numeric;
+begin
+ select id
+ into o_id
+ from rhnTransactionOperation
+ where label = o_in;
+
+ if not found then
+ perform rhn_exception.raise_exception('invalid_transaction_operation');
+ end if;
+
+ n_id := lookup_package_name(n_in);
+ e_id := lookup_evr(e_in, v_in, r_in);
+ p_arch_id := null;
+
+ if a_in is not null then
+ p_arch_id := lookup_package_arch(a_in);
+ end if;
+
+ select id
+ into tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id is
null));
+
+ if not found then
+ tp_id := nextval('rhn_transpack_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnTransactionPackage (id, operation, name_id, evr_id,
package_arch_id)' ||
+ ' values (' || tp_id || ', ' || o_id || ', ' ||
n_id || ', ' || e_id ||
+ ', ' || ', ' || p_arch_id || ')');
+ exception when unique_violation then
+ select id
+ into strict tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id
is null));
+ end;
+ end if;
+
+ return tp_id;
+end;
+$$
+language plpgsql immutable;
commit c918551e94513edbbc95aba67f349fd589a522fa
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 15:36:21 2012 +0100
use pg_dblink_exec to exec insert inside lookup_transaction_package
diff --git a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
index 1cbcfee..599d209 100644
--- a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
+++ b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 5507c4bf3760c813f3f8ffb461a73f5e2903473e
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_transaction_package.sql
--
--- Copyright (c) 2008--2010 Red Hat, Inc.
+-- Copyright (c) 2008--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
@@ -13,61 +12,69 @@
-- 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_TRANSACTION_PACKAGE(o_in IN VARCHAR, n_in IN VARCHAR,
- e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR, a_in IN VARCHAR)
-RETURNS NUMERIC
-AS
+create or replace function
+lookup_transaction_package(
+ o_in in varchar,
+ n_in in varchar,
+ e_in in varchar,
+ v_in in varchar,
+ r_in in varchar,
+ a_in in varchar)
+returns numeric
+as
$$
-DECLARE
- o_id NUMERIC;
- n_id NUMERIC;
- e_id NUMERIC;
- p_arch_id NUMERIC;
- tp_id NUMERIC;
-BEGIN
- SELECT id
- INTO o_id
- FROM rhnTransactionOperation
- WHERE label = o_in;
-
- IF NOT FOUND THEN
- PERFORM rhn_exception.raise_exception('invalid_transaction_operation');
- END IF;
+declare
+ o_id numeric;
+ n_id numeric;
+ e_id numeric;
+ p_arch_id numeric;
+ tp_id numeric;
+begin
+ select id
+ into o_id
+ from rhnTransactionOperation
+ where label = o_in;
- SELECT LOOKUP_PACKAGE_NAME(n_in)
- INTO n_id;
+ if not found then
+ perform rhn_exception.raise_exception('invalid_transaction_operation');
+ end if;
- SELECT LOOKUP_EVR(e_in, v_in, r_in)
- INTO e_id;
+ n_id := lookup_package_name(n_in);
+ e_id := lookup_evr(e_in, v_in, r_in);
+ p_arch_id := null;
- p_arch_id := NULL;
- IF a_in IS NOT NULL
- THEN
- SELECT LOOKUP_PACKAGE_ARCH(a_in)
- INTO p_arch_id;
- END IF;
+ if a_in is not null then
+ p_arch_id := lookup_package_arch(a_in);
+ end if;
- SELECT id
- INTO tp_id
- FROM rhnTransactionPackage
- WHERE operation = o_id
- AND name_id = n_id
- AND evr_id = e_id
- AND (package_arch_id = p_arch_id OR (p_arch_id IS NULL AND package_arch_id IS
NULL));
+ select id
+ into tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id is
null));
- IF NOT FOUND THEN
- INSERT INTO rhnTransactionPackage
- (id, operation, name_id, evr_id, package_arch_id) VALUES
(nextval('rhn_transpack_id_seq'), o_id, n_id, e_id, p_arch_id);
- tp_id := currval('rhn_transpack_id_seq');
- END IF;
+ if not found then
+ tp_id := nextval('rhn_transpack_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnTransactionPackage (id, operation, name_id, evr_id,
package_arch_id)' ||
+ ' values (' || tp_id || ', ' || o_id || ', ' ||
n_id || ', ' || e_id ||
+ ', ' || ', ' || p_arch_id || ')');
+ exception when unique_violation then
+ select id
+ into strict tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id
is null));
+ end;
+ end if;
- RETURN tp_id;
-END;
+ return tp_id;
+end;
$$
-LANGUAGE PLPGSQL;
+language plpgsql immutable;
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index aa0c1a3..5fa70c3 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -101,7 +101,8 @@ lookup_tag :: rhnTag lookup_tag_name
pg_dblink_exec
lookup_tag_name :: rhnTagName pg_dblink_exec
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
lookup_evr lookup_package_arch \
- rhnTransactionPackage rhn_exception.pks
+ rhnTransactionPackage rhn_exception.pks \
+ pg_dblink_exec
lookup_virt_sub_level :: rhnVirtSubLevel
pxt_session_cleanup :: PXTSessions
queue_server :: rhnServerNeededPackageCache rhnTaskQueue \
commit 9f37a6295f0fc98f6acd50c9a63c2266bbdf940d
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 14:14:16 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_transaction_package.sql
b/schema/spacewalk/oracle/procs/insert_transaction_package.sql
new file mode 100644
index 0000000..ca4510e
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_transaction_package.sql
@@ -0,0 +1,30 @@
+-- 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_transaction_package(
+ o_id in number,
+ n_id in number,
+ e_id in number,
+ p_arch_id in number)
+return number
+is
+ pragma autonomous_transaction;
+ tp_id number;
+begin
+ insert into rhnTransactionPackage (id, operation, name_id, evr_id, package_arch_id)
+ values (rhn_transpack_id_seq.nextval, o_id, n_id, e_id, p_arch_id) returning id into
tp_id;
+ commit;
+ return tp_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/lookup_transaction_package.sql
b/schema/spacewalk/oracle/procs/lookup_transaction_package.sql
index 2e0b4d2..2312b68 100644
--- a/schema/spacewalk/oracle/procs/lookup_transaction_package.sql
+++ b/schema/spacewalk/oracle/procs/lookup_transaction_package.sql
@@ -1,5 +1,4 @@
---
--- Copyright (c) 2008 Red Hat, Inc.
+-- Copyright (c) 2008-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
@@ -11,73 +10,62 @@
-- 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_TRANSACTION_PACKAGE(o_in IN VARCHAR2, n_in IN VARCHAR2,
- e_in IN VARCHAR2, v_in IN VARCHAR2, r_in IN VARCHAR2, a_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- o_id NUMBER;
- n_id NUMBER;
- e_id NUMBER;
- p_arch_id NUMBER;
- tp_id NUMBER;
-BEGIN
- BEGIN
- SELECT id
- INTO o_id
- FROM rhnTransactionOperation
- WHERE label = o_in;
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- rhn_exception.raise_exception('invalid_transaction_operation');
- END;
+create or replace function
+lookup_transaction_package(
+ o_in in varchar2,
+ n_in in varchar2,
+ e_in in varchar2,
+ v_in in varchar2,
+ r_in in varchar2,
+ a_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ o_id number;
+ n_id number;
+ e_id number;
+ p_arch_id number;
+ tp_id number;
+begin
+ begin
+ select id
+ into o_id
+ from rhnTransactionOperation
+ where label = o_in;
+ exception when no_data_found then
+ rhn_exception.raise_exception('invalid_transaction_operation');
+ end;
- SELECT LOOKUP_PACKAGE_NAME(n_in)
- INTO n_id
- FROM dual;
+ n_id := lookup_package_name(n_in);
+ e_id := lookup_evr(e_in, v_in, r_in);
+ p_arch_id := null;
- SELECT LOOKUP_EVR(e_in, v_in, r_in)
- INTO e_id
- FROM dual;
+ if a_in is not null then
+ p_arch_id := lookup_package_arch(a_in);
+ end if;
- p_arch_id := NULL;
- IF a_in IS NOT NULL
- THEN
- SELECT LOOKUP_PACKAGE_ARCH(a_in)
- INTO p_arch_id
- FROM dual;
- END IF;
-
- SELECT id
- INTO tp_id
- FROM rhnTransactionPackage
- WHERE operation = o_id
- AND name_id = n_id
- AND evr_id = e_id
- AND (package_arch_id = p_arch_id OR (p_arch_id IS NULL AND package_arch_id IS
NULL));
- RETURN tp_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- INSERT INTO rhnTransactionPackage
- (id, operation, name_id, evr_id, package_arch_id)
- VALUES (rhn_transpack_id_seq.nextval, o_id, n_id, e_id, p_arch_id)
- RETURNING id INTO tp_id;
- COMMIT;
- RETURN tp_id;
-END;
+ select id
+ into tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id is
null));
+ return tp_id;
+exception when no_data_found then
+ begin
+ tp_id := insert_transaction_package(o_id, n_id, e_id, p_arch_id);
+ exception when dup_val_on_index then
+ select id
+ into tp_id
+ from rhnTransactionPackage
+ where operation = o_id and
+ name_id = n_id and
+ evr_id = e_id and
+ (package_arch_id = p_arch_id or (p_arch_id is null and package_arch_id is
null));
+ end;
+ return tp_id;
+end;
/
-SHOW ERRORS
-
---
--- Revision 1.2 2003/07/09 16:29:33 cturner
--- update some strings and change lookup_transaction_package to throw an exception
instead of SILENTLY RETURNING NULL
---
--- Revision 1.1 2003/07/01 14:52:35 misa
--- bugzilla: 90376 Need function to add transactions in the DB
---
+show errors
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index 3d38bc9..26c1676 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -67,6 +67,7 @@ insert_package_nevra :: rhnPackageNEVRA
insert_source_name :: rhnSourceRPM
insert_tag :: rhnTag
insert_tag_name :: rhnTagName
+insert_transaction_package :: rhnTransactionPackage
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -109,7 +110,8 @@ lookup_tag :: rhnTag lookup_tag_name insert_tag
lookup_tag_name :: rhnTagName insert_tag_name
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
lookup_evr lookup_package_arch \
- rhnTransactionPackage rhn_exception.pks
+ rhnTransactionPackage rhn_exception.pks \
+ insert_transaction_package
lookup_virt_sub_level :: rhnVirtSubLevel
pxt_session_cleanup :: PXTSessions
queue_server :: rhnServerNeededPackageCache rhnTaskQueue \
diff --git a/schema/spacewalk/postgres/procs/insert_transaction_package.sql
b/schema/spacewalk/postgres/procs/insert_transaction_package.sql
new file mode 100644
index 0000000..6142bba
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_transaction_package.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 c6072b2270204eee9312f4f43bfc3c0c7eb4dc4e
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
index 8eade8b..1cbcfee 100644
--- a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
+++ b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 ab85631a39989d1c81a089955d8a10f3b266bc98
+-- oracle equivalent source sha1 5507c4bf3760c813f3f8ffb461a73f5e2903473e
-- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_transaction_package.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.