schema/spacewalk/oracle/procs/insert_tag_name.sql
| 26 ++++
schema/spacewalk/oracle/procs/lookup_tag_name.sql
| 54 ++++------
schema/spacewalk/oracle/procs/procs.deps
| 3
schema/spacewalk/postgres/procs/insert_tag_name.sql
| 2
schema/spacewalk/postgres/procs/lookup_tag_name.sql
| 54 +++++-----
schema/spacewalk/postgres/procs/procs.deps
| 4
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.oracle
| 40 +++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.postgresql
| 32 +++++
8 files changed, 156 insertions(+), 59 deletions(-)
New commits:
commit b0980a2847a0abe93178fdac3d9de1450a32d785
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:56:13 2012 +0100
lookup_tag_name: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.oracle
new file mode 100644
index 0000000..67e5e8a
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.oracle
@@ -0,0 +1,40 @@
+create or replace function insert_tag_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnTagName(id, name)
+ values (rhn_tagname_id_seq.nextval, name_in) returning id into name_id;
+ commit;
+ return name_id;
+end;
+/
+show errors
+
+create or replace function
+lookup_tag_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ select id
+ into name_id
+ from rhnTagName
+ where name = name_in;
+
+ return name_id;
+exception when no_data_found then
+ begin
+ name_id := insert_tag_name(name_in);
+ exception when dup_val_on_index then
+ select id
+ into name_id
+ from rhnTagName
+ 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/104-lookup_tag_name.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.postgresql
new file mode 100644
index 0000000..220e582
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/104-lookup_tag_name.sql.postgresql
@@ -0,0 +1,32 @@
+-- oracle equivalent source sha1 7ee8c6969cbd24fb3e30636737486f2e470d02ab
+
+create or replace function
+lookup_tag_name(name_in in varchar)
+returns numeric
+as
+$$
+declare
+ name_id numeric;
+begin
+ select id
+ into name_id
+ from rhnTagName
+ where name = name_in;
+
+ if not found then
+ name_id := nextval('rhn_tagname_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnTagName(id, name) values (' ||
+ name_id || ', ' || coalesce(quote_literal(name_in),
'NULL') || ')');
+ exception when unique_violation then
+ select id
+ into strict name_id
+ from rhnTagName
+ where name = name_in;
+ end;
+ end if;
+
+ return name_id;
+end;
+$$ language plpgsql immutable;
commit 6a972d6a26490e6fa90370028ed03c677a855ac4
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:54:29 2012 +0100
fix proc dependencies
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index 4fe0f88..aa0c1a3 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -97,8 +97,8 @@ lookup_server_arch :: rhn_exception.pks data/rhnException
rhnServer
lookup_sg_type :: rhnServerGroupType
lookup_snapshot_invalid_reason :: rhnSnapshotInvalidReason
lookup_source_name :: rhnSourceRPM pg_dblink_exec
-lookup_tag :: rhnTag lookup_tag_name
-lookup_tag_name :: rhnTagName
+lookup_tag :: rhnTag lookup_tag_name pg_dblink_exec
+lookup_tag_name :: rhnTagName pg_dblink_exec
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
lookup_evr lookup_package_arch \
rhnTransactionPackage rhn_exception.pks
commit f548793093512efeb92e0596be21b5942eeab3f4
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:53:26 2012 +0100
use pg_dblink_exec to execute insert inside lookup_tag_name
diff --git a/schema/spacewalk/postgres/procs/lookup_tag_name.sql
b/schema/spacewalk/postgres/procs/lookup_tag_name.sql
index 1d524a6..9a9e02b 100644
--- a/schema/spacewalk/postgres/procs/lookup_tag_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_tag_name.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 a0ac36c5fc0ed1f1a7a62a4799b4170612d78614
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_tag_name.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,29 +12,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_TAG_NAME(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS
+create or replace function
+lookup_tag_name(name_in in varchar)
+returns numeric
+as
$$
-DECLARE
- name_id NUMERIC;
-BEGIN
- select id into name_id
- from rhnTagName
- where name = name_in;
+declare
+ name_id numeric;
+begin
+ select id
+ into name_id
+ from rhnTagName
+ where name = name_in;
- IF NOT FOUND THEN
- insert into rhnTagName(id, name) values (nextval('rhn_tagname_id_seq'),
name_in);
- name_id := currval('rhn_tagname_id_seq');
- END IF;
+ if not found then
+ name_id := nextval('rhn_tagname_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnTagName(id, name) values (' ||
+ name_id || ', ' || coalesce(quote_literal(name_in),
'NULL') || ')');
+ exception when unique_violation then
+ select id
+ into strict name_id
+ from rhnTagName
+ where name = name_in;
+ end;
+ end if;
- RETURN name_id;
-END;
-$$ LANGUAGE PLPGSQL;
+ return name_id;
+end;
+$$ language plpgsql immutable;
commit 9456215e3d467631d6683f1b1f61314178a0d891
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:53:17 2012 +0100
fix proc dependencies
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index 7de2aab..3d38bc9 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -66,6 +66,7 @@ insert_evr :: rhnPackageEVR
insert_package_nevra :: rhnPackageNEVRA
insert_source_name :: rhnSourceRPM
insert_tag :: rhnTag
+insert_tag_name :: rhnTagName
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -105,7 +106,7 @@ lookup_sg_type :: rhnServerGroupType
lookup_snapshot_invalid_reason :: rhnSnapshotInvalidReason
lookup_source_name :: insert_source_name rhnSourceRPM
lookup_tag :: rhnTag lookup_tag_name insert_tag
-lookup_tag_name :: rhnTagName
+lookup_tag_name :: rhnTagName insert_tag_name
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
lookup_evr lookup_package_arch \
rhnTransactionPackage rhn_exception.pks
commit 637f7fce5669ed18f585fc44fe593a3ef3b8500c
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:48:26 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_tag_name.sql
b/schema/spacewalk/oracle/procs/insert_tag_name.sql
new file mode 100644
index 0000000..44ebfd1
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_tag_name.sql
@@ -0,0 +1,26 @@
+-- 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_tag_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ insert into rhnTagName(id, name)
+ values (rhn_tagname_id_seq.nextval, name_in) returning id into name_id;
+ commit;
+ return name_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/lookup_tag_name.sql
b/schema/spacewalk/oracle/procs/lookup_tag_name.sql
index bbf48b0..38b46e1 100644
--- a/schema/spacewalk/oracle/procs/lookup_tag_name.sql
+++ b/schema/spacewalk/oracle/procs/lookup_tag_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,37 +10,30 @@
-- 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_TAG_NAME(name_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- name_id NUMBER;
-BEGIN
- select id into name_id
+create or replace function
+lookup_tag_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ name_id number;
+begin
+ select id
+ into name_id
from rhnTagName
where name = name_in;
- RETURN name_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- insert into rhnTagName(id, name)
- values (rhn_tagname_id_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_tag_name(name_in);
+ exception when dup_val_on_index then
+ select id
+ into name_id
+ from rhnTagName
+ where name = name_in;
+ end;
+ return name_id;
+end;
/
-SHOW ERRORS
-
---
--- Revision 1.1 2003/10/15 20:29:53 bretm
--- bugzilla: 107189
---
--- 1st pass at snapshot tagging schema
---
+show errors
diff --git a/schema/spacewalk/postgres/procs/insert_tag_name.sql
b/schema/spacewalk/postgres/procs/insert_tag_name.sql
new file mode 100644
index 0000000..d9a45de
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_tag_name.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 3a243557dc0c065deb58725a0deb2014574b748a
+-- This file is intentionaly left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_tag_name.sql
b/schema/spacewalk/postgres/procs/lookup_tag_name.sql
index 2b9c782..1d524a6 100644
--- a/schema/spacewalk/postgres/procs/lookup_tag_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_tag_name.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 6f988ed5edeb906dd639beeb2321eed9208864b3
+-- oracle equivalent source sha1 a0ac36c5fc0ed1f1a7a62a4799b4170612d78614
-- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_tag_name.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.