[Dbmail-dev] postgres create table script creates incorrect physmessage table

[Paul F. De La Cruz]

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