Hallo everyone,
While doing testing on dbmail 2.0rc2 with Micro$oft Outlook using IMAP to grab mail from dbmail, I found an interesting problem.
I'm using postgresql 7.2 for the db backend and the sql/postgres/create_tables.pgsql script specifies the following for the physmessage table:
CREATE SEQUENCE physmessage_id_seq;
CREATE TABLE physmessage (
id INT8 DEFAULT nextval('physmessage_id_seq'),
messagesize INT8 DEFAULT '0' NOT NULL,
rfcsize INT8 DEFAULT '0' NOT NULL,
internal_date TIMESTAMP,
PRIMARY KEY(id)
);
The problem is that specifying TIMESTAMP to PostgreSQL 7.1 and above will make PostgreSQL default to what they call 'timestamp with time zone' which looks like 2004-02-24 14:58:42.167476-05 and is internally known as timestamptz. This causes Outlook (didn't see this behavior on KMail with IMAP though), to show the Received Date as 11/03/1979 12:00AM which of course isn't correct. Upon Outlook's first display of a new message, it shows the correct Received date but then something updates (or you click on the message) and the date gets transformed to 11/03/1979 12:00AM and thereafter stays that way within Outlook.
There wouldn't be any problem if it were not for the date_sql2imap function expecting to see a mysql format date, as specified in imaputil.c:
-- excerpt from imaputil.c --
/*
* convert a mySQL date (yyyy-mm-dd hh:mm:ss) to a valid IMAP internal date:
* 0123456789012345678
* dd-mon-yyyy hh:mm:ss with mon characters (i.e. 'Apr' for april)
* 01234567890123456789
* return value is valid until next function call.
* NOTE: sqldate is not tested for validity. Behaviour is undefined for non-sql
* dates.
*/
char *date_sql2imap(const char *sqldate)
{
int mon;
if (strlen(sqldate) != strlen("yyyy-mm-dd hh:mm:ss"))
{
strcpy(_imapdate, "03-Nov-1979 00:00:00");
return _imapdate;
}
-- end excerpt from imaputil.c --
date_sql2imap specifically returns the 03-Nov-1979 00:00:00 date/time when the sql date returned by the db backend is longer than the default date format it expects to convert.
I don't know if the code should be changed to handle the timezone or not, but I do know that I was able to fix this behavior and have not seen any problems with the following specification for the physmessage table:
CREATE SEQUENCE physmessage_id_seq;
CREATE TABLE physmessage (
id INT8 DEFAULT nextval('physmessage_id_seq'),
messagesize INT8 DEFAULT '0' NOT NULL,
rfcsize INT8 DEFAULT '0' NOT NULL,
internal_date TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY(id)
);
So that's about it for that... any ideas as to what should be done?
Blessings in Jesus,
Paul F. De La Cruz
While doing testing on dbmail 2.0rc2 with Micro$oft Outlook using IMAP to grab mail from dbmail, I found an interesting problem.
I'm using postgresql 7.2 for the db backend and the sql/postgres/create_tables.pgsql script specifies the following for the physmessage table:
CREATE SEQUENCE physmessage_id_seq;
CREATE TABLE physmessage (
id INT8 DEFAULT nextval('physmessage_id_seq'),
messagesize INT8 DEFAULT '0' NOT NULL,
rfcsize INT8 DEFAULT '0' NOT NULL,
internal_date TIMESTAMP,
PRIMARY KEY(id)
);
The problem is that specifying TIMESTAMP to PostgreSQL 7.1 and above will make PostgreSQL default to what they call 'timestamp with time zone' which looks like 2004-02-24 14:58:42.167476-05 and is internally known as timestamptz. This causes Outlook (didn't see this behavior on KMail with IMAP though), to show the Received Date as 11/03/1979 12:00AM which of course isn't correct. Upon Outlook's first display of a new message, it shows the correct Received date but then something updates (or you click on the message) and the date gets transformed to 11/03/1979 12:00AM and thereafter stays that way within Outlook.
There wouldn't be any problem if it were not for the date_sql2imap function expecting to see a mysql format date, as specified in imaputil.c:
-- excerpt from imaputil.c --
/*
* convert a mySQL date (yyyy-mm-dd hh:mm:ss) to a valid IMAP internal date:
* 0123456789012345678
* dd-mon-yyyy hh:mm:ss with mon characters (i.e. 'Apr' for april)
* 01234567890123456789
* return value is valid until next function call.
* NOTE: sqldate is not tested for validity. Behaviour is undefined for non-sql
* dates.
*/
char *date_sql2imap(const char *sqldate)
{
int mon;
if (strlen(sqldate) != strlen("yyyy-mm-dd hh:mm:ss"))
{
strcpy(_imapdate, "03-Nov-1979 00:00:00");
return _imapdate;
}
-- end excerpt from imaputil.c --
date_sql2imap specifically returns the 03-Nov-1979 00:00:00 date/time when the sql date returned by the db backend is longer than the default date format it expects to convert.
I don't know if the code should be changed to handle the timezone or not, but I do know that I was able to fix this behavior and have not seen any problems with the following specification for the physmessage table:
CREATE SEQUENCE physmessage_id_seq;
CREATE TABLE physmessage (
id INT8 DEFAULT nextval('physmessage_id_seq'),
messagesize INT8 DEFAULT '0' NOT NULL,
rfcsize INT8 DEFAULT '0' NOT NULL,
internal_date TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY(id)
);
So that's about it for that... any ideas as to what should be done?
Blessings in Jesus,
Paul F. De La Cruz