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.