Mailing-List: contact dev-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@cayenne.apache.org
Received-SPF: pass (athena.apache.org: local policy)
Content-Type: text/plain; charset=iso-8859-1
Mime-Version: 1.0 (Mac OS X Mail 6.3 \(1503\))
Subject: =?windows-1252?Q?Re=3A_SQLSelect=3A_Getting_fluent=85_WDYT=3F?=
From: Andrus Adamchik <andrus@objectstyle.org>
In-Reply-To: 
 <CAA-upzqTU7=oXRDgBzG0oiAUmRr=M3zCAURc_cFgKYKdcn0PUg@mail.gmail.com>
Date: Sun, 26 May 2013 22:41:45 +0300
Content-Transfer-Encoding: quoted-printable
Message-Id: <7BB0FE1E-1D2C-407F-B8D9-02318AB5E3AE@objectstyle.org>
References: <1B9775F4-3039-4390-B647-408192678243@objectstyle.org>
 <CAA-upzqA=bJBrHsAqGxbCFSX4Ed0iW6JVmEPPC+26XkHa=nKVg@mail.gmail.com>
 <FBBB8107-88C5-4097-9E45-52B51947036F@objectstyle.org>
 <CAA-upzqTU7=oXRDgBzG0oiAUmRr=M3zCAURc_cFgKYKdcn0PUg@mail.gmail.com>
To: dev@cayenne.apache.org

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.=20

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).=20

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.
>=20
> 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:
>=20
> "SELECT NAME FROM ARTIST"
>=20
> if you are returning Artist.class (you want to fetch all the columns).
> That type of query is only valid for data rows, right?
>=20
> Also, I still don't like having to specify the artist twice.  So if =
doing a
> data row query, maybe:
>=20
> SQLSelect.dataRowsOn(Artist.class).columns(List<String> or
> String...).where("...").fetch(context);
>=20
> 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:
>=20
> =
SQLSelect.dataRowsOn(Artist.class).columns(ARTIST.NAME_PROPERTY).where("..=
.").select(context);
>=20
> Of course, there should also be a where() method accepts an =
Expression, I
> think.
>=20
> Thoughts?
>=20
> Thanks,
>=20
> mrg
>=20
>=20
>=20
>=20
>=20
> On Sun, May 26, 2013 at 12:58 PM, Andrus Adamchik =
<andrus@objectstyle.org>wrote:
>=20
>> 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.
>>=20
>> 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.
>>=20
>> A.
>>=20
>>=20
>> On May 26, 2013, at 3:19 PM, Michael Gentry <mgentry@masslight.net> =
wrote:
>>> Hi Andrus,
>>>=20
>>> I may be missing something, but it looks like you'd have to do =
something
>>> such as:
>>>=20
>>> SQLSelect.query(Artist.class, "SELECT * FROM ARTIST WHERE ...");
>>>=20
>>> In most cases, you are always going to select "*" I think and artist =
is
>>> duplicated.  Why not something more along the lines of:
>>>=20
>>> SQLSelect.on(Artist.class).where("...");
>>>=20
>>> Thanks,
>>>=20
>>> mrg
>>>=20
>>>=20
>>>=20
>>> On Fri, May 24, 2013 at 11:45 AM, Andrus Adamchik <
>> andrus@objectstyle.org>wrote:
>>>=20
>>>> https://issues.apache.org/jira/browse/CAY-1828
>>>>=20
>>>>=20
>> =
http://svn.apache.org/repos/asf/cayenne/main/trunk/framework/cayenne-jdk1.=
5-unpublished/src/main/java/org/apache/cayenne/query/SQLSelect.java
>>>>=20
>>>> 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.
>>>>=20
>>>> I can think of other methods for building SQL chunks based on =
Cayenne
>>>> mapping, such as "allColumns()", etc.
>>>>=20
>>>> 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.
>>>>=20
>>>> Andrus
>>>>=20
>>>>=20
>>>>=20
>>=20
>>=20