using real views

[cjhowe]

After commenting on
https://issues.apache.org/jira/browse/OFBIZ-468 I went
looking for how to create the view entity that would
be beneficial to Si's feature.  I discovered that it's
not possible with the current state of the view-entity
in OFBiz.  I had assumed that because there was a
function attribute in the complex-alias that more
elaborate fields were possible.  However, it's not
quite that robust.  

Using Views greatly reduces the need to denormalize
the data model and allows programming to remain as
simple as if you had denormalized it.

Seeing as adding that functionality is quite an
undertaking, I went looking for a hack.  The easiest
hack is to create the VIEW in the underlying table and
then create an <entity/> definition that matches it.
This approach is prone to quite a few potential
problems (startup script will attempt to create a
table with that name if the view for whatever reason
doesn't exist and I'm sure there are many more).  

Can anyone propose a way to accomplish using a
database's Views that is a bit more complete and less
error prone or at the very least could be handled
completely within OFBiz.  Thanks!
,Chris

[David E Jones-2]

I'm not following you here.... What sort of combination of tables are  
you trying to create that wouldn't be possible? I tried imagining  
what you're imagining based on that issue, but I'm just not seeing it.

-David


On Nov 19, 2006, at 7:56 PM, Chris Howe wrote:

> After commenting on
> https://issues.apache.org/jira/browse/OFBIZ-468 I went
> looking for how to create the view entity that would
> be beneficial to Si's feature.  I discovered that it's
> not possible with the current state of the view-entity
> in OFBiz.  I had assumed that because there was a
> function attribute in the complex-alias that more
> elaborate fields were possible.  However, it's not
> quite that robust.
>
> Using Views greatly reduces the need to denormalize
> the data model and allows programming to remain as
> simple as if you had denormalized it.
>
> Seeing as adding that functionality is quite an
> undertaking, I went looking for a hack.  The easiest
> hack is to create the VIEW in the underlying table and
> then create an <entity/> definition that matches it.
> This approach is prone to quite a few potential
> problems (startup script will attempt to create a
> table with that name if the view for whatever reason
> doesn't exist and I'm sure there are many more).
>
> Can anyone propose a way to accomplish using a
> database's Views that is a bit more complete and less
> error prone or at the very least could be handled
> completely within OFBiz.  Thanks!
> ,Chris

[cjhowe]

using the various functions listed here:
http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html



--- David E Jones <[hidden email]>
wrote:

>
> I'm not following you here.... What sort of
> combination of tables are  
> you trying to create that wouldn't be possible? I
> tried imagining  
> what you're imagining based on that issue, but I'm
> just not seeing it.
>
> -David
>
>
> On Nov 19, 2006, at 7:56 PM, Chris Howe wrote:
>
> > After commenting on
> > https://issues.apache.org/jira/browse/OFBIZ-468 I
> went
> > looking for how to create the view entity that
> would
> > be beneficial to Si's feature.  I discovered that
> it's
> > not possible with the current state of the
> view-entity
> > in OFBiz.  I had assumed that because there was a
> > function attribute in the complex-alias that more
> > elaborate fields were possible.  However, it's not
> > quite that robust.
> >
> > Using Views greatly reduces the need to
> denormalize
> > the data model and allows programming to remain as
> > simple as if you had denormalized it.
> >
> > Seeing as adding that functionality is quite an
> > undertaking, I went looking for a hack.  The
> easiest
> > hack is to create the VIEW in the underlying table
> and
> > then create an <entity/> definition that matches
> it.
> > This approach is prone to quite a few potential
> > problems (startup script will attempt to create a
> > table with that name if the view for whatever
> reason
> > doesn't exist and I'm sure there are many more).
> >
> > Can anyone propose a way to accomplish using a
> > database's Views that is a bit more complete and
> less
> > error prone or at the very least could be handled
> > completely within OFBiz.  Thanks!
> > ,Chris
>
>

[David E Jones-2]

Sorry, I'm still not even close to getting it. Any more hints?

-David


On Nov 19, 2006, at 8:45 PM, Chris Howe wrote:

