Lisp comprehensions => SQL

ruby-talk
1

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

Something like this. (I still don't know, how to limit selected columns in
obvious way)

And even more complex:

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

#generates "select * from Employees, Positions where Employees.id ==
Positions.employee_id"

Idea is completely stolen from Phil Wadler's Links[1] language.
Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

But the realization of above seems to be interesting task (for RubyQuiz, may
be?)

Thanks for your patience. And sorry me my English.

V.

1. http://groups.inf.ed.ac.uk/links/
2. http://en.wikipedia.org/wiki/Language_Integrated_Query
3. http://rubyforge.org/projects/mongoose/

2

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

Something like this. (I still don't know, how to limit selected columns in
obvious way)

And even more complex:

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

#generates "select * from Employees, Positions where Employees.id ==
Positions.employee_id"

Idea is completely stolen from Phil Wadler's Links[1] language.
Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

But the realization of above seems to be interesting task (for RubyQuiz, may
be?)

The ez_where plugin for rails does this.
http://brainspl.at/articles/2006/01/30/i-have-been-busy

···

On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev wrote:

Thanks for your patience. And sorry me my English.

V.

1. http://groups.inf.ed.ac.uk/links/
2. Language Integrated Query - Wikipedia
3. http://rubyforge.org/projects/mongoose/

3

Victor "Zverok" Shepelev wrote:

Hi all.

Random idea, just for fun - using "list comprehensions" for SQL queries
generation.

I think it has been done verbatim, can't recall the library. Search the
archives?

Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

MS's LINQ does the opposite, allowing SQL-ish constructs to express list
comprehensions on steroids. Well, basically; I have my doubts about the
more relational features like joins on object collections performing any
well (no concept of the dataset, no analysis thereof to do query
planning, probably no indexing either) or making actual sense whatsoever
(why join when you can just use direct references?). From a design point
of view, I'd prefer preintegrating data into a clean domain model
instead of bridging over with the band-aid that is generalised data
structure query. It's boilerplate, more effort, and less "whoa, cool",
but you get a high level of consistency in the core business logic if
you take care of the data format impedance mismatch up front instead of
putting over a bandaid later. (No "What paradigm am I coding THIS line
in again?" issues.) The field of data modelling seems way too varied to
me for one query language / paradigm to work universally without kludges
appearing around the edges.

(For the obvious impaired: I just don't like LINQ. By far the ugliest
and most out of place addition to C#3.0.)

But the realization of above seems to be interesting task (for RubyQuiz, may
be?)

Well, to work with blocks, you'd need to involve ParseTree or something
like that, unless you're willing to offload some of the logic back to
the interpreter (gruesomely laggy). That might be a little out of scope
for a RubyQuiz. Then again, having seen some of the more golfy /
braintwisting solutions to some problems, maybe not :slight_smile:

David Vallner

4

A lot of these things are either supported in Kansas, or are in the drawing board for when I can get back to Kansas development.

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]

Current Kansas:

dbh.select(:Employees) do |e|
   sort_by(e.age)
   limit(2,10)
   (e.name == 'John') & (e.salary > 50)
end

Future Kansas:

employees = Kansas::DBNAME::Employees
employees.select do |e|
   sort_by(e.age)
   limit(2,10)
   (e.name == 'John') & (e.salary > 50)
end

employees.select{|e| e.salary < 150}.count

Current:

dbh.count(:Employees) {|e| e.salary < 150}

Future:

employees.count {|e| e.salary < 150}

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

Current:

dbh.select(:Employees, :Positions) {|e,p| e.id == p.employee_id}

I have not worked on Kansas much in the last year because it is stable for me, but there are a number of things that I have planned when I can set aside some time to get back around to it.

Kirk Haines

···

On Wed, 6 Dec 2006, Victor "Zverok" Shepelev wrote:

5

Have you had a look at my SqlStatement library?
http://sqlstatement.rubyforge.org/

It's not quite like what you're asking, as it prefers to look a little
bit more like SQL translated into Ruby. It's really designed more for
programmatic manipulation of SQL statements, and for using Ruby to drive
processing performed in a SQL database.

You might also want to look at Criteria. http://mephle.org/Criteria/

···

On Wed, 06 Dec 2006 06:58:49 +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

Something like this. (I still don't know, how to limit selected columns in
obvious way)

And even more complex:

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

#generates "select * from Employees, Positions where Employees.id ==
Positions.employee_id"

Idea is completely stolen from Phil Wadler's Links[1] language.
Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

But the realization of above seems to be interesting task (for RubyQuiz, may
be?)

Thanks for your patience. And sorry me my English.

V.

1. http://groups.inf.ed.ac.uk/links/
2. Language Integrated Query - Wikipedia
3. http://rubyforge.org/projects/mongoose/

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

6

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

Something like this. (I still don't know, how to limit selected columns in
obvious way)

And even more complex:

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

#generates "select * from Employees, Positions where Employees.id ==
Positions.employee_id"

Idea is completely stolen from Phil Wadler's Links[1] language.
Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

But the realization of above seems to be interesting task (for RubyQuiz, may
be?)

Thanks for your patience. And sorry me my English.

I think Og can already do this, more or less.

T.

7

To: ruby-talk ML
Subject: Re: Lisp comprehensions => SQL

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.
Ruby on Rails Blog / What is Ruby on Rails for?

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. Syntactic sugar - Wikipedia

···

From: Logan Capaldo [mailto:logancapaldo@gmail.com]
Sent: Wednesday, December 06, 2006 12:15 AM

On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev wrote:

8

To: ruby-talk ML
Subject: Re: Lisp comprehensions => SQL

Victor "Zverok" Shepelev wrote:

Hi all.

Random idea, just for fun - using "list comprehensions" for SQL queries
generation.

I think it has been done verbatim, can't recall the library. Search the
archives?

Hmm... can't imagine right keywords for search (as "list comprehension"
returns no likewise results).

Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

