You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Aristedes Maniatis <ar...@ish.com.au> on 2007/02/15 11:54:46 UTC
entities with calculated fields
We are very close to a release of our software but we've hit a
performance problem in our approach to displaying a list of records.
We are fetching a list of courses and for each one showing the count
of enrolments. To do that, we are performing a second stored query to
do a count which is proving to be too slow since it requires a second
trip back to the database for each row.
Is there a way to do this by creating runtime virtual entity which
represents a database view including that count column? Or for that
matter any other approach to this speed problem? Doing this as a raw
SQL fetch would obviously be quite quick since we could do this in
one SELECT statement including the embedded COUNT, but we'd rather a
Cayenne friendly option.
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: entities with calculated fields
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Feb 20, 2007, at 5:33 PM, Aristedes Maniatis wrote:
>
> On 20/02/2007, at 10:49 PM, Andrus Adamchik wrote:
>
>> As to the replacement, this is not a simple question and it goes
>> to the ORM basics, namely - only straight table rows joined via PK/
>> FK keys can be mapped to an object graph in a clean fashion. If a
>> result row's identity is unclear (e.g. a few table rows aggregated
>> in some derived data rows), ORM model breaks. In Cayenne we
>> recognize that by separating *objects* managed by Cayenne runtime,
>> and *data rows* representing some tabular data that may or may not
>> have a direct relationship to an object with a given identity.
>> Data rows can be fetched, but they are unmanaged.
>
> Moving to dev...
>
> Derived attributes don't need to break the object graph. They would
> be read only since it makes no sense to write that data back to the
> database, but they could cleanly map SQL query results to an object
> entity attribute. Mostly I'm thinking of aggregate functions across
> a relation (SUM, COUNT, MAX, etc) but anything would be possible
> including a flattened attribute (where a field from a related table
> is added to the many table attributes - this might be easier to
> deal with in some situations) or more exotic SQL functions like
> CHECKSUM or SOUNDEX.
Aggregate functions require GROUP BY (as they all *aggregate* rows;
except for some edge conditions like "select count(*) from x" where
GROUP BY is implied), flattened attributes require joins (that will
be supported at some point as a separate feature). Derived attributes
(in WebObjects sense) cover what's left - normally some kind of
straightforward derivatives of the root table columns (e.g. concat of
first name/last name). I am 99% sure that "no real aggregate"
limitation applies to WO EOF derived attributes (if not, please
correct me).
> To be honest I don't quite understand the need for derived entities
> with grouping (is this for report writing?) but should they stay if
> they promised to be read-only? My interest in them was because I
> thought they were a way to create derived attributes.
No, GROUP BY is implied in any aggregate query so if you take GROUP
BY out of equation, derived entities become redundant and can be
mapped as regular (read-only) db-entities.
So - single table, multi-table and aggregate entities are all
different beasts in both (a) mapping semantics and (b) runtime
handling. EOF derived columns and Cayenne derived entities are both
hacks (albeit often useful) that allow to present certain cases of
aggregate and non-standard single table query results as simple
single table queries.
> I'm still not clear about the SQLtemplate. Is it the columnAlias
> which defines how an attribute is mapped to the object entity?
Yes. But more like a DbAttribute of that ObjEntity's DbEntity. This
query:
"select 1 as ARTIST_ID, 'A' as ARTIST_NAME, '1900-01-01' as
DATE_OF_BIRTH"
can be converted to the Artist object.
> How does this work today if we want objects back out and not just
> data rows?
Essentially the result set MUST match the columns of DbEntity for the
ObjEntity in question. This is indeed very limiting, and I hope we
will improve it in 3.0 - but likely for unmanaged objects.
We can do two things to replace derived entities:
* Allow read-only ObjEntities without DbEntities.
* Add support for JPA-compatible SqlResultSetMapping.
Andrus
Re: entities with calculated fields
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 20/02/2007, at 10:49 PM, Andrus Adamchik wrote:
> As to the replacement, this is not a simple question and it goes to
> the ORM basics, namely - only straight table rows joined via PK/FK
> keys can be mapped to an object graph in a clean fashion. If a
> result row's identity is unclear (e.g. a few table rows aggregated
> in some derived data rows), ORM model breaks. In Cayenne we
> recognize that by separating *objects* managed by Cayenne runtime,
> and *data rows* representing some tabular data that may or may not
> have a direct relationship to an object with a given identity. Data
> rows can be fetched, but they are unmanaged.
Moving to dev...
Derived attributes don't need to break the object graph. They would
be read only since it makes no sense to write that data back to the
database, but they could cleanly map SQL query results to an object
entity attribute. Mostly I'm thinking of aggregate functions across a
relation (SUM, COUNT, MAX, etc) but anything would be possible
including a flattened attribute (where a field from a related table
is added to the many table attributes - this might be easier to deal
with in some situations) or more exotic SQL functions like CHECKSUM
or SOUNDEX.
Sometimes it might be necessary to use in-database functions rather
than adding this in Java to the object entity sub-class. In my case,
mainly for speed, but I can imagine other uses too.
Derived entities are another situation again. They need to be
completely read-only since you can never map them back to database
rows. But as you say, data rows are a good solution to this and the
SQLtemplate mechanism is very flexible. To be honest I don't quite
understand the need for derived entities with grouping (is this for
report writing?) but should they stay if they promised to be read-
only? My interest in them was because I thought they were a way to
create derived attributes.
I'm still not clear about the SQLtemplate. Is it the columnAlias
which defines how an attribute is mapped to the object entity? How
does this work today if we want objects back out and not just data rows?
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: entities with calculated fields
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 20/02/2007, at 10:49 PM, Andrus Adamchik wrote:
> As to the replacement, this is not a simple question and it goes to
> the ORM basics, namely - only straight table rows joined via PK/FK
> keys can be mapped to an object graph in a clean fashion. If a
> result row's identity is unclear (e.g. a few table rows aggregated
> in some derived data rows), ORM model breaks. In Cayenne we
> recognize that by separating *objects* managed by Cayenne runtime,
> and *data rows* representing some tabular data that may or may not
> have a direct relationship to an object with a given identity. Data
> rows can be fetched, but they are unmanaged.
Moving to dev...
Derived attributes don't need to break the object graph. They would
be read only since it makes no sense to write that data back to the
database, but they could cleanly map SQL query results to an object
entity attribute. Mostly I'm thinking of aggregate functions across a
relation (SUM, COUNT, MAX, etc) but anything would be possible
including a flattened attribute (where a field from a related table
is added to the many table attributes - this might be easier to deal
with in some situations) or more exotic SQL functions like CHECKSUM
or SOUNDEX.
Sometimes it might be necessary to use in-database functions rather
than adding this in Java to the object entity sub-class. In my case,
mainly for speed, but I can imagine other uses too.
Derived entities are another situation again. They need to be
completely read-only since you can never map them back to database
rows. But as you say, data rows are a good solution to this and the
SQLtemplate mechanism is very flexible. To be honest I don't quite
understand the need for derived entities with grouping (is this for
report writing?) but should they stay if they promised to be read-
only? My interest in them was because I thought they were a way to
create derived attributes.
I'm still not clear about the SQLtemplate. Is it the columnAlias
which defines how an attribute is mapped to the object entity? How
does this work today if we want objects back out and not just data rows?
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: entities with calculated fields
Posted by Andrus Adamchik <an...@objectstyle.org>.
> Would you like me to create a task that we mark the API and
> documentation mentions of it as deprecated?
Yeah - IMO now is a good time to mark it as deprecated.
As to the replacement, this is not a simple question and it goes to
the ORM basics, namely - only straight table rows joined via PK/FK
keys can be mapped to an object graph in a clean fashion. If a result
row's identity is unclear (e.g. a few table rows aggregated in some
derived data rows), ORM model breaks. In Cayenne we recognize that by
separating *objects* managed by Cayenne runtime, and *data rows*
representing some tabular data that may or may not have a direct
relationship to an object with a given identity. Data rows can be
fetched, but they are unmanaged.
What we can do to improve the usability of the data rows is to
produce "unmanaged" objects of a specific class from tabular data
(IIRC JPA spec SqlResultSetMapping should help us with that). For now
you can use data rows in your app, or wrap them in some unmapped user
defined classes to add behavior.
A few side notes:
* "data rows" are billed as a performance optimization feature in the
User Guide, but more importantly, this is a representation of tabular
data. We may need to make it more clear in the docs.
* The EOF example (which IIRC you can map in Cayenne already), is
only addressing an edge case of derived data that doesn't change the
identity of the row, so I don't think you can do a relationship count
as a EOF derived column.
Andrus
On Feb 20, 2007, at 2:21 PM, Aristedes Maniatis wrote:
> On 20/02/2007, at 1:43 AM, Andrus Adamchik wrote:
>
>>> I think Andrus is the right person to answer your question, but I do
>>> recall a conversation where the intention was for derived dbentities
>>> to be removed.
>>
>> True - that was an ugly and limited workaround. We do need to get
>> rid of it.
>
> Would you like me to create a task that we mark the API and
> documentation mentions of it as deprecated? Would a goal be to
> replace this with something like:
>
> http://developer.apple.com/documentation/LegacyTechnologies/
> WebObjects/WebObjects_4.5/System/Documentation/Developer/
> EnterpriseObjects/EOTools/DerivedProperties1.html
>
> or do you think that SQLtemplates are the catch all for this?
>
>>> SQLTemplate can also work with data objects, just need to make sure
>>> the object properties match the returned column names. The SQL
>>> template has a nice facility to support multiple database
>>> versions as
>>> well.
>>
>> Yep, that would be my recommendation as well.
>
>
> I've read the docs but I don't quite understand how this mapping
> back to an object entity will work. If I use the #result notation
> to specify all the attributes already in the _Artist.class and I
> then add something like paintingCount to Artist.class, how do I
> then tell the SQLtemplate to return that derived attribute into
> paintingCount?
>
> #result('COUNT(SELECT ...)' 'int' paintingCount)
>
> Will that do it? Is the columnAlias mapped to the attribute in the
> object entity?
>
>
> Ari
>
>
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
>
Re: entities with calculated fields
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 20/02/2007, at 1:43 AM, Andrus Adamchik wrote:
>> I think Andrus is the right person to answer your question, but I do
>> recall a conversation where the intention was for derived dbentities
>> to be removed.
>
> True - that was an ugly and limited workaround. We do need to get
> rid of it.
Would you like me to create a task that we mark the API and
documentation mentions of it as deprecated? Would a goal be to
replace this with something like:
http://developer.apple.com/documentation/LegacyTechnologies/
WebObjects/WebObjects_4.5/System/Documentation/Developer/
EnterpriseObjects/EOTools/DerivedProperties1.html
or do you think that SQLtemplates are the catch all for this?
>> SQLTemplate can also work with data objects, just need to make sure
>> the object properties match the returned column names. The SQL
>> template has a nice facility to support multiple database versions as
>> well.
>
> Yep, that would be my recommendation as well.
I've read the docs but I don't quite understand how this mapping back
to an object entity will work. If I use the #result notation to
specify all the attributes already in the _Artist.class and I then
add something like paintingCount to Artist.class, how do I then tell
the SQLtemplate to return that derived attribute into paintingCount?
#result('COUNT(SELECT ...)' 'int' paintingCount)
Will that do it? Is the columnAlias mapped to the attribute in the
object entity?
Ari
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: entities with calculated fields
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Feb 19, 2007, at 12:13 PM, Malcolm Edgar wrote:
> I think Andrus is the right person to answer your question, but I do
> recall a conversation where the intention was for derived dbentities
> to be removed.
True - that was an ugly and limited workaround. We do need to get rid
of it.
> SQLTemplate can also work with data objects, just need to make sure
> the object properties match the returned column names. The SQL
> template has a nice facility to support multiple database versions as
> well.
Yep, that would be my recommendation as well.
Andrus
Re: entities with calculated fields
Posted by Malcolm Edgar <ma...@gmail.com>.
Hi Aristedes,
I think Andrus is the right person to answer your question, but I do
recall a conversation where the intention was for derived dbentities
to be removed.
SQLTemplate can also work with data objects, just need to make sure
the object properties match the returned column names. The SQL
template has a nice facility to support multiple database versions as
well.
regards Malcolm Edgar
On 2/19/07, Aristedes Maniatis <ar...@ish.com.au> wrote:
>
> On 15/02/2007, at 10:48 PM, Juergen Saar wrote:
>
> > For solving such kind of problem,
> > we created views in the database.
>
> Thanks for this note. In our case we want to avoid that mainly
> because we want to maintain compatibility with different databases
> I'd rather do this in Cayenne. Now I understand we can create an
> SQLTemplate and work with raw data rows, but that comes with a few
> inconveniences.
>
> Today I stumbled on 'derived dbentities'. I could find nothing in the
> documentation about them and not a lot in Javadocs. My questions
> about them are:
>
> * are they a fully support Cayenne feature which is sticking around
> in the long term?
> * what does "%@" mean in the spec column and what else can I put
> there? Can I put "COUNT(SELECT some other table)" there?
> * if I add extra fields to the derived entity are they then simply
> ignored for the purpose of committing data back to the database?
> * can I now point my objEntity to this derived dbEntity instead of
> the original 'real' dbEntity? Will everything continue to work as
> before except I get to use a couple of new virtual fields? If that is
> the case, why do we even need derived dbEntities and why can't we
> just add additional virtual fields to the 'real' dbEntity?
>
> Sorry for lots of questions, but I'd like to understand these thing
> better since they look like they might be really useful.
>
>
> Ari Maniatis
>
>
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
>
>
>
Re: entities with calculated fields
Posted by Aristedes Maniatis <ar...@ish.com.au>.
On 15/02/2007, at 10:48 PM, Juergen Saar wrote:
> For solving such kind of problem,
> we created views in the database.
Thanks for this note. In our case we want to avoid that mainly
because we want to maintain compatibility with different databases
I'd rather do this in Cayenne. Now I understand we can create an
SQLTemplate and work with raw data rows, but that comes with a few
inconveniences.
Today I stumbled on 'derived dbentities'. I could find nothing in the
documentation about them and not a lot in Javadocs. My questions
about them are:
* are they a fully support Cayenne feature which is sticking around
in the long term?
* what does "%@" mean in the spec column and what else can I put
there? Can I put "COUNT(SELECT some other table)" there?
* if I add extra fields to the derived entity are they then simply
ignored for the purpose of committing data back to the database?
* can I now point my objEntity to this derived dbEntity instead of
the original 'real' dbEntity? Will everything continue to work as
before except I get to use a couple of new virtual fields? If that is
the case, why do we even need derived dbEntities and why can't we
just add additional virtual fields to the 'real' dbEntity?
Sorry for lots of questions, but I'd like to understand these thing
better since they look like they might be really useful.
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
Re: entities with calculated fields
Posted by Juergen Saar <ju...@jsaar.org>.
I'm not really sure about your needs ....
For solving such kind of problem,
we created views in the database.
As far as we could do performance tests
it seems, that it is the fastest way
and if you have several software-products working
on the same database, all products can
have a benefit of this.
In cayenne-repository the view
is configured as readonly entity.
Good luck ...
--- Juergen ---
2007/2/15, Aristedes Maniatis <ar...@ish.com.au>:
>
> We are very close to a release of our software but we've hit a
> performance problem in our approach to displaying a list of records.
> We are fetching a list of courses and for each one showing the count
> of enrolments. To do that, we are performing a second stored query to
> do a count which is proving to be too slow since it requires a second
> trip back to the database for each row.
>
> Is there a way to do this by creating runtime virtual entity which
> represents a database view including that count column? Or for that
> matter any other approach to this speed problem? Doing this as a raw
> SQL fetch would obviously be quite quick since we could do this in
> one SELECT statement including the embedded COUNT, but we'd rather a
> Cayenne friendly option.
>
>
> Ari Maniatis
>
>
>
>
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001 fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
>
>
>
>