> using the various functions listed here:
> http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html
>
>
>
> --- David E Jones <[hidden email]>
> wrote:
>
>>
>> I'm not following you here.... What sort of
>> combination of tables are
>> you trying to create that wouldn't be possible? I
>> tried imagining
>> what you're imagining based on that issue, but I'm
>> just not seeing it.
>>
>> -David
>>
>>
>> On Nov 19, 2006, at 7:56 PM, Chris Howe wrote:
>>
>>> After commenting on
>>> https://issues.apache.org/jira/browse/OFBIZ-468 I
>> went
>>> looking for how to create the view entity that
>> would
>>> be beneficial to Si's feature.  I discovered that
>> it's
>>> not possible with the current state of the
>> view-entity
>>> in OFBiz.  I had assumed that because there was a
>>> function attribute in the complex-alias that more
>>> elaborate fields were possible.  However, it's not
>>> quite that robust.
>>>
>>> Using Views greatly reduces the need to
>> denormalize
>>> the data model and allows programming to remain as
>>> simple as if you had denormalized it.
>>>
>>> Seeing as adding that functionality is quite an
>>> undertaking, I went looking for a hack.  The
>> easiest
>>> hack is to create the VIEW in the underlying table
>> and
>>> then create an <entity/> definition that matches
>> it.
>>> This approach is prone to quite a few potential
>>> problems (startup script will attempt to create a
>>> table with that name if the view for whatever
>> reason
>>> doesn't exist and I'm sure there are many more).
>>>
>>> Can anyone propose a way to accomplish using a
>>> database's Views that is a bit more complete and
>> less
>>> error prone or at the very least could be handled
>>> completely within OFBiz.  Thanks!
>>> ,Chris
>>
>>
>

[cjhowe]

That chapter gave about 100 different functions that
are not supported by the view-entity.  

Let me try to split up my explanation into facts.

1) The view-entity only supports the functions avg,
count, count-distinct, lower, min, max, and sum.  
2) Many databases support creating VIEWS that have
columns in the View created with functions like
concat, dayname, month, case.
3) OFBiz does not actually create VIEWS in the
underlying database
4) OFBiz can read VIEWS in the underlying database if
an <entity/> definition is made with the same name as
the view that is stored in the database and that
entity is included in a group.
5) using <entity/> instead of <view-entity/> will
create a new table with the definition given if a
table or view does not exist in the underlying
database.

Since OFBiz cannot currently create a view with all of
the specialized functions offered by many of the
different databases, I would like OFBiz to at least be
able to utilize them but not subject the views to
possible errors that will come up if OFBiz tries to
treat the view like an entity (table)

--- David E Jones <[hidden email]>
wrote:

>
> Sorry, I'm still not even close to getting it. Any
> more hints?
>
> -David
>
>
> On Nov 19, 2006, at 8:45 PM, Chris Howe wrote:
>
> > using the various functions listed here:
> >
>

http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html

> >
> >
> >
> > --- David E Jones <[hidden email]>
> > wrote:
> >
> >>
> >> I'm not following you here.... What sort of
> >> combination of tables are
> >> you trying to create that wouldn't be possible? I
> >> tried imagining
> >> what you're imagining based on that issue, but
> I'm
> >> just not seeing it.
> >>
> >> -David
> >>
> >>
> >> On Nov 19, 2006, at 7:56 PM, Chris Howe wrote:
> >>
> >>> After commenting on
> >>> https://issues.apache.org/jira/browse/OFBIZ-468
> I
> >> went
> >>> looking for how to create the view entity that
> >> would
> >>> be beneficial to Si's feature.  I discovered
> that
> >> it's
> >>> not possible with the current state of the
> >> view-entity
> >>> in OFBiz.  I had assumed that because there was
> a
> >>> function attribute in the complex-alias that
> more
> >>> elaborate fields were possible.  However, it's
> not
> >>> quite that robust.
> >>>
> >>> Using Views greatly reduces the need to
> >> denormalize
> >>> the data model and allows programming to remain
> as
> >>> simple as if you had denormalized it.
> >>>
> >>> Seeing as adding that functionality is quite an
> >>> undertaking, I went looking for a hack.  The
> >> easiest
> >>> hack is to create the VIEW in the underlying
> table
> >> and
> >>> then create an <entity/> definition that matches
> >> it.
> >>> This approach is prone to quite a few potential
> >>> problems (startup script will attempt to create
> a
> >>> table with that name if the view for whatever
> >> reason
> >>> doesn't exist and I'm sure there are many more).
> >>>
> >>> Can anyone propose a way to accomplish using a
> >>> database's Views that is a bit more complete and
> >> less
> >>> error prone or at the very least could be
> handled
> >>> completely within OFBiz.  Thanks!
> >>> ,Chris
> >>
> >>
> >
>
>

[David E Jones-2]

On Nov 19, 2006, at 9:52 PM, Chris Howe wrote:

> That chapter gave about 100 different functions that
> are not supported by the view-entity.

That's true, but not quite in the way you think. Most of the  
functions on that page are scalar functions. The only functions  
relevant to the view-entity alias definitions are the aggregate  
functions listed at the top of that page.

There aren't very many scalar functions supported, but there are a  
few and more can certainly be added. For some details on this see the  
EntityExpr, EntityFunction, EntityConditionValue, and  
EntityFieldValue classes.

Just a warning here: while the Entity Engine's primary function is to  
generate SQL to send to the database as a result of more generic  
application level objects, it does quite a bit more than that. For  
example, it also uses those objects to evaluate conditions in memory  
for the automatic cache maintenance and such.

