ANN: ClojureQL 1.0.0 finally released as public beta
Hi gents, For those of you who have followed the development of ClojureQL over the past 2.5 years you'll be excited to know that ClojureQL is as of today being released as 1.0.0 beta1. That means that the significant primitives from Relational Algebra are all in place and functional. The documentation is there and I've even made a screencast in order to get you guys started. Personally Im excited about this version because it really does change the way you work with a database. Queries are incredibly composable! For more information, please checkout this blogpost + screencast: http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta.html Best regards, Lau -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
On 19 November 2010 06:10, LauJensen wrote: > Hi gents, > > For those of you who have followed the development > of ClojureQL over the past 2.5 years you'll be excited > to know that ClojureQL is as of today being released > as 1.0.0 beta1. > > That means that the significant primitives from Relational > Algebra are all in place and functional. The documentation > is there and I've even made a screencast in order to get > you guys started. > > Personally Im excited about this version because it really > does change the way you work with a database. Queries > are incredibly composable! > > For more information, please checkout this blogpost + > screencast: > http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta.html > > Best regards, > Lau > Looks great! Can't wait to try it out! Cheers, Stuart -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
On Nov 18, 1:10 pm, LauJensen wrote: > Hi gents, > > For those of you who have followed the development > of ClojureQL over the past 2.5 years you'll be excited > to know that ClojureQL is as of today being released > as 1.0.0 beta1. > > That means that the significant primitives from Relational > Algebra are all in place and functional. The documentation > is there and I've even made a screencast in order to get > you guys started. > > Personally Im excited about this version because it really > does change the way you work with a database. Queries > are incredibly composable! > > For more information, please checkout this blogpost + > screencast:http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta > > Best regards, > Lau Great stuff Lau. I enjoyed the screencast. -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Looks great, I'm giving it a try now. FYI, the github link in your post is the private URL so it won't work for anyone but you. -Jeff On Nov 18, 8:10 pm, LauJensen wrote: > Hi gents, > > For those of you who have followed the development > of ClojureQL over the past 2.5 years you'll be excited > to know that ClojureQL is as of today being released > as 1.0.0 beta1. > > That means that the significant primitives from Relational > Algebra are all in place and functional. The documentation > is there and I've even made a screencast in order to get > you guys started. > > Personally Im excited about this version because it really > does change the way you work with a database. Queries > are incredibly composable! > > For more information, please checkout this blogpost + > screencast:http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta > > Best regards, > Lau -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Looks really good. Great work, Sam --- http://sam.aaron.name On 18 Nov 2010, at 19:10, LauJensen wrote: > Hi gents, > > For those of you who have followed the development > of ClojureQL over the past 2.5 years you'll be excited > to know that ClojureQL is as of today being released > as 1.0.0 beta1. > > That means that the significant primitives from Relational > Algebra are all in place and functional. The documentation > is there and I've even made a screencast in order to get > you guys started. > > Personally Im excited about this version because it really > does change the way you work with a database. Queries > are incredibly composable! > > For more information, please checkout this blogpost + > screencast: > http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta.html > > Best regards, > Lau > > -- > 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 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: ANN: ClojureQL 1.0.0 finally released as public beta
I've just been playing around with ClojureQL, and I'm very impressed at how clean, elegant and idiomatic the syntax is. So far this is the best SQL library I've come across - for any language. - James On 18 November 2010 19:10, LauJensen wrote: > Hi gents, > > For those of you who have followed the development > of ClojureQL over the past 2.5 years you'll be excited > to know that ClojureQL is as of today being released > as 1.0.0 beta1. > > That means that the significant primitives from Relational > Algebra are all in place and functional. The documentation > is there and I've even made a screencast in order to get > you guys started. > > Personally Im excited about this version because it really > does change the way you work with a database. Queries > are incredibly composable! > > For more information, please checkout this blogpost + > screencast: > http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta.html > > Best regards, > Lau > > -- > 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 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: ANN: ClojureQL 1.0.0 finally released as public beta
ClojureQL is all about queries, right? As far as I can tell, it provides no abstraction for creating tables, specifying indices, etc., correct? -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
@Mark: Thanks! :)
@Jeff: Thanks a lot. Link is fixed now.
@Sam: Thanks!
@James: Wow - I should put that in my resume, thanks a lot !
@Mark E:
Half right.
ClojureQL has a major focus on queries as they are the largest
part of our interaction with databases. However it piggy-backs
on contrib.sql and the functions provided there for creating tables,
dropping them etc are well suited for the job, so simply use those.
ClojureQL also has conj!, disj! and update-in! for manipulating
tables.
And finally, I've just refurbished the compiler so that its not a
25 line beast of a recursive machine that automatically spawns
subselects for aggregates, limit/offset and predicates:
cql.core> (to-sql (-> (table {} :users)
(join (table {} :salary)
(where (= :users.id :salary.id)))
(outer-join (take (table :wages)
5) :left :id)
(join (select (table :test) (where (> :id
5))) (where (= :test.id :users.id)))
(project [:users.id :salary.wage])
(select (where (= :test.id :wages.id)
SELECT users.id,salary.wage,wages_subselect.*,test_subselect.* FROM
users
JOIN salary ON (users.id = salary.id) LEFT OUTER JOIN
(SELECT wages.* FROM wages LIMIT 5) AS wages_subselect USING(id)
JOIN (SELECT test.* FROM test WHERE (id > 5)) AS test_subselect ON
(test_subselect.id = users.id) WHERE (test_subselect.id =
wages_subselect.id)"
There are probably still a few minor quirks (what you see above is
fresh from the REPL),
but if nobody sues me in the next month and if I manage to
automatically paramterize all
queries 1.0.0 FINAL should be just up ahead.
Thanks to all who have tried this out already,
Lau
--
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: ANN: ClojureQL 1.0.0 finally released as public beta
On Tue, Nov 23, 2010 at 5:34 AM, LauJensen wrote:
> And finally, I've just refurbished the compiler so that its not a
> 25 line beast of a recursive machine that automatically spawns
> subselects for aggregates, limit/offset and predicates:
>
> cql.core> (to-sql (-> (table {} :users)
> (join (table {} :salary)
> (where (= :users.id :salary.id)))
> (outer-join (take (table :wages)
> 5) :left :id)
> (join (select (table :test) (where (> :id
> 5))) (where (= :test.id :users.id)))
> (project [:users.id :salary.wage])
> (select (where (= :test.id :wages.id)
>
> SELECT users.id,salary.wage,wages_subselect.*,test_subselect.* FROM
> users
> JOIN salary ON (users.id = salary.id) LEFT OUTER JOIN
> (SELECT wages.* FROM wages LIMIT 5) AS wages_subselect USING(id)
> JOIN (SELECT test.* FROM test WHERE (id > 5)) AS test_subselect ON
> (test_subselect.id = users.id) WHERE (test_subselect.id =
> wages_subselect.id)"
>
Great work. Has the updated version been pushed to clojars yet? Or do I
need to git pull and build manually?
--
Omnem crede diem tibi diluxisse supremum.
--
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: ANN: ClojureQL 1.0.0 finally released as public beta
@Wilson,
Normally I release the jar to clojars when I implement some major
change
of interest to the public, but the compiler above didn't not get
released
due to forgetfulness on my part!
I was reworking the predicate compiler into a very fancy record which
does automated parameterization of all predicates and wanted to
include this as well. It turned out quite nice, as the (toString)
interface
made most of the old code work:
cql.predicates> (where (or (= :x 5) (< 10 :y 5)))
#:cql.predicates.APredicate{:stmt ["(( ? = ? ) OR ( ? < ?
< ? ))"], :env ["x" 5 10 "y" 5]}
cql.predicates> (str *1)
"(( ? = ? ) OR ( ? < ? < ? ))"
cql.predicates> (:env *2)
["x" 5 10 "y" 5]
I hope to release this very shortly as its the final building block
needed
for 1.0.0 FINAL - Injection protection.
Lau
On Nov 23, 4:57 pm, Wilson MacGyver wrote:
> On Tue, Nov 23, 2010 at 5:34 AM, LauJensen wrote:
> > And finally, I've just refurbished the compiler so that its not a
> > 25 line beast of a recursive machine that automatically spawns
> > subselects for aggregates, limit/offset and predicates:
>
> > cql.core> (to-sql (-> (table {} :users)
> > (join (table {} :salary)
> > (where (= :users.id :salary.id)))
> > (outer-join (take (table :wages)
> > 5) :left :id)
> > (join (select (table :test) (where (> :id
> > 5))) (where (= :test.id :users.id)))
> > (project [:users.id :salary.wage])
> > (select (where (= :test.id :wages.id)
>
> > SELECT users.id,salary.wage,wages_subselect.*,test_subselect.* FROM
> > users
> > JOIN salary ON (users.id = salary.id) LEFT OUTER JOIN
> > (SELECT wages.* FROM wages LIMIT 5) AS wages_subselect USING(id)
> > JOIN (SELECT test.* FROM test WHERE (id > 5)) AS test_subselect ON
> > (test_subselect.id = users.id) WHERE (test_subselect.id =
> > wages_subselect.id)"
>
> Great work. Has the updated version been pushed to clojars yet? Or do I
> need to git pull and build manually?
>
> --
> Omnem crede diem tibi diluxisse supremum.
--
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: ANN: ClojureQL 1.0.0 finally released as public beta
Lau, This is really impressive, and I can't wait to experiment with it. That said, I'm curious as to what good use cases for this would be, and what it's motivation is. SQL is already a highly specialized DSL for slinging around relational data that most developers are already pretty good with. I'm wondering how useful it is to have a separate relational DSL. Is this motivated by the same "everything in Clojure" philosophy (like Hiccup or Scriptjure)? Not that there's anything wrong with that... I'm just curious as to use cases. Or does it confer some other benefit over SQL besides being Clojure? The only thing I see is the ability to reuse and compose fragments of relational logic. Unarguably, that is very cool, but off the top of my head I can't think of any particular benefit it would give, at least in my apps. 99% of my database interaction is a fixed set of CRUD operations, which (unless I'm missing something) would be just as easy to write in SQL directly. Thanks, -Luke On Nov 18, 2:10 pm, LauJensen wrote: > Hi gents, > > For those of you who have followed the development > of ClojureQL over the past 2.5 years you'll be excited > to know that ClojureQL is as of today being released > as 1.0.0 beta1. > > That means that the significant primitives from Relational > Algebra are all in place and functional. The documentation > is there and I've even made a screencast in order to get > you guys started. > > Personally Im excited about this version because it really > does change the way you work with a database. Queries > are incredibly composable! > > For more information, please checkout this blogpost + > screencast:http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta > > Best regards, > Lau -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Hi Luke,
Thanks!
Initially CQL0.1 was motivated by "everything in Clojure" which was
the driving design principle behind the first version of CQL. When
I scrapped that project (for reasons you can read on my blog) I
instead turned my attention towards Relational Algebra as this
gives you unique ways to compose and express your queries, in
my opinion far superior to the SQL language itself.
Example from our test suite: Lets say you want to define a table
which computes an aggregate on a column, ex counting pictures
by user id and then join those stats to your users table. In CQL
you will (hopefully) intuitively write:
(let [photo-counts (-> (table :photos)
(aggregate [[:count/* :as :cnt]]
[:id])))]
(-> (table :users)
(join photo-counts (= {:users.id :photos.id}))
I think thats really as simple as you can express that join operation.
However for the SQL to work/be efficient, you need to detect when
a join benefits from spawning subselects and ClojureQL handles this
for you completely transparently, so if you execute the above
statement
the following is actually generated and run:
"SELECT users.*,photos_aggregation.cnt FROM users
LEFT OUTER JOIN
(SELECT photos.user_id,count(photos.*) AS cnt FROM photos GROUP BY
user_id)
AS photos_aggregation ON (users.user_id = photos_aggregation.user_id)"
And notice how your reference to :id in photos is automatically
aliased
to photos_aggregation. There are SQL statements which are truely
complex
and it takes a long time to master SQL and get comfortable with such
queries.
It is my ambition that ClojureQL will take away that complexity while
still
generating the most efficient SQL statements possible. The
implications for
Q/A on your projects will be substantial I hope.
And even if I was only doing CRUD - I'd prefer to do it (and have my
developers do it)
in Clojure, which is guaranteed to compile to correct SQL.
Answers your question?
Lau
On Nov 24, 1:56 am, Luke VanderHart wrote:
> Lau,
>
> This is really impressive, and I can't wait to experiment with it.
>
> That said, I'm curious as to what good use cases for this would be,
> and what it's motivation is. SQL is already a highly specialized DSL
> for slinging around relational data that most developers are already
> pretty good with. I'm wondering how useful it is to have a separate
> relational DSL. Is this motivated by the same "everything in Clojure"
> philosophy (like Hiccup or Scriptjure)? Not that there's anything
> wrong with that... I'm just curious as to use cases.
>
> Or does it confer some other benefit over SQL besides being Clojure?
> The only thing I see is the ability to reuse and compose fragments of
> relational logic. Unarguably, that is very cool, but off the top of my
> head I can't think of any particular benefit it would give, at least
> in my apps. 99% of my database interaction is a fixed set of CRUD
> operations, which (unless I'm missing something) would be just as easy
> to write in SQL directly.
>
> Thanks,
> -Luke
>
> On Nov 18, 2:10 pm, LauJensen wrote:
>
> > Hi gents,
>
> > For those of you who have followed the development
> > of ClojureQL over the past 2.5 years you'll be excited
> > to know that ClojureQL is as of today being released
> > as 1.0.0 beta1.
>
> > That means that the significant primitives from Relational
> > Algebra are all in place and functional. The documentation
> > is there and I've even made a screencast in order to get
> > you guys started.
>
> > Personally Im excited about this version because it really
> > does change the way you work with a database. Queries
> > are incredibly composable!
>
> > For more information, please checkout this blogpost +
> > screencast:http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta
>
> > Best regards,
> > Lau
--
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: ANN: ClojureQL 1.0.0 finally released as public beta
Thanks... I think I do see the benefit. I'll certainly give it a try
and see if I can get the feel of it.
One question... You say it generates optimized SQL. Does it take the
DBMS into account? I know the performance profiles of various SQL
idioms vary widely between MySQL and Oracle, for example - what may be
better as a subselect on one may sometimes be better as a join on
another.
Thanks so much for the detailed answer...
-Luke
On Nov 24, 3:37 am, LauJensen wrote:
> Hi Luke,
>
> Thanks!
>
> Initially CQL0.1 was motivated by "everything in Clojure" which was
> the driving design principle behind the first version of CQL. When
> I scrapped that project (for reasons you can read on my blog) I
> instead turned my attention towards Relational Algebra as this
> gives you unique ways to compose and express your queries, in
> my opinion far superior to the SQL language itself.
>
> Example from our test suite: Lets say you want to define a table
> which computes an aggregate on a column, ex counting pictures
> by user id and then join those stats to your users table. In CQL
> you will (hopefully) intuitively write:
>
> (let [photo-counts (-> (table :photos)
> (aggregate [[:count/* :as :cnt]]
> [:id])))]
> (-> (table :users)
> (join photo-counts (= {:users.id :photos.id}))
>
> I think thats really as simple as you can express that join operation.
> However for the SQL to work/be efficient, you need to detect when
> a join benefits from spawning subselects and ClojureQL handles this
> for you completely transparently, so if you execute the above
> statement
> the following is actually generated and run:
>
> "SELECT users.*,photos_aggregation.cnt FROM users
> LEFT OUTER JOIN
> (SELECT photos.user_id,count(photos.*) AS cnt FROM photos GROUP BY
> user_id)
> AS photos_aggregation ON (users.user_id = photos_aggregation.user_id)"
>
> And notice how your reference to :id in photos is automatically
> aliased
> to photos_aggregation. There are SQL statements which are truely
> complex
> and it takes a long time to master SQL and get comfortable with such
> queries.
> It is my ambition that ClojureQL will take away that complexity while
> still
> generating the most efficient SQL statements possible. The
> implications for
> Q/A on your projects will be substantial I hope.
>
> And even if I was only doing CRUD - I'd prefer to do it (and have my
> developers do it)
> in Clojure, which is guaranteed to compile to correct SQL.
>
> Answers your question?
>
> Lau
>
> On Nov 24, 1:56 am, Luke VanderHart wrote:
>
>
>
>
>
>
>
> > Lau,
>
> > This is really impressive, and I can't wait to experiment with it.
>
> > That said, I'm curious as to what good use cases for this would be,
> > and what it's motivation is. SQL is already a highly specialized DSL
> > for slinging around relational data that most developers are already
> > pretty good with. I'm wondering how useful it is to have a separate
> > relational DSL. Is this motivated by the same "everything in Clojure"
> > philosophy (like Hiccup or Scriptjure)? Not that there's anything
> > wrong with that... I'm just curious as to use cases.
>
> > Or does it confer some other benefit over SQL besides being Clojure?
> > The only thing I see is the ability to reuse and compose fragments of
> > relational logic. Unarguably, that is very cool, but off the top of my
> > head I can't think of any particular benefit it would give, at least
> > in my apps. 99% of my database interaction is a fixed set of CRUD
> > operations, which (unless I'm missing something) would be just as easy
> > to write in SQL directly.
>
> > Thanks,
> > -Luke
>
> > On Nov 18, 2:10 pm, LauJensen wrote:
>
> > > Hi gents,
>
> > > For those of you who have followed the development
> > > of ClojureQL over the past 2.5 years you'll be excited
> > > to know that ClojureQL is as of today being released
> > > as 1.0.0 beta1.
>
> > > That means that the significant primitives from Relational
> > > Algebra are all in place and functional. The documentation
> > > is there and I've even made a screencast in order to get
> > > you guys started.
>
> > > Personally Im excited about this version because it really
> > > does change the way you work with a database. Queries
> > > are incredibly composable!
>
> > > For more information, please checkout this blogpost +
> > > screencast:http://bestinclass.dk/index.clj/2010/11/clojureql--1.0.0-now-in-beta
>
> > > Best regards,
> > > Lau
--
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: ANN: ClojureQL 1.0.0 finally released as public beta
No problem Luke.
ClojureQL does not take the backend in to account. This is the one
feature from the old CQL that I didn't want to carry over because it
would be impossible for me to cater to all backends. If you hit
specific problems, let me know and I'll see what we can do.
We adhere to SQL92 and test everything on MySQL and Postgres. If
you're in a situation where thats not good enough, its always possible
to supply part of your expression as a string.
I just implemented the final piece before 1.0.0 FINAL today, which is
a predicate compiler that captures the environment and automatically
paramterizes all predicates. To help spread such lightweight news
snippets Ive put up this tumblr in case anybody's interested:
http://laujensen.tumblr.com
Lau
On Nov 24, 9:31 pm, Luke VanderHart wrote:
> Thanks... I think I do see the benefit. I'll certainly give it a try
> and see if I can get the feel of it.
>
> One question... You say it generates optimized SQL. Does it take the
> DBMS into account? I know the performance profiles of various SQL
> idioms vary widely between MySQL and Oracle, for example - what may be
> better as a subselect on one may sometimes be better as a join on
> another.
>
> Thanks so much for the detailed answer...
>
> -Luke
>
> On Nov 24, 3:37 am, LauJensen wrote:
>
> > Hi Luke,
>
> > Thanks!
>
> > Initially CQL0.1 was motivated by "everything in Clojure" which was
> > the driving design principle behind the first version of CQL. When
> > I scrapped that project (for reasons you can read on my blog) I
> > instead turned my attention towards Relational Algebra as this
> > gives you unique ways to compose and express your queries, in
> > my opinion far superior to the SQL language itself.
>
> > Example from our test suite: Lets say you want to define a table
> > which computes an aggregate on a column, ex counting pictures
> > by user id and then join those stats to your users table. In CQL
> > you will (hopefully) intuitively write:
>
> > (let [photo-counts (-> (table :photos)
> > (aggregate [[:count/* :as :cnt]]
> > [:id])))]
> > (-> (table :users)
> > (join photo-counts (= {:users.id :photos.id}))
>
> > I think thats really as simple as you can express that join operation.
> > However for the SQL to work/be efficient, you need to detect when
> > a join benefits from spawning subselects and ClojureQL handles this
> > for you completely transparently, so if you execute the above
> > statement
> > the following is actually generated and run:
>
> > "SELECT users.*,photos_aggregation.cnt FROM users
> > LEFT OUTER JOIN
> > (SELECT photos.user_id,count(photos.*) AS cnt FROM photos GROUP BY
> > user_id)
> > AS photos_aggregation ON (users.user_id = photos_aggregation.user_id)"
>
> > And notice how your reference to :id in photos is automatically
> > aliased
> > to photos_aggregation. There are SQL statements which are truely
> > complex
> > and it takes a long time to master SQL and get comfortable with such
> > queries.
> > It is my ambition that ClojureQL will take away that complexity while
> > still
> > generating the most efficient SQL statements possible. The
> > implications for
> > Q/A on your projects will be substantial I hope.
>
> > And even if I was only doing CRUD - I'd prefer to do it (and have my
> > developers do it)
> > in Clojure, which is guaranteed to compile to correct SQL.
>
> > Answers your question?
>
> > Lau
>
> > On Nov 24, 1:56 am, Luke VanderHart wrote:
>
> > > Lau,
>
> > > This is really impressive, and I can't wait to experiment with it.
>
> > > That said, I'm curious as to what good use cases for this would be,
> > > and what it's motivation is. SQL is already a highly specialized DSL
> > > for slinging around relational data that most developers are already
> > > pretty good with. I'm wondering how useful it is to have a separate
> > > relational DSL. Is this motivated by the same "everything in Clojure"
> > > philosophy (like Hiccup or Scriptjure)? Not that there's anything
> > > wrong with that... I'm just curious as to use cases.
>
> > > Or does it confer some other benefit over SQL besides being Clojure?
> > > The only thing I see is the ability to reuse and compose fragments of
> > > relational logic. Unarguably, that is very cool, but off the top of my
> > > head I can't think of any particular benefit it would give, at least
> > > in my apps. 99% of my database interaction is a fixed set of CRUD
> > > operations, which (unless I'm missing something) would be just as easy
> > > to write in SQL directly.
>
> > > Thanks,
> > > -Luke
>
> > > On Nov 18, 2:10 pm, LauJensen wrote:
>
> > > > Hi gents,
>
> > > > For those of you who have followed the development
> > > > of ClojureQL over the past 2.5 years you'll be excited
> > > > to know that ClojureQL is as of today being released
> > > > as 1.0.0 beta1.
>
> > > > That means that the significant primitives from Relational
> > > > Algebra are all in place and func
Re: ANN: ClojureQL 1.0.0 finally released as public beta
> ClojureQL does not take the backend in to account. This is the one > feature from the old CQL that I didn't want to carry over because it > would be impossible for me to cater to all backends. If you hit > specific problems, let me know and I'll see what we can do. > > We adhere to SQL92 and test everything on MySQL and Postgres. If > you're in a situation where thats not good enough, its always possible > to supply part of your expression as a string. Lau Off the top of my head, I know that the LIMIT syntax for SQL Server is totally different. A lot of the apps that I write end up getting deployed using Oracle and SQL Server. If you plan for CQL to be widely used I think you will need to take backends into account. You don't need to implement them all yourself, but you should provide a way so that others can implement them when they need to. Brenton -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Hi Brenton, Yes the OFFSET/LIMIT syntax differs from backend to backend. However in some instances (like MySQL/PostgreSQL) they have ensured compatability so that the same statement will run on several DBs although the syntax might not be considered 'native'. For something like Oracle there actually isnt a syntax for LIMIT so instead they do something like SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) WHERE rownumber <= n But as you can see it would be trivial to express this in terms of ClojureQL: (defn oracle-take [tname limit] (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)" " AS rownumber,columns" " FROM " (to-tablename tname) ")")) (select (where (<= :rownumber limit))) (project ["*"]))) (to-sql (oracle-table :users 10)) ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,columns FROM users) WHERE (rownumber <= ?)" 10] >From the outset it has been my ambition to make ClojureQL extremely composable and as far as possible allow users to directly insert strings into the query to allow for backend specific customization. The entire design-plan of this customization is not yet thought out so input is welcomed. To me, flexibility and leaving with the power to the user is the key to wide adoption across various backends. Lau On Nov 24, 11:42 pm, Brenton wrote: > > ClojureQL does not take the backend in to account. This is the one > > feature from the old CQL that I didn't want to carry over because it > > would be impossible for me to cater to all backends. If you hit > > specific problems, let me know and I'll see what we can do. > > > We adhere to SQL92 and test everything on MySQL and Postgres. If > > you're in a situation where thats not good enough, its always possible > > to supply part of your expression as a string. > > Lau > > Off the top of my head, I know that the LIMIT syntax for SQL Server is > totally different. A lot of the apps that I write end up getting > deployed using Oracle and SQL Server. If you plan for CQL to be widely > used I think you will need to take backends into account. You don't > need to implement them all yourself, but you should provide a way so > that others can implement them when they need to. > > Brenton -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
On Nov 25, 8:28 am, LauJensen wrote: > ClojureQL: > > (defn oracle-take > [tname limit] > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)" > " AS rownumber,columns" > " FROM " (to-tablename tname) ")")) > (select (where (<= :rownumber limit))) > (project ["*"]))) > > (to-sql (oracle-table :users 10)) > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10] > > From the outset it has been my ambition to make ClojureQL extremely > composable and as far as possible allow users to directly insert > strings into the query to allow for backend specific customization. > The entire design-plan of this customization is not yet thought out so > input is welcomed. To me, flexibility and leaving with the power to > the user is the key to wide adoption across various backends. > My experience would agree with this assumption. I looked at the original Clojure QL for use with H2 but didn't want to put in the effort of writing a H2 driver to TEST a library that I may want to use. If I can play with the library and I like it, its then no problem at all to write some small workarounds for the non-standard behaviour of the database I'm using. Ideally though, it would be nice if workarounds for various databases were added to the library as the appear - so in this example I can call oracle-take without having to write it myself. Saul -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Database dialect independence may be a useful thing to have for ClojureQL, so that users can work based on intent rather than syntax. Consider something like this (just an idea): ;; by default in ClojureQL ;; make-sql92-handler is a factory fn for a protocol (def *dialect-handler* (make-sql92-handler)) ;; somewhere in an app (binding [cql/*dialect-handler* (make-oracle-handler)] ;; do usual stuff ..) This kind of a structure may allow others to write dialect handlers for different databases as contrib and ClojureQL can stay as the composable core. Databases that do not support all of SQL-92 constructs can throw a FeatureNotAvailable exception when an unsupported intent is asked for. Regards, Shantanu On Nov 25, 12:28 pm, LauJensen wrote: > Hi Brenton, > > Yes the OFFSET/LIMIT syntax differs from backend to backend. However > in some instances (like MySQL/PostgreSQL) they have ensured > compatability so that the same statement will run on several DBs > although the syntax might not be considered 'native'. For something > like Oracle there actually isnt a syntax for LIMIT so instead they do > something like > > SELECT * FROM ( > SELECT > ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, > columns > FROM tablename > ) > WHERE rownumber <= n > > But as you can see it would be trivial to express this in terms of > ClojureQL: > > (defn oracle-take > [tname limit] > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)" > " AS rownumber,columns" > " FROM " (to-tablename tname) ")")) > (select (where (<= :rownumber limit))) > (project ["*"]))) > > (to-sql (oracle-table :users 10)) > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10] > > From the outset it has been my ambition to make ClojureQL extremely > composable and as far as possible allow users to directly insert > strings into the query to allow for backend specific customization. > The entire design-plan of this customization is not yet thought out so > input is welcomed. To me, flexibility and leaving with the power to > the user is the key to wide adoption across various backends. > > Lau > > On Nov 24, 11:42 pm, Brenton wrote: > > > > > > > > > > ClojureQL does not take the backend in to account. This is the one > > > feature from the old CQL that I didn't want to carry over because it > > > would be impossible for me to cater to all backends. If you hit > > > specific problems, let me know and I'll see what we can do. > > > > We adhere to SQL92 and test everything on MySQL and Postgres. If > > > you're in a situation where thats not good enough, its always possible > > > to supply part of your expression as a string. > > > Lau > > > Off the top of my head, I know that the LIMIT syntax for SQL Server is > > totally different. A lot of the apps that I write end up getting > > deployed using Oracle and SQL Server. If you plan for CQL to be widely > > used I think you will need to take backends into account. You don't > > need to implement them all yourself, but you should provide a way so > > that others can implement them when they need to. > > > Brenton -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Allowing implementation-specific and optimized SQL only via strings and not via a mechanism within ClojureQL allows its use in specific applications but effectively prevents use for libraries and frameworks. Indirect use of ClojureQL could yield unacceptable performance with no elegant fix. On Nov 24, 11:28 pm, LauJensen wrote: > Hi Brenton, > > Yes the OFFSET/LIMIT syntax differs from backend to backend. However > in some instances (like MySQL/PostgreSQL) they have ensured > compatability so that the same statement will run on several DBs > although the syntax might not be considered 'native'. For something > like Oracle there actually isnt a syntax for LIMIT so instead they do > something like > > SELECT * FROM ( > SELECT > ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, > columns > FROM tablename > ) > WHERE rownumber <= n > > But as you can see it would be trivial to express this in terms of > ClojureQL: > > (defn oracle-take > [tname limit] > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)" > " AS rownumber,columns" > " FROM " (to-tablename tname) ")")) > (select (where (<= :rownumber limit))) > (project ["*"]))) > > (to-sql (oracle-table :users 10)) > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10] > > From the outset it has been my ambition to make ClojureQL extremely > composable and as far as possible allow users to directly insert > strings into the query to allow for backend specific customization. > The entire design-plan of this customization is not yet thought out so > input is welcomed. To me, flexibility and leaving with the power to > the user is the key to wide adoption across various backends. > > Lau > > On Nov 24, 11:42 pm, Brenton wrote: > > > > > > ClojureQL does not take the backend in to account. This is the one > > > feature from the old CQL that I didn't want to carry over because it > > > would be impossible for me to cater to all backends. If you hit > > > specific problems, let me know and I'll see what we can do. > > > > We adhere to SQL92 and test everything on MySQL and Postgres. If > > > you're in a situation where thats not good enough, its always possible > > > to supply part of your expression as a string. > > > Lau > > > Off the top of my head, I know that the LIMIT syntax for SQL Server is > > totally different. A lot of the apps that I write end up getting > > deployed using Oracle and SQL Server. If you plan for CQL to be widely > > used I think you will need to take backends into account. You don't > > need to implement them all yourself, but you should provide a way so > > that others can implement them when they need to. > > > Brenton -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
There's some valuable food for thought in this thread. My major problem with support various backends is the amount of work thats involved. Every single backend needs to play nice with all the primitives defined in Relational Algebra and that are implemented in ClojureQL. I believe that the SQL92 standard takes us very far down the middle road, but I realize that it wont take us all the way. ClojureQL is actually two sets of primitives. Firstly its the RA stuf (select, project, join, etc) and then internally there is a number of primitives defined which help translate the RTable record into a query string. At the highest level only two functions need to be changed in order to effectively replace the entire compiler: to-sql and build- join. These call each other to build complex queries. If these were supplied as plugins, I think most backends could be fully supported simply by providing these two methods. They are not trivial however, so anybody interested in contributing should check out whats already there. Still thinking this through but the input is appreciated! Lau On Nov 25, 6:14 pm, rickmode wrote: > Allowing implementation-specific and optimized SQL only via strings > and not via a mechanism within ClojureQL allows its use in specific > applications but effectively prevents use for libraries and > frameworks. Indirect use of ClojureQL could yield unacceptable > performance with no elegant fix. > > On Nov 24, 11:28 pm, LauJensen wrote: > > > Hi Brenton, > > > Yes the OFFSET/LIMIT syntax differs from backend to backend. However > > in some instances (like MySQL/PostgreSQL) they have ensured > > compatability so that the same statement will run on several DBs > > although the syntax might not be considered 'native'. For something > > like Oracle there actually isnt a syntax for LIMIT so instead they do > > something like > > > SELECT * FROM ( > > SELECT > > ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, > > columns > > FROM tablename > > ) > > WHERE rownumber <= n > > > But as you can see it would be trivial to express this in terms of > > ClojureQL: > > > (defn oracle-take > > [tname limit] > > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)" > > " AS rownumber,columns" > > " FROM " (to-tablename tname) ")")) > > (select (where (<= :rownumber limit))) > > (project ["*"]))) > > > (to-sql (oracle-table :users 10)) > > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS > > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10] > > > From the outset it has been my ambition to make ClojureQL extremely > > composable and as far as possible allow users to directly insert > > strings into the query to allow for backend specific customization. > > The entire design-plan of this customization is not yet thought out so > > input is welcomed. To me, flexibility and leaving with the power to > > the user is the key to wide adoption across various backends. > > > Lau > > > On Nov 24, 11:42 pm, Brenton wrote: > > > > > ClojureQL does not take the backend in to account. This is the one > > > > feature from the old CQL that I didn't want to carry over because it > > > > would be impossible for me to cater to all backends. If you hit > > > > specific problems, let me know and I'll see what we can do. > > > > > We adhere to SQL92 and test everything on MySQL and Postgres. If > > > > you're in a situation where thats not good enough, its always possible > > > > to supply part of your expression as a string. > > > > Lau > > > > Off the top of my head, I know that the LIMIT syntax for SQL Server is > > > totally different. A lot of the apps that I write end up getting > > > deployed using Oracle and SQL Server. If you plan for CQL to be widely > > > used I think you will need to take backends into account. You don't > > > need to implement them all yourself, but you should provide a way so > > > that others can implement them when they need to. > > > > Brenton -- 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: ANN: ClojureQL 1.0.0 finally released as public beta
Lau, I agree that it is a lot of work and no one person can do it all well. I think that the main thing that needs to happen at this point is that you, as the brains behind ClojureQL, should spend a little time thinking about the best way for people to contribute back end implementations. Maybe there is something that can be done to CQL to make this easy. For example, maybe we add a "dialect" key to the database connection map and then have the functions which will need change be multimethods which dispatch on that key (dispatching on the classname or subprotocol may not always be good enough. For example, newer versions of SQL Server may provide better optimizations.). This will allow others to have a path for implementing new back ends as they need them and not be that much additional work for you. It could also be designed so that people can provide back end implementations as separate projects so that they don't even need to be included in ClojureQL. I'm sure you will come up with something on par with the rest of ClojureQL. Great work! I didn't like the first ClojureQL but I love it now. Now I have to figure out how what to do with Carte. Brenton On Nov 25, 10:27 am, LauJensen wrote: > There's some valuable food for thought in this thread. > > My major problem with support various backends is the amount of work > thats involved. Every single backend needs to play nice with all the > primitives defined in Relational Algebra and that are implemented in > ClojureQL. I believe that the SQL92 standard takes us very far down > the middle road, but I realize that it wont take us all the way. > > ClojureQL is actually two sets of primitives. Firstly its the RA stuf > (select, project, join, etc) and then internally there is a number of > primitives defined which help translate the RTable record into a query > string. At the highest level only two functions need to be changed in > order to effectively replace the entire compiler: to-sql and build- > join. These call each other to build complex queries. If these were > supplied as plugins, I think most backends could be fully supported > simply by providing these two methods. They are not trivial however, > so anybody interested in contributing should check out whats already > there. > > Still thinking this through but the input is appreciated! > > Lau > > On Nov 25, 6:14 pm, rickmode wrote: > > > Allowing implementation-specific and optimized SQL only via strings > > and not via a mechanism within ClojureQL allows its use in specific > > applications but effectively prevents use for libraries and > > frameworks. Indirect use of ClojureQL could yield unacceptable > > performance with no elegant fix. > > > On Nov 24, 11:28 pm, LauJensen wrote: > > > > Hi Brenton, > > > > Yes the OFFSET/LIMIT syntax differs from backend to backend. However > > > in some instances (like MySQL/PostgreSQL) they have ensured > > > compatability so that the same statement will run on several DBs > > > although the syntax might not be considered 'native'. For something > > > like Oracle there actually isnt a syntax for LIMIT so instead they do > > > something like > > > > SELECT * FROM ( > > > SELECT > > > ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, > > > columns > > > FROM tablename > > > ) > > > WHERE rownumber <= n > > > > But as you can see it would be trivial to express this in terms of > > > ClojureQL: > > > > (defn oracle-take > > > [tname limit] > > > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)" > > > " AS rownumber,columns" > > > " FROM " (to-tablename tname) ")")) > > > (select (where (<= :rownumber limit))) > > > (project ["*"]))) > > > > (to-sql (oracle-table :users 10)) > > > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS > > > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10] > > > > From the outset it has been my ambition to make ClojureQL extremely > > > composable and as far as possible allow users to directly insert > > > strings into the query to allow for backend specific customization. > > > The entire design-plan of this customization is not yet thought out so > > > input is welcomed. To me, flexibility and leaving with the power to > > > the user is the key to wide adoption across various backends. > > > > Lau > > > > On Nov 24, 11:42 pm, Brenton wrote: > > > > > > ClojureQL does not take the backend in to account. This is the one > > > > > feature from the old CQL that I didn't want to carry over because it > > > > > would be impossible for me to cater to all backends. If you hit > > > > > specific problems, let me know and I'll see what we can do. > > > > > > We adhere to SQL92 and test everything on MySQL and Postgres. If > > > > > you're in a situation where thats not good enough, its always possible > > > > > to supply part of your expression as a string. > > > > > Lau > > > > > Off the top of my head, I know that the LIMIT syntax for SQ
Re: ANN: ClojureQL 1.0.0 finally released as public beta
First of all, thank you for this awesome library.
I'm experimenting with ClojureQL for accessing Postgis, the spacial
extender for Postgres. To improve the ClojureQL for this use case, it
would be useful to have a way to add custom predicates. For example to
find all places whose location column intersect with a polygon, you
can use a query like this:
SELECT * FROM place WHERE ST_Intersects(location,
ST_GeographyFromText('SRID=4326;POLYGON((33 38,34 38,34 39,33 39,33
38))'))
It would be nice, if I could write that as
(-> places (select (where (st-intersects :location polygon
Is there a way to do this (or something similar) in ClojureQL or is it
planned for a future version?
Janico
On Thu, Nov 25, 2010 at 7:27 PM, LauJensen wrote:
> There's some valuable food for thought in this thread.
>
> My major problem with support various backends is the amount of work
> thats involved. Every single backend needs to play nice with all the
> primitives defined in Relational Algebra and that are implemented in
> ClojureQL. I believe that the SQL92 standard takes us very far down
> the middle road, but I realize that it wont take us all the way.
>
> ClojureQL is actually two sets of primitives. Firstly its the RA stuf
> (select, project, join, etc) and then internally there is a number of
> primitives defined which help translate the RTable record into a query
> string. At the highest level only two functions need to be changed in
> order to effectively replace the entire compiler: to-sql and build-
> join. These call each other to build complex queries. If these were
> supplied as plugins, I think most backends could be fully supported
> simply by providing these two methods. They are not trivial however,
> so anybody interested in contributing should check out whats already
> there.
>
> Still thinking this through but the input is appreciated!
>
> Lau
>
> On Nov 25, 6:14 pm, rickmode wrote:
>> Allowing implementation-specific and optimized SQL only via strings
>> and not via a mechanism within ClojureQL allows its use in specific
>> applications but effectively prevents use for libraries and
>> frameworks. Indirect use of ClojureQL could yield unacceptable
>> performance with no elegant fix.
>>
>> On Nov 24, 11:28 pm, LauJensen wrote:
>>
>> > Hi Brenton,
>>
>> > Yes the OFFSET/LIMIT syntax differs from backend to backend. However
>> > in some instances (like MySQL/PostgreSQL) they have ensured
>> > compatability so that the same statement will run on several DBs
>> > although the syntax might not be considered 'native'. For something
>> > like Oracle there actually isnt a syntax for LIMIT so instead they do
>> > something like
>>
>> > SELECT * FROM (
>> > SELECT
>> > ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
>> > columns
>> > FROM tablename
>> > )
>> > WHERE rownumber <= n
>>
>> > But as you can see it would be trivial to express this in terms of
>> > ClojureQL:
>>
>> > (defn oracle-take
>> > [tname limit]
>> > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)"
>> > " AS rownumber,columns"
>> > " FROM " (to-tablename tname) ")"))
>> > (select (where (<= :rownumber limit)))
>> > (project ["*"])))
>>
>> > (to-sql (oracle-table :users 10))
>> > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS
>> > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10]
>>
>> > From the outset it has been my ambition to make ClojureQL extremely
>> > composable and as far as possible allow users to directly insert
>> > strings into the query to allow for backend specific customization.
>> > The entire design-plan of this customization is not yet thought out so
>> > input is welcomed. To me, flexibility and leaving with the power to
>> > the user is the key to wide adoption across various backends.
>>
>> > Lau
>>
>> > On Nov 24, 11:42 pm, Brenton wrote:
>>
>> > > > ClojureQL does not take the backend in to account. This is the one
>> > > > feature from the old CQL that I didn't want to carry over because it
>> > > > would be impossible for me to cater to all backends. If you hit
>> > > > specific problems, let me know and I'll see what we can do.
>>
>> > > > We adhere to SQL92 and test everything on MySQL and Postgres. If
>> > > > you're in a situation where thats not good enough, its always possible
>> > > > to supply part of your expression as a string.
>>
>> > > Lau
>>
>> > > Off the top of my head, I know that the LIMIT syntax for SQL Server is
>> > > totally different. A lot of the apps that I write end up getting
>> > > deployed using Oracle and SQL Server. If you plan for CQL to be widely
>> > > used I think you will need to take backends into account. You don't
>> > > need to implement them all yourself, but you should provide a way so
>> > > that others can implement them when they need to.
>>
>> > > Brenton
>
> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post
Re: ANN: ClojureQL 1.0.0 finally released as public beta
@Brenton,
Thanks a lot for the vote of confidence :) I will do my best to try
and make some kind of elegant design solution for this. Your
suggestions are great!
@Janico:
Since we allow strings as arguments to most functions, I think you
could simple express it like so:
clojureql.core> (defn st-intersects [loc poly]
(format "ST_Intersects(%s,
ST_GeographyFromText('SRID=4326;POLYGON((%s))')"
(name loc)
(join-str ", " poly)))
#'clojureql.core/st-intersects
clojureql.core> (-> (table :place)
(select (where (st-intersects :location [15, 20,
25, 30])))
to-sql)
["SELECT place.* FROM place WHERE ST_Intersects(location,
ST_GeographyFromText('SRID=4326;POLYGON((15, 20, 25, 30))')"]
Would that work for you?
Lau
On Nov 27, 7:05 pm, Janico Greifenberg wrote:
> First of all, thank you for this awesome library.
>
> I'm experimenting withClojureQLfor accessing Postgis, the spacial
> extender for Postgres. To improve theClojureQLfor this use case, it
> would be useful to have a way to add custom predicates. For example to
> find all places whose location column intersect with a polygon, you
> can use a query like this:
>
> SELECT * FROM place WHERE ST_Intersects(location,
> ST_GeographyFromText('SRID=4326;POLYGON((33 38,34 38,34 39,33 39,33
> 38))'))
>
> It would be nice, if I could write that as
>
> (-> places (select (where (st-intersects :location polygon
>
> Is there a way to do this (or something similar) inClojureQLor is it
> planned for a future version?
>
> Janico
>
> On Thu, Nov 25, 2010 at 7:27 PM, LauJensen wrote:
> > There's some valuable food for thought in this thread.
>
> > My major problem with support various backends is the amount of work
> > thats involved. Every single backend needs to play nice with all the
> > primitives defined in Relational Algebra and that are implemented in
> >ClojureQL. I believe that the SQL92 standard takes us very far down
> > the middle road, but I realize that it wont take us all the way.
>
> >ClojureQLis actually two sets of primitives. Firstly its the RA stuf
> > (select, project, join, etc) and then internally there is a number of
> > primitives defined which help translate the RTable record into a query
> > string. At the highest level only two functions need to be changed in
> > order to effectively replace the entire compiler: to-sql and build-
> > join. These call each other to build complex queries. If these were
> > supplied as plugins, I think most backends could be fully supported
> > simply by providing these two methods. They are not trivial however,
> > so anybody interested in contributing should check out whats already
> > there.
>
> > Still thinking this through but the input is appreciated!
>
> > Lau
>
> > On Nov 25, 6:14 pm, rickmode wrote:
> >> Allowing implementation-specific and optimized SQL only via strings
> >> and not via a mechanism withinClojureQLallows its use in specific
> >> applications but effectively prevents use for libraries and
> >> frameworks. Indirect use ofClojureQLcould yield unacceptable
> >> performance with no elegant fix.
>
> >> On Nov 24, 11:28 pm, LauJensen wrote:
>
> >> > Hi Brenton,
>
> >> > Yes the OFFSET/LIMIT syntax differs from backend to backend. However
> >> > in some instances (like MySQL/PostgreSQL) they have ensured
> >> > compatability so that the same statement will run on several DBs
> >> > although the syntax might not be considered 'native'. For something
> >> > like Oracle there actually isnt a syntax for LIMIT so instead they do
> >> > something like
>
> >> > SELECT * FROM (
> >> > SELECT
> >> > ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
> >> > columns
> >> > FROM tablename
> >> > )
> >> > WHERE rownumber <= n
>
> >> > But as you can see it would be trivial to express this in terms of
> >> >ClojureQL:
>
> >> > (defn oracle-take
> >> > [tname limit]
> >> > (-> (table (str "(SELECT ROW_NUMBER() OVER (ORDER BY key ASC)"
> >> > " AS rownumber,columns"
> >> > " FROM " (to-tablename tname) ")"))
> >> > (select (where (<= :rownumber limit)))
> >> > (project ["*"])))
>
> >> > (to-sql (oracle-table :users 10))
> >> > ["SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS
> >> > rownumber,columns FROM users) WHERE (rownumber <= ?)" 10]
>
> >> > From the outset it has been my ambition to makeClojureQLextremely
> >> > composable and as far as possible allow users to directly insert
> >> > strings into the query to allow for backend specific customization.
> >> > The entire design-plan of this customization is not yet thought out so
> >> > input is welcomed. To me, flexibility and leaving with the power to
> >> > the user is the key to wide adoption across various backends.
>
> >> > Lau
>
> >> > On Nov 24, 11:42 pm, Brenton wrote:
>
> >> > > >ClojureQLdoes not take the backend in to account. This is the one
Re: ANN: ClojureQL 1.0.0 finally released as public beta
On Tue, Nov 30, 2010 at 12:00 PM, LauJensen wrote:
> @Janico:
>
> Since we allow strings as arguments to most functions, I think you
> could simple express it like so:
>
> clojureql.core> (defn st-intersects [loc poly]
> (format "ST_Intersects(%s,
> ST_GeographyFromText('SRID=4326;POLYGON((%s))')"
> (name loc)
> (join-str ", " poly)))
> #'clojureql.core/st-intersects
> clojureql.core> (-> (table :place)
> (select (where (st-intersects :location [15, 20,
> 25, 30])))
> to-sql)
> ["SELECT place.* FROM place WHERE ST_Intersects(location,
> ST_GeographyFromText('SRID=4326;POLYGON((15, 20, 25, 30))')"]
>
> Would that work for you?
Yes, that works nicely. Thank you. I didn't realize you could use a
function that returns a string as a predicate.
Janico
--
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
