openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Boblitz John <John.Bobl...@BERTSCHI.com>
Subject AW: Order of SQL Statements
Date Tue, 12 Jun 2012 08:44:57 GMT
Hi Kevin,

> Q1:  Where is the duplicate key exception coming from?  Which 
> of the SQL statements is producing this exception?
Just after the set of SQL statements below (generated by commit()) the exception is thrown
On the first Axle update. 

> Q2:  The reason for the question is that Delete seems to be 
> processing on uniqueid of 413966, but I don't see any Updates 
> being processed on that key...

Axle 413966 is not updated (see below)

> Q3:  So, are you expecting Axle with uniqueid of 413966 to be 
> deleted before the updates?  Or, not at all?  Or, is this 
> uniqueid key in error and you are expecting some other Axle 
> to be deleted?

Here is the Scenario:

Start with 

Equipment #413908

Axle 1 #413966
Axle 2 #498801
Axle 3 #413965 
Axle 4 #504805

User removes Axle 1 from the Collection (on screen)

What "should" happen:

Since the reference bewteen Axle 1 and equipment is set to null - Axel 1(413966) should be
Removed from the Database.

Axle 2 (498801) should become Axle 1
Axle 3 (431965) should become Axle 2
Axle 4 (504805) should become Axle 3


IOW - I think the delete should be done before the updates - and I assumed that JPA would

Organise the SQLs based on the Unique Key Constraint accordingly, or at least that is
what I thought this passage meant:

13.2. Unique Constraints
...
"The unique constraints you define are used during table
creation to generate the proper database constraints, and may also be used at runtime to order
INSERT, UPDATE , and DELETE
statements. For example, suppose there is a unique constraint on the columns of field F. In
the same transaction, you remove an
object A and persist a new object B, both with the same F value. The JPA runtime must ensure
that the SQL deleting A is sent to
the database before the SQL inserting B to avoid a unique constraint violation."

Most likely, I am just missing some link somewhere as I can't imagine that this is a bug that
went
unnoticed ....


Thanks a lot for taking the time to look into this!


John


