Hello again, I not sure a stated the problem we are seeing very clearly. So I have created a simplified test case to demonstrate the problem.
CircleTest.java is the TestCase
OneTable, TwoTable, and Child.java are the persistent objects.
The following was added to the repository_junit.xml:
-------------------------------------------
<class-descriptor
class="org.apache.ojb.broker.OneTable"
table="ONETABLE"
>
<field-descriptor id="1"
name="oneId"
column="ONEID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="name"
column="NAME"
jdbc-type="VARCHAR"
/>
<collection-descriptor
name="twos"
element-class-ref="org.apache.ojb.broker.TwoTable"
auto-retrieve="true"
auto-update="true"
auto-delete="true"
>
<inverse-foreignkey field-id-ref="2"/>
</collection-descriptor>
<collection-descriptor
name="children"
element-class-ref="org.apache.ojb.broker.Child"
auto-retrieve="true"
auto-update="true"
auto-delete="true"
>
<inverse-foreignkey field-id-ref="2"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor
class="org.apache.ojb.broker.TwoTable"
table="TWOTABLE"
>
<field-descriptor id="1"
name="twoId"
column="TWOID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="oneId"
column="ONEID"
jdbc-type="INTEGER"
/>
<field-descriptor id="3"
name="name"
column="NAME"
jdbc-type="VARCHAR"
/>
<reference-descriptor
name="one"
class-ref="org.apache.ojb.broker.OneTable"
auto-update="true"
>
<foreignkey field-id-ref="2"/>
</reference-descriptor>
<collection-descriptor
name="children"
element-class-ref="org.apache.ojb.broker.Child"
auto-retrieve="true"
auto-update="true"
auto-delete="true"
>
<inverse-foreignkey field-id-ref="3"/>
</collection-descriptor>
</class-descriptor>
<class-descriptor
class="org.apache.ojb.broker.Child"
table="CHILD"
>
<field-descriptor id="1"
name="childId"
column="CHILDID"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="oneId"
column="ONEID"
jdbc-type="INTEGER"
/>
<field-descriptor id="3"
name="twoId"
column="TWOID"
jdbc-type="INTEGER"
/>
<field-descriptor id="4"
name="name"
column="NAME"
jdbc-type="VARCHAR"
/>
<reference-descriptor
name="one"
class-ref="org.apache.ojb.broker.OneTable"
auto-update="true"
>
<foreignkey field-id-ref="2"/>
</reference-descriptor>
<reference-descriptor
name="two"
class-ref="org.apache.ojb.broker.TwoTable"
auto-update="true"
>
<foreignkey field-id-ref="3"/>
</reference-descriptor>
</class-descriptor>
-----------------------------------
And the following was added to the objtest-schema.xml:
------------------------------
<table name="ONETABLE">
<column name="ONEID" required="true" primaryKey="true" type="INTEGER"/>
<column name="NAME" type="VARCHAR" size="20"/>
</table>
<table name="TWOTABLE">
<column name="TWOID" required="true" primaryKey="true" type="INTEGER"/>
<column name="ONEID" required="true" type="INTEGER"/>
<column name="NAME" type="VARCHAR" size="20"/>
<foreign-key foreignTable="ONETABLE">
<reference local="ONEID" foreign="ONEID"/>
</foreign-key>
</table>
<table name="CHILD">
<column name="CHILDID" required="true" primaryKey="true"
type="INTEGER"/>
<column name="ONEID" required="true" type="INTEGER"/>
<column name="TWOID" required="true" type="INTEGER"/>
<column name="NAME" type="VARCHAR" size="25"/>
<foreign-key foreignTable="ONETABLE">
<reference local="ONEID" foreign="ONEID"/>
</foreign-key>
<foreign-key foreignTable="TWOTABLE">
<reference local="TWOID" foreign="TWOID"/>
</foreign-key>
</table>
------------------------
This test fails with the following error:
-------------------------------
Caused an ERROR
Integrity constraint violation: CHILD_FK_2 table: TWOTABLE in statement
[INSERT INTO CHILD (CHILDID,ONEID,TWOID,NAME) VALUES ( 1, 1, 1, 'name' ) ]
java.sql.SQLException: Integrity constraint violation: CHILD_FK_2 table:
TWOTABLE in statement [INSERT INTO CHILD (CHILDID,ONEID,TWOID,NAME) VALUES (
1, 1, 1, 'name' ) ]
at org.hsqldb.Trace.getError(Unknown Source)
at org.hsqldb.jdbcResultSet.<init>(Unknown Source)
at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source)
at org.hsqldb.jdbcConnection.execute(Unknown Source)
at org.hsqldb.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbcStatement.executeUpdate(Unknown Source)
at org.hsqldb.jdbcPreparedStatement.executeUpdate(Unknown Source)
at
org.apache.ojb.broker.accesslayer.JdbcAccess.executeInsert(JdbcAccess.java:1
95)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeToDb(PersistenceBr
okerImpl.java:1975)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:1916)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:637)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeCollectionObject(P
ersistenceBrokerImpl.java:812)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeCollections(Persis
tenceBrokerImpl.java:792)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeToDb(PersistenceBr
okerImpl.java:1998)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:1916)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:637)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeReferences(Persist
enceBrokerImpl.java:665)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeToDb(PersistenceBr
okerImpl.java:1947)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:1916)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:637)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:593)
at
org.apache.ojb.broker.singlevm.DelegatingPersistenceBroker.store(DelegatingP
ersistenceBroker.java:123)
at org.apache.ojb.broker.CircleTest.testCircle(CircleTest.java:38)
rethrown as org.apache.ojb.broker.KeyConstraintViolatedException: Integrity
constraint violation: CHILD_FK_2 table: TWOTABLE in statement [INSERT INTO
CHILD (CHILDID,ONEID,TWOID,NAME) VALUES ( 1, 1, 1, 'name' ) ]
at
org.apache.ojb.broker.accesslayer.JdbcAccess.executeInsert(JdbcAccess.java:2
10)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeToDb(PersistenceBr
okerImpl.java:1975)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:1916)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:637)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeCollectionObject(P
ersistenceBrokerImpl.java:812)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeCollections(Persis
tenceBrokerImpl.java:792)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeToDb(PersistenceBr
okerImpl.java:1998)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:1916)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:637)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeReferences(Persist
enceBrokerImpl.java:665)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.storeToDb(PersistenceBr
okerImpl.java:1947)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:1916)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:637)
at
org.apache.ojb.broker.singlevm.PersistenceBrokerImpl.store(PersistenceBroker
Impl.java:593)
at
org.apache.ojb.broker.singlevm.DelegatingPersistenceBroker.store(DelegatingP
ersistenceBroker.java:123)
at org.apache.ojb.broker.CircleTest.testCircle(CircleTest.java:38)
---------------------------------------
Alan
-----Original Message-----
From: Olmanson, Alan [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 16, 2002 9:13 AM
To: [EMAIL PROTECTED]
Subject: Referential Integrity Constraints
Hello,
For our application we are using a database that has a lot of referential
integrity constraints in it. And we have run into a situation in which OJB
is hitting one of them.
The problems occurs when we are storing a graph of objects, using the PB.
A m<--1 B 1-->m C 1-->m D m<--1 A
In the database the B and C's are already existing. We create a object A
which is has a reference to B, for each C attached to B we create a object D
which is also added to a collection on A.
The problem arises when A is stored. First OJB marks A as being stored,
saves it references (B) which stores B's collections (C) which stores C's
collections (D) which tries to stores its references (A) A is already marked
as stored (however it isn't in the database, since we are still storing its
references). When we try to store D we get a referential integrity error
because the record referenced in A by D doesn't exist.
Is this a bug/oversight in the PB, or something we need to work around in
our DB/code?
Alan
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
CircleTest.java
Description: Binary data
OneTable.java
Description: Binary data
TwoTable.java
Description: Binary data
Child.java
Description: Binary data
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
