# ANN: Sequel 2.1.0 Released

**URL:** <https://www.ruby-forum.com/t/ann-sequel-2-1-0-released/140705>\
**Category:** Ruby\
**Created:** [June 17, 2008, 7:21pm UTC](https://www.ruby-forum.com/t/ann-sequel-2-1-0-released/140705 "2008-06-17T19:21:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![nerdmaster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/c68b51/32.png) [@nerdmaster](https://www.ruby-forum.com/u/nerdmaster)\
**Post date:** [June 17, 2008, 7:21pm UTC](https://www.ruby-forum.com/t/ann-sequel-2-1-0-released/140705/1 "2008-06-17T19:21:25Z")

</div>

Sequel is a lightweight database access toolkit for Ruby.

- Sequel provides thread safety, connection pooling and a concise DSL  
for constructing database queries and table schemas.
- Sequel also includes a lightweight but comprehensive ORM layer for  
mapping records to Ruby objects and handling associated records.
- Sequel makes it easy to deal with multiple records without having  
to break your teeth on SQL.
- Sequel currently has adapters for ADO, DB2, DBI, Informix, JDBC,  
MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.

Sequel 2.1.0 has been released and should be available on the gem  
mirrors. 2.1.0 has the following exciting new features:

## Model Improvements

- one\_to\_many/many\_to\_many associations now support a :limit option,  
adding a limit/offset to the records returned. This was possible  
before using a block, so it is just added for convenience.

- Associations now support a :read\_only option, which doesn’t create  
methods that modify the database.

- Associations now support a :graph\_select option, which allows  
specifying the columns of associated models to include when using  
eager\_graph.

- one\_to\_many associations now have a :one\_to\_one option. When used  
it creates a getter and setter method similar to many\_to\_one. This  
fills the same role as ActiveRecord’s has\_one, but it is  
implemented as a couple of convenience methods over one\_to\_many, so  
it still requires that you specify the association name as a  
plural.

- Model datasets now have to\_hash augmented so that it can be called  
without any arguments, in which case it yields an identity map (a  
hash with keys being primary key values and values being model  
instances).

- The Model.set\_sti\_key method was added, for easily setting up  
single table inheritance. It should be called only in the parent  
class.

- Calls to def\_dataset\_method with a block are now cached and  
reapplied to the new dataset if set\_dataset is called afterward,  
or in a subclass.

- All validation methods can now be made conditional via an :if  
option, which takes either a symbol (which specifies an instance  
method) or a proc (which is instance\_evaled).

- Model#set and Model#update have been added back, they are now  
aliases of #set\_with\_params and #update\_with\_params.

- Models now have set\_only/set\_except/update\_only/update\_except  
instance methods that take a hash (like you would provide to  
set or update) and additional arguments specifying which columns  
to allow or disallow.

- Models now have a set\_allowed\_columns and set\_restricted\_columns  
methods, which operate similarly to ActiveRecord’s attr\_accessible  
and attr\_protected. It is recommend that you use the set\_only or  
update\_only instead of these methods, though. You can ignore the  
allowed or restricted columns by using #set\_all or #update\_all.

- The primary key column(s) is restricted by default. To allow it to  
be set via new/set/update, use:

- It is now easy to override the one\_to\_many/many\_to\_many association  
methods that modify the database (add\_/remove\_/remove\_all\_), as  
they have been broken into two methods, one that handles the  
caching features and a private one (prepended with an \_) that  
handles the database changes (and which you can easily override  
without worrying about the caching).

## Table Joining

Dataset#join\_table got a nice overhaul. You can now use any join  
type your database allows:

DB[:artist].join\_table(:natural, :albums)  
DB[:numbers].join\_table(:cross, :numbers)

You can now specify the conditions as

- String: “a.b = c.d” # ON a.b = c.d
- Expression :x \< :y # ON x \< y
- Array of Symbols: [:x, :y, :z] # USING (x, y, z)
- nil # no conditions, used for NATURAL or CROSS joins

Dataset#join\_table also takes a block that yields three arguments:

- join\_table\_alias - The alias/name of the table currently being  
joined
- last\_join\_table\_alias - The alias name of the last table joined  
(if there was one) or the first FROM table (if not).
- joins - An array of JoinClause objects for all previous joins in  
the query.

Using the block you can specify conditions for complex joins without  
needing to know in advance what table aliases will be used.

## Expanded SQL Syntax Support

SQL Case statements are now supported directly using hashes or  
arrays:

{:x \> 1 =\> 1}.case(0)

# CASE WHEN x \> 1 THEN 1 ELSE 0 END

[[{:x=\>1}, 0], [:x \< 1, 1], [:x \> 1, 2]].case(-1)

# CASE WHEN x = 1 THEN 0 WHEN x \< 1 THEN 1 WHEN x \> 1 THEN 2

```
ELSE -1 END

```

You should use an array instead of a hash for multiple conditions  
unless all conditions are orthogonal.

The SQL extract function has special syntax:

EXTRACT(day FROM date)

This syntax is now supported via the following ruby code:

:date.extract(:day)

## Other Notable Changes

- The sequel command line tool can now run migrations. The -m  
option specifies the directory holding the migration files,  
and the -M options specifies the version to which to migrate.

- The PostgreSQL adapter supports nested transactions/savepoints.

- The schema parser now understands decimal fields, and will  
typecast to BigDecimal.

- PostgreSQL’s numeric type is now recognized and returned as  
BigDecimal.

- HAVING now comes before ORDER BY, which most databases seem to  
prefer. If your database wants HAVING after ORDER BY, please  
let us know.

- Symbol#qualify now exists, to specify the table name for a given  
symbol, similar to the use of #as to specify an alias. This is  
mainly helpful in conjuction with the #join\_table block, as that  
provides the table aliases to use to qualify the columns inside  
the block.

- BitwiseMethods (&, |, ^, ~, \<\<, \>\>) have been added to the  
NumericExpression class, so you can do the following:

- #cast\_numeric and #cast\_string exist for Symbols, Strings, and  
other Sequel Expressions, which return the results casted and  
wrapped in either NumericExpression or StringExpression, so you  
can use the BitwiseMethods (&, |, ^, ~, \<\<, \>\>) or  
StringConcatenationMethods (+) directly.

# Dataset#to\_hash can take only one argument, in which case it uses

that argument to specify the key, and uses the entire hash for the  
value.

# Dataset#graph can now take an array of columns to select from the

joined table via the :select option.

# Dataset#filter and similar methods now combine the block and

regular argument conditions if both are given, instead of ignoring  
the regular argument conditions.

# Dataset#filter(false) can now be used to make sure that no records

are returned. Dataset#filter(true) also works, but it’s a no-op.  
Before, these raised errors.

# Dataset#count does a subquery for a dataset using DISTINCT, since

the otherwise it would yield a count for the query without  
DISTINCT.

## ParseTree Support Officially Deprecated

The support for ParseTree-based block filters has officially been  
deprecated and will be removed in Sequel 2.2. To use the  
expression filters (which don’t require ParseTree) inside blocks,  
use:

SEQUEL\_NO\_PARSE\_TREE = true  
require ‘sequel’

# OR

require ‘sequel’  
Sequel.use\_parse\_tree = false

This is the default if ParseTree cannot be loaded. If ParseTree  
can be loaded, it remains the default, in order not to immediately  
break existing code.

With this set, you can use the expression filters inside of blocks:

dataset.filter{((:x + 1) & 10 \< :y) & :z}

That doesn’t gain you all that much, but there are some methods  
that feed block arguments into filter, such as the following:

dataset.first(5){((:x + 1) & 10 \< :y) & :z}

Which will get you the first 5 records matching the condition.

## Backwards Incompatible Changes

- To change the datetime classe used from Time to DateTime, you  
now use:

- Models now raise errors if you try to access a missing or  
restricted method via new/set/update, instead of just silently  
skipping that parameter. To get the old behavior:

- The association\_dataset method now takes into account the :eager  
option and the block argument, where it didn’t before. It also  
takes into account the new :limit option.

- Association methods now raise errors in most cases if the model  
doesn’t have a valid primary key.

- Dataset#join\_table used to allow a symbol as a conditions argument  
as a shortcut for a hash:

- MySQL users need to be careful when upgrading, the following code  
will once again cause an error:

- String#asc and String#desc are no longer defined, as ordering on a  
plain string column should be a no-op. They are still defined  
on LiteralStrings.

- You can no longer abuse the SQL::Function syntax to use a table  
alias with specified columns (e.g. :table[:col1, :col2, :col3])  
or to cast to types (e.g. :x.cast\_as(:varchar[20])). Use a  
LiteralString in both cases.

If you have any questions, please post on the Google Group.

Thanks,  
Jeremy

- {Source code}[[GitHub - jeremyevans/sequel: Sequel: The Database Toolkit for Ruby](http://github.com/jeremyevans/sequel)]
- {Bug tracking}[[Google Code Archive - Long-term storage for Google Code Project Hosting.](http://code.google.com/p/ruby-sequel/issues/list)]
- {Google group}[[http://groups.google.com/group/sequel-talk](http://groups.google.com/group/sequel-talk)]
- {RDoc}[[http://sequel.rubyforge.org](http://sequel.rubyforge.org)]

---

<div class="post-metadata">

**Author:** ![nerdmaster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/c68b51/32.png) [@nerdmaster](https://www.ruby-forum.com/u/nerdmaster)\
**Post date:** [June 18, 2008, 5:09am UTC](https://www.ruby-forum.com/t/ann-sequel-2-1-0-released/140705/2 "2008-06-18T05:09:22Z")

</div>

Jeremy E. wrote:

> Sequel is a lightweight database access toolkit for Ruby.
> 
> - Sequel provides thread safety, connection pooling and a concise DSL  
> for constructing database queries and table schemas.
> - Sequel also includes a lightweight but comprehensive ORM layer for  
> mapping records to Ruby objects and handling associated records.
> - Sequel makes it easy to deal with multiple records without having  
> to break your teeth on SQL.
> - Sequel currently has adapters for ADO, DB2, DBI, Informix, JDBC,  
> MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.

Can I use sequel for MSSQL database?

Regards,  
Shin Guey

---

<div class="post-metadata">

**Author:** ![nerdmaster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/c68b51/32.png) [@nerdmaster](https://www.ruby-forum.com/u/nerdmaster)\
**Post date:** [June 18, 2008, 5:35am UTC](https://www.ruby-forum.com/t/ann-sequel-2-1-0-released/140705/3 "2008-06-18T05:35:36Z")

</div>

Shin guey Wong wrote:

> > MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.
> 
> Can I use sequel for MSSQL database?

Yes, preferably using the ADO adapter on Windows, or the ODBC one on  
others.

---

<div class="post-metadata">

**Author:** ![nerdmaster](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/c68b51/32.png) [@nerdmaster](https://www.ruby-forum.com/u/nerdmaster)\
**Post date:** [June 18, 2008, 5:38am UTC](https://www.ruby-forum.com/t/ann-sequel-2-1-0-released/140705/4 "2008-06-18T05:38:32Z")

</div>

On Wed, 2008-06-18 at 12:08 +0900, Shin guey Wong wrote:

> > - Sequel currently has adapters for ADO, DB2, DBI, Informix, JDBC,  
> > MySQL, ODBC, OpenBase, Oracle, PostgreSQL and SQLite3.

> Can I use sequel for MSSQL database?

Are there ADO or ODBC drivers for MSSQL? (Hint: yes.)