Asp Forum
Home
|
Login
|
Register
|
Search
Forums
>
comp.lang.ruby
[ANN] SqlStatement 2.0 -- Generate complex SQL statements programmatically
Ken Bloom
1/20/2008 5:41:00 PM
sqlstatement - Generate complex SQL statements programmatically
===============================================================
The main goal of this library is to be able to construct an SQL statement
from "slices" that concern different aspects of the final query (perhaps
in different places in your code) and then combine them all together into
one statement easily.
Another important goal of this library is to give some consistent Ruby
syntax to three statements (INSERT, SELECT, and UPDATE) that seem to have
different enough syntax that one has two write different code to generate
each kind of statement.
I use my SQL database (specifically MySQL) largely as a bulk data
processing engine, by doing INSERTâ?¦SELECT or CREATE TABLEâ?¦SELECT
statements. This library is intended to make that kind of coding easier.
I expect that Object Relational mappers (such as ActiveRecord) are more
useful for most people, who are performing queries and inserting/updating/
querying for individual records. I have nevertheless added INSERTâ?¦VALUES
statements, and will add other statements soon, for consistency.
This library is inspired by CLSQL for Common LISP, or SchemeQL for
Scheme, although it is very different from these two libraries. Scheme
and LISPâ??s use of s-expressions make it very easy to construct an entire
sublanguage for the WHERE clause, simply by list parsing. The Criteria
library for Ruby has attempted this, but in a more limited manner than
SchemeQL or CLSQL. My library aims to cover much of the functionality in
these libraries.
This library doesnâ??t try to abstract out the limitations of your DBMS,
and I think that the SQL it uses should be fairly portable, in large
measure because it hasnâ??t attempted to deal with serious CREATE TABLE
statements, where a lot of syntax concerning types, keys and sequences is
much more variable.
This library can be downloaded from rubyforge.org/projects/sqlstatement
Changes in this release
=======================
* Vastly redesign the DSL. The methods for adding components to an
SQL statement have all been renamed, and internal state is no longer
directly exposed to the outside. The interface is now much more intutive
and consistent. Please read the documentation to find out how this
version of the library works.
* Left joins have been added
* Thereâ??s no more SelectParts class â?? the corresponding semantics
have been built directly into the Select class.
* Select.new do |s| â?¦ end syntax has been added
* Select statements now remember the order of fields and tables. This
makes the next two changes feasible.
o Support for Mysqlâ??s STRAIGHT_JOIN modifier has been added
o Unit tests have been added
Example
=======
If we wanted to generate SQL code similar to
SELECT `value1`,`value2`
FROM `jointable`,`dictionary1`,`dictionary2`
WHERE `jointable`.`id1`=`dictionary1`.`id` AND
`jointable`.`id2`=`dictionary2`.`id`
we could generate it as follows:
def foo(x)
Select.new do |s|
s.field :"value#{x}"
s.table :"dictionary#{x}"
s.condition string_func("`jointable`.`id#{x}`=`dictionary#{x}
`.`id`")
end
end
stmt=Select.new
stmt.table :jointable
(1..2).each do |x|
stmt << foo(x)
end
dbh.execute(stmt)
or like this:
stmt=Select.new do |s|
s.table :jointable
[1,2].each do |num|
s.field "value#{num}".dbid
s.table "dictionary#{num}".dbid
s.condition string_func("`jointable`.`id#{num}`=`dictionary#{num}
`.`id`")
end
end
dbh.execute stmt
--
Ken (Chanoch) Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu...
1 Answer
Ken Bloom
1/20/2008 9:42:00 PM
0
On Sun, 20 Jan 2008 11:40:31 -0600, Ken Bloom wrote:
> sqlstatement - Generate complex SQL statements programmatically
> ===============================================================
I forgot to mention that the documentation is online at
http://sqlstatement.ruby...
> Example
> =======
> If we wanted to generate SQL code similar to
>
> SELECT `value1`,`value2`
> FROM `jointable`,`dictionary1`,`dictionary2` WHERE
> `jointable`.`id1`=`dictionary1`.`id` AND
> `jointable`.`id2`=`dictionary2`.`id`
The examples actually generate slightly more verbose SQL code, using
INNER JOIN instead of a comma to indicate a join. (This is to avoid
precedence issues when performing LEFT JOINs).
--Ken
--
Ken (Chanoch) Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu...
Servizio di avviso nuovi messaggi
Ricevi direttamente nella tua mail i nuovi messaggi per
[ANN] SqlStatement 2.0 -- Generate complex SQL statements programmatically
Inserendo la tua e-mail nella casella sotto, riceverai un avviso tramite posta elettronica ogni volta che il motore di ricerca troverà un nuovo messaggio per te
Il servizio è completamente GRATUITO!
x
Login to ForumsZone
Login with Google
Login with E-Mail & Password