# ANN: Sequel 0.2.0 Released

**URL:** <https://www.ruby-forum.com/t/ann-sequel-0-2-0-released/113733>\
**Category:** Ruby\
**Created:** [September 4, 2007, 1:17pm UTC](https://www.ruby-forum.com/t/ann-sequel-0-2-0-released/113733 "2007-09-04T13:17:48Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![ciconia](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/71c47a/32.png) [@ciconia](https://www.ruby-forum.com/u/ciconia)\
**Post date:** [September 4, 2007, 1:17pm UTC](https://www.ruby-forum.com/t/ann-sequel-0-2-0-released/113733/1 "2007-09-04T13:17:48Z")

</div>

Sequel version 0.2.0 has just been released. This release includes a  
completely rewritten Ruby to SQL translator and a number of minor bug  
fixes.

# New R. to SQL translator

Sequel now has a new Ruby to SQL translator based on the wonderful  
ParseTree and Ruby2Ruby gems by the Seattle Ruby Brigade. You can now  
specify dataset filters using your favorite language, with virtually  
no limitations. Please note that column references must be specified  
as symbols. You can qualify column references by using the regular  
Sequel conventions:

:items\_\_id.to\_field\_name #=\> “items.id”  
:price\_\_\_p.to\_field\_name #=\> “price AS p”  
:items\_\_id\_\_\_iid.to\_field\_name #=\> “items.id AS iid”

First, some simple examples:

DB[:items].filter {:price \< 100}.sql  
#=\> “SELECT \* FROM items WHERE (price \< 100)”  
DB[:items].filter {:category == ‘Ruby’}.sql  
#=\> “SELECT \* FROM items WHERE (category = ‘Ruby’)”  
DB[:items].filter {:name =~ ‘AL%’}.sql  
#=\> “SELECT \* FROM items WHERE (name LIKE ‘AL%’)”  
DB[:items].filter {:state.nil?}.sql  
#=\> “SELECT \* FROM items WHERE (state IS NULL)”

There’s support for nested expressions with AND, OR and NOT:

DB[:items].filter {:x \> 5 && y \> 10}.sql  
#=\> “SELECT \* FROM items WHERE ((x \> 5) AND (y \> 10))”

DB[:items].filter {(:x == 1 || :y == 2) && :z != 3}.sql  
#=\> “SELECT \* FROM items WHERE (((x = 1) OR (y = 2)) AND (NOT (z =  
3)))”

You can use arithmetic operators and specify SQL functions:

DB[:items].filter {(:x + :y) \> :z}.sql  
#=\> “SELECT \* FROM items WHERE ((x + y) \> z)”

DB[:items].filter {:price \< :AVG[:price] + 100}.sql  
#=\> “SELECT \* FROM items WHERE (price \< (AVG(price) + 100))”

You can also easily create parameterized queries by including  
references to Ruby variables, constants, globals, object attributes  
etc.:

t = Time.now - 86400 # 1 day ago  
DB[:items].filter {:stamp \<= t}.sql  
#=\> “SELECT \* FROM items WHERE (stamp \<=  
‘2007-09-03T13:32:29+03:00’)”

RUBY = ‘ruby’  
DB[:items].filter {:category == RUBY}.sql  
#=\> “SELECT \* FROM items WHERE (category = ‘ruby’)”

There’s the usual support for arrays, ranges:

DB[:items].filter {:id =\> [1, 2, 3]}.sql  
#=\> “SELECT \* FROM items WHERE (id IN (1, 2, 3))”

DB[:items].filter {:id == (4…6)}.sql # inclusive  
#=\> “SELECT \* FROM items WHERE (id \>= 4 AND id \<= 6)”

DB[:items].filter {:id == (4…6)}.sql # exclusive  
#=\> “SELECT \* FROM items WHERE (id \>= 4 AND id \< 6)”

And support for sub-queries:  
DB[:orders].filter {:price \>= DB[:items].select(:price)}.sql  
#=\> “SELECT \* FROM orders WHERE (price \>= (SELECT price FROM  
items))”

You can also specify filter expressions as separate statements:  
DB[:items].filter do  
:x == 1  
:y \> 2  
:z \< 3  
end.sql  
#=\> “SELECT \* FROM items WHERE ((x = 1) AND (y \> 2) AND (z \< 3))”

Plus, you get correct literalization of values and protection from SQL  
injection for free.

# Bug fixes and other changes

- DB#from now accepts a filter block, e.g.: DB.from(:items) {:x == 1}

- Added support for specifying SQL functions based on symbols, e.g.:  
DB[:items].select(:price.STD)

- Fixed possible deadlock bug in ConnectionPool.

- Fixed Model.drop\_table (thanks Duane J…)

=============================================

Sequel project page:  
[http://code.google.com/p/ruby-sequel](http://code.google.com/p/ruby-sequel)

Sequel documentation:  
[http://sequel.rubyforge.org](http://sequel.rubyforge.org)

Join the Sequel-talk group:  
[http://groups.google.com/group/sequel-talk](http://groups.google.com/group/sequel-talk)

Install the gem:  
sudo gem install sequel

Or check out the source and install manually:  
svn co [http://ruby-sequel.googlecode.com/svn/trunk](http://ruby-sequel.googlecode.com/svn/trunk) sequel  
cd sequel  
rake install