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 = sql:pgsql:host=localhost dbname=mails user=mailuser password=foo mail_plugins = mail_sql
I've been waiting till 1.0 came out to ask for new features like SQL. I don't think that SQL is a waste of time at all. In fact I believe that SQL is the future of email storage. SQL has a lot of advantages that will be tapped once people start using it. Yes - it is probably slower than mbox or maildir but you can make up for that with faster computers. SQL offers.
Replication - the ability to have instant redundancy on multiple servers.
The ability to undeliver messages - if the indexes are configured right, if you get a spam attack from an IP and after the attack you realize it was spam you can run a quick query and delete delivered email.
Statistical advantages - admins can do email queries across all domains and all usersand all folders to gather useful information. An example might be to generate white list information that can be used for trusted email sources to bypass spam filtering and reduce false positives.
Search Speed - should be a lot faster to search messages than maildir.
And - there's probably a lot of things that will be realized once it's implemented because the indexing ability opens new posibilities.
So - I definitely am someone who supports this idea. It will also make Dovecot stand out as a superior IMAP server.
Go for it!