MS's LINQ does the opposite, allowing SQL-ish constructs to express list
comprehensions on steroids. Well, basically; I have my doubts about the
more relational features like joins on object collections performing any
well (no concept of the dataset, no analysis thereof to do query
planning, probably no indexing either) or making actual sense whatsoever
(why join when you can just use direct references?). From a design point
of view, I'd prefer preintegrating data into a clean domain model
instead of bridging over with the band-aid that is generalised data
structure query. It's boilerplate, more effort, and less "whoa, cool",
but you get a high level of consistency in the core business logic if
you take care of the data format impedance mismatch up front instead of
putting over a bandaid later. (No "What paradigm am I coding THIS line
in again?" issues.) The field of data modelling seems way too varied to
me for one query language / paradigm to work universally without kludges
appearing around the edges.

OK, I've taken your point.
Just 2 notes:
1. I was really inspired by Links language - but it is from Haskell's author
Phil Wadler, therefore completely functional.
2. My typical task is small desktop programs in a manner of "usable
proof-of-concept", so, they are just a mix of clean-and-simple-concepts and
pretty-quick-hacks. The idea discussed looks close to latter :slight_smile:

(For the obvious impaired: I just don't like LINQ. By far the ugliest
and most out of place addition to C#3.0.)

Yeah, completely agreed. I was mentioned it only as Phil Wadler did.

But the realization of above seems to be interesting task (for RubyQuiz,

may be?)

Well, to work with blocks, you'd need to involve ParseTree or something
like that, unless you're willing to offload some of the logic back to
the interpreter (gruesomely laggy). That might be a little out of scope
for a RubyQuiz.

No-no. I think about some very simple, like special objects taken into
blocks, which translates its methods <, >, ==, && to something, which can be
done as SQL. That's why I consider it as an interesting task (despite of
your treating of overall idea).

V.

···

From: David Vallner [mailto:david@vallner.net]
Sent: Wednesday, December 06, 2006 12:59 AM

9

A lot of these things are either supported in Kansas, or are in the
drawing board for when I can get back to Kansas development.

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]

Current Kansas:

dbh.select(:Employees) do |e|
  sort_by(e.age)
  limit(2,10)
  (e.name == 'John') & (e.salary > 50)
end

Future Kansas:

employees = Kansas::DBNAME::Employees
employees.select do |e|
  sort_by(e.age)
  limit(2,10)
  (e.name == 'John') & (e.salary > 50)
end

employees.select{|e| e.salary < 150}.count

Current:

dbh.count(:Employees) {|e| e.salary < 150}

Future:

employees.count {|e| e.salary < 150}

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

Current:

dbh.select(:Employees, :Positions) {|e,p| e.id == p.employee_id}

I have not worked on Kansas much in the last year because it is stable for
me, but there are a number of things that I have planned when I can set
aside some time to get back around to it.

Interesting. But I still don't understand, why to use custom DSL instead of
familiar Ruby (ok, with changin && => &)

#having
employees.select do |e|
   sort_by(e.age)
   limit(2,10)
   (e.name == 'John') & (e.salary > 50)
end

#proposal:

