GNU Mailutils Manual (split by node):   Section:   Chapter:FastBack: Programs   Up: Mailbox Quotas   FastForward: Libraries   Contents: Table of ContentsIndex: Function Index

3.12.4.2 Keeping Quotas in SQL Database

User quotas can be kept in an SQL table as well. Currently (as of mailutils version 3.14) 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}'

To define this query use the sql-query statement:

quota {
  sql-query "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. Mda always uses the first tuple from the set returned by mailbox quota query. So, you can 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 quota for the user, which will be used by mda. 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 {
  sql-query "SELECT quota "
            "FROM mailbox_quota "
            "WHERE user_name IN ('${user}','00DEFAULT') "
            "ORDER BY user_name DESC";
}            

GNU Mailutils Manual (split by node):   Section:   Chapter:FastBack: Programs   Up: Mailbox Quotas   FastForward: Libraries   Contents: Table of ContentsIndex: Function Index