05-Feb-03 at 16:01, [EMAIL PROTECTED] ([EMAIL PROTECTED]) 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:[EMAIL PROTECTED]/unsub.txt
46 1 * * * /usr/local/bin/ncftpget -DD \
ftp://user:[EMAIL PROTECTED]/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
<%
'
' 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
%>
#!/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: [EMAIL PROTECTED]" > /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: [EMAIL PROTECTED]" > /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
------------------------------------------------------
Mailman-Users mailing list
[EMAIL PROTECTED]
http://mail.python.org/mailman/listinfo/mailman-users
Mailman FAQ: http://www.python.org/cgi-bin/faqw-mm.py
Searchable Archives: http://www.mail-archive.com/mailman-users%40python.org/
This message was sent to: archive@jab.org
Unsubscribe or change your options at
http://mail.python.org/mailman/options/mailman-users/archive%40jab.org
