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
}
