Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Hi Carlo - I'm using the 2.0 snapshot. How should I express a join on multiple columns, ie SELECT blah FROM t1 JOIN t2 ON (t1.a=t2.b) AND (t1.c=t2.d) ? On Wednesday, July 3, 2013 1:48:07 AM UTC-7, Carlo wrote: Hey guys! I've been working on a small library to make writing SQL queries a little bit easier. It's along the same lines as ClojureQL, but takes a different approach and compiles into quite different SQL in the end. At the moment it's quite immature, but it should be able to support any queries which can be expressed in relational algebra. There will be some SQL queries which can't be expressed in clojure-sql, but hopefully there won't be too many of those. A greater limitation is that at the moment the SQL generation is specific to the PostgresSQL database (although any contributions for other databases are welcome!). Dependency vector: [clojure-sql 0.1.0] Repository: https://bitbucket.org/czan/clojure-sql Clojars link: https://clojars.org/clojure-sql Let me know what you think! Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Hi Mark! On Wed, Nov 20, 2013 at 01:11:11PM -0800, Mark wrote: I'm using the 2.0 snapshot. How should I express a join on multiple columns, ie SELECT blah FROM t1 JOIN t2 ON (t1.a=t2.b) AND (t1.c=t2.d) ? Something like this should work: (let [t1 (- (table :t1) (project [:a :c])) t2 (- (table :t2) (project [:b :d]))] (join t1 t2 :on `(and (= :a :b) (= :c :d If you've got shared attributes this won't work for you, though, because then there's an ambiguity about whether to use a natural join (ie. joining on equality of shared attributes) or your join condition (which will leave an ambiguity about which shared attribute to use). In that case you can use something like this: (let [t1 (- (table :t1) (project [:a :b])) t2 (- (table :t2) (project [:a :c]))] (join (rename t1 (as-subobject :t1)) (rename t2 (as-subobject :t2)) :on `(and (= :t1.a :t2.c) (= :t2.a :t1.b Sorry about the lack of documentation for this. I'm working on it, but I've been quite busy/distracted lately. Hopefully I'll have some more time to get back to it soon. Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Thanks, Carlo. Even without the documentation, I'm beginning to get the hang of the DSL. I should have guessed that '(and...) would have done the trick. I'd like to put a request in for using a map or a vector of pairs as an alternative since it's easier to construct those. One more request: Support for SELECT DISTINCT. Using 0.1.0, I could get around the lack of DISTINCT by using GROUP BY but with the new projection argument, that becomes inconvenvient. In our project, we do a ton of programmatic SQL manipulation and your library makes it much easier. Thanks for contribution! On Wednesday, November 20, 2013 7:54:19 PM UTC-8, Carlo wrote: Hi Mark! On Wed, Nov 20, 2013 at 01:11:11PM -0800, Mark wrote: I'm using the 2.0 snapshot. How should I express a join on multiple columns, ie SELECT blah FROM t1 JOIN t2 ON (t1.a=t2.b) AND (t1.c=t2.d) ? Something like this should work: (let [t1 (- (table :t1) (project [:a :c])) t2 (- (table :t2) (project [:b :d]))] (join t1 t2 :on `(and (= :a :b) (= :c :d If you've got shared attributes this won't work for you, though, because then there's an ambiguity about whether to use a natural join (ie. joining on equality of shared attributes) or your join condition (which will leave an ambiguity about which shared attribute to use). In that case you can use something like this: (let [t1 (- (table :t1) (project [:a :b])) t2 (- (table :t2) (project [:a :c]))] (join (rename t1 (as-subobject :t1)) (rename t2 (as-subobject :t2)) :on `(and (= :t1.a :t2.c) (= :t2.a :t1.b Sorry about the lack of documentation for this. I'm working on it, but I've been quite busy/distracted lately. Hopefully I'll have some more time to get back to it soon. Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
This looks really nice Carlo! It'd be even better if it were on GitHub so I could star it :). But I'm definitely going to take a look at this when I'm in the SQL world again. On Wednesday, July 3, 2013 1:48:07 AM UTC-7, Carlo wrote: Hey guys! I've been working on a small library to make writing SQL queries a little bit easier. It's along the same lines as ClojureQL, but takes a different approach and compiles into quite different SQL in the end. At the moment it's quite immature, but it should be able to support any queries which can be expressed in relational algebra. There will be some SQL queries which can't be expressed in clojure-sql, but hopefully there won't be too many of those. A greater limitation is that at the moment the SQL generation is specific to the PostgresSQL database (although any contributions for other databases are welcome!). Dependency vector: [clojure-sql 0.1.0] Repository: https://bitbucket.org/czan/clojure-sql Clojars link: https://clojars.org/clojure-sql Let me know what you think! Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Sounds good! I've been having a go with it today in a data-migration thing and I like it. It feels very like clojureql but as you said, it fails earlier with ambiguous queries, which definitely makes debugging easier. One other difference I did notice with clojureql is that in clojureql the database/connection is part of the query definition `(table db :users)`, where as in clojure-sql the database/connection is a separate thing. This seemed more orthogonal to me, but in practice I found it a bit tricky when working with multiple databases as I kept having to change global state - is there a better way of handling multiple databases using clojure-sql (perhaps I need to write my own query executor...?) A couple more questions (if that's ok) - 1. Are there any known issues/not-implemented-yet bits I might run into when using clojure-sql? 2. What plans do you have for the library going forward? Anyway, many thanks for your work again, and I do hope clojure-sql finds its niche amongst the other clojure sql libraries. I think the relational algebra approach has a lot going for it. Dan -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
On Wed, Sep 18, 2013 at 09:27:22AM -0700, Daniel Neal wrote: One other difference I did notice with clojureql is that in clojureql the database/connection is part of the query definition `(table db :users)`, where as in clojure-sql the database/connection is a separate thing. This seemed more orthogonal to me, but in practice I found it a bit tricky when working with multiple databases as I kept having to change global state - is there a better way of handling multiple databases using clojure-sql (perhaps I need to write my own query executor...?) Yeah, writing your own executor is a good option. Personally I would write one which returns a function of the database, like this: (set-query-executor! (fn [type query] (fn [db] (jdbc/with-connection db (case type :select (jdbc/with-query-results results query (dotted-to-nested-maps results)) :insert (jdbc/do-prepared-return-keys (first query) (next query)) :update (jdbc/do-prepared-return-keys (first query) (next query)) :delete (first (jdbc/do-prepared (first query) (next query))) (assert false (str Unknown query type: type))) Then you can use (@query db) to run queries, and the slightly-clunkier ((insert! query record ...) db) to modify the database. (I haven't tried running this code, so it may require some modification to actually work.) In my use case I am always working with a single database, so I thought this approach would be a bit overkill as the default. 1. Are there any known issues/not-implemented-yet bits I might run into when using clojure-sql? Almost certainly, but I don't know all of the issues. So far it's been working for what I've needed it for (which is a fairly small subset of SQL, admittedly). I'm not happy with the way the grouping stuff works at the moment, but at the moment I've not really had much time to fix it. It should work, but you lose the ability to freely compose things once you do a group (because a select being applied later can change the group result). 2. What plans do you have for the library going forward? A few things, when I get a chance, mostly to do with generalising the relational algebra approach beyond just SQL. I'd like to make it so queries can be executed against other possible data stores (eg. an in-memory structure, files on disk) efficiently. As part of this would also be re-thinking how queries are executed. Obviously, as you've seen above, the executor stuff isn't quite as nice as it could be. I don't like having the global state stuff, but I wanted executing queries to be both easy and configurable. I'd also like to start generating SQL for other databases. I've been putting this off because I don't really have any need for it at the moment and it's a fair bit of work to do properly. Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
This library looks great!
I've always liked ClojureQL much more than the other Clojure SQL libraries,
exactly for its emphasis on composability
and relational algebra.
At first looks - your library looks like it will be easier to extend for
different database servers...
I'd be interested in knowing some more about your approach compared to that
of ClojureQL and the motivation behind it.
Dan
On Saturday, July 6, 2013 5:34:37 PM UTC+2, r0man wrote:
You can do this with the second argument to the where function. I added an
example here:
https://github.com/r0man/sqlingvo/blob/master/test/sqlingvo/test/core.clj#L24
On Saturday, July 6, 2013 4:02:23 PM UTC+2, Carlo wrote:
This is a fairly restricted composition, though:
(def triangles (compose (select [:color :num_sides] (from :shapes))
(where '(= :num_sides 3
(def green-triangles (compose triangles
(where '(= :color green
(sql green-triangles)
;= [SELECT color, num_sides FROM shapes WHERE (color = ?) green]
We've lost our `num_sides` selection, so now our query is wrong. Last
clause wins means you have to be aware of the previous ones to ensure you
don't obliterate them when composing.
The same example in `clojure-sql`:
(def triangles (- (table :shapes)
(project [:color :num_sides])
(select '(= :num_sides 3
(def green-triangles (select triangles '(= :color :green)))
(deref green-triangles)
;= [SELECT \shapes2142\.\color\ AS \color\,
\shapes2142\.\num_sides\ AS \num_sides\ FROM \shapes\ AS
\shapes2142\ WHERE ((\shapes2142\.\num_sides\ = 3) AND
(\shapes2142\.\color\ = ?)) green]
On 6 July 2013 22:37, r0man roman@burningswell.com wrote:
Composing queries is done via compose.
Take a look here:
https://github.com/r0man/sqlingvo/blob/master/test/sqlingvo/test/core.clj#L16
On Saturday, July 6, 2013 5:46:06 AM UTC+2, Carlo wrote:
Hey Roman,
The issue that I see with `sqlingvo`, and the thing which I was trying
to solve for myself, is that it doesn't compose well. Unless I'm missing
something, you have to generate the entire query in the one `sql` form. To
me, this is a big restriction and was the number one thing I was trying to
fix with `clojure-sql`.
The basic sort of thing I want to be able to do is this:
(def users (- (table :users) (project [:id :person :username])))
(def people (- (table :people) (project [:id :first-name])))
(def combined-query (- people
(rename {:id :person})
(join users)
(project [:username :first-name])))
So now in queries I can use `people`, `users` and `combined-query` in
the same way. The only difference in terms of how I can compose them is
that they expose different fields (`users` exposes [:id, :person,
:username], `people` exposes [:id :first-name], `combined-query` exposes
[:username :first-name]). In this example it's not completely obvious why
this would be beneficial, but it means that I can change `users`, for
instance, to also have a `(select '(= :deleted false)` in its definition
and no other code has to change. They will all join/query against the
users
where `deleted` is false without any other modifications of code.
This freedom of composition is what you have in relational algebra, and
what I was trying to get in Clojure as well. All the naming of tables and
field aliases and everything is handled by the library, so you only have
to
worry about constructing the queries. Unfortunately SQL provides a number
of operations outside of the relational algebra model (grouping, sorting,
take/drop), so they've been tacked on as a bit of an afterthought and
could probably use some improvement.
Looking at `sqlingvo` did show up a mistake that I made in how I was
dealing with sorts, though, so thanks for that! I think our libraries just
have fairly different concerns at the moment.
Carlo
On 5 July 2013 20:59, r0man roman@burningswell.**com wrote:
Hi Carlo,
if you'are looking for generating more complex SQL there's also:
https://github.com/r0man/**sqlingvohttps://github.com/r0man/sqlingvo
Roman.
On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote:
Hey guys!
I've been working on a small library to make writing SQL queries a
little bit easier. It's along the same lines as ClojureQL, but takes a
different approach and compiles into quite different SQL in the end.
At the moment it's quite immature, but it should be able to support
any queries which can be expressed in relational algebra. There will be
some SQL queries which can't be expressed in clojure-sql, but hopefully
there won't be too many of those. A greater limitation is that at the
moment the SQL generation is specific to the PostgresSQL database
(although
any contributions for other databases are welcome!).
Dependency vector:
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
On Tue, Sep 17, 2013 at 02:28:08AM -0700, Daniel Neal wrote: I'd be interested in knowing some more about your approach compared to that of ClojureQL and the motivation behind it. Sure! The basic difference is that I tried to be a little bit more rigid in how queries must be constructed. I tried to ensure that the generated queries weren't just valid SQL, but that they were also sensible and unambiguous (or, if there isn't enough information to make a sensible, unambiguous, query, that it throws an exception). As an example, in ClojureQL you can create the following query, which will compile into a query without any issue: (join (table :users) (table :people) (where (= :uid :pid))) but the result of this query (or even the structure of the result) is difficult to determine by examining the code. Even worse, because the :uid and :pid aren't from either table explicitly, this could fail on the database if both tables have a :uid or :pid field. The equivalent query in clojure-sql, (join (table :users) (table :people) :on `(= :uid :pid)) will fail, telling you there's an ambiguous field :uid in the query. If you disambiguate :uid (by projecting :uid in either of the tables) then it will complain about :pid. My ideal is that you could treat a query as some opaque object about which you can only know two things: the shape of the data it specifies, and (after executing the query) the data that it specifies. You should then be able to arbitrarily apply clojure-sql functions to those queries to produce new opaque queries without any concern about how the subqueries were produced (with each of the functions causing a known, predictable, effect on the execution result). At the moment this should be true of all the relational operators (project/select/rename/join), the set operations (union/intersection) and some other operations (take/drop/sort) but it's not true of the grouping operations (group/having). Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
On Fri, Jul 5, 2013 at 8:53 PM, Carlo Zancanaro carlozancan...@gmail.com wrote: Is there a reason you don't use the database's table/column name quoting? It means that keywords like :first-name cannot be used as table names without a fair bit of trouble. The DSL in java.jdbc supports :entities and :identifiers to deal with quoting. Sounds like a reasonable enhancement to extend that functionality to third-party libraries as well... feel free to create issues in JIRA. -- Sean A Corfield -- (904) 302-SEAN An Architect's View -- http://corfield.org/ World Singles, LLC. -- http://worldsingles.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 --- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Composing queries is done via compose.
Take a look here:
https://github.com/r0man/sqlingvo/blob/master/test/sqlingvo/test/core.clj#L16
On Saturday, July 6, 2013 5:46:06 AM UTC+2, Carlo wrote:
Hey Roman,
The issue that I see with `sqlingvo`, and the thing which I was trying to
solve for myself, is that it doesn't compose well. Unless I'm missing
something, you have to generate the entire query in the one `sql` form. To
me, this is a big restriction and was the number one thing I was trying to
fix with `clojure-sql`.
The basic sort of thing I want to be able to do is this:
(def users (- (table :users) (project [:id :person :username])))
(def people (- (table :people) (project [:id :first-name])))
(def combined-query (- people
(rename {:id :person})
(join users)
(project [:username :first-name])))
So now in queries I can use `people`, `users` and `combined-query` in the
same way. The only difference in terms of how I can compose them is that
they expose different fields (`users` exposes [:id, :person, :username],
`people` exposes [:id :first-name], `combined-query` exposes [:username
:first-name]). In this example it's not completely obvious why this would
be beneficial, but it means that I can change `users`, for instance, to
also have a `(select '(= :deleted false)` in its definition and no other
code has to change. They will all join/query against the users where
`deleted` is false without any other modifications of code.
This freedom of composition is what you have in relational algebra, and
what I was trying to get in Clojure as well. All the naming of tables and
field aliases and everything is handled by the library, so you only have to
worry about constructing the queries. Unfortunately SQL provides a number
of operations outside of the relational algebra model (grouping, sorting,
take/drop), so they've been tacked on as a bit of an afterthought and
could probably use some improvement.
Looking at `sqlingvo` did show up a mistake that I made in how I was
dealing with sorts, though, so thanks for that! I think our libraries just
have fairly different concerns at the moment.
Carlo
On 5 July 2013 20:59, r0man roman@burningswell.com javascript:wrote:
Hi Carlo,
if you'are looking for generating more complex SQL there's also:
https://github.com/r0man/sqlingvo
Roman.
On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote:
Hey guys!
I've been working on a small library to make writing SQL queries a
little bit easier. It's along the same lines as ClojureQL, but takes a
different approach and compiles into quite different SQL in the end.
At the moment it's quite immature, but it should be able to support any
queries which can be expressed in relational algebra. There will be some
SQL queries which can't be expressed in clojure-sql, but hopefully there
won't be too many of those. A greater limitation is that at the moment the
SQL generation is specific to the PostgresSQL database (although any
contributions for other databases are welcome!).
Dependency vector: [clojure-sql 0.1.0]
Repository:
https://bitbucket.org/czan/**clojure-sqlhttps://bitbucket.org/czan/clojure-sql
Clojars link:
https://clojars.org/clojure-**sqlhttps://clojars.org/clojure-sql
Let me know what you think!
Carlo
--
--
You received this message because you are subscribed to the Google
Groups Clojure group.
To post to this group, send email to clo...@googlegroups.comjavascript:
Note that posts from new members are moderated - please be patient with
your first post.
To unsubscribe from this group, send email to
clojure+u...@googlegroups.com javascript:
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+u...@googlegroups.com javascript:.
For more options, visit https://groups.google.com/groups/opt_out.
--
--
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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
This is a fairly restricted composition, though:
(def triangles (compose (select [:color :num_sides] (from :shapes))
(where '(= :num_sides 3
(def green-triangles (compose triangles
(where '(= :color green
(sql green-triangles)
;= [SELECT color, num_sides FROM shapes WHERE (color = ?) green]
We've lost our `num_sides` selection, so now our query is wrong. Last
clause wins means you have to be aware of the previous ones to ensure you
don't obliterate them when composing.
The same example in `clojure-sql`:
(def triangles (- (table :shapes)
(project [:color :num_sides])
(select '(= :num_sides 3
(def green-triangles (select triangles '(= :color :green)))
(deref green-triangles)
;= [SELECT \shapes2142\.\color\ AS \color\,
\shapes2142\.\num_sides\ AS \num_sides\ FROM \shapes\ AS
\shapes2142\ WHERE ((\shapes2142\.\num_sides\ = 3) AND
(\shapes2142\.\color\ = ?)) green]
On 6 July 2013 22:37, r0man roman.sche...@burningswell.com wrote:
Composing queries is done via compose.
Take a look here:
https://github.com/r0man/sqlingvo/blob/master/test/sqlingvo/test/core.clj#L16
On Saturday, July 6, 2013 5:46:06 AM UTC+2, Carlo wrote:
Hey Roman,
The issue that I see with `sqlingvo`, and the thing which I was trying to
solve for myself, is that it doesn't compose well. Unless I'm missing
something, you have to generate the entire query in the one `sql` form. To
me, this is a big restriction and was the number one thing I was trying to
fix with `clojure-sql`.
The basic sort of thing I want to be able to do is this:
(def users (- (table :users) (project [:id :person :username])))
(def people (- (table :people) (project [:id :first-name])))
(def combined-query (- people
(rename {:id :person})
(join users)
(project [:username :first-name])))
So now in queries I can use `people`, `users` and `combined-query` in the
same way. The only difference in terms of how I can compose them is that
they expose different fields (`users` exposes [:id, :person, :username],
`people` exposes [:id :first-name], `combined-query` exposes [:username
:first-name]). In this example it's not completely obvious why this would
be beneficial, but it means that I can change `users`, for instance, to
also have a `(select '(= :deleted false)` in its definition and no other
code has to change. They will all join/query against the users where
`deleted` is false without any other modifications of code.
This freedom of composition is what you have in relational algebra, and
what I was trying to get in Clojure as well. All the naming of tables and
field aliases and everything is handled by the library, so you only have to
worry about constructing the queries. Unfortunately SQL provides a number
of operations outside of the relational algebra model (grouping, sorting,
take/drop), so they've been tacked on as a bit of an afterthought and
could probably use some improvement.
Looking at `sqlingvo` did show up a mistake that I made in how I was
dealing with sorts, though, so thanks for that! I think our libraries just
have fairly different concerns at the moment.
Carlo
On 5 July 2013 20:59, r0man roman@burningswell.**com wrote:
Hi Carlo,
if you'are looking for generating more complex SQL there's also:
https://github.com/r0man/**sqlingvo https://github.com/r0man/sqlingvo
Roman.
On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote:
Hey guys!
I've been working on a small library to make writing SQL queries a
little bit easier. It's along the same lines as ClojureQL, but takes a
different approach and compiles into quite different SQL in the end.
At the moment it's quite immature, but it should be able to support any
queries which can be expressed in relational algebra. There will be some
SQL queries which can't be expressed in clojure-sql, but hopefully there
won't be too many of those. A greater limitation is that at the moment the
SQL generation is specific to the PostgresSQL database (although any
contributions for other databases are welcome!).
Dependency vector: [clojure-sql 0.1.0]
Repository:
https://bitbucket.org/czan/**clo**jure-sqlhttps://bitbucket.org/czan/clojure-sql
Clojars link:
https://clojars.org/clojure-**sq**lhttps://clojars.org/clojure-sql
Let me know what you think!
Carlo
--
--
You received this message because you are subscribed to the Google
Groups Clojure group.
To post to this group, send email to clo...@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+u...@**googlegroups.com
For more options, visit this group at
http://groups.google.com/**group/clojure?hl=enhttp://groups.google.com/group/clojure?hl=en
---
You received this message because you are
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
You can do this with the second argument to the where function. I added an
example here:
https://github.com/r0man/sqlingvo/blob/master/test/sqlingvo/test/core.clj#L24
On Saturday, July 6, 2013 4:02:23 PM UTC+2, Carlo wrote:
This is a fairly restricted composition, though:
(def triangles (compose (select [:color :num_sides] (from :shapes))
(where '(= :num_sides 3
(def green-triangles (compose triangles
(where '(= :color green
(sql green-triangles)
;= [SELECT color, num_sides FROM shapes WHERE (color = ?) green]
We've lost our `num_sides` selection, so now our query is wrong. Last
clause wins means you have to be aware of the previous ones to ensure you
don't obliterate them when composing.
The same example in `clojure-sql`:
(def triangles (- (table :shapes)
(project [:color :num_sides])
(select '(= :num_sides 3
(def green-triangles (select triangles '(= :color :green)))
(deref green-triangles)
;= [SELECT \shapes2142\.\color\ AS \color\,
\shapes2142\.\num_sides\ AS \num_sides\ FROM \shapes\ AS
\shapes2142\ WHERE ((\shapes2142\.\num_sides\ = 3) AND
(\shapes2142\.\color\ = ?)) green]
On 6 July 2013 22:37, r0man roman@burningswell.com javascript:wrote:
Composing queries is done via compose.
Take a look here:
https://github.com/r0man/sqlingvo/blob/master/test/sqlingvo/test/core.clj#L16
On Saturday, July 6, 2013 5:46:06 AM UTC+2, Carlo wrote:
Hey Roman,
The issue that I see with `sqlingvo`, and the thing which I was trying
to solve for myself, is that it doesn't compose well. Unless I'm missing
something, you have to generate the entire query in the one `sql` form. To
me, this is a big restriction and was the number one thing I was trying to
fix with `clojure-sql`.
The basic sort of thing I want to be able to do is this:
(def users (- (table :users) (project [:id :person :username])))
(def people (- (table :people) (project [:id :first-name])))
(def combined-query (- people
(rename {:id :person})
(join users)
(project [:username :first-name])))
So now in queries I can use `people`, `users` and `combined-query` in
the same way. The only difference in terms of how I can compose them is
that they expose different fields (`users` exposes [:id, :person,
:username], `people` exposes [:id :first-name], `combined-query` exposes
[:username :first-name]). In this example it's not completely obvious why
this would be beneficial, but it means that I can change `users`, for
instance, to also have a `(select '(= :deleted false)` in its definition
and no other code has to change. They will all join/query against the users
where `deleted` is false without any other modifications of code.
This freedom of composition is what you have in relational algebra, and
what I was trying to get in Clojure as well. All the naming of tables and
field aliases and everything is handled by the library, so you only have to
worry about constructing the queries. Unfortunately SQL provides a number
of operations outside of the relational algebra model (grouping, sorting,
take/drop), so they've been tacked on as a bit of an afterthought and
could probably use some improvement.
Looking at `sqlingvo` did show up a mistake that I made in how I was
dealing with sorts, though, so thanks for that! I think our libraries just
have fairly different concerns at the moment.
Carlo
On 5 July 2013 20:59, r0man roman@burningswell.**com wrote:
Hi Carlo,
if you'are looking for generating more complex SQL there's also:
https://github.com/r0man/**sqlingvo https://github.com/r0man/sqlingvo
Roman.
On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote:
Hey guys!
I've been working on a small library to make writing SQL queries a
little bit easier. It's along the same lines as ClojureQL, but takes a
different approach and compiles into quite different SQL in the end.
At the moment it's quite immature, but it should be able to support
any queries which can be expressed in relational algebra. There will be
some SQL queries which can't be expressed in clojure-sql, but hopefully
there won't be too many of those. A greater limitation is that at the
moment the SQL generation is specific to the PostgresSQL database
(although
any contributions for other databases are welcome!).
Dependency vector: [clojure-sql 0.1.0]
Repository:
https://bitbucket.org/czan/**clo**jure-sqlhttps://bitbucket.org/czan/clojure-sql
Clojars link:
https://clojars.org/clojure-**sq**lhttps://clojars.org/clojure-sql
Let me know what you think!
Carlo
--
--
You received this message because you are subscribed to the Google
Groups Clojure group.
To post to this group, send email to clo...@googlegroups.com
Note that posts from new members are moderated - please be
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Hi Carlo, if you'are looking for generating more complex SQL there's also: https://github.com/r0man/sqlingvo Roman. On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote: Hey guys! I've been working on a small library to make writing SQL queries a little bit easier. It's along the same lines as ClojureQL, but takes a different approach and compiles into quite different SQL in the end. At the moment it's quite immature, but it should be able to support any queries which can be expressed in relational algebra. There will be some SQL queries which can't be expressed in clojure-sql, but hopefully there won't be too many of those. A greater limitation is that at the moment the SQL generation is specific to the PostgresSQL database (although any contributions for other databases are welcome!). Dependency vector: [clojure-sql 0.1.0] Repository: https://bitbucket.org/czan/clojure-sql Clojars link: https://clojars.org/clojure-sql Let me know what you think! Carlo -- -- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
And there's HoneySQL: https://github.com/jkk/honeysql (that's the one java.jdbc will recommend going forward since I worked with the author, Justin Kramer, on compatibility and direction for java.jdbc and HoneySQL at Clojure/conj last year) On Fri, Jul 5, 2013 at 3:59 AM, r0man roman.sche...@burningswell.com wrote: Hi Carlo, if you'are looking for generating more complex SQL there's also: https://github.com/r0man/sqlingvo Roman. On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote: Hey guys! I've been working on a small library to make writing SQL queries a little bit easier. It's along the same lines as ClojureQL, but takes a different approach and compiles into quite different SQL in the end. At the moment it's quite immature, but it should be able to support any queries which can be expressed in relational algebra. There will be some SQL queries which can't be expressed in clojure-sql, but hopefully there won't be too many of those. A greater limitation is that at the moment the SQL generation is specific to the PostgresSQL database (although any contributions for other databases are welcome!). Dependency vector: [clojure-sql 0.1.0] Repository: https://bitbucket.org/czan/clojure-sql Clojars link: https://clojars.org/clojure-sql Let me know what you think! Carlo -- -- 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. -- Sean A Corfield -- (904) 302-SEAN An Architect's View -- http://corfield.org/ World Singles, LLC. -- http://worldsingles.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 --- 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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Hey Roman,
The issue that I see with `sqlingvo`, and the thing which I was trying to
solve for myself, is that it doesn't compose well. Unless I'm missing
something, you have to generate the entire query in the one `sql` form. To
me, this is a big restriction and was the number one thing I was trying to
fix with `clojure-sql`.
The basic sort of thing I want to be able to do is this:
(def users (- (table :users) (project [:id :person :username])))
(def people (- (table :people) (project [:id :first-name])))
(def combined-query (- people
(rename {:id :person})
(join users)
(project [:username :first-name])))
So now in queries I can use `people`, `users` and `combined-query` in the
same way. The only difference in terms of how I can compose them is that
they expose different fields (`users` exposes [:id, :person, :username],
`people` exposes [:id :first-name], `combined-query` exposes [:username
:first-name]). In this example it's not completely obvious why this would
be beneficial, but it means that I can change `users`, for instance, to
also have a `(select '(= :deleted false)` in its definition and no other
code has to change. They will all join/query against the users where
`deleted` is false without any other modifications of code.
This freedom of composition is what you have in relational algebra, and
what I was trying to get in Clojure as well. All the naming of tables and
field aliases and everything is handled by the library, so you only have to
worry about constructing the queries. Unfortunately SQL provides a number
of operations outside of the relational algebra model (grouping, sorting,
take/drop), so they've been tacked on as a bit of an afterthought and
could probably use some improvement.
Looking at `sqlingvo` did show up a mistake that I made in how I was
dealing with sorts, though, so thanks for that! I think our libraries just
have fairly different concerns at the moment.
Carlo
On 5 July 2013 20:59, r0man roman.sche...@burningswell.com wrote:
Hi Carlo,
if you'are looking for generating more complex SQL there's also:
https://github.com/r0man/sqlingvo
Roman.
On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote:
Hey guys!
I've been working on a small library to make writing SQL queries a little
bit easier. It's along the same lines as ClojureQL, but takes a different
approach and compiles into quite different SQL in the end.
At the moment it's quite immature, but it should be able to support any
queries which can be expressed in relational algebra. There will be some
SQL queries which can't be expressed in clojure-sql, but hopefully there
won't be too many of those. A greater limitation is that at the moment the
SQL generation is specific to the PostgresSQL database (although any
contributions for other databases are welcome!).
Dependency vector: [clojure-sql 0.1.0]
Repository:
https://bitbucket.org/czan/**clojure-sqlhttps://bitbucket.org/czan/clojure-sql
Clojars link:
https://clojars.org/clojure-**sqlhttps://clojars.org/clojure-sql
Let me know what you think!
Carlo
--
--
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.
--
--
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.
Re: [ANN] clojure-sql 0.1.0: relational algebra in clojure
Hey Sean, Most of the points in my reply to Roman also apply to `HoneySQL`. In particular this phrase in the README summarises a good deal of my criticism: When using the vanilla helper functions, new clauses will replace old clauses. You have to go out of your way to get the composability that I desire. `HoneySQL` seems like it would fit fairly well as compiler in the `clojure-sql` sense. A translation of the internal `clojure-sql` query into a `HoneySQL` format, then run it through the `HoneySQL` compiler. Is there a reason you don't use the database's table/column name quoting? It means that keywords like :first-name cannot be used as table names without a fair bit of trouble. (This was also an issue I had with most other SQL generation libraries I've seen, and something I made sure to fix in my `clojure-sql` compiler for PostgresSQL.) Thanks, Carlo On 6 July 2013 02:32, Sean Corfield seancorfi...@gmail.com wrote: And there's HoneySQL: https://github.com/jkk/honeysql (that's the one java.jdbc will recommend going forward since I worked with the author, Justin Kramer, on compatibility and direction for java.jdbc and HoneySQL at Clojure/conj last year) On Fri, Jul 5, 2013 at 3:59 AM, r0man roman.sche...@burningswell.com wrote: Hi Carlo, if you'are looking for generating more complex SQL there's also: https://github.com/r0man/sqlingvo Roman. On Wednesday, July 3, 2013 10:48:07 AM UTC+2, Carlo wrote: Hey guys! I've been working on a small library to make writing SQL queries a little bit easier. It's along the same lines as ClojureQL, but takes a different approach and compiles into quite different SQL in the end. At the moment it's quite immature, but it should be able to support any queries which can be expressed in relational algebra. There will be some SQL queries which can't be expressed in clojure-sql, but hopefully there won't be too many of those. A greater limitation is that at the moment the SQL generation is specific to the PostgresSQL database (although any contributions for other databases are welcome!). Dependency vector: [clojure-sql 0.1.0] Repository: https://bitbucket.org/czan/clojure-sql Clojars link: https://clojars.org/clojure-sql Let me know what you think! Carlo -- -- 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. -- Sean A Corfield -- (904) 302-SEAN An Architect's View -- http://corfield.org/ World Singles, LLC. -- http://worldsingles.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 --- 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. -- -- 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.
