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','[email protected]');
INSERT INTO quota (bytes,username) VALUES ('112497181','[email protected]');
SELECT * FROM quota;
[email protected]|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','[email protected]');
INSERT INTO quota (messages,username) VALUES ('1743','[email protected]');
SELECT * FROM quota;
[email protected]|0|1743
This can be easily bypassed if INSERT will be:
INSERT INTO quota (bytes,messages,username) VALUES
('112497180','1743','[email protected]');
So if dovecot will INSERT values to sql dict in this manner, sqlite can be used
for storing quota data.
