schema/spacewalk/oracle/procs/insert_tag.sql
| 26 ++++
schema/spacewalk/oracle/procs/lookup_tag.sql
| 62 ++++------
schema/spacewalk/oracle/procs/procs.deps
| 3
schema/spacewalk/postgres/procs/insert_source_name.sql
| 2
schema/spacewalk/postgres/procs/insert_tag.sql
| 2
schema/spacewalk/postgres/procs/lookup_source_name.sql
| 2
schema/spacewalk/postgres/procs/lookup_tag.sql
| 56 +++++----
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.oracle
| 42 ++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.postgresql
| 47 +++++++
10 files changed, 180 insertions(+), 64 deletions(-)
New commits:
commit 1eda0a8d3d096dd0a5500b9ae7a1dd21f9cdcba6
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:41:40 2012 +0100
lookup_tag: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.oracle
new file mode 100644
index 0000000..d9a7e2e
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.oracle
@@ -0,0 +1,42 @@
+create or replace function insert_tag(org_id_in in number, name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ tag_id number;
+begin
+ insert into rhnTag(id, org_id, name_id)
+ values (rhn_tag_id_seq.nextval, org_id_in, lookup_tag_name(name_in)) returning id
into tag_id;
+ commit;
+ return tag_id;
+end;
+/
+show errors
+
+create or replace function
+lookup_tag(org_id_in in number, name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ tag_id number;
+begin
+ select id
+ into tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
+
+ return tag_id;
+exception when no_data_found then
+ begin
+ tag_id := insert_tag(org_id_in, name_in);
+ exception when dup_val_on_index then
+ select id
+ into tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
+ end;
+ return tag_id;
+end;
+/
+show errors
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.postgresql
new file mode 100644
index 0000000..0b76cb0
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/103-lookup_tag.sql.postgresql
@@ -0,0 +1,47 @@
+-- oracle equivalent source sha1 9e86081eb6bf62471b147e5caf621f79d9753ef6
+--
+-- 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_tag(org_id_in in numeric, name_in in varchar)
+returns numeric
+as $$
+declare
+ tag_id numeric;
+begin
+ select id
+ into tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
+
+ if not found then
+ tag_id := nextval('rhn_tag_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnTag(id, org_id, name_id) values (' ||
+ tag_id || ', ' || org_id_in || ', ' ||
+ coalesce(quote_literal(lookup_tag_name(name_in), 'NULL')) ||
')');
+ exception when unique_violation then
+ select id
+ into strict tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
+ end;
+ end if;
+
+ return tag_id;
+end; $$
+language plpgsql immutable;
commit 92af3ff1dd7981a036ce01b8a1cda8be2260eb32
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:40:01 2012 +0100
use pg_dblink_exec to execute insert inside lookup_tag
diff --git a/schema/spacewalk/postgres/procs/lookup_tag.sql
b/schema/spacewalk/postgres/procs/lookup_tag.sql
index d252994..c653402 100644
--- a/schema/spacewalk/postgres/procs/lookup_tag.sql
+++ b/schema/spacewalk/postgres/procs/lookup_tag.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 65eff98afb7636fef45db31369ce6c5dd5ae1a37
--- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_tag.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,36 @@
-- 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(org_id_in IN NUMERIC, name_in IN VARCHAR)
-RETURNS NUMERIC
+create or replace function
+lookup_tag(org_id_in in numeric, name_in in varchar)
+returns numeric
as $$
-DECLARE
- tag_id NUMERIC;
-BEGIN
- select id into tag_id
- from rhnTag
- where org_id = org_id_in
- and name_id = lookup_tag_name(name_in);
+declare
+ tag_id numeric;
+begin
+ select id
+ into tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
- IF NOT FOUND THEN
- insert into rhnTag(id, org_id, name_id) values (nextval('rhn_tag_id_seq'),
org_id_in, lookup_tag_name(name_in));
- tag_id := currval('rhn_tag_id_seq');
- END IF;
+ if not found then
+ tag_id := nextval('rhn_tag_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnTag(id, org_id, name_id) values (' ||
+ tag_id || ', ' || org_id_in || ', ' ||
+ coalesce(quote_literal(lookup_tag_name(name_in), 'NULL')) ||
')');
+ exception when unique_violation then
+ select id
+ into strict tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
+ end;
+ end if;
- RETURN tag_id;
-END; $$
-LANGUAGE PLPGSQL;
+ return tag_id;
+end; $$
+language plpgsql immutable;
commit 87c7c2b2473a0be1797d92e48240836ff5a3573c
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:35:18 2012 +0100
fix proc dependencies
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index 2ff33ed..7de2aab 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -65,6 +65,7 @@ insert_config_info :: rhnConfigInfo
insert_evr :: rhnPackageEVR
insert_package_nevra :: rhnPackageNEVRA
insert_source_name :: rhnSourceRPM
+insert_tag :: rhnTag
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -103,7 +104,7 @@ lookup_server_arch :: rhn_exception.pks data/rhnException
rhnServer
lookup_sg_type :: rhnServerGroupType
lookup_snapshot_invalid_reason :: rhnSnapshotInvalidReason
lookup_source_name :: insert_source_name rhnSourceRPM
-lookup_tag :: rhnTag lookup_tag_name
+lookup_tag :: rhnTag lookup_tag_name insert_tag
lookup_tag_name :: rhnTagName
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
lookup_evr lookup_package_arch \
commit 0d0e4cd20af886044c49a42a46e1d33785ec999c
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:32:48 2012 +0100
use autonomous_transaction for insert only
diff --git a/schema/spacewalk/oracle/procs/insert_tag.sql
b/schema/spacewalk/oracle/procs/insert_tag.sql
new file mode 100644
index 0000000..6e5b415
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_tag.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(org_id_in in number, name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ tag_id number;
+begin
+ insert into rhnTag(id, org_id, name_id)
+ values (rhn_tag_id_seq.nextval, org_id_in, lookup_tag_name(name_in)) returning id
into tag_id;
+ commit;
+ return tag_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/lookup_tag.sql
b/schema/spacewalk/oracle/procs/lookup_tag.sql
index ac8a1b1..fbb7f2c 100644
--- a/schema/spacewalk/oracle/procs/lookup_tag.sql
+++ b/schema/spacewalk/oracle/procs/lookup_tag.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,39 +10,32 @@
-- 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(org_id_in IN NUMBER, name_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- tag_id NUMBER;
-BEGIN
- select id into tag_id
- from rhnTag
- where org_id = org_id_in
- and name_id = lookup_tag_name(name_in);
+create or replace function
+lookup_tag(org_id_in in number, name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ tag_id number;
+begin
+ select id
+ into tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
- RETURN tag_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- insert into rhnTag(id, org_id, name_id)
- values (rhn_tag_id_seq.nextval, org_id_in, lookup_tag_name(name_in))
- returning id into tag_id;
- COMMIT;
- RETURN tag_id;
-END;
+ return tag_id;
+exception when no_data_found then
+ begin
+ tag_id := insert_tag(org_id_in, name_in);
+ exception when dup_val_on_index then
+ select id
+ into tag_id
+ from rhnTag
+ where org_id = org_id_in and
+ name_id = lookup_tag_name(name_in);
+ end;
+ return tag_id;
+end;
/
-SHOW ERRORS
-
---
--- Revision 1.1 2003/10/16 14:23:33 bretm
--- bugzilla: 107189
---
--- o added server_id to rhnSnapshotTag
--- o added lookup_tag(org_id, tagname)
---
+show errors
diff --git a/schema/spacewalk/postgres/procs/insert_tag.sql
b/schema/spacewalk/postgres/procs/insert_tag.sql
new file mode 100644
index 0000000..35d1d9e
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_tag.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 0747be17bb344bff3073e00930025cdd503f6ee2
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_tag.sql
b/schema/spacewalk/postgres/procs/lookup_tag.sql
index 8645384..d252994 100644
--- a/schema/spacewalk/postgres/procs/lookup_tag.sql
+++ b/schema/spacewalk/postgres/procs/lookup_tag.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 de7b3b92b5697a52663a3df142c716ea0953960c
+-- oracle equivalent source sha1 65eff98afb7636fef45db31369ce6c5dd5ae1a37
-- retrieved from
./1241042199/53fa26df463811901487b608eecc3f77ca7783a1/schema/spacewalk/oracle/procs/lookup_tag.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.
commit b45108b6a81d063fc0df88218d806bbca1a52634
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Wed Feb 29 13:32:21 2012 +0100
fix sha1 sums
diff --git a/schema/spacewalk/postgres/procs/insert_source_name.sql
b/schema/spacewalk/postgres/procs/insert_source_name.sql
index a383f56..f7c6969 100644
--- a/schema/spacewalk/postgres/procs/insert_source_name.sql
+++ b/schema/spacewalk/postgres/procs/insert_source_name.sql
@@ -1,2 +1,2 @@
--- oracle equivalent source sha1 70bd3a54227c44ef37d184e3595793b4c93a14e1
+-- oracle equivalent source sha1 961eb765a11aacb9649ca6546c7d71ff06221eab
-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_source_name.sql
b/schema/spacewalk/postgres/procs/lookup_source_name.sql
index 4ce876b..0427b8a 100644
--- a/schema/spacewalk/postgres/procs/lookup_source_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_source_name.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 007c497a31ad8fe3716d393fd154ad753dcdb41a
+-- oracle equivalent source sha1 f948e9b5eb7803acc73679b6a09203892468961a
--
-- Copyright (c) 2008--2012 Red Hat, Inc.
--
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql
index e339e8b..23f214d 100644
---
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 96b3445ea3839e32ec9c3963845f4931a5d278e0
+-- oracle equivalent source sha1 6750c771494ff37bb3d153ccd8ef433020c075d8
create or replace function
lookup_source_name(name_in in varchar)