wiki:ExternalSqlHowto

Project Lancelot External SQL HOWTO

Version 0.1, 2012-07-27 Anselm Lingnau <anselm@…>

Introduction

This document explains how to connect a Project Lancelot list to an external SQL database for the purpose of retrieving subscriber data. This makes Project Lancelot useful if you want to send mail to all your customers without maintaining a separate process to get your customers' names and addresses into a Project Lancelot mailing list database.

Note that, for the time being, the method explained here is strictly read-only as far as the external database is concerned. Among other drawbacks this means that »subscribers« to the list will not be able to subscribe to or unsubscribe from the list by the usual means. See the »Caveats« section below.

The External Database

Project Lancelot can talk to any database server supported by the Perl DBI/DBD infrastructure, using the dbi: database type.

You can configure a Project Lancelot list to retrieve its subscriber data from an external SQL database by setting the list.addressdb parameter to something along the lines of

  list.addressdb = dbi:mysql:database=mydb|myuser|mypassword

Everything from right after dbi: up to the first vertical bar will be used as the Perl DBI connection string (where dbi: will helpfully supply the DBI: at the start). myuser and mypassword stand for an appropriate database account name and password, respectively.

In addition to the DBI connection string and account specifics, you will also need to supply two SQL query strings, one to retrieve a list of subscribers' addresses and one to retrieve subscription options for these addresses.

The query to retrieve subscribers' addresses must be stored in db.dbi.query.getaddresses and might look like

  db.dbi.query.getaddresses = SELECT email FROM customers WHERE opt_in = 1

(Remember you're not supposed to send mail to people who don't want any from you.) You are completely free to design this query however you wish except that it is supposed to return one column per row which must be a valid e-mail address. (You will have only yourself to blame if you get lots of error messages from your MTA because you're attempting to send stuff to addresses that look funny.) Use pl-list to check that what this query returns is sane:

  $ pl-list -l list@example.com
  S----- alfred@example.net
  ... (more subscribers) ...

(Note that without a query for the subscription options, the various options of pl-list are unlikely to do anything useful.) In principle, you could be done here.

IMPORTANT: Be warned that the standard Project Lancelot bulk sending module retrieves only those addresses from the database which are actually known as receiving mail (their subscription status is SUBSCRIBED and their nomail option is not set). The dbi: database connector currently does not enforce this restriction so you will have to put it into the db.dbi.query.getaddresses query by yourself, in a way that is appropriate to your database setup.

If you want Project Lancelot to get at subscribers' names (probably the main reason) or other subscription information (such as digest, no-mail, what have you) you must also supply another SQL query in db.dbi.query.getoptions. This could look like

  db.dbi.query.getoptions = SELECT CONCAT(first_name, ' ', last_name) AS name,
                               0 AS digest FROM customers WHERE email = ?

(line break added for readability). The ? will be replaced by the e-mail address whose subscription options are being looked for.

Note that the query is expected to return any of the named columns name (subscriber name), the options admin, moderator, digest, nomail, and moderated, and status (subscription status). If they do not occur in the actual query, most of these options will default to »false« except status, which will default to SUBSCRIBED, and name, which will default to an empty string. As you can see in the example, it is 100% OK to manufacture whatever results are desired as long as the correct column names are used (possibly by means of SQL AS).

The db.dbi.query.getoptions query will only be used on addresses that are part of the result of a previous db.dbi.query.getaddresses query.

(The query names are intentionally picked such that we will be able to have, say, db.dbi.query.setoptions at some point in the future.)

Caveats

There are some important caveats with the dbi: database connector that you should be aware of:

  • Project Lancelot currently has no way of modifying a subscriber's subscription state, since according to the standard Project Lancelot schema arrangement, the subscription state is part of the data that is stored in the external SQL database. In particular, Project Lancelot cannot set an address to BOUNCING if messages sent to it keep bouncing. (You can still look at the output of the pl-bouncedb command to see which addresses are unresponsive, and do whatever you have to do in your database. cron is your friend.)
  • The usual subscription/unsubscription addresses (list+subscribe@… and friends) will not work, since Project Lancelot can't change the external database. It is best to remove the corresponding mail.workflow.subscribe and mail.workflow.unsubscribe workflows or set them to return an explanatory message using help_message(message=no-subscription) (with an appropriate file no-subscription in a Project Lancelot template directory for the list).
  • You may also wish to set mail.removeheaders to get rid of the default List-Subscribe and List-Unsubscribe headers, possibly replacing them by something more suitable using the mail.addheaders mechanism. (This all takes place in the list_headers module, which you do not want to omit from the submit workflow altogether.)
  • Sending mail to list+config@… to set your own configuration options will also not work. You may wish to deal with the mail.workflow.config workflow in a manner similar to that recommended above for mail.workflow.subscribe.
  • Finally, it would make sense to sanitise the list's help posting to omit all the various features that don't work. You can do this by putting a file called help-message containing an appropriate message into a Project Lancelot template directory. If in doubt, pinch the original text from the Project Lancelot send_help.pm module and edit it to taste.

Last modified 5 years ago Last modified on Jul 27, 2012, 2:48:57 PM