[Dovecot] quota imap_quota

[Bazy]

Nils Vogels wrote:
> Bazy wrote on 28-8-2007 23:05:
>> plugin {
>>     # 10 MB + 1000 messages quota limit
>>     # quota = maildir:storage=10240:messages=1000
>>
>>     driver = mysql
>>     connect = host=/var/lib/mysql/mysql.sock user=mail_admin
>> password=XXXXXXXX dbname=mail
>>     user_query = SELECT CONCAT(('/home/vmail/'),
>> SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/'),
>> CONCAT('5000'), CONCAT('5000'), CONCAT('maildir:storage=', quota) FROM
>> users AS quota WHERE email = '%u';
>> }
>>
>> The querry output looks like this:
>>
>> +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+
>> | CONCAT(('/home/vmail/'),
>> SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') |
>> CONCAT('5000') | CONCAT('5000') | CONCAT('maildir:storage=', quota) |
>> +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+
>> | /home/vmail/goofy.celuloza.ro/bazy/
>>                        | 5000           | 5000           |
>> maildir:storage=102400000         |
>> +----------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------------+
>>   
> 
> I'm not the expert on the matter, but I think your columns are named
> wrong. Try changing the latter part of the user_query to
> 'CONCAT('maildir:storage=', quote) AS quota FROM users WHERE email = '%u';
> 
> As an example, I've posted my userdb query:
> 
> user_query = SELECT concat('/mail/', maildir) as home,
> concat('maildir:/mail/', maildir) as mail, 108 AS uid, 116 AS gid,
> concat('maildir:storage=', quota) AS quota FROM mailbox WHERE username =
> '%u' AND active = '1'
> 
> 
> Hope this helps!
> 
> Nils.
> 

Thank you Nils,

It's working now. I had some configuration options wrong in my mind, I
didn't understand them...

It looks like this now:

auth default {
    mechanisms = plain login cram-md5 digest-md5

    passdb sql {
        args = /etc/dovecot-sql.conf
    }

#   userdb static {
#       args = uid=5000 gid=5000 home=/home/vmail/%d/%n
#   }

    userdb sql {
        args = /etc/dovecot-userdb-sql.conf
    }

As you can see, i had a userdb static, now I'm querying mysql for it :)
and with your help I fixed my query.

It looks like this now:

SELECT CONCAT(('/home/vmail/'),
SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') as
home, 5000 as uid, 5000 as gid, CONCAT('maildir:storage=',
floor(quota/1024)) as quota FROM users WHERE email = '%u';

Thank you!