Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Tue, Apr 26, 2011 at 10:13 AM, Sean Corfield seancorfi...@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.
--
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)
--
You received this message because you are subscribed to the Google
Groups Clojure group.
To post to this group, send email to clojure@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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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! -- 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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Tue, Apr 26, 2011 at 2:16 PM, lispnik burnsid...@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. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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. Stu Stuart Halloway Clojure/core http://clojure.com -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
Good point. Issue added to track that. On Wed, Apr 27, 2011 at 11:58 AM, Stuart Halloway stuart.hallo...@gmail.com wrote: 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. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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? -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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? 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 :) -- 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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Mon, Apr 25, 2011 at 12:23 PM, Michael michael-a...@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. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On 23 April 2011 23:53, Nicolas Buduroi nbudu...@gmail.com wrote: 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. MySQL can be configured to accept double quotes too, but yes, by default it uses back ticks. -- Michael Wood esiot...@gmail.com -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Fri, Apr 22, 2011 at 10:32 PM, Shantanu Kumar kumar.shant...@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? -- 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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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.
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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. -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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.shant...@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 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)
--
You received this message because you are subscribed to the Google
Groups Clojure group.
To post to this group, send email to clojure@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+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Sat, Apr 23, 2011 at 10:01 AM, Stuart Sierra the.stuart.sie...@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 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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Sat, Apr 23, 2011 at 10:13 AM, Nicolas Buduroi nbudu...@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? -- 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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
On Sat, Apr 23, 2011 at 1:37 PM, Nicolas Buduroi nbudu...@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? -- 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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 s...@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 seancorfi...@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) -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
Re: clojure.contrib.sql = clojure.java.jdbc - looking for feedback!
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 -- You received this message because you are subscribed to the Google Groups Clojure group. To post to this group, send email to clojure@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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
