3.11.4.2 Keeping Quotas in SQL Database

Configuration statement quota-query allows to specify a special query to retrieve the quota from the database. Currently (as of mailutils version 2.99.97) it is assumed that this table can be accessed using the credentials set in ‘sql’ configuration statement (see SQL Statement).

For example, suppose you have the following quota table:

     create table mailbox_quota (
       user_name varchar(32) binary not null,
       quota int,
       unique (user_name)
     );

To retrieve user quota the following query can be used:

     SELECT quota FROM mailbox_quota WHERE user_name='${user}'

There are no special provisions for specifying group quotas, similar to ‘DEFAULT’ in DBM databases. This is because group quotas can easily be implemented using SQL language. Maidag always uses the first tuple from the set returned by mailbox quota query. So, you may add a special entry to the mailbox_quota table that would keep the group quota. In the discussion below we assume that the user_name column for this entry is lexicographically less than any other user name in the table. Let's suppose the group quota name is ‘00DEFAULT’. Then the following query:

     SELECT quota
     FROM mailbox_quota
     WHERE user_name IN ('${user}','00DEFAULT')
     ORDER BY user_name DESC

will return two tuples if the user is found in mailbox_quota. Due to ORDER statement, the first tuple will contain the quota for the user, which will be used by maidag. On the other hand, if the requested user name is not present in the table, the above query will return a single tuple containing the group quota.

The following configuration statement instructs maidag to use this query for retrieving the user quota:

     quota-query "SELECT quota "
                 "FROM mailbox_quota "
                 "WHERE user_name IN ('${user}','00DEFAULT') "
                 "ORDER BY user_name DESC";