employees.select{|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e|
e.age}[2..10]

The latter is "just Ruby".

V.

···

From: khaines@enigo.com [mailto:khaines@enigo.com]
Sent: Wednesday, December 06, 2006 2:46 AM

On Wed, 6 Dec 2006, Victor "Zverok" Shepelev wrote:

10

It already was a ruby quiz. Or at least something close enough to it was.
See Ruby Quiz #95 and its solutions.

--Ken Bloom

···

On Wed, 06 Dec 2006 07:58:56 +0900, David Vallner wrote:

Victor "Zverok" Shepelev wrote:

Hi all.

Random idea, just for fun - using "list comprehensions" for SQL queries
generation.

[SNIP]

But the realization of above seems to be interesting task (for RubyQuiz, may
be?)

Well, to work with blocks, you'd need to involve ParseTree or something
like that, unless you're willing to offload some of the logic back to
the interpreter (gruesomely laggy). That might be a little out of scope
for a RubyQuiz. Then again, having seen some of the more golfy /
braintwisting solutions to some problems, maybe not :slight_smile:

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

11

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

Something like this. (I still don't know, how to limit selected columns

in

obvious way)

And even more complex:

employees = Table.new(:id, :name, :sec_name, :salary, :age)
positions = Table.new(:id, :employee_id, :position_name)

(employees + positions).select{|e, p| e.id == p.employee_id}

#generates "select * from Employees, Positions where Employees.id ==
Positions.employee_id"

Idea is completely stolen from Phil Wadler's Links[1] language.
Also MS's LINQ and ruby's Mongoose DB seems to do something like this.

But the realization of above seems to be interesting task (for RubyQuiz,

may

be?)

Thanks for your patience. And sorry me my English.

V.

1. http://groups.inf.ed.ac.uk/links/
2. http://en.wikipedia.org/wiki/Language_Integrated_Query
3. http://rubyforge.org/projects/mongoose/

Have you had a look at my SqlStatement library?
http://sqlstatement.rubyforge.org/

It's not quite like what you're asking, as it prefers to look a little
bit more like SQL translated into Ruby. It's really designed more for
programmatic manipulation of SQL statements, and for using Ruby to drive
processing performed in a SQL database.

You might also want to look at Criteria. http://mephle.org/Criteria/

This approach (somethin-like-SQL inside Ruby) is acceptable. But for me, it
is not seem very natural.

V.

···

From: Ken Bloom [mailto:kbloom@gmail.com]
Sent: Wednesday, December 06, 2006 3:35 AM

On Wed, 06 Dec 2006 06:58:49 +0900, Victor \"Zverok\" Shepelev wrote:

12

From: Logan Capaldo [mailto:logancapaldo@gmail.com]
Sent: Wednesday, December 06, 2006 12:15 AM
>To: ruby-talk ML
>Subject: Re: Lisp comprehensions => SQL
>
>> 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.
>Ruby on Rails Blog / What is Ruby on Rails for?

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

···

On Wed, Dec 06, 2006 at 07:27:31AM +0900, Victor Zverok Shepelev wrote:

>On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev wrote:

   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. Syntactic sugar - Wikipedia

13

Actually, you could do that second one with Kansas. With the current Kansas, it'd be:

dbh.select(:Employees) {|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e| e.age}[2..10]

The difference, though, is that it won't do all of that on the database.

The database will do "select * from employees where e.name = 'John' and e.salary > 50" and then the results will be sorted by Ruby and the set extracted by Ruby.

Kirk Haines

···

On Wed, 6 Dec 2006, Victor "Zverok" Shepelev wrote:

#having
employees.select do |e|
  sort_by(e.age)
  limit(2,10)
  (e.name == 'John') & (e.salary > 50)
end

#proposal:

employees.select{|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e|
e.age}[2..10]

The latter is "just Ruby".

14

From: Logan Capaldo [mailto:logancapaldo@gmail.com]
Sent: Wednesday, December 06, 2006 12:15 AM

To: ruby-talk ML
Subject: Re: Lisp comprehensions => SQL

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.
Ruby on Rails Blog / What is Ruby on Rails for?

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)

···

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

On Wed, Dec 06, 2006 at 06:58:49AM +0900, Victor Zverok Shepelev >> wrote:

15

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

Ooops. I was a C++ guy before Ruby. I've just forgotten Ruby has no
overridable && operator. Sorry my stupidness.

(ok, we can do some 'imitation' with & and | operators, but the overall
beauty of "no DSL" idea is failed).

Thanks for your comments.

V.

···

