dovecot - Jun 2011 - 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 at
spam.dom');
INSERT INTO quota (bytes,username) VALUES ('112497181','foo at
spam.dom');
SELECT * FROM quota;
foo at 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 at
spam.dom');
INSERT INTO quota (messages,username) VALUES ('1743','foo at
spam.dom');
SELECT * FROM quota;
foo at spam.dom|0|1743

This can be easily bypassed if INSERT will be:
INSERT INTO  quota (bytes,messages,username) VALUES
('112497180','1743','foo at 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 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..