Asp Forum - Lisp comprehensions => SQL

Victor 'Zverok' Shepelev

12/5/2006 9:59:00 PM

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...
2. http://en.wikipedia.org/wiki/Language_Integr...
3. http://rubyforge.org/projects...

6 Answers

Logan Capaldo

12/5/2006 10:15:00 PM

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
>
> 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...
> Thanks for your patience. And sorry me my English.
>
> V.
>
> 1. http://groups.inf.ed.ac...
> 2. http://en.wikipedia.org/wiki/Language_Integr...
> 3. http://rubyforge.org/projects...
>
>
>

David Vallner

12/5/2006 10:59:00 PM

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

David Vallner

khaines

12/6/2006 12:46:00 AM

Ken Bloom

12/6/2006 1:33:00 AM

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...
> 2. http://en.wikipedia.org/wiki/Language_Integr...
> 3. http://rubyforge.org/projects...

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

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

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

Ken Bloom

12/6/2006 1:40:00 AM

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

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

--Ken Bloom

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

Trans

12/6/2006 4:03:00 PM

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.

comp.lang.ruby

Lisp comprehensions => SQL

Victor 'Zverok' Shepelev

Logan Capaldo

David Vallner

khaines

Ken Bloom

Ken Bloom

Trans

x Login to ForumsZone