Key column does not cascade multiple joins.
-------------------------------------------
Key: OPENJPA-206
URL: https://issues.apache.org/jira/browse/OPENJPA-206
Project: OpenJPA
Issue Type: Bug
Affects Versions: 0.9.7
Environment: WebSphere 6.1, DB2 v8.1
Reporter: Matt Anderson
Problem: Parent keys will not cascade beyond one generation. Example: Company
--> Department --> Employee: Company key will not cascade to Employee.
The following example further describes this issue.
A COMPANY table contains two columns, COMP_ID and NAME. The COMP_ID column is
the primary key.
A DEPARTMENT table contains three columns, COMP_ID, DEPT_ID, and NAME. The
COMP_ID and DEPT_ID columns are the primary key columns. The COMP_ID column is
a foreign key column which references the COMP_ID column in the COMPANY table.
An EMPLOYEE table contains four columns, COMP_ID, DEPT_ID, EMP_ID, and NAME.
The COMP_ID, DEPT_ID, and EMP_ID columns are the primary key columns. The
COMP_ID and DEPT_ID columns are foreign key columns which reference the COMP_ID
and DEPT_ID columns respectively in the DEPARTMENT table.
An entity exists for each table, Company, Department, and Employee. The
Company entity has three attributes, compId, name, and departments. The compId
attribute maps to the COMP_ID column. The name attribute maps to the NAME
column. The departments attribute maps a one-to-many relationship to the
Department entity.
The Department entity has five attributes, compId, deptId, name, employees, and
company. The compId attribute maps to the COMP_ID column. The deptId
attribute maps to the DEPT_ID column. The name attribute maps to the NAME
column. The employees attribute maps a one-to-many relationship to the
Employee entity. The company attribute maps a many-to-one relationship to the
Company entity, joined by the COMP_ID column.
The Employee entity has five attributes, compId, deptId, empId, name, and
department. The compId attribute maps to the COMP_ID column. The deptId
attribute maps to the DEPT_ID column. The empId attribute maps to the EMP_ID
column. The name attribute maps to the NAME column. The department attribute
maps a many-to-one relationship to the Department entity, joined by the COMP_ID
and DEPT_ID columns.
Below are the eleven steps to re-create this problem.
1. A new Company entity is instantiated.
2. The name attribute on the Company instance is set to "company".
3. A new Department entity is instantiated.
4. The name attribute on the Department instance is set to "department".
5. The department attribute on the Company instance is set to the Department
instance.
6. A new Employee entity is instantiated.
7. The name attribute on the Employee instance is set to "Frank".
8. The employee attribute on the Department instance is set to the Employee
instance.
9. The Employee instance is added to the employees attribute on the Department
instance.
10. The Department instance is added to the departments attribute on the
Company instance.
11. The Company instance is persisted.
The following SQL statements are executed.
INSERT INTO COMPANY (COMP_ID, NAME)
VALUES (?, ?)
[params=(long) 1, (String) company]
INSERT INTO DEPARTMENT (COMP_ID, DEPT_ID, NAME)
VALUES (?, ?, ?)
[params=(long) 1, (long) 1, (String) department]
INSERT INTO EMPLOYEE (DEPT_ID, EMP_ID, NAME)
VALUES (?, ?, ?)
[params=(long) 1, (long) 1, (String) Frank]
A PersistenceException is thrown because of the DB2 error noted below. The
COMP_ID column cascades to the DEPARTMENT table, but does not cascade to the
EMPLOYEE table.
DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2,
TABLEID=4, COLNO=0 {prepstmnt 1256737512
INSERT INTO EMPLOYEE (DEPT_ID, EMP_ID, NAME)
VALUES (?, ?, ?)
[params=(long) 1, (long) 1, (String) Frank]} [code=-407, state=23502]SQLCA
OUTPUT[Errp=SQLDFMT1, Errd=[-2146041828, 28, 0, 0, 0, 0]]
An application is provided to demonstrate the issue. To setup and
unsuccessfully run demonstration with OpenJPA:
1. Add DB provider JAR(s) to the FunInheritanceJava project's build path.
2. Modify FunInheritanceJava/src/META-INF/applicationcontext-jpa.xml to set the
correct dataSource class, user, password, and currentSchema.
3. Modify FunInheritanceJava/database/createtables.sql to set current schema to
match value set in step 1.
4. Setup database by running SQL in
FunInheritanceJava/database/createtables.sql.
5. Run testCreate_WithDepartmentAndEmployee Junit test in
FunInheritanceJava/test/service/dao/CompanyDaoTest.java.
6. Results should indicate an AssertionFailedError caused by the
PersistenceException displayed above.
To successfully run demonstration with TopLink:
1. Change "org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter" to
"org.springframework.orm.jpa.vendor.TopLinkJpaVendorAdapter" in
FunInheritanceJava/src/META-INF/applicationcontext-jpa.xml.
2. Run testCreate_WithDepartmentAndEmployee Junit test in
FunInheritanceJava/test/service/dao/CompanyDaoTest.java.
3. Results should indicate a successful Junit execution.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
