Just about right. When the SchemaFactory setting is enabled the update
statement occurs regardless of the way I mark the many to one relationship
optional attribute.
Without the SchemaFactory setting, no update statement is ran only inserts,
and the order is dependent on which object you pass to em.persist().
You are correct that if I mark optional=false and testLookup is null, it
throws an exception, but I believe the interpretation of optional is
incorrect. The optional flag should probably be used to determine if the
relationship is mandatory - ie. the related object must exist first. The
nullable attribute of the join-column should probably be checked to see if
the field can be null or not.
make sense?
"Marc
Prud'hommeaux"
<[EMAIL PROTECTED] To
org> open-jpa-dev@incubator.apache.org
Sent by: "Marc cc
Prud'hommeaux"
<[EMAIL PROTECTED] Subject
mail.com> Re: Bug in insert of
bidirectionally mapped many-to-one
relationships
02/08/2007 01:52
PM
Please respond to
[EMAIL PROTECTED]
bator.apache.org
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>
>
>
