schema/spacewalk/oracle/procs/insert_package_name.sql
| 25 +++
schema/spacewalk/oracle/procs/lookup_package_name.sql
| 70 +++-------
schema/spacewalk/postgres/procs/insert_package_name.sql
| 2
schema/spacewalk/postgres/procs/lookup_package_name.sql
| 62 ++++----
schema/spacewalk/postgres/procs/procs.deps
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.oracle
| 42 ++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.postgresql
| 49 +++++++
7 files changed, 178 insertions(+), 74 deletions(-)
New commits:
commit 044e869688aac307c5a007f50d1e6889553039df
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 16:25:26 2012 +0100
lookup_package_name: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.oracle
new file mode 100644
index 0000000..b1e987f
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.oracle
@@ -0,0 +1,42 @@
+create or replace function insert_package_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnPackageName(id, name)
+ values (rhn_pkg_name_seq.nextval, name_in) returning id into name_id;
+ commit;
+ return name_id;
+end;
+/
+
+create or replace function
+lookup_package_name(name_in in varchar2, ignore_null in number := 0)
+return number
+is
+ name_id number;
+begin
+ if ignore_null = 1 and name_in is null then
+ return null;
+ end if;
+
+ select id
+ into name_id
+ from rhnPackageName
+ where name = name_in;
+
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_package_name(name_in);
+ exception when dup_val_on_index then
+ select id
+ into name_id
+ from rhnPackageName
+ where name = name_in;
+ end;
+ return name_id;
+end;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.postgresql
new file mode 100644
index 0000000..a4339fb
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/099-lookup_package_name.sql.postgresql
@@ -0,0 +1,49 @@
+-- oracle equivalent source sha1 374bf89041a9c2a12ea0378495b55de0affb38db
+--
+-- 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_package_name(name_in in varchar, ignore_null in numeric default 0)
+returns numeric
+as
+$$
+declare
+ name_id numeric;
+begin
+ if ignore_null = 1 and name_in is null then
+ return null;
+ end if;
+
+ select id
+ into name_id
+ from rhnPackageName
+ where name = name_in;
+
+ if not found then
+ name_id := nextval('rhn_pkg_name_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageName(id, name) values (' || name_id ||
', ' ||
+ coalesce(quote_literal(name_in), 'NULL') || ')');
+ exception when unique_violation then
+ select id
+ into strict name_id
+ from rhnPackageName
+ where name = name_in;
+ end;
+ end if;
+
+ return name_id;
+end;
+$$ language plpgsql immutable;
commit d8ba0df1296f399d0bdf50f3ec031219c0cf189d
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 16:22:12 2012 +0100
use pg_dblink_exec to exec insert inside lookup_package_name
diff --git a/schema/spacewalk/postgres/procs/lookup_package_name.sql
b/schema/spacewalk/postgres/procs/lookup_package_name.sql
index 743c6c7..f0b5b43 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_name.sql
@@ -1,7 +1,6 @@
--- oracle equivalent source sha1 791c0067198055114ca9fdf61f4a6a6d66f95b7d
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_package_name.sql
+-- oracle equivalent source sha1 3d574cd26eebe3152eb20bcb57fbbc4d264dfc12
--
--- 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,35 +12,38 @@
-- 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_PACKAGE_NAME(name_in IN VARCHAR, ignore_null in NUMERIC DEFAULT 0)
-RETURNS NUMERIC
-AS
+create or replace function
+lookup_package_name(name_in in varchar, ignore_null in numeric default 0)
+returns numeric
+as
$$
-DECLARE
- name_id NUMERIC;
-BEGIN
- if ignore_null = 1 and name_in is null then
- return null;
- end if;
+declare
+ name_id numeric;
+begin
+ if ignore_null = 1 and name_in is null then
+ return null;
+ end if;
- SELECT id
- INTO name_id
- FROM rhnPackageName
- WHERE name = name_in;
+ select id
+ into name_id
+ from rhnPackageName
+ where name = name_in;
- IF NOT FOUND THEN
- INSERT INTO rhnPackageName (id, name) VALUES (nextval('rhn_pkg_name_seq'),
name_in);
- name_id := currval('rhn_pkg_name_seq');
-
- END IF;
+ if not found then
+ name_id := nextval('rhn_pkg_name_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageName(id, name) values (' || name_id ||
', ' ||
+ coalesce(quote_literal(name_in), 'NULL') || ')');
+ exception when unique_violation then
+ select id
+ into strict name_id
+ from rhnPackageName
+ where name = name_in;
+ end;
+ end if;
- RETURN name_id;
-END;
-$$ LANGUAGE PLPGSQL;
+ return name_id;
+end;
+$$ language plpgsql immutable;
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index 9fd7058..29b13d2 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -88,7 +88,7 @@ lookup_package_arch :: rhn_exception.pks data/rhnException
rhnPackag
data/rhnPackageArch
lookup_package_capability :: rhnPackageCapability
lookup_package_delta :: rhnPackageDelta
-lookup_package_name :: rhnPackageName
+lookup_package_name :: rhnPackageName pg_dblink_exec
lookup_package_provider :: rhnPackageProvider
lookup_package_key_type :: rhnPackageKeyType
lookup_package_nevra :: rhnPackageNEVRA
commit 8d0b4ffac0036df9075afabb3272c794c511cced
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 16:20:18 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_package_name.sql
b/schema/spacewalk/oracle/procs/insert_package_name.sql
new file mode 100644
index 0000000..7c241d3
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_package_name.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_package_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnPackageName(id, name)
+ values (rhn_pkg_name_seq.nextval, name_in) returning id into name_id;
+ commit;
+ return name_id;
+end;
+/
diff --git a/schema/spacewalk/oracle/procs/lookup_package_name.sql
b/schema/spacewalk/oracle/procs/lookup_package_name.sql
index ab27e9c..d5328cc 100644
--- a/schema/spacewalk/oracle/procs/lookup_package_name.sql
+++ b/schema/spacewalk/oracle/procs/lookup_package_name.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,48 +10,33 @@
-- 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_PACKAGE_NAME(name_in IN VARCHAR2, ignore_null in number := 0)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- name_id NUMBER;
-BEGIN
- if ignore_null = 1 and name_in is null then
- return null;
- end if;
+create or replace function
+lookup_package_name(name_in in varchar2, ignore_null in number := 0)
+return number
+is
+ name_id number;
+begin
+ if ignore_null = 1 and name_in is null then
+ return null;
+ end if;
- SELECT id
- INTO name_id
- FROM rhnPackageName
- WHERE name = name_in;
+ select id
+ into name_id
+ from rhnPackageName
+ where name = name_in;
- RETURN name_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- INSERT INTO rhnPackageName (id, name)
- VALUES (rhn_pkg_name_seq.nextval, name_in)
- RETURNING id INTO name_id;
- COMMIT;
- RETURN name_id;
-END;
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_package_name(name_in);
+ exception when dup_val_on_index then
+ select id
+ into name_id
+ from rhnPackageName
+ where name = name_in;
+ end;
+ return name_id;
+end;
/
-SHOW ERRORS
-
---
--- Revision 1.5 2003/02/28 19:58:35 pjones
--- api change for lookup_package_name
---
--- Revision 1.4 2003/02/28 19:27:32 pjones
--- make lookup_package_name return early null with null input
--- lookup_hw* in lookup_functions target
---
--- Revision 1.3 2002/05/13 22:53:38 pjones
--- cvs id/log
--- some (note enough) readability fixes
---
+show errors
diff --git a/schema/spacewalk/postgres/procs/insert_package_name.sql
b/schema/spacewalk/postgres/procs/insert_package_name.sql
new file mode 100644
index 0000000..5650074
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_package_name.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 865d405e7f5618baa2e444e282fd8800296066f8
+-- This file is intentionally left empty.