schema/spacewalk/postgres/packages/rhn_user.pkb | 20 --
schema/spacewalk/postgres/procs/create_pxt_session.sql | 28 ---
schema/spacewalk/postgres/procs/lookup_client_capability.sql | 22 +-
schema/spacewalk/postgres/procs/lookup_client_capability_autonomous.sql | 44 -----
schema/spacewalk/postgres/procs/lookup_config_filename.sql | 24 ++-
schema/spacewalk/postgres/procs/lookup_config_filename_autonomous.sql | 38 -----
schema/spacewalk/postgres/procs/lookup_config_info.sql | 48 +++---
schema/spacewalk/postgres/procs/lookup_config_info_autonomous.sql | 32 ----
schema/spacewalk/postgres/procs/lookup_cve.sql | 28 ++-
schema/spacewalk/postgres/procs/lookup_cve_autonomous.sql | 39 -----
schema/spacewalk/postgres/procs/lookup_erratafile_type.sql | 34 ++--
schema/spacewalk/postgres/procs/lookup_erratafile_type_autonomous.sql | 21 --
schema/spacewalk/postgres/procs/lookup_evr.sql | 32 ++--
schema/spacewalk/postgres/procs/lookup_evr_autonomous.sql | 24 ---
schema/spacewalk/postgres/procs/lookup_package_capability.sql | 25 ---
schema/spacewalk/postgres/procs/lookup_package_delta.sql | 27 ---
schema/spacewalk/postgres/procs/lookup_package_name.sql | 37 -----
schema/spacewalk/postgres/procs/lookup_package_nevra.sql | 61 --------
schema/spacewalk/postgres/procs/lookup_snapshot_invalid_reason.sql | 23 ---
schema/spacewalk/postgres/procs/lookup_source_name.sql | 25 ---
schema/spacewalk/postgres/procs/lookup_tag.sql | 27 ---
schema/spacewalk/postgres/procs/lookup_tag_name.sql | 25 ---
schema/spacewalk/postgres/procs/lookup_transaction_package.sql | 48 +-----
schema/spacewalk/postgres/procs/pxt_session_cleanup.sql | 74
++--------
24 files changed, 151 insertions(+), 655 deletions(-)
New commits:
commit 53fa26df463811901487b608eecc3f77ca7783a1
Author: Tom Lane <tgl(a)redhat.com>
Date: Wed Apr 29 17:56:39 2009 -0400
Remove use of dblink to emulate Oracle autonomous transactions.
Per discussion with Jeff, it is unclear that we need to emulate
that behavior at all in the Postgres port; it seems to have been
used only to work around Oracle restrictions. And it is clear
that we don't want to depend on dblink if at all possible.
diff --git a/schema/spacewalk/postgres/packages/rhn_user.pkb
b/schema/spacewalk/postgres/packages/rhn_user.pkb
index 08e32e6..ce0e70f 100644
--- a/schema/spacewalk/postgres/packages/rhn_user.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_user.pkb
@@ -89,7 +89,7 @@ $$ language plpgsql;
-- paid users often don't have verified email addresses, so
-- try to find an address that is useful to us.
create or replace
- function find_mailable_address_autonomous(user_id_in in numeric)
+ function find_mailable_address(user_id_in in numeric)
returns varchar as $$
declare
retval rhnEmailAddress.address%TYPE;
@@ -163,24 +163,6 @@ create or replace
$$ language plpgsql;
create or replace
- function find_mailable_address(user_id_in in numeric)
- returns varchar as $$
- declare
- retval rhnEmailAddress.address%TYPE;
- begin
- select retcode
- into retval
- from dblink( 'dbname=' || current_database(),
- 'select find_mailable_address_autonomous('
- || coalesce( user_id_in::varchar, 'null' )
- || ')' )
- as f( retcode numeric );
-
- return retval;
- end;
-$$ language plpgsql;
-
-create or replace
function add_servergroup_perm(
user_id_in in numeric,
server_group_id_in in numeric
diff --git a/schema/spacewalk/postgres/procs/create_pxt_session.sql
b/schema/spacewalk/postgres/procs/create_pxt_session.sql
index e2b3ee1..4a10655 100644
--- a/schema/spacewalk/postgres/procs/create_pxt_session.sql
+++ b/schema/spacewalk/postgres/procs/create_pxt_session.sql
@@ -17,15 +17,12 @@
--
create or replace function
-create_pxt_session_autonomous(p_web_user_id in numeric, p_expires in numeric, p_value in
varchar)
+create_pxt_session(p_web_user_id in numeric, p_expires in numeric, p_value in varchar)
returns numeric as $$
declare
l_id numeric;
begin
- select
- sequence_nextval( 'pxt_id_seq' ) into l_id
- from
- dual;
+ select nextval( 'pxt_id_seq' ) into l_id;
insert into PXTSessions (id, value, expires, web_user_id)
values (l_id, p_value, p_expires, p_web_user_id);
@@ -33,24 +30,3 @@ begin
return l_id;
end;
$$ language plpgsql;
-
-create or replace function
-create_pxt_session(web_user_id in numeric, expires in numeric, value in varchar)
-returns numeric as $$
-declare
- ret numeric;
-begin
- select retcode
- into ret
- from dblink( 'dbname='||current_database(),
- 'select create_pxt_session_autonomous( '
- || coalesce( web_user_id::varchar, 'null' ) || ', '
- || coalesce( expires::varchar, 'null' ) || ', '
- || coalesce( quote_literal( value ), 'null' ) ||
- ')' )
- as f( retcode int );
-
- return ret;
-end;
-$$ language plpgsql;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_client_capability.sql
b/schema/spacewalk/postgres/procs/lookup_client_capability.sql
index 0d2ed85..3be77cc 100644
--- a/schema/spacewalk/postgres/procs/lookup_client_capability.sql
+++ b/schema/spacewalk/postgres/procs/lookup_client_capability.sql
@@ -15,20 +15,24 @@
--
--
--
+
CREATE OR REPLACE FUNCTION
LOOKUP_CLIENT_CAPABILITY(name_in IN VARCHAR)
RETURNS NUMERIC
AS $$
DECLARE
- ret_val NUMERIC;
+ cap_name_id NUMERIC;
BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'select lookup_client_capabality_autonomous('
- ||coalesce(name_in::varchar,'null')||')')
- as f(retcode int);
-
+ SELECT id
+ INTO cap_name_id
+ FROM rhnClientCapabilityName
+ WHERE name = name_in;
- RETURN ret_val;
-END; $$
-LANGUAGE plpgsql;
+ IF NOT FOUND THEN
+ INSERT INTO rhnClientCapabilityName (id, name) VALUES
(nextval('rhn_client_capname_id_seq'), name_in);
+ cap_name_id := currval('rhn_client_capname_id_seq');
+ END IF;
+ RETURN cap_name_id;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_client_capability_autonomous.sql
b/schema/spacewalk/postgres/procs/lookup_client_capability_autonomous.sql
deleted file mode 100644
index 2191d15..0000000
--- a/schema/spacewalk/postgres/procs/lookup_client_capability_autonomous.sql
+++ /dev/null
@@ -1,44 +0,0 @@
---
--- Copyright (c) 2008 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_CLIENT_CAPABILITY(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS $$
-DECLARE
- cap_name_id NUMERIC;
-BEGIN
- SELECT id
- INTO cap_name_id
- FROM rhnClientCapabilityName
- WHERE name = name_in;
-
- RETURN cap_name_id;
-
-
-
- IF NOT FOUND THEN
- INSERT INTO rhnClientCapabilityName (id, name) VALUES
(nextval('rhn_client_capname_id_seq'), name_in);
- cap_name_id := currval('rhn_client_capname_id_seq');
- END IF;
-
- RETURN cap_name_id;
-END;
-$$ LANGUAGE plpgsql;
-
-
diff --git a/schema/spacewalk/postgres/procs/lookup_config_filename.sql
b/schema/spacewalk/postgres/procs/lookup_config_filename.sql
index 7b31636..0f286d8 100644
--- a/schema/spacewalk/postgres/procs/lookup_config_filename.sql
+++ b/schema/spacewalk/postgres/procs/lookup_config_filename.sql
@@ -7,26 +7,32 @@
-- 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.
+-- in this software or its documentation.
--
--
--
--
+
CREATE OR REPLACE FUNCTION
LOOKUP_CONFIG_FILENAME(name_in IN VARCHAR)
RETURNS NUMERIC
AS $$
DECLARE
- ret_val NUMERIC;
+ name_id NUMERIC;
BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'select
lookup_config_filename_autonomous('||coalesce(name_in::varchar,'null')||')')
- as f(retcode numeric);
-
- RETURN ret_val;
+ SELECT id
+ INTO name_id
+ FROM rhnConfigFileName
+ WHERE path = name_in;
+
+ IF NOT FOUND THEN
+ INSERT INTO rhnConfigFileName (id, path) VALUES (nextval('rhn_cfname_id_seq'),
name_in);
+ name_id := currval('rhn_cfname_id_seq');
+ END IF;
+
+ RETURN name_id;
END; $$
LANGUAGE plpgsql;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_config_filename_autonomous.sql
b/schema/spacewalk/postgres/procs/lookup_config_filename_autonomous.sql
deleted file mode 100644
index 4b730e4..0000000
--- a/schema/spacewalk/postgres/procs/lookup_config_filename_autonomous.sql
+++ /dev/null
@@ -1,38 +0,0 @@
---
--- Copyright (c) 2008 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_FILENAME_AUTONOMOUS(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS $$
-DECLARE
-
- name_id NUMERIC;
-BEGIN
- SELECT id
- INTO name_id
- FROM rhnConfigFileName
- WHERE path = name_in;
-
- IF NOT FOUND THEN
- INSERT INTO rhnConfigFileName (id, path) VALUES (nextval('rhn_cfname_id_seq'),
name_in);
- name_id := currval('rhn_cfname_id_seq');
- END IF;
-
-END; $$
-LANGUAGE plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info.sql
b/schema/spacewalk/postgres/procs/lookup_config_info.sql
index 6d1386e..aa95e70 100644
--- a/schema/spacewalk/postgres/procs/lookup_config_info.sql
+++ b/schema/spacewalk/postgres/procs/lookup_config_info.sql
@@ -7,34 +7,40 @@
-- 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.
---
---
---
+-- in this software or its documentation.
--
+
create or replace function
-lookup_config_info (
+lookup_config_info
+(
username_in in varchar,
groupname_in in varchar,
filemode_in in varchar
-) returns numeric
-AS $$
-DECLARE
- ret_val numeric;
-
+)
+returns numeric
+as
+$$
+declare
+ r numeric;
+ v_id numeric;
+ lookup_cursor cursor for
+ select id
+ from rhnConfigInfo
+ where username = username_in
+ and groupname = groupname_in
+ and filemode = filemode_in;
begin
-
- select retcode into ret_val
- from dblink('dbname='||current_database(),
- 'select
lookup_config_info_autonomous('||coalesce(username_in::varchar,'null')||','
- || coalesce(groupname_in::varchar,'null')||','
- || coalesce (filemode_in::varchar, 'null')|| ')')
- as f (retcode numeric);
-
- return ret_val;
+ for r in lookup_cursor loop
+ 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)
+ values (v_id, username_in, groupname_in, filemode_in);
+ return v_id;
end;
$$ language plpgsql;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_config_info_autonomous.sql
b/schema/spacewalk/postgres/procs/lookup_config_info_autonomous.sql
deleted file mode 100644
index 8f924cd..0000000
--- a/schema/spacewalk/postgres/procs/lookup_config_info_autonomous.sql
+++ /dev/null
@@ -1,32 +0,0 @@
-create or replace function
-lookup_config_info_autonomous
-(
- username_in in varchar,
- groupname_in in varchar,
- filemode_in in varchar
-)
-returns numeric
-as
-$$
-declare
- r numeric;
- v_id numeric;
- lookup_cursor cursor for
- select id
- from rhnConfigInfo
- where username = username_in
- and groupname = groupname_in
- and filemode = filemode_in;
-begin
- loop
- fetch lookup_cursor into r;
- return r;
- 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)
- values (v_id, username_in, groupname_in, filemode_in);
- return v_id;
-end;
-$$ language plpgsql stable;
diff --git a/schema/spacewalk/postgres/procs/lookup_cve.sql
b/schema/spacewalk/postgres/procs/lookup_cve.sql
index 17ef8dc..a3d102a 100644
--- a/schema/spacewalk/postgres/procs/lookup_cve.sql
+++ b/schema/spacewalk/postgres/procs/lookup_cve.sql
@@ -7,27 +7,33 @@
-- 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.
+-- in this software or its documentation.
--
--
--
--
+
CREATE OR REPLACE FUNCTION
LOOKUP_CVE(name_in IN VARCHAR)
RETURNS NUMERIC
AS $$
DECLARE
- ret_val NUMERIC;
+ name_id NUMERIC;
BEGIN
+ SELECT id
+ INTO name_id
+ FROM rhnCve
+ WHERE name = name_in;
+
+ IF NOT FOUND THEN
+ INSERT INTO rhnCve (id, name) VALUES (nextval('rhn_cve_id_seq'),
name_in);
+ name_id := currval('rhn_cve_id_seq');
+
+ END IF;
+
+ RETURN name_id;
+END; $$ language plpgsql;
- select retcode into ret_val
- from dblink('dbname='||current_database(),
- 'select
lookup_cve_autonomous('||coalesce(name_in::varchar,'null')||')')
- as f(retcode numeric);
-
- RETURN ret_val;
-END;
-$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_cve_autonomous.sql
b/schema/spacewalk/postgres/procs/lookup_cve_autonomous.sql
deleted file mode 100644
index 6da24ff..0000000
--- a/schema/spacewalk/postgres/procs/lookup_cve_autonomous.sql
+++ /dev/null
@@ -1,39 +0,0 @@
---
--- Copyright (c) 2008 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_CVE_AUTONOUMOUS(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS $$
-DECLARE
- name_id NUMERIC;
-BEGIN
- SELECT id
- INTO name_id
- FROM rhnCve
- WHERE name = name_in;
-
- IF NOT FOUND THEN
- INSERT INTO rhnCve (id, name) VALUES (nextval('rhn_cve_id_seq'),
name_in);
- name_id := currval('rhn_cve_id_seq');
-
- END IF;
-
- RETURN name_id;
-END; $$ language plpgsql;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_erratafile_type.sql
b/schema/spacewalk/postgres/procs/lookup_erratafile_type.sql
index 7587c48..8372c4e 100644
--- a/schema/spacewalk/postgres/procs/lookup_erratafile_type.sql
+++ b/schema/spacewalk/postgres/procs/lookup_erratafile_type.sql
@@ -7,27 +7,29 @@
-- 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.
---
---
---
+-- in this software or its documentation.
--
-
-create or replace function lookup_erratafile_type(lable_in in varchar)
-returns numeric
-as $$
+create or replace function
+lookup_erratafile_type (
+ label_in in varchar
+) returns numeric as
+$$
declare
- ret_val numeric;
+ erratafile_type_id numeric;
begin
+ select id
+ into erratafile_type_id
+ from rhnErrataFileType
+ where label = label_in;
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'select
lookup_erratafile_type_autonomous('||coalesce(label_in::varchar,'null')||')')
- as f (retcode numeric);
-end;
-$$language plpgsql;
-
+ if not found then
+ perform rhn_exception.raise_exception('erratafile_type_not_found');
+ end if;
+ return erratafile_type_id;
+end;
+$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_erratafile_type_autonomous.sql
b/schema/spacewalk/postgres/procs/lookup_erratafile_type_autonomous.sql
deleted file mode 100644
index df51d55..0000000
--- a/schema/spacewalk/postgres/procs/lookup_erratafile_type_autonomous.sql
+++ /dev/null
@@ -1,21 +0,0 @@
-create or replace function
-lookup_erratafile_type_autonomous (
- label_in in varchar
-) returns numeric as
-$$
-declare
- erratafile_type_id numeric;
-begin
- select id
- into erratafile_type_id
- from rhnErrataFileType
- where label = label_in;
-
- if not found then
- perform rhn_exception.raise_exception('erratafile_type_not_found');
- end if;
-
- return erratafile_type_id;
-
-end;
-$$ language plpgsql stable;
diff --git a/schema/spacewalk/postgres/procs/lookup_evr.sql
b/schema/spacewalk/postgres/procs/lookup_evr.sql
index 04c2677..f424645 100644
--- a/schema/spacewalk/postgres/procs/lookup_evr.sql
+++ b/schema/spacewalk/postgres/procs/lookup_evr.sql
@@ -7,30 +7,32 @@
-- 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.
---
---
---
+-- in this software or its documentation.
--
-CREATE OR REPLACE FUNCTION LOOKUP_EVR(e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR)
+CREATE OR REPLACE FUNCTION
+LOOKUP_EVR(e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR)
RETURNS NUMERIC
AS
$$
DECLARE
- ret_val numeric;
+ evr_id NUMERIC;
BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_EVR_AUTONOMOUS('
- ||quote_nullable(e_in)||','
- ||quote_nullable(v_in)||','
- ||quote_nullable(r_in)||')')
- as f (retcode numeric);
+ SELECT id INTO evr_id
+ FROM rhnPackageEvr
+ WHERE ((epoch IS NULL and e_in IS NULL) OR (epoch = e_in))
+ AND version = v_in AND release = r_in;
+
+ IF NOT FOUND THEN
+ INSERT INTO rhnPackageEvr (id, epoch, version, release, evr)
+ VALUES (nextval('rhn_pkg_evr_seq'), e_in, v_in, r_in,EVR_T(e_in,
v_in, r_in));
+
+ evr_id := currval('rhn_pkg_evr_seq');
+ END IF;
- return ret_val;
+ RETURN evr_id;
END;
$$ LANGUAGE PLPGSQL;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_evr_autonomous.sql
b/schema/spacewalk/postgres/procs/lookup_evr_autonomous.sql
deleted file mode 100644
index da05a48..0000000
--- a/schema/spacewalk/postgres/procs/lookup_evr_autonomous.sql
+++ /dev/null
@@ -1,24 +0,0 @@
-CREATE OR REPLACE FUNCTION
-LOOKUP_EVR_AUTONOMOUS(e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- evr_id NUMERIC;
-BEGIN
- SELECT id INTO evr_id
- FROM rhnPackageEvr
- WHERE ((epoch IS NULL and e_in IS NULL) OR (epoch = e_in))
- AND version = v_in AND release = r_in;
-
-
- IF NOT FOUND THEN
- INSERT INTO rhnPackageEvr (id, epoch, version, release, evr)
- VALUES (nextval('rhn_pkg_evr_seq'), e_in, v_in, r_in,EVR_T(e_in,
v_in, r_in));
-
- evr_id := currval('rhn_pkg_evr_seq');
- END IF;
-
- RETURN evr_id;
-END;
-$$ LANGUAGE PLPGSQL;
diff --git a/schema/spacewalk/postgres/procs/lookup_package_capability.sql
b/schema/spacewalk/postgres/procs/lookup_package_capability.sql
index c467071..0fa74b4 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_capability.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_capability.sql
@@ -17,8 +17,10 @@
--
-CREATE OR REPLACE FUNCTION LOOKUP_PACKAGE_CAPABILITY_AUTONOMOUS(name_in IN VARCHAR,
version_in IN VARCHAR)
-RETURNs NUMERIC
+CREATE OR REPLACE FUNCTION
+LOOKUP_PACKAGE_CAPABILITY(name_in IN VARCHAR,
+ version_in IN VARCHAR DEFAULT NULL)
+RETURNS NUMERIC
AS
$$
DECLARE
@@ -47,22 +49,3 @@ BEGIN
END;
$$
LANGUAGE PLPGSQL;
-
-
-
-CREATE OR REPLACE FUNCTION
-LOOKUP_PACKAGE_CAPABILITY(name_in IN VARCHAR,version_in IN VARCHAR)
-RETURNS NUMERIC
-AS $$
-DECLARE
- ret_val NUMERIC;
-BEGIN
-
- select retcode into ret_val
- from dblink('dbname='||current_database(),
- 'select
LOOKUP_PACKAGE_CAPABILITY_AUTONOMOUS('||coalesce(name_in::varchar,'null')||','
- ||coalesce(version_in::varchar,'null')||')')
- as f(retcode numeric);
-
- RETURN ret_val;
-END; $$ language plpgsql;
diff --git a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
index 50e6b9f..5a0e270 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_delta.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_delta.sql
@@ -17,7 +17,7 @@
--
CREATE OR REPLACE FUNCTION
-LOOKUP_PACKAGE_DELTA_AUTONOMOUS(n_in IN VARCHAR)
+LOOKUP_PACKAGE_DELTA(n_in IN VARCHAR)
RETURNS NUMERIC
AS
$$
@@ -28,8 +28,6 @@ BEGIN
FROM rhnPackageDelta
WHERE label = n_in;
- RETURN name_id;
-
IF NOT FOUND THEN
INSERT INTO rhnPackageDelta (id, label) VALUES
(nextval('rhn_packagedelta_id_seq'), n_in);
name_id := currval('rhn_packagedelta_id_seq');
@@ -38,26 +36,3 @@ BEGIN
RETURN name_id;
END;
$$ LANGUAGE PLPGSQL;
-
-
-CREATE OR REPLACE FUNCTION
-LOOKUP_PACKAGE_DELTA(n_in IN VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val NUMERIC;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_PACKAGE_DELTA('
- ||COALESCE(n_in::varchar,'null')||')')
- as f(retcode numeric);
-
- return ret_val;
-
-END;
-$$
-LANGUAGE PLPGSQL;
-
-
-
diff --git a/schema/spacewalk/postgres/procs/lookup_package_name.sql
b/schema/spacewalk/postgres/procs/lookup_package_name.sql
index b242b48..ac4eee0 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_name.sql
@@ -18,7 +18,7 @@
CREATE OR REPLACE FUNCTION
-LOOKUP_PACKAGE_NAME_AUTONOMOUS(name_in IN VARCHAR, ignore_null in NUMERIC)
+LOOKUP_PACKAGE_NAME(name_in IN VARCHAR, ignore_null in NUMERIC DEFAULT 0)
RETURNS NUMERIC
AS
$$
@@ -41,40 +41,5 @@ BEGIN
END IF;
RETURN name_id;
-
-END;
-$$ LANGUAGE PLPGSQL;
-
-CREATE OR REPLACE FUNCTION LOOKUP_PACKAGE_NAME(name_in VARCHAR,ignore_null in NUMERIC)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val NUMERIC;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_PACKAGE_NAME_AUTONOMOUS ('
- ||quote_nullable(name_in)||','
- ||quote_nullable(ignore_null)||')')
- as f(retcode numeric);
-
- return ret_val;
END;
$$ LANGUAGE PLPGSQL;
-
-CREATE OR REPLACE FUNCTION LOOKUP_PACKAGE_NAME(name_in VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val NUMERIC;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_PACKAGE_NAME_AUTONOMOUS ('
- ||quote_nullable(name_in)||',0)')
- as f(retcode numeric);
-
- return ret_val;
-END;
-$$ LANGUAGE PLPGSQL;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
b/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
index 23fba34..0f1eefa 100644
--- a/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
+++ b/schema/spacewalk/postgres/procs/lookup_package_nevra.sql
@@ -19,11 +19,11 @@
create or replace function
-lookup_package_nevra_autonomous(
+lookup_package_nevra(
name_id_in in varchar,
evr_id_in in varchar,
package_arch_id_in in varchar,
- ignore_null_name in numeric
+ ignore_null_name in numeric default 0
) returns numeric
AS
$$
@@ -37,14 +37,12 @@ BEGIN
select id
into nevra_id
from rhnPackageNEVRA
- where 1=1
- and name_id = name_id_in
+ where name_id = name_id_in
and evr_id = evr_id_in
and (package_arch_id = package_arch_id_in or
(package_arch_id is null
and package_arch_id_in is null));
-
IF NOT FOUND THEN
insert into rhnPackageNEVRA
(id, name_id, evr_id, package_arch_id)
@@ -57,55 +55,4 @@ BEGIN
return nevra_id;
end;
-$$ LANGUAGE PLPGSQL STABLE;
-
-
-
-create or replace function
-lookup_package_nevra(
- name_id_in in varchar,
- evr_id_in in varchar,
- package_arch_id_in in varchar,
- ignore_null_name in numeric
-) returns numeric
-as
-$$
-declare
- ret_val int;
-begin
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT lookup_package_nevra_autonomous ('
- ||COALESCE(name_id_in::varchar,'null')||','
- ||COALESCE(evr_id_in::varchar, 'null')||','
- ||COALESCE(package_arch_id_in::varchar,'null')||','
- ||COALESCE(0,'null')||')')
- as f(retcode numeric);
-
- return ret_val;
-end;
-$$ language plpgsql;
-
-
-create or replace function
-lookup_package_nevra(
- name_id_in in varchar,
- evr_id_in in varchar,
- package_arch_id_in in varchar
-) returns numeric
-as
-$$
-declare
- ret_val int;
-begin
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT lookup_package_nevra_autonomous ('
- ||COALESCE(name_id_in::varchar,'null')||','
- ||COALESCE(evr_id_in::varchar, 'null')||','
- ||COALESCE(package_arch_id_in::varchar,'null')||','
- ||COALESCE(0,'null')||')')
- as f(retcode numeric);
-
- return ret_val;
-end;
-$$ language plpgsql;
-
+$$ LANGUAGE PLPGSQL;
diff --git a/schema/spacewalk/postgres/procs/lookup_snapshot_invalid_reason.sql
b/schema/spacewalk/postgres/procs/lookup_snapshot_invalid_reason.sql
index 680bd18..0f9ad84 100644
--- a/schema/spacewalk/postgres/procs/lookup_snapshot_invalid_reason.sql
+++ b/schema/spacewalk/postgres/procs/lookup_snapshot_invalid_reason.sql
@@ -18,7 +18,7 @@
CREATE OR REPLACE FUNCTION
-lookup_snapshot_invalid_reason_autonomous(label_in IN VARCHAR)
+lookup_snapshot_invalid_reason(label_in IN VARCHAR)
RETURNS NUMERIC
AS
$$
@@ -34,25 +34,6 @@ BEGIN
PERFORM rhn_exception.raise_exception('invalid_snapshot_invalid_reason');
END IF;
-
-END;
-$$ LANGUAGE PLPGSQL;
-
-
-CREATE OR REPLACE FUNCTION
-lookup_snapshot_invalid_reason(label_in IN VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val NUMERIC;
-BEGIN
-
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'select lookup_snapshot_invalid_reason('
- ||COALESCE(label_in::varchar,'null')||')')
- as f(retcode numeric);
-
- return ret_val;
+ RETURN snapshot_invalid_reason_id;
END;
$$ LANGUAGE PLPGSQL;
diff --git a/schema/spacewalk/postgres/procs/lookup_source_name.sql
b/schema/spacewalk/postgres/procs/lookup_source_name.sql
index fd178e2..7913c1a 100644
--- a/schema/spacewalk/postgres/procs/lookup_source_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_source_name.sql
@@ -17,7 +17,7 @@
--
CREATE OR REPLACE FUNCTION
-LOOKUP_SOURCE_NAME_AUTONOMOUS(name_in IN VARCHAR)
+LOOKUP_SOURCE_NAME(name_in IN VARCHAR)
RETURNS NUMERIC
AS
$$
@@ -33,30 +33,7 @@ BEGIN
source_id := currval('(rhn_sourcerpm_id_seq');
END IF;
-
RETURN source_id;
END;
$$
LANGUAGE PLPGSQL;
-
-
-
-CREATE OR REPLACE FUNCTION
-LOOKUP_SOURCE_NAME(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val NUMERIC;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_SOURCE_NAME('
- ||COALESCE(name_in,'null')||')')
- as f(retcode numeric);
-
- return ret_val;
-END;
-$$ LANGUAGE PLPGSQL;
-
-
-
diff --git a/schema/spacewalk/postgres/procs/lookup_tag.sql
b/schema/spacewalk/postgres/procs/lookup_tag.sql
index 9042e72..4dd818d 100644
--- a/schema/spacewalk/postgres/procs/lookup_tag.sql
+++ b/schema/spacewalk/postgres/procs/lookup_tag.sql
@@ -18,11 +18,10 @@
CREATE OR REPLACE FUNCTION
-LOOKUP_TAG_AUTONOMOUS(org_id_in IN NUMERIC, name_in IN NUMERIC)
+LOOKUP_TAG(org_id_in IN NUMERIC, name_in IN VARCHAR)
RETURNS NUMERIC
as $$
DECLARE
-
tag_id NUMERIC;
BEGIN
select id into tag_id
@@ -36,29 +35,5 @@ BEGIN
END IF;
RETURN tag_id;
-
END; $$
LANGUAGE PLPGSQL;
-
-
-CREATE OR REPLACE FUNCTION
-LOOKUP_TAG(org_id_in IN NUMERIC, name_in IN VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
-
- ret_val NUMERIC;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_TAG_AUTONOMOUS('
- ||COALESCE(org_id_in::numeric,'null')||','
- ||COALESCE(name_id,'null')||')')
- as f(retcode numeric);
-
- RETURN ret_val;
-
-END;
-$$
-LANGUAGE PLPGSQL;
-
diff --git a/schema/spacewalk/postgres/procs/lookup_tag_name.sql
b/schema/spacewalk/postgres/procs/lookup_tag_name.sql
index 2c472d2..43b9857 100644
--- a/schema/spacewalk/postgres/procs/lookup_tag_name.sql
+++ b/schema/spacewalk/postgres/procs/lookup_tag_name.sql
@@ -18,7 +18,7 @@
CREATE OR REPLACE FUNCTION
-LOOKUP_TAG_NAME_AUTONOMOUS(name_in IN VARCHAR)
+LOOKUP_TAG_NAME(name_in IN VARCHAR)
RETURNS NUMERIC
AS
$$
@@ -35,28 +35,5 @@ BEGIN
END IF;
RETURN name_id;
-
END;
$$ LANGUAGE PLPGSQL;
-
-
-CREATE OR REPLACE FUNCTION
-LOOKUP_TAG_NAME(name_in IN VARCHAR)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val NUMERIC;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT LOOKUP_TAG_NAME_AUTONOMOUS('
- ||COALESCE(name_in,'null')||')')
- as f(retcode numeric);
-
-
- RETURN ret_val;
-END;
-$$ LANGUAGE PLPGSQL;
-
-
-
diff --git a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
index bcd64da..d03cbbf 100644
--- a/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
+++ b/schema/spacewalk/postgres/procs/lookup_transaction_package.sql
@@ -16,29 +16,27 @@
--
--
-
-CREATE OR REPLACE FUNCTION LOOKUP_TRANSACTION_PACKAGE_AUTONOMOUS(o_in IN VARCHAR, n_in IN
VARCHAR,
+CREATE OR REPLACE FUNCTION
+LOOKUP_TRANSACTION_PACKAGE(o_in IN VARCHAR, n_in IN VARCHAR,
e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR, a_in IN VARCHAR)
RETURNS NUMERIC
AS
$$
DECLARE
- o_id NUMERIC;
+ o_id NUMERIC;
n_id NUMERIC;
e_id NUMERIC;
p_arch_id NUMERIC;
tp_id NUMERIC;
BEGIN
- BEGIN
- SELECT id
- INTO o_id
- FROM rhnTransactionOperation
- WHERE label = o_in;
+ SELECT id
+ INTO o_id
+ FROM rhnTransactionOperation
+ WHERE label = o_in;
+
IF NOT FOUND THEN
PERFORM rhn_exception.raise_exception('invalid_transaction_operation');
END IF;
-
- END;
SELECT LOOKUP_PACKAGE_NAME(n_in)
INTO n_id;
@@ -50,8 +48,7 @@ BEGIN
IF a_in IS NOT NULL
THEN
SELECT LOOKUP_PACKAGE_ARCH(a_in)
- INTO p_arch_id
- FROM dual;
+ INTO p_arch_id;
END IF;
SELECT id
@@ -61,7 +58,6 @@ BEGIN
AND name_id = n_id
AND evr_id = e_id
AND (package_arch_id = p_arch_id OR (p_arch_id IS NULL AND package_arch_id IS
NULL));
-
IF NOT FOUND THEN
INSERT INTO rhnTransactionPackage
@@ -73,29 +69,3 @@ BEGIN
END;
$$
LANGUAGE PLPGSQL;
-
-
-CREATE OR REPLACE FUNCTION LOOKUP_TRANSACTION_PACKAGE(o_in IN VARCHAR, n_in IN VARCHAR,
- e_in IN VARCHAR, v_in IN VARCHAR, r_in IN VARCHAR, a_in IN VARCHAR)
-RETURNS NUMERIC
-AS $$
-DECLARE
- ret_val NUMERIC;
-BEGIN
-
- select retcode into ret_val
- from dblink('dbname='||current_database(),
- 'select
LOOKUP_TRANSACTION_PACKAGE_AUTONOMOUS('||coalesce(name_in::varchar,'null')||','
- ||COALESCE(o_in::varchar,'null')||','
- ||COALESCE(n_invarchar,'null')||','
- ||COALESCE(e_in::varchar,'null')||','
- ||COALESCE(v_in::varchar,'null')||','
- ||COALESCE(r_in::varchar,'null')||','
- ||COALESCE(a_in::varchar,'null')||')')
- as f(retcode numeric);
-
- RETURN ret_val;
-END;
-$$ language plpgsql;
-
-
diff --git a/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
b/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
index 5a706af..d5e83fa 100644
--- a/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
+++ b/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql
@@ -16,81 +16,41 @@
--
--
+--
+-- TODO: look closer at usage of this. Probably we should get rid of the
+-- looping and do the delete in a single command. Oracle's problems with
+-- large transactions are not relevant to Postgres.
+--
-create or replace function pxt_session_cleanup_autonomous
- (bound_in in numeric, commit_interval in numeric ,
-batch_size in numeric , sessions_deleted in numeric)
-
+create or replace function pxt_session_cleanup
+ (bound_in in numeric, commit_interval in numeric default 100,
+ batch_size in numeric default 50000, sessions_deleted in numeric default 0)
returns numeric
as
$$
declare
-
sessions cursor (bound_val numeric) for
select rowid from PXTSessions
where expires < bound_val;
counter numeric := 0;
-
- sessions_curs_rowid numeric;
-
- sessions_deleted_tmp numeric :=0;
- commit_interval_tmp numeric := 100;
- batch_size_tmp numeric := 50000;
-
-
begin
- open sessions(bound_in);
-
- loop
- fetch sessions into sessions_curs_rowid;
- exit when not found;
-
- delete from PXTSessions where rowid = sessions_curs_rowid;
+ for session in sessions (bound_in) loop
+ delete from PXTSessions where rowid = session.rowid;
+
counter := counter + 1;
- if mod(counter, commit_interval_tmp) = 0 then
- commit;
- end if;
- if counter >= batch_size_tmp then
- commit;
- sessions_deleted_tmp := counter;
+ -- commit_interval is ignored
- return sessions_deleted_tmp;
+ if counter >= batch_size then
+ return counter;
end if;
-
- end loop;
-
- --sessions_deleted := counter;
-
- return counter;
+ end loop;
+
+ return counter;
end;
$$
language plpgsql;
-
-
-CREATE OR REPLACE FUNCTION pxt_session_cleanup(bound_in in numeric, commit_interval in
numeric ,
-batch_size in numeric , sessions_deleted in numeric)
-RETURNS NUMERIC
-AS
-$$
-DECLARE
- ret_val numeric;
-BEGIN
- SELECT retcode into ret_val from dblink('dbname='||current_database(),
- 'SELECT pxt_session_cleanup_autonomous('
- ||COALESCE(bound_in::numeric,'null')||','
- ||COALESCE(commit_interval::numeric,'null')||','
- ||COALESCE(batch_size::numeric,'null')||
- ||COALESCE(sessions_deleted::numeric,'null')||')')
- as f (retcode numeric);
-
-
- return ret_val;
-END;
-$$ LANGUAGE PLPGSQL;
-
-