From: Logan Capaldo [mailto:logancapaldo@gmail.com]
Sent: Wednesday, December 06, 2006 2:32 AM

16

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

···

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.
>Ruby on Rails Blog / What is Ruby on Rails for?

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

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

17

#having
employees.select do |e|
  sort_by(e.age)
  limit(2,10)
  (e.name == 'John') & (e.salary > 50)
end

#proposal:

employees.select{|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e|
e.age}[2..10]

The latter is "just Ruby".

Actually, you could do that second one with Kansas. With the current
Kansas, it'd be:

dbh.select(:Employees) {|e| (e.name == 'John') & (e.salary >
50)}.sort_by{|e| e.age}[2..10]

The difference, though, is that it won't do all of that on the database.

The database will do "select * from employees where e.name = 'John' and
e.salary > 50" and then the results will be sorted by Ruby and the set
extracted by Ruby.

This is predictable, but not very interesting.

v.

···

From: khaines@enigo.com [mailto:khaines@enigo.com]
Sent: Wednesday, December 06, 2006 3:19 AM

On Wed, 6 Dec 2006, Victor "Zverok" Shepelev wrote:

18

From: Logan Capaldo [mailto:logancapaldo@gmail.com]

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 !=.

Hmm, really. Still my C++-ish habits for "all operators are overloadable" :frowning:

V.

···

From: Ken Bloom [mailto:kbloom@gmail.com]
Sent: Wednesday, December 06, 2006 3:45 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:

19

Think about the syntax of it, though.

From a ruby perspective, you call select(), then sort_by(), then

Three separate method calls, and there is no way any of them can know which is the last one. So there's no way to assemble the SQL call you would like to see, where a single query is assembled to implement what is implied by those three separate method calls.

To me, the logical thing is to have the select() call be the one that actually interacts with the database, and I am using "just Ruby" inside that select() block, and I know what I am getting back from each method call in the chain.

Kirk Haines

···

On Wed, 6 Dec 2006, Victor "Zverok" Shepelev wrote:

employees.select{|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e|
e.age}[2..10]

The latter is "just Ruby".

Actually, you could do that second one with Kansas. With the current
Kansas, it'd be:

dbh.select(:Employees) {|e| (e.name == 'John') & (e.salary >
50)}.sort_by{|e| e.age}[2..10]

The difference, though, is that it won't do all of that on the database.

The database will do "select * from employees where e.name = 'John' and
e.salary > 50" and then the results will be sorted by Ruby and the set
extracted by Ruby.

This is predictable, but not very interesting.

20

employees.select{|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e|
e.age}[2..10]

The latter is "just Ruby".

Actually, you could do that second one with Kansas. With the current
Kansas, it'd be:

dbh.select(:Employees) {|e| (e.name == 'John') & (e.salary >
50)}.sort_by{|e| e.age}[2..10]

The difference, though, is that it won't do all of that on the database.

The database will do "select * from employees where e.name = 'John' and
e.salary > 50" and then the results will be sorted by Ruby and the set
extracted by Ruby.

This is predictable, but not very interesting.

Think about the syntax of it, though.

From a ruby perspective, you call select(), then sort_by(), then

Three separate method calls, and there is no way any of them can know
which is the last one. So there's no way to assemble the SQL call you
would like to see, where a single query is assembled to implement what is
implied by those three separate method calls.

To me, the logical thing is to have the select() call be the one that
actually interacts with the database, and I am using "just Ruby" inside
that select() block, and I know what I am getting back from each method
call in the chain.

It's a matter of philosophy.

Trying to read this:

dbh.select(:Employees) do |e|
   sort_by(e.age)
   limit(2,10)
   (e.name == 'John') & (e.salary > 50)
end

cause a bunch of questions from Ruby perspective. I "can't understand"
(intuitively, I mean) "what would be sorted", "what would be limited",
"where condition applies".

To the contrary, trying to read this

employees.select{|e| (e.name == 'John') & (e.salary > 50)}.sort_by{|e|
e.age}[2..10]

is very familiar for any rubyist; but also cause a bunch of questions from
SQL perspective (would it be performed in one request or several? How many
data would be selected? What's the overhead?)

Which is better? Any opinion is possible, there is no "the best for
everyone". Personally I dislike your variant as it is neither SQL now Ruby.
But you arguments are completely understandable.

V.

···

From: khaines@enigo.com [mailto:khaines@enigo.com]
Sent: Wednesday, December 06, 2006 12:14 PM

On Wed, 6 Dec 2006, Victor "Zverok" Shepelev wrote: