Re:ClojureQL - 1.0.0 now in beta
by andreas_bak on Thursday November 18, 2010 @06:49PM
(#1919826)
1. Why to write an ad-hoc sql parser in Clojure. The result of query is a JDBC ResultSet (on JVM). You may define some functions that transform rows from ResultSet to Clojure terms in order to make your code shorter.
2. They are not simple functions. Compare this : (-> (select (table {} {:employees :p})(where (= :name "John")))(join (table {} {:employees :b})(where (= :p.manager :b.id)))to-sql)
to this :
SELECT p.,b. FROM employees p JOIN employees b ON (p.manager = b.id) WHERE (name = 'John')
Which one is more verbose ?
EDIT: SQL examples with "employees" and "departmets" are very misleading and usually oversimplify the reality.
Re:ClojureQL - 1.0.0 now in beta
by swannodette on Thursday November 18, 2010 @07:02PM
(#1919863)
But your missing the point. You can store parts of queries and reuse them, recombine them however you see fit:
(def users (cql/table db :users))
(def photos (cql/table db :photos))
(def users-and-photos
(cql/join users photos
(cql/where (= :users.id :photos.user_id))))
(def photo-titles
(-> users-and-photos
(cql/project #{:photos.title})))
Note that my queries just keep getting smaller and smaller ;) By writing the mundane portions of your SQL queries again and again by hand you will have more duplication.
Re:ClojureQL - 1.0.0 now in beta
by andreas_bak on Thursday November 18, 2010 @07:16PM
(#1919902)
Yes I agree. But, I believe that it is just a perversion. In any project that involves RDBMS writing queries is just a small part of development. Surely you could modify queries with Clojure-QL (with SQL it will be a suicide) but what you gain from it ??? (more free time - I don't think so).
Furthermore, having such dynamic "meta-"things in your code, eventually will cost you more time in debugging it.
With SQL it is straight-forward: you prototype your query using some GUI client and then you just embed it in your program. How it can be done with Clojure-QL ?
if the point is length of code, then:
SELECT p.*,b.* FROM employees p JOIN employees b ON (p.manager = b.id) WHERE (p.name = 'John')
is only 8 characters less than: (join (select (table {:employees :p}) (=* :name "John")) (table {:employees :b}) (=* :p.manager :b.id))
but that isn't the point. the point is composabilty. you can take 8 simple functions (select, project, join, rename, aggregate, take, drop, sort) and build any query you want. these queries can be passed around your code, reused, modified, and refined, in a way that plain SQL strings cannot.
Instead of writing an ad-hoc SQL parser and doing string manipulation, you can write relatively simple Clojure functions to manipulate these lists and generate valid SQL.