Re: [GENERAL] PG Email Client
Hello Sim, Am 2007-01-29 14:29:27, schrieb Sim Zacks: For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to multiple folders. Current email clients mimic file cabinets too much in that the system considers an email to be a physical entity that can only be stored in one place. Searching for emails can also be done much faster with a database then with a traditional mail client approach. Since some time I am coding an IMAP-Client which heavy search functionality but Server based. My base is Mutt (with GTK2 Interface), Courier-Imap, PostgreSQL and Sieve. The thing is currently in PreAlpha state (it works for me) with realy limited functions,since I have excluded most thinks to reduce errors while coding my stuff. It is only a private Research Project but maybe some times... Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] PG Email Client
Am 2007-01-30 07:56:32, schrieb Sim Zacks: In the traditional imap server the mail is stored in folders on the server that are accessed by the client. Therefore you are limited to one indexed location per email. Lets say you want to look at your email by customer. You would want to have a folder per customer so you can review your correspondence with each one individually. If you want to look at Not needed! I have a Table per $USER and folders are only virtuell. So if you have the message in your INBOX and copy it to INBOX.pgsql the the message in the table become two references for the TWO folders. snip Note: Currently I have 7.41 million messages in my table for testing plus the originals on Courier-Imap. Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] PG Email Client
Tom Allison wrote: I would be careful about using dspam. I have documented cases where it lost email and even with posting all the debug and other logs on the mailing lists I've never received any indication that they recognize this as a dspam problem. dspam will lose your mail. This is getting off topic, but I can't just let this hang in the air. I know there have been cases - particularly in old versions ( 1 year old IIRC) where dspam would fail to deliver a message w/o returning an error code. I'm sure there are combinations of MTA - dspam - delivery agent that break. Dspam can be used in so many ways, it's very difficult to cover every possible scenario it can be deployed in. However, anything remotely recent (I still use an old 3.6.6) returns an error code on failure. For the rest, it is up to your MTA to respond to those error codes, crashes of dspam, etc. I use postfix, and it does check things like that. As a matter of fact, I've had trouble in the past where postfix decided dspam wasn't going to finish processing mail and delivered the message regardless (that was when my mail/spam machine was a P233 w/ only 64MB RAM - it simply couldn't cope). I know there are bugs in dspam, but I doubt it's losing mail. That said, anything beyond the scope of its performance on postgres is off topic and should be taken off list. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG Email Client
Alban Hertroys wrote: Jean-Gérard Pailloncy wrote: I do not have test numbers rigth now, because I need first to configure the box for virtual hosting, then dspam. It would be really wonderful if someone managed to combine this with dspams own database. Maybe it'd be possible to pull dspam's algorithms as a lib into the database? I've been wanting an IMAP spam drop-box like forever, this seems like a possibility to achieve this. Combine a spam/not-spam trigger to specific IMAP boxes and make those update dspam's statistics - seems possible :) All I'd need to do to mark something spam is drop the mail in the right box from my client. The quarantine box could work similarly. I would be careful about using dspam. I have documented cases where it lost email and even with posting all the debug and other logs on the mailing lists I've never received any indication that they recognize this as a dspam problem. dspam will lose your mail. I wouldn't spend a second considering trying to merge a spam filtering application into an imap server. It's just plain dumb. You have lose all modularity of code or function and now you exponentially increase the probability that you will have defects and they will be exponentially more difficult to identify and fix. Leave dbmail to do what it does and invent something else to do what other clear functionality you want available. That's the only sane way to do this kind of development. For example -- spam filtering in itself can require a very large box with extensive amounts of resources in terms of memory, cpu, disk IO If you have that combined with a large dbmail installation and a postgres database what you end up with is wanting multiple boxes to disperse the loads around. What you are describing here is the opposite. Make one box do everything in the entire environment of email. There are other things you can do to use imap folders and spam training that would make more sense. The *one* feature I did like about dspam is they quarantine spam and you can sort it out later. That can be done easily enough and won't interfere with dbmail if you don't want it to. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Email Client
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/03/07 07:17, Tom Allison wrote: Alban Hertroys wrote: Jean-Gérard Pailloncy wrote: [snip] The *one* feature I did like about dspam is they quarantine spam and you can sort it out later. That can be done easily enough and won't interfere with dbmail if you don't want it to. This can also be done with server-side filtering. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFxKv3S9HxQb37XmcRAsaaAJ9HvzOKWivAik9VTWxTu/YKf2pwAwCfScHT 15viYSFVVyyTLhy7ukzq0Z0= =6n70 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG Email Client
I would be careful about using dspam. I have documented cases where it lost email and even with posting all the debug and other logs on the mailing lists I've never received any indication that they recognize this as a dspam problem. dspam will lose your mail. *ahem* I have several dspam installations and have never had a problem with it loosing email. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG Email Client
Please keep us posted on your archiveopteryx experience! I just install aox on a soekris ! with openbsd 4.0 with a remote postgresl db and setup sendmail to forward by lmtp. Really nice. No problem to compile (just a postgresql.h in a obsd specific location). The installer (wich cretae the database) was not working, but I do everything by hand to setup. I do not have test numbers rigth now, because I need first to configure the box for virtual hosting, then dspam. The table structure is really nice. Normalize all the field. Each multipart mime attachements are handle separatly. It is really esay to use rules to map config and data to an other soft. My dream will be to setup a webdav script to access all the attachement in a dynamic FS. Then I mount the webdav to my mac or my pc. In dbmail it was very complicated to get each attachement independently because the message is stored by block of 500Kb and not already parsed by attachement. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Email Client
Jean-Gérard Pailloncy wrote: I do not have test numbers rigth now, because I need first to configure the box for virtual hosting, then dspam. It would be really wonderful if someone managed to combine this with dspams own database. Maybe it'd be possible to pull dspam's algorithms as a lib into the database? I've been wanting an IMAP spam drop-box like forever, this seems like a possibility to achieve this. Combine a spam/not-spam trigger to specific IMAP boxes and make those update dspam's statistics - seems possible :) All I'd need to do to mark something spam is drop the mail in the right box from my client. The quarantine box could work similarly. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG Email Client
Jean-Gérard Pailloncy wrote: I do not have test numbers rigth now, because I need first to configure the box for virtual hosting, then dspam. It would be really wonderful if someone managed to combine this with dspams own database. Maybe it'd be possible to pull dspam's algorithms as a lib into the database? Right now, you can do without dspam lib. Here my planned setup: sendmail --lmtp-- dpsam --lmtp-- aox with: - sendmail + sm-pgsql http://blue-labs.org/software/sm-pgsql/ - dpsam + pg_driver + clamav + daemon - aox (pg db) So all the configuration and data will be in pg. With few rules, everything should rock! For sendmail config, I am testing - local-host-names with only mail.domain.com - accessdb with pg_map and a view to aox user db. I plan to use no virtual domain in local-host-names and RELAY in access_db because I want sendmail to be able to test which email adress is OK at RCPT time. I do no want to spool the file, then send to lmtp send bounce in case of error. I will add a virtualuser/aliases to manage .forward-like with pg. Right now, sendmail+aox works. I am on the road to sendmail+pg+virtual_domain+oax. I've been wanting an IMAP spam drop-box like forever, this seems like a possibility to achieve this. Combine a spam/not-spam trigger to specific IMAP boxes and make those update dspam's statistics - seems possible :) On solution I seen on the web (but do not remember the URL) is to use 3 specials folders in INBOX: quarantine, ham, spam. - HAM go to INBOX - SPAM go to INBOX.qarantine - false-HAM is moved by user to INBOX.spam - false-SPAM is moved by user to INBOX.ham - a cron script retrain dspam with messages in INBOX.ham and then move messages to INBOX - a cron script retrain dspam with messages in INBOX.spam and then move messages to INBOX.quarantine - a cron script delete old messages (14 days) from INBOX.qarantine May be a trigger that call some external program and tell dspam to retrain. I need more time to study that. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG Email Client
dbmail isn't as fast as dovecot for a single user, single event. But not by much. However, it scales *much* better into the 100's and 1000's of actions. $.02 I came within an eyelash of going live with dbmail, and then pulled back. This was a year or so ago, I don't remember exactly. The performance I was getting on very large IMAP folders was just awful. So I turned on PostgreSQL query logging, and looked at the query it was throwing at the database to select an IMAP folder. And then I looked at the schema. I remember being horrified. It was clear that as a pure database application, dbmail was *a mess*. The schema was just not well designed for the queries they were actually making, and not even slightly well indexed. They may have fixed this in the meantime. I did look at the schema for archiveopteryx, and it looked to me to be much better thought out. I got as far as compiling archiveopteryx on my OS of choice (OpenBSD, no flames please) which was not a supported platform. It built, but I didn't go further, so I don't have any results. There's a lot of work in dbmail, and the architecture around the database seems to me to be quality stuff. Now if only they've gotten the database coding up to the same level ... If a database as a message store cannot give good performance, then why do it. Asking for good performance on a folder of 65K messages is not asking for the moon. dbmail broke my heart. This was a while ago, maybe they fixed it, or maybe I had my database tuned wrong, but I'd encourage anybody using a database as a message store for an E-mail system to look over the schema with a fine-tooth comb and evaluate it like you would part of your ERP system. If the schema is not well designed for a typical IMAP query, run the other way. /$.02 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG Email Client
Please keep us posted on your archiveopteryx experience! Thanks and Cheers. They may have fixed this in the meantime. I did look at the schema for archiveopteryx, and it looked to me to be much better thought out. I got as far as compiling archiveopteryx on my OS of choice (OpenBSD, no flames please) which was not a supported platform. It built, but I didn't go further, so I don't have any results. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG Email Client
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/30/07 00:32, Hakan Kocaman wrote: Hi, you can find a nice virtual folder implementation in the Opera-Mailclient M2. Not sure if this also works with IMAP (don't use IMAP yet). Virtual folders are based on regexes over various fields of a mail(Subject,From,to,Body,etc.). Of course this is not db-based, but the feature is neat. And it is client-based, not host-based. You'd have to hack the IMAP server to add virtual folders and double links (so that deleting the base-mail will also delete the link mails). I'm sure it's not trivial... -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFv1G5S9HxQb37XmcRApGNAJ9akiBYJz+3KF18qVuoqLgPFc8rrwCg0lRh g7FQlKpYe1wihYRIxAVdnDs= =slkT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG Email Client
Sim Zacks schrieb: Are there any postgresql based email clients that anyone has experience with? Is there a good technical reason why everyone is not doing this? It would seem to me to be a super CRM/ERP enhancement. I am toying with the idea of fully integrating email into our ERP system so that our email can be indexed in multiple ways and can be viewable as a direct relation with the subsystem or multiple subsystems that it is related to. It would also allow an organizational view of the communication related to the subsystem rather then an individual view that email generally gives. For example, if I want to look at a customer, if corporate email is integrated then I can see the entire history of communication with that customer, including pre-sales, sales, support, shipping, etc irrelevant as to who sent the email. Well client would be the wrong approach imho, but there is for example dbmail which can use postgres as storage for mail and opens a lot of the usecases above. You would continue to use your MUA for reading mail but some of the mails could be handled by your application directly. Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Email Client
DBMail is an interesting concept, but I think the real advantage would be if there were a client that could take advantage of the power of a database backend. For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to multiple folders. Current email clients mimic file cabinets too much in that the system considers an email to be a physical entity that can only be stored in one place. Searching for emails can also be done much faster with a database then with a traditional mail client approach. However, to start out with, using dbmail imap with current email clients might be a great idea. Then the users will not even know that anything has changed, and I can basically build the client that I want directly into my ERP. Sim Well client would be the wrong approach imho, but there is for example dbmail which can use postgres as storage for mail and opens a lot of the usecases above. You would continue to use your MUA for reading mail but some of the mails could be handled by your application directly. Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PG Email Client
Tino Wildenhain wrote: Sim Zacks schrieb: Are there any postgresql based email clients that anyone has experience with? Well client would be the wrong approach imho, but there is for example dbmail which can use postgres as storage for mail and opens a lot of the usecases above. You would continue to use your MUA for reading mail but some of the mails could be handled by your application directly. I'll second the dbmail application. It's POP or IMAP server based. But it does what you are looking for. I used to use dovecot, which is extremely fast. But, if you gave it too much to do it would hang for hours chewing up the hard drive and generating heat. dbmail isn't as fast as dovecot for a single user, single event. But not by much. However, it scales *much* better into the 100's and 1000's of actions. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Email Client
Hello list, Has anyone checked out http://www.archiveopteryx.org/ looks very promising! Cheers, Mike ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG Email Client
Sim Zacks schrieb: DBMail is an interesting concept, but I think the real advantage would be if there were a client that could take advantage of the power of a database backend. For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to multiple folders. Current email clients mimic file cabinets too much in that the system considers an email to be a physical entity that can only be stored in one place. Searching for emails can also be done much faster with a database then with a traditional mail client approach. Err... I do not understand copy an email in 1 folder - I use IMAP which stores all the mail on the server. (That is what dbmail does too) The client or rather MUA queries the server via IMAP so its complete transparent to where it saves the mail - however - improvements to IMAP, like the sieve protocol could be implemented in dbmail to make use of advantage search using the database. Regards Tino ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG Email Client
Might I suggest you consider using dbmail as an IMAP server. This indexing you speak of is exactly what it will do. But the additional advantage with IMAP is that you can have multiple clients attached to the same folders (especially useful if shared) or be able to attach from multiple sources (notebooks, webpages, desktops) and have all the same messages/folders each time. On 1/29/2007, Sim Zacks [EMAIL PROTECTED] wrote: DBMail is an interesting concept, but I think the real advantage would be if there were a client that could take advantage of the power of a database backend. For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to multiple folders. Current email clients mimic file cabinets too much in that the system considers an email to be a physical entity that can only be stored in one place. Searching for emails can also be done much faster with a database then with a traditional mail client approach. However, to start out with, using dbmail imap with current email clients might be a great idea. Then the users will not even know that anything has changed, and I can basically build the client that I want directly into my ERP. Sim Well client would be the wrong approach imho, but there is for example dbmail which can use postgres as storage for mail and opens a lot of the usecases above. You would continue to use your MUA for reading mail but some of the mails could be handled by your application directly. Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly QIDX:afa47a3e4ac627067728eb058e4b191f ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG Email Client
Sim Zacks [EMAIL PROTECTED] wrote: DBMail is an interesting concept, but I think the real advantage would be if there were a client that could take advantage of the power of a database backend. For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to multiple folders. Current email clients mimic file cabinets too much in that the system considers an email to be a physical entity that can only be stored in one place. Searching for emails can also be done much faster with a database then with a traditional mail client approach. I believe that's the case with at least some of these systems; a mail folder can be a query so that you could readily have various folders that hit the same messages. However, to start out with, using dbmail imap with current email clients might be a great idea. Then the users will not even know that anything has changed, and I can basically build the client that I want directly into my ERP. That's certainly a starting point... -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/ Q. What's the difference between Batman and Bill Gates? A. When Batman fought the Penguin, he won. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Email Client
Err... I do not understand copy an email in 1 folder - I use IMAP which stores all the mail on the server. (That is what dbmail does too) In the traditional imap server the mail is stored in folders on the server that are accessed by the client. Therefore you are limited to one indexed location per email. Lets say you want to look at your email by customer. You would want to have a folder per customer so you can review your correspondence with each one individually. If you want to look at correspondence per product. You would need one folder for each product so you can review the correspondence for each product individually. However, if you want to have the ability to look at your email both per customer and per product, then with the traditional imap system, you would need to copy the email so you had two physical copies of the email. Now if you wanted to make notes on the email, it would only be tied to one of them, etc.. I haven't checked whether dbmail can make virtual folders yet based on queries, but the normal clients certainly don't support setting up indexes on the emails so that they could be viewed in both folders. A client that took advantage of a database backend would be one in which the user could a) create virtual folders that are actually queries b) choose indexes or put in keywords so that whether you clicked on the Customer A folder or the Product C folder you would see the exact same e-mail. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG Email Client
Hi, you can find a nice virtual folder implementation in the Opera-Mailclient M2. Not sure if this also works with IMAP (don't use IMAP yet). Virtual folders are based on regexes over various fields of a mail(Subject,From,to,Body,etc.). Of course this is not db-based, but the feature is neat. Just my 0,02€ Hakan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sim Zacks Sent: Tuesday, January 30, 2007 6:57 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] PG Email Client Err... I do not understand copy an email in 1 folder - I use IMAP which stores all the mail on the server. (That is what dbmail does too) In the traditional imap server the mail is stored in folders on the server that are accessed by the client. Therefore you are limited to one indexed location per email. Lets say you want to look at your email by customer. You would want to have a folder per customer so you can review your correspondence with each one individually. If you want to look at correspondence per product. You would need one folder for each product so you can review the correspondence for each product individually. However, if you want to have the ability to look at your email both per customer and per product, then with the traditional imap system, you would need to copy the email so you had two physical copies of the email. Now if you wanted to make notes on the email, it would only be tied to one of them, etc.. I haven't checked whether dbmail can make virtual folders yet based on queries, but the normal clients certainly don't support setting up indexes on the emails so that they could be viewed in both folders. A client that took advantage of a database backend would be one in which the user could a) create virtual folders that are actually queries b) choose indexes or put in keywords so that whether you clicked on the Customer A folder or the Product C folder you would see the exact same e-mail. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
