[Dovecot] Dovecot, mysql, and quota

[David Mehler]

Hello,

I'm running dovecot 2.2 and wanting to get quotas going. I've got the
plugins loaded appropriately and am wanting to configure a dictionary
backend to talk to my mysql database.

I've got a single database called mail which has all the mail-related
items in it. One table is for virtual users which postfix uses. It
looks like this:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(128) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and a user looks like:
INSERT INTO `mail`.`virtual_users` (
  `id` ,
  `domain_id` ,
  `password` ,
  `email`
)
VALUES (
  '1', '1', SHA2( 'PasswordGoesHere', 512) , 'username at domain.com'
);

plugin {
  # SQL backend:
  quota = dict:User quota::proxy::sqlquota
}
dict {
  sqlquota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}

Now when creating the quota table i'd like to have the username field
linked to my virtual_users table username field, so I don't have to
set up two tables both with the same username information in it. 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?

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?

Thanks.
Dave.