Q: How can I use LISTSERV with my existing database?

Answer by Liam Kelly
Senior Consulting Analyst, L-Soft

If you're installing LISTSERV in an environment that already has an available database server, you may want to use that DBMS for LISTSERV list subscriptions. In this tech tip, we'll discuss two different ways of connecting LISTSERV to an existing DBMS. Each method has different advantages and limitations, which will be described below.

Method One: Dynamic Query Lists (DQL)

Starting with LISTSERV 16.0, LISTSERV can use Dynamic Query Lists (DQL) to fetch recipients from a DBMS or LDAP data source. DQL is useful when the list membership is managed outside of LISTSERV. For example, we might want to mail to employees stored in an LDAP directory, association members stored in a membership database, or university students registered in a course registration database. DQL allows LISTSERV to query those data sources on a read-only basis. Subscribers don't opt in or out of DQL-based lists through LISTSERV, nor are they managed through the usual LISTSERV subscription management tools. Instead, the LISTSERV site administrator defines an SQL query or LDAP filter as a LISTSERV sub-list, and LISTSERV runs that query every time the list distributes mail. Through DQL, list membership is kept up-to-date with the source data in real time, with no need to synchronize the email list with the data source.

DBMS-based Dynamic Query Lists are detailed in Section 8 of the LISTSERV Advanced Topics Manual. In general, they require that you define one or more DBMS servers in your LISTSERV site configuration, and then define one or more SQL queries to query the DBMS. Individual lists then use the queries by way of the Sub-Lists list configuration keyword. A list may have any number of Sub-Lists, which means that a single list may run multiple queries to fetch its list membership. LISTSERV will automatically de-duplicate the query results, just as it does for other sub-lists.

Method Two: DBMS Lists

The second method of interfacing LISTSERV with a DBMS is through DBMS-based LISTSERV lists. DBMS lists are useful when you need LISTSERV to manage the list membership through the usual LISTSERV subscription management features – opt-in, opt-out, bulk subscriber uploads, etc. – but you also want to be able to access that list membership through a DBMS interface. Unlike DBMS-based Dynamic Queries, the SQL queries for DBMS lists are not user-defined. This means that LISTSERV requires a certain database table format: one row per unique recipient email address, with (at minimum) columns for email address, name, and a subscription options (LISTNAME) column for each LISTSERV list. (Optional, but strongly recommended, is a UEMAIL column containing an upper-case version of the subscriber email address to serve as a unique key.) See the example below:

+------------------+-------------+----------------------------+--------+------------------+
| email            | fullname    | list1                      | list2  | uemail           |
+------------------+-------------+----------------------------+--------+------------------+
| user@EXAMPLE.ORG | sample user | 4;1024;0;0;729927;16777215 | <null> | USER@EXAMPLE.ORG |
+------------------+-------------+----------------------------+--------+------------------+

In the DBMS list model, LISTSERV runs an SQL query against the DBMS every time the list distributes mail, and looks for a particular subscription option string in the LISTNAME column. That option string is updated in the DBMS when a subscriber updates her subscription information, and is set to null when the subscriber signs off (or is deleted from) the list.

For DBMS lists, this method may create a need for data synchronization if other applications also need to access or update the subscriber records. For example, we might have a need to add a new employee to an EMPLOYEES list. We have probably created a new record for the employee elsewhere in the DBMS; to add her to the EMPLOYEES list, we also need to create a row for her in the LISTSERV database table and populate the EMPLOYEES subscription option column for that row. Or the synchronization may need to go the other way: if LISTSERV automatically deletes a subscriber because of bounced email, we might want that update to be reflected elsewhere in the DBMS. Much of this synchronization can be handled through database triggers, but the specifics are highly case-dependent.

DBMS lists are detailed in Section 1 of the LISTSERV Advanced Topics Manual. In general, they require that you define one or more DBMS servers in your LISTSERV site configuration, and then configure individual lists to use those DBMS connections by way of the DBMS list header keyword. While multiple LISTSERV lists may share the same DBMS table, each list will query one and only one table.

Both Methods Compared

Whether DQL or DBMS-based lists are appropriate depends on your particular needs.

Dynamic Query Lists should be used:

• When you don't want LISTSERV to maintain the subscriber list
• When you have an existing data source already populated with subscriber data
• When you want to supply your own custom SQL statement to select the subscribers from the DBMS
• When you want to query multiple data sources for the same list

Dynamic Query Lists are limited as follows:

• Because the access is read-only, LISTSERV cannot add subscribers to a list
• Members retrieved by DQL cannot unsubscribe through LISTSERV
• LISTSERV cannot automatically delete invalid (bouncing) addresses

DBMS Lists should be used:

• When you don't have an existing subscriber list, but want to build that list through LISTSERV
• When you want to use the standard LISTSERV subscription management features
• When you want LISTSERV to automatically delete invalid addresses

DBMS Lists are limited as follows:

• User-defined SQL statements cannot be used
• Table format is determined by LISTSERV

For more information about both DBMS and DQL based LISTSERV lists, see the LISTSERV Advanced Topics Manual.

Subscribe to LISTSERV at Work.