[Dovecot] A sqlite-based quota dict

[Axel Luttgens]

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
	}