ANN: Sequel 3.5.0 Released

nerdmaster · October 1, 2009, 6:10pm

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 supports advanced database features such as prepared
statements, bound variables, stored procedures, master/slave
configurations, and database sharding.
Sequel makes it easy to deal with multiple records without having
to break your teeth on SQL.
Sequel currently has adapters for ADO, Amalgalite, DataObjects,
DB2, DBI, Firebird, Informix, JDBC, MySQL, ODBC, OpenBase, Oracle,
PostgreSQL and SQLite3.

Sequel 3.5.0 has been released and should be available on the gem
mirrors. The 3.5.0 release adds numerous improvements:

New Plugins

A class_table_inheritance plugin has been added, supporting model
inheritance in the database using a table-per-model-class approach.
Each table stores only attributes unique to that model or subclass
hierarchy.

For example, with this hierarchy:
```
          Employee
         /        \
      Staff     Manager
                   |
                Executive
```
the following database schema may be used (table - columns):
- employees - id, name, kind
- staff - id, manager_id
- managers - id, num_staff
- executives - id, num_managers
The class_table_inheritance plugin assumes that the main table
(e.g. employees) has a primary key field (usually
autoincrementing), and all other tables have a foreign key of the
same name that points to the same key in their superclass’s table.
For example:
- employees.id - primary key, autoincrementing
- staff.id - foreign key referencing employees(id)
- managers.id - foreign key referencing employees(id)
- executives.id - foreign key referencing managers(id)
When using the class_table_inheritance plugin, subclasses use joined
datasets:

Employee.dataset.sql # SELECT * FROM employees
Manager.dataset.sql # SELECT * FROM employees
# INNER JOIN managers USING (id)
Executive.dataset.sql # SELECT * FROM employees
# INNER JOIN managers USING (id)
# INNER JOIN executives USING (id)

This allows Executive.all to return instances with all attributes
loaded. The plugin overrides deleting, inserting, and updating
in the model to work with multiple tables, by handling each table
individually.

This plugin allows and encourages the use of a :key option to mark
a column holding the class name. This allows methods on the
superclass to return instances of specific subclasses.

a = Employee.all # [<#Staff>, <#Manager>, <#Executive>]

This plugin requires the lazy_attributes plugin and uses it to
handle subclass specific attributes that would not be loaded
when calling superclass methods (since those wouldn’t join
to the subclass tables). For example:

a.first.values # {:id=>1, name=>‘S’, :kind=>‘Staff’}
a.first.manager_id # Loads the manager_id attribute from the
# database

The class_table_inheritance plugin requires JOIN USING and
therefore is not supported on H2 or Microsoft SQL Server, which do
not support that SQL-92 feature.
An associations_dependencies plugin was added for deleting,
destroying, or nullifying associated objects when destroying a
model object. This just gives an easy way to add the necessary
before and after destroy hooks. The following association types
support the following dependency actions:
- :many_to_many - :nullify (removes all related entries in join
  table)
- :many_to_one - :delete, :destroy
- :one_to_many - :delete, :destroy, :nullify (sets foreign key to
  NULL for all associated objects)
This plugin works directly with the association datasets and does
not use any cached association values. The :delete action will
delete all associated objects from the database in a single SQL
call. The :destroy action will load each associated object from the
database and call the destroy method on it.

The plugin call takes a hash of association symbol keys and
dependency action symbol values. Alternatively, you can specify
additional dependencies later using add_association_dependencies:

Business.plugin :association_dependencies, :address=>:delete

or:

Artist.plugin :association_dependencies
Artist.add_association_dependencies :albums=>:destroy,
:reviews=>:delete, :tags=>:nullify
A force_encoding plugin was added that forces the encoding of
strings used in model instances. When model instances are loaded
from the database, all values in the hash that are strings are
forced to the given encoding. Whenever you update a model column
attribute, the resulting value is forced to a given encoding if the
value is a string. There are two ways to specify the encoding.
You can either do so in the plugin call itself, or via the
forced_encoding class accessor:

class Album < Sequel::Model
plugin :force_encoding, ‘UTF-8’
# or
plugin :force_encoding
self.forced_encoding = ‘UTF-8’
end

This plugin only works on ruby 1.9, since strings don’t have
encodings in 1.8.
A typecast_on_load plugin was added, for fixing bad database
typecasting when loading model objects. Most of Sequel’s database
adapters don’t have complete control over typecasting, and may
return columns that aren’t typecast correctly (with correct being
defined as how the model object would typecast the same column
values).

