clojure.contrib.sql => clojure.java.jdbc - looking for feedback!

705 views
Skip to first unread message

Sean Corfield

unread,
Apr 22, 2011, 5:50:58 PM4/22/11
to clo...@googlegroups.com
As part of the 1.3.0 release process, several of the old
clojure.contrib.* libraries are being picked up and "promoted" into a
new hierarchy of namespaces which you can see here:

https://github.com/clojure

The discussion / rationale is here:

http://dev.clojure.org/display/design/Contrib+Library+Names

I'm going to be working on clojure.java.jdbc, with Steve Gilardi, and
I'd like to get people's feedback on what you like / what you don't
like / what you need changed or added in clojure.contrib.sql so that
clojure.java.jdbc can become what the community wants for 1.3.0.

Feel free to provide feedback here on-list, directly to me off-list at
se...@corfield.org (not to this Gmail address please - I only use Gmail
for lists) or feel free to contact me via IM:

seancorfield on AIM / Skype / Twitter
seanco...@gmail.com on Gtalk

I can also be found on #clojure on freenode (as seancorfield).

Thanx in advance!
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/
Railo Technologies, Inc. -- http://www.getrailo.com/

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

Shantanu Kumar

unread,
Apr 23, 2011, 1:32:53 AM4/23/11
to Clojure
> I'm going to be working on clojure.java.jdbc, with Steve Gilardi, and
> I'd like to get people's feedback on what you like / what you don't
> like / what you need changed or added in clojure.contrib.sql so that
> clojure.java.jdbc can become what the community wants for 1.3.0.

Thanks for asking -- personally I think c.c.sql has a lot to improve
on the configuration aspect (some of which I have started
incorporating here: https://bitbucket.org/kumarshantanu/clj-dbspec/src
). Listed below:

1. Allow _ (underscore) in field names
2. Provide a mechanism to show the SQL being executed (configurable,
so that it can be turned off)
3. Allow users to flexibly convert from Clojure name to DB entity name
4. Allow users to flexibly convert from DB entity name to Clojure name
(this affects resultset-seq)
5. Provide a mode to prevent write operations

DB entity name can be table name, column name, schema name etc.

Besides configuration, I think it can benefit from the following:

6. An INSERT function that returns the generated key(s)
7. The function for creating tables is non-portable across databases
-- make it known in the docstring

I am sure others will have additional points to share. I would suggest
the configuration aspect be split into a separate library from
c.c.sql. Even more importantly, I think it needs a certain degree of
incubation before being promoted to clojure.java.jdbc.

Regards,
Shantanu

Sean Corfield

unread,
Apr 23, 2011, 2:54:08 AM4/23/11
to clo...@googlegroups.com
On Fri, Apr 22, 2011 at 10:32 PM, Shantanu Kumar
<kumar.s...@gmail.com> wrote:
> Listed below:

Thank you!

> 1. Allow _ (underscore) in field names

This already seems to work. I just tested the following - can you elaborate:

(deftest test-create
(jdbc/with-connection (worldsingles-db)
(jdbc/create-table :jdbcTestTable [:id :int] [:name_first
"varchar(32)"] ["name_last" "varchar(32)"])))

It created the following table:

mysql> desc jdbcTestTable;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name_first | varchar(32) | YES | | NULL | |
| name_last | varchar(32) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

I agree that translating - / _ would be more idiomatic. I'm not sure
it needs to be more sophisticated than that tho'...

> 2. Provide a mechanism to show the SQL being executed (configurable,
> so that it can be turned off)

Good idea. Even better, a way to access statistics about the prepared
statement after execution - timing etc?

> 3. Allow users to flexibly convert from Clojure name to DB entity name

Right now you can specify :name or "name" in arguments. Records come
back with entity names as keywords. Could you elaborate on what you'd
want to see here, beyond the - / _ translation mentioned above?

> 4. Allow users to flexibly convert from DB entity name to Clojure name
> (this affects resultset-seq)

As per 3. could you give an example use case? I saw what your library
provides but I'm not entirely sure how many folks would need that
flexibility. What do others think?

> 5. Provide a mode to prevent write operations

Interesting idea but isn't that better handled by grants on the user
accessing the database?

> 6. An INSERT function that returns the generated key(s)

The current clojure.java.jdbc does this (I added a first cut of it as
part of the initial new version but it needs some streamlining). I was
disappointed the original c.c.sql just returned nil from insert
operations so I changed it to return a sequence of vector pairs
containing update counts and the resultset-seq map from the generated
keys. It's ugly right now so I'm looking for input there.
* Do we need the update counts?
* Do we need the map?
* Or just the key values?
* For multiple inserts, do we want to return a sequence of generated
keys, one per insert?
* Should there be a specific method to insert (one set of values | one
record) that returns keys?
* Or should it simply default to that for insert operations that have
a (single set of values | single record).

My personal feeling is that it should return just the key values, in a
vector, one per inserted record, with an option to turn it off, per
insert operation). Maybe as a convenience, if you only insert one
record, you get back just a key, rather than a vector of one key?

> 7. The function for creating tables is non-portable across databases
> -- make it known in the docstring

Or work hard to make it more portable :)

To me, a bigger problem is that c.j.j doesn't strop the entity names
so you can't have, e.g., columns named like SQL keywords. That will
definitely get fixed because the application I'm working on has such
column names. Of course stropping varies across database vendors...

> I am sure others will have additional points to share. I would suggest
> the configuration aspect be split into a separate library from
> c.c.sql. Even more importantly, I think it needs a certain degree of
> incubation before being promoted to clojure.java.jdbc.

Could you elaborate on the deficiencies you see in the configuration
part of c.j.j? I think your library provides some nice syntactic sugar
on creating the DB spec but since most projects do it only once per
application, I'm not sure that needs to be in standard library?

Shantanu Kumar

unread,
Apr 23, 2011, 4:38:58 AM4/23/11
to Clojure
> > 1. Allow _ (underscore) in field names
>
> This already seems to work. I just tested the following - can you elaborate:

Yes, I tested this and it seems to work now. Not sure where I noticed
earlier where it was not working. My bad.

>
> I agree that translating - / _ would be more idiomatic. I'm not sure
> it needs to be more sophisticated than that tho'...
>
> > 2. Provide a mechanism to show the SQL being executed (configurable,
> > so that it can be turned off)
>
> Good idea. Even better, a way to access statistics about the prepared
> statement after execution - timing etc?

Yes, that would be an add-on value to show how are the queries
performing.

>
> > 3. Allow users to flexibly convert from Clojure name to DB entity name
>
> Right now you can specify :name or "name" in arguments. Records come
> back with entity names as keywords. Could you elaborate on what you'd
> want to see here, beyond the - / _ translation mentioned above?
>
> > 4. Allow users to flexibly convert from DB entity name to Clojure name
> > (this affects resultset-seq)
>
> As per 3. could you give an example use case? I saw what your library
> provides but I'm not entirely sure how many folks would need that
> flexibility. What do others think?

Some databases (under certain configuration) work only with case-
sensitive entity names. Converting them to all lowercase breaks under
those situations. Also see related problems reported by others:
http://osdir.com/ml/clojure/2010-10/msg00290.html (sorry, can't find
it on the group.)

>
> > 5. Provide a mode to prevent write operations
>
> Interesting idea but isn't that better handled by grants on the user
> accessing the database?

The intention here is to proactively prevent errors from a development
standpoint. And then, some databases do not support permissions --
however, the notion of read-only vs writes is common in database
development and can be useful to have.

>
> > 6. An INSERT function that returns the generated key(s)
>
> The current clojure.java.jdbc does this (I added a first cut of it as
> part of the initial new version but it needs some streamlining). I was
> disappointed the original c.c.sql just returned nil from insert
> operations so I changed it to return a sequence of vector pairs
> containing update counts and the resultset-seq map from the generated
> keys. It's ugly right now so I'm looking for input there.
> * Do we need the update counts?
> * Do we need the map?
> * Or just the key values?
> * For multiple inserts, do we want to return a sequence of generated
> keys, one per insert?
> * Should there be a specific method to insert (one set of values | one
> record) that returns keys?
> * Or should it simply default to that for insert operations that have
> a (single set of values | single record).
>
> My personal feeling is that it should return just the key values, in a
> vector, one per inserted record, with an option to turn it off, per
> insert operation). Maybe as a convenience, if you only insert one
> record, you get back just a key, rather than a vector of one key?

Some databases allow multiple auto-generated columns in a table.
Inserts to such tables generates multiple keys - it probably makes
sense to return them as a map. So, my suggestion: return a map {column
=> generated-value} per `insert`, and for `update-or-insert` it should
return just an empty map {} when it's actually an update. Again, batch-
inserts do not return generated keys on all JDBC drivers (notable
exception is MySQL, on which it does).

>
> > 7. The function for creating tables is non-portable across databases
> > -- make it known in the docstring
>
> Or work hard to make it more portable :)

How? :) Lowest common-denominator is not what a customer would pay the
database vendor for, and it's difficult to incorporate syntaxes for
major-if-not-all databases (which would still be non-portable).
Probably a reasonable set of types that map respectively to different
databases? What about indexes, primary keys, constraints etc? This
calls for a separate, dedicated library IMHO.

> > I am sure others will have additional points to share. I would suggest
> > the configuration aspect be split into a separate library from
> > c.c.sql. Even more importantly, I think it needs a certain degree of
> > incubation before being promoted to clojure.java.jdbc.
>
> Could you elaborate on the deficiencies you see in the configuration
> part of c.j.j? I think your library provides some nice syntactic sugar
> on creating the DB spec but since most projects do it only once per
> application, I'm not sure that needs to be in standard library?

The point here is to setup a generic configuration mechanism for
database libraries beyond c.j.j so that they can hook up their own
functions and intercept when necessary. As long as the configuration
can be contained in a map, I think it can serve libraries as well. Not
sure if the configuration mechanism should always be accompanied with
c.j.j hence my suggestion -- please consider it more of food for
thought rather than recommendation.

Regards,
Shantanu

Stuart Sierra

unread,
Apr 23, 2011, 1:01:24 PM4/23/11
to Clojure
One snag I ran into recently with c.c.sql: it creates structmaps for
returning query results, which means you can't subsequently dissoc one
of the keys without converting to a regular map.

In general, c.c.sql is inconsistent about using tuples of column
values versus maps of column=>value pairs. Both are useful, and I'd
like to see versions of all the core functions that support both
modes.

Thanks for taking this on!

-Stuart Sierra
clojure.com

Nicolas Buduroi

unread,
Apr 23, 2011, 1:13:37 PM4/23/11
to clo...@googlegroups.com
I'm going to be working on clojure.java.jdbc, with Steve Gilardi, and
I'd like to get people's feedback on what you like / what you don't
like / what you need changed or added in clojure.contrib.sql so that
clojure.java.jdbc can become what the community wants for 1.3.0.

What I would like to see added is a global connection mechanism like in ClojureQL and Lobos. We could even add a connection pool behind the scene.

Then there's support for quoted identifiers, maybe something like:

  (with-quoted-identifiers \" ...)

And for qualified identifiers we could let c.j.j accept vectors of identifiers so that [:some_schema :a_table] would become "some_schema.a_table".

I could provide an implementation if you'd like.

Sean Corfield

unread,
Apr 23, 2011, 3:59:44 PM4/23/11
to clo...@googlegroups.com
I've added an issue for getting access to generated SQL and possibly
execution statistics.

On Sat, Apr 23, 2011 at 1:38 AM, Shantanu Kumar
<kumar.s...@gmail.com> wrote:
> Some databases (under certain configuration) work only with case-
> sensitive entity names. Converting them to all lowercase breaks under
> those situations.

It might have converted entities to lowercase at some point but it
doesn't seem to now, at least as far as the SQL is concerned - but see
next comment.

> Also see related problems reported by others:
> http://osdir.com/ml/clojure/2010-10/msg00290.html (sorry, can't find
> it on the group.)

Yes, resultset-seq does lowercase the column names and it doesn't
translate between - / _ either. But that's not part of c.j.j so,
whilst I may agree with the criticisms of it, I can't actually fix
that :)

Perhaps someone from Clojure/core could speak to resultset-seq's behavior?

> Some databases allow multiple auto-generated columns in a table.
> Inserts to such tables generates multiple keys - it probably makes
> sense to return them as a map. So, my suggestion: return a map {column
> => generated-value} per `insert`, and for `update-or-insert` it should
> return just an empty map {} when it's actually an update.

The current c.j.j behavior is to return a seq of vectors; each vector
contains the update count and, if > 0, a map of the generated keys. If
I restrict returning generated keys to single inserts, then I can just
return a map of keys. If I allow for multiple inserts, I have to keep
the update counts because if one of the set of inserts does not
generate keys, you would need a way to detect that. Overall, I think
simplicity leans toward only returning generated keys, as a simple
map, for a single insert operation.

Then the only question is whether inserts should automatically attempt
to return a map of generated keys when there is a single insert. I'd
lean to making that the default and, if people care, providing an
optional argument to suppress it. Thoughts?

>> > 7. The function for creating tables is non-portable across databases
>> > -- make it known in the docstring
>> Or work hard to make it more portable :)
> How? :) Lowest common-denominator is not what a customer would pay the
> database vendor for, and it's difficult to incorporate syntaxes for
> major-if-not-all databases (which would still be non-portable).

Well, create-table does minimal transformation on its arguments so you
can specify:

(create-table "adminUser"
[
["id" "int(11)" "not null" "auto_increment"]
...
["primary key" "(id)"]])

And that will generate:

CREATE TABLE adminUser (
id int(11) not null auto_increment,
...,
primary key (id)
)

What you cannot do is provide any options after the columns specs -
that's definitely a weakness. I've added an issue for that.

I'd definitely be interested in suggestions for cleaner ways to
specify column specs (although I'm more concerned with general queries
than DDL operations).

> Probably a reasonable set of types that map respectively to different
> databases? What about indexes, primary keys, constraints etc? This
> calls for a separate, dedicated library IMHO.

It may be a good idea to separate out the DDL operations into a
sub-library. Thoughts from others? clojure.java.jdbc.ddl perhaps?

> The point here is to setup a generic configuration mechanism for
> database libraries beyond c.j.j so that they can hook up their own
> functions and intercept when necessary. As long as the configuration
> can be contained in a map, I think it can serve libraries as well. Not
> sure if the configuration mechanism should always be accompanied with
> c.j.j hence my suggestion -- please consider it more of food for
> thought rather than recommendation.

If such a mechanism were added for c.j.j, I think it would have to be
in effect for resultset-seq too since one of the behaviors I think
people will want to modify is the mapping to / from entity names,
right?

I guess I need to see a more concrete example before I really grok
what you're suggesting here.

Sean Corfield

unread,
Apr 23, 2011, 4:05:24 PM4/23/11
to clo...@googlegroups.com
On Sat, Apr 23, 2011 at 10:01 AM, Stuart Sierra
<the.stua...@gmail.com> wrote:
> One snag I ran into recently with c.c.sql: it creates structmaps for
> returning query results, which means you can't subsequently dissoc one
> of the keys without converting to a regular map.

That's down to resultset-seq - should c.j.j stop relying on that
function and have its own, more flexible version?

> In general, c.c.sql is inconsistent about using tuples of column
> values versus maps of column=>value pairs. Both are useful, and I'd
> like to see versions of all the core functions that support both
> modes.

I agree. As I was working with the functions early on I bumped into
that a few times. I'll have a think about how to make that more
consistent and ask for feedback on it.

Sean Corfield

unread,
Apr 23, 2011, 4:15:57 PM4/23/11
to clo...@googlegroups.com
On Sat, Apr 23, 2011 at 10:13 AM, Nicolas Buduroi <nbud...@gmail.com> wrote:
> What I would like to see added is a global connection mechanism like in
> ClojureQL and Lobos. We could even add a connection pool behind the scene.

Thanx. I'll take a look at those.

> Then there's support for quoted identifiers, maybe something like:

Agreed.

> And for qualified identifiers we could let c.j.j accept vectors of
> identifiers so that [:some_schema :a_table] would become
> "some_schema.a_table".

Should [:some-schema :a-table] become "some_schema.a_table"?

How do people want to handle mixed case entity names? Do we want
something like Hibernate Naming Strategies?

Nicolas Buduroi

unread,
Apr 23, 2011, 4:37:07 PM4/23/11
to clo...@googlegroups.com
On Saturday, 23 April 2011 16:15:57 UTC-4, Sean Corfield wrote:
Should [:some-schema :a-table] become "some_schema.a_table"?

How do people want to handle mixed case entity names? Do we want
something like Hibernate Naming Strategies?

I hadn't thought about naming strategies that could be a great idea! It would the best for everybody and it wouldn't impose some arbitrary name transformation. It could be implementation as a simple macro:

    (with-naming-strategy #(string/replace ...)
       ...)
 
After rethinking about quoted identifiers, I realized they would probably need an extra argument to escape the quotes found inside an identifier if there are some.

Sean Corfield

unread,
Apr 23, 2011, 5:33:48 PM4/23/11
to clo...@googlegroups.com
On Sat, Apr 23, 2011 at 1:37 PM, Nicolas Buduroi <nbud...@gmail.com> wrote:
> After rethinking about quoted identifiers, I realized they would probably
> need an extra argument to escape the quotes found inside an identifier if
> there are some.

Well, not all DB stropping approaches are simply (str q ident q) -
there's also select [name] from [table] style quoting and that part is
DB vendor specific I believe?

Nicolas Buduroi

unread,
Apr 23, 2011, 5:53:07 PM4/23/11
to clo...@googlegroups.com
On Saturday, 23 April 2011 17:33:48 UTC-4, Sean Corfield wrote:
Well, not all DB stropping approaches are simply (str q ident q) -
there's also select [name] from [table] style quoting and that part is 
DB vendor specific I believe?

Oh yeah, I forgot SQL Server again!

(with-quoted-identifiers \" escape-fn ...)
(with-quoted-identifiers [\[ \]] escape-fn ...)

Else there's just MySQL which use the backquote "`" and, if I'm not mistaken, modern SQL Server versions also accept the double-quote.

Shantanu Kumar

unread,
Apr 24, 2011, 4:30:22 AM4/24/11
to Clojure
Just to point out what's not supported by resultset-seq:

1. Flexible conversion of column label to a Clojure equivalent.

2. Handling of duplicate column labels in a resultset. For example,
this is a perfectly valid SQL statement in MySQL:
SELECT productId, productId+4 as productId FROM `t_product`
This is mainly limited by the fact that maps do not allow duplicate
keys. While it is debatable whether writing such queries is a good
idea, often the person running the query is not the same as the
person(s) wrote them. Fixing this problem may require creating a new
protocol/type. An experiment is here:
https://bitbucket.org/kumarshantanu/clj-dbspec/src/c0b6797faaec/src/main/clj/org/bituf/clj_dbspec.clj#cl-329
(see upto row-seq)

It may be a good idea to build a complete replacement for resultset-
seq in c.j.j.

Regards,
Shantanu

Michael Wood

unread,
Apr 24, 2011, 8:41:51 AM4/24/11
to clo...@googlegroups.com

MySQL can be configured to accept double quotes too, but yes, by
default it uses back ticks.

--
Michael Wood <esio...@gmail.com>

Michael

unread,
Apr 25, 2011, 12:23:43 PM4/25/11
to Clojure

We've been using iBatis (http://ibatis.apache.org/) to compose sql
fragments and map to java objects. iBatis has since forked from Apache
to become mybatis (http://www.mybatis.org/). With iBatis, you can use
XML to attach an identifier to sql fragments. You can build up sql
expressions by referencing the fragments by id and also use simple
logic. The XML is cumbersome, but seemed nicer than java strings to
DRY out the sql.

I was wondering if c.j.jdbc could provide some help in composing sql
fragments, but I'm not sure what form it should take or if core
clojure would suffice. We would have looked into ClojureQL, but it
doesn't directly support Oracle. Would also be curious to know how
people use clojure to compose sql fragments.

Nicolas Buduroi

unread,
Apr 25, 2011, 12:45:17 PM4/25/11
to clo...@googlegroups.com
On Mon, Apr 25, 2011 at 12:23 PM, Michael <michae...@db.com> wrote:
I was wondering if c.j.jdbc could provide some help in composing sql
fragments, but I'm not sure what form it should take or if core
clojure would suffice. We would have looked into ClojureQL, but it
doesn't directly support Oracle. Would also be curious to know how
people use clojure to compose sql fragments.

For Lobos I've written a compiler which transform an AST into SQL DDL statements. You can have a look at the compiler here:

https://github.com/budu/lobos/blob/master/src/lobos/compiler.clj

It's based on the legacy ClojureQL project, I've written some helpers but it's mostly using Clojure string facilities. So I'm not sure it would be a good idea to include such helpers inside c.j.j, outside the as-identifier function here:

http://dev.clojure.org/jira/browse/CLJ-778

As for the new ClojureQL project, it would certainly be feasible to add support for Oracle, but I find it hard to work with its current "simplified" compiler. I've tried to add stropping to ClojureQL multiple times, but gave up in the end.

Stuart Halloway

unread,
Apr 26, 2011, 7:46:44 AM4/26/11
to clo...@googlegroups.com
Also see related problems reported by others:
http://osdir.com/ml/clojure/2010-10/msg00290.html (sorry, can't find
it on the group.)

Yes, resultset-seq does lowercase the column names and it doesn't
translate between - / _ either. But that's not part of c.j.j so,
whilst I may agree with the criticisms of it, I can't actually fix
that :)

Perhaps someone from Clojure/core could speak to resultset-seq's behavior?

I don't want to make a breaking change to the existing API, but in a world there there is an actively-maintained clojure.java.jdbc I don't think a resultset function in core makes a lot of sense anyway.

How about we mark core's resultset-seq as deprecated, with a link to the new project? Then c.j.j. can do a better resultset-seq, and we will leave the old fn in core for at least on major release cycle. 

Sound ok?

Stu

Stuart Halloway
Clojure/core
http://clojure.com

David Powell

unread,
Apr 26, 2011, 9:19:21 AM4/26/11
to clo...@googlegroups.com

Yes, resultset-seq does lowercase the column names and it doesn't
translate between - / _ either. But that's not part of c.j.j so,
whilst I may agree with the criticisms of it, I can't actually fix
that :)
There is justification for resultset-seq's current behaviour, even if it isn't to everyone's preference.  

Down-casing the column names ensures that comparisons done in clojure are done without regard to case, just as they would be in SQL.  For example, queries of two views can be joined in clojure, even if the author of the view hasn't deliberately matched the case of all the column names.  Typically I find queries and views are not consistent in case because in SQL there is no requirement for them to be.  I don't want to have to convince database people to change their code because I'm using a 'weird' tool to process the results.

 
I don't want to make a breaking change to the existing API, but in a world there there is an actively-maintained clojure.java.jdbc I don't think a resultset function in core makes a lot of sense anyway.

How about we mark core's resultset-seq as deprecated, with a link to the new project? Then c.j.j. can do a better resultset-seq, and we will leave the old fn in core for at least on major release cycle. 

I use resultset-seq everywhere.  I don't mind us deprecating it, if a better version is available elsewhere (but please allow the option of down-casing); but is there really any need to remove a working function from core?  I'm not keen on introducing gratuitous back-compat issues.

Can we arrange for the deprecated core version to call the c.j.j version, passing any options to preserve current behaviour as much as possible, and to fail at runtime if that library is not present?


Stuart Halloway

unread,
Apr 26, 2011, 10:00:14 AM4/26/11
to clo...@googlegroups.com
I don't want to make a breaking change to the existing API, but in a world there there is an actively-maintained clojure.java.jdbc I don't think a resultset function in core makes a lot of sense anyway.

How about we mark core's resultset-seq as deprecated, with a link to the new project? Then c.j.j. can do a better resultset-seq, and we will leave the old fn in core for at least on major release cycle. 

I use resultset-seq everywhere.  I don't mind us deprecating it, if a better version is available elsewhere (but please allow the option of down-casing); but is there really any need to remove a working function from core?  I'm not keen on introducing gratuitous back-compat issues.

That's why we ask before doing it. :-) The counter-pressures are overall footprint of core, and the potential for library compatibility issues on semi-Java platforms such as android. That said, these issues are hypothetical at this point.

Can we arrange for the deprecated core version to call the c.j.j version, passing any options to preserve current behaviour as much as possible, and to fail at runtime if that library is not present?

That seems complicated. If removal is going to cause heartburn, we could deprecate without ever removing. 

Sean Corfield

unread,
Apr 26, 2011, 1:13:55 PM4/26/11
to clo...@googlegroups.com
On Tue, Apr 26, 2011 at 6:19 AM, David Powell <djpo...@djpowell.net> wrote:
> There is justification for resultset-seq's current behaviour, even if it
> isn't to everyone's preference.

Agreed. And I would actually want the lowercasing behavior to remain
the default, for my own use anyway. What irks me more is the lack of
translation between foo_bar and :foo-bar or, for folks who camelCase
column names, between fooBar and :foo-bar perhaps. This all goes back
to the idea of a "naming strategy" for translating between Clojure
keywords and SQL entity names.

I think at this point it makes sense to add a function to c.j.j that
mimics the current resultset-seq functionality but allows for the
application of naming strategies - with the default being the current
behavior, and some other standard strategies available.

The question then is whether the c.j.j function should also be called
resultset-seq or whether a new name should be picked?

However, c.j.j functions yield the _result_ of resultset-seq so
perhaps it doesn't even need to expose the function itself? On the
other hand, if c.j.j supports naming strategies to provide different
keyword/entity translations, users may want to be able to apply the
same translations to any raw resultSet objects they have... Thoughts?

> Can we arrange for the deprecated core version to call the c.j.j version,
> passing any options to preserve current behaviour as much as possible, and
> to fail at runtime if that library is not present?

It's a relatively small function so it should stay as-is in core - it
should not depend on c.j.j. As Stuart notes, deprecation doesn't
necessarily mean it will go away, just that users are discouraged from
using it. One of the other languages I work with deprecated some
functions about a decade ago but those functions are still present
nearly half a dozen releases later :)

lispnik

unread,
Apr 26, 2011, 2:16:46 PM4/26/11
to clo...@googlegroups.com
Thanks for the feedback request.

When I use clojure.contrib.sql, I use it for its DML only -- no create/drop table, etc. I usually manage the DDL outside of Clojure base using (for example) Liquibase so I can get rollbacks and branching easily. Perhaps DML can be factored out into a separate namespace? Also, I am not interested in building statements in Clojure sexps, for example I am quite happy using clojure.contrib.sql like this, which I personally find readable:

  (with-connection *connection*
...
        (do-prepared "
insert into password_reset (user_id, token)
    select id, ? from user where user.email = ?
on duplicate key
    update token = ?, creation_date = now()"
                     [token username token])
        token)))

I suggest not getting into the SQL in sexps/naming strategies business.  That kind of thing doesn't really fall under the scope of JDBC, which clojure.java.jdbc should be modeled around.

update-or-insert-values can probably be removed as it currently contains a race condition depending on the transaction isolation level.  One has to use a vendor specific feature (on duplicate key update in MySQL, merge in Oracle etc.).

The row as a structmap works well. 90% of the time I use use "select foo_bar as \"foo-bar\"..." to get a Clojure-ish map key.  Other 10% I might also have to apply a transformation to the row, especially when working with legacy databases.  I thought having something like:

       (with-query-results rs transform-fn ["
..." params...]
         (first rs))

So that by the time I call (first rs), transform has already been applied, but (map transform-fn rs) is trivial enough also.

Nicolas Buduroi

unread,
Apr 27, 2011, 12:00:14 PM4/27/11
to clo...@googlegroups.com
On Tue, Apr 26, 2011 at 2:16 PM, lispnik <burns...@gmail.com> wrote:
...Liquibase so I can get rollbacks and branching easily.

Off-topic question: What does branching mean in the context of Liquidbase?
 
I suggest not getting into the SQL in sexps/naming strategies business.  That kind of thing doesn't really fall under the scope of JDBC, which clojure.java.jdbc should be modeled around.

I'm with you on that one.
 
The row as a structmap works well. 90% of the time I use use "select foo_bar as \"foo-bar\"..." to get a Clojure-ish map key.  Other 10% I might also have to apply a transformation to the row, especially when working with legacy databases.  I thought having something like:

       (with-query-results rs transform-fn ["
..." params...]
         (first rs))

So that by the time I call (first rs), transform has already been applied, but (map transform-fn rs) is trivial enough also.


Is that good enough for you:

https://github.com/clojure/java.jdbc/issues/7#issuecomment-1060402

I could always add an extra optional argument to with-query-results.

Brian Carper

unread,
Apr 27, 2011, 2:45:17 PM4/27/11
to Clojure
On Apr 26, 10:13 am, Sean Corfield <seancorfi...@gmail.com> wrote:
> On Tue, Apr 26, 2011 at 6:19 AM, David Powell <djpow...@djpowell.net> wrote:
> > There is justification for resultset-seq's current behaviour, even if it
> > isn't to everyone's preference.
>
> Agreed. And I would actually want the lowercasing behavior to remain
> the default, for my own use anyway. What irks me more is the lack of
> translation between foo_bar and :foo-bar or, for folks who camelCase
> column names, between fooBar and :foo-bar perhaps. This all goes back
> to the idea of a "naming strategy" for translating between Clojure
> keywords and SQL entity names.
>
> I think at this point it makes sense to add a function to c.j.j that
> mimics the current resultset-seq functionality but allows for the
> application of naming strategies - with the default being the current
> behavior, and some other standard strategies available.
>
> The question then is whether the c.j.j function should also be called
> resultset-seq or whether a new name should be picked?
>
> However, c.j.j functions yield the _result_ of resultset-seq so
> perhaps it doesn't even need to expose the function itself? On the
> other hand, if c.j.j supports naming strategies to provide different
> keyword/entity translations, users may want to be able to apply the
> same translations to any raw resultSet objects they have... Thoughts?
>

I'd like to at least have the option of passing SQL query strings
verbatim to the SQL server and getting returned data verbatim as well,
with no auto-quoting or column name munging at all (including
downcasing). Name-munging can be implemented on top of functions that
return data and column names verbatim, but the opposite is not true.

--Brian

Stuart Halloway

unread,
Apr 27, 2011, 2:58:40 PM4/27/11
to clo...@googlegroups.com
I'd like to at least have the option of passing SQL query strings
verbatim to the SQL server and getting returned data verbatim as well,
with no auto-quoting or column name munging at all (including
downcasing).  Name-munging can be implemented on top of functions that
return data and column names verbatim, but the opposite is not true.

--Brian

+1. It should be a guiding principle that contribs provide the building blocks first, then the buildings.

Sean Corfield

unread,
Apr 27, 2011, 5:18:53 PM4/27/11
to clo...@googlegroups.com
Good point. Issue added to track that.

Mibu

unread,
Apr 29, 2011, 10:59:23 PM4/29/11
to clo...@googlegroups.com
I think adding the generic sub-namespaces java, data, algo, tools, etc. is unnecessary and confusing. How many libraries fit neatly in one of those categories and not the other? Why use clojure.data.json when clojure.json would suffice? More examples: clojure.cli, clojure.enlive, clojure.monads are all sufficient. Think of the confusion clojure.data.java is going to cause. Or was it clojure.java.data? clojure.* is not that crowded to justify another layer. Even with the few libs we have today I still need to look up what lines I need to add to my ns.

Namespaces while important, IMHO, are the most confusing part of basic clojure coding for new users, and they are a bureaucratic annoyance to everyone else. Over here we were very excited to hear about slamhound, which is saying something if there's a need/desire for an external tool to handle simple ns declarations.

I think the right direction should be simplifying namespaces and contrib libs as much as possible. Consolidating contrib libs is great. Eliminating duplicate names in common contrib libraries so they can be :used together without exceptions would also be great (kind of defeats the purpose of ns, though, but still). It would be even greater if some of the more core-ish functions from string and io and other contribs would finally graduate to core. I don't think core should become an all-inclusive bloated mess, but it's my feeling that right now some of the essential vocabulary found in contrib is missing from core.

Maybe revamp the ns macro for something more intuitive with less boilerplate. Maybe get rid of :use, :require, :import, etc. and try something like:
(ns my-ns
  clojure.repl ;for :use/:import
  [clojure.sql :as sql] ;for :require
  java.net.URL ;for :importing single classes
  [java.util.concurrent TimeUnit ExecutionException]) ;for :importing multiple classes

Sean Corfield

unread,
May 2, 2011, 7:21:19 PM5/2/11
to clo...@googlegroups.com
On Tue, Apr 26, 2011 at 10:13 AM, Sean Corfield <seanco...@gmail.com> wrote:
> I think at this point it makes sense to add a function to c.j.j that
> mimics the current resultset-seq functionality but allows for the
> application of naming strategies - with the default being the current
> behavior, and some other standard strategies available.

This is done - along with all the options for quoting and naming
strategies. Thanx to Nicolas Buduroi for helpful pointers along the
way!

I've added some nascent documentation (which will ultimately show up
here http://clojure.github.com/java.jdbc/ but can be seen directly in
github here https://github.com/clojure/java.jdbc/tree/master/doc/clojure/java/jdbc
right now).

> The question then is whether the c.j.j function should also be called
> resultset-seq or whether a new name should be picked?

For now, c.j.j.internal/resultset-seq* is the name used (by c.j.j
itself) so the question still remains as to whether c.j.j should
expose it publicly - either as resultset-seq or as a new name.

The default behavior is unchanged but this new version respects the
naming strategy specified so you can control exactly how SQL entity
names are mapped to keywords and vice versa.

Please read this page for specifics:
https://github.com/clojure/java.jdbc/blob/master/doc/clojure/java/jdbc/NameMapping.md

Note in particular that passing "strings" into c.j.j means no
translation, only :keywords are translated when passed in. However,
resultset-seq* still always converts entity names to keywords (so that
the resulting maps are easy to work with. You can use
(with-naming-strategy { :keyword identity } ...) to override the
lowercasing and then, if you wish, a simple (map name ...) over the
returned maps will get you the original entity names back.

Justin Balthrop

unread,
May 2, 2011, 8:33:45 PM5/2/11
to clo...@googlegroups.com
Sean,

What are your thoughts on handling connection pooling in clojure.java.jdbc. We currently use c3po for connection pooling at Geni, but it seems that won't work as expected with the new shared thread bindings in Clojure 1.3. Since, the binding that holds the connection would be shared with child threads, the parent would be using the same connection as the children, which won't work since JDBC connections are not thread safe.

It seems to me that moving the connection pooling into clojure.java.jdbc would be the only way to save multithreaded code from having to deal with this complexity. Perhaps there is even some connection pooling code that could be factored out into a separate contrib library as this will be a common problem for any code that relied on non-shared bindings in 1.2 for thread safe connections.

Justin

Sean Corfield

unread,
May 2, 2011, 10:06:12 PM5/2/11
to clo...@googlegroups.com
On Mon, May 2, 2011 at 5:33 PM, Justin Balthrop
<jus...@justinbalthrop.com> wrote:
> What are your thoughts on handling connection pooling in clojure.java.jdbc.

If there's a way to provide it in a portable manner without undue
external dependencies, I'd be interested in exploring it but that gets
a bit outside my familiarity with Java's basic JDBC stuff.

My understanding is that if you use a db-spec with a key of :factory,
you can supply a function that returns connections and therefore, if
you have a pooled connection factory, you can already use it with
c.j.j (modulo the threading issue you mention).

Reminder: c.j.j can only accept code from folks with a signed CA on record!

Jim

unread,
May 19, 2011, 11:12:08 AM5/19/11
to Clojure
On Apr 23, 9:38 am, Shantanu Kumar <kumar.shant...@gmail.com> wrote:
<snip>
> > 5. Provide a mode to prevent write operations
>
> > Interesting idea but isn't that better handled by grants on the user
> > accessing the database?
>
> The intention here is to proactively prevent errors from a development
> standpoint. And then, some databases do not support permissions --
> however, the notion of read-only vs writes is common in database
> development and can be useful to have.

+1 for this feature - it would also be very handy to have when running
against replicated databases. e.g. the MySQL replication driver
directs write queries to the master and read-only queries across the
slaves based on the readOnly property of the connection.

<snip>

Best regards,
jim

Shantanu Kumar

unread,
May 25, 2011, 2:07:38 AM5/25/11
to Clojure


On Apr 22, 10:32 pm, Shantanu Kumar <kumar.shant...@gmail.com> wrote:
> > I'm going to be working on clojure.java.jdbc, with Steve Gilardi, and
> > I'd like to get people's feedback on what you like / what you don't
> > like / what you need changed or added in clojure.contrib.sql so that
> > clojure.java.jdbccan become what the community wants for 1.3.0.
>
> Thanks for asking -- personally I think c.c.sql has a lot to improve
> on the configuration aspect (some of which I have started
> incorporating here:https://bitbucket.org/kumarshantanu/clj-dbspec/src
> ). Listed below:
>
> 1. Allow _ (underscore) in field names
> 2. Provide a mechanism to show the SQL being executed (configurable,
> so that it can be turned off)
> 3. Allow users to flexibly convert from Clojure name to DB entity name
> 4. Allow users to flexibly convert from DB entity name to Clojure name
> (this affects resultset-seq)
> 5. Provide a mode to prevent write operations
>
> DB entity name can be table name, column name, schema name etc.
>
> Besides configuration, I think it can benefit from the following:
>
> 6. An INSERT function that returns the generated key(s)
> 7. The function for creating tables is non-portable across databases
> -- make it known in the docstring
>

8. Allow users to specify :fetch-size, :fetch-direction, :timeout etc
through the config-map and make functions honor that. I think a
default fetch-size of 1000 would be good for performance and more
predictable across databases:

http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html

Regards,
Shantanu
Reply all
Reply to author
Forward
0 new messages