[Dovecot] sqlite quota dict

[Nikita Koshikov]

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.