From dev-return-4354-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org Fri Jun 01 00:39:37 2007 Return-Path: Delivered-To: apmail-openjpa-dev-archive@www.apache.org Received: (qmail 61650 invoked from network); 1 Jun 2007 00:39:37 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 1 Jun 2007 00:39:37 -0000 Received: (qmail 62999 invoked by uid 500); 1 Jun 2007 00:39:41 -0000 Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org Received: (qmail 62845 invoked by uid 500); 1 Jun 2007 00:39:40 -0000 Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@openjpa.apache.org Delivered-To: mailing list dev@openjpa.apache.org Received: (qmail 62827 invoked by uid 500); 1 Jun 2007 00:39:40 -0000 Delivered-To: apmail-incubator-open-jpa-dev@incubator.apache.org Received: (qmail 62823 invoked by uid 99); 1 Jun 2007 00:39:40 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 31 May 2007 17:39:40 -0700 X-ASF-Spam-Status: No, hits=-100.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO brutus.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 31 May 2007 17:39:36 -0700 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id B25F1714183 for ; Thu, 31 May 2007 17:39:15 -0700 (PDT) Message-ID: <22827332.1180658355686.JavaMail.jira@brutus> Date: Thu, 31 May 2007 17:39:15 -0700 (PDT) From: "Markus Fuchs (JIRA)" To: open-jpa-dev@incubator.apache.org Subject: [jira] Commented: (OPENJPA-235) SQL reordering to avoid non-nullable foreign key constraint violations In-Reply-To: <16358846.1178303115756.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12500542 ] Markus Fuchs commented on OPENJPA-235: -------------------------------------- Hi Reece, I ran the JPA TCK with your changes, and all tests passed! Congratulations. But I'd need some more time to really understand your changes. One question upfront: What does "dereference" mean? Does it mean changing the reference to a persistence capable object? Hi Reece & Gokhan: Are non-nullable foreign keys the only reason for constraint dependencies? One update can be dependent on another update in case of a OneToOne relationship, as OneToOne relationships are usually enforced by unique keys in the database. Please also consider the following situation: a -> b; c -> d; tx.begin(); c -> b; a -> null; tx.commit(); B/c of the unique key, the foreign key in the row corresponding to "a" has to be nullified, *before* the foreign key in row "c" can be updated. Does this dependency also need to be addressed? Is above dependency different from the dependencies imposed by non-nullable foreign keys? I.e. can non-nullable foreign keys cause dependencies, that can't be addressed by the user order of operations? Are non-nullable foreign keys different in this regard? Thanks and sorry for my late reply! -- markus. > SQL reordering to avoid non-nullable foreign key constraint violations > ---------------------------------------------------------------------- > > Key: OPENJPA-235 > URL: https://issues.apache.org/jira/browse/OPENJPA-235 > Project: OpenJPA > Issue Type: Improvement > Components: kernel > Reporter: Reece Garrett > Assignee: Patrick Linskey > Fix For: 0.9.8 > > Attachments: merge-detached.patch, merge-multigen-collection-testcase.zip, openjpa-235-test.jar, openjpa-235-test1.jar, sqlreorder.patch, sqlReorder2.patch > > > OpenJPA does not do any SQL statement re-ordering in order to resolve foreign key constraints. Instead, objects are always inserted in the order in which the user persists the instances. When you persist in an order that would violate foreign key constraints, OpenJPA attempts to insert null and then update the foreign key value in a separate statement. If you use non-nullable constraints, though, you must persist your objects in the correct order. > This improvement re-orders SQL statements as follows: > 1. First, all insert statements execute. Inserts which have foreign keys with non-nullable constraints execute AFTER the foreign keys which they depend on have been inserted since no deferred update is possible. > 2. Next, all update statements execute. No reordering is necessary. > 3. Finally, all delete statements execute. Like inserts, deletes execute in an order which does not violate non-nullable foreign key constraints. > If a circular foreign key reference is found during the re-ordering process then re-ordering halts and the remaining unordered statements are left as is. There is nothing that can be done about the circular reference (other than fixing the schema) and the resulting SQL statements will not succeed. > The net effect is that users do not need to worry about the persistence order of their objects regardless of non-nullable foreign key constraints. The only class modified was org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager. I have included a patch which includes my modifications to OperationOrderUpdateManager and test cases. The test cases I have provided fail on the current trunk but pass with my modifications. I have also verified that I did not break anything by using maven to run all test cases with my modifications in place. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.