> Let me try to split up my explanation into facts.
>
> 1) The view-entity only supports the functions avg,
> count, count-distinct, lower, min, max, and sum.
> 2) Many databases support creating VIEWS that have
> columns in the View created with functions like
> concat, dayname, month, case.
> 3) OFBiz does not actually create VIEWS in the
> underlying database
> 4) OFBiz can read VIEWS in the underlying database if
> an <entity/> definition is made with the same name as
> the view that is stored in the database and that
> entity is included in a group.
> 5) using <entity/> instead of <view-entity/> will
> create a new table with the definition given if a
> table or view does not exist in the underlying
> database.
>
> Since OFBiz cannot currently create a view with all of
> the specialized functions offered by many of the
> different databases, I would like OFBiz to at least be
> able to utilize them but not subject the views to
> possible errors that will come up if OFBiz tries to
> treat the view like an entity (table)

This is correct, the Entity Engine builds join statements on the fly  
to support the view-entity and DynamicViewEntity features. This can  
be pretty easily seen by just watching the SQL sent to the database.  
It is very intentionally done this way. Using views in the database  
for this would make dynamic view entities untenable, and even for the  
static view-entity stuff supporting all databases would be really  
difficult, and with varying support for views (from no support to  
standard stuff to super funky stuff) would not be reliable.

You can certainly define a view in the database and then point a  
plain entity (ie not a view-entity) to it and then do normal  
operations on it and such. There shouldn't be any problems with this.

All of that said, I still don't get how this relates to issue  
OFBIZ-468. Does it not really have anything to do with it, but just  
stuff you started researching after thinking about that issue?

-David

>
> --- David E Jones <[hidden email]>
> wrote:
>
>>
>> Sorry, I'm still not even close to getting it. Any
>> more hints?
>>
>> -David
>>
>>
>> On Nov 19, 2006, at 8:45 PM, Chris Howe wrote:
>>
>>> using the various functions listed here:
>>>
>>
> http://www.oreilly.com/catalog/sqlnut/chapter/ch04.html
>>>
>>>
>>>
>>> --- David E Jones <[hidden email]>
>>> wrote:
>>>
>>>>
>>>> I'm not following you here.... What sort of
>>>> combination of tables are
>>>> you trying to create that wouldn't be possible? I
>>>> tried imagining
>>>> what you're imagining based on that issue, but
>> I'm
>>>> just not seeing it.
>>>>
>>>> -David
>>>>
>>>>
>>>> On Nov 19, 2006, at 7:56 PM, Chris Howe wrote:
>>>>
>>>>> After commenting on
>>>>> https://issues.apache.org/jira/browse/OFBIZ-468
>> I
>>>> went
>>>>> looking for how to create the view entity that
>>>> would
>>>>> be beneficial to Si's feature.  I discovered
>> that
>>>> it's
>>>>> not possible with the current state of the
>>>> view-entity
>>>>> in OFBiz.  I had assumed that because there was
>> a
>>>>> function attribute in the complex-alias that
>> more
>>>>> elaborate fields were possible.  However, it's
>> not
>>>>> quite that robust.
>>>>>
>>>>> Using Views greatly reduces the need to
>>>> denormalize
>>>>> the data model and allows programming to remain
>> as
>>>>> simple as if you had denormalized it.
>>>>>
>>>>> Seeing as adding that functionality is quite an
>>>>> undertaking, I went looking for a hack.  The
>>>> easiest
>>>>> hack is to create the VIEW in the underlying
>> table
>>>> and
>>>>> then create an <entity/> definition that matches
>>>> it.
>>>>> This approach is prone to quite a few potential
>>>>> problems (startup script will attempt to create
>> a
>>>>> table with that name if the view for whatever
>>>> reason
>>>>> doesn't exist and I'm sure there are many more).
>>>>>
>>>>> Can anyone propose a way to accomplish using a
>>>>> database's Views that is a bit more complete and
>>>> less
>>>>> error prone or at the very least could be
>> handled
>>>>> completely within OFBiz.  Thanks!
>>>>> ,Chris
>>>>
>>>>
>>>
>>
>>
>

[cjhowe]

--- David E Jones <[hidden email]>
wrote:

>
> All of that said, I still don't get how this relates
> to issue  
> OFBIZ-468. Does it not really have anything to do
> with it, but just  
> stuff you started researching after thinking about
> that issue?
>

It has more to do with those types of jira issues than
with that issue in particular.  Those "types" being
implementing functionality where there is an good,
established pattern of relating the commonly used
entities to a particular entity.  In regards to
OFBIZ-468 there's an established way of relating
ContactMech to other entities to where it's much more
robust and generic in adding additional features like
OFBIZ-468 than the pattern being proposed.

Building out the robustness of views can help prevent
the modification of the data model to support specific
functionality.  I imagine as the interest in reporting
and OLAP and data mining grows in OFBiz, this issue is
going to pop up more and more frequently.

OT: David, check out OFBIZ-400, that should
sufficiently solve the primitive type support in
call-*-mehtod that you were looking for.