schema/spacewalk/oracle/procs/insert_package_capability.sql
| 25 ++
schema/spacewalk/oracle/procs/lookup_package_capability.sql
| 85 +++++----
schema/spacewalk/postgres/procs/insert_package_capability.sql
| 2
schema/spacewalk/postgres/procs/lookup_package_capability.sql
| 86 +++++-----
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.oracle
| 56 ++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.postgresql
| 65 +++++++
6 files changed, 240 insertions(+), 79 deletions(-)
New commits:
commit 2f1e29f8a91f09e879cb99b6267a1e4e7cb813ad
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 13:51:08 2012 +0100
lookup_package_capability: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.oracle
new file mode 100644
index 0000000..1fc155f
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.oracle
@@ -0,0 +1,56 @@
+create or replace function insert_package_capability(name_in in varchar2, version_in in
varchar2 default null)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnPackageCapability (id, name, version)
+ values (rhn_pkg_capability_id_seq.nextval, name_in, version_in) returning id into
name_id;
+ commit;
+ return name_id;
+end;
+/
+
+create or replace function
+lookup_package_capability(name_in in varchar2, version_in in varchar2 default null)
+return number
+is
+ name_id number;
+begin
+ if version_in is null then
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version = version_in;
+ end if;
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_package_capability(name_in, version_in);
+ exception when dup_val_on_index then
+ if version_in is null then
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version = version_in;
+ end if;
+
+ end;
+ return name_id;
+end;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.postgresql
new file mode 100644
index 0000000..27843d2
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.postgresql
@@ -0,0 +1,65 @@
+-- oracle equivalent source sha1 f90ddcef03fa88ab008080a48bab3670809f857d
+--
+-- 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_capability(name_in in varchar, version_in in
varchar default null)
+returns numeric
+as
+$$
+declare
+ name_id numeric;
+begin
+ if version_in is null then
+ select id
+ into name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version = version_in;
+ end if;
+
+ if not found then
+ name_id = nextval('rhn_pkg_capability_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageCapability(id, name, version) values ('
||
+ name_id || ', ' ||
+ coalesce(quote_literal(name_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(version_in), 'NULL') || ')');
+ exception when unique_violation then
+ if version_in is null then
+ select id
+ into strict name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into strict name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version = version_in;
+ end if;
+ end;
+ end if;
+
+ return name_id;
+end;
+$$
+language plpgsql immutable;
commit 75c284932a47e63c186c517bba890b91934f7a82
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 13:49:09 2012 +0100
use pg_dblink_exec to exec insert inside lookup_package_capability
diff --git a/schema/spacewalk/postgres/procs/lookup_package_capability.sql
b/schema/spacewalk/postgres/procs/lookup_package_capability.sql
index 1b85692..b2d8625 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_capability.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_capability.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 a234e21a0def0d24de24d91a12fcbc9bca9ac22a
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_package_capability.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,41 +12,54 @@
-- 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_CAPABILITY(name_in IN VARCHAR,
- version_in IN VARCHAR DEFAULT NULL)
-RETURNS NUMERIC
-AS
+create or replace function lookup_package_capability(name_in in varchar, version_in in
varchar default null)
+returns numeric
+as
$$
-DECLARE
- name_id NUMERIC;
-BEGIN
- IF version_in IS NULL THEN
- SELECT id
- INTO name_id
- FROM rhnPackageCapability
- WHERE name = name_in
- AND version IS NULL;
- ELSE
- SELECT id
- INTO name_id
- FROM rhnPackageCapability
- WHERE name = name_in
- AND version = version_in;
- END IF;
+declare
+ name_id numeric;
+begin
+ if version_in is null then
+ select id
+ into name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version = version_in;
+ end if;
+
+ if not found then
+ name_id = nextval('rhn_pkg_capability_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageCapability(id, name, version) values ('
||
+ name_id || ', ' ||
+ coalesce(quote_literal(name_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(version_in), 'NULL') || ')');
+ exception when unique_violation then
+ if version_in is null then
+ select id
+ into strict name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into strict name_id
+ from rhnpackagecapability
+ where name = name_in and
+ version = version_in;
+ end if;
+ end;
+ end if;
- IF NOT FOUND THEN
- INSERT INTO rhnPackageCapability (id, name, version) VALUES
(nextval('rhn_pkg_capability_id_seq'), name_in, version_in);
- name_id := currval('rhn_pkg_capability_id_seq');
- END IF;
-
- RETURN name_id;
-END;
+ return name_id;
+end;
$$
-LANGUAGE PLPGSQL;
+language plpgsql immutable;
commit 17df58b2aaef0e92609974fd439a0c12bd014f09
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 13:40:18 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_package_capability.sql
b/schema/spacewalk/oracle/procs/insert_package_capability.sql
new file mode 100644
index 0000000..6dbc67a
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_package_capability.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_capability(name_in in varchar2, version_in in
varchar2 default null)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnPackageCapability (id, name, version)
+ values (rhn_pkg_capability_id_seq.nextval, name_in, version_in) returning id into
name_id;
+ commit;
+ return name_id;
+end;
+/
diff --git a/schema/spacewalk/oracle/procs/lookup_package_capability.sql
b/schema/spacewalk/oracle/procs/lookup_package_capability.sql
index a821834..9a66fa2 100644
--- a/schema/spacewalk/oracle/procs/lookup_package_capability.sql
+++ b/schema/spacewalk/oracle/procs/lookup_package_capability.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,45 +10,47 @@
-- 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_CAPABILITY(name_in IN VARCHAR2,
- version_in IN VARCHAR2 DEFAULT NULL)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- name_id NUMBER;
-BEGIN
- IF version_in IS NULL THEN
- SELECT id
- INTO name_id
- FROM rhnPackageCapability
- WHERE name = name_in
- AND version IS NULL;
- ELSE
- SELECT id
- INTO name_id
- FROM rhnPackageCapability
- WHERE name = name_in
- AND version = version_in;
- END IF;
- RETURN name_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- INSERT INTO rhnPackageCapability (id, name, version)
- VALUES (rhn_pkg_capability_id_seq.nextval, name_in, version_in)
- RETURNING id INTO name_id;
- COMMIT;
- RETURN name_id;
-END;
-/
-SHOW ERRORS
+create or replace function
+lookup_package_capability(name_in in varchar2, version_in in varchar2 default null)
+return number
+is
+ name_id number;
+begin
+ if version_in is null then
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version = version_in;
+ end if;
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_package_capability(name_in, version_in);
+ exception when dup_val_on_index then
+ if version_in is null then
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version is null;
+ else
+ select id
+ into name_id
+ from rhnPackageCapability
+ where name = name_in and
+ version = version_in;
+ end if;
---
--- Revision 1.1 2003/03/03 16:46:51 misa
--- bugzilla: 83674 Reworked the schema for package removal
---
+ end;
+ return name_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/postgres/procs/insert_package_capability.sql
b/schema/spacewalk/postgres/procs/insert_package_capability.sql
new file mode 100644
index 0000000..bc27975
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_package_capability.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 eb465dbc3bda10414a4f848336018a9d1b91bf20
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_package_capability.sql
b/schema/spacewalk/postgres/procs/lookup_package_capability.sql
index 8d68a2c..1b85692 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_capability.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_capability.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 2a9d6258e70469a6cb38bc5be57a865a3cc8e65b
+-- oracle equivalent source sha1 a234e21a0def0d24de24d91a12fcbc9bca9ac22a
-- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_package_capability.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.