A flag on the messageblks field would certainly help, and allow webmail
applications to generate message lists with one query
E.g. select messages.*, messageblks.messageblk as headers from
messages,messageblks where
messages.message_idnr=messageblks.messageblks_idnr and
messageblks.isheader=1 order by <SORT KEY>
Currently this query requires a group to get the first block, which is very
costly in database CPU.
However what this does is only give the option to sort on sort-keys in the
messages table (e.g. itnernal date, etc) and still obviously requires
parsing of the headers. Certainly an improvement though.
It doesn't easly allow access to the 'metadata' discussed earlier though,
which would be nice. E.g. in a webmail application it's good to show whether
there is an attachment with the application. Whether it's been replied to,
etc. If we're heading down the Exchange replacement path this may be a
requirement -- as with Outlook you can add all sorts of custom storage
fields.
Ed's proposed structure (or perhaps Magnus' by storing the field
defininition (e.g. "from) in the header table certainly helps from a parsing
mechanism, but for a mailbox of 1000 messages, you're going to need 1 query
to get message list, and then 1000 queries -- vs. 1 join if you get the
table structure into a 1-to-1 form.
Parsing certainly isn't a huge overhead. So the 'isheader' flag on
messageblks means you get this. But parsing header blocks can be tedious at
times, as the format varies (and you get badly constructed headers being
sent through), etc. You need to decode quoted printable, etc or handle
varying character sets.
Maybe we could try a dbmail-specific header table / metadata format; that
was heavily optimised for simple-parsing; and perhaps only cached the
headers/metadata we needed. I'm guessing that at this time the charset would
be coverted to the 'native charset' for the user of the system.
E.g. "key:value|key2.value2"
Provided this is matched 1-to-1, joins would be pretty trivial;
output-parsers (not necessarily the parser that initially creates the
metadata) would be really basic, etc. For searches it would probably also be
easy to do just using SQL:
select ... From msgs,msgblks ... And metadata like
'%|SEARCHFIELD:SEARCHVALUE|%'
/Mark
