Surely for webmail applications, etc you're after a join where you get the headers as columns for displaying a message list; to avoid having nested queries.
E.g. avoid "select * from messages"; then for each message "select * from headers where messageid=X" I know the goal is to have the headers table really flexible so that additional headers can be added, etc -- but maybe this could be done differently. A run-time config file where you mapped the column names/numbers to header names, etc. That way if a developer wanted to add a new column and start caching a new header they could alter the table and simply update the config files, one-by-one. Or disable the headercache altogether. I could be missing something here -- is there a query which can turn a one-to-many join into a single line? /Mark > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Stone > Sent: Thursday, 18 March 2004 6:30 p.m. > To: DBMAIL Developers Mailinglist > Subject: Re: [Dbmail-dev] NNTP > > Seems terribly complicated, and it would make the messages > very difficult to > dump by hand. I'm also not sure why you want to have two > tables; is that what > you're proposing: > > header_labels (index on label) > id label > -------------- > 1 to > 2 from > 3 subject > > message_headers (index on messageid, labelid, header and > labelid, header) > ----------------------------------------------------- > id message_id label_id header > 1 1 1 [EMAIL PROTECTED] > 2 1 2 [EMAIL PROTECTED] > 3 1 3 Hey Bob, it's Joe! > > > I think that unless your database has miraculously good > JOINs, this is a > nightmare; note that you cannot reassemble by returning rows > in ascending > order of the 'id' column, another column would be needed to > keep the order of > the headers. This is probably the most compact was to store > the headers, > though, but I think it is at the cost of being severely slow > and obtuse. > > My proposed header looks like this: > > fastheaders (index on messageid, header, contents and > header, contents) > id message_id header contents > 1 1 to [EMAIL PROTECTED] > 2 1 from [EMAIL PROTECTED] > > > Suffers from using a lot more space, but would have faster > search times. > Administrators would be able to more easily query the table > by hand. As it is > intended to only be a cache, one could freely zap this table > and rebuild it > from the original headers. Naturally, I'm strongly advocating > my idea ;-) > > Aaron > > > > ""Ed K."" <[EMAIL PROTECTED]> said: > [snip] > > What if a solution was proposed in which the order and the > formatting of > > the headers could be preserved. A documented method to > deconstruct and > > construct the headers, and working code to be included in > db.c. Then a flag > > in the messageblks row that would indicate if the header is > either only, > > also, or not in the fastheaders table. i suggest we call > the table pair > > message_headers and header_labels. > > > > ed > > > > Security on the internet is impossible without strong, open, > > and unhindered encryption. > > > > _______________________________________________ > > Dbmail-dev mailing list > > [email protected] > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev > > > > > > -- > > > > _______________________________________________ > Dbmail-dev mailing list > [email protected] > http://twister.fastxs.net/mailman/listinfo/dbmail-dev >
