You might have better chances in dev ML. Not guaranted, as most of OFBiz developers read also this ML, but worth the try...
Of course, better than whish lists are patches to review/test or detailled specifications. Recently, Adrian has proved how to achieve the 1st and Jacopo the 2d, thanks to them BTW ! Jacques PS : don't get me wrong, this does not mean that we shall not discuss your propositions, just that you will maybe get more returns ;o) De : "Jonathon -- Improov" <[EMAIL PROTECTED]> > None of the responses pertain to this thread? :( > > Help? Anyone? > > Jonathon > > Jonathon -- Improov wrote: > > Ah sheesh, too late to correct my own thoughts. Responses came in. > > Anyway, notes to self here. > > > > - Foreign keys must point to UNIQUE or PRIMARY indexes > > > > I don't see a point in having a relation where the referencing entity > > has a record that points ambiguously to more than 1 record in the > > referenced entity. > > > > - Foreign keys must point to *full* indexes, not partial > > > > Consider how a ON DELETE CASCADE can cause a child record to be > > erroneously deleted. Why? The conditions for cascading the delete is not > > specific enough. > > > > That brings me down to only 2 wishlist items. > > > > - Have a "ON DELETE/UPDATE" attribute for <relation>s (optional) > > > > - Build an application-level foreign key constraint enforcement in > > Entity Engine. > > > > For eg, PartyContactMechPurpose could have a type "one-app" relation to > > PartyContactMech's [partyId, contactMechId, fromDate]. The "one-app" > > will mean a one-to-one foreign key constraint enforced by the application. > > > > No real foreign key will be created in database. > > > > What do you think? > > > > And now, to read the many responses... > > > > Jonathon > > > > Jonathon -- Improov wrote: > >> More thoughts aloud. > >> > >> > Of course, that would mean we cannot easily change the "fromDate" > >> field in > >> > PartyContactMech (unless we do EECA). > >> > >> Correction. With foreign key constraint in place, it is not possible > >> to use EECA to do a pseudo "ON UPDATE CASCADE". I can't find any "turn > >> off foreign key checks" switches in OFBiz, nor any standard way to do > >> so in various RDBMSs. > >> > >> I was wondering why we need to have foreign keys *exactly match* > >> referenced primary keys. In MySQL, it is possible to have a foreign > >> key reference part of an index (top part, not tail end). This way, we > >> can have a foreign key "[partyId, contactMechId]" from > >> PartyContactMechPurpose to PartyContactMech. We can do > >> application-level (EECA) checks for fromDate. Currently, OFBiz > >> entities don't have foreign keys at all when it encounters fromDate > >> entities! > >> > >> Also, why don't we allow foreign keys to reference indexes on > >> referenced entities? Restricting foreign keys to referencing primary > >> keys only seems a bit inflexible. > >> > >> Speaking of application-level (EECA) checks for fromDate, is it a good > >> idea to have some automatic checks at the Entity Engine level? Much > >> like how fields "lastUpdatedStamp", "lastUpdatedTxStamp", etc, are > >> handled. I know it's slower than using "ON UPDATE CASCADE". But not > >> all RDBMSs have that, I think. We can always have a switch to toggle > >> on/off the application-level checks for fromDate fields. > >> > >> Speaking of "ON UPDATE CASCADE", should we allow an attribute in > >> <relation> for this? Do we also want to auto-detect the RDBMS being > >> used, and scream a warning if we encounter one that doesn't support > >> "ON UPDATE CASCADE"? > >> > >> Am I missing a whole chunk of the Entity Engine? Or is this a commonly > >> wished wishlist? > >> > >> Jonathon > >> > >> > >> Jonathon -- Improov wrote: > >>> Ah, wait. Note one more thing. > >>> > >>> In entity PartyContactMechPurpose, there is supposed to be a type > >>> "one" relation to PartyContactMech. Why is it missing? We could have > >>> a field like "partyContactMechFromDate", so it doesn't clash with > >>> "fromDate". Same for "thruDate". Of course, that would mean we cannot > >>> easily change the "fromDate" field in PartyContactMech (unless we do > >>> EECA). > >>> > >>> As it is now, it is possible to actually get PartyContactMechPurpose > >>> to point to a non-existent PartyContactMech. Just mix up the partyId > >>> and contactMechId such that no such combination exists. > >>> > >>> Advice? Or should we live without foreign key checks in such cases? > >>> > >>> Jonathon > >>> > >>> Jonathon -- Improov wrote: > >>>> I found this from David Jones: > >>>> > Foreign keys are done for type "one" relationships, not type > >>>> many. A type > >>>> > "many" relationship is usually just the reverse direction of a > >>>> type "one" > >>>> > relationship so the FK covers both. > >>>> > > >>>> > What would it mean to have a foreign key on a type "many" > >>>> relationship?" > >>>> > >>>> Then a corresponding "one" relation will supply the foreign key > >>>> constraint? > >>>> > >>>> What about a many-to-many relation? Look at entity PartyAttribute > >>>> and PartyTypeAttr for example. Is that a many-to-many? Looks odd, > >>>> though. A many-to-many usually requires a separate "match-make" table. > >>>> > >>>> Is it correct to say that OFBiz does not do foreign key constraints > >>>> with type "many" relations? > >>>> > >>>> Do we have to insert an additional type "one" relation on field > >>>> "attrName" in order to get the foreign key constraints checks? But > >>>> can a "one" relation be created without specifying the full primary > >>>> key? > >>>> > >>>> Looks like the type "many" relation is merely a convenient means to > >>>> do a query like "where attrName = whatever", so we can do a simple > >>>> getRelated(). > >>>> > >>>> Jonathon > >>>> > >>>> > >>> > >>> > >> > >> > > > > >
