SQL to Mailman

I also maintain various web sites which use MySQL to maintain information about users. I often need these user to be subscribed to mailing lists based on groups or other attributes stored in the database. The following script, called regurlarly with a few appropriate parameters will do just that :

#! /bin/sh
# mmsql: An sql extractor for mailman
#
# Usage: mmsql      []
#   is the name of and existing mailman list
#   is the name of an existing mysql db
#   is the username to login to mysql
#   is the password to login to mysql
#   is the select clause to run
#   are the parameters passed to mailman's sync_members command
#     defaults are : -a -w=no -g=no
#
# Example :
#   mmsql MyList MyDB user password "SELECT email FROM users"
#
# Author : nicolas@barcet.com


case "$1" in
    -h)
        cat $0.help
        ;;

    --help)
        more $0.readme
        ;;

    *)
        if (test -n "$3") then
                LISTNAME=$1
                BASE=$2
                USER=$3
                PWD=$4
                SELECT=$5

                echo "$0 invoked at `date` for $LISTNAME"

                if (test -n "$6") then
                        OPTIONS=$6
                else
                        OPTIONS="-a -w=no -g=no"
                fi

                mysql $BASE -B -u $USER -p$PWD -e"$SELECT" | awk "/@/ { print }" >/tmp/maillist
                /usr/lib/mailman/bin/sync_members $OPTIONS -f /tmp/maillist $LISTNAME
                rm /tmp/maillist

        else
                echo " Usage: mmsql      []
   is the name of and existing mailman list
   is the name of an existing mysql db
   is the username to login to mysql
   is the password to login to mysql
   is the select clause to run
   are the parameters passed to mailman's sync_members command
     defaults are : -a -w=no -g=no"
        fi
        ;;
esac