java.jdbc DSLs (java.jdbc.sql / java.jdbc.ddl)

[Sean Corfield]

In response to the (very reasonable) question from Alex Hudek in a
recent thread, here are some of my responses to questions that have
arisen about the inclusion of the minimal DSLs in the java.jdbc
contrib library:

> Just wondering if the intention is to make the DSL the primary way to work
> with the API or if clojure.java.jdbc.sql will be completely optional?

Completely optional.

The idea is just to provide some (optional) sugar for common
operations. I have no intention of going particularly deep on the DSL.

If folks want a full DSL for SQL in Clojure, I'd suggest
https://github.com/jkk/honeysql or http://sqlkorma.com - the former is
a DSL to generate SQL that is compatible with clojure.java.jdbc, the
latter is a DSL that wraps clojure.java.jdbc.

> Using the latest release of java.jdbc, does anybody know how I can
> construct a where clause when I want to check if the value is one of many values?

clojure.java.jdbc.sql is a deliberately minimal DSL - Justin Kramer's
HoneySQL is what I recommend for more expressive SQL construction
(that's the "official" recommendation based on discussions Justin and
I had about java.jdbc and HoneySQL at Clojure/conj 2012).

> I want to generate sql string using clojure.java.jdbc.sql for eg.
>
> "SELECT B.ID , B.TITLE ,B.AUTHOR , C.CATEGORY , S.STATUS
> FROM BOOK B ,CATEGORY C , STATUS S
> WHERE (B.CATEGORY_ID=C.ID) AND
> (B.STATUS_ID = B.ID) AND
> (B.TITLE LIKE "%TOM%")"
>
> How to do this using clojure.java.jdbc.sql dsl functions?

The basic DSL cannot do 'like' so you probably want to look at
HoneySQL which is the recommended way to extend clojure.java.jdbc.

===

The justification for the DSL at all is that it provides some sugar
for simple, common usage and also provides a template for other more
expansive DSLs that the community might write, by showing what the
primary API expects in terms of SQL strings and parameter vectors.

At World Singles, we use the basic DSL to support a lot of "CRUD"
operations that are doing simple lookups, basic joins, and so on - and
we rely on HoneySQL for our reporting queries and anything that is
substantially more complex than the basic DSL offers.

Will the DSL be expanded to support some additional common SQL operations?

Perhaps, based on user feedback - assuming people want something
between raw strings and the sophistication of HoneySQL (or any other
DSL that the community may produce).
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/

"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)

[Colin Yates]

Hi Sean,

First - I hugely appreciate the work you have done and use java.jdbc daily.

However, as a complete newbie I found the included DSL very unhelpful.  The java.jdbc API is very wide and navigating it was hard,  Particularly as it was in a transition from using bound *db* to not, so effectively there seemed two APIs.

I started using the DSL and it didn't meet the expectation I think anybody would have for a DSL in that it wasn't complete or expressive enough.  There were moments where the combined experience was very very negative and not representative of the quality of the work you have put in.

It also made it unclear what the library was supposed to do (regardless of what the documentation says :)).

Treating java.jdbc without the DSL and the DSL as separate projects (I realise they are separate namespaces already) would be a much stronger statement of intent I think.

To put it another way, at the moment the risk is to interpret java.jdbc as a DSL on top of JDBC with some low level utilities around managing connections.  That clearly isn't true and the project comes off looking very poor.

Please hear me - I write this only because I think my experience will be very similar to a lot of other people's experiences when they start adopting the library whilst picking up Clojure at the same time!

For me, the combination of honeysql and java.jdbc is close to perfect, but my point again is that I came very close many times to throwing java.jdbc out because of the experience of a very wide API which was effectively two APIs and a DSL which didn't meet a reasonable definition of a full 'DSL' (even if the documentation states it is minimal).  Moving the DSL into a separate project makes things much simpler.

Just my 2 pennies from a very grateful user who wants to avoid other people missing the point.

Col

[Alice]

I agree with Colin and had a similar experience.

Even if you say it's completely optional, people will first try it because it's already included. I think honeysql is good and also not any harder to use than the included DSL. It's concept is very simple and clear. Actually, your DSL is magical because it's based on macro.

For example, I asked about specifying the default name mapping functions before* and it was not the first time. I had to see the source code to understand why it's not possible. I was only the second but I bet you'll keep hearing this kind of questions over and over again. If you keep the jdbc and DSL in the same library, it becomes confusing to users why some things are not possible.

And I don't see any problem with separating it into another library. It's just one additional line in project.clj.

* https://groups.google.com/forum/#!searchin/clojure/jdbc/clojure/iOBs_VC09FM/8aUF2xzz7scJ

[Alexander Hudek]

Thanks for the explanation Sean. That was helpful.

Alex

[Sean Corfield]

Thank you both - that's excellent feedback!

