Hello Marcus,
Thursday, June 2, 2011, 1:56:58 PM, you wrote:
> Duane Hill wrote:
> Am 02.06.2011 um 18:40 schrieb Duane Hill:
>>> I read wiki a little and it looks i can't define own sql queries, they
>>> appear to be hardcoded. Am i wrong? Is it possible to write own queries
>>> to get/set quota using such table schema?
> No problem.
>> I'm using mysql here with everything being served out of a single
>> users table. I would think you should be able to do that from a pgsql
>> table as well.
> [...]
>> user_query = \
>> SELECT '/var/mail/mailhost/%d/%n' AS home, \
>> 'maildir:/var/mail/mailhost/%d/%n' AS mail, \
>> 1002 AS uid, 6 AS gid, \
>> 'dict:user::proxy::quota' AS quota, \
>> CONCAT('*:bytes=',max_bytes,':messages=',max_msgs) AS quota_rule, \
>> 'Trash:bytes=+32M' AS quota_rule2, \
>> 'Spam:bytes=+32M' AS quota_rule3 \
>> FROM users \
>> WHERE username = '%u' AND active =
> As an example I do it with postgres like this:
> I create a view with suitable data from my different tables in postgres:
> -----------------------
> CREATE VIEW dovecot_user AS
> SELECT users.userid, users.domain, users.uid, users.gid,
> '*:storage='||ROUND(users.quota/1024) AS quota_rule,
> '/home/vhosts/'||users.domain||'/'||users.userid AS home,
> domains.mailhost, users.active FROM users, domains
> WHERE users.domain = domains.domain;
> grant select on dovecot_user to imapd;
> -----------------------
> And then I just tell dovecot to use it accordingly:
> password_query= SELECT password FROM users WHERE userid = '%n' AND domain =
> '%d'
> user_query = SELECT home, uid, gid, quota_rule FROM dovecot_user
> WHERE userid = '%n' AND domain = '%d' AND mailhost='blah'
> How your table looks in detail is not really important for setting up quotas.
> Basically all you need to do is to generate quota_rule fields in
> your query and see to it that it delivers suitable data for the user_query.
> Duane did this in his example above by using a little more lengthy
> queries (BTW: Is there a reason for the redundancy in the
> password_query?) and I do this by using a database view and keeping
> the dovecot user_query itself a little simpler.
Isn't the redundancy suppose to be if you are using prefetch? If not,
correct me.
> Hope this helps.
> Marcus
--
Best regards,
Duane mailto:du...@duanemail.org