This plugin modifies Model.load to call the setter methods (which
typecast by default) for all columns given. You can either specify
the columns to typecast on load in the plugin call itself, or
afterwards using add_typecast_on_load_columns:

Album.plugin :typecast_on_load, :release_date, :record_date

or:

Album.plugin :typecast_on_load
Album.add_typecast_on_load_columns :release_date, :record_date

If the database returns release_date and record_date columns as
strings instead of dates, this will ensure that if you access those
columns through the model object, you’ll get Date objects instead of
strings.
A touch plugin was added, which adds Model#touch for updating an
instance’s timestamp, as well as touching associations when an
instance is updated or destroyed.

The Model#touch instance method saves the object with a modified
timestamp. By default, it uses the :updated_at column, but you can
set which column to use. It also supports touching of associations,
so that when the current model object is updated or destroyed, the
associated rows in the database can have their modified timestamp
updated to the current timestamp. Example:

class Album < Sequel::Model
plugin :touch, :column=>:modified_on, :associations=>:artist
end
A subclasses plugin was added, for recording all of a models
subclasses and descendent classes. Direct subclasses are available
via the subclasses method, and all descendent classes are available
via the descendents method:

c = Class.new(Sequel::Model)
c.plugin :subclasses
sc1 = Class.new(c)
sc2 = Class.new(c)
ssc1 = Class.new(sc1)
c.subclasses # [sc1, sc2]
sc1.subclasses # [ssc1]
sc2.subclasses # []
ssc1.subclasses # []
c.descendents # [sc1, ssc1, sc2]

The main use case for this is if you want to modify all models
after the model subclasses have been created. Since mutable
options are copied when subclassing, modifying parent classes
does not affect current subclasses, only future ones. The
subclasses plugin allows you get all subclasses so that you can
easily modify them. The plugin only records subclasses
created after the plugin call, though.
An active_model plugin was added, giving Sequel::Model an
ActiveModel complaint API, in so much as it passes the
ActiveModel::Lint tests.

New Extensions

A named_timezones extension was added, allowing you to use named
timezones such as “America/Los_Angeles” (the default Sequel
timezone support only supports UTC or local time). This extension
requires TZInfo. It also sets the Sequel.datetime_class to
DateTime, so database timestamps will be returned as DateTime
instances instead of Time instances. This is because ruby’s
Time class doesn’t support timezones other than UTC and local time.

This plugin allows you to pass either strings or TZInfo::Timezone
instance to Sequel.database_timezone=, application_timezone=, and
typecast_timezone=. If a string is passed, it is converted to a
TZInfo::Timezone using TZInfo::Timezone.get.

Let’s say you have the database server in New York and the
application server in Los A… For historical reasons, data
is stored in local New York time, but the application server only
services clients in Los A., so you want to use New York
time in the database and Los A. time in the application. This
is easily done via:

Sequel.database_timezone = ‘America/New_York’
Sequel.application_timezone = ‘America/Los_Angeles’

Then, before timestamps are stored in the database, they are
converted to New York time. When timestamps are retrieved from the
database, they are converted to Los A. time.
A thread_local_timezones extension was added. This allows you to
set a per-thread timezone that will override the default global
timezone while the thread is executing. The main use case is for
web applications that execute each request in its own thread, and
want to set the timezones based on the request. The most common
example is having the database always store time in UTC, but have
the application deal with the timezone of the current user. That
can be done with:

Sequel.database_timezone = :utc

In each thread:

Sequel.thread_application_timezone = current_user.timezone

This extension is designed to work with the named_timezones
extension.
An sql_expr extension was added that adds .sql_expr methods to
all objects, giving them easy access to Sequel’s DSL:

1.sql_expr < :a # 1 < a
false.sql_expr & :a # FALSE AND a
true.sql_expr | :a # TRUE OR a
~nil.sql_expr # NOT NULL
“a”.sql_expr + “b” # ‘a’ || ‘b’

Proc#sql_expr uses a virtual row:

proc{[[a, b], [a, c]]}.sql_expr | :x

(((a = b) AND (a = c)) OR x)
A looser_typecasting extension was added, for using to_f and to_i
instead of the more strict Kernel.Float and Kernel.Integer when
typecasting floats and integers. To use it, you should extend the
database with the Sequel::LooserTypecasting module after loading
the extension:

