On Sat, 31 Mar 2007 17:34:23 +0300, Timo Sirainen wrote:
I wasted some time yesterday and today implementing a SQL storage plugin. It seems to be working, but:
- Saving new messages is done in a regular INSERT statement, which is bad. PostgreSQL has at least this COPY TO command which could be used instead.
- It breaks in stress testing
- It's not that well optimized. Especially it could support caching some commonly requested fields (same as dovecot.index.cache file).
- Currently works only with PostgreSQL.
Nothing is committed to CVS yet. I'm not sure if I should even do that. I'm not sure if those lib-sql API changes were that great. Maybe there's a better way..
If you're interested in trying it, you need the latest CVS HEAD sources and these patches:
http://dovecot.org/patches/sql-storage-changes.diff http://dovecot.org/patches/sql-storage-plugin.diff
The SQL schema is in src/plugins/mail-sql/schema.sql file. It creates a "tss" named user by default, so you might want to modify that. :)
Then set:
mail_location =3D sql:pgsql:host=3Dlocalhost dbname=3Dmails user=3Dmailuser password=3Dfoo mail_plugins =3D mail_sql
Hi Timo,
After thoughs about SQL: no time wasted, this is a very interesting project. But instead of storing the mail in the SQL database, which would generate a huuuge database, wouldn't it be more optimised to store only the indexes (and some of the header fields) in SQL and keep the mails on file system? With maybe only the smallest mails in SQL as well... This would sort out a lot of locking problems with the indexes, and give very easy sharing/backup/replication/migration facilities. And it might look a bit like the GMail solution, wich proved to work.
As I am looking for that type of solution, I'll be able to put some time to help on developpement.
Regards,
Thierry