Cascade Delete

[Jeff Caddel <jc...@cox.net>]

I'm trying to understand how PersistenceBroker handles deletes.  I setup 
this repository.xml and the corresponding database schema to illustrate 
an issue I'm having.


Depts and Projects have a n:m relationship with Employees.  Each dept 
can have many employees, each employee can belong to many depts.  Same 
with projects.


When I call broker.delete(employee) several things happen (not sure 
about the order):


1) The Employee object gets removed from the OJB memory cache.
2) The corresponding row gets removed from the employee table in the 
database.
3) Any DeptEmployee and ProjectEmployee objects related to the Employee 
object get removed from the OJB memory cache.
4) The corresponding rows in the dept_employee and project_employee 
tables get deleted.


The issue is that none of the Dept and Project objects in the OJB memory 
cache get updated.  The Dept objects have references to DeptEmployee 
objects that have been deleted from the database and removed from the 
OJB cache.  Same thing with Project objects.  On the database side, 
everything is handled perfectly.  If I remove a Dept from the cache and 
then re-query for it, everything is as it should be. 


I was about to extend the default caching algorithm provided with OJB to 
implement "cascade delete" on the memory cache.  The only additional 
thing it would do is remove references to deleted DeptEmployee and 
ProjectEmployee objects from the Dept and Project objects.  My thinking 
is that I would examine the information present in the xml config file 
(via MetadataManager) to determine what other objects in the cache need 
to be checked when a delete occurs.


Has anyone else encountered this?  Is there a setting somewhere I'm 
missing?  Any thoughts/comments/suggestions are welcomed!  (I'm on 
version 1.0 RC2)



Here is the repository.xml file I'm using:


<descriptor-repository version="1.0" isolation-level="read-uncommitted">

   <jdbc-connection-descriptor jcd-alias="apply"
                                  platform="Oracle"
                        default-connection="true"
                                jdbc-level="3.0"
                                    driver="oracle.jdbc.OracleDriver"
                                  protocol="jdbc"
                               subprotocol="oracle:thin"
                                   dbalias="@localhost:1521:orcl"
                                  username="xxxxxx"
                                  password="xxxxxx"/>

   <class-descriptor class="net.link11.apply.persistence.ojb.Dept" 
table="dept">
      <field-descriptor name="id"   column="id"   jdbc-type="INTEGER" 
primarykey="true" autoincrement="true"/>
      <field-descriptor name="name" column="name" jdbc-type="VARCHAR"/>
      <collection-descriptor name="deptEmployees" 
element-class-ref="net.link11.apply.persistence.ojb.DeptEmployee" 
auto-delete="true">
        <inverse-foreignkey field-ref="deptId"/>
      </collection-descriptor>
   </class-descriptor>

   <class-descriptor class="net.link11.apply.persistence.ojb.Project" 
table="project">
      <field-descriptor name="id"   column="id"   jdbc-type="INTEGER" 
primarykey="true" autoincrement="true"/>
      <field-descriptor name="name" column="name" jdbc-type="VARCHAR"/>
      <collection-descriptor name="projectEmployees" 
element-class-ref="net.link11.apply.persistence.ojb.ProjectEmployee" 
auto-delete="true">
        <inverse-foreignkey field-ref="projectId"/>
      </collection-descriptor>
   </class-descriptor>

   <class-descriptor class="net.link11.apply.persistence.ojb.Employee" 
table="employee">
      <field-descriptor name="id"   column="id"   jdbc-type="INTEGER" 
primarykey="true" autoincrement="true"/>
      <field-descriptor name="name" column="name" jdbc-type="VARCHAR"/>
      <collection-descriptor name="employeeDepts" 
element-class-ref="net.link11.apply.persistence.ojb.DeptEmployee" 
auto-delete="true">
        <inverse-foreignkey field-ref="employeeId"/>
      </collection-descriptor>
      <collection-descriptor name="employeeProjects" 
element-class-ref="net.link11.apply.persistence.ojb.ProjectEmployee" 
auto-delete="true">
        <inverse-foreignkey field-ref="employeeId"/>
      </collection-descriptor>
   </class-descriptor>

   <class-descriptor 
class="net.link11.apply.persistence.ojb.DeptEmployee" table="dept_employee">
     <field-descriptor name="deptId"     column="dept_id"     
jdbc-type="INTEGER" primarykey="true"/>
     <field-descriptor name="employeeId" column="employee_id" 
jdbc-type="INTEGER" primarykey="true"/>
     <reference-descriptor name="dept" 
class-ref="net.link11.apply.persistence.ojb.Dept">
       <foreignkey field-ref="deptId"/>
     </reference-descriptor>
     <reference-descriptor name="employee" 
