[Mailman-Users] Per Virtual domain customizations

Simon White simon at mtds.com
Wed Feb 5 16:29:03 CET 2003 

05-Feb-03 at 16:01, df at dune.org (df at dune.org) wrote :
> I've been reading Simon's post regarding the possible mailman users >
> mysql synchronisation.
> 
> In a website we develop (www.urbanshadows.com), we propose to our
> users to subscribe to a mailman mailling list; We also propose the
> users to choose beetween a different list of topics.
> Those users login/passwd/prefered_topics are stored in a mysql database.
> 
> According to one of Simons post i understood their might be a way to synch the users..
> 
> Now my question would be, aside adding/deleting users, could we also
> specify witch topics the users choosed ?  Is there a binary intended
> to modify mailman's user topic settings ?
> Otherwise some file of some sort that could be parsed/edited to modify
> those settings ?
> 
> Final note, i don't know i can ask you this Simon, but do you think
> you can paste us your script to synch mailman > mysql ..?  (would
> understand if you prefer to keep it private).

Hi

Well first of all it works MS-SQL-> Mailman. Not MySQL.

So, it's in ASP (but would be trivial to convert to PHP/MySQL)

Here's the gory dirty hack details:-

- A page is called in ASP which contains POST data comprising an email
  address either for subscription/unsubscription.
- This script updates the DB which itself has a field recevoirLettreInfo
  which is true if subscribed, otherwise false (people may be "members"
  without being subscribers to the list). The DB Primary Key field is also
  required in case of duplicate entries.
- This appends one of two files, either a flat list of new members to
  subscribe or one to unsubscribe
- An FTP access is created for the Linux Server, which will log the user
  directly into the folder where the text files are kept

Then, on the Linux server:-

- A cron job runs every night doing this sort of thing
45 1 * * *      /usr/local/bin/ncftpget -DD \
            ftp://user:pass@server.domain.com/unsub.txt
46 1 * * *      /usr/local/bin/ncftpget -DD \
            ftp://user:pass@server.domain.com/subsc.txt

You need to install ncftpget. The -DD deletes the file if successfully
retrieved.

Then cron runs a script to do updating, looks like this:-

50 1 * * *      /path/to/subunsub

