On 7/1/17 12:08am, Nikita Timofeev wrote:
> Hi all,
>
> I would like to present new Cayenne feature in development to you: API
> for SQL functions (both aggregate and non aggregate).
>
> When it will be completed it will be possible to use functions in
> select queries with pure API calls without writing any custom SQL or
> EJBQL queries.
Wow, that's brilliant. And so very useful. I like your implementation using
Properties which is unexpected but very powerful.
Some of my notes below might be painting the bikeshed, but it might help us
understand your thinking...
> You'll be able to do something like this (though it may be not a final
> version of API):
>
> long totalCount = ObjectSelect.query(Artist.class)
> .column(Artist.ARTIST_COUNT)
> .selectOne(context);
The problem here is that selectOne no longer returns <Artist.class>. This seems
like a backward step to me and a significant breakage of existing code. Do we
need instead
long totalCount = ObjectSelect.query(Artist.class)
.selectColumn(context, Artist.ARTIST_COUNT);
or is it your idea that ObjectSelect.column(Artist.ARTIST_COUNT) returns some
new ColumnSelect object type? Is that confusing or more helpful than
selectColumn() approach?
> All this queries will rely on custom properties that can be created
> ad-hoc for specific query:
>
> Expression substrExp =
> FunctionExpressionFactory.substringExp(Artist.ARTIST_NAME.path(), 10,
> 15);
> Property<String> namePart = Property.create("namePart", substrExp,
> String.class);
Now this is interesting. What generators do you anticipate having in
FunctionExpressionFactory?
* count
* max
* min
* substring
I'm assuming just those things that are commonly available in many SQL
implementations?
Do you imagine that these properties will only implement SQL functions, or
would it also be possible to implement Java functions which act on the data
once returned from JDBC?
I know factories are the Java way, but given how common count() and max() are,
surely we'd want a shortcut. I'd like to be able to go directly to:
int count = ObjectSelect.query(Artist.class)
.where(Artist.ARTIST_NAME.eq("Ari"))
.selectCount(context);
Without casting and unwrapping the result for such a common use-case.
On the other hand, here:
Expression paintingCountExp =
FunctionExpressionFactory.countExp(Artist.PAINTING_ARRAY.path());
Property<Long> paintingCountProperty = Property.create("paintingCount",
paintingCountExp, Long.class);
List<Object[]> result2 = ObjectSelect.query(Artist.class)
.columns(Artist.ARTIST_NAME, paintingCountProperty)
.having(paintingCountProperty.gt(10L))
.select(context);
would a map be more useful to return than this tuple style approach? Java's
collections are a bit clunky, but:
Map<PersistentObject, Object[]> result2 = ObjectSelect.query(Artist.class)
.columns(Artist.ARTIST_NAME, paintingCountProperty)
.having(paintingCountProperty.gt(10L))
.select(context);
Where PersistentObject is a hollow Artist. You can get the id from it directly,
or fault the object to get its attributes. But still get the name and count
from the Object[] without hitting the database again.
And if you really didn't want the map, you still can use result2.values() with
(I guess) some performance hit:
* forcing SQL to always to fetch the PK
* building a map instead of array
* unwrapping the map
I think this work could be the most important and widely used changes to
Cayenne in years. But it is important to make it really easy to use.
I looked at Rails AR to see how they did it, and at Hibernate, but I don't
think either have useful patterns that were really clean. For reference
Hibernate is something like:
Criteria crit = session.createCriteria(Artist.class);
crit.add( Restrictions.eq("name", "Ari"));
crit.setProjection(Projections.rowCount());
Integer count = (Integer)crit.uniqueResult();
Hibernate Projections/Criteria look a bit like the way Properties and
Expressions are used above, but your columns() idea is much easier to read.
Cheers
Ari
--
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
