Oops. Sorry guys... its early here and i havent had my morning coffee :(
P
----- Original Message -----
From: "Patrick" <patrick@xsinet.com>
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" <aaron@serendipity.palo-alto.ca.us>
| 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"" <mark@orcon.net.nz> 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: dbmail-dev-bounces@dbmail.org
|| > > [mailto:dbmail-dev-bounces@dbmail.org] 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 bob@dbmail
|| > > 2 1 2 joe@sender
|| > > 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 bob@dbmail
|| > > 2 1 from joe@sender
|| > >
|| > >
|| > > 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."" <ed@hp.uab.edu> 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
|
|
P
----- Original Message -----
From: "Patrick" <patrick@xsinet.com>
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" <aaron@serendipity.palo-alto.ca.us>
| 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"" <mark@orcon.net.nz> 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: dbmail-dev-bounces@dbmail.org
|| > > [mailto:dbmail-dev-bounces@dbmail.org] 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 bob@dbmail
|| > > 2 1 2 joe@sender
|| > > 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 bob@dbmail
|| > > 2 1 from joe@sender
|| > >
|| > >
|| > > 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."" <ed@hp.uab.edu> 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
|
|