There is only one way to handle ManyToMany table on BulkDelete - the entries corresponding to objects to be deleted should be deleted.
It doesn't matter whether we intent to keep the objects on the other side of the mappings or delete them, too, or else.
There is more than one way in say, ManyToOne case:
if we delete departments me may choose to either delete all their employees, or keep their employees department-less, or switch to another department...
BulkDelete doesn't cascade - merely allows for choices.
----- Original Message -----
From: Wonseok Kim
To: persistence_at_glassfish.dev.java.net
Sent: Friday, March 30, 2007 12:35 PM
Subject: Re: Bulk DELETE query should handle relationships automatically?
Hi Andrei,
There is already similar issue 1775 for this, I just now added many-to-many case and a test to that.
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1775
Your point is that provider should handle many-to-many associations, but application should handle other types of associations, right?
Then, why does not provider handle other types of association? Isn't it convenient and consistent to handle all associations similarly (of course generated SQL is very different) whether it's many-to-many or not? :-)
Thanks,
-Wonseok
On 3/31/07, Andrei Ilitchev <andrei.ilitchev_at_oracle.com> wrote:
The user should be able to delete any set of objects using a combination of BulkDeletes and BulkUpdates.
It's easy to see how to do it if the class objects of which to be deleted uses OneToOne, OneToMany, ManyToOne mappings.
ManyToMany is different: it has its own table.
BulkDelete all Employees working on the projects with names starting with "A" illustrates why ManyToMany should be handled by BulkDelete.
Please log the cache invalidation issue.
----- Original Message -----
From: Wonseok Kim
To: persistence_at_glassfish.dev.java.net
Sent: Friday, March 30, 2007 11:07 AM
Subject: Re: Bulk DELETE query should handle relationships automatically?
Hi Tom,
I found problem in TopLink that shared cache does not reflect the change of many-to-many associations which are removed by DELETE query.
i.e. even though many-to-many assocations are removed in database, Employees have still associated Projects which are deleted by the query(of course em is closed and recreated). If I clear shared cache manually after DELETE query, this does not occur.
Hmm, anyhow I think bulk DELETE cause confusions and can cause problem. it needs to be clarified.
Cheers,
-Wonseok
On 3/30/07, Tom Ware <tom.ware_at_oracle.com> wrote:
I think this is an area where the spec could use some clarification.
If we cannot remove the entries from join table, there are very few
cases where a bulk delete can be called on an Entity with a
bidirectional ManyToMany relationship. It would be nice if we could
maintain this functionality somehow.
-Tom
Wonseok Kim wrote:
> Hi Michael,
>
> spec only says cascade which I think means associated entites are not
> deleted even with Cascade.DELETE, but does not tell association itself
> should be maintained or not - of course the association should be
> removed for successful DELETE, but It's not clear that application
> should do it.
> Also, do you have an idea regarding an UPDATE query which can remove
> many-to-many association for DELETE query (1)?
>
> Thanks,
> -Wonseok
>
> On 3/30/07, * Michael Bouschen* <Michael.Bouschen_at_sun.com
> <mailto:Michael.Bouschen_at_sun.com>> wrote:
>
> Hi Wonseok,
>
> I think a buld delete should not handled relationships. Here is
> what the
> spec says in chapter "4.10 Bulk Update and Delete Operations":
> A delete operation only applies to entities of the specified class
> and
> its subclasses. It does not cascade to related entities.
>
> Regards Michael
>
> > Hi Tom, Marina, Michael...
> >
> > While I'm investigating the issue 2744(bulk delete problem with
> > temporary table), I come to have a very curious question.
> >
> > Should bulk DELETE query handle relationships automatically or
> should
> > application do it manually by another query? It seems there is no
> > words about it in the specification.
> >
> > For example, if Department and Employee have one-to-many
> relationship,
> > and Employee and Project have many-to-many relationship like below.
> >
> > @Entity public class Department {
> > @Id Integer id;
> > String name;
> > @OneToMany(mappedBy="dept") Collection<Employee> employees;
> > }
> >
> > @Entity public class Employee {
> > @Id Integer id;
> > String name;
> > @ManyToOne Department dept;
> > @ManyToMany(mappedBy="members") Collection<Project> projects;
> > }
> >
> > @Entity public class Project {
> > @Id Integer id;
> > String name;
> > @ManyToMany Collection<Employee> members;
> > }
> >
> > (1) "DELETE FROM Project p WHERE p.name < http://p.name>
> <http://p.name > = :name"
> > (there is many-to-many relationship bet. Project and Employee)
> >
> > Should this also remove many-to-many relation rows from relation
> table
> > automatically?
> >
> > TopLink is now doing this, generating DELETE SQLs of many-to-many
> > relations like below.
> >
> > ExpressionQueryMechanism:1165
> > // Add statements for ManyToMany and
> DirectCollection mappings
> > Vector deleteStatementsForMappings =
> > buildDeleteAllStatementsForMappings(selectCallForExist,
> > selectStatementForExist, tablesToIgnore == null);
> > if(!deleteStatementsForMappings.isEmpty()) {
> > if(getSQLStatement() != null) {
> > getSQLStatements().add(getSQLStatement());
> > setSQLStatement(null);
> > }
> >
> getSQLStatements().addAll(deleteStatementsForMappings);
> > }
> >
> > (2) "DELETE FROM Department d WHERE d.name <http://d.name>
> <http://d.name> = :name"
> > (there is one-to-many relationship bet. Department and Employee)
> >
> > Similary, should this also remove one-to-many relations
> automatically?
> >
> > Toplink is *not* generating any SQLs to set relationship column
> of the
> > owner to NULL in this case, so foreign key constraint violation
> occurs!
> >
> > To avoid constraint violation, I should execute another query like
> > "UPDATE Employee e SET e.dept = NULL WHERE e.dept = :dept" before
> > DELETE query.
> >
> >
> > In sum, it seems that two cases are inconsistent and it's not clear
> > whether bulk DELETE query should handle relationship automatically.
> >
> > If it's not proper thing for provider to handle relationship
> like (1)
> > and application should always handle relationship for itself like I
> > did in (2), I could not figure out a query to update collection
> > association field to NULL for (1) like below.
> >
> > "UPDATE Project p SET p.members = NULL WHERE p.name
> < http://p.name> <http://p.name> =
> > :name" -> p.members = NULL is error as JPQL only allows single
> valued
> > association field for update.
> >
> > I guess this issue seems something to be clearified in the spec.
> > Could anyone give me some explanation or opinion about this?
> >
> > Thanks,
> > -Wonseok
> >
>
>