Re: Entity engine, "many" relations, foreign keys

[jonwimp]

Skip,

Read the DTDs. See ${component:entity}/dtd/entitymodel.xsd .

For an <entity>, there are <field>, <prim-key>, <relation>, <index>.

Jonathon

skip@thedevers wrote:

> Gads Adrian.  I never knew of the existence of this tag.  Thanks.  It is
> perfect.  Someone needs to document it in the entity engine guide or at
> least somewhere prominent.  I just did a search for this tag and found
> dozens of uses, but somehow, I missed it even though I looked at
> OrderHeader's definition a gazillion times.
>
> Are there any other cool undocumented DB features?
>
> Skip
>
> -----Original Message-----
> From: Adrian Crum [mailto:[hidden email]]
> Sent: Tuesday, December 04, 2007 11:47 AM
> To: [hidden email]
> Subject: Re: Entity engine, "many" relations, foreign keys
>
>
> Why won't the <index> element work?
>
> skip@thedevers wrote:
>> Ya BJ, seen that.  What I want to do is create an index that is simply
> used
>> for speed lookups.  I do this now in postgres after install has been done.
>> It would just be nice to be able to specify it in the entitydef file.
> But,
>> it aint a big deal.  Easily done manually in postgres.
>>
>> Skip
>>
>> -----Original Message-----
>> From: BJ Freeman [mailto:[hidden email]]
>> Sent: Tuesday, December 04, 2007 11:09 AM
>> To: [hidden email]
>> Subject: Re: Entity engine, "many" relations, foreign keys
>>
>>
>> have you looked at this for tools
>> https://localhost:8443/webtools/control/view/checkdb
>>
>> skip@thedevers sent the following on 12/4/2007 11:01 AM:
>>
>>> It would be nice to have the foreign key checks as well as ways to
>> creating
>>
>>> indexes on non-key fields.  There is a great performance increase having
>> an
>>
>>> index in some tables.  Now I have to do in manually inside of the DB which
>>> is fine.
>>>
>>> Skip
>>>
>>> -----Original Message-----
>>> From: Jonathon -- Improov [mailto:[hidden email]]
>>> Sent: Tuesday, December 04, 2007 3:08 AM
>>> To: [hidden email]
>>> Subject: Re: Entity engine, "many" relations, foreign keys
>>>
>>>
>>> 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
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>
>
>

[jonwimp]

Skip,

 > Ah well, "undocumented" is probably not the right word given that we all
 > have the source.  Perhaps "hidden" might be better?  But no, that would
 > imply the intent to hide something which is surely not the case.  Perhaps
 > "unpublished"?

Actually, for open source and free software, "as is" is the key phrase. We give it to you "as is". :)

 > This is all tongue-in-cheek David.  My point is that here is a really cool
 > feature that went un-noticed by me after looking through all the
 > documentation

And that's why some people are paying folks to write in-house documentation.

 > It is a simple matter to request that submitters provide at least minimal
 > documentation for new features that the committers can then include in the
 > base documentation, especially in the entity and widget and other "engines".

Then we'd get less submitters. I don't even write JIRA issues! *apologies*.

 > There is no value in any software if the implementer has to spend countless
 > hours experimenting and digging through source code to implement some new
 > feature, fun as such an exercise may be.

Agreed totally. And that's why there is business in training folks to use OFBiz. I've gotten an
average of 3 week-long engagements per month. :) Meet all kinds of folks, though, heh. I *am*
funny. :P (playful reference to David's Willy Wonka).

 > And who sez that "Documented features are somewhat the exception, not the
 > norm." in open source software.  I can point you to scads of open source
 > software with excellent documention, and some of it used by Ofbiz itself
 > (ftl and tomcat come instantly to mind).

Tomcat documentation was incomplete before. It's always work-in-progress.

FTL is smaller than OFBiz, in a way.

Common ground between those: they are specialized packages that deal with a widely needed layer --
the webserver and the templating (MVC).

Those projects will naturally get more use than OFBiz. It's like a toothbrush gets more market
than a laptop. More ROI in documenting those completely.

And yes, some authors make a living documenting popular tools like Tomcat and Joomla and
osCommerce and such. Reverse-engineers make a living doing hands-on training soon after the
technology is published.

Maybe it's not entirely true that documentation is the exception and not the norm. I'd say it's
all down to market supply-and-demand.

 > Also, have an overview look at the most successful open soure projects.  All
 > (that I know of) are very well documented.  The success of any open source
 > project is determined by its committers and the quality of their code.  The
 > more committers, the more successful the project becomes.  You get more
 > committers with good documentation.

Chicken and egg issue that David and I fought over in epic battles, since my joining OFBiz
community in Jan 2007. :)

