You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by "Manaster, Carl" <Ca...@ACTIVX.com> on 2008/04/09 21:58:31 UTC
Joined Delete
Hi, all,
Below my signature is a test that demonstrates the problem I'm seeing.
We're using torque 3.3. When I try to delete through a join, it is
deleting all records from the table, instead of just the records of
interest. It does not matter whether records in the T1 table exist or
not. Can you please explain this behavior and suggest a workaround?
Thanks!
Peace,
--Carl
package com.activx.apps.ms.quan.util.tests;
import org.apache.torque.TorqueException;
import org.apache.torque.util.CountHelper;
import org.apache.torque.util.Criteria;
import com.activx.lims.tests.TorqueTestCase;
import com.activx.om.T1Peer;
import com.activx.om.T2;
import com.activx.om.T2Peer;
/**
* create table t1 (t1_id bigint(20) not null auto_increment, primary
key (t1_id));
* create table t2 (t2_id bigint(20) not null auto_increment, t1_id
bigint(20) not null, primary key (t2_id));
*/
public class JoinedDeleteTest extends TorqueTestCase {
public void testDelete() throws Exception {
// start with an empty table
T2Peer.doDelete(new Criteria());
assertEquals(0, countT2s());
// add one record with a foreign key of 50
T2 t2 = new T2();
t2.setT1Id(50);
t2.save();
// see that the record made it in
assertEquals(1, countT2s());
// delete records joined to the #100 record (of which
none exist)
final Criteria criteria = new
Criteria().add(T1Peer.T1_ID, 100).addJoin(T1Peer.T1_ID, T2Peer.T1_ID);
assertEquals(0, new CountHelper().count(criteria));
T2Peer.doDelete(criteria);
// expect that no records were deleted, but in fact all
records were deleted.
assertEquals(1, countT2s());
}
private int countT2s() throws TorqueException {
return new CountHelper().count(new
Criteria().add(T2Peer.T2_ID, 0, Criteria.GREATER_EQUAL));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Joined Delete
Posted by Thomas Vandahl <tv...@apache.org>.
Manaster, Carl wrote:
> Below my signature is a test that demonstrates the problem I'm seeing.
> We're using torque 3.3. When I try to delete through a join, it is
> deleting all records from the table, instead of just the records of
> interest. It does not matter whether records in the T1 table exist or
> not. Can you please explain this behavior and suggest a workaround?
Deletes with joined criterias should be used with extreme caution.
Village does not support the correct SQL syntax for that. Try to avoid
this. Other than that I second Thomas Fischers suggestion to open a JIRA
issue at https://issues.apache.org/jira/
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Joined Delete
Posted by Thomas Vandahl <tv...@apache.org>.
Thomas Fischer wrote:
> The best thing that came into my mind as a quick solution is to select all
> objects you want to delete and to pass them one by one to
> XXXPeer.doDelete. This looks like a big performance issue but in fact it is
> not as Torque does this anyway internally :-(
There is BasePeer.deleteAll() which may serve as a shortcut if that is
an option.
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by Thomas Fischer <fi...@seitenbau.net>.
Hi Carl,
Thanks for the jira issue.
The best thing that came into my mind as a quick solution is to select all
objects you want to delete and to pass them one by one to
XXXPeer.doDelete. This looks like a big performance issue but in fact it is
not as Torque does this anyway internally :-(
Hope this helps until the issue is resolved,
Thomas
"Manaster, Carl" <Ca...@ACTIVX.com> schrieb am 10.04.2008 19:21:14:
> https://issues.apache.org/jira/browse/TORQUE-112
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Thomas,
> Could you please open a jira issue for this ?
https://issues.apache.org/jira/browse/TORQUE-112
Peace,
--Carl
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Thomas,
> Could you please open a jira issue for this ?
Could you send me a link with jira instructions, please? I don't know
jira. Thanks.
Peace,
--Carl
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by Thomas Fischer <fi...@seitenbau.net>.
Could you please open a jira issue for this ?
Thanks,
Thomas
"Manaster, Carl" <Ca...@ACTIVX.com> schrieb am 10.04.2008 17:55:22:
> Hi, all,
>
> Here's an updated test case that eliminates the criteria re-use issue
> and demonstrates that the delete has the desired behavior when no join
> is used.
>
> Peace,
> --Carl
>
> package com.activx.apps.ms.quan.util.tests;
>
> import org.apache.torque.TorqueException;
> import org.apache.torque.util.CountHelper;
> import org.apache.torque.util.Criteria;
>
> import com.activx.lims.tests.TorqueTestCase;
> import com.activx.om.T1Peer;
> import com.activx.om.T2;
> import com.activx.om.T2Peer;
>
> /**
> * create table t1 (t1_id bigint(20) not null auto_increment, primary
> key (t1_id));
> * create table t2 (t2_id bigint(20) not null auto_increment, t1_id
> bigint(20) not null, primary key (t2_id));
> */
> public class JoinedDeleteTest extends TorqueTestCase {
> private static final int PRESENT_FK = 50;
> private static final int MISSING_FK = 100;
>
> /**
> * delete from t2 where t1_id = 100
> * passes
> */
> public void testDirectDelete() throws Exception {
> delete(direct(), direct());
> }
>
> private static Criteria direct() {
> return new Criteria().add(T2Peer.T1_ID, MISSING_FK);
> }
>
> /**
> * delete from t2 where t1.t1_id = 100 and t2.t1_id = t1.t1_id
> * fails because all records are deleted
> */
> public void testJoinedDelete() throws Exception {
> delete(joined(), joined());
> }
>
> private static Criteria joined() {
> return new Criteria().add(T1Peer.T1_ID,
> MISSING_FK).addJoin(T1Peer.T1_ID, T2Peer.T1_ID);
> }
>
> private void delete(Criteria c1, Criteria c2) throws Exception {
> // there are concerns that re-use of a Criteria can
> // have undesirable effects. So we need two identical
> // criteria here.
> assertEquals(c1, c2);
> assertNotSame(c1, c2);
>
> // start with an empty table
> T2Peer.doDelete(new Criteria());
> assertEquals(0, countT2s());
>
> // add one record with a foreign key of 50
> T2 t2 = new T2();
> t2.setT1Id(PRESENT_FK);
> t2.save();
>
> // see that the record made it in
> assertEquals(1, countT2s());
>
> // there are no t2 records with the MISSING_FK
> assertEquals(0, new CountHelper().count(c1));
> // try to delete only records with the MISSING_FK
> T2Peer.doDelete(c2);
>
> // expect that no records were deleted
> assertEquals(1, countT2s());
> }
>
> private int countT2s() throws TorqueException {
> return new CountHelper().count(new
> Criteria().add(T2Peer.T2_ID, 0, Criteria.GREATER_EQUAL));
> }
>
> }
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, all,
Here's an updated test case that eliminates the criteria re-use issue
and demonstrates that the delete has the desired behavior when no join
is used.
Peace,
--Carl
package com.activx.apps.ms.quan.util.tests;
import org.apache.torque.TorqueException;
import org.apache.torque.util.CountHelper;
import org.apache.torque.util.Criteria;
import com.activx.lims.tests.TorqueTestCase;
import com.activx.om.T1Peer;
import com.activx.om.T2;
import com.activx.om.T2Peer;
/**
* create table t1 (t1_id bigint(20) not null auto_increment, primary
key (t1_id));
* create table t2 (t2_id bigint(20) not null auto_increment, t1_id
bigint(20) not null, primary key (t2_id));
*/
public class JoinedDeleteTest extends TorqueTestCase {
private static final int PRESENT_FK = 50;
private static final int MISSING_FK = 100;
/**
* delete from t2 where t1_id = 100
* passes
*/
public void testDirectDelete() throws Exception {
delete(direct(), direct());
}
private static Criteria direct() {
return new Criteria().add(T2Peer.T1_ID, MISSING_FK);
}
/**
* delete from t2 where t1.t1_id = 100 and t2.t1_id = t1.t1_id
* fails because all records are deleted
*/
public void testJoinedDelete() throws Exception {
delete(joined(), joined());
}
private static Criteria joined() {
return new Criteria().add(T1Peer.T1_ID,
MISSING_FK).addJoin(T1Peer.T1_ID, T2Peer.T1_ID);
}
private void delete(Criteria c1, Criteria c2) throws Exception {
// there are concerns that re-use of a Criteria can
// have undesirable effects. So we need two identical
// criteria here.
assertEquals(c1, c2);
assertNotSame(c1, c2);
// start with an empty table
T2Peer.doDelete(new Criteria());
assertEquals(0, countT2s());
// add one record with a foreign key of 50
T2 t2 = new T2();
t2.setT1Id(PRESENT_FK);
t2.save();
// see that the record made it in
assertEquals(1, countT2s());
// there are no t2 records with the MISSING_FK
assertEquals(0, new CountHelper().count(c1));
// try to delete only records with the MISSING_FK
T2Peer.doDelete(c2);
// expect that no records were deleted
assertEquals(1, countT2s());
}
private int countT2s() throws TorqueException {
return new CountHelper().count(new
Criteria().add(T2Peer.T2_ID, 0, Criteria.GREATER_EQUAL));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by Thomas Fischer <fi...@seitenbau.net>.
> > - A criteria can be changed when a select is made. You use
> > the same criteria to count and to delete. This is typically
> > not a good idea.
>
> That comes as a surprise to me - I would not expect use of a criteria to
> change it. I tried it just now with separate but (initially) identical
> criteria and the behavior is unchanged.
I was also surprised when I discovered it, and it is not a good thing in my
opinion. But that is how it is...
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Thomas,
Thank you for the reply.
> - A criteria can be changed when a select is made. You use
> the same criteria to count and to delete. This is typically
> not a good idea.
That comes as a surprise to me - I would not expect use of a criteria to
change it. I tried it just now with separate but (initially) identical
criteria and the behavior is unchanged.
> - Unfortunately, select and delete sql is composed
> differently. Have you tried to debug the sql code which is
> used for the delete ?
No, I haven't. As I dug into it, when it got to BasePeer, it hit
library code (classes from the jar instead of source files in my
project) before creating the sql and I haven't yet brought the torque
source into my project to debug further.
Peace,
--Carl
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Thomas,
> - Unfortunately, select and delete sql is composed
> differently. Have you tried to debug the sql code which is
> used for the delete ?
I've now had a chance to debug with the torque source, and here is my
best explanation of what is going on. There doesn't appear to be any
sql code in play. Instead, all records are being deleted because inside
BasePeer.processTables(), whereClause is empty (resulting in a selection
of all records). There are two opportunities to put something into
whereClause, in the columnMaps loop. Both are passed up because
crit.containsKey(key) fails with both "t2.t2_id" and "t2.t1_id". I
believe that the only key in crit is "t1.t1_id", which is never examined
by this code, because it studies only the columns of the table of
interest, which is of course t2.
I don't understand enough of torque's big picture to suggest how to fix
this, but maybe this explanation will help.
Peace,
--Carl
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Joined Delete
Posted by Thomas Fischer <fi...@seitenbau.net>.
Two things come to my mind
- A criteria can be changed when a select is made. You use the same
criteria to count and to delete. This is typically not a good idea.
- Unfortunately, select and delete sql is composed differently. Have you
tried to debug the sql code which is used for the delete ?
Thomas
"Manaster, Carl" <Ca...@ACTIVX.com> schrieb am 09.04.2008 21:58:31:
> Hi, all,
>
> Below my signature is a test that demonstrates the problem I'm seeing.
> We're using torque 3.3. When I try to delete through a join, it is
> deleting all records from the table, instead of just the records of
> interest. It does not matter whether records in the T1 table exist or
> not. Can you please explain this behavior and suggest a workaround?
> Thanks!
>
> Peace,
> --Carl
>
> package com.activx.apps.ms.quan.util.tests;
>
> import org.apache.torque.TorqueException;
> import org.apache.torque.util.CountHelper;
> import org.apache.torque.util.Criteria;
>
> import com.activx.lims.tests.TorqueTestCase;
> import com.activx.om.T1Peer;
> import com.activx.om.T2;
> import com.activx.om.T2Peer;
>
> /**
> * create table t1 (t1_id bigint(20) not null auto_increment, primary
> key (t1_id));
> * create table t2 (t2_id bigint(20) not null auto_increment, t1_id
> bigint(20) not null, primary key (t2_id));
> */
> public class JoinedDeleteTest extends TorqueTestCase {
>
> public void testDelete() throws Exception {
> // start with an empty table
> T2Peer.doDelete(new Criteria());
> assertEquals(0, countT2s());
>
> // add one record with a foreign key of 50
> T2 t2 = new T2();
> t2.setT1Id(50);
> t2.save();
>
> // see that the record made it in
> assertEquals(1, countT2s());
>
> // delete records joined to the #100 record (of which
> none exist)
> final Criteria criteria = new
> Criteria().add(T1Peer.T1_ID, 100).addJoin(T1Peer.T1_ID, T2Peer.T1_ID);
> assertEquals(0, new CountHelper().count(criteria));
> T2Peer.doDelete(criteria);
>
> // expect that no records were deleted, but in fact all
> records were deleted.
> assertEquals(1, countT2s());
> }
>
> private int countT2s() throws TorqueException {
> return new CountHelper().count(new
> Criteria().add(T2Peer.T2_ID, 0, Criteria.GREATER_EQUAL));
> }
>
> }
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org