Lisp comprehensions => SQL

Ruby
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. Language Integrated Query - Wikipedia
  3. http://rubyforge.org/projects/mongoose/
2

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

#generates "select * from Employees where name = ‘John’ and salary > 50

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

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 V.

4

From: Logan C. [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 12:15 AM

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
5

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

#generates "select count(*) from Employees where salary < 150
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
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

6

From: David V. [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 12:59 AM

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).

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.

7

From: Logan C. [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 2:32 AM

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.

8

On Wed, 6 Dec 2006, Victor “Zverok” Shepelev wrote:

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 H.

9

On Wed, 6 Dec 2006, Victor “Zverok” Shepelev wrote:

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.

Kirk H.

10

From: [email protected] [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 2:46 AM

Current Kansas:
employees.select do |e|

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.

11

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

#generates "select * from Employees where name = ‘John’ and salary > 50

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/

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/

12

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

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:

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

–Ken B.

13

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

generation.

(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 B.'s RubyNode-based solution in my own SqlStatement
library (which you should definitely check out).

–Ken

14

From: [email protected] [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 3:19 AM

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.

15

From: Ken B. [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 3:45 AM

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.

16

From: Ken B. [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 3:35 AM

e.age}[2,10]
obvious way)

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.

17

On Wed, 6 Dec 2006, Victor “Zverok” Shepelev wrote:

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.

Kirk H.

18

From: [email protected] [mailto:[email protected]]
Sent: Wednesday, December 06, 2006 12:14 PM

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.

19

On Wed, 6 Dec 2006, Victor “Zverok” Shepelev wrote:

“where condition applies”.
How about this:

employees.select do |e|
((e.name == ‘John’) & (e.salary > 50)).sort_by(e.age)[2…10]
end

I like that syntax, personally.

Kirk H.

20

On Wed, 6 Dec 2006, Victor “Zverok” Shepelev wrote:

It’s a matter of philosophy.
No, my point is that philosophy doesn’t matter there, because without
some
additional information, it can’t work quite like you have described it.

If you requires that a query start with a call to select(), but not be
invoked until a second method call, then it would be possible;

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

You could assemble all of the information that described the query with
the chained method calls; the sentinel that indicates the end of query
construction is the call to run()

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?)

That syntax seems very straight forward from a Ruby perspective and a
database interaction perspective.

  1. select all employees from the database where the name is ‘John’ and
    the
    salary is greater than 50. Return that result set. That’s a discrete
    method call that invokes a discrete database interaction.

  2. Sort the result set by the age field on the records.

  3. Return a slice from the result set.

I think your original wish – that all of those operations magically be
combined into a single query – would be counterintuitive if it were
possible without some sort of a final sentinel method call like I
illustrated above.

Kirk H.