But like David, I need to make a living too. I once suggested we do an "OFBiz Engineer's Guide".
Sadly, my altruism often gets bought over by the highest bidder.

Yeah, you did hit a sore spot. If I weren't working with highest bidders right now, I'd be
shooting back at you sorely. :P And yes, I must humbly admit that I do lots of codes that didn't
make it back into OFBiz. Maybe if I change that, I'd be really sore.

If we all earned $10K per month for spending 40-hour weeks in OFBiz ML, I'd probably be going
around greeting people like "hey sunshine, how's the weather?". :)

Jonathon

skip@thedevers wrote:

> Ah well, "undocumented" is probably not the right word given that we all
> have the source.  Perhaps "hidden" might be better?  But no, that would
> imply the intent to hide something which is surely not the case.  Perhaps
> "unpublished"?
>
> This is all tongue-in-cheek David.  My point is that here is a really cool
> feature that went un-noticed by me after looking through all the
> documentation and a substantial part of the source code, including the
> documentation in the link you provided.  Someone added this feature,
> apparently a couple of years ago, but did not take the 5 minutes to note its
> existance.
>
> It is a simple matter to request that submitters provide at least minimal
> documentation for new features that the committers can then include in the
> base documentation, especially in the entity and widget and other "engines".
>
> There is no value in any software if the implementer has to spend countless
> hours experimenting and digging through source code to implement some new
> feature, fun as such an exercise may be.
>
> And who sez that "Documented features are somewhat the exception, not the
> norm." in open source software.  I can point you to scads of open source
> software with excellent documention, and some of it used by Ofbiz itself
> (ftl and tomcat come instantly to mind).
>
> Also, have an overview look at the most successful open soure projects.  All
> (that I know of) are very well documented.  The success of any open source
> project is determined by its committers and the quality of their code.  The
> more committers, the more successful the project becomes.  You get more
> committers with good documentation.
>
> This is not so say that Ofbiz documentation is bad.  It's just not as good
> (read that complete) as it could be with a few policy changes.
>
> Skip
>
> -----Original Message-----
> From: David E Jones [mailto:[hidden email]]
> Sent: Tuesday, December 04, 2007 1:07 PM
> To: [hidden email]
> Subject: Re: Entity engine, "many" relations, foreign keys
>
>
>
> On Dec 4, 2007, at 1:57 PM, skip@thedevers wrote:
>
>> Gads Adrian.  I never knew of the existence of this tag.  Thanks.
>> It is
>> perfect.  Someone needs to document it in the entity engine guide or
>> at
>> least somewhere prominent.  I just did a search for this tag and found
>> dozens of uses, but somehow, I missed it even though I looked at
>> OrderHeader's definition a gazillion times.
>>
>> Are there any other cool undocumented DB features?
>
> Undocumented features? You're funny. ;)
>
> This isn't commercial closed source software where the only way you
> can find out about a feature is through documentation. Documented
> features are somewhat the exception, not the norm.
>
> Of course, there are significantly more complete materials available,
> like these:
>
> http://docs.ofbiz.org/display/OFBTECH/Framework+Introduction+Videos+and+Diag
> rams
>
> -David
>
>
>

[jonwimp]

Skip,

 > First, I have read EVERY document I can get my hands on concerning Ofbiz
 > ... nowhere in them is the <index> tag mentioned and I am betting many other
 > equally cool features.

There's was a remark by Andy some years back, kind of apologizing for lack of documentation.
Reason given was that OFBiz is moving fast ahead, and had no resources for documenting.

I agree OFBiz is not very friendly to non-technical folks now. But then, there's money to be made
in that market! :)

 > Which brings me to point two.  Not everyone functions as you do reading xsds
 > and such.

Which is why those of us who do read them (plus corresponding Java codes) make a living doing
training.