I certainly don't want the library to cause confusion so maybe
hiding/removing the DSLs would be the best path going forward.

Right now, a handful of the DSL functions are used to generate the SQL
behind delete!, insert! and update! as well as the core implementation
of the naming strategy stuff. We're also using a few of them in
production code at World Singles but it would be easy enough to back
off that and switch to HoneySQL (which we use extensively elsewhere).

I totally agree about the API being too broad. I'd like to just drop
the old API completely but experience with APIs changing in contrib
libraries has shown that backward compatibility needs to be maintained
for at least a version or two so folks can migrate off the old API. I
was planning to streamline the API - dropping the old API that relied
on the dynamic *db* variable - in 0.5.0 but maybe I'll make that the
focus of 0.4.0 instead.

The feedback on the DSLs - ever since my first proposal to add them -
seems to be overwhelmingly negative so at this point I'm now
considering removing them from 0.3.0 rather than perpetuate the
confusion.

Is anyone using the java.jdbc.sql namespace? (besides World Singles :)

Sean

> --
> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@googlegroups.com
> Note that posts from new members are moderated - please be patient with your
> first post.
> To unsubscribe from this group, send email to
> clojure+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en
> ---
> You received this message because you are subscribed to the Google Groups
> "Clojure" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to clojure+u...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Ray Miller]

On 20 November 2013 22:25, Sean Corfield <seanco...@gmail.com> wrote:

Is anyone using the java.jdbc.sql namespace? (besides World Singles :)

Only the 'where' function, in a few places.

Ray.

[Alexander Hudek]

Is anyone using the java.jdbc.sql namespace? (besides World Singles :)

We are using it but not the DDL. We also use honeysql in places where jdbc.sql cannot express the query.

[seanco...@gmail.com]

I spent this afternoon removing use of java.jdbc.sql from World Singles’ code base to see how much work it would be. The worst part for us was how much we relied on the naming strategy convenience macros (especially entities, since it flows :entities through all the DSL constructs).

How much impact would it have on you, Alexander, if the java.jdbc.sql namespace went away? 

Sean Corfield -- (904) 302-SEAN

An Architect's View -- http://corfield.org

--

[Armando Blancas]

We use select several times in one module; it wouldn't be hard to just copy and paste the function somewhere. Now, you could drop DSL and call it optional utility functions not to be used when deemed unhelpful, or whatever. Given your goals stated goals that code isn't bad at all. 

[Sean Corfield]

On Fri, Nov 22, 2013 at 1:04 PM, Armando Blancas <abm2...@gmail.com> wrote:
> We use select several times in one module; it wouldn't be hard to just copy
> and paste the function somewhere.

Technically, copying it into your code means absorbing some EPL code
and copyright © 2013 Sean Corfield stuff so beware of that in
commercial situations (I personally don't mind but lawyers at some
companies will object to this). Perhaps a solution here is for me to
put it in a library, on Clojars, under a different name and let folks
migrate to that as an interim solution (i.e., identical API so folks
would just update project.clj and update some ns declarations)?

FWIW, I replaced most (select ...) uses in our code with raw SQL in a
vector with the parameters, completely replacing the DSL usage where
it was easy to do so. I also wrote a function to mimic a subset of
(where ...) to just produce the WHERE condition with our quoting of
entities strategy hard-coded (and managed the parameters separately).
I also mimicked a subset of (order-by ...) with our quoted strategy
hard-code, which I could probably simplify some more if I analyze our
usage for more than a few minutes.

I've asked for guidance on clojure-dev as well, but I suspect I'm in
somewhat uncharted territory with a contrib lib wanting to revert a
big chunk of changes in an already released (alpha / beta) version of
a library.

In order to tackle the "API bloat" (which is temporary until the old
0.2.3 API goes away), one possibility is to restore java.jdbc 0.2.3 to
something like java.jdbc.deprecated and strip java.jdbc down to just
the new API. It would be a breaking change but relatively painless
(just change some ns declarations to switch from java.jdbc to
java.jdbc.deprecated for old code, or add java.jdbc.deprecated to
support both old and new code).

[Armando Blancas]

Didn't think of that. I can just rewrite those simple select calls with parameterized raw SQL, which is our preferred way of using the API.

[Dave Della Costa]

> How much impact would it have on you, Alexander, if the java.jdbc.sql
> namespace went away?

I work for Alex and I can say that it wouldn't be such a big deal since
we are already including honeysql, and I would simply swap the built-in
DSL for that where necessary.

I can't speak for him but personally I feel like this would be a good move.

(2013/11/22 15:12), seanco...@gmail.com wrote:
> I spent this afternoon removing use of java.jdbc.sql from World Singles�

> code base to see how much work it would be. The worst part for us was
> how much we relied on the naming strategy convenience macros (especially
> entities, since it flows :entities through all the DSL constructs).
>
> How much impact would it have on you, Alexander, if the java.jdbc.sql
> namespace went away?
>
> Sean Corfield -- (904) 302-SEAN
> An Architect's View -- http://corfield.org
>

