From jdo-dev-return-4699-apmail-db-jdo-dev-archive=www.apache.org@db.apache.org Tue Jul 18 16:43:18 2006 Return-Path: Delivered-To: apmail-db-jdo-dev-archive@www.apache.org Received: (qmail 23940 invoked from network); 18 Jul 2006 16:43:18 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 18 Jul 2006 16:43:18 -0000 Received: (qmail 92982 invoked by uid 500); 18 Jul 2006 16:43:17 -0000 Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: jdo-dev@db.apache.org Delivered-To: mailing list jdo-dev@db.apache.org Received: (qmail 92971 invoked by uid 99); 18 Jul 2006 16:43:17 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 18 Jul 2006 09:43:17 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [212.224.30.66] (HELO service-01.spree.de) (212.224.30.66) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 18 Jul 2006 09:43:16 -0700 Received: from [172.16.1.19] (rio.spree.de [172.16.1.19]) (authenticated bits=0) by service-01.spree.de (8.13.4/8.13.4/Debian-3) with ESMTP id k6IGgsnu009504; Tue, 18 Jul 2006 18:42:54 +0200 Message-ID: <44BD0F8E.6040209@spree.de> Date: Tue, 18 Jul 2006 18:42:54 +0200 From: Michael Watzek Organization: Tech@Spree GmbH User-Agent: Thunderbird 1.5.0.4 (Windows/20060516) MIME-Version: 1.0 To: jdo-dev@db.apache.org CC: JDO Expert Group Subject: Re: Minutes: JDO TCK Conference Call Friday, July 14 9 am PST References: <4463CFBC.2050403@sun.com> <0FDF8D06-F493-4359-9576-F3CC1175E7E8@SUN.com> <447F845F.3060204@sun.com> <44888D6A.9000504@sun.com> <4491FE74.1030207@sun.com> <449B0D8E.8010701@sun.com> <449B10F7.30904@sun.com> <44A451ED.2070100@sun.com> <44B6E741.4070908@sun.com> <56F18307-2AD9-411F-9361-C4F73F73AEE4@SUN.com> <44BBBA20.80104@artnology.com> <96AF4949-DFD5-42BB-A672-CBB446AB2F6E@SUN.com> <44BBC89B.5060909@artnology.com> <4A9A1917-0909-4017-B41E-689F3E47B36D@SUN.com> <44BCBB8A.3000203@artnology.com> In-Reply-To: <44BCBB8A.3000203@artnology.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Hi Craig, Joerg, I agree that FK constraints are an important and powerful feature of relational databases. They provide for referential integrity, and, as a side effect, prevent people/applications from deleting rows unless FKs are nullified first. My impression is that below you are talking of different delete semantics in the object domain: 1) Deleting an object means that all references to that object are nullify implicitly. 2) Deleting an object is only successful if there are no references to that object. I think it would make sense to support both semantics in JDO. Regards, Michael Jörg von Frantzius wrote: > Hi Craig, > > Craig L Russell schrieb: >> Hi Jörg, >> >> On Jul 17, 2006, at 10:27 AM, Jörg von Frantzius wrote: >> >>> Craig L Russell schrieb: >>>>> If that's the case, then I wonder why a user would want to have FK >>>>> constraints in his schema at all? They wouldn't be of much use then. >>>> If only using JDO, I agree. What matters is the consistency of the >>>> object model, which is managed by the JDO implementation without >>>> help from the database. But seldom is JDO the only access path to >>>> the database. >>> To me this means losing an important data security feature when using >>> JDO over other access paths to the DB. FK constraints can prevent you >>> from inadvertently deleting data, and by silently removing any >>> dangling references on the object level (and so circumventing any FK >>> constraints) you are entirely losing this kind of "safety belt"! JDO >>> would never tell you about these dangling references, but instead >>> would silently go and delete that information. >> I'm not sure I understand your concern. Is it ok for the application >> to explicitly nullify references to instances that it deletes? It's >> not clear to me how a nullable database constraint improves the >> reliability of your user's code. > Yes exactly, I find it good if the application programmer must > explicitly nullify any references before deleting. That way he'll know > what relationships exactly he is going to remove the object from. If it > happens transparently and without notice, he might not realize the > consequences. I find it adequate that code for deleting objects must be > written with care. > > So in my opinion, and from my experience, FK constraints help to prevent > programming mistakes from creating serious data loss. >> On the other hand, if it is a requirement of the object model that >> there must be a non-null reference to an instance, then would a >> not-null foreign key satisfy the requirement? In this case, JPOX's >> automatic nullification of the reference would cause a (null) >> constraint violation. Or JPOX would throw an exception if it knew >> about the not-null constraint. Or defer the delete until commit, by >> which time the other side would have to have been set to a valid >> not-deleted instance. And the application would have to be careful to >> set the other side of the relationship. > That's interesting, but I wasn't thinking about FKs with an additional > NOT-NULL constraint here. I was thinking of simply not allowing the > deletion to happen at all, as it turned out to our own surprise, please > see below. >> As you know, there are no TCK tests for these cases. I'd like to >> formalize the specification and add some tests once we all agree on >> the semantics. >>> As I wrote in an earlier mail, we had a case here where we were >>> really lucky that the FK constraint would fire upon deletion, because >>> we didn't think of all the relationships that an object would be >>> removed from, and for which we really didn't want to lose the >>> information. That was for users that are logged as creators and >>> modifiers of other objects: if you can delete a user, you'll lose the >>> information about who created or edited an object, something that >>> would have gone lost silently forever if it wasn't for the FK >>> constraints firing. >> So is it the case that explicitly nullifying the relationship is >> illegal for the user? I'm trying to picture the security domain model >> where a user is authorized to delete from a table but is not >> authorized to set the column of a related table to null. How is this >> supposed to work? > What I meant to say is that the "delete user" functionality of the > application turned out to not having been thought out well enough. It > was only when an end-user clicked the "delete"-button on another user > that had already created or modified other objects, that we realized we > don't want to allow a deletion in this case at all. And we realized it > only because luckily an SQLException was thrown due to the constraint > violation. > > In this case, it would have been a serious mistake if the programmer had > nullified the relationships explicitly, but in doing so he is forced to > think twice about it, hopefully, and you can't prevent people from > creating damage on purpose ;) >> >>>>> I may also add that, as far as I know, JPOX nulls out only for >>>>> one-one bidirectional, not for one-many, so there would be some >>>>> inconsistency of behaviour here. >>>> Interesting point, but without another level of detail, it's hard to >>>> evaluate this statement. >>> If you look at the code in DeleteRequest, you'll see that in >>> execute() it'll null out FKs for any fields obained using >>> getFildsWithRelatedObjects(). The latter's javadoc says >>> >>> /** >>> * All fields that have related objects and where we use the >>> related object to >>> * maintain a relationship. These are used where we have 1-1 >>> bidirectional >>> * relationships with a single FK (in the other object). >>> * @return The fields with related objects >>> */ >>> public FieldMetaData[] getFieldsWithRelatedObjects() >>> >>> That's the case for 1-1 relationships with "mapped-by" on the side >>> where the object is deleted. From what I saw in my tests, that's >>> where the nulling out happens, and it doesn't happen for one-many >>> relationships with "mapped-by", so you'll see FK constraints >>> violations there. >> >> For one-many relationships there is no additional foreign key. Take >> the case of deleting an Employee for which there is a >> Department-Employee relationship. There is no explicit database action >> needed when deleting the Employee because there is no explicit >> reference from the DEPARTMENT table to the key values in the EMPLOYEE >> table. The primary key in EMPLOYEE defines the relationship. > What I was thinking of would in this example be a deletion of a > Department that has Employees. I had understood you that you want to > transparently have the Employees be removed from the Department when it > is deleted. Currently JPOX doesn't do that, and an FK violation occurs. > > Let's take this a step further. Let's assume that Department has an > "employees" Collection field annotated with "mapped-by". That means we > have two Java object references that are represented by one FK on the > database level. From an object-oriented perspective, I shouldn't need to > know that there is only a single FK, and where it is exactly. However, I > can delete an Employee without further action, while I cannot delete a > Department without further action. There is an asymmetry here that is > rooted in the relational nature of the database, and, for the sake of > transparency, it should be hidden. For the sake of data security, I'd > prefer to hide the asymmetry by throwing exceptions in both cases > > So I'd even say that it should not be possible to delete an Employee > before removing it from its Department's employees collection, because > there won't be any FK violation notifying of the dangling object > reference. However, if you find that a little over the top, I won't > insist too long as our code generator already causes exceptions to be > thrown in these cases... >> Regards, >> >> Craig >> >>>> >>>> Regards, >>>> >>>> Craig >>>>> >>>>> Regards, >>>>> Jörg >>>>> >>>>> Craig L Russell schrieb: >>>>>> >>>>>> 2. Deletion of objects when foreign-key is present (JDO-392) (any >>>>>> and all) It seems that there are two different issues: managing >>>>>> the memory model and managing the database. Craig opines that the >>>>>> general case of consistency is already covered in the chapter on >>>>>> mapping, requiring that the object model be consistent after a >>>>>> flush. Object model consistency would disallow a reference to a >>>>>> deleted object, so the natural behavior would be to nullify the >>>>>> reference to the deleted object. For to-many relationships mapped >>>>>> to a foreign key on the other side, the consistency rule would >>>>>> delete the reference from the collection on the one- side of the >>>>>> relationship. So it seems that the JPOX behavior as originally >>>>>> reported is consistent and we might simply document it in the >>>>>> specification. >>>>> Why does object model consistency disallow a reference to a deleted >>>>> object? >>>>> >>>> >>>> Craig Russell >>>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo >>>> 408 276-5638 mailto:Craig.Russell@sun.com >>>> P.S. A good JDO? O, Gasp! >>>> >>> >>> >> >> Craig Russell >> >> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo >> >> 408 276-5638 mailto:Craig.Russell@sun.com >> >> P.S. A good JDO? O, Gasp! >> >> > -- ------------------------------------------------------------------- Michael Watzek Tech@Spree Engineering GmbH mailto:mwa.tech@spree.de Buelowstr. 66 Tel.: ++49/30/235 520 36 10783 Berlin - Germany Fax.: ++49/30/217 520 12 http://www.spree.de/ -------------------------------------------------------------------