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