schema/spacewalk/oracle/procs/insert_package_delta.sql
| 25 ++++
schema/spacewalk/oracle/procs/lookup_package_delta.sql
| 58 ++++------
schema/spacewalk/postgres/procs/insert_package_capability.sql
| 2
schema/spacewalk/postgres/procs/insert_package_delta.sql
| 2
schema/spacewalk/postgres/procs/lookup_package_capability.sql
| 2
schema/spacewalk/postgres/procs/lookup_package_delta.sql
| 52 ++++----
schema/spacewalk/postgres/procs/procs.deps
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/097-lookup_package_capability.sql.postgresql
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.oracle
| 38 ++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
| 44 +++++++
10 files changed, 166 insertions(+), 60 deletions(-)
New commits:
commit e0146a1e6dda3fb3789b68fc8855ac1b6be19e6a
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 15:47:12 2012 +0100
fix sha1 sums
diff --git a/schema/spacewalk/postgres/procs/insert_package_delta.sql
b/schema/spacewalk/postgres/procs/insert_package_delta.sql
index d232d35..813facd 100644
--- a/schema/spacewalk/postgres/procs/insert_package_delta.sql
+++ b/schema/spacewalk/postgres/procs/insert_package_delta.sql
@@ -1,2 +1,2 @@
--- oracle equivalent source sha1 83c8b7d30f415eea9580388f778e68541067d242
+-- oracle equivalent source sha1 4a8214db905b81b0ea4418155a53cc2e8bff4335
-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
index f1522ca..af20225 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 942483729cc7d9263a99c6605ee35e14afd1e526
+-- oracle equivalent source sha1 eb7d5bd540d93ef3e48dd3e05f93b040a00b809d
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
index 3a8d29e..9f75491 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 6b2fa37131c51df51f8a0f341f954168e05cff3e
+-- oracle equivalent source sha1 369db26375f04e6e734a7b746a3ce8b1ba78b0d9
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
commit ddbf46bf6631642879ae848f8329a98bfb88b3a9
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 15:43:38 2012 +0100
lookup_package_delta: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.oracle
new file mode 100644
index 0000000..f0ca072
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.oracle
@@ -0,0 +1,38 @@
+create or replace function insert_package_delta(n_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnPackageDelta(id, label)
+ values (rhn_packagedelta_id_seq.nextval, n_in) returning id into name_id;
+ commit;
+ return name_id;
+end;
+/
+
+create or replace function
+lookup_package_delta(n_in in varchar2)
+return number
+is
+ name_id number;
+begin
+ select id
+ into name_id
+ from rhnpackagedelta
+ where label = n_in;
+
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_package_delta(n_in);
+ exception when dup_val_on_index then
+ select id
+ into name_id
+ from rhnPackageDelta
+ where label = n_in;
+ end;
+ return name_id;
+end;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
new file mode 100644
index 0000000..3a8d29e
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/098-lookup_package_delta.sql.postgresql
@@ -0,0 +1,44 @@
+-- oracle equivalent source sha1 6b2fa37131c51df51f8a0f341f954168e05cff3e
+--
+-- 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_delta(n_in in varchar)
+returns numeric
+as
+$$
+declare
+ name_id numeric;
+begin
+ select id
+ into name_id
+ from rhnPackageDelta
+ where label = n_in;
+
+ if not found then
+ name_id := nextval('rhn_packagedelta_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageDelta(id, label) values (' ||
+ name_id || ', ' || coalesce(quote_literal(n_in), 'NULL'),
')' );
+ exception when unique_violation then
+ select id
+ into strict name_id
+ from rhnpackagedelta
+ where label = n_in;
+ end;
+ end if;
+
+ return name_id;
+end;
+$$ language plpgsql immutable;
commit 7a9a5397dc37220aa0b9ef2464623a9b21ca3299
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 15:40:23 2012 +0100
use pg_dblink_exec to execute insert inside lookup_package_delta
diff --git a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
index c39a9b8..f1522ca 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 942483729cc7d9263a99c6605ee35e14afd1e526
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_package_delta.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,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_DELTA(n_in IN VARCHAR)
-RETURNS NUMERIC
-AS
+create or replace function lookup_package_delta(n_in in varchar)
+returns numeric
+as
$$
-DECLARE
- name_id NUMERIC;
-BEGIN
- SELECT id INTO name_id
- FROM rhnPackageDelta
- WHERE label = n_in;
+declare
+ name_id numeric;
+begin
+ select id
+ into name_id
+ from rhnPackageDelta
+ where label = n_in;
- IF NOT FOUND THEN
- INSERT INTO rhnPackageDelta (id, label) VALUES
(nextval('rhn_packagedelta_id_seq'), n_in);
- name_id := currval('rhn_packagedelta_id_seq');
- END IF;
+ if not found then
+ name_id := nextval('rhn_packagedelta_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnPackageDelta(id, label) values (' ||
+ name_id || ', ' || coalesce(quote_literal(n_in), 'NULL'),
')' );
+ exception when unique_violation then
+ select id
+ into strict name_id
+ from rhnpackagedelta
+ where label = n_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 6b34819..9fd7058 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -87,6 +87,7 @@ lookup_checksum :: pg_dblink_exec
lookup_package_arch :: rhn_exception.pks data/rhnException rhnPackageArch \
data/rhnPackageArch
lookup_package_capability :: rhnPackageCapability
+lookup_package_delta :: rhnPackageDelta
lookup_package_name :: rhnPackageName
lookup_package_provider :: rhnPackageProvider
lookup_package_key_type :: rhnPackageKeyType
commit d53fd1a939d88e139650ae2032159fc98a129fb5
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 14:04:12 2012 +0100
fix sha1 sums
diff --git a/schema/spacewalk/postgres/procs/insert_package_capability.sql
b/schema/spacewalk/postgres/procs/insert_package_capability.sql
index bc27975..8ceb27a 100644
--- a/schema/spacewalk/postgres/procs/insert_package_capability.sql
+++ b/schema/spacewalk/postgres/procs/insert_package_capability.sql
@@ -1,2 +1,2 @@
--- oracle equivalent source sha1 eb465dbc3bda10414a4f848336018a9d1b91bf20
+-- oracle equivalent source sha1 a397d522dd41720dcd8b77e0a20d999ea5365355
-- 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 b2d8625..a51795e 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 a234e21a0def0d24de24d91a12fcbc9bca9ac22a
+-- oracle equivalent source sha1 52eb093fba09adfd4b8a9822023d89374276ef12
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
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
index 27843d2..386f860 100644
---
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
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 f90ddcef03fa88ab008080a48bab3670809f857d
+-- oracle equivalent source sha1 defa71d34e7a255e0a7e8db401dcfb72564bfdac
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
commit 90721be6aefb1378b862e7227a54a96bc710d16e
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 14:02:29 2012 +0100
use autonomous_transaction for insert_only
diff --git a/schema/spacewalk/oracle/procs/insert_package_delta.sql
b/schema/spacewalk/oracle/procs/insert_package_delta.sql
new file mode 100644
index 0000000..fd979b3
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_package_delta.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_delta(n_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnPackageDelta(id, label)
+ values (rhn_packagedelta_id_seq.nextval, n_in) returning id into name_id;
+ commit;
+ return name_id;
+end;
+/
diff --git a/schema/spacewalk/oracle/procs/lookup_package_delta.sql
b/schema/spacewalk/oracle/procs/lookup_package_delta.sql
index 24fc705..eba9166 100644
--- a/schema/spacewalk/oracle/procs/lookup_package_delta.sql
+++ b/schema/spacewalk/oracle/procs/lookup_package_delta.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,36 +10,29 @@
-- 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_DELTA(n_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- name_id NUMBER;
-BEGIN
- SELECT id INTO name_id
- FROM rhnPackageDelta
- WHERE label = n_in;
-
- RETURN name_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- INSERT INTO rhnPackageDelta (id, label)
- VALUES (rhn_packagedelta_id_seq.nextval, n_in)
- RETURNING id INTO name_id;
- COMMIT;
- RETURN name_id;
-END;
-/
-SHOW ERRORS
+create or replace function
+lookup_package_delta(n_in in varchar2)
+return number
+is
+ name_id number;
+begin
+ select id
+ into name_id
+ from rhnpackagedelta
+ where label = n_in;
---
--- Revision 1.1 2003/06/30 22:14:16 misa
--- bugzilla: none Added a lookup function for package delta
---
---
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_package_delta(n_in);
+ exception when dup_val_on_index then
+ select id
+ into name_id
+ from rhnPackageDelta
+ where label = n_in;
+ end;
+ return name_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/postgres/procs/insert_package_delta.sql
b/schema/spacewalk/postgres/procs/insert_package_delta.sql
new file mode 100644
index 0000000..d232d35
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_package_delta.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 83c8b7d30f415eea9580388f778e68541067d242
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
index 7aa1b2f..c39a9b8 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 71a7553a3c473b6262ea6845d638ed420d4983e4
+-- oracle equivalent source sha1 942483729cc7d9263a99c6605ee35e14afd1e526
-- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_package_delta.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.