Hi Ray,
Although the sql-phrasebook does capture much of the same philosophy
behind Yesql (and has the excellent feature that we can store multiple SQL
queries in the same file), its syntax is somewhat more unpleasant to my
eyes than Yesql's macro api.
yesql
-----------------
(defquery users-by-country "some/where/users_by_country.sql")
(users-by-country db-spec "GB")
sql-phrasebook
-----------------
(def pb (sql-phrasebook "test/phrasebook.sql"))
(jdbc/query my-db (pb "select-users-by-id" {:user-ids [123 456 789]}))
I actually do really appreciate having the SQL queries in their own file
because I do a lot of Literate Programming, and being able to simply tangle
all my SQL code blocks into a single SQL file and then repurpose them from
my Clojure app is extremely inviting.
This ability to separate out the SQL from my Clojure and pull it in a la
carte to manipulate in my running app is one of those great breakthrough
features that I think makes Enlive and Enfocus such awesome tools for
collaborating with a team of non-Clojure developers.
My 2c,
~Gary
P.S. Kris, perhaps you could adjust the defquery syntax to take a key for
the SQL query you want to load from your external file. This could be part
of the comment line prepended to the queries that you already use to
autogenerate docstrings.
(defquery users-by-country "some/where/users_by_country.sql" :that-cool-query)
On Monday, November 18, 2013 6:26:05 AM UTC-5, Ray Miller wrote:
>
> Hi Gary et al,
>
> On 15 November 2013 23:55, Gary Johnson <gwjo...@uvm.edu <javascript:>>wrote:
>
>> I really like your approach, but I've also been feeling the burn a bit
>> with having to store each SQL query in its own file (especially since my
>> data exploration frequently leaves me cataloging hundreds of them). Take a
>> look at the approach used in sql-phrasebook:
>>
>> https://github.com/ray1729/sql-phrasebook
>>
>> I'm hoping it will give you some ideas to extend Yesql to address this
>> particular issue, ideally with a nicer syntax than that used in
>> sql-phrasebook. Otherwise, I think Yesql is bound for great things!
>>
>>
> Is it the syntax of the phrasebook you don't like, or the API for the
> library?
>
> This was just something I put together quickly, so there's a lot of room
> for improvement. The phrasebook parser is very simplistic and, although it
> works for the simple cases where I've used it, I'm concerned it's not
> robust. It's trivial to change the place-holder syntax from ${var-name} if
> that's the problem.
>
> My biggest concern is more philosophical. Although there are advantages to
> defining the queries in an SQL file, the code where you use them needs to
> know the details of the query (what columns does it return? what bind
> parameters are needed? is any post-processing aggregation needed?) So I
> don't like the distance between the Clojure and SQL implied by a
> phrasebook-based approach.
>
> My current thinking is to do something like:
>
> (defselect select-user
> "SELECT user.* FROM user
> WHERE user_id = {{user_id}}")
>
> with appropriate syntactic sugar. This would return a function of a
> database connection, which would return the rows. The next step would be to
> support a post-processing option for the cases where you modify the
> returned results in some way:
>
> (defselect select-user-roles
> "SELECT user.*, role.name AS role_name
> FROM user
> LEFT OUTER JOIN user_role USING(user_id)
> LEFT OUTER JOIN role USING(role_id)
> ORDER BY user.user_id"
> :post-process (fn [rows] (map (fn [user-rows]
> (assoc (select-keys
> (first user-rows) [:user_id :user_name])
> :roles (map
> :role_name user-rows))
> (partition-by :user_id rows)))
>
> That way, the post-processing of the results is kept close to the query.
>
> Just some food for thought...
>
> Ray.
>
--
--
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
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to clojure+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
