schema/spacewalk/postgres/procs/lookup_transaction_package.sql
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.oracle
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.postgresql
| 80 ++++++++++
3 files changed, 82 insertions(+), 1 deletion(-)
New commits:
commit ee67a9217af643af345be6aab7e912fb9719dc83
Merge: e7a98e7 cc27b05
Author: Tomas Kasparek <tkasparek(a)redhat.com>
Date: Mon Jul 23 11:34:03 2012 +0200
Merge branch 'master' of
ssh://git.fedorahosted.org/git/spacewalk
commit e7a98e7e14aaf075ebdbe77c1bde93c733b32584
Author: Tomas Kasparek <tkasparek(a)redhat.com>
Date: Mon Jul 23 11:30:34 2012 +0200
Preventing whole SQL string from being NULL
which caused:
ERROR: insert or update on table "rhnpackagedeltaelement" violates foreign
key constraint "rhn_pdelement_tpid_fk"
DETAIL: Key (transaction_package_id)=(84) is not present in table
"rhntransactionpackage".
STATEMENT:
insert into rhnPackageDeltaElement
(package_delta_id, transaction_package_id)
values
(26,
lookup_transaction_package(E'insert', E'apr',
NULL, E'1.3.9', E'3.el6_1.2', NULL))
diff --git a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
index f07a703..1f0f732 100644
--- a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
+++ b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
@@ -62,7 +62,7 @@ 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 || ')');
+ ', ' || ( case when p_arch_id is null then 'NULL' else
p_arch_id::varchar end ) || ')');
exception when unique_violation then
select id
into strict tp_id
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.oracle
new file mode 100644
index 0000000..b7b00b1
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.oracle
@@ -0,0 +1 @@
+-- This file is intentionally left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.postgresql
new file mode 100644
index 0000000..272e3a6
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.7-to-spacewalk-schema-1.8/113-lookup_transaction_package.sql.postgresql
@@ -0,0 +1,80 @@
+-- oracle equivalent source sha1 b14267384bc104605623a41b755e68e0103b5aa8
+--
+-- 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
+-- 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
+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 ||
+ ', ' || ( case when p_arch_id is null then 'NULL' else
p_arch_id::varchar end ) || ')');
+ 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;