In fact, I'm setting up a center in Singapore next year Jan, probably. I need more OFBiz
programmers here.

 > Furthermore, not every element choice in an XSD is obvious in its use and
 > their existance is not a good substitute for notes from the author.

Worse than that, some XSD elements are not implemented. But those are quite rare, I'd say.

You will always need to get to the source (Java) to "read" OFBiz "documentation". I've been doing
that since day 1 with OFBiz. The source is like an open book to me.

 > "And where do you see yourself in this picture Skip?"  I had hoped that was
 > obvious.

Sigh. Let's not put anyone into roles here. We're all here for ourselves. To be here *not* for
ourselves would be irresponsible. We all need to take care of ourselves, so we don't become a
burden to others, I mean.

If David and other committers were robots, maybe things would've been different.

We're humans. :) We have just 2 options when dealing with humans. Like it or hate it. I prefer to
like it. :)

It's nice to have you join us, Skip!

Jonathon

skip@thedevers wrote:

> David
>
> I seem to have hit a sore spot.  That was not my intent and I apologize.  I
> will limit my response to a few comments.  First, I have read EVERY document
> I can get my hands on concerning Ofbiz including all the ones in your link
> below.  I even downloaded and printed the more informative ones which I keep
> for a reference.  I for one am most appreciative for their existance.  I
> have watched all your training videos at least twice.  I have done all of Si
> Chens things as well.  All were very helpful.  However, nowhere in them is
> the <index> tag mentioned and I am betting many other equally cool features.
>
> Which brings me to point two.  Not everyone functions as you do reading xsds
> and such.  Furthermore, not every element choice in an XSD is obvious in its
> use and their existance is not a good substitute for notes from the author.
> I personally like to have references I can look at.  However, I understand
> that not everyone works like me so I comment my code as well.
>
> I did not suggest that documention should be required from submitters.  What
> I said was "It is a simple matter to request ...".  Right now there are
> submission guidelines that cover such things as code formatting.  It is not
> "required".  It sez that your code is more likely to be adopted if you
> follow these guidelines.  The same thing could be said for documentation.
>
> As for saying that I would rather the <index> feature did not exist, the
> answer is that it did not exist for me till I found out about it when Adrian
> so kindly pointed it out.
>
> "And where do you see yourself in this picture Skip?"  I had hoped that was
> obvious.  I have contributed lots of code to date and will will continue to
> make contributions knowing full well that the majority will never be
> adopted.  I expect that is more than most.  I will continue to do so over
> the coming years as I am committed to several Ofbiz projects.  It is true
> that I dropped off the dev mailing list, but that does not mean that I am
> not willing to help if asked.  I just got tired of the hassle and felt my
> time was better spent writting the code myself instead of debating it.
>
> Skip
>
>
>
> -----Original Message-----
> From: David E Jones [mailto:[hidden email]]
> Sent: Tuesday, December 04, 2007 2:35 PM
> To: [hidden email]
> Subject: Re: Entity engine, "many" relations, foreign keys
>
>
>
> On Dec 4, 2007, at 3:07 PM, skip@thedevers wrote:
>
>> Ah well, "undocumented" is probably not the right word given that we
>> all
>> have the source.  Perhaps "hidden" might be better?  But no, that
>> would
>> imply the intent to hide something which is surely not the case.
>> Perhaps
>> "unpublished"?
>>
>> This is all tongue-in-cheek David.  My point is that here is a
>> really cool
>> feature that went un-noticed by me after looking through all the
>> documentation and a substantial part of the source code, including the
>> documentation in the link you provided.  Someone added this feature,
>> apparently a couple of years ago, but did not take the 5 minutes to
>> note its
>> existance.
>
> You think that's all it takes? Then why don't you do it? ;)
>
>> It is a simple matter to request that submitters provide at least
>> minimal
>> documentation for new features that the committers can then include
>> in the
>> base documentation, especially in the entity and widget and other
>> "engines".
>
> I'm pushing 80 hours a week trying to stay on top of earning a living
> and doing what little I can to keep OFBiz moving. Why should I also
> take on the responsibility of speculatively training every possible
> user?
>
> Oh wait, I did that, but in order to afford my own over-encumbered
> time it is only available for a charge, and yes this and much more
> information is in it, hence this link:
>
> http://docs.ofbiz.org/display/OFBTECH/Framework+Introduction+Videos+and+Diag
> rams
>
> Follow the link to the more complete package, that's just an
> introduction.
>
>> There is no value in any software if the implementer has to spend
>> countless
>> hours experimenting and digging through source code to implement
>> some new
>> feature, fun as such an exercise may be.
>
> Oh yeah, then why does OFBiz exist with NO central organization that
> sponsors it and pays people to work on it? Why do so many people use
> it in spite of this fatal weakness?
>
>> And who sez that "Documented features are somewhat the exception,
>> not the
>> norm." in open source software.  I can point you to scads of open
>> source
>> software with excellent documention, and some of it used by Ofbiz
>> itself
>> (ftl and tomcat come instantly to mind).
>
> Did I say that was the norm for open source software?
>
> Again to paraphrase Johnny Depp in Willy Wonka and the Chocolate
> Factory: "you're funny".
>
>> Also, have an overview look at the most successful open soure
>> projects.  All
>> (that I know of) are very well documented.  The success of any open
>> source
>> project is determined by its committers and the quality of their
>> code.  The
>> more committers, the more successful the project becomes.  You get
>> more
>> committers with good documentation.
>
> Wouldn't that be nice! I may have a jaded view, but I think marketing
> and such have a stronger effect when it comes to mass adoption.
>
> I really agree though that more committers and good code quality are
> important for a community-driven project.
>
> This might seem funny, but I don't agree that good documentation
> attracts more committers. If people can stay distanced from the
> project and be only a consumer and not a collaborator, many will just
> do that and not see the value in learning more about the software or
> contributing to it.
>
>> This is not so say that Ofbiz documentation is bad.  It's just not
>> as good
>> (read that complete) as it could be with a few policy changes.
>
> Policy changes? You mean don't accept contributions without full
> documentation?
>
> No thanks, I'd like OFBiz contributions to increase, not decrease.
>
> Are you saying you'd rather this feature did NOT exist, than to exist
> with documentation that doesn't meet your standards? BTW, my opinion
> on docs: forget them, they are slow and in efficient, I want to look
> at the XSD file and see what exists as it's way faster and usually my
> XML editor looks up the options and their in-line docs for me.
>
> And where do you see yourself in this picture Skip? A critic and user
> who is outside the community and can help most effectively by
> complaining? Something else?
>
> How do the people behave who are contributor-users of OFBiz, ie the
> ones that have created all the stuff you are complaining about?
>
> If I had $50 million in the bank and wanted to create something like
> you are dreaming about then maybe it would exist... but it's not that
> way... I'm just a broke programmer and business analyst working with
> others to create something that is cool and useful for all of us
> involved, and as unencumbered as possible so that it remains that way
> and makes a difference in the world.
>
> Nothing personal about you, BTW, lots of people misunderstand what a
> community-driven software project means. I think it's mostly because
> of the mountains of manure that "open source" companies push on the
> world, ie the ones with traditionally developed products that are
> partially open source (dual-licensed, etc, etc) for the sake of
> marketing.
>
> -David
>
>
>
>

