dovecot - Jun 2011 - Custom sql query for keeping quota with dict-sql

Hello!
I'd like to setup quota for for virtualusers, i'm using both maildir++
and mdbox as storage, and i'm using postgresql to keep users.
I'm keeping all users data in one table:
[...]
login text NOT NULL,
domain text,
password text,
quota_mb integer NOT NULL DEFAULT 0
[... other columns ...]

i'd like to keep present quota in such two columns:
quota_dict_bytes integer NOT NULL DEFAULT 0,
quota_dict_messages integer NOT NULL DEFAULT 0

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? Or maybe there is diffrent way
to track quota for diffrent storages?

Regards!
Marcin

Thursday, June 2, 2011, 9:48:11 AM, Marcin wrote:
> Hello!
> I'd like to setup quota for for virtualusers, i'm using both
maildir++
> and mdbox as storage, and i'm using postgresql to keep users.
> I'm keeping all users data in one table:
> [...]
> login text NOT NULL,
> domain text,
> password text,
> quota_mb integer NOT NULL DEFAULT 0
> [... other columns ...]
> i'd like to keep present quota in such two columns:
> quota_dict_bytes integer NOT NULL DEFAULT 0,
> quota_dict_messages integer NOT NULL DEFAULT 0
> 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? Or maybe there is diffrent way
> to track quota for diffrent storages?
> Regards!
> Marcin
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.

Here's my password_query and user_query:

password_query = \
  SELECT username AS user, password, \
         '/var/mail/mailhost/%d/%n' AS userdb_home, \
         'maildir:/var/mail/mailhost/%d/%n' AS userdb_mail, \
         1002 AS userdb_uid, 6 AS userdb_gid, \
         'dict:user::proxy::quota' AS userdb_quota, \
         CONCAT('*:bytes=',max_bytes,':messages=',max_msgs) AS
userdb_quota_rule, \
         'Trash:bytes=+32M' AS userdb_quota_rule2, \
         'Spam:bytes=+32M' AS userdb_quota_rule3 \
  FROM users \
  WHERE username = '%u' AND active = 1

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 = 1

-- 
Best regards,
 Duane                            mailto:duane at duanemail.org

On Thu, 2011-06-02 at 16:48 +0200, Marcin Miros?aw wrote:
> i'd like to keep present quota in such two columns:
> quota_dict_bytes integer NOT NULL DEFAULT 0,
> quota_dict_messages integer NOT NULL DEFAULT 0
> 
> 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? Or maybe there is diffrent way
> to track quota for diffrent storages?
You can't define your own SQL queries for dict quota. Besides being able
to do such query, there's the problem that when recalculating quota it's
done by deleting a row and inserting it back. You wouldn't want to do
that for your users table. Making this work some other way could be
tricky with PostgreSQL. It's quite an ugly trigger already, because
there's no easy "ON DUPLICATE KEY UPDATE .." feature in PostgreSQL
as
there is for MySQL.