openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "John Boblitz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (OPENJPA-2211) Ordering of SQL Statements when using @UniqueConstraints
Date Wed, 13 Jun 2012 06:54:43 GMT
John Boblitz created OPENJPA-2211:
-------------------------------------

             Summary: Ordering of SQL Statements when using @UniqueConstraints
                 Key: OPENJPA-2211
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2211
             Project: OpenJPA
          Issue Type: Bug
    Affects Versions: 2.2.0
            Reporter: John Boblitz


Scenario:
In my Model I have an Entity "Equipment" which contains a bi-directional @OneToMany relationship
with "Axle".  All entities have a primary key (uniqueId) which is contained in a MappedSuperClass
"BaseEntity".

Further, all Entities have a secondary unique key based on the business requirements.  In
the case of Axles, this is the equipmentId and the axleNumber.  This is annotated in Axle
through @UniqueConstraint.

Axle is also defined as @ElementDependant in Equipment so a deletion of an Equipment or the
delinking of an Axle from the Equipment will result in the deletion of the Axle.  This works
as intended.

However, it is also necessary that any Axles defined after the deleted Axle (i.e. with a higher
axelNumber) should "move up" the chain.

Here is an illustration:

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

However, the removal Axle 1 is occuring AFTER the statements which reorganize the other Axles.
 This causes a duplicate key error as Axle 2 tries to become Axle 1 before Axle 1 is removed
from the database.

according to the Manual, this should not occur:
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."

The following are the relevant portions of the code:

ENTITIES

@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;


THE DELETION CODE

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); 
}

** pData is the Data passed from the GUI

TRACE OF STATMENTS

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]








--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message