Sequel.extension :looser_typecasting
DB.extend(Sequel::LooserTypecasting)

This makes the behavior more like ActiveRecord:

a = Artist.new(:num_albums=>‘a’)
a.num_albums # => 0

Other New Features

Associations now support composite keys. All of the :*key options
options now accept arrays of symbols instead of plain symbols.
Example:

Artist.primary_key # [:name, :city]
Album.many_to_one :artist, :key=>[:artist_name, :artist_city]
Artist.one_to_many :albums, :key=>[:artist_name, :artist_city]

All association types are supported, including the built-in
many_to_many association and the many_through_many plugin. Both
methods of eager loading work with composite keys for all
association types. Setter and add/remove/remove_all methods
also now work with composite keys.
Associations now respect a :validate option, which can be set to
false to not validate when implicitly saving associated objects.

There isn’t a lot of implicit saving in Sequel’s association
methods, but this gives the user the control over validation when
the association methods implicitly save an object.
In addition to the regular association methods, the
nested_attributes plugin was also updated to respect the
:validate_association option. It was also modified to not validate
associated objects twice, once when the parent object was validated
and again when the associated object was saved. Additionally, if
you pass :validate=>false to the save method when saving the parent
object, it will not longer attempt to validate associated objects
when saving them.
Dataset#insert and #insert_sql were refactored and now support the
following API:
- No arguments - Treat as a single empty hash argument
- Single argument:
  - Hash - Use keys as columns and values as values
  - Array - Use as values, without specifying columns
  - Dataset - Use a subselect, without specifying columns
  - LiteralString - Use as the values
- 2 arguments:
  - Array, Array - Use first array as keys, second as values
  - Array, Dataset - Use a subselect, with the array as columns
  - Array, LiteralString - Use LiteralString as the values, with
    the array as the columns
- Anything else: Treat all given values an an array of values
Graphing now works with previously joined datasets. The main use
case of this is when eagerly loading (via eager_graph) model
associations for models backed by joined datasets, such as those
created by the class_table_inheritance plugin.
Sequel.virtual_row was added allowing you to easily use the
VirtualRow support outside of select, order, and filter calls:

net_benefit = Sequel.virtual_row{revenue > cost}
good_employee = Sequel.virtual_row{num_commendations > 0}
fire = ~net_benefit & ~good_employee
demote = ~net_benefit & good_employee
promote = net_benefit & good_employee
DB[:employees].filter(fire).update(:employed=>false)
DB[:employees].filter(demote).update(:rank=>:rank-1)
DB[:employees].filter(promote).update(:rank=>:rank+1)
When Sequel wraps exception in its own classes (to provide database
independence), it now keeps the wrapped exception available in
a wrapped_exception accessor. This allows you to more easily
determine the wrapped exception class, without resorting to parsing
the exception message.

begin
DB.run(‘…’)
rescue Sequel::DatabaseError => e
case e.wrapped_exception
when Mysql::Error
…
when PGError
…
end
end
The MySQL adapter now supports a Dataset#split_multiple_result_sets
method that yields arrays of rows (one per result set), instead of
rows. This allows you to submit multiple statements at the same
time (or call a stored procedure that returns multiple result
sets), and know which rows are related to which result sets.

This violates a lot of Sequel’s internal assumptions and should be
used with care. Existing row_procs are modified to work correctly,
but graphing will not work on these datasets.
The ADO adapter now accepts a :conn_string option and uses that
as the full ADO connection string. This can be used to connect to
any datasource ADO supports, such as Microsoft Excel.
The Microsoft SQL Server shared adapter now supports a
Database#server_version method.
The Microsoft SQL Server shared adapter now supports updating and
deleting from joined datasets.
The Microsoft SQL Server shared adapter now supports a
Dataset#output method that uses the OUTPUT clause.
Model#_save now calls either Model#_insert or Model#_update for
inserting/updating the row in the database. This allows for easier
overriding when you want to allow creating and updating model
objects backed by a joined dataset.
Dataset#graph now takes a :from_self_alias option specifying the
alias to use for the subselect created if the receiver is a joined
but not yet graphed dataset. It defaults to the first source table
in the receiver.

Other Improvements

Typecasting model attributes is now done before checking existing
values, instead of after. Before, the code for the model attribute
setters would compare the given value to the existing entry. If it
didn’t match, the value was typecasted and then assigned. That led
to the following situation:

