[turba] MySQL storage and auto_increment column.

Stephen Warren swarren at wwwdotorg.org
Thu Aug 28 11:00:36 PDT 2003


Hi.

I'm using Turba 1.2 with a MySQL (4.0) backend to store the data.

I'm attempting to integrate the MySQL database with another product, which
will both insert new contacts, and query for the existence of contacts.

At present, this product can simply execute a single SQL INSERT statement
(well, actually, any single SQL statement) that can only be parameterized
by the owner's email address (which is what I use for user IDs) and the
contact's email address (to be added). Because of this, the object_id
column in the Turba schema is causing a problem - it can't be generated by
the current system.

What I'd like is to replace this column in the schema with a MySQL
auto-increment field, so I simply insert NULL and it'll automatically
generate a new unique ID. I can easily hack the schema and sql.php driver
to do this.

However, the problem is the makeKey() function in the drivers - at
present, Source.php goes to the driver and calculates the key *then* calls
addObject() to actually create the object. It'd be best with the new
schema to completely remove makeKey() and have it be an internal part of
addObject() in the driver, which then returns the new key.

So, the changes I'm proposing are:

a) Remove makeKey in the drivers, moving the code to addObject

b) Update all driver addObject to return either the new key, or an error -
instead of the current "true" or error return.

c) Update addObject() in Source.php not to call makeKey, and to always
just return the driver's addObject() return value, which will always be
the error or new key.

Do these changes make sense? I can't see any code in the other drivers
that require makeKey to be a separate function. I think it's generally
cleaner for addObject to return the new key, rather than being given it,
and I don't foresee a need to ever derive a new key value without actually
performing addObject() to create it in the store.

Alternatively, if the above doesn't sound good, perhaps I can hack
makeKey() in sql.php to insert an empty dummy row, then return the key of
that row, to be updated instead of inserted in addObject().

I'm not sure if other SQL sources support a similar concept to MySQL's
auto_increment column, so perhaps the sql.php code for all of this would
have to be duplicated into mysql.php then changed? I couldn't find any
generic DB.php support for it, so I guess to retrieve the auto_increment
value, I'm going to have to execute "SELECT LAST_INSERT_ID();" in 
MySQL-specific code.

Does anyone have any comments on the above changes. If they sound
sensible, let me know, and I can start learning PHP to implement them (!)
and provide patches.

Thanks.

-- 
Stephen Warren, Software Engineer, Parama Networks, San Jose, CA
swarren at wwwdotorg.org                  http://www.wwwdotorg.org/


More information about the turba mailing list