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)
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?
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.
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.
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.
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?
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?
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?
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?
MySQL can be configured to accept double quotes too, but yes, by
default it uses back ticks.
--
Michael Wood <esio...@gmail.com>
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.
Also see related problems reported by others:http://osdir.com/ml/clojure/2010-10/msg00290.html (sorry, can't findit 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?
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 :)
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 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?
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 :)
...Liquibase so I can get rollbacks and branching easily.
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.
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.
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
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.
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!