On 02/18/2014 10:29 PM, David Mehler wrote:
Can someone check me on this design so that if I delete a virtual user out
of the virtual_users table the coresponding row in the quota table is
also eliminated?
That's a purely MySQL question.
Try something like
CREATE TABLE quota (
username varchar(100) not null,
bytes bigint not null default 262144,
messages integer not null default 0,
primary key (username)
CONSTRAINT `username_virtual_users` FOREIGN KEY (`username`)
REFERENCES `virtual_users` (`email`) ON DELETE CASCADE
);
(Check that, don't just copy and paste..)
My second question has to do with global and per user quotas. If I
have something like this:
CREATE TABLE quota (
username varchar(100) not null,
bytes bigint not null default 262144,
messages integer not null default 0,
primary key (username)
);
256MB for all users by default, but then if I give a user a 100MB
value in the bytes field of their quota entry would that be a per-user
quota?
dovecot stores the bytes currently used in the bytes column. Do not set
a default value, or just make it zero!!! This table does not hold the
allocated quota size, only the used storage space.
Update your passdb / userdb queries to return a quota size, presumably
from your virtual_users table.
password_query = SELECT password, \
concat('*:storage=', quota_bytes) as userdb_quota_rule, \
FROM ........
user_query = SELECT concat('*:storage=', quota_bytes) as quota_rule, \
FROM ....
Personally I prefer to store the value in megabytes, and use
concat('*:storage=', quota_mb, 'M') as quota_rule