> *From:* Alexander Hudek <mailto:alex...@hudek.org>
> *Sent:* Thursday, November 21, 2013 7:37 PM
> *To:* clo...@googlegroups.com <mailto:clo...@googlegroups.com>

[John D. Hume]

On Nov 22, 2013 4:09 PM, "Sean Corfield" <seanco...@gmail.com> wrote:
>
> Perhaps a solution here is for me to
> put it in a library, on Clojars, under a different name and let folks
> migrate to that as an interim solution (i.e., identical API so folks
> would just update project.clj and update some ns declarations)?

That sounds like a good move.

[Alexander Hudek]

As Dave said, we can move to honeysql without too much trouble. However, it would be extremely useful to move the sql namespace to a separate library as an interim solution.

Out of curiosity, what exactly does entities do? It isn't immediately obvious from the document string.

[Sean Corfield]

On Sat, Nov 23, 2013 at 3:06 PM, Alexander Hudek <alex...@hudek.org> wrote:
> As Dave said, we can move to honeysql without too much trouble. However, it
> would be extremely useful to move the sql namespace to a separate library as
> an interim solution.

OK, I'll get that done soon.

> Out of curiosity, what exactly does entities do? It isn't immediately
> obvious from the document string.

entities (and identifiers) walk the contained expression and insert
the :entities (or :identifiers) argument into forms that support it so
that naming strategies are applied as expected. In other words:

(entities (quoted \`)
(query (my-db)
(select [:id {:title :name}] :thing
(where {:status "active"}))))

is shorthand for:

(query (my-db)
(select [:id {:title :name}] :thing
(where {:status "active"} :entities (quoted \`))
:entities (quoted \`))))

So this generates:

(query (my-db)
["select `id`, `title` as `name` from `thing` where `status` = ?" "active"])

[Keith Irwin]

Sean—

Personally, the DSL doesn’t bother me at all. (Just a data point.) I get where you’re going with it, and support the idea, FWIW, but if it were gone, I wouldn’t notice. My needs are 1) so simple, strings work, or 2) so complicated, a (or any) DSL is just extra headache. (Reading them out of a separately maintained data file, for instance, is one way to go.)

BUT:

The mixture of the old API and the new API is problematic, mainly because it’s difficult for me (anyway) to look down the list of functions and figure out which are old and which are new.

I understand the need to maintain the old API, and I’m not arguing against that at all.

Might you be able to publish a parallel version of the API *documentation* with all the deprecated stuff removed for those folks new to the library who are uninterested in the old API? Even users of the old API might appreciate it for the same reasons.

Parallel (and conjoined) API docs would solve ALL my (admittedly shallow) problems, actually, but I wonder if it would remove some confusion for others new to the lib and Clojure?

[Paul Samways]

> Parallel (and conjoined) API docs would solve ALL my (admittedly shallow) problems, actually, but I wonder if it would remove some confusion for others new to the lib and Clojure?

Definitely. I have only just started using Clojure and the java.jdbc library and have found this a (minor) pain point in an otherwise excellent library.

[Sean Corfield]

On Sat, Nov 23, 2013 at 8:27 PM, Keith Irwin <ke...@devtrope.com> wrote:
> Personally, the DSL doesn’t bother me at all. (Just a data point.) I get where you’re going with it, and support the idea, FWIW, but if it were gone, I wouldn’t notice. My needs are 1) so simple, strings work, or 2) so complicated, a (or any) DSL is just extra headache. (Reading them out of a separately maintained data file, for instance, is one way to go.)

Thank you.

> The mixture of the old API and the new API is problematic, mainly because it’s difficult for me (anyway) to look down the list of functions and figure out which are old and which are new.

I agree.

> Might you be able to publish a parallel version of the API *documentation* with all the deprecated stuff removed for those folks new to the library who are uninterested in the old API? Even users of the old API might appreciate it for the same reasons.

I think, given that I am retiring the DSL namespaces and making them
available in a separate project (that change is already committed,
although the docstrings have yet to catch up), the least confusing
thing to do at this point for 0.3.0-beta2 onward will be to create a
java.jdbc.deprecated namespace containing the entirety of the 0.2.3
API and remove all of the deprecated functions from java.jdbc itself?

That would clearly separate the API documentation into two namespaces:
one the modern API going forward, one the deprecated API provided for
backward compatibility.

[Colin Yates]

Perfect.

--- Original Message ---

--
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clo...@googlegroups.com
Note that posts from new members are moderated - please be patient with your first post.
To unsubscribe from this group, send email to
clojure+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---

You received this message because you are subscribed to a topic in the Google Groups "Clojure" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/clojure/4vx6rlBdrX8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to clojure+u...@googlegroups.com.