Stephen Warren <[EMAIL PROTECTED]> writes: > 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.
Ugh. Pity they had to do that, thus making everyone else do something similar. I suppose we could add something like uniqid() to Util.py... > I'm thinking the simplest solution to this is to change the SQL schema to > use an auto-increment column, then update the Turba code to use this, > rather than trying to work out how TMDA might generate some form of unique > data for this column... If you can do this with a minimum of hassle, it might be easiest, given the current DB implementation in TMDA. > * TMDA simply performs INSERTs (well, at least, simply executes one single > SQL statement - can it be a non-INSERT?). Yeah, but the "only one" restriction will still be true. We use the cursor.execute() method (from the Python DB API 2.0). > 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). Hmm. I'm really not sure what to suggest here. You probably don't need the IGNORE, although it doesn't hurt and might be a hair more efficient. TMDA catches any DatabaseError exceptions thrown by the particular DB API and ignores them. Different implementations throw different exceptions (!), so I had to catch them at the DatabaseError level. <frown> If you want to see just how gross catching entirely different exceptions with the same name (from different modules) can be, look at the first two lines of Util.db_insert(). On the other hand, it's a testament to Python's versatility and power that it was possible at all. > I'm really not sure how to solve this. Can TMDA be modified to do a > SELECT, then INSERT only if there were no existing matches? MySQL doesn't > seem to support stored procedures (at least not in 4.0.x) so I can't just > call one. Yeah, MySQL is the exception in several cases that made building this support in rather difficult. There is a better way to do it than what I have now, but it would take some significant design and implementation effort and I didn't have time before the version 1.0 release. I hope to address these issues better in the future. > Otherwise, perhaps we could have an EXEC_BARE_APPEND and > EXEC_CONFIRM_APPEND to call a custom script "exec $script $recipient > $sender" to do whatever updates are required... This isn't a bad idea as a workaround, although it's a little sad that we would have to run a separate program for the insert when we've got an open database connection presumably being used in the filter already. Setting up the connection, not making the query or executing insert/update, is the time-consuming part, which is why database pooling on web servers works so well. > Any thoughts. I've trawled google for ideas for hours but didn't really > come up with anything. If you were looking for anything TMDA-related, you probably wouldn't have found it. The general SQL support is relatively new (end of June, early July, I think...). Feel free to discuss this more on -workers. I'm the implementor and I'm regularly there and would appreciate any and all ideas. Tim _____________________________________________ tmda-users mailing list ([EMAIL PROTECTED]) http://tmda.net/lists/listinfo/tmda-users
