@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 <j...@acm.org> 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 <lau.jen...@bestinclass.dk> 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 <rickm...@gmail.com> 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 <lau.jen...@bestinclass.dk> 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 <bashw...@gmail.com> wrote:
>
> >> > > >ClojureQLdoes 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
--
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
