Re: [OJB-developers] Database generated primary keys

[Thomas M. <tho...@ho...>]

Hi Brian,

Brian Devries wrote:

> Hi all,
> 
> I was wondering if anyone has tried to, or if there are plans to expand
> OJB to support database generated primary keys, such as Informix's
> SERIAL type.
> 


There is a code-contribution to use OJB with oracle sequencenumbers. But 
it has not been incorporated to the main distribution yet.

There have been no request in this direction so far.

Even commercial products like TopLink only support Oracle sequence numbers.


> I am running into a problem using the included SequenceManagers when the
> tables being mapped to may be inserted into by other non-OJB processes.


Right, this is currently not handled by OJB. I'm currently facing the 
same problem in a project in my company.


> In such cases OJB may begin a transaction to insert a new row, grab the
> max value from the table in question, but then by the time it actually
> does the insert, another process may have already inserted the next row.
> 
> There seem to be two options, to create a SequenceManager which gets the
> max value on the table and places a lock on the table until the
> transaction is complete, or to have OJB try to support the built in
> SERIAL-type fields of the databases.
> 


You might also implement a SequenceManager, that does not lock the 
table, but inserts a row with the new primary key.
This will preserve uniqueness of the primary key accross all running 
applications (incl. OJB).

In my above mentioned project I will go this way.


>>From looking at the architecture of the ODMG code, however, that may be
> pretty tricky. 


Right The ODMG implementation is very "far away" from RDBMS specifics.
Things like sequence-numbering etc should be handled on the 
PersistenceBroker layer.

It looks like when an object is first locked, a new key
> is generated using the SequenceManager if one is not already set and the
> autoincrement indicator is true. This allows the LockManager to lock a
> new object using the newly generated primary key. It is not until later,
> however, when the ODMG transaction is committed, that the insert is
> actually performed. This means that if you were going to try to rely on
> the database to generate the primary key on insert, that you don't have
> a primary key when the object is first locked to hold a lock on.
> 
> One question that came to mind was if a lock really needs to be placed
> on a new object that is deferring its primary key generation to the
> database? Instead, could the identity of that object just be unique to
> that instance until a primary key is assigned to it during the commit?
> This would mean that the assignment of reference foreign keys and
> collection inverse foreign keys would have to be moved from the lock
> stage to the commit stage, but I can't think of why that would be a
> problem.


The reason is not so obvious to see. here is why:

Say you have two classes A and B. A has a collection of Bs.
say we have an object a of type A that holds a collection of 5 Bs.

Navigability is unidirectional from A to B. That is a B has no reference 
to its A!

The objects are newly created and primary and foreign keys are not assigned.

Now we lock a (and the 5 Bs) to a tx without assignment of Identities 
and foreignkeys.

Now we try to commit the tx:
If we try to store one of the Bs it won't have its foreignkey attribute 
pointing to its A filled.

As there is no reference back to a the Transactionmanager has no way to 
know how to fill the foreign key attribute.

The only place the transactionmanager can do this is on registering A, 
or later when Bs are added to the collection attribute of a.

I hope this makes things clearer a bit.


cheers,

Thomas


> 
> Has anyone gone down this road before, or have thoughts on this?
> 
> -Brian
> 
> --
> Brian DeVries                   Sr. Software Engineer
> mailto:bde...@in...   http://www.intraware.com
> Voice: 925.253.6516             Fax: 925.253.6785
> --------------------------------------------------------
> Intraware... The leading provider of Electronic Software
>              Delivery and Management (ESDM) Solutions
> 
> 
> 
> _______________________________________________________________
> 
> Don't miss the 2002 Sprint PCS Application Developer's Conference
> August 25-28 in Las Vegas -- http://devcon.sprintpcs.com/adp/index.cfm
> 
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
> 
> 
> 
>