[jonwimp]

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

[Jacques Le Roux]

Or even better read and then add annotations/documentations that others will read and maybe complete/enhance...

https://issues.apache.org/jira/browse/OFBIZ-1398

This part of the work, as for code, benefits to all but is more neglicted. Because most people just read the DTD as Jonathon
suggested. Nevertheless having it as code-completion should motivates everyone.

Jacques

De : "Jonathon -- Improov" <[hidden email]>

> Skip,
>
> Read the DTDs. See ${component:entity}/dtd/entitymodel.xsd .
>
> For an <entity>, there are <field>, <prim-key>, <relation>, <index>.
>
> Jonathon
>
> skip@thedevers wrote:
> > Gads Adrian.  I never knew of the existence of this tag.  Thanks.  It is
> > perfect.  Someone needs to document it in the entity engine guide or at
> > least somewhere prominent.  I just did a search for this tag and found
> > dozens of uses, but somehow, I missed it even though I looked at
> > OrderHeader's definition a gazillion times.
> >
> > Are there any other cool undocumented DB features?
> >
> > Skip
> >
> > -----Original Message-----
> > From: Adrian Crum [mailto:[hidden email]]
> > Sent: Tuesday, December 04, 2007 11:47 AM
> > To: [hidden email]
> > Subject: Re: Entity engine, "many" relations, foreign keys
> >
> >
> > Why won't the <index> element work?
> >
> > skip@thedevers wrote:
> >> Ya BJ, seen that.  What I want to do is create an index that is simply
> > used
> >> for speed lookups.  I do this now in postgres after install has been done.
> >> It would just be nice to be able to specify it in the entitydef file.
> > But,
> >> it aint a big deal.  Easily done manually in postgres.
> >>
> >> Skip
> >>
> >> -----Original Message-----
> >> From: BJ Freeman [mailto:[hidden email]]
> >> Sent: Tuesday, December 04, 2007 11:09 AM
> >> To: [hidden email]
> >> Subject: Re: Entity engine, "many" relations, foreign keys
> >>
> >>
> >> have you looked at this for tools
> >> https://localhost:8443/webtools/control/view/checkdb
> >>
> >> skip@thedevers sent the following on 12/4/2007 11:01 AM:
> >>
> >>> It would be nice to have the foreign key checks as well as ways to
> >> creating
> >>
> >>> indexes on non-key fields.  There is a great performance increase having
> >> an
> >>
> >>> index in some tables.  Now I have to do in manually inside of the DB which
> >>> is fine.
> >>>
> >>> Skip
> >>>
> >>> -----Original Message-----
> >>> From: Jonathon -- Improov [mailto:[hidden email]]
> >>> Sent: Tuesday, December 04, 2007 3:08 AM
> >>> To: [hidden email]
> >>> Subject: Re: Entity engine, "many" relations, foreign keys
> >>>
> >>>
> >>> 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
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>
> >
> >
> >
>

