# SqlStatement 2.0 -- Generate complex SQL statements programmatically

**URL:** <https://www.ruby-forum.com/t/sqlstatement-2-0-generate-complex-sql-statements-programmatically/126632>\
**Category:** Ruby\
**Created:** [January 20, 2008, 6:55pm UTC](https://www.ruby-forum.com/t/sqlstatement-2-0-generate-complex-sql-statements-programmatically/126632 "2008-01-20T18:55:53Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [January 20, 2008, 6:55pm UTC](https://www.ruby-forum.com/t/sqlstatement-2-0-generate-complex-sql-statements-programmatically/126632/1 "2008-01-20T18:55:53Z")

</div>

# 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](http://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

---

<div class="post-metadata">

**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\
**Post date:** [January 20, 2008, 11:11pm UTC](https://www.ruby-forum.com/t/sqlstatement-2-0-generate-complex-sql-statements-programmatically/126632/2 "2008-01-20T23:11:01Z")

</div>

On Sun, 20 Jan 2008 11:40:31 -0600, Ken B. wrote:

> # sqlstatement - Generate complex SQL statements programmatically

I forgot to mention that the documentation is online at  
[http://sqlstatement.rubyforge.org/](http://sqlstatement.rubyforge.org/)

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