cayenne-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Zeigler <robert.zeig...@puregumption.com>
Subject Re: SQLSelect: Getting fluent… WDYT?
Date Mon, 27 May 2013 05:37:24 GMT
My most common use-cases for SQLTemplate are either
a) To express an idiom that doesn't exist via other API's
b) For performance purposes.

One example of b: Several years ago (long before EJBQL support), I essentially had to build
a pivot table, and to do it through the object api wound up being incredibly expensive. So
I wrote some highly tuned and optimized queries and used SQLTemplate to fetch the data as
data rows.

Outside of a and b, I typically stick with the object-based APIs.

Robert

On May 26, 2013, at 5/262:41 PM , Andrus Adamchik <andrus@objectstyle.org> wrote:

> I think the example in the unit tests is just not too representative of how this will
be used in real life. If all I need is "SELECT * FROM ARTIST", I'd use SelectQuery, not raw
SQL. Usually SQLTemplate (and now SQLSelect) is a query of a last resort. 
> 
> An example from my customer apps... Occasionally I'd have a  situation when in addition
to the main table ARTIST, I have a set of views that fetch data structures compatible with
ARTIST, but do it in some really twisted way (with unions, subqueries, etc.). So 90% of the
time I'd do "new SelectQuery(Artist.class)", and in the remaining 10% it will be "new SQLTemplate(Artist.class,
"SELECT * FROM ARTIST_VIEW1")".
> 
> If others are mostly using SQLTemplate to get a count or another aggregate function,
we should just build utilities around SelectQuery to make aggregates possible / easy (kind
of like you did already). 
> 
> So I'd like to hear from everyone what are the most typical use cases for SQLTemplate
now?
> 
> Andrus
> 
> 
> On May 26, 2013, at 9:13 PM, Michael Gentry <mgentry@masslight.net> wrote:
>> That's cool, but it still seems like there is too much duplication for SQL
>> queries.
>> 
>> The main reason to pass in Artist.class, I think, is to know which objects
>> to create coming back, which means you shouldn't do:
>> 
>> "SELECT NAME FROM ARTIST"
>> 
>> if you are returning Artist.class (you want to fetch all the columns).
>> That type of query is only valid for data rows, right?
>> 
>> Also, I still don't like having to specify the artist twice.  So if doing a
>> data row query, maybe:
>> 
>> SQLSelect.dataRowsOn(Artist.class).columns(List<String> or
>> String...).where("...").fetch(context);
>> 
>> In this example, Cayenne can look up the correct table name for
>> Artist.class and automatically build it into the "SELECT ... FROM ARTIST"
>> for you.  And no need to write "select" three times, either -- I've
>> replaced your select(context) above with fetch(context).  Also, have two
>> methods for columns(), one which takes a List<String> and one that takes a
>> varargs parameter.  If you omit the columns, it can default to "*"
>> automatically.  I think this would provide more type safety, such as:
>> 
>> SQLSelect.dataRowsOn(Artist.class).columns(ARTIST.NAME_PROPERTY).where("...").select(context);
>> 
>> Of course, there should also be a where() method accepts an Expression, I
>> think.
>> 
>> Thoughts?
>> 
>> Thanks,
>> 
>> mrg
>> 
>> 
>> 
>> 
>> 
>> On Sun, May 26, 2013 at 12:58 PM, Andrus Adamchik <andrus@objectstyle.org>wrote:
>> 
>>> Absolutely. I was planning a model-based SQL building as the next step for
>>> SQLSelect. The current version (that only took me maybe an hour to write)
>>> streamlines casting the result to something that you need, binding
>>> parameters, etc. I haven't looked at the SQL "payload" part of it yet. My
>>> note below about "other methods for building SQL chunks based on Cayenne
>>> mapping, such as "allColumns()" is essentially about doing something like
>>> you suggest.
>>> 
>>> In general designing this fluent API requires a bit different mindset
>>> compared to designing "canonical" API that we have. Will need to better
>>> wrap my head around it.
>>> 
>>> A.
>>> 
>>> 
>>> On May 26, 2013, at 3:19 PM, Michael Gentry <mgentry@masslight.net> wrote:
>>>> Hi Andrus,
>>>> 
>>>> I may be missing something, but it looks like you'd have to do something
>>>> such as:
>>>> 
>>>> SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ...");
>>>> 
>>>> In most cases, you are always going to select "*" I think and artist is
>>>> duplicated.  Why not something more along the lines of:
>>>> 
>>>> SQLSelect.on(Artist.class).where("...");
>>>> 
>>>> Thanks,
>>>> 
>>>> mrg
>>>> 
>>>> 
>>>> 
>>>> On Fri, May 24, 2013 at 11:45 AM, Andrus Adamchik <
>>> andrus@objectstyle.org>wrote:
>>>> 
>>>>> https://issues.apache.org/jira/browse/CAY-1828
>>>>> 
>>>>> 
>>> http://svn.apache.org/repos/asf/cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/query/SQLSelect.java
>>>>> 
>>>>> So SQLTemplate annoyed me enough to wrap it as a quick experiment with
>>>>> fluent APIs. I guess this is the direction where the rest of the queries
>>>>> should be going. Aside from chaining query configuration parameters,
>>> there
>>>>> are "select" and "selectOne" methods that allow to take the chain to
the
>>>>> logical conclusion - the resulting objects.
>>>>> 
>>>>> I can think of other methods for building SQL chunks based on Cayenne
>>>>> mapping, such as "allColumns()", etc.
>>>>> 
>>>>> What do you think? Also method naming criticism is accepted. E.g. I am
>>> not
>>>>> sure that changing "setPageSize()" to "pageSize()" was such a great
>>> idea.
>>>>> 
>>>>> Andrus
>>>>> 
>>>>> 
>>>>> 
>>> 
>>> 
> 


Mime
View raw message