class-ref="net.link11.apply.persistence.ojb.Employee">
       <foreignkey field-ref="employeeId"/>
     </reference-descriptor>
   </class-descriptor>

   <class-descriptor 
class="net.link11.apply.persistence.ojb.ProjectEmployee" 
table="project_employee">
     <field-descriptor name="projectId"  column="project_id"  
jdbc-type="INTEGER" primarykey="true"/>
     <field-descriptor name="employeeId" column="employee_id" 
jdbc-type="INTEGER" primarykey="true"/>
     <reference-descriptor name="project" 
class-ref="net.link11.apply.persistence.ojb.Project">
       <foreignkey field-ref="projectId"/>
     </reference-descriptor>
     <reference-descriptor name="employee" 
class-ref="net.link11.apply.persistence.ojb.Employee">
       <foreignkey field-ref="employeeId"/>
     </reference-descriptor>
   </class-descriptor>

</descriptor-repository>




Here is the sql script I'm using to create the tables:


DROP TABLE dept CASCADE CONSTRAINTS;
DROP TABLE dept_employee CASCADE CONSTRAINTS;
DROP TABLE project_employee CASCADE CONSTRAINTS;
DROP TABLE employee CASCADE CONSTRAINTS;
DROP TABLE project CASCADE CONSTRAINTS;

CREATE TABLE dept(id NUMBER(15) NOT NULL, name VARCHAR2(50));
CREATE TABLE employee(id NUMBER(15) NOT NULL, name VARCHAR2(50));
CREATE TABLE project(id NUMBER(15) NOT NULL, name VARCHAR2(50));
CREATE TABLE dept_employee(dept_id NUMBER(15) NOT NULL, employee_id 
NUMBER(15) NOT NULL);
CREATE TABLE project_employee(project_id NUMBER(15) NOT NULL, 
employee_id NUMBER(15) NOT NULL);

ALTER TABLE dept ADD CONSTRAINT pk_dept PRIMARY KEY(id) USING INDEX 
TABLESPACE indx;
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY(id) USING 
INDEX TABLESPACE indx;
ALTER TABLE project ADD CONSTRAINT pk_proejct PRIMARY KEY(id) USING 
INDEX TABLESPACE indx;
ALTER TABLE dept_employee ADD CONSTRAINT pk_dept_employee PRIMARY 
KEY(dept_id,employee_id) USING INDEX TABLESPACE indx;
ALTER TABLE project_employee ADD CONSTRAINT pk_project_employee PRIMARY 
KEY(project_id,employee_id) USING INDEX TABLESPACE indx;

ALTER TABLE dept_employee ADD CONSTRAINT de_dept_fk FOREIGN KEY 
(dept_id) REFERENCES dept (id) ON DELETE CASCADE;
ALTER TABLE dept_employee ADD CONSTRAINT de_employee_fk FOREIGN KEY 
(employee_id) REFERENCES employee (id) ON DELETE CASCADE;
ALTER TABLE project_employee ADD CONSTRAINT pe_project_fk FOREIGN KEY 
(project_id) REFERENCES project (id) ON DELETE CASCADE;
ALTER TABLE project_employee ADD CONSTRAINT pe_employee_fk FOREIGN KEY 
(employee_id) REFERENCES employee (id) ON DELETE CASCADE;

INSERT INTO dept(id,name) VALUES (1,'Finance');
INSERT INTO dept(id,name) VALUES (2,'Human Resources');
INSERT INTO dept(id,name) VALUES (3,'Engineering');
INSERT INTO employee(id,name) VALUES (1,'Jeff');
INSERT INTO employee(id,name) VALUES (2,'Patrick');
INSERT INTO employee(id,name) VALUES (3,'Susan');
INSERT INTO project(id,name) VALUES (1,'Offshore Drilling');
INSERT INTO project(id,name) VALUES (2,'EPA');
INSERT INTO project(id,name) VALUES (3,'Boston Marathon');
INSERT INTO dept_employee(dept_id,employee_id) VALUES (1,1);
INSERT INTO dept_employee(dept_id,employee_id) VALUES (2,1);
INSERT INTO dept_employee(dept_id,employee_id) VALUES (3,1);
INSERT INTO project_employee(project_id,employee_id) VALUES (1,1);
INSERT INTO project_employee(project_id,employee_id) VALUES (2,1);
INSERT INTO project_employee(project_id,employee_id) VALUES (3,1);
INSERT INTO dept_employee(dept_id,employee_id) VALUES (1,2);
INSERT INTO dept_employee(dept_id,employee_id) VALUES (2,2);
INSERT INTO project_employee(project_id,employee_id) VALUES (2,2);
INSERT INTO dept_employee(dept_id,employee_id) VALUES (1,3);