[BJ Freeman]

how about linking this with the entity one as a complete documentation.
https://issues.apache.org/jira/browse/OFBIZ-1387

Jacques Le Roux sent the following on 12/4/2007 10:21 PM:

> Or even better read and then add annotations/documentations that others will read and maybe complete/enhance...
>
> https://issues.apache.org/jira/browse/OFBIZ-1398
>
> This part of the work, as for code, benefits to all but is more neglicted. Because most people just read the DTD as Jonathon
> suggested. Nevertheless having it as code-completion should motivates everyone.
>
> Jacques
>
> De : "Jonathon -- Improov" <[hidden email]>
>> Skip,
>>
>> Read the DTDs. See ${component:entity}/dtd/entitymodel.xsd .
>>
>> For an <entity>, there are <field>, <prim-key>, <relation>, <index>.
>>
>> Jonathon
>>
>> skip@thedevers wrote:
>>> Gads Adrian.  I never knew of the existence of this tag.  Thanks.  It is
>>> perfect.  Someone needs to document it in the entity engine guide or at
>>> least somewhere prominent.  I just did a search for this tag and found
>>> dozens of uses, but somehow, I missed it even though I looked at
>>> OrderHeader's definition a gazillion times.
>>>
>>> Are there any other cool undocumented DB features?
>>>
>>> Skip
>>>
>>> -----Original Message-----
>>> From: Adrian Crum [mailto:[hidden email]]
>>> Sent: Tuesday, December 04, 2007 11:47 AM
>>> To: [hidden email]
>>> Subject: Re: Entity engine, "many" relations, foreign keys
>>>
>>>
>>> Why won't the <index> element work?
>>>
>>> skip@thedevers wrote:
>>>> Ya BJ, seen that.  What I want to do is create an index that is simply
>>> used
>>>> for speed lookups.  I do this now in postgres after install has been done.
>>>> It would just be nice to be able to specify it in the entitydef file.
>>> But,
>>>> it aint a big deal.  Easily done manually in postgres.
>>>>
>>>> Skip
>>>>
>>>> -----Original Message-----
>>>> From: BJ Freeman [mailto:[hidden email]]
>>>> Sent: Tuesday, December 04, 2007 11:09 AM
>>>> To: [hidden email]
>>>> Subject: Re: Entity engine, "many" relations, foreign keys
>>>>
>>>>
>>>> have you looked at this for tools
>>>> https://localhost:8443/webtools/control/view/checkdb
>>>>
>>>> skip@thedevers sent the following on 12/4/2007 11:01 AM:
>>>>
>>>>> It would be nice to have the foreign key checks as well as ways to
>>>> creating
>>>>
>>>>> indexes on non-key fields.  There is a great performance increase having
>>>> an
>>>>
>>>>> index in some tables.  Now I have to do in manually inside of the DB which
>>>>> is fine.
>>>>>
>>>>> Skip
>>>>>
>>>>> -----Original Message-----
>>>>> From: Jonathon -- Improov [mailto:[hidden email]]
>>>>> Sent: Tuesday, December 04, 2007 3:08 AM
>>>>> To: [hidden email]
>>>>> Subject: Re: Entity engine, "many" relations, foreign keys
>>>>>
>>>>>
>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
>
>

