As I mentioned earlier today in A First CPAN Odyssey, I am presently in the throes of polishing up a module that I wish to publish on CPAN. It is a module that provides an object oriented framework for the representation of objects and links stored within a SQL database. While for my own purposes writing solely to the MySQL DBD is satisfactory, I'm striving to make the module as general as possible, a key component of that being DBD agnosticism. I'd like very much for any database that works with DBI to operate smoothly with my module.
To this end, one of my main concerns is dealing with the retrieval of auto-increment values after issuing an insert into a table. I am using surrogate primary keys for all linkages between objects, said key being an auto-increment column in every "object" table, and as such I will probably often want to know the value of this column after doing an insert so I can subsequently populate a "link" table with it.
Obtaining this value with the MySQL DBD is trivially easy. After executing an insert query, the statement handle object contains a field called mysql_insertid which one can grab without any trouble. Since this module is under development in a MySQL environment, I have thus far punted on generality and used the mysql_insertid field as a temporary kludge. Now, however, as I am wrapping things up, I need to take off this bandage and come up with a real solution.
The obvious and simple fix is simply to perform a select right after the insert, calling up the auto-increment value that way. This mostly works, though I am concerned that it is going to result in my code executing twice as many SQL statements, and there is the further danger that the saved row is not unique. On the other hand, I wonder what kind of overhead already exists as the result of the mysql_insertid's availability. Is DBD::mysql already transparently doing something along these lines, or is it somehow optimized?
The best compromise I can muster right now involves making the post-insert querying be "on-demand". Should a Perl object in my framework be saved to the underlying database, it would have a Boolean field called "saved" marked as true. Then when a user invoked the get_pk_value method (the method for retrieving the primary key), it would issue a select statement to ascertain the primary key if it saw that the "saved" flag was true but it did not have the primary key value cached. This still leaves a bitter taste in my mouth, but at least it would avoid the superfluity of executing a second statement that wasn't going to be of any value.
As a hybrid solution, I could make the object storage code semi-aware of the DBD in use, having it utilize the mysql_insertid field of the statement handle when it can, and falling back on the above-described solution when using other DBDs.
So, what I would really like to know... Does some better, less hackish way exist to accomplish that which I have described, or am I stuck dealing with it in some way along the lines of what I have delineated herein?
In reply to Auto-Increment and DBD Agnosticism by skyknight
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |