schema/spacewalk/oracle/procs/insert_config_info.sql
| 34 +++++
schema/spacewalk/oracle/procs/lookup_config_info.sql
| 21 +--
schema/spacewalk/oracle/procs/procs.deps
| 2
schema/spacewalk/postgres/procs/insert_config_info.sql
| 2
schema/spacewalk/postgres/procs/lookup_config_info.sql
| 24 ++-
schema/spacewalk/postgres/procs/procs.deps
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.oracle
| 62 +++++++++
schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.postgresql
| 67 ++++++++++
8 files changed, 196 insertions(+), 17 deletions(-)
New commits:
commit 6fa2558f0bf0cbb758ac4d1eee7bf9f66ee15dbf
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 11:00:53 2012 +0100
update postgresql procs dependencies
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index 276d5f5..6abde3a 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -66,6 +66,7 @@ lookup_channel_arch :: rhn_exception.pks data/rhnException
rhnChanne
lookup_client_capability :: rhnClientCapabilityName pg_dblink_exec
lookup_cf_state :: rhnConfigFileState
lookup_config_filename :: pg_dblink_exec
+lookup_config_info :: pg_dblink_exec
lookup_cve :: rhnCVE
lookup_erratafile_type :: rhnErrataFileType data/rhnErrataFileType
lookup_evr :: rhnPackageEVR
commit a2ff80d0d934a6104baa5b43cef8bddf35788ee3
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 10:59:26 2012 +0100
lookup_config_info: postgresql schema ugprade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.postgresql
new file mode 100644
index 0000000..1ba81f8
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.postgresql
@@ -0,0 +1,67 @@
+-- oracle equivalent source sha1 972edb1a123761b8e7d7e239455bff1bae2307b1
+--
+-- Copyright (c) 2008--2010 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_config_info
+(
+ username_in in varchar,
+ groupname_in in varchar,
+ filemode_in in numeric,
+ selinux_ctx_in in varchar,
+ symlink_target_id in numeric
+)
+returns numeric
+as
+$$
+declare
+ r numeric;
+ v_id numeric;
+ lookup_cursor cursor for
+ select id
+ from rhnConfigInfo
+ where 1=1
+ and (username = username_in or (username is null and username_in is null))
+ and (groupname = groupname_in or (groupname is null and groupname_in is
null))
+ and (filemode = filemode_in or (filemode is null and filemode_in is null))
+ and (selinux_ctx = selinux_ctx_in or
+ (selinux_ctx is null and selinux_ctx_in is null))
+ and (symlink_target_filename_id = symlink_target_id or
+ (symlink_target_filename_id is null and symlink_target_id is null))
+ ;
+begin
+ for r in lookup_cursor loop
+ return r.id;
+ end loop;
+ -- If we got here, we don't have the id
+ v_id := nextval('rhn_confinfo_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnConfigInfo ' ||
+ '(id, username, groupname, filemode, selinux_ctx,
symlink_target_filename_id)' ||
+ 'values (' || v_id || ', ' ||
+ coalesce(quote_literal(username_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(groupname_in), 'NULL') || ', ' ||
+ coalesce(quote_literal( filemode_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(selinux_ctx_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(symlink_target_id), 'NULL') || ')');
+ exception when unique_violation then
+ for r in lookup_cursor loop
+ return r.id;
+ end loop;
+ end;
+ return v_id;
+end;
+$$ language plpgsql immutable;
commit f908b168e04528f1fda66b06bbdbe6b77c216ae8
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 10:58:33 2012 +0100
use pg_dblink_exec to execute insert inside lookup_config_info
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info.sql
b/schema/spacewalk/postgres/procs/lookup_config_info.sql
index 4e5efb6..980be5a 100644
--- a/schema/spacewalk/postgres/procs/lookup_config_info.sql
+++ b/schema/spacewalk/postgres/procs/lookup_config_info.sql
@@ -46,10 +46,22 @@ begin
return r.id;
end loop;
-- If we got here, we don't have the id
- select nextval('rhn_confinfo_id_seq') into v_id;
- insert into rhnConfigInfo
- (id, username, groupname, filemode, selinux_ctx, symlink_target_filename_id)
- values (v_id, username_in, groupname_in, filemode_in, selinux_ctx_in,
symlink_target_id);
+ v_id := nextval('rhn_confinfo_id_seq');
+ begin
+ perform pg_dblink_exec(
+ 'insert into rhnConfigInfo ' ||
+ '(id, username, groupname, filemode, selinux_ctx,
symlink_target_filename_id)' ||
+ 'values (' || v_id || ', ' ||
+ coalesce(quote_literal(username_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(groupname_in), 'NULL') || ', ' ||
+ coalesce(quote_literal( filemode_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(selinux_ctx_in), 'NULL') || ', ' ||
+ coalesce(quote_literal(symlink_target_id), 'NULL') || ')');
+ exception when unique_violation then
+ for r in lookup_cursor loop
+ return r.id;
+ end loop;
+ end;
return v_id;
end;
-$$ language plpgsql;
+$$ language plpgsql immutable;
commit 91fb6cc2e1489415ad9f7106757bc4787a666a28
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 10:46:14 2012 +0100
lookup_config_info: correct proc dependencies
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index de72a8f..8e4c3e1 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -61,6 +61,7 @@ does_user_have_role :: rhnUserGroup rhnUserGroupMembers
rhnUserGroup
insert_client_capability :: rhnClientCapabilityName
insert_checksum :: rhnChecksum rhnChecksumType
insert_config_filename :: rhnConfigFileName
+insert_config_info :: rhnConfigInfo
is_user_applicant :: rhnUserGroupType web_contact rhnUserGroup \
rhnUserGroupMembers
lookup_arch_type :: data/rhnArchType rhn_exception.pks
@@ -70,6 +71,7 @@ lookup_checksum :: rhnChecksumView insert_checksum
lookup_client_capability :: insert_client_capability rhnClientCapabilityName
lookup_cf_state :: rhnConfigFileState
lookup_config_filename :: insert_config_filename
+lookup_config_info :: insert_config_info
lookup_cve :: rhnCVE
lookup_erratafile_type :: rhnErrataFileType data/rhnErrataFileType
lookup_evr :: rhnPackageEVR
commit 15604f10426a91f57305a9860f22af1ace870d48
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 10:43:54 2012 +0100
lookup_config_info: schema upgrade
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.oracle
new file mode 100644
index 0000000..048d825
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/093-lookup_config_info.sql.oracle
@@ -0,0 +1,62 @@
+create or replace function insert_config_info (
+ username_in in varchar2,
+ groupname_in in varchar2,
+ filemode_in in number,
+ selinux_ctx_in in varchar2,
+ symlink_target_id in number
+) return number
+is
+ pragma autonomous_transaction;
+ v_id number;
+begin
+ select rhn_confinfo_id_seq.nextval
+ into v_id
+ from dual;
+ insert into rhnConfigInfo (id, username, groupname, filemode, selinux_ctx,
symlink_target_filename_id)
+ values (v_id, username_in, groupname_in, filemode_in, selinux_ctx_in,
symlink_target_id);
+ commit;
+ return v_id;
+end;
+/
+
+create or replace function
+lookup_config_info (
+ username_in in varchar2,
+ groupname_in in varchar2,
+ filemode_in in number,
+ selinux_ctx_in in varchar2,
+ symlink_target_id in number
+) return number
+deterministic
+is
+ pragma autonomous_transaction;
+ v_id number;
+ cursor lookup_cursor is
+ select id
+ from rhnConfigInfo
+ where 1=1
+ and nvl(username, ' ') = nvl(username_in, ' ')
+ and nvl(groupname,' ') = nvl(groupname_in, ' ')
+ and nvl(filemode, -1) = nvl(filemode_in, -1)
+ and nvl(selinux_ctx, ' ') = nvl(selinux_ctx_in, ' ')
+ and nvl(symlink_target_filename_id, -1) = nvl(symlink_target_id, -1)
+ ;
+begin
+ for r in lookup_cursor loop
+ return r.id;
+ end loop;
+ -- If we got here, we don't have the id
+ v_id := insert_config_info(
+ username_in,
+ groupname_in,
+ filemode_in,
+ selinux_ctx_in,
+ symlink_target_id);
+exception when dup_val_on_index then
+ for r in lookup_cursor loop
+ return r.id;
+ end loop;
+ return v_id;
+end lookup_config_info;
+/
+show errors
commit 517eb35618d4007cd577ce6a40a4d6dbefb928f8
Author: Milan Zazrivec <mzazrivec(a)redhat.com>
Date: Tue Feb 28 10:41:03 2012 +0100
restrict autonomous_transaction to insert only
diff --git a/schema/spacewalk/oracle/procs/insert_config_info.sql
b/schema/spacewalk/oracle/procs/insert_config_info.sql
new file mode 100644
index 0000000..8ffe0e5
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/insert_config_info.sql
@@ -0,0 +1,34 @@
+-- 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_config_info (
+ username_in in varchar2,
+ groupname_in in varchar2,
+ filemode_in in number,
+ selinux_ctx_in in varchar2,
+ symlink_target_id in number
+) return number
+is
+ pragma autonomous_transaction;
+ v_id number;
+begin
+ select rhn_confinfo_id_seq.nextval
+ into v_id
+ from dual;
+ insert into rhnConfigInfo (id, username, groupname, filemode, selinux_ctx,
symlink_target_filename_id)
+ values (v_id, username_in, groupname_in, filemode_in, selinux_ctx_in,
symlink_target_id);
+ commit;
+ return v_id;
+end;
+/
diff --git a/schema/spacewalk/oracle/procs/lookup_config_info.sql
b/schema/spacewalk/oracle/procs/lookup_config_info.sql
index b2aa167..8769931 100644
--- a/schema/spacewalk/oracle/procs/lookup_config_info.sql
+++ b/schema/spacewalk/oracle/procs/lookup_config_info.sql
@@ -1,4 +1,3 @@
---
-- Copyright (c) 2008--2010 Red Hat, Inc.
--
-- This software is licensed to you under the GNU General Public License,
@@ -11,10 +10,6 @@
-- 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_config_info (
@@ -43,12 +38,16 @@ begin
return r.id;
end loop;
-- If we got here, we don't have the id
- select rhn_confinfo_id_seq.nextval
- into v_id
- from dual;
- insert into rhnConfigInfo (id, username, groupname, filemode, selinux_ctx,
symlink_target_filename_id)
- values (v_id, username_in, groupname_in, filemode_in, selinux_ctx_in,
symlink_target_id);
- commit;
+ v_id := insert_config_info(
+ username_in,
+ groupname_in,
+ filemode_in,
+ selinux_ctx_in,
+ symlink_target_id);
+exception when dup_val_on_index then
+ for r in lookup_cursor loop
+ return r.id;
+ end loop;
return v_id;
end lookup_config_info;
/
diff --git a/schema/spacewalk/postgres/procs/insert_config_info.sql
b/schema/spacewalk/postgres/procs/insert_config_info.sql
new file mode 100644
index 0000000..a43dd9b
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/insert_config_info.sql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 927ed17ae7b2c43d40e5a397f793ad986f7ba410
+-- This file is intentionally left empty.
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info.sql
b/schema/spacewalk/postgres/procs/lookup_config_info.sql
index 5be0e94..4e5efb6 100644
--- a/schema/spacewalk/postgres/procs/lookup_config_info.sql
+++ b/schema/spacewalk/postgres/procs/lookup_config_info.sql
@@ -1,4 +1,4 @@
--- oracle equivalent source sha1 66ef8ae50f983aa465ebe116c6c5d0d44f4073ea
+-- oracle equivalent source sha1 d6f51503a2c3261083fa31ce1bb1343c718292c1
--
-- Copyright (c) 2008--2010 Red Hat, Inc.
--