Oops. Sorry guys... its early here and i havent had my morning coffee :( P ----- Original Message ----- From: "Patrick" <[EMAIL PROTECTED]> To: "DBMAIL Developers Mailinglist" <dbmail-dev@dbmail.org> Sent: Thursday, March 18, 2004 8:58 AM Subject: Re: [Dbmail-dev] Cached Headers (was NNTP)
| Haai, | | This is a major problem with most 'redundant' database based systems as far | as i understand it ... When i looked as sql relay it didnt seem to do | anything natively with the database. And considering i havent used it much i | wouldnt be able to yay or nay it. The stuff in the white papers about | backplane seem to be rather advanced and seem to fix alot of my personal | gripes with running a system such as DBMail. Redundancy in a mail system was | a really big thing for me considering that the biggest part of the internet | from an isp/companys point of view is E-Mail and downtime is not always an | option. | | Another one of the features that i would think would be rather usefull would | be the SNAPShots ( for backup purposes ) I currently backup a mail | filesystem with XFSDump which is nice and allows me to make file system | snapshots. But this is not the ultimate solution in a large system because | of the nature of E-Mail in those enviroments. | | Would adding SQL Relay not add another possible point of failure ? I know | with systems like portfwd and suchlike they arent created to handle high | loads of traffic or mass ammounts of threading ( as i say again i havent | used SQL Relay :) ) ? | | Comments ? | | P | ----- Original Message ----- | From: "Aaron Stone" <[EMAIL PROTECTED]> | To: "DBMAIL Developers Mailinglist" <dbmail-dev@dbmail.org> | Sent: Thursday, March 18, 2004 8:23 AM | Subject: RE: [Dbmail-dev] Cached Headers (was NNTP) | | || I'm thinking in terms of searches primarily, where a query might be: || || select distinct(message_id) from fastheaders || where header = 'to' and contents like '%bob%' || || You're looking for displaying the message, where the webmail application || probably doesn't want to read in the header blocks of the message and | parse || them just to show the Date, From, Subject, To, CC, Bcc, Fcc, Reply-To | fields. || || Originally I suggested using a table that has the header fields as the || columns, thinking that those were the only commonly requested ones, but it | was || either Ilja or Roel at the time who watched for a couple of IMAP queries | and || saw all kinds of headers being requested both for searching and for | listing || messages. So the configurable columns thing means that the admin needs to | be || watching to see what fields his clients are requesting, and to add those, | and || it means quite a bit more complexity in DBMail, which would need to either || find the header in the header cache table or parse the full headers. || || For your webmail application, since you know exactly which header fields | you || want and you're writing your own queries, you can just do this: || || select header, contents from fastheaders || where message_id = 82 and (header = 'To' or header = 'From' or ...) || || My instinct is that this would still be reasonably fast, completely | flexible || and generic. Even if the query took slightly longer, the much smaller | result || set size and the negligible parsing needed would offset the query speed. || || Aaron || || || ""Mark Mackay - Orcon"" <[EMAIL PROTECTED]> said: || || > 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 || > > > Dbmail-dev@dbmail.org || > > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev || > > > || > > || > > || > > || > > -- || > > || > > || > > || > > _______________________________________________ || > > Dbmail-dev mailing list || > > Dbmail-dev@dbmail.org || > > http://twister.fastxs.net/mailman/listinfo/dbmail-dev || > > || > || > _______________________________________________ || > Dbmail-dev mailing list || > Dbmail-dev@dbmail.org || > http://twister.fastxs.net/mailman/listinfo/dbmail-dev || > || || || || -- || || || || _______________________________________________ || Dbmail-dev mailing list || Dbmail-dev@dbmail.org || http://twister.fastxs.net/mailman/listinfo/dbmail-dev || || | | _______________________________________________ | Dbmail-dev mailing list | Dbmail-dev@dbmail.org | http://twister.fastxs.net/mailman/listinfo/dbmail-dev | |