problem with foreign key : ensure order of Sql statements

[imho <la...@fmsb.be>]

Hi all,
I'm working with WASCE and OpenJpa 1.2.2, and I face this strange problem
from a while.
I've searched and tried several solutions, but with no complete success.

The problem is : 
I have different entities with foreign key, let's call them A with fk B
(A->B), and C with fk D (C->D)

What I'd like to be able is for example : 
- first create A->null, and then do an update of A with A->B
- first create A->B, and then do an update of a with A->B', resulting in a
delete of B
- first create A->B, and then do an update of a with A->null, resulting in a
delete of B
and the same with C and D.

For the A->B relation, everything is working fine, the Sql statements order
is correct (INSERT, UPDATE, DELETE) when I do an update of A after modifying
B. I have managed to have the deletion done with @Dependent annotations.

But for the C->D relation, the statements order is INSERT, DELETE, and
UPDATE, which leads to a foreign key violation.

Also, in the A->C relation I have a problem (the C class seems thus the
problem) : 
- first I create a A->null entity, then do an update with A->C.
The statements look fine, but the last Update of A with the new cId is not
done on the table. 

Please find here the properties I have setted in persistence.xml : 

	 <property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(ForeignKeys=true)" /> 
	<property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)" />	
	<property name="openjpa.ConnectionFactoryProperties"
value="PrettyPrint=true, PrettyPrintLineLength=72" />
	<property name="openjpa.ConnectionFactoryMode" value="managed" />
	<property name="openjpa.jdbc.MappingDefaults"
value="ForeignKeyDeleteAction=null, JoinForeignKeyDeleteAction=null"/>
<property name="openjpa.Optimistic" value="true" />
<property name="openjpa.RuntimeUnenhancedClasses" value="supported" />
<property name="openjpa.Log" value="SQL=TRACE" />
<property name="show_sql" value="true" />
<property name="openjpa.Sequence" value="table(Table=IDENTIFIERS)" />
<property name="openjpa.jdbc.QuerySQLCache" value="false" />

I tried <property name="openjpa.jdbc.UpdateManager" value="operation-order"
/> , but as I do only one merge (all the statements are generated from
this), that doesn't fix the problem. 
I also tried bi-directionnal oneToOne relations, but it stay the same.
The ids are all auto-generated.

And here are the annotations : 

-----
@Entity
@Table(name = "A")
public class A implements Serializable
{
...
@Id
  @TableGenerator(name = "doc_gen", table = "IDENTIFIERS", pkColumnName =
"ID", pkColumnValue = "ATYP", valueColumnName = "SEQUENCE_VALUE",
initialValue = 1, allocationSize = 10)
  @GeneratedValue(strategy = GenerationType.TABLE, generator = "doc_gen")
  @Column(name = "AID", unique = true, nullable = false, length = 12)
  private String AId;

  @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, optional =
false)
  @JoinColumn(name = "BID", nullable = true, columnDefinition =
"VARCHAR(12)")
  @ForeignKey(updateAction = ForeignKeyAction.CASCADE)
  @Dependent
  private B b;

// not dependent, because we need to do others things to remove this
relation
  @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, optional =
true)
  @JoinColumn(name = "CID", nullable = true, columnDefinition =
"VARCHAR(12)")
  @ForeignKey(updateAction = ForeignKeyAction.CASCADE)
  private C c;
...
---------
@Entity
@Table(name = "B")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "DTYPE", length = 20, discriminatorType =
DiscriminatorType.STRING)
public class B implements Serializable
{

  @Id
  @TableGenerator(name = "doctype_gen", table = "IDENTIFIERS", pkColumnName
= "ID", pkColumnValue = "BTYP", valueColumnName = "SEQUENCE_VALUE",
initialValue = 1, allocationSize = 10)
  @GeneratedValue(strategy = GenerationType.TABLE, generator =
"doctype_gen")
  @Column(name = "BID", unique = true, nullable = false, length = 12)
  private String bId;

---------
@Entity
@Table(name = "C")
public class C implements Serializable
{

  @Id
  @TableGenerator(name = "envelope_gen", table = "IDENTIFIERS", pkColumnName
= "ID", pkColumnValue = "CTYP", valueColumnName = "SEQUENCE_VALUE",
initialValue = 1, allocationSize = 10)
  @GeneratedValue(strategy = GenerationType.TABLE, generator =
"envelope_gen")
  @Column(name = "CID", unique = true, nullable = false, length = 12)
  private String cId;

  @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, optional =
false)
  @JoinColumn(name = "DID", nullable = true, columnDefinition =
"VARCHAR(12)")
  @ForeignKey(updateAction = ForeignKeyAction.CASCADE)
  @Dependent
  private D d;

  @OneToOne(fetch = FetchType.EAGER, cascade =
  { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH }, optional
= false)
  @JoinColumn(name = "AID", nullable = true, columnDefinition =
"VARCHAR(12)")
  @ForeignKey(updateAction = ForeignKeyAction.CASCADE)
  private A a;
...
----

@Entity
@Table(name = "D")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "DTYPE", length = 20, discriminatorType =
DiscriminatorType.STRING)
public class JpaRedactionEnvelopeType implements Serializable
{

  private static final long serialVersionUID = 1L;

  @Id
  @TableGenerator(name = "envelopetype_gen", table = "IDENTIFIERS",
pkColumnName = "ID", pkColumnValue = "DTYP", valueColumnName =
"SEQUENCE_VALUE", initialValue = 1, allocationSize = 10)
  @GeneratedValue(strategy = GenerationType.TABLE, generator =
"envelopetype_gen")
  // nullable true because of bug jpa ?
  @Column(name = "DID", unique = true, nullable = false, length = 12)
  private String dId;
...
-------------

So my questions are : 
- How to be sure the statements are always in INSERT, UPDATE, DELETE order ?
(I saw a patch on Jira but it seems it's included in my version?)
- What's wrong with my C entity : because the A update statement is not
done, and the C->D relation can't be updated for a fk violation due to
statement order.

Thank you,

imho


-- 
View this message in context: http://openjpa.208410.n2.nabble.com/problem-with-foreign-key-ensure-order-of-Sql-statements-tp5347045p5347045.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: problem with foreign key : ensure order of Sql statements

[imho <si...@gmail.com>]

Hi all,
I allow myself to respond on my own thread, as I have a lead for one of the
problems : 

"
Also, in the A->C relation I have a problem (the C class seems thus the
problem) :
- first I create a A->null entity, then do an update with A->C.
The statements look fine, but the last Update of A with the new cId is not
done on the table. 
"
When I look at the sql statements, the problem is with versioning.
I dont know why, but in this case I have : 
- Update A to increment the version number (!!!)
- Insert C
- Update A with the C id but with the older version in "where" statement

So this is the explanation why the third statement (the update) does
nothing.

When I do the same with the relation A->B, everything works fine.
The order is the following : 
- Insert of B
- Update of A to set the B id and the version

Why is the version updated separately in the first case ?

Thank you!

imho

-- 
View this message in context: http://openjpa.208410.n2.nabble.com/problem-with-foreign-key-ensure-order-of-Sql-statements-tp5347045p5354056.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.