[Jacques Le Roux]

Not sure it's really related but maybe a better chance to advertise ;o)

Done

Jacques

De : "BJ Freeman" <[hidden email]>

> how about linking this with the entity one as a complete documentation.
> https://issues.apache.org/jira/browse/OFBIZ-1387
>
> Jacques Le Roux sent the following on 12/4/2007 10:21 PM:
> > Or even better read and then add annotations/documentations that others will read and maybe complete/enhance...
> >
> > https://issues.apache.org/jira/browse/OFBIZ-1398
> >
> > This part of the work, as for code, benefits to all but is more neglicted. Because most people just read the DTD as Jonathon
> > suggested. Nevertheless having it as code-completion should motivates everyone.
> >
> > Jacques
> >
> > De : "Jonathon -- Improov" <[hidden email]>
> >> Skip,
> >>
> >> Read the DTDs. See ${component:entity}/dtd/entitymodel.xsd .
> >>
> >> For an <entity>, there are <field>, <prim-key>, <relation>, <index>.
> >>
> >> Jonathon
> >>
> >> skip@thedevers wrote:
> >>> Gads Adrian.  I never knew of the existence of this tag.  Thanks.  It is
> >>> perfect.  Someone needs to document it in the entity engine guide or at
> >>> least somewhere prominent.  I just did a search for this tag and found
> >>> dozens of uses, but somehow, I missed it even though I looked at
> >>> OrderHeader's definition a gazillion times.
> >>>
> >>> Are there any other cool undocumented DB features?
> >>>
> >>> Skip
> >>>
> >>> -----Original Message-----
> >>> From: Adrian Crum [mailto:[hidden email]]
> >>> Sent: Tuesday, December 04, 2007 11:47 AM
> >>> To: [hidden email]
> >>> Subject: Re: Entity engine, "many" relations, foreign keys
> >>>
> >>>
> >>> Why won't the <index> element work?
> >>>
> >>> skip@thedevers wrote:
> >>>> Ya BJ, seen that.  What I want to do is create an index that is simply
> >>> used
> >>>> for speed lookups.  I do this now in postgres after install has been done.
> >>>> It would just be nice to be able to specify it in the entitydef file.
> >>> But,
> >>>> it aint a big deal.  Easily done manually in postgres.
> >>>>
> >>>> Skip
> >>>>
> >>>> -----Original Message-----
> >>>> From: BJ Freeman [mailto:[hidden email]]
> >>>> Sent: Tuesday, December 04, 2007 11:09 AM
> >>>> To: [hidden email]
> >>>> Subject: Re: Entity engine, "many" relations, foreign keys
> >>>>
> >>>>
> >>>> have you looked at this for tools
> >>>> https://localhost:8443/webtools/control/view/checkdb
> >>>>
> >>>> skip@thedevers sent the following on 12/4/2007 11:01 AM:
> >>>>
> >>>>> It would be nice to have the foreign key checks as well as ways to
> >>>> creating
> >>>>
> >>>>> indexes on non-key fields.  There is a great performance increase having
> >>>> an
> >>>>
> >>>>> index in some tables.  Now I have to do in manually inside of the DB which
> >>>>> is fine.
> >>>>>
> >>>>> Skip
> >>>>>
> >>>>> -----Original Message-----
> >>>>> From: Jonathon -- Improov [mailto:[hidden email]]
> >>>>> Sent: Tuesday, December 04, 2007 3:08 AM
> >>>>> To: [hidden email]
> >>>>> Subject: Re: Entity engine, "many" relations, foreign keys
> >>>>>
> >>>>>
> >>>>> 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
> >>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>
> >>>
> >
> >
> >
> >
>

[Jacques Le Roux]

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" <[hidden email]>

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