a = Album[1]
a.num_tracks # => 10
params # => {‘num_tracks’=>‘10’}
a.set(params)
a.changed_columns # => [:num_tracks]

The new behavior typecasts the value first, and only sets it and
records the column as changed if it doesn’t match the typecasted
value.
Model#modified? is now always true if the record is new. modified?
indicates the instance’s status relative to the database, and since
a new object is not yet in the database, and saving the object
would add it, the object is considered modified. A consequence of
this is that Model#save_changes now always saves if the object is
new.

If you want to check if there were changes to columns since the
object was first initialized, you should use
!changed_columns.empty?, which was the historical way to handle
the situation.
The DataObjects (do) adpater now supports DataObjects 0.10.
Dataset#select_more and Dataset#order_more no longer affect the
receiver. They are supposed to just return a modified copy of the
receiver instead of modifying the receiver itself. For a few
versions they have been broken in that they modified the receiver
in addition to returning a modified copy.
Performance was increased for execution of prepared statements
with multiple bound variables on MySQL.
On MySQL, database errors raised when preparing statements or
setting bound variable values are now caught and raised as
Sequel::DatabaseErrors.
On MySQL, more types of disconnection errors are detected.
When altering columns in MySQL, options such as :unsigned,
:elements, and :size that are given in the call are now respected.
MySQL enum defaults are now handled correctly in the schema dumper.
The schema dumper no longer attempts to use unparseable defaults
as literals on MySQL, since MySQL does not provide defaults as
valid literals.
The emulated offset support in the shared Microsoft SQL Server
adapter now works better with model classes (or any datasets with
row_procs).
Microsoft SQL Server now supports using the WITH clause in delete,
update, and insert calls.
Parsed indexes when connecting to Microsoft SQL Server via JDBC no
longer include primary key indexes.
Dataset#insert_select now returns nil if disable_insert_returning
is used in the shared PostgreSQL adapter. This makes it work as
expected with model object creation.
Calling Model.set_primary_key with an array of symbols to set
a composite primary key is now supported. You can also provide
multiple symbol arguments to do the same thing. Before, specifying
an array of symbols broke the Model.[] optimization.
Literalization of timezones in timestamps now works correctly on
Oracle.
FILE and LINE are now used everywhere that eval is called
with a string, which makes for better backtraces.
The native MySQL adapter now correctly handles returning before
yielding all result sets. Previously, this caused a commands out
of sync error.
Table names in common table expressions are now quoted.
The Oracle adapter’s Dataset#except now accepts a hash, giving it
the same API as the default Dataset#except.
When connecting to Microsoft SQL Server via ADO, allow
Dataset#insert to take multiple arguments.
Fractional timestamps are no longer used on ODBC.
Schema parsing now works on MSSQL when the database is set to not
quote identifiers.
Timezone offsets are no longer used on Microsoft SQL Server, since
they only work for the datetimeoffset type.
Only 3 fractional digits in timestamps are used in Microsoft SQL
Server, since an error is raised if the use the datetime type
with more than that.
The integration test suite now has guards for expected failures
when run on known databases. Expected failures are marked as
pending.

Backwards Compatibility

Graphing to an previously joined (but not graphed) dataset now
causes the receiver to be wrapped in a subselect, so if you
graph a dataset to a previously joined dataset, and then filter
the dataset referring to tables that were in the joined dataset
(other than the first table), the SQL produced will probably no
longer be valid. You should either filter the dataset before
graphing or use the name of the first source of the joined
dataset (which is what the subselected is aliased to) if filtering
afterward.

In certain cases, this change can cause tables to be aliased
differently, so if you were graphing previously joined datasets
and then filtering using the automatically generated aliases, you
might need to modify your code.
The DataObjects (do) adpater no longer supports DataObjects 0.9.x.
The Dataset#virtual_row_block_call private instance method has
been removed.
Sequel’s timezone support was significantly refactored, so if you
had any custom modifications to the timezone support, they might
need to be refactored as well.
The SQL generation code was significantly refactored, so if you
had any custom modifications in that area, you might need to
refactor as well.

Thanks,
Jeremy

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

ANN: Sequel 3.5.0 Released

New Plugins

or:

or:

New Extensions

In each thread:

(((a = b) AND (a = c)) OR x)

Other New Features

Other Improvements

Backwards Compatibility