> -----Urspr√ľngliche Nachricht-----
> Von: Kevin Sutter [mailto:kwsutter@gmail.com] 
> Gesendet: Montag, 11. Juni 2012 22:04
> An: users@openjpa.apache.org
> Betreff: Re: Order of SQL Statements
> 
> Hi John,
> Q1:  Where is the duplicate key exception coming from?  Which 
> of the SQL statements is producing this exception?
> 
> Q2:  The reason for the question is that Delete seems to be 
> processing on uniqueid of 413966, but I don't see any Updates 
> being processed on that key...
> 
> Q3:  So, are you expecting Axle with uniqueid of 413966 to be 
> deleted before the updates?  Or, not at all?  Or, is this 
> uniqueid key in error and you are expecting some other Axle 
> to be deleted?
> 
> Just trying to understand the scenario and expected results.  Thanks.
> 
> Kevin
> 
> 
> On Mon, Jun 11, 2012 at 10:29 AM, Boblitz John 
> <John.Boblitz@bertschi.com>wrote:
> 
> > Hello,
> >
> > after correcting the issues arrising from the InverseManager, and 
> > adding annotations to remove unlinked Entities 
> (@ElementDependent) I 
> > get an error due to duplicate key.
> >
> > The goal is:
> >
> > I have up to 5 Axles and the User can delete any one of 
> them and the 
> > others should move up (Axle 3 becomes Axle 2 ...)  When I 
> delete the 
> > first Axel, I get an exception.
> >
> > I get all the axles back from the GUI and proceed to move them from 
> > the Data Object to my Entity:
> >
> > TreeSet<AxleDTO> axleDtos = new TreeSet<AxleDTO>(new 
> > Comparator<AxleDTO>() {
> >
> > @Override
> > public int compare(AxleDTO pO1, AxleDTO pO2) { if 
> (pO1.getAxleNumber() 
> > < pO2.getAxleNumber()) { return -1; } else if 
> (pO1.getAxleNumber() == 
> > pO2.getAxleNumber()) { return 0; } else { return 1; } } });
> >
> > axleDtos.addAll(pData.getAxles());
> > if (axleDtos != null && !axleDtos.isEmpty()) { short i = 1; 
> Set<Axle> 
> > axles = new HashSet<Axle>(); AxleMgr axleMgr = new 
> AxleMgr(mPm); for 
> > (AxleDTO axleDto : axleDtos) { Axle axle = new Axle(); 
> > axleMgr.toEntity(axleDto, axle); if (!axle.isActive()) { 
> > axle.setEquipmentId(null); } else { axle.setAxleNumber(i++); 
> > axle.setEquipmentId(pEntity); axles.add(axle); } } 
> > pEntity.setAxles(axles); }
> >
> >
> > @Entity
> > @Table(schema = "galaxy11", name = "Equipment") public 
> class Equipment 
> > extends BaseEntity {
> >
> > @JsonManagedReference
> > @OneToMany(cascade = CascadeType.ALL, mappedBy = 
> "equipmentId", fetch 
> > =
> > FetchType.EAGER)
> > @ElementDependent
> > private Set<Axle> axles = new HashSet<Axle>();
> >
> > @Entity
> > @Table(schema = "galaxy11", name = "Axle", uniqueConstraints= 
> > @UniqueConstraint(name="uq_Axle_equipmentId_axleNumber", 
> columnNames = 
> > { "equipmentId", "axleNumber" })
> > )
> > public class Axle extends BaseEntity {
> >
> > @Basic
> > @Column(columnDefinition = "int2")
> > private short axleNumber;
> >
> > @JsonBackReference
> > @ManyToOne(fetch = FetchType.EAGER)
> > @JoinColumn(name = "equipmentId", columnDefinition = 
> "int8", nullable 
> > =
> > false)
> > private Equipment equipmentId;
> >
> >
> >
> > It all seems to work as I expect, except for the order of 
> the statements!
> > The trace shows that the delete of the Axle is done AFTER 
> the updates:
> >
> >
> >
> > UPDATE galaxy11.Equipment
> > SET modified = ?, versionid = ?, length = ?, height = ?, 
> wheelbase = 
> > ?, tareweight = ?, grossweight = ?
> > WHERE uniqueid = ? AND versionid = ?
> > [params=(Timestamp) 2012-06-11 13:47:18.21, (int) 33, (BigDecimal) 
> > 6.2,
> > (BigDecimal) 3.1, (BigDecimal) 3.8, (BigDecimal) 6.7, 
> (BigDecimal) 18,
> > (long) 413908, (int) 32]
> > - <t 656806424, conn 972792495> [27 ms] spent
> >
> > - <t 656806424, conn 972792495> executing prepstmnt 
> 684003742 UPDATE 
> > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, 
> > axlenumber = ?
> > WHERE uniqueid = ? AND versionid = ?
> > [params=(Timestamp) 2012-06-11 13:47:18.23, (int) 15, (long) 413908,
> > (short) 2, (long) 413965, (int) 14]
> > - <t 656806424, conn 972792495> [23 ms] spent
> >
> > - <t 656806424, conn 972792495> executing prepstmnt 10578812 UPDATE 
> > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, 
> > axlenumber = ?
> > WHERE uniqueid = ? AND versionid = ?
> > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 2, (long) 413908,
> > (short) 3, (long) 504805, (int) 1]
> > - <t 656806424, conn 972792495> [21 ms] spent
> >
> > - <t 656806424, conn 972792495> executing prepstmnt 
> 1704434230 UPDATE 
> > galaxy11.Axle SET modified = ?, versionid = ?, equipmentid = ?, 
> > axlenumber = ?
> > WHERE uniqueid = ? AND versionid = ?
> > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 13, (long) 413908,
> > (short) 1, (long) 498801, (int) 12]
> > - <t 656806424, conn 972792495> [22 ms] spent
> >
> > - <t 656806424, conn 972792495> executing prepstmnt 
> 1141736277 UPDATE 
> > galaxy11.Motor SET modified = ?, versionid = ?
> > WHERE uniqueid = ? AND versionid = ?
> > [params=(Timestamp) 2012-06-11 13:47:18.24, (int) 28, 
> (long) 414058, 
> > (int) 27]
> > - <t 656806424, conn 972792495> [21 ms] spent
> >
> > - <t 656806424, conn 972792495> executing prepstmnt 
> 1395221594 DELETE 
> > FROM galaxy11.Axle WHERE uniqueid = ? AND versionid = ?
> > [params=(long) 413966, (int) 14]
> >
> >
> > I am certain I am missing something quite minor, but after running 
> > interactive debugs between The GUI and the persistence for 
> the last 48 
> > hrs, I think I am missing the obvious.
> >
> > Anyone out there see where I am going astray?
> >
> >
> > Thanks,
> >
> > John
> >
> >
> >
> 
Mime
View raw message