OFBiz › OFBiz - User

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

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

28 messages Options

jonwimp

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

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

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

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

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

SkipDever

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

In reply to this post by jonwimp

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

David E Jones

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

Hugh? Manually specified indexes? Those have been there for years...

-David

On Dec 4, 2007, at 12:01 PM, skip@thedevers wrote:

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

smime.p7s (3K) Download Attachment

BJ Freeman

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

In reply to this post by SkipDever

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

SkipDever

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

In reply to this post by David E Jones

Can someone point me to an example or some document? I find no reference
here http://ofbiz.apache.org/docs/entity.html

Skip

-----Original Message-----
From: David E Jones [mailto:[hidden email]]
Sent: Tuesday, December 04, 2007 11:06 AM
To: [hidden email]
Subject: Re: Entity engine, "many" relations, foreign keys

Hugh? Manually specified indexes? Those have been there for years...

-David

On Dec 4, 2007, at 12:01 PM, skip@thedevers wrote:

SkipDever

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

In reply to this post by BJ Freeman

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

Adrian Crum

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

SkipDever

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

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

David E Jones

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

-David

smime.p7s (3K) Download Attachment

BJ Freeman

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

In reply to this post by SkipDever

skip the webtools link I gave you showed indexes, not sure why you did
not pick up on it.
also if you have looked a any of then entities you would see them also.

skip@thedevers sent the following on 12/4/2007 12:57 PM:

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

SkipDever

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

In reply to this post by David E Jones

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

Canh Phan

Unsubscribe

____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping

SkipDever

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

In reply to this post by BJ Freeman

Ya BJ, I saw those index fields, but why could I not have interpreted that
as the indexes created with the primary key?. As mentioned, I have looked
countless times at OrderHeader which contains this <index> tag and it did
not get noticed. I think in my case it was because I saw a naggle post that
requested this feature and therefore assumed it did not exist.

-----Original Message-----
From: BJ Freeman [mailto:[hidden email]]
Sent: Tuesday, December 04, 2007 1:23 PM
To: [hidden email]
Subject: Re: Entity engine, "many" relations, foreign keys

skip the webtools link I gave you showed indexes, not sure why you did
not pick up on it.
also if you have looked a any of then entities you would see them also.

skip@thedevers sent the following on 12/4/2007 12:57 PM:

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

David E Jones

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

In reply to this post by SkipDever

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

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

smime.p7s (3K) Download Attachment

SkipDever

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

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:

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

Vikas Mayur-2

Re: Unsubscribe

In reply to this post by Canh Phan

Check out this link for the address to unsubscribe
http://docs.ofbiz.org/display/OFBADMIN/Mailing+Lists

On Dec 5, 2007 3:41 AM, Canh Phan <[hidden email]> wrote:

>
>
>
>
>
> ____________________________________________________________________________________
> Looking for last minute shopping deals?
> Find them fast with Yahoo! Search.
> http://tools.search.yahoo.com/newsearch/category.php?category=shopping
>

--
Vikas Mayur

jonwimp

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

In reply to this post by jonwimp

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

jonwimp

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

In reply to this post by SkipDever

Skip,

Use <index> in <entity>. Check out PostalAddress in ${component:party}/entitydef/entitymodel.xml .

Jonathon

skip@thedevers wrote: