Hi Ari
Thank you for your so detailed feedback!
I'll try to explain a little bit our new design. (By the way you can
already try some parts of it: string and math functional expressions
and column() / columns() methods)
> 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);
No, selectOne() method is untouched. In fact there is no
incompatibility introduced by this feature. The only new thing
(explicit type in Property) is needed only if you are going to use
this new feature.
>
> 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?
>
The later is closer to the design.
Both column() and columns() methods return same ObjectSelect object,
they only change result type (as fetchDataRows() method do):
column() changes result to ObjectSelect<T>, where T is type of
Property, so no cast of the result required
and columns() changes to ObjectSelect<Object[]>. The idea is that you
can still use all methods in ObjectSelect.
Actually, column() method is only a shortcut of columns() method made
to return correct type for single Property case without type casting.
I.e. if you need only names of Artists you can do this :
List<String> names = ObjectSelect.query(Artist.class)
.column(Artist.ARTIST_NAME).select(context);
And if you need more than one property then columns() method is your choice:
List<Object[]> namesAndBirthDates = ObjectSelect.query(Artist.class)
.columns(Artist.ARTIST_NAME,
Artist.ARTIST_BIRTH_DATE).select(context);
So selectColumn() may be a handy method but then it will be
inconsistent with columns().
> Now this is interesting. What generators do you anticipate having in
> FunctionExpressionFactory?
> I'm assuming just those things that are commonly available in many SQL
> implementations?
Yes, you are right.
There will be all standard aggregate functions:
* COUNT
* MIN
* MAX
* AVG
* SUM
and common SQL functions (based on JPA standard):
* CONCAT
* SUBSTRING
* TRIM
* LOWER
* UPPER
* LENGTH
* LOCATE
* SQRT
* MOD
* ABS
* CURRENT_DATE
* CURRENT_TIME
* CURRENT_TIMESTAMP
> 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?
For now only SQL functions will be implemented. Using java functions
directly on the result would probably be a nice feature on its own,
but I don't see an easy way to use them as SQL functions could be
used,
e.g. in WHERE, ORDER BY, HAVING clauses:
Property<Integer> nameLength =
Property.create(FunctionExpressionFactory.lengthExp(Artist.ARTIST_NAME.path()),
Integer.class);
List<Artist> artists = ObjectSelect.query(Artist.class)
.where(nameLength.gt(10))
.select(context);
> 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);
>
I want this shortcut too :) I'd like to avoid a feature bloat, so that
could be done later.
> 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);
>
I'm not sure that Map<> will be a good approach to the problem.
We actually discussed with Andrus how PersistentObject can be returned
with columns() method.
And we decided that it will be better to define explicitly that you
want PersistentObject in result:
Property<Artist> artistSelf = Property.create("hollowArtist", ?some
expression?, Artist.class);
List<Object[]> result2 = ObjectSelect.query(Artist.class)
.columns(Artist.ARTIST_NAME, artistSelf, paintingCountProperty)
.having(paintingCountProperty.gt(10L))
.select(context);
What do you think about it?
And final hidden thought we have (not in any plans, may be not even in 4.0).
It will be possible to do relatively simple conversion into some
arbitrary POJO with plain Object[].
On Sat, Jan 7, 2017 at 5:35 AM, Aristedes Maniatis <a...@maniatis.org> wrote:
> 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
--
Best regards,
Nikita Timofeev
