Lisp comprehensions => SQL

[Victor \Zverok\ Shepelev]

Hi all.

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

employees = Table.newid, :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.newid, :name, :sec_name, :salary, :age)
positions = Table.newid, :employee_id, osition_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/

 

[Logan Capaldo]

Hi all.

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

employees = Table.newid, :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.newid, :name, :sec_name, :salary, :age)
positions = Table.newid, :employee_id, osition_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

 

[David Vallner]

--------------enig61ED7DA553C91790ACB1CA76
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi all.
=20
Random idea, just for fun - using "list comprehensions" for SQL queries=

generation.
=20

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

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

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


--------------enig61ED7DA553C91790ACB1CA76
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iD8DBQFFdfmty6MhrS8astoRAnW5AJ9ppVyCvh6rqD2Zm4L3TUeRWz74bwCfcojd
Fz+tBJgzaECyI0iplY/hC4k=
=R24x
-----END PGP SIGNATURE-----

--------------enig61ED7DA553C91790ACB1CA76--

 

[Logan Capaldo]

From: Logan Capaldo [mailto:[email protected]]
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.newid, :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-been-busy

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_findall, :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_saccharin

 

[khaines]

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.newid, :name, :sec_name, :salary, :age)

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

Current Kansas:

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

Future Kansas:

employees = Kansas:BNAME::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.countEmployees) {|e| e.salary < 150}

Future:

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

employees = Table.newid, :name, :sec_name, :salary, :age)
positions = Table.newid, :employee_id, osition_name)

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

Current:

dbh.selectEmployees, ositions) {|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

 

[Ken Bloom]

Hi all.

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

employees = Table.newid, :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.newid, :name, :sec_name, :salary, :age)
positions = Table.newid, :employee_id, osition_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]

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]

From: Logan Capaldo [mailto:[email protected]]
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.newid, :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-been-busy

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_findall, :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

 

[Trans]

Hi all.

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

employees = Table.newid, :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.newid, :name, :sec_name, :salary, :age)
positions = Table.newid, :employee_id, osition_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.