How to set up a simple list with SQL support - ezmlm-idx FAQ

5.4 How to set up a simple list with SQL support

To use SQL database support, you have to compile the programs with SQL support. Currently, only MySQL support is available. See INSTALL in the package on how to do this.

The programs with SQL support will work exactly like the normal programs for standard lists. However, if the file sql exists in the basedir, it turns on the SQL mode and it is expected to contain SQL server connect info in the format

     host:port:user:password:database:table

Here, ‘host’ is the SQL database server host, ‘port’ can be left blank to use the default port, ‘user’ and ‘password’ are connection credentials for a user you need to define and grant access to the database. ‘Table’ is the name of the address table (‘list’ in the examples above and ‘list_digest’ for the corresponding digest list). For list clusters, ‘:sublist’ is suffixed to this info and it is the name/address of the sublist.

For each address database, you also need to create the address table as well as the ‘*_slog’ subscription log table. In addition, you should create a ‘*_cookie’ and ‘*_mlog’ table for message logging. This is all it takes to start using an SQL database.

5.4.1 Helper programs for SQL-enabled lists

Two programs are supplied in the distribution to make it easier to create the database user and tables. Also, ezmlm-make(1) has support for setting up SQL-enabled lists.

Creating the tables

ezmlm-mktab(1) will create the necessary tables:

          % ezmlm-mktab -d table

Pipe this into the SQL client with the appropriate administrator credentials needed to create tables (see the MySQL documentation).

For most lists, the only addresses that are stored in the SQL database are the subscribers of list and digest, and the ‘allow’ aliases. It is NOT normally advisable to store moderator addresses there, since they are needed only at the main list and secrecy is more important. ‘Deny’ addresses are few and again only needed at the main list. ‘Allow’ are put in the SQL database when using the default ezmlmrc file only to make all relevant addresses manipulatable via the SQL server. The other tables are created, in case they are wanted (the cost for having them as empty table is zero). The basedir/sql file is the decision point. If it exists, an SQL table is used; if not a local ezmlm db is used.

Creating a user entry

Create a user that has full access to the database from the list host. How to do this depends on the RDBMS.

Creating the list

ezmlm-make(1) supports SQL-enabled lists with the -6 switch:

          % ezmlm-make other_switches -6 'host:port:user:pw:db:table' \
               dir dot local host

This will create an SQL-enabled list that uses the SQL server for the main list subscribers, digest list subscribers (if configured) and ‘allow’ poster alias addresses (if configured).