From: Ken Bloom <kbloom@...>
Date: 2006-12-06T10:35:11+09:00
Subject: Re: Lisp comprehensions => SQL

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

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