Personally, I think we should use a very clean and generic design for
headers / header searching.
First: Leave the current table structures intact, I don't think we
require any modifications to them.
Second: Add 2 new tables: header_list and header_values
header_list: ( Contains an exhaustive list of all headers from all
messages in the database. )
header_id int primary key
header text not null
header_values: ( Contains the values from all the headers in all the
messages in database )
header_value_id serial primary key
message_id int (references unique message ID from the
physmessage table)
header_id int (references unique ID from the header_list table)
header_value text (the actual value from this header in this message)
hearder_order int ( optional column, used to be able to recreate
the header order from the original message)
This structure will make it very easy to query all the headers from a
given message or find all the messages with a given header, or a given
header value. It also leaves our current structure intact which will
make it easier to phase in.
What do you think? I don't think we need to special case any headers
not even sendername or subject.
Matthew
Paul J Stevens wrote:
Hi all,
I'm working towards header storage. Came across what looks like a
very useful design at http://yukatan.sourceforge.net
Though the design is not a perfect fit for dbmail there are some very
useful ideas in there. In fact they combine some of the ideas posted
on the list before.
Add some fields to the physmessage table (these all refer to rfc2822
headers).
sendername varchar(xx),
senderaddr varchar(xx),
subject varchar(xx),
messageid varchar(xx)
Add a separate headerfield table which will contain *all* headers for
a given message.
CREATE TABLE headerfield (
physmessage_id int NOT NULL,
CONSTRAINT headerfield_physmsg
FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id)
ON UPDATE CASCADE ON DELETE CASCADE,
fieldno INTEGER NOT NULL,
CONSTRAINT headerfield_key
PRIMARY KEY (physmessage_id, fieldno),
fieldname VARCHAR(128) NOT NULL DEFAULT '',
fieldbody VARCHAR(255) NOT NULL DEFAULT ''
);
And split out some very common headers to their own tables along the
same lines:
CREATE TABLE inreplytofield (...);
CREATE TABLE referencesfield (...);
CREATE TABLE fromfield (...);
CREATE TABLE replytofield (...);
CREATE TABLE tofield (...);
CREATE TABLE ccfield (...);
But perhaps these should be merged into the physmessage table.
Of course, when all fields are extracted into a separate header table,
splitting them out to a table of their own, or merging them into the
physmessage table is not that difficult and will - I'm sure - be
subject to further discussion.
and a happy newyear everybody,
