backend/server/rhnChannel.py | 2
backend/server/rhnSQL/driver_postgresql.py | 47 +++++++---
java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml | 44 +++++----
java/code/src/com/redhat/rhn/frontend/dto/SystemOverview.java | 2
java/code/src/com/redhat/rhn/taskomatic/core/SchedulerKernel.java | 3
schema/spacewalk/postgres/packages/rhn_channel.pkb | 31 +++---
schema/spacewalk/postgres/procs/numtodsinterval.sql | 13 +-
7 files changed, 84 insertions(+), 58 deletions(-)
New commits:
commit 2f7642ce88ca59b530480e43e201a8786858d846
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Wed Oct 27 11:27:57 2010 +0200
In PostgreSQL NUMERIC types are returned as int or float now
diff --git a/backend/server/rhnSQL/driver_postgresql.py
b/backend/server/rhnSQL/driver_postgresql.py
index b68d2f0..960ff74 100644
--- a/backend/server/rhnSQL/driver_postgresql.py
+++ b/backend/server/rhnSQL/driver_postgresql.py
@@ -100,6 +100,10 @@ class Procedure(Function):
def decimal2intfloat(dec, cursor):
"Convert a Decimal to an int or a float with no loss of information"
+ if dec is None:
+ return None
+ if not isinstance(dec, decimal.Decimal):
+ return dec
oldcontext = decimal.getcontext()
decimal.setcontext(decimal.Context(traps=[decimal.Inexact]))
try:
commit fc40acb8e7ff0e0631cbf8dd295ec33e294e8c37
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Wed Oct 27 11:26:40 2010 +0200
Fixing indentation
diff --git a/backend/server/rhnSQL/driver_postgresql.py
b/backend/server/rhnSQL/driver_postgresql.py
index e8b82f4..b68d2f0 100644
--- a/backend/server/rhnSQL/driver_postgresql.py
+++ b/backend/server/rhnSQL/driver_postgresql.py
@@ -120,7 +120,7 @@ class Database(sql_base.Database):
""" Class for PostgreSQL database operations. """
def __init__(self, host=None, port=None, username=None,
- password=None, database=None):
+ password=None, database=None):
self.host = host
self.port = port
@@ -143,13 +143,13 @@ class Database(sql_base.Database):
try:
if self.host is None:
self.dbh = psycopg2.connect(database=self.database, user=self.username,
- password=self.password)
+ password=self.password)
else:
self.dbh = psycopg2.connect(database=self.database, user=self.username,
- password=self.password, host=self.host, port=self.port)
+ password=self.password, host=self.host,
port=self.port)
# convert all DECIMAL types to float (let Python to choose one)
DEC2INTFLOAT =
psycopg2.extensions.new_type(psycopg2.extensions.DECIMAL.values,
- 'DEC2INTFLOAT', decimal2intfloat)
+ 'DEC2INTFLOAT',
decimal2intfloat)
psycopg2.extensions.register_type(DEC2INTFLOAT)
except Exception, e:
if reconnect:
@@ -158,16 +158,16 @@ class Database(sql_base.Database):
# Failed reconnect, time to error out:
raise apply(sql_base.SQLConnectError,
- [self.database, e.pgcode, e.pgerror, "Attempting Re-Connect to the
database failed",])
+ [self.database, e.pgcode, e.pgerror, "Attempting Re-Connect
to the database failed",])
def is_connected_to(self, backend, host, port, username, password,
- database):
+ database):
adjusted_port = -1
if port:
adjusted_port = port
return (backend == POSTGRESQL) and (self.host == host) and \
- (self.port == adjusted_port) and (self.username == username) \
- and (self.password == password) and (self.database == database)
+ (self.port == adjusted_port) and (self.username == username) \
+ and (self.password == password) and (self.database == database)
def check_connection(self):
try:
@@ -248,9 +248,9 @@ class Cursor(sql_base.Cursor):
def _execute_wrapper(self, function, *p, **kw):
params = ','.join(["%s: %s" % (key, value) for key, value \
- in kw.items()])
+ in kw.items()])
log_debug(5, "Executing SQL: \"%s\" with bind params: {%s}"
- % (self.sql, params))
+ % (self.sql, params))
if self.sql is None:
raise rhnException("Cannot execute empty cursor")
@@ -297,14 +297,14 @@ class Cursor(sql_base.Cursor):
return rowcount
def update_blob(self, table_name, column_name, where_clause, data,
- **kwargs):
+ **kwargs):
"""
PostgreSQL uses bytea columns instead of blobs. Nothing special
needs to be done to insert text into one.
"""
# NOTE: Injecting a :column_name parameter here
sql = "UPDATE %s SET %s = :%s %s" % (table_name, column_name,
- column_name, where_clause)
+ column_name, where_clause)
c = rhnSQL.prepare(sql)
kwargs[column_name] = data
apply(c.execute, (), kwargs)
commit a79823af829dac9382f6b061391c4dcc14a994a4
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Wed Oct 27 10:55:48 2010 +0200
In PostgreSQL NUMERIC types are returned as int or float now
diff --git a/backend/server/rhnSQL/driver_postgresql.py
b/backend/server/rhnSQL/driver_postgresql.py
index 31e6ac1..e8b82f4 100644
--- a/backend/server/rhnSQL/driver_postgresql.py
+++ b/backend/server/rhnSQL/driver_postgresql.py
@@ -98,6 +98,23 @@ class Procedure(Function):
#if not (type(result) == 'tuple' and result[0] == ''):
#raise rhnSQL.SQLError("Unexpected result returned by procedure %s:
%s" % (self.name, str(result)))
+def decimal2intfloat(dec, cursor):
+ "Convert a Decimal to an int or a float with no loss of information"
+ oldcontext = decimal.getcontext()
+ decimal.setcontext(decimal.Context(traps=[decimal.Inexact]))
+ try:
+ while True:
+ try:
+ # try to quantize to integer
+ return int(dec.quantize(decimal.Decimal(1)))
+ except decimal.Inexact:
+ # if not exact return float
+ return float(dec)
+ except:
+ # if anything bad happends return float as well
+ return float(dec)
+ finally:
+ decimal.setcontext(oldcontext)
class Database(sql_base.Database):
""" Class for PostgreSQL database operations. """
@@ -130,6 +147,10 @@ class Database(sql_base.Database):
else:
self.dbh = psycopg2.connect(database=self.database, user=self.username,
password=self.password, host=self.host, port=self.port)
+ # convert all DECIMAL types to float (let Python to choose one)
+ DEC2INTFLOAT =
psycopg2.extensions.new_type(psycopg2.extensions.DECIMAL.values,
+ 'DEC2INTFLOAT', decimal2intfloat)
+ psycopg2.extensions.register_type(DEC2INTFLOAT)
except Exception, e:
if reconnect:
# Try one more time:
commit 2a6a07710ddd59b2a43f27f6651c3e3cac20dcad
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 13:32:58 2010 +0200
Rewritten DECODE to ANSI CASE-WHEN syntax for yum
diff --git a/backend/server/rhnChannel.py b/backend/server/rhnChannel.py
index ae097ef..0357fd9 100644
--- a/backend/server/rhnChannel.py
+++ b/backend/server/rhnChannel.py
@@ -709,7 +709,7 @@ def channels_for_server(server_id):
c.summary,
c.description,
c.gpg_key_url,
- decode(s.org_id, c.org_id, 1, 0) local_channel,
+ case s.org_id when c.org_id then 1 else 0 end local_channel,
TO_CHAR(c.last_modified, 'YYYYMMDDHH24MISS') last_modified
from
rhnChannelArch ca,
commit 0bb8c201eb593018ffcb66aded1321106acda81f
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 13:24:33 2010 +0200
Addresing recursion with opened cursor in PostgreSQL
ERROR: cursor "local_chk_server_parent_memb" already in use
diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pkb
b/schema/spacewalk/postgres/packages/rhn_channel.pkb
index 5ad5b79..306af77 100644
--- a/schema/spacewalk/postgres/packages/rhn_channel.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_channel.pkb
@@ -447,21 +447,17 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
from rhnChannelFamily
where id = channel_family_id_in
and label = 'rhn-satellite';
- -- this is *EXACTLY* like check_server_parent_membership, but if we recurse
- -- with the package-level one, we get a "cursor already open", so we
need a
- -- copy on our call stack instead. GROAN.
- local_chk_server_parent_memb cursor (
- server_id_in numeric,
- channel_id_in numeric ) for
- select c.id
+ child record;
+ BEGIN
+ -- In PostgreSQL recursion with opened cursors is not allowed so we use
+ -- FOR IN SELECT form which is ok.
+ FOR child IN select c.id
from rhnChannel c,
rhnServerChannel sc
where 1=1
and c.parent_channel = channel_id_in
and c.id = sc.channel_id
- and sc.server_id = server_id_in;
- BEGIN
- FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in)
+ and sc.server_id = server_id_in
LOOP
if unsubscribe_children_in = 1 then
perform rhn_channel.unsubscribe_server(server_id_in,
commit 1cff427cdc1a8307321a664e35f27d67e1eddcf7
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 12:52:14 2010 +0200
Fixing missing brace in Taskomatic query
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
index cadf234..d9a8c36 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
@@ -361,7 +361,7 @@ AND CC.confchan_type_id =
<query params="threshold">
SELECT enq.errata_id, enq.org_id, enq.channel_id
FROM rhnErrataNotificationQueue ENQ
- WHERE (enq.next_action < current_timestamp + numtodsinterval(:threshold,
'minute')
+ WHERE (enq.next_action < current_timestamp + numtodsinterval(:threshold,
'minute'))
ORDER BY next_action DESC
</query>
</mode>
commit e1f6736e8da39c2ab49c2979c05dacc7eb987d18
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 12:42:22 2010 +0200
Function unsubscribe_server now in sync with Oracle
diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pkb
b/schema/spacewalk/postgres/packages/rhn_channel.pkb
index 120fed3..5ad5b79 100644
--- a/schema/spacewalk/postgres/packages/rhn_channel.pkb
+++ b/schema/spacewalk/postgres/packages/rhn_channel.pkb
@@ -1,5 +1,6 @@
-- This file is not yet in sync with Oracle. Synced functions are:
--- subscribe_server + all subfunctions called
+-- subscribe_server + all subfunctions
+-- unsubscribe_server + all subfunctions
--
-- Copyright (c) 2008--2010 Red Hat, Inc.
--
@@ -428,7 +429,8 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
END$$ language plpgsql;
CREATE OR REPLACE FUNCTION unsubscribe_server(server_id_in IN NUMERIC, channel_id_in
NUMERIC, immediate_in NUMERIC default 1, unsubscribe_children_in numeric default 0,
- deleting_server IN NUMERIC default 0 ) returns void
+ deleting_server in numeric default 0,
+ update_family_countsYN in numeric default 1) returns
void
AS $$
declare
channel_family_id_val NUMERIC;
@@ -445,6 +447,9 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
from rhnChannelFamily
where id = channel_family_id_in
and label = 'rhn-satellite';
+ -- this is *EXACTLY* like check_server_parent_membership, but if we recurse
+ -- with the package-level one, we get a "cursor already open", so we
need a
+ -- copy on our call stack instead. GROAN.
local_chk_server_parent_memb cursor (
server_id_in numeric,
channel_id_in numeric ) for
@@ -463,7 +468,8 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
child.id,
immediate_in,
unsubscribe_children_in,
- deleting_server);
+ deleting_server,
+ update_family_countsYN);
else
perform
rhn_exception.raise_exception('channel_unsubscribe_child_exists');
end if;
@@ -510,16 +516,13 @@ update pg_settings set setting = 'rhn_channel,' || setting
where name = 'search_
for ignore in channel_family_is_proxy(channel_family_id_val) loop
delete from rhnProxyInfo where server_id = server_id_in;
end loop;
-
- DELETE FROM rhnChannelFamilyLicenseConsent
- WHERE channel_family_id = channel_family_id_val
- AND server_id = server_id_in;
-
SELECT org_id INTO server_org_id_val
FROM rhnServer
WHERE id = server_id_in;
- perform rhn_channel.update_family_counts(channel_family_id_val,
server_org_id_val);
+ if update_family_countsYN = 1 then
+ perform rhn_channel.update_family_counts(channel_family_id_val,
server_org_id_val);
+ end if;
END$$ language plpgsql;
CREATE OR REPLACE FUNCTION family_for_channel(channel_id_in IN NUMERIC)
commit e5a6ebc1a8011b8ccf36766d8464205ad6f4380c
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 12:41:57 2010 +0200
Addressing issue in system overview (PG)
diff --git a/java/code/src/com/redhat/rhn/frontend/dto/SystemOverview.java
b/java/code/src/com/redhat/rhn/frontend/dto/SystemOverview.java
index 3faf9b6..e0eef68 100644
--- a/java/code/src/com/redhat/rhn/frontend/dto/SystemOverview.java
+++ b/java/code/src/com/redhat/rhn/frontend/dto/SystemOverview.java
@@ -621,7 +621,7 @@ public class SystemOverview extends BaseDto implements Serializable
{
* null if not selectable
*/
public void setSelectable(Integer selectableIn) {
- setSelectable(new Long(selectableIn));
+ selectable = (selectableIn != null);
}
/**
commit 1be2d7e1a440cee01d4283615ba204d080210587
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 11:53:46 2010 +0200
PostgreSQL needs FROM keyword in DELETE
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
index 166ea21..cadf234 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
@@ -536,7 +536,7 @@ ORDER BY next_action DESC
<write-mode name="erratamailer_clean_work_queue">
<query params="">
- DELETE rhnPaidErrataTempCache
+ DELETE FROM rhnPaidErrataTempCache
</query>
</write-mode>
@@ -605,7 +605,7 @@ ORDER BY next_action DESC
<write-mode name="delete_task">
<query params="org_id, name, task_data, priority">
- DELETE rhnTaskQueue where org_id = :org_id and task_name = :name
+ DELETE FROM rhnTaskQueue where org_id = :org_id and task_name = :name
and task_data = :task_data and priority = :priority
</query>
</write-mode>
commit cc130bcc7df3ccecbdf285a333659048df71c444
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 11:41:38 2010 +0200
Adding missing interval keyword to Taskomatic (PG)
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
index 185a005..166ea21 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
@@ -197,7 +197,7 @@ SELECT id, channel_label, client, reason, force, bypass_filters,
next_action
WHERE KS.state_id = KSS.id
AND ((KSS.label NOT IN ('created', 'complete', 'failed')
AND KS.last_action < current_timestamp - interval '1' day)
OR
- (KSS.label = 'created' AND KS.last_action < current_timestamp -
'6' day))
+ (KSS.label = 'created' AND KS.last_action < current_timestamp -
interval '6' day))
AND (KS.kickstart_mode != 'default_session' OR KS.kickstart_mode is NULL)
</query>
</mode>
commit 83ad542ec692b7a7badb2f56836edf09bfa44122
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 11:19:58 2010 +0200
Protocol config value is now used in Taskomatic
diff --git a/java/code/src/com/redhat/rhn/taskomatic/core/SchedulerKernel.java
b/java/code/src/com/redhat/rhn/taskomatic/core/SchedulerKernel.java
index 809bb53..a4323e7 100644
--- a/java/code/src/com/redhat/rhn/taskomatic/core/SchedulerKernel.java
+++ b/java/code/src/com/redhat/rhn/taskomatic/core/SchedulerKernel.java
@@ -94,10 +94,9 @@ public class SchedulerKernel {
"org.postgresql.Driver");
props.setProperty(ds + ".driver", driver);
- // the format is jdbc:postgresql://localhost:5432/dbname
String dbHost = Config.get().getString(ConfigDefaults.DB_HOST);
String dbPort = Config.get().getString(ConfigDefaults.DB_PORT);
- String dbUrl = "jdbc:postgresql://" + dbHost + ":" +
dbPort + "/" +
+ String dbUrl = dbProto + "://" + dbHost + ":" + dbPort +
"/" +
dbName;
props.setProperty(ds + ".URL", dbUrl);
}
commit 9ffd90f3aef2545a3f8f28719677c338cd716423
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 11:19:14 2010 +0200
Function numtodsinterval fix (PostgreSQL)
diff --git a/schema/spacewalk/postgres/procs/numtodsinterval.sql
b/schema/spacewalk/postgres/procs/numtodsinterval.sql
index 9c5a145..f20f0bf 100644
--- a/schema/spacewalk/postgres/procs/numtodsinterval.sql
+++ b/schema/spacewalk/postgres/procs/numtodsinterval.sql
@@ -14,7 +14,7 @@
-- in this software or its documentation.
--
-create or replace function numtodsinterval(sec_in in numeric, type_in in varchar)
+create or replace function numtodsinterval(num_in in numeric, type_in in varchar)
returns interval
as
$$
commit 03ec57ac9bab8cbfdd9398ca8052408a23240eb6
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 10:50:01 2010 +0200
Getting taskomatic working on PostgreSQL
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
index c3728e4..185a005 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml
@@ -49,7 +49,7 @@ SELECT DISTINCT S.id, S.name, SI.checkin
rhnUserServerPerms USP
WHERE USP.user_id = :user_id
AND USP.server_id = SI.server_id
- AND (sysdate - SI.checkin) BETWEEN 1 AND (1 + :checkin_threshold)
+ AND date_diff_in_days(current_timestamp, SI.checkin) BETWEEN 1 AND (1 +
:checkin_threshold)
AND SI.server_id = S.id
AND NOT EXISTS (
SELECT *
@@ -91,8 +91,8 @@ SELECT AT.name as type,
where sfv.server_id = usp.server_id
and sfv.label = 'ftr_daily_summary')
AND USP.server_id = SA.server_id
- AND sysdate - SA.modified > 0
- AND sysdate - SA.modified < 1
+ AND date_diff_in_days(current_timestamp, SA.modified) > 0
+ AND date_diff_in_days(current_timestamp, SA.modified) < 1
AND SA.status = AStat.id
AND SA.action_id = A.id
AND A.action_type = AT.id
@@ -108,7 +108,10 @@ INSERT
INTO rhnErrataNotificationQueue
(errata_id, org_id, next_action, channel_id)
SELECT DISTINCT
- :errata_id, wc.id, sysdate + :minutes / 1440, :channel_id
+ :errata_id,
+ wc.id,
+ current_timestamp + numtodsinterval(:minutes, 'minute'),
+ :channel_id
FROM web_customer wc,
rhnChannelErrata CE
WHERE CE.errata_id = :errata_id
@@ -170,13 +173,13 @@ SELECT id, channel_label, client, reason, force, bypass_filters,
next_action
<write-mode name="repomd_mark_in_progress">
<query params="channel_label">
- UPDATE rhnRepoRegenQueue set next_action=null, modified=sysdate WHERE channel_label
= :channel_label
+ UPDATE rhnRepoRegenQueue set next_action=null, modified=current_timestamp WHERE
channel_label = :channel_label
</query>
</write-mode>
<write-mode name="repomd_clear_in_progress">
<query params="">
- UPDATE rhnRepoRegenQueue set next_action=sysdate, modified=sysdate WHERE
next_action is null
+ UPDATE rhnRepoRegenQueue set next_action=current_timestamp,
modified=current_timestamp WHERE next_action is null
</query>
</write-mode>
@@ -192,9 +195,9 @@ SELECT id, channel_label, client, reason, force, bypass_filters,
next_action
SELECT KS.id, KSS.label, KS.action_id, KS.new_server_id, KS.old_server_id
FROM rhnKickstartSession KS, rhnKickstartSessionState KSS
WHERE KS.state_id = KSS.id
- AND ((KSS.label NOT IN ('created', 'complete', 'failed')
AND KS.last_action < sysdate - 1)
+ AND ((KSS.label NOT IN ('created', 'complete', 'failed')
AND KS.last_action < current_timestamp - interval '1' day)
OR
- (KSS.label = 'created' AND KS.last_action < sysdate - 6))
+ (KSS.label = 'created' AND KS.last_action < current_timestamp -
'6' day))
AND (KS.kickstart_mode != 'default_session' OR KS.kickstart_mode is NULL)
</query>
</mode>
@@ -231,7 +234,7 @@ SELECT id, channel_label, client, reason, force, bypass_filters,
next_action
<callable-mode name="synchprobestate_synch_proc">
<query params="">
- {call rhn_synch_probe_state}
+ { call rhn_synch_probe_state() }
</query>
</callable-mode>
@@ -264,7 +267,8 @@ inner join rhnServerGroup sg on (s.org_id = sg.org_id)
inner join rhnServerGroupType sgt on (sg.group_type = sgt.id)
where sgt.label in ('enterprise_entitled', 'provisioning_entitled')
and sg.max_members > 0
- and si.checkin between (sysdate + 1) and (sysdate + 1 + :checkin_threshold)
+ and si.checkin between (current_timestamp + interval '1' day)
+ and (current_timestamp + numtodsinterval(1 + :checkin_threshold, 'day')
</query>
</mode>
@@ -281,7 +285,7 @@ select distinct sg.org_id as id
select s.org_id
from rhnServer s, rhnServerINfo si
where s.id = si.SERVER_ID
- AND (sysdate - SI.checkin) BETWEEN 1 AND (1 + :checkin_threshold))
+ AND date_diff_in_days(current_timestamp, SI.checkin) BETWEEN 1 AND (1 +
:checkin_threshold))
</query>
</mode>
@@ -299,7 +303,7 @@ SELECT /*+full(sa)*/ DISTINCT S.org_id as id
AND SG.max_members > 0
AND SG.org_id = S.org_id
AND S.id = SA.server_id
- AND SA.modified between sysdate - 1 and sysdate
+ AND SA.modified between current_timestamp - interval '1' day and
current_timestamp
</query>
</mode>
@@ -335,7 +339,7 @@ FROM rhnConfigFile CF
WHERE CF.config_channel_id IN
(SELECT cc.Id
FROM rhnConfigChannel CC
- WHERE CC.created < sysdate - :window
+ WHERE CC.created < current_timestamp - numtodsinterval(:window,
'day')
AND CC.confchan_type_id =
(SELECT id FROM rhnConfigChannelType WHERE label = 'server_import'))
</query>
@@ -345,7 +349,7 @@ WHERE CF.config_channel_id IN
<query params="window">
SELECT CC.id
FROM rhnConfigChannel CC
-WHERE CC.created < sysdate - :window
+WHERE CC.created < current_timestamp - numtodsinterval(:window, 'day')
AND CC.confchan_type_id =
(SELECT id FROM rhnConfigChannelType WHERE label = 'server_import')
@@ -357,7 +361,7 @@ AND CC.confchan_type_id =
<query params="threshold">
SELECT enq.errata_id, enq.org_id, enq.channel_id
FROM rhnErrataNotificationQueue ENQ
- WHERE (enq.next_action < sysdate + :threshold/1440)
+ WHERE (enq.next_action < current_timestamp + numtodsinterval(:threshold,
'minute')
ORDER BY next_action DESC
</query>
</mode>
@@ -547,14 +551,14 @@ ORDER BY next_action DESC
<write-mode name="create_task_stats">
<query params="display_name">
INSERT INTO rhnDaemonState (LABEL, LAST_POLL)
- VALUES (:display_name, sysdate)
+ VALUES (:display_name, current_timestamp)
</query>
</write-mode>
<write-mode name="update_task_stats">
<query params="display_name">
UPDATE rhnDaemonState
- SET LAST_POLL = sysdate
+ SET LAST_POLL = current_timestamp
WHERE LABEL = :display_name
</query>
</write-mode>
@@ -569,7 +573,7 @@ ORDER BY next_action DESC
<mode name="get_current_time">
<query params="">
- SELECT sysdate
+ SELECT current_timestamp
FROM dual
</query>
</mode>
commit 86493d5bb1e7ae0db6a14301c456ae56dba7d029
Author: Lukas Zapletal <lzap+git(a)redhat.com>
Date: Tue Oct 26 10:49:25 2010 +0200
Function numtodsinterval now accepts days, minutes, hours
diff --git a/schema/spacewalk/postgres/procs/numtodsinterval.sql
b/schema/spacewalk/postgres/procs/numtodsinterval.sql
index ded2883..9c5a145 100644
--- a/schema/spacewalk/postgres/procs/numtodsinterval.sql
+++ b/schema/spacewalk/postgres/procs/numtodsinterval.sql
@@ -19,12 +19,11 @@ returns interval
as
$$
begin
- if type_in != 'second' then
- raise exception 'Function numtodsinterval only supports conversion from [second],
not from [%].', type_in;
- end if;
-
- return sec_in || ' ' || type_in;
+ if type_in = any(array['second', 'hour', 'minute',
'day']) then
+ return num_in || ' ' || type_in;
+ else
+ raise exception 'Function numtodsinterval only supports conversion from [second,
hour, minte, day], not from [%].', type_in;
+ end if;
end;
$$ language plpgsql
stable;
-