That file is attached, along with the ASP file for what it's worth. If
anyone converts it to PHP (I don't have the time) I'd appreciate
receiving a copy. I have anonymised the scripts a bit, if there's
anything private in there then I'm counting on your discretion, list
readers. The output of the ASP script (to the browser) is in French. If
you think you need a translation, then you probably don't understand the
script well enough...

subunsub is a simple bash script. It sends email to anyone you care to
specify, in order to let them know who was subscribed and who was
unsubscribed.

Problems: 

- this only happens once a day, because it's not important for us to get
  people subscribed straight away. however, you could have cron look at
  the files more often without causing problems.

- if someone subscribes via email (we disallow this because of that) then
  they won't be in the SQL DB. However, it also means they can only use
  the web form to unsubscribe. (oh, and the web form is NOT the Mailman 
  web form, but a simple HTML page with two text boxes: one to subscribe
  with NAME="subsc" and one to unsubscribe with NAME="unsub")

Neither of these files will work out-of-the-box on your systems. You
have been warned.

Hope this helps,

-- 
|-Simon White, Internet Services Manager, Certified Check Point CCSA.
|-MTDS  Internet, Security, Anti-Virus, Linux and Hosting Solutions.
|-MTDS  14, rue du 16 novembre, Agdal, Rabat, Morocco.
|-MTDS  tel +212.3.767.4861 - fax +212.3.767.4863
-------------- next part --------------
<%

'
' By Simon White. Credit where credit's due.
'

' test
Dim unsub, subsc, verif, connString, conn, rs, sSQL, count

whichFN=server.mappath("/mailing/unsub.txt")
whichFN2=server.mappath("/mailing/subsc.txt")
unsub = request.querystring("unsub")
subsc = request.querystring("subsc")
count = 0

if unsub <> "" then count=count+1
if subsc <> "" then count=count+1

if count > 1 then
	response.write "<P>Erreur: deux cases remplies, je ne peux pas d?cider quoi faire..."
	response.end
end if

' DSN-less connection model, YMMV
'
'connString  = "Provider=SqlOLEDB;Network Library=DBMSSOCN;" & _
'		"Data Source=DATABASE_SERVER;" &_
'		"Initial Catalog=dabatbase;" & _
'		"User Id=user;" &_
'		"Password=letmein;"
'

	set conn = Server.CreateObject("ADODB.Connection")
	set rs = Server.CreateObject("ADODB.Recordset")

	'conn.open connString
	conn.open "DSN","user","pass"
	set rs.ActiveConnection = conn

if unsub <> "" then
	sSQL = "SELECT IdentifiantPdc, email FROM coordonnees WHERE email='" & unsub & "';"
	rs.Open sSQL

	if rs.EOF then
		response.write "Adresse email non trouv?e"
		rs.close
		unsub=""
	else
		do while not rs.EOF
		IDtogo = rs.fields("IdentifiantPdc")
		response.write "Nom: " & rs.fields("nom")
		response.write "<BR>Prenom: " & rs.fields("prenom")
		response.write "<BR>Email: " & rs.fields("email")
		sSQL = "UPDATE coordonnees SET recevoirLettreInfo=0 WHERE IdentifiantPdc='" & IDtogo & "';"
		conn.execute sSQL
		response.write "<P>Cette personne est maintenant d?sabonn?e."
		rs.MoveNext
		loop
		rs.close

' Add the email to the list for batching later
		Set fstemp = server.CreateObject("Scripting.FileSystemObject")
		if not fstemp.FileExists(whichFN) then
' create the file
			Set filetemp = fstemp.CreateTextFile(whichFN, false)
			filetemp.WriteLine(unsub)
			filetemp.Close
		else
' open it and add some lines
			forappending = 8
			set filetemp=fstemp.OpentextFile(whichFN, forappending)
			filetemp.writeline(unsub)
			filetemp.close
		end if

		set filetemp=nothing
		set fstemp=nothing

		If err.number=0 then
			response.write "<P>Un fichier de synchronisation a ?t? correctement gen?r?."
		else
			response.write "VBScript Errors Occured!<br>"
			response.write "Error Number=#<b>" & err.number & "</b><br>"
			response.write "Error Desc. =<b>" & err.description & "</b><br>"
			response.write "Help Path =<b>" & err.helppath & "</b><br>"
			response.write "Native Error=<b>" & err.nativeerror & "</b><br>"
			response.write "Error Source =<b>" & err.source & "</b><br>"
			response.write "SQL State=#<b>" & err.sqlstate & "</b><br>"
		end if

	end if
end if

if subsc <> "" then
	sSQL = "SELECT IdentifiantPdc, email FROM coordonnees WHERE email='" & subsc & "';"
	rs.Open sSQL

	if rs.EOF then
		response.write "Adresse email non trouv?e"
		subsc=""
		rs.close
	else
		do while not rs.EOF
		response.write "<BR>Email: " & rs.fields("email")
		sSQL = "UPDATE coordonnees SET recevoirLettreInfo=1 WHERE IdentifiantPdc='" & rs.fields("IdentifiantPdc") & "';"
		conn.execute sSQL
		response.write "<P>Cette personne est maintenant abonn?e ? la liste<BR>"
		rs.MoveNext
		loop
		rs.close

' Add the email to the list for batching later
		Set fstemp = server.CreateObject("Scripting.FileSystemObject")
		if not fstemp.FileExists(whichFN2) then
' create the file
			Set filetemp = fstemp.CreateTextFile(whichFN2, false)
			filetemp.WriteLine(subsc)
			filetemp.Close
		else
' open it and add some lines
			forappending = 8
			set filetemp=fstemp.OpentextFile(whichFN2, forappending)
			filetemp.writeline(subsc)
			filetemp.close
		end if

		set filetemp=nothing
		set fstemp=nothing

		If err.number=0 then
			response.write "<P>Un fichier de synchronisation a ?t? correctement gen?r?."
		else
			response.write "VBScript Errors Occured!<br>"
			response.write "Error Number=#<b>" & err.number & "</b><br>"
			response.write "Error Desc. =<b>" & err.description & "</b><br>"
			response.write "Help Path =<b>" & err.helppath & "</b><br>"
			response.write "Native Error=<b>" & err.nativeerror & "</b><br>"
			response.write "Error Source =<b>" & err.source & "</b><br>"
			response.write "SQL State=#<b>" & err.sqlstate & "</b><br>"
		end if

	end if
end if

	conn.close
	set rs=nothing
	set conn=nothing
%>
-------------- next part --------------
#!/bin/bash
##
## Simple Bash Script by Simon White.
## Credit where credit's due
##

if [ -f /root/unsub.txt ]
then
# file exists, so we can remove_members
#
	/home/mailman/bin/remove_members -f /root/unsub.txt -N List-Name

liste=`cat /root/unsub.txt`
echo "To: Person at tonotify.com" > /root/unsubmessage
echo "Subject: The unsubscribers have been treated" >> /root/unsubmessage
echo "Unsubscribed: " >> /root/unsubmessage
echo $liste >> /root/unsubmessage
echo "." >> /root/unsubmessage

	cat /root/unsubmessage | /usr/sbin/sendmail -F "Mailman Mailing Server" -t	
	sleep 10
	rm -f /root/unsub.txt
fi


if [ -f /root/subsc.txt ]
then
# file exists, so we can subscribe members
#
	/home/mailman/bin/add_members -w n -r /root/subsc.txt List-Name

liste=`cat /root/subsc.txt`
noliste=`cat /root/subsc.txt | wc -l`
echo "To: Person at tonotify.com" > /root/submessage
echo "Subject: The subscribers have been sorted" >> /root/submessage
echo "Subscribed: " >> /root/submessage
echo "New subscribers today: $noliste" >> /root/submessage
echo $liste >> /root/submessage
echo "." >> /root/submessage

	cat /root/submessage | /usr/sbin/sendmail -F "Mailman Mailing Server" -t
	sleep 10
	rm -f /root/subsc.txt
fi

More information about the Mailman-Users mailing list