[Dovecot] sqlite quota dict

Timo Sirainen tss at iki.fi
Mon Jun 13 16:55:32 EEST 2011


On Thu, 2011-06-09 at 11:56 +0300, Nikita Koshikov wrote:
> Hello Timo, and list followers,
> 
> Sqlite supports good feature like mysql 'ON DUPLICATE KEY UPDATE' - it calls REPLACE/INSERT OR REPLACE
> 
> This clause can be added to INSERT query, like:
> INSERT OR REPLACE INTO table VALUES()
> REPLACE INTO table VALUES() // alias to previous one 

Quota updates atomically increase or decrease quota. Dovecot doesn't
know the exact quota value. The only way to do that would be to globally
lock the user's all mailboxes while updating quota, which seems pretty
horrible idea to me.

So, Dovecot can't do something like this:

> INSERT INTO quota (bytes,username) VALUES ('112497180','foo at spam.dom');

Instead it does:

UPDATE quota SET bytes=bytes + 12345 WHERE username = 'foo at spam.dom';

Or it would do this, if it knew that the row always exists. But since
there are no such guarantees, it's doing the INSERT .. ON DUPLICATE KEY
UPDATE.

> So if dovecot will INSERT values to sql dict in this manner, sqlite can be used for storing quota data.

I could consider doing the UPDATE way where all users are always
required to exist in the table. That'd require making this behavior
optional in dict-sql backend. Probably a per-map {} block setting.
Still, that's some work and a pretty low priority currently..



More information about the dovecot mailing list