2.10.3.2 Keeping Quotas in SQL Database

Option --quota-query allows to specify a special query to retrieve the quota from the database. Currently (as of mailutils version 1.0) it is assumed that this table can be accessed using the same credentials as sql authentication tables (See daemon, for the detailed discussion of --sql- options).

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 the quota for user %u you may then use the following query:

     SELECT quota
     FROM mailbox_quota
     WHERE user_name='%u'

There is 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. Mail.local 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. For the following discussion, it is important that the user_name column for this entry be 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 ('%u','00DEFAULT')
     ORDER BY user_name DESC

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

To summarize this, here is a working mailutils.rc entry for mail.local:

     :mail.local \
           --sql-db MAILAUTH \
           --sql-host some.host.net \
           --sql-user mail.local \
           --sql-passwd guessme \
           --quota-query "SELECT quota \
                          FROM mailbox_quota \
                          WHERE user_name IN ('%u','00DEFAULT') \
                          ORDER BY user_name DESC"