Hello,

While experimenting with the sqlite backend, I noticed a big vacuum wrt its use for a quota dict.

This is thus an opportunity to share a solution I managed to devise after some trials and errors and a (very quick and unscientific) look at the code.

And also the opportunity to ask the community to check for possible flaws. Yes, nothing comes for free... ;-)

TIA, Axel

This is the SQL for creating the database or for adding needed table/trigger to an existing database (with many comments and few "active" lines):

CREATE TABLE quota (

	-- We will assume that, in the case of a sqlite DB (and probably a pgsql DB
	-- as well), Dovecot will *always* act along these lines:
	--		In case of a "row creation":
	--			INSERT	INTO quota (subject, byte_count)
	--			VALUES	(<some id>, <byte count>);
	--			INSERT	INTO quota (subject, message_count)
	--			VALUES	(<some id>, <msg count>);
	--		In case of a recalculation:
	--			UPDATE	quota
	--			SET		byte_count = byte_count + <byte count diff>,
	--					message_count = message_count + <msg count diff>
	--			WHERE	subject = <some id>;
	--		In case of a row deletion:
	--			DELETE	FROM quota
	--			WHERE	subject = <some id>;
	-- For example, a "doveadm quota recalc" may first delete the relevant row
	-- if it exists, then (re-)create it.
	-- Clearly, the "row creation" case is a bit problematic, hence the need for
	-- a trigger.

	-- An id for the entity subjected to a quota (a user/mailbox, a domain...).
	subject			TEXT PRIMARY KEY NOT NULL,

	-- The entity's size, expressed as a storage space use and as a message
	-- count.
	-- Note the NULL defaults: they allow to simplify the trigger's logics.
	byte_count		INTEGER DEFAULT NULL,
	message_count	INTEGER DEFAULT NULL
);


CREATE	TRIGGER quota_insert
BEFORE	INSERT ON quota
FOR		EACH ROW
BEGIN

	-- If there's a row to update, that's because the first INSERT of the "row
	-- creation" has been performed.
	-- (this UPDATE could probably be simplified if the insertion order,
	-- byte_count then message_count, was guaranteed; anyway, since the "row
	-- creation" operation shouldn't be very frequent, we may afford a slight
	-- inefficiency).
	UPDATE	quota
	SET		byte_count =
				CASE
					WHEN NEW.byte_count ISNULL THEN byte_count
					ELSE NEW.byte_count
				END,
			message_count =
				CASE
					WHEN NEW.message_count ISNULL THEN message_count
					ELSE NEW.message_count
				END
	WHERE	subject = NEW.subject;

	-- In which case we may ignore that second (current) INSERT, since the
	-- previously inserted row has just been updated with the value coming
	-- from the current INSERT statement.
	SELECT	raise(IGNORE)
	WHERE	EXISTS (SELECT * FROM quota WHERE subject = NEW.subject);

END;

The corresponding dict configuration being thus supposed to be similar to this one:

connect = /path/to/database

map {
  pattern = priv/quota/storage
  table = quota
  username_field = subject
  value_field = byte_count
}

map {
  pattern = priv/quota/messages
  table = quota
  username_field = subject
  value_field = message_count
}