Re: mysql AUTO_INCREMENT
Re: mysql AUTO_INCREMENT
- Subject: Re: mysql AUTO_INCREMENT
- From: Jake Kauth <email@hidden>
- Date: Mon, 6 Oct 2008 11:05:42 -0700
On Oct 4, 2008, at 4:58 AM, Q wrote:
The reason that WO just doesn't work well with AUTO_INC is because
it expects to be able to obtain an id for the EO BEFORE the insert
is executed so that it can resolve temporary id's and any foreign
keys in a transaction safe way. When you use AUTO_INC, you don't
know what the ID is actually going to be until after it has been
inserted. What you need in order to do this properly is a
sequence, which mysql lacks, but the behaviour can be emulated by
using the EO_PK_TABLE. You may be able to sort of resolve the
issue of EO_PK_TABLE being out of date by using a trigger to
update the EO_PK_TABLE on INSERT, or better yet use a trigger to
use the EO_PK_TABLE to generate the ID value instead of allowing
mysql to AUTO_INC the ID field, but I have never actually tried to
do that.
Thank you very much for this explanation, it makes a lot more
sense now.
But I'm not completely dissuaded by this. The next AUTO_INCREMENT
key is stored in table metadata - where it can be read and written
to. So would it not be possible to hack the mysql driver to use
this value instead of using the one in the EO_PK_TABLE?
On Oct 4, 2008, at 10:47 AM, Chuck Hill wrote:
I am not familiar with how MySQL stores this, but this sounds quite
possible to me.
The plugin simply needs to be able to return a new primary key value.
EOF does not care how it gets that value.
I had a similar problems with mixing PHP and other languages; I needed
to be able
to create a new record atomically, without locking the database, but
also without
using auto-increment in the main table.
In the end, I used a second table with unique keys generated by
mysql's auto-increment column.
For your case, rather than hacking the mysql driver, couldn't you do
something like this:
1) Create a mysql stored procedure that does the same thing that the
WebOb Java
framework does to manage the EO_PK_TABLE, to generate unique ID before
insertion.
I'm not sure of the exact behavior, but it could probably be
generalized to a
mysql function call. Call it EO_PK_TABLE_generate_id().
2) Make a WebObects plugin to override the behavior of the native WebOb
EO_PK_TABLE manager, so that instead of using the Java code, it calls
the myqsql stored procedure EO_PK_TABLE_generate_id().
?
jake
*****
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden