This query will get you the headers and the flags of each message in a give mailbox. A strictly ascending messageblk_idnr column is assumed.
select messages.*, min(messageblk_idnr), messageblk from messages, physmessage, messageblks where messages.physmessage_id = physmessage.id = messageblks.physmessage_id and mailbox_idnr = 1 group by physmessage_id \G However, that assumption is exactly one that needs to be thrown away. (gets onto soapbox) We need to move towards the elimination of reliance on autoincrement columns so that eventually we can use [statistically] unique hashes that can be generated by a number of hosts without coordination and with minimal or no risk of collisions. So basically that's 2.1 and later stuff. But keep it in mind. (jumps off soapbox). In the meantime, I'm beginning to believe in the JOIN stuff. I'm not convinced that it's faster than an indexed char column, but I'm going to do some fake data benchmarks to find out. I'm still hugely against storing the headers in a parsed-only format. It just doesn't seem worth the space savings not to keep a copy of the headers in their original off-the-wire format, though provided that it gets eaiser, say by adding an is_header column to messageblks. By the way, the problems about passing the headers around if they huge sized are greatly alleviated if they can be inserted into the database as they arrive and are quickly looked up on an as-needed basis. Of course, that means many more database calls during delivery... Aaron ""Mark Mackay - Orcon"" <[EMAIL PROTECTED]> said: > 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 > > > > > > > _______________________________________________ > Dbmail-dev mailing list > Dbmail-dev@dbmail.org > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > --
