[Dovecot] sqlite quota dict
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
Also this can be done during CREATE TABLE syntax, like so:
CREATE TABLE quota ( username text not null, bytes integer not null default 0, messages integer not null default 0, primary key (username) ON CONFLICT REPLACE );
So the examples will work: INSERT INTO quota (bytes,username) VALUES ('112497180','foo@spam.dom'); INSERT INTO quota (bytes,username) VALUES ('112497181','foo@spam.dom'); SELECT * FROM quota; foo@spam.dom|112497181|0
But 1 thing to note - if we updating not all fields and column has NOT NULL DEFAULT value, then DEFAULT value will alter current one:
INSERT INTO quota (bytes,username) VALUES ('112497180','foo@spam.dom'); INSERT INTO quota (messages,username) VALUES ('1743','foo@spam.dom'); SELECT * FROM quota; foo@spam.dom|0|1743
This can be easily bypassed if INSERT will be: INSERT INTO quota (bytes,messages,username) VALUES ('112497180','1743','foo@spam.dom');
So if dovecot will INSERT values to sql dict in this manner, sqlite can be used for storing quota data.
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@spam.dom');
Instead it does:
UPDATE quota SET bytes=bytes + 12345 WHERE username = 'foo@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..
participants (2)
-
Nikita Koshikov
-
Timo Sirainen