ANN: Sequel 0.2.0 Released

[Sharon Rosner]

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 Ruby 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"
rice___p.to_field_name #=> "price AS p"
:items__id___iid.to_field_name #=> "items.id AS iid"

First, some simple examples:

DB[:items].filter {rice < 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 {rice < :AVG[rice] + 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[rders].filter {rice >= DB[:items].selectprice)}.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.fromitems) {:x == 1}

* Added support for specifying SQL functions based on symbols, e.g.:
DB[:items].selectprice.STD)

* Fixed possible deadlock bug in ConnectionPool.

* Fixed Model.drop_table (thanks Duane Johnson.)

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

Sequel project page:
<http://code.google.com/p/ruby-sequel>

Sequel documentation:
<http://sequel.rubyforge.org>

Join the Sequel-talk group:
<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 sequel
cd sequel
rake install