Paul J Stevens wrote:
Matthew T. O'Connor wrote:
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
Breaking out the header names to a table of their own may be useful.
But doing it just for storage's sake seems a bit overkill given the
added complexity in constructing queries and maintaining data
integrity. If it's boosts performance for the target use-cases
(search,sort,thread) I all for it, if.
Agreed, the only reason I propose the header_list table is for
performance reasons. This allows the header_values table (which will be
much bigger) to be searched based on an int comparison rather than text
search. I think this is a serious performance boost, but if it's proven
that it's not, then we don't need it.
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)
Header_order will never happen. Recreating headers from the header
tables will never happen. Complete headers are stored in the
messageblk. Also, header-order from the original message is already
*not* being maintained. Gmime does it's own reformatting and
reshuffling of the headers.
Ok, I wasn't sure, but that helps make things more simple if we don't
need it.
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.
Agreed. Starting with a single separate headers table, or with two
tables like you propose will probably be the starting point. Once we
have consistent storage of headers, it will be relatively easy to move
certain headers to tables of their own, or merge them into the
physmessage table. Of course, postgres users could probably even use
triggers for stuff like that.
Glad you agree this is a good starting point. I hope that we don't ever
need to special case any headers, but only time will tell. Perhaps the
special casing is that we hard code certain headers in the header_list
table so that we always know that sendername has a header_id of 1
therefore the code can automatically search the headers table without
having to do that lookup.
Either way, if we can get away without special casing any headers it
should make the code related to header searching very easy.
What do you think? I don't think we need to special case any headers
not even sendername or subject.
Well, yukatan's datamodel looks like a very serious attempt at
optimizing datastorage for email. My working assumption is that there
are some very valid reasons for doing it the way they're doing things.
Also, as a long term goal, a unified model for sql based email storage
is something I think about.
I just took a look at the yuktan datamodel, it looks nice, and it make a
lot more sense then ours, but they are only supporting PostgreSQL so
they don't have to deal with all the Mysql limitations. They use
foreign keys to manage deletes for them and maintain integreity. They
create custom domains for most of their datatypes. They also keep the
entire message in one field in the messages table which we don't do
because of the size limitations of MySQL (at least older mysql...)
The other major thing they have on us is that they manage each MIME
entity inside an email separately. This has nice advantages for
searching headers of attachments etc.. That would be a large change
from our datamodel, but might be nice to think about someday.
Their model makes a lot of sense in many ways, but I still don't like
some of it. They special case a handful of headers for each MIME entity
not only by having a separate copy in the entity table but also by
having a separate table for many of these headers. Perhaps someday we
can add this for performance reasons, but I don't think we need to.
Yukatan also has a headers table much like the one I described above,
they don't have the header_list table broken out the way I do, but I
think that is why then need to special case alot of the headers.
So in summary, yes the Yukatan model is nice, and has a lot of
advantages over ours, but I still think we are best served by starting
with the two table design I described earlier. This should be
sufficiently fast and flexible that we can go a long way before we have
to special case anything.
Matthew
