Asp Forum - Re: Lisp comprehensions => SQL

Victor 'Zverok' Shepelev

12/5/2006 10:28:00 PM

From: Logan Capaldo [mailto:logancapaldo@gmail.com]
Sent: Wednesday, December 06, 2006 12:15 AM
>To: ruby-talk ML
>Subject: Re: Lisp comprehensions => SQL
>
>On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev wrote:
>> Hi all.
>>
>> Random idea, just for fun - using "list comprehensions" for SQL queries
>> generation.
>>
>> employees = Table.new(:id, :name, :sec_name, :salary, :age)
>>
>> employees.select{|e| e.name == 'John' && e.salary > 50}.sort_by{|e|
>> e.age}[2,10]
>>
>> #generates "select * from Employees where name = 'John' and salary > 50
>> order by age limit 2,10"
>>
>> employees.select{|e| e.salary < 150}.count
>>
>> #generates "select count(*) from Employees where salary < 150
[...]
>The ez_where plugin for rails does this.
>http://brainspl.at/articles/2006/01/30/i-have...

Yeah, seems to be close to my proposition.
What I dislike in the solution (and in Mongoose, which repeats the solution)
that conditions is NOT plain Ruby, but custom DSL.

#ez_where:

articles = Article.ez_find(:all, :include => :author) do |article, author|
article.title =~ "%Foo Title%"
author.any do
name == 'Ezra'
name == 'Fab'
end
end

#my idea, thus lacking knowledge about tables relationships:

(articles+authors).select{|article, author|
article.author_id == author.id &&
article.title =~ "%Foo Title%" &&
(author.name == 'Ezra' || author.name == 'Fab')
}

Seems to read as "just ruby".
What do you think about?

BTW, I think about some common "over-DSL-ing" between DSL-addict.
It's something like "Syntactic saccharin"[1] - seems to be "very cool", but
no added value.

V.

1. http://en.wikipedia.org/wiki/Syntactic_sugar#Syntactic...

3 Answers

Logan Capaldo

12/6/2006 12:32:00 AM

On Wed, Dec 06, 2006 at 07:27:31AM +0900, Victor Zverok Shepelev wrote:
> From: Logan Capaldo [mailto:logancapaldo@gmail.com]
> Sent: Wednesday, December 06, 2006 12:15 AM
> >To: ruby-talk ML
> >Subject: Re: Lisp comprehensions => SQL
> >
> >On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev wrote:
> >> Hi all.
> >>
> >> Random idea, just for fun - using "list comprehensions" for SQL queries
> >> generation.
> >>
> >> employees = Table.new(:id, :name, :sec_name, :salary, :age)
> >>
> >> employees.select{|e| e.name == 'John' && e.salary > 50}.sort_by{|e|
> >> e.age}[2,10]
> >>
> >> #generates "select * from Employees where name = 'John' and salary > 50
> >> order by age limit 2,10"
> >>
> >> employees.select{|e| e.salary < 150}.count
> >>
> >> #generates "select count(*) from Employees where salary < 150
> [...]
> >The ez_where plugin for rails does this.
> >http://brainspl.at/articles/2006/01/30/i-have...
>
> Yeah, seems to be close to my proposition.
> What I dislike in the solution (and in Mongoose, which repeats the solution)
> that conditions is NOT plain Ruby, but custom DSL.
>
> #ez_where:
>
> articles = Article.ez_find(:all, :include => :author) do |article, author|
> article.title =~ "%Foo Title%"
> author.any do
> name == 'Ezra'
> name == 'Fab'
> end
> end
There's a reason for this, see below
>
> #my idea, thus lacking knowledge about tables relationships:
>
> (articles+authors).select{|article, author|
> article.author_id == author.id &&
> article.title =~ "%Foo Title%" &&
> (author.name == 'Ezra' || author.name == 'Fab')
> }
>
> Seems to read as "just ruby".
> What do you think about?
>
There's a problem:
> (articles+authors).select{|article, author|
articles+authors perfectly doable. Same with select
> article.author_id == author.id &&
article.authod_id == author.id # doable with appropiate def's of
author_id, #==, id, etc. What's _NOT_ doable is &&. You can't override
that. You have to use another operator or something like any do ... end

> article.title =~ "%Foo Title%" &&
> (author.name == 'Ezra' || author.name == 'Fab')
> }
> BTW, I think about some common "over-DSL-ing" between DSL-addict.
> It's something like "Syntactic saccharin"[1] - seems to be "very cool", but
> no added value.
>
> V.
>
> 1. http://en.wikipedia.org/wiki/Syntactic_sugar#Syntactic...
>

Ken Bloom

12/6/2006 1:43:00 AM

On Wed, 06 Dec 2006 09:31:44 +0900, Logan Capaldo wrote:

> On Wed, Dec 06, 2006 at 07:27:31AM +0900, Victor Zverok Shepelev wrote:
>> From: Logan Capaldo [mailto:logancapaldo@gmail.com]
>> Sent: Wednesday, December 06, 2006 12:15 AM
>> >To: ruby-talk ML
>> >Subject: Re: Lisp comprehensions => SQL
>> >
>> >On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev wrote:
>> >> Hi all.
>> >>
>> >> Random idea, just for fun - using "list comprehensions" for SQL queries
>> >> generation.
>> >>
>> >> employees = Table.new(:id, :name, :sec_name, :salary, :age)
>> >>
>> >> employees.select{|e| e.name == 'John' && e.salary > 50}.sort_by{|e|
>> >> e.age}[2,10]
>> >>
>> >> #generates "select * from Employees where name = 'John' and salary > 50
>> >> order by age limit 2,10"
>> >>
>> >> employees.select{|e| e.salary < 150}.count
>> >>
>> >> #generates "select count(*) from Employees where salary < 150
>> [...]
>> >The ez_where plugin for rails does this.
>> >http://brainspl.at/articles/2006/01/30/i-have...
>>
>> Yeah, seems to be close to my proposition.
>> What I dislike in the solution (and in Mongoose, which repeats the solution)
>> that conditions is NOT plain Ruby, but custom DSL.
>>
>> #ez_where:
>>
>> articles = Article.ez_find(:all, :include => :author) do |article, author|
>> article.title =~ "%Foo Title%"
>> author.any do
>> name == 'Ezra'
>> name == 'Fab'
>> end
>> end
> There's a reason for this, see below
>>
>> #my idea, thus lacking knowledge about tables relationships:
>>
>> (articles+authors).select{|article, author|
>> article.author_id == author.id &&
>> article.title =~ "%Foo Title%" &&
>> (author.name == 'Ezra' || author.name == 'Fab')
>> }
>>
>> Seems to read as "just ruby".
>> What do you think about?
>>
> There's a problem:
>> (articles+authors).select{|article, author|
> articles+authors perfectly doable. Same with select
>> article.author_id == author.id &&
> article.authod_id == author.id # doable with appropiate def's of
> author_id, #==, id, etc.

> What's _NOT_ doable is &&. You can't override
> that. You have to use another operator or something like any do ... end

That's not so much of a problem, as one can simply use & (which is
implemented to be a non-short-circuit version of && on booleans anyway, so
it's not all that out of place)

A much bigger problem is !, not and !=. For this, I resigned myself to
using Dominik Bathon's RubyNode-based solution in my own SqlStatement
library (which you should definitely check out).

--Ken

--
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu...

Ezra Zygmuntowicz

12/6/2006 8:02:00 PM

On Dec 5, 2006, at 2:27 PM, Victor Zverok Shepelev wrote:

> From: Logan Capaldo [mailto:logancapaldo@gmail.com]
> Sent: Wednesday, December 06, 2006 12:15 AM
>> To: ruby-talk ML
>> Subject: Re: Lisp comprehensions => SQL
>>
>> On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev
>> wrote:
>>> Hi all.
>>>
>>> Random idea, just for fun - using "list comprehensions" for SQL
>>> queries
>>> generation.
>>>
>>> employees = Table.new(:id, :name, :sec_name, :salary, :age)
>>>
>>> employees.select{|e| e.name == 'John' && e.salary > 50}.sort_by{|e|
>>> e.age}[2,10]
>>>
>>> #generates "select * from Employees where name = 'John' and
>>> salary > 50
>>> order by age limit 2,10"
>>>
>>> employees.select{|e| e.salary < 150}.count
>>>
>>> #generates "select count(*) from Employees where salary < 150
> [...]
>> The ez_where plugin for rails does this.
>> http://brainspl.at/articles/2006/01/30/i-have...
>
> Yeah, seems to be close to my proposition.
> What I dislike in the solution (and in Mongoose, which repeats the
> solution)
> that conditions is NOT plain Ruby, but custom DSL.
>
> #ez_where:
>
> articles = Article.ez_find(:all, :include => :author) do |article,
> author|
> article.title =~ "%Foo Title%"
> author.any do
> name == 'Ezra'
> name == 'Fab'
> end
> end
>
> #my idea, thus lacking knowledge about tables relationships:
>
> (articles+authors).select{|article, author|
> article.author_id == author.id &&
> article.title =~ "%Foo Title%" &&
> (author.name == 'Ezra' || author.name == 'Fab')
> }
>
> Seems to read as "just ruby".
> What do you think about?

There are quite a few more features in ez-where then the example
above. ez-where is only a where clause generator. It doesnt ourput
complete selects. But there are many ways to use it besides the above:

You can make it write the :include statements for you

articles = Article.find_where(:all) do |article|
article.title =~ 'Lorem%'
article.author.name == 'Ezra'
article.comments.user.name == 'Fab'
end

The above just creates the following hashes and passes them to a
normal AR#find

:include => { :author => {}, :comments => { :user => {} } }
:conditions => ["(articles.title LIKE ? AND (authors.name = ?) AND
(users.name = ?))", "Lorem%", "Ezra", "Fab"]

But you can use it without active record at all to generate where
clauses for you:

>> a = c(:authors) { name == 'jimbob' }
<snip>

>> b = c(:articles) { title =~ '%ruby%' }
<snip>

>> (a + b).to_sql
# => ["(authors.name = ?) AND (articles.title LIKE ?)", "jimbob", "%
ruby%"]

>> (a | b).to_sql
# => ["(authors.name = ?) OR (articles.title LIKE ?)", "jimbob", "%
ruby%"]

>> (a - b).to_sql
# => ["(authors.name = ?) AND NOT (articles.title LIKE ?)", "jimbob",
"%ruby%"]

>> c = c(:tags) {any_of(:name, :comment, :metadata) =~ '%ruby%'}
<snip>

>> c.to_sql
# => ["(tags.name LIKE ? OR tags.comment LIKE ? OR tags.metadata
LIKE ?)", "%ruby%", "%ruby%", "%ruby%"]

>> (a + b | c).to_sql
# => ["((authors.name = ?) AND (articles.title LIKE ?)) OR (tags.name
LIKE ? OR tags.comment LIKE ? OR tags.metadata LIKE ?)", "jimbob", "%
ruby%", "%ruby%", "%ruby%", "%ruby%"]

Make sure you get this from the rubyforge repository and not the old
version. This plugin is heavily test driven.

svn://rubyforge.org//var/svn/ez-where

Cheers
-- Ezra Zygmuntowicz
-- Lead Rails Evangelist
-- ez@engineyard.com
-- Engine Yard, Serious Rails Hosting
-- (866) 518-YARD (9273)

comp.lang.ruby

Re: Lisp comprehensions => SQL

Victor 'Zverok' Shepelev

Logan Capaldo

Ken Bloom

Ezra Zygmuntowicz

x Login to ForumsZone