Has anybody attempted to integrated TMDA's DB_BARE_APPEND/DB_CONFIRM_APPEND with the schema for Horde/Turba's address book in MySQL?
A couple of problems I'm not quite sure how to solve yet:
* Each row in the Turba table has a unique "object id" field that's generated via the PHP code md5(uniqid(rand())) [or similar], rather than an auto-incrementing column in MySQL.
* TMDA simply performs INSERTs (well, at least, simply executes one single SQL statement - can it be a non-INSERT?).
So, if the user already has an entry in the address book, but TMDA receives a valid confirmation response, it will attempt to add the entry into the address book, which will actually succeed, since the combination of (TMDA_recipient, email_sender) isn't a unique key in the schema - I though about making it one and adding the IGNORE option to the SQL so that duplicate inserts were no-op'd, but the fields are too large for MySQL to allow me to create a key (max 500 chars allowed, each field is 255 chars).
I'm sending this to -users so people searching the archives are more likely to find it in case it's useful to them, plus I've done it all without making any TMDA changes...
So, I made the following changes:
a)
Changed the type of turba_objects.object_id to "INT AUTO_INCREMENT" (using phpMyAdmin - my SQL isn't that good any more:-).
This is so any SQL code executed by TMDA can have MySQL automatically generate the unique ID field without jumping through hoops.
b)
Changed the type of turba_objects.owner_id to a shorter VARCHAR type, so that it and object_email were short enough together to define a UNIQUE index on the pair of them.
This was so that when TMDA executes the INSERT SQL to add entries into the whitelist, duplicate entries won't be created.
c)
Updated Turba's lib/Source.php::addObject() to not call the driver's makeKey() function, not add the __key field into the attributes array/hash, and to always return whatever the driver returns.
This is so the driver can generate the key inside addObject, and hence the MySQL driver will pull it back from MySQL's LAST_INSERT_ID() function.
d) Updated Turba's lib/Driver/sql.php to execute 'SELECT LAST_INSERT_ID()' after the INSERT and return this instead of true on success, so that Source.php::addObject() returns the key from MySQL.
e) Setup the appropriate DB_CONNECTION and INSERT/SELECT statements in a test account's ~/.tmda/config and filter/incoming.
This seems to work great! I sent an email to the test account, confirmed it and saw the row get added into MySQL. Further emails pass through without requiring confirmation.
Two issues, though:
1)
The DB_CONFIRM_APPEND (or DB_BARE_APPEND) INSERT statement don't have the sender's/recipient's name (parsed out of the to/from header) available as a variable, hence the newly inserted address book contacts have nothing in the name field - just an email address. It'd be nice if we could try and pull this out into the DB too.
2)
I tried to put the whitelist SELECT SQL statement into ~/.tmda/config as:
=====
SQL_WHITELIST = """
SELECT object_email
FROM turba_objects
WHERE owner_id = %(recipient)s
AND %(criteria)s
LIMIT 1"""
=====and use this in ~/.tmda/filters/incoming as:
===== from-sql -addr_column=object_email "$(SQL_WHITELIST)" accept =====
but it didn't appear to expand $(SQL_WHITELIST) at all - it just attempted to execute that literal text as SQL. Putting the whole SQL into the filter works fine, but suffers from being a long nasty looking line! The at http://www.tmda.net/filter-sources.html have an example setup like this...
Anyone who wants diffs against Turba and/or full details of the SQL can email me. Perhaps one day I'll put up a howto on my website to show how I've configured everything, since it came from a whole bunch of different sources on the 'net...
==== config ==== import MySQLdb
FULLNAME = "TMDA test account"
ADDED_HEADERS_CLIENT = {"X-Primary-Address" : "[EMAIL PROTECTED]"}
USERNAME = "tmdatest"
HOSTNAME = "wwwdotorg.org"DB_CONNECTION = MySQLdb.connect(
host='localhost',
user='xxx',
passwd='yyy',
db='horde')DB_BARE_APPEND = """
INSERT INTO turba_objects (owner_id, object_type, object_email)
VALUES (%(recipient)s, 'Object', %(sender)s)"""DB_CONFIRM_APPEND = """
INSERT INTO turba_objects (owner_id, object_type, object_email)
VALUES (%(recipient)s, 'Object', %(sender)s)"""SQL_WHITELIST = """
SELECT object_email
FROM turba_objects
WHERE owner_id = %(recipient)s
AND %(criteria)s
LIMIT 1"""# Over-ride text files setup by /etc/tmdarc CONFIRM_APPEND = "" BARE_APPEND = "" ================
==== filters/incoming ==== # Next is all one line... from-sql -addr_column=object_email "SELECT object_email FROM turba_objects WHERE owner_id = %(recipient)s AND object_type = 'Object' AND %(criteria)s LIMIT 1" accept
#from-file ~/.tmda/lists/confirmed accept ==========================
-- Stephen Warren, Software Engineer, Parama Networks, San Jose, CA [EMAIL PROTECTED] http://www.wwwdotorg.org/ _____________________________________________ tmda-users mailing list ([EMAIL PROTECTED]) http://tmda.net/lists/listinfo/tmda-users
