schema/spacewalk/oracle/procs/insert_evr.sql
| 29 ++++
schema/spacewalk/oracle/procs/lookup_evr.sql
| 63 ++++-----
schema/spacewalk/oracle/procs/procs.deps
| 3
schema/spacewalk/postgres/procs/insert_evr.sql
| 2
schema/spacewalk/postgres/procs/lookup_evr.sql
| 64 ++++++----
schema/spacewalk/postgres/procs/procs.deps
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.oracle
| 30 ++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.postgresql
| 56 ++++++++
8 files changed, 189 insertions(+), 60 deletions(-)
New commits:
commit 8e1341fe430ae3201ef5db8793897d6bd2a4f5f9
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 12:26:27 2012 +0100
lookup_evr: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.oracle
new file mode 100644
index 0000000..ac2a8af
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.oracle
@@ -0,0 +1,30 @@
+create or replace function
+lookup_evr(e_in in varchar2, v_in in varchar2, r_in in varchar2)
+return number
+is
+ evr_id number;
+begin
+ select id
+ into evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
+
+ return evr_id;
+exception when no_data_found then
+ begin
+ evr_id := insert_evr(e_in, v_in, r_in);
+ exception when dup_val_on_index then
+ select id
+ into evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
+ end;
+
+ return evr_id;
+end;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.postgresql
new file mode 100644
index 0000000..ab27070
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/096-lookup_evr.sql.postgresql
@@ -0,0 +1,56 @@
+-- oracle equivalent source sha1 d6056fb46cefe089db3dead9e49d58defa74cd08
+--
+-- 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_evr(e_in in varchar, v_in in varchar, r_in in varchar)
+returns numeric
+as
+$$
+declare
+ evr_id numeric;
+begin
+ select id
+ into evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
+
+ if not found then
+ evr_id := nextval('rhn_pkg_evr_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageEVR(id, epoch, version, release, evr) values
(' ||
+ evr_id || ', ' ||
+ coalesce(quote_literal(e_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(v_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(r_in), 'NULL') || ', ' ||
+ evr_t(coalesce(quote_literal(e_in), 'NULL'),
+ coalesce(quote_literal(v_in), 'NULL'),
+ coalesce(quote_literal(r_in), 'NULL')) || ')'
+ );
+ exception when unique_violation then
+ select id
+ into strict evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
+ end;
+ end if;
+
+ return evr_id;
+end;
+$$ language plpgsql immutable;
commit 54c9ad233d116ea5c311305cf09af891979364ba
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 12:24:24 2012 +0100
use pg_dblink_exec to execute insert inside lookup_evr
diff --git a/schema/spacewalk/postgres/procs/lookup_evr.sql
b/schema/spacewalk/postgres/procs/lookup_evr.sql
index 42b1198..1952e4d 100644
--- a/schema/spacewalk/postgres/procs/lookup_evr.sql
+++ b/schema/spacewalk/postgres/procs/lookup_evr.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 741172a1f8f23e20540fd2b5dd50a5b193e6cab3
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_evr.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,28 +12,45 @@
-- 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_EVR(e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR)
-RETURNS NUMERIC
-AS
+create or replace function
+lookup_evr(e_in in varchar, v_in in varchar, r_in in varchar)
+returns numeric
+as
$$
-DECLARE
- evr_id NUMERIC;
-BEGIN
- SELECT id INTO evr_id
- FROM rhnPackageEvr
- WHERE ((epoch IS NULL and e_in IS NULL) OR (epoch = e_in))
- AND version = v_in AND release = r_in;
+declare
+ evr_id numeric;
+begin
+ select id
+ into evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
- IF NOT FOUND THEN
- INSERT INTO rhnPackageEvr (id, epoch, version, release, evr)
- VALUES (nextval('rhn_pkg_evr_seq'), e_in, v_in, r_in,EVR_T(e_in,
v_in, r_in));
-
- evr_id := currval('rhn_pkg_evr_seq');
- END IF;
+ if not found then
+ evr_id := nextval('rhn_pkg_evr_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageEVR(id, epoch, version, release, evr) values
(' ||
+ evr_id || ', ' ||
+ coalesce(quote_literal(e_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(v_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(r_in), 'NULL') || ', ' ||
+ evr_t(coalesce(quote_literal(e_in), 'NULL'),
+ coalesce(quote_literal(v_in), 'NULL'),
+ coalesce(quote_literal(r_in), 'NULL')) || ')'
+ );
+ exception when unique_violation then
+ select id
+ into strict evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
+ end;
+ end if;
- RETURN evr_id;
-END;
-$$ LANGUAGE PLPGSQL;
+ return evr_id;
+end;
+$$ language plpgsql immutable;
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index 49a446e..6b34819 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -69,7 +69,7 @@ lookup_config_filename :: pg_dblink_exec
lookup_config_info :: pg_dblink_exec
lookup_cve :: rhnCVE pg_dblink_exec
lookup_erratafile_type :: rhnErrataFileType data/rhnErrataFileType
-lookup_evr :: rhnPackageEVR
+lookup_evr :: rhnPackageEVR pg_dblink_exec
lookup_feature_type :: rhnFeature
lookup_first_matching_cf :: rhnConfigFile rhnConfigChannel \
rhnServerConfigChannel lookup_cf_state \
commit fda792c1c034e0b67bd19a3e855860203ef56972
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 12:12:28 2012 +0100
lookup_evr: use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_evr.sql
b/schema/spacewalk/oracle/procs/insert_evr.sql
new file mode 100644
index 0000000..4f5fec2
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_evr.sql
@@ -0,0 +1,29 @@
+-- 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_evr(e_in in varchar2, v_in in varchar2, r_in in
varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ evr_id number;
+begin
+ insert into rhnPackageEVR(id, epoch, version, release, evr)
+ values (rhn_pkg_evr_seq.nextval,
+ e_in,
+ v_in,
+ r_in,
+ evr_t(e_in, v_in, r_in)) returning id into evr_id;
+ commit;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/lookup_evr.sql
b/schema/spacewalk/oracle/procs/lookup_evr.sql
index 57f8e43..d0e6f00 100644
--- a/schema/spacewalk/oracle/procs/lookup_evr.sql
+++ b/schema/spacewalk/oracle/procs/lookup_evr.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,38 +10,34 @@
-- 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_EVR(e_in IN VARCHAR2, v_in IN VARCHAR2, r_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- evr_id NUMBER;
-BEGIN
- SELECT id INTO evr_id
- FROM rhnPackageEvr
- WHERE ((epoch IS NULL and e_in IS NULL) OR (epoch = e_in))
- AND version = v_in AND release = r_in;
+create or replace function
+lookup_evr(e_in in varchar2, v_in in varchar2, r_in in varchar2)
+return number
+is
+ evr_id number;
+begin
+ select id
+ into evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
- RETURN evr_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- INSERT INTO rhnPackageEvr (id, epoch, version, release, evr)
- VALUES (rhn_pkg_evr_seq.nextval, e_in, v_in, r_in,
- EVR_T(e_in, v_in, r_in))
- RETURNING id INTO evr_id;
- COMMIT;
- RETURN evr_id;
-END;
-/
-SHOW ERRORS
+ return evr_id;
+exception when no_data_found then
+ begin
+ evr_id := insert_evr(e_in, v_in, r_in);
+ exception when dup_val_on_index then
+ select id
+ into evr_id
+ from rhnPackageEVR
+ where ((epoch is null and e_in is null) or (epoch = e_in)) and
+ version = v_in and
+ release = r_in;
+ end;
---
--- Revision 1.3 2002/05/13 22:53:38 pjones
--- cvs id/log
--- some (note enough) readability fixes
---
+ return evr_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index 1e72734..1691918 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -62,6 +62,7 @@ insert_client_capability :: rhnClientCapabilityName
insert_checksum :: rhnChecksum rhnChecksumType
insert_config_filename :: rhnConfigFileName
insert_config_info :: rhnConfigInfo
+insert_evr :: rhnPackageEVR
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -74,7 +75,7 @@ lookup_config_filename :: insert_config_filename
lookup_config_info :: insert_config_info
lookup_cve :: rhnCVE insert_cve
lookup_erratafile_type :: rhnErrataFileType data/rhnErrataFileType
-lookup_evr :: rhnPackageEVR
+lookup_evr :: rhnPackageEVR insert_evr
lookup_feature_type :: rhnFeature
lookup_first_matching_cf :: rhnConfigFile rhnConfigChannel \
rhnServerConfigChannel lookup_cf_state \
diff --git a/schema/spacewalk/postgres/procs/insert_evr.sql
b/schema/spacewalk/postgres/procs/insert_evr.sql
new file mode 100644
index 0000000..fdd7cec
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_evr.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 873017e2b56a8c100659f47d5158fcb5e0f936dd
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_evr.sql
b/schema/spacewalk/postgres/procs/lookup_evr.sql
index ac3e849..42b1198 100644
--- a/schema/spacewalk/postgres/procs/lookup_evr.sql
+++ b/schema/spacewalk/postgres/procs/lookup_evr.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 72b90c220b6786603547c31360870fcfeadeed9a
+-- oracle equivalent source sha1 741172a1f8f23e20540fd2b5dd50a5b193e6cab3
-- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_evr.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.