Re: Bug in insert of bidirectionally mapped many-to-one relationships

Thu, 08 Feb 2007 12:01:10 -0800 

Jeff-
I was just reminded that we don't actually do any SQL statement re- ordering in order to optimize foreign key constraints in OpenJPA (this was one of the hold-backs from the contribution). We will always insert in the order in which you persist the instances. 


If you do tell OpenJPA to analyze the foreign key constraints using  
the SchemaFactory, we try to avoid constraint conflicts by nulling  
and updating whatever we can without reordering.


So your options are:


1. Insert in the correct order (you could always put some validation  
logic in a PrePersist callback to make sure that this is always done)
2. Make the foreign key constraint nullable (is the database supports  
nullable constraints; this has the added benefit of usually being  
faster at commit time)






On Feb 8, 2007, at 11:52 AM, Marc Prud'hommeaux wrote:


Jeff-


I don't think "optional" is being ignored ... if you try to commit  
an instance where testLookup is null, I bet we'll throw an  
exception as expected at commit time.



It almost sounds as if we are identifying a circular foreign key,  
and so are issuing the key-linking update statement after the 2  
rows are inserted in order to satisfy it (which would work if the  
foreign key constraint checking was deferred until commit time).



So you are saying that when you don't set the SchemaFactory  
setting, we default to inserting in the order you persist, and the  
inserts happen in a way that violates the constraints  
(TEST_DATATYPES then TEST_LOOKUP). When you do enable the  
SchemaFactory setting, we start to respect the foreign key  
constraints, and insert in the correct order (TEST_LOOKUP then  
TEST_DATATYPES). But if you then set the "TEST_LOOKUP_ID" column to  
be not-null, we go back to inserting in the wrong order. Is that  
correct?






On Feb 8, 2007, at 6:56 AM, Jeff Melching wrote:


ok, that worked but only if i make the foreign key  
TEST_DATATYPES.TEST_LOOKUP_ID nullable (ie. an optional foreign  
key). If I make the column not null in the db and change the  
mapping to the below, then I would expect the insert statement of  
test_datatypes to have the test_lookup_id populated. Instead, an  
update is ran to update the value which is incorrect. See below.  
Is the optional="false" attribute being ignored?


FYI: I am using build time enhancement if that matters or not.

<many-to-one name="testLookup" fetch="EAGER" optional="false">
<join-column name="TEST_LOOKUP_ID" nullable="false"/>
<cascade>
<cascade-all />
</cascade>
</many-to-one>


INSERT INTO I3.TEST_DATATYPES (TEST_DATATYPES_ID,  
DEFAULT_TEST_ONE_TO_MANY_ID, TEST_DATATYPES_NAME, TEST_DATE,  
TEST_TIME, TEST_DATETIME, TEST_TIMEZONE, TEST_AMT, TEST_IND,  
IS_TEST, COMMENTS, IS_ACTIVE, VER_ID, INSERT_DATETIME,  
INSERT_USER_ID, MODIFY_DATE, MODIFY_USER_ID) VALUES  
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [params=(long)  
475, (null) null, (null) null, (null) null, (null) null, (null)  
null, (null) null, (double) 0.0, (null) null, (int) 0, (null)  
null, (int) 1, (int) 1, (Timestamp) 2007-02-08 08:49:30.015,  
(long) 34, (Timestamp) 2007-02-08 08:49:31.062, (long) 1]



INSERT INTO I3.TEST_LOOKUP (TEST_LOOKUP_ID, TEST_LOOKUP_CODE,  
TEST_LOOKUP_NAME, TEST_LOOKUP_ALT_NAME, TEST_LOOKUP_ABBR,  
TEST_LOOKUP_SEQ, COMMENTS, IS_ACTIVE, VER_ID, MODIFY_DATE,  
MODIFY_USER_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) [params= 
(long) 66, (String) CODE, (null) null, (String) alt name, (String)  
Abbrev, (int) 0, (String) my lookup, (int) 1, (int) 1, (Timestamp)  
2007-02-08 08:49:31.078, (long) 1]



UPDATE I3.TEST_DATATYPES SET TEST_LOOKUP_ID = ? WHERE  
TEST_DATATYPES_ID = ? [params=(long) 66, (long) 475]


<graycol.gif>
"Marc Prud'hommeaux" <[EMAIL PROTECTED]>


"Marc Prud'hommeaux" <[EMAIL PROTECTED]>
Sent by: "Marc Prud'hommeaux" <[EMAIL PROTECTED]>
02/07/2007 02:56 PM
Please respond to
open-jpa-dev@incubator.apache.org

<ecblank.gif>

To
<ecblank.gif>

open-jpa-dev@incubator.apache.org
<ecblank.gif>

cc
<ecblank.gif>

<ecblank.gif>

Subject
<ecblank.gif>

Re: Bug in insert of bidirectionally mapped many-to-one relationships
<ecblank.gif>
<ecblank.gif>

Jeff-

Why is that order bad? Is it because it violates your foreign key
constraints, or some other reason?

If it is a foreign key issue, you need to tell OpenJPA to read
foreign keys from the schema in order to ensure that SQL ordering is
done correctly. You can do this by setting the
"openjpa.jdbc.SchemaFactory" property to "native(ForeignKeys=true)".


If that isn't the issue, can you clarify why this is causing  
problems?




On Feb 7, 2007, at 9:09 AM, Jeff Melching wrote:

>
>
>
>
>
>

> I have a bidirectional mapping of a many-to-one relationship as  
listed

> below.  If I have the following code:
>
>             TestDatatypes blob = new TestDatatypes();
>             TestLookup lookup = new TestLookup();
>
>             Set<TestDatatypes> tdts = new HashSet<TestDatatypes>();
>             tdts.add(blob);
>             lookup.setTestDatatypes(tdts);
>             blob.setTestLookup(lookup);
>
>             dao.insert(lookup);
>
> It works because the sql insert for the lookup table is ran first,
> but if I
> replace dao.insert(lookup) with dao. insert(blob), then the child
> sql is
> ran before the parent record has been inserted. as listed here:
>
> INSERT INTO TEST_DATATYPES (TEST_DATATYPES_ID, MODIFY_DATE,
> MODIFY_USER_ID,
> COMMENTS, INSERT_DATETIME, INSERT_USER_ID, IS_ACTIVE, IS_TEST,
> TEST_AMT,
> TEST_DATATYPES_NAME, TEST_DATE, TEST_DATETIME, TEST_IND, TEST_TIME,
> TEST_TIMEZONE, VER_ID, TEST_LOOKUP_ID) VALUES
> (?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?) [params=(long) 359, (Timestamp) 2007-02-06
> 10:07:12.468, (long) 1, (null) null, (Timestamp) 2007-02-06
> 10:07:11.671,
> (long) 34, (int) 1, (int) 0, (double) 0.0, (null) null, (null)
> null, (null)
> null, (null) null, (null) null, (null) null, (int) 1, (long) 40]
>
> INSERT INTO I3.TEST_LOOKUP (TEST_LOOKUP_ID, MODIFY_DATE,
> MODIFY_USER_ID,
> COMMENTS, IS_ACTIVE, TEST_LOOKUP_ABBR, TEST_LOOKUP_ALT_NAME,
> TEST_LOOKUP_CODE, TEST_LOOKUP_NAME, TEST_LOOKUP_SEQ, VER_ID) VALUES
> (?, ?,

> ?, ?, ?, ?, ?, ?, ?, ?, ?) [params=(long) 40, (Timestamp)  
2007-02-06
> 10:07:12.531, (long) 1, (String) my lookup, (int) 1, (String)  
Abbrev,

> (String) alt name, (String) CODE, (null) null, (int) 0, (int) 1]
>
> I believe this to be a bug as I have tried the same mappings with
> other jpa
> implementations and it works fine.
>
>
> <entity class="TestDatatypes">
>             <table schema="I3" name="TEST_DATATYPES" />
>             <sequence-generator name="TestDataTypeSeq"
>                   sequence-name="SEQ_TEST_DATATYPES_ID" allocation-
> size="1"
> />
>             <attributes>
>                   <id name="testDatatypesId">
>                         <column name="TEST_DATATYPES_ID" />
>                         <generated-value strategy="SEQUENCE"
>                               generator="TestDataTypeSeq" />
>                   </id>
>                   ...
>                   <many-to-one name="testLookup" fetch="EAGER">
>                         <join-column name="TEST_LOOKUP_ID" />
>                         <cascade>
>                               <cascade-all />
>                         </cascade>
>                   </many-to-one>
>             </attributes>
>       </entity>
>       <entity class="TestLookup">
>             <table schema="I3" name="TEST_LOOKUP" />
>             <sequence-generator name="LookupSeq"
>                   sequence-name="SEQ_TEST_LOOKUP_ID" allocation-
> size="1" />
>             <attributes>
>                   <id name="testLookupId">
>                         <column name="TEST_LOOKUP_ID" />
>                         <generated-value strategy="SEQUENCE"
>                               generator="LookupSeq" />
>                   </id>
>                   ...
>                   <one-to-many name="testDatatypes"
>                         target-entity=
> "com.ibm.ptp.i3.domain.test.TestDatatypes"
>                         mapped-by="testLookup" fetch="EAGER">
>
>                         <cascade>
>                               <cascade-all />
>
>                         </cascade>
>                   </one-to-many>
>             </attributes>
>       </entity>




























					Reply via email to