[prev in list] [next in list] [prev in thread] [next in thread]
List: spacewalk-commits
Subject: 4 commits - schema/spacewalk
From: mzazrive () fedoraproject ! org (Milan Zazrivec)
Date: 2012-02-29 10:45:32
Message-ID: 20120229104532.3349112E9 () lists ! fedorahosted ! org
[Download RAW message or body]
schema/spacewalk/oracle/procs/insert_source_name.sql \
| 26 ++++ schema/spacewalk/oracle/procs/lookup_source_name.sql \
| 56 ++++------ schema/spacewalk/oracle/procs/procs.deps \
| 3 schema/spacewalk/postgres/procs/insert_source_name.sql \
| 2 schema/spacewalk/postgres/procs/lookup_source_name.sql \
| 55 +++++---- schema/spacewalk/postgres/procs/procs.deps \
| 2 schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.oracle \
| 51 +++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql \
| 33 +++++ 8 files changed, 169 insertions(+), 59 deletions(-)
New commits:
commit d166bba10a727f5951d1e32d0d1c9674398a1bcc
Author: Milan Zazrivec <mzazrivec at redhat.com>
Date: Wed Feb 29 11:44:29 2012 +0100
lookup_source_name: schema upgrade
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.oracle \
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.oracle
new file mode 100644
index 0000000..18e28ff
--- /dev/null
+++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.oracle
@@ -0,0 +1,51 @@
+create or replace function insert_source_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ source_id number;
+begin
+ insert into rhnSourceRPM(id, name)
+ values (rhn_sourcerpm_id_seq.nextval, name_in) returning id into source_id;
+ commit;
+ return source_id;
+end;
+/
+show errors
+-- 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_source_name(name_in in varchar2)
+return number
+is
+ source_id number;
+begin
+ select id
+ into source_id
+ from rhnSourceRPM
+ where name = name_in;
+
+ return source_id;
+exception when no_data_found then
+ begin
+ source_id := insert_source_name(name_in);
+ exception when dup_val_on_index then
+ select id
+ into source_id
+ from rhnSourceRPM
+ where name = name_in;
+ end;
+ return source_id;
+end;
+/
+show errors
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
new file mode 100644
index 0000000..e339e8b
--- /dev/null
+++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/102-lookup_source_name.sql.postgresql
@@ -0,0 +1,33 @@
+-- oracle equivalent source sha1 96b3445ea3839e32ec9c3963845f4931a5d278e0
+
+create or replace function
+lookup_source_name(name_in in varchar)
+returns numeric
+as
+$$
+declare
+ source_id numeric;
+begin
+ select id
+ into source_id
+ from rhnSourceRPM
+ where name = name_in;
+
+ if not found then
+ source_id := nextval('rhn_sourcerpm_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnSourceRPM(id, name) values (' ||
+ source_id || ', ' || coalesce(quote_literal(name_in), 'NULL') || \
')'); + exception when unique_violation then
+ select id
+ into strict source_id
+ from rhnSourceRPM
+ where name = name_in;
+ end;
+ end if;
+
+ return source_id;
+end;
+$$
+language plpgsql immutable;
commit c63d7509172ba344e9cc5b7acdd646e2a895b1ff
Author: Milan Zazrivec <mzazrivec at redhat.com>
Date: Wed Feb 29 11:40:50 2012 +0100
use pg_dblink_exec to execute insert inside lookup_source_name
diff --git a/schema/spacewalk/postgres/procs/lookup_source_name.sql \
b/schema/spacewalk/postgres/procs/lookup_source_name.sql index f643c18..4ce876b \
100644
--- a/schema/spacewalk/postgres/procs/lookup_source_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_source_name.sql
@@ -1,7 +1,6 @@
-- oracle equivalent source sha1 007c497a31ad8fe3716d393fd154ad753dcdb41a
--- retrieved from ./1241057068/d2f16725f65bddae85cd4782cd82e0c84c0a776d/schema/spacewalk/oracle/procs/lookup_source_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,35 @@
-- 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_SOURCE_NAME(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS
+create or replace function
+lookup_source_name(name_in in varchar)
+returns numeric
+as
$$
-DECLARE
- source_id NUMERIC;
-BEGIN
- select id into source_id
- from rhnSourceRPM
- where name = name_in;
+declare
+ source_id numeric;
+begin
+ select id
+ into source_id
+ from rhnSourceRPM
+ where name = name_in;
+
+ if not found then
+ source_id := nextval('rhn_sourcerpm_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnSourceRPM(id, name) values (' ||
+ source_id || ', ' || coalesce(quote_literal(name_in), 'NULL') || \
')'); + exception when unique_violation then
+ select id
+ into strict source_id
+ from rhnSourceRPM
+ where name = name_in;
+ end;
+ end if;
- IF NOT FOUND THEN
- insert into rhnSourceRPM(id, name) values (nextval('rhn_sourcerpm_id_seq'), \
name_in);
- source_id := currval('rhn_sourcerpm_id_seq');
- END IF;
-
- RETURN source_id;
-END;
+ return source_id;
+end;
$$
-LANGUAGE PLPGSQL;
+language plpgsql immutable;
diff --git a/schema/spacewalk/postgres/procs/procs.deps \
b/schema/spacewalk/postgres/procs/procs.deps index c96776f..4fe0f88 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -96,7 +96,7 @@ lookup_server_arch :: rhn_exception.pks \
data/rhnException rhnServer data/rhnServerArch
lookup_sg_type :: rhnServerGroupType
lookup_snapshot_invalid_reason :: rhnSnapshotInvalidReason
-lookup_source_name :: rhnSourceRPM
+lookup_source_name :: rhnSourceRPM pg_dblink_exec
lookup_tag :: rhnTag lookup_tag_name
lookup_tag_name :: rhnTagName
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
commit 0eaca794cba262cf7ad2cd8993871763274799cd
Author: Milan Zazrivec <mzazrivec at redhat.com>
Date: Wed Feb 29 10:58:17 2012 +0100
fix proc dependencies
diff --git a/schema/spacewalk/oracle/procs/procs.deps \
b/schema/spacewalk/oracle/procs/procs.deps index 835c59c..2ff33ed 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -64,6 +64,7 @@ insert_config_filename :: rhnConfigFileName
insert_config_info :: rhnConfigInfo
insert_evr :: rhnPackageEVR
insert_package_nevra :: rhnPackageNEVRA
+insert_source_name :: rhnSourceRPM
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -101,7 +102,7 @@ lookup_server_arch :: rhn_exception.pks \
data/rhnException rhnServer data/rhnServerArch
lookup_sg_type :: rhnServerGroupType
lookup_snapshot_invalid_reason :: rhnSnapshotInvalidReason
-lookup_source_name :: rhnSourceRPM
+lookup_source_name :: insert_source_name rhnSourceRPM
lookup_tag :: rhnTag lookup_tag_name
lookup_tag_name :: rhnTagName
lookup_transaction_package :: rhnTransactionOperation lookup_package_name \
commit 8532b86897154d7bfcc37e642dfc16b559692dd1
Author: Milan Zazrivec <mzazrivec at redhat.com>
Date: Wed Feb 29 10:57:14 2012 +0100
use autonomous_transaction in insert only
diff --git a/schema/spacewalk/oracle/procs/insert_source_name.sql \
b/schema/spacewalk/oracle/procs/insert_source_name.sql new file mode 100644
index 0000000..10e0c06
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_source_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_source_name(name_in in varchar2)
+return number
+is
+ pragma autonomous_transaction;
+ source_id number;
+begin
+ insert into rhnSourceRPM(id, name)
+ values (rhn_sourcerpm_id_seq.nextval, name_in) returning id into source_id;
+ commit;
+ return source_id;
+end;
+/
+show errors
diff --git a/schema/spacewalk/oracle/procs/lookup_source_name.sql \
b/schema/spacewalk/oracle/procs/lookup_source_name.sql index 110fa6a..2dc80a7 100644
--- a/schema/spacewalk/oracle/procs/lookup_source_name.sql
+++ b/schema/spacewalk/oracle/procs/lookup_source_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,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_SOURCE_NAME(name_in IN VARCHAR2)
-RETURN NUMBER
-IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- source_id NUMBER;
-BEGIN
- select id into source_id
- from rhnSourceRPM
- where name = name_in;
+create or replace function
+lookup_source_name(name_in in varchar2)
+return number
+is
+ source_id number;
+begin
+ select id
+ into source_id
+ from rhnSourceRPM
+ where name = name_in;
- RETURN source_id;
-EXCEPTION
- WHEN NO_DATA_FOUND THEN
- insert into rhnSourceRPM(id, name)
- values (rhn_sourcerpm_id_seq.nextval, name_in)
- returning id into source_id;
- COMMIT;
- RETURN source_id;
-END;
+ return source_id;
+exception when no_data_found then
+ begin
+ source_id := insert_source_name(name_in);
+ exception when dup_val_on_index then
+ select id
+ into source_id
+ from rhnSourceRPM
+ where name = name_in;
+ end;
+ return source_id;
+end;
/
-SHOW ERRORS
-
---
--- Revision 1.5 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_source_name.sql \
b/schema/spacewalk/postgres/procs/insert_source_name.sql new file mode 100644
index 0000000..a383f56
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_source_name.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 70bd3a54227c44ef37d184e3595793b4c93a14e1
+-- 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 71f805d..f643c18 \
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 df5951ee3fa3819c099453015dadcef459d90d5e
+-- oracle equivalent source sha1 007c497a31ad8fe3716d393fd154ad753dcdb41a
-- retrieved from ./1241057068/d2f16725f65bddae85cd4782cd82e0c84c0a776d/schema/spacewalk/oracle/procs/lookup_source_name.sql
--
-- Copyright (c) 2008--2010 Red Hat, Inc.
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic