New Adapter Support¶ ↑

Sequel now has much better support for Oracle, both in the ruby-oci8-based oracle adapter and in the jdbc/oracle adapter.
Sequel now has much better support for connecting to HSQLDB using the jdbc adapter. This support does not work correctly with the jdbc-hsqldb gem, since the version it uses is too old. You’ll need to load the .jar file manually until the gem is updated.
Sequel now has much better support for connecting to Apache Derby databases using the jdbc adapter. This works with the jdbc-derby gem, but it’s recommend you grab an updated .jar file as the jdbc-derby gem doesn’t currently support truncate or booleans.
The db2 adapter has had most of the remaining issues fixed, and can now run Sequel’s test suite cleanly. It’s still recommended that users switch to the ibmdb adapter if they are connecting to DB2.
A mock adapter has been added which provides a mock Database object that allows you to easily set the returned rows, the number of rows modified by update/delete, and the autogenerating primary key integer for insert. It also allows you to set specific columns in the dataset when retrieving rows. The specs were full of partial implementations of mock adapters, this mock adapter is much more complete and offers full support for mocking transactions and database sharding. Example:
```
DB = Sequel.mock(:fetch=>{:id=>1}, :numrows=>2, :autoid=>3)
DB[:items].all            # => [{:id => 1}]
DB[:items].insert         # => 3
DB[:items].insert         # => 4
DB[:items].delete         # => 2
DB[:items].update(:id=>2) # => 2
DB.sqls # => ['SELECT ...', 'INSERT ...', ...]
```
In addition to being useful in the specs, the mock adapter is also used if you use bin/sequel without a database argument, which makes it much easier to play around with Sequel on the command line without being tied to a real database.

New Transaction Features¶ ↑

Database after_commit and after_rollback hooks have been added, allowing you to set procs that are called after the currently- in-effect transaction commits or rolls back. If the Database is not currently in a transaction, the after_commit proc is called immediately and the after_rollback proc is ignored.
Model after_commit, after_rollback, after_destroy_commit, and after_destroy_rollback hooks have been added that use the new Database after_commit/after_rollback hook to execute code after commit or rollback.
Database#transaction now supports a :rollback => :reraise option to reraise any Sequel::Rollback exceptions raised by the block.
Database#transaction now supports a :rollback => :always option to always rollback the transaction, which is mostly useful when using transaction-based testing.
Sequel.transaction has been added, allowing you to run simultaneous transactions on multiple Database objects:
```
Sequel.transaction([DB1, DB2]){...}
# similar to:
DB1.transaction{DB2.transaction{...}}
```
You can combine this with the :rollback => :always option to easily use multiple databases in the same test suite and make sure that changes are rolled back on all of them.
Database#in_transaction? has been added so that users can detect whether the code is currently inside a transaction.
The generic JDBC transaction support, used by 6 of Sequel’s jdbc subapters, now supports savepoints if the underlying JDBC driver supports savepoints.

Other New Features¶ ↑

A dataset_associations plugin has been added, allowing datasets to call association methods, which return datasets of rows in the associated table that are associated to rows in the current dataset.

# Dataset of tracks from albums with name < 'M'
# by artists with name > 'M'

Artist.filter(:name > 'M').albums.filter(:name < 'M').tracks

# SELECT * FROM tracks
# WHERE (tracks.album_id IN (
#   SELECT albums.id FROM albums
#   WHERE ((albums.artist_id IN (
#     SELECT artists.id FROM artists
#     WHERE (name > 'M')))
#    AND (name < 'M'))))

Database#extend_datasets has been added, allowing you to do the equivalent of extending all of the database’s future datasets with a module. For performance, it creates an anonymous subclass of the current dataset class and includes a module in it, and uses the subclass to create future datasets.

Using this feature allows you to override any dataset method and call super, similar to how Sequel::Model plugins work. The method takes either a module:
```
Sequel.extension :columns_introspection
DB.extend_datasets(Sequel::ColumnsIntrospection)
```
or a block that it uses to create an anonymous module:
```
DB.extend_datasets do
  # Always select from table.* instead of *
  def from(*tables)
    ds = super
    if !@opts[:select] || @opts[:select].empty?
      ds = ds.select_all(*tables)
    end
    ds
  end
end
```

Database#<< and Dataset#<< now return self, which allow them to be used in chaining:

DB << "UPDATE foo SET bar_id = NULL" << "DROP TABLE bars"
DB[:foo] << {:bar_id=>0} << DB[:bars].select(:id)

A Database#timezone accessor has been added, allowing you to override Sequel.database_timezone on a per-Database basis, which allows you to use two separate Database objects in the same process that have different timezones.
You can now modify the type conversion procs on a per-Database basis when using the mysql, sqlite, and ibmdb adapters, by modifying the hash returned by Database#conversion_procs.
Model.dataset_module now accepts a Module instance as an argument, and extends the model’s dataset with that module.
When using the postgres adapter with the pg driver, you can now use Database#listen to wait for notifications. All adapters that connect to postgres now support Database#notify to send notifications:
```
# process 1
DB.listen('foo') do |ev, pid, payload|
  ev # => 'foo'
  notify_pid # => some Integer
  payload # => 'bar'
end

# process 2
DB.notify('foo', :payload=>'bar')
```
many_to_one associations now have a :qualify option that can be set to false to not qualify the primary key when loading the association. This shouldn’t be necessary to use in most cases, but in some cases qualifying a primary key breaks certain queries (e.g. using JOIN USING on the same column on Oracle).
Database#schema can now take a dataset as an argument if it just selects from a single table. If a dataset is provided, the schema parsing will use that dataset’s identifier_input_method and identifier_output_method for the parsing, instead of the database’s default. This makes it possible for Model classes to correctly get the table schema if they use a dataset whose identifier_(input|output)_method differs from the database default.
On databases that support common table expressions (CTEs) but do not support CTE usage in subselects, Sequel now emulates support by moving CTEs from the subselect to the main select when using the Dataset from, from_self, with, with_recursive, union, intersect, and except methods.
The bitwise compliment operator is now emulated on H2.
You can now set the convert_tinyint_to_bool setting on a per-Database basis in the mysql and mysql2 adapters.
You can now set the convert_invalid_date_time setting on a per-Database basis in the mysql adapter.
Database instances now have a dataset_class accessor that allows you to set which class is used when creating datasets. This is mostly used to implement the extend_datasets support, but it could be useful for other purposes.
Dataset#unused_table_alias now accepts an optional 2nd argument, which should be an array of additional symbols that should be considered as already used.
Dataset#requires_placeholder_type_specifiers? has been added to check if the dataset requires you use type specifiers for bound variable placeholders.

The prepared_statements plugin now checks this setting and works correctly on adapters that set it to true, such as oracle.
Dataset#recursive_cte_requires_column_aliases? has been added to check if you must provide a column list for a recursive CTE.

The rcte_tree plugin now checks this setting an works correctly on databases that set it to true, such as Oracle and HSQLDB.

Performance Improvements¶ ↑

Numerous optimizations were made to loading model objects from the database, resulting in a 7-16% speedup.

Model.call was added, and now .load is just an alias for .call. This allows you to make the model dataset’s row_proc the model itself, instead of needing a separate block, which improves performance.

While Model.load used to call .new (and therefore initialize), Model.call uses .allocate/#set_values/#after_initialize for speed. This saves a method call or two, and skips setting the @new instance variable.
Dataset#map, to_hash, select_map, select_order_map, and select_hash are now faster if any of the provided arguments are an array of symbols.
The Model.[] optimization is now applied in more cases.

Other Improvements¶ ↑

Sequel now creates accessor methods for all columns in a model’s table, even if the dataset doesn’t select the columns. This has been the specified behavior for a while, but the spec was broken. This allows you do to:
```
Model.dataset = DB[:table].select(:column1, :column2)
Model.select_more(:column3).first.column3
```
Model.def_dataset_method now correctly handles method names that can’t be used directly (such as method names with spaces). This isn’t so the method can be used with arbitrary user input, but it will allow safe creation of dataset methods that are derived from column names, which could contain spaces.
Model.def_dataset_method no longer overrides private model methods.
The optimization that Model.[] uses now works correctly if the model’s dataset uses a different identifier_input_method than the database.
Sharding is supported correctly by default for the transactions used by model objects. Previously, you had to use the sharding plugin to make sure the same shard was used for transactions as for the insert/update/delete statements.
Sequel now fully supports using an aliased table for the :join_table option of a many_to_many association. The only real use case for an aliased :join_table option is when the join table is the same as the associated model table.
A bug when eagerly loading a many_through_many association with composite keys where one of the join tables requires an alias has been fixed.
Sequel’s transaction internals have had substantial improvments. You can now open up simultaneous transactions on two separate shards of the same Database object in the same thread. The new design allows for future support of connection pools that aren’t based on threads. Sequel no longer abuses thread-local variables to store savepoint state.
Dataset#select_map and select_order_map now return an array of single element arrays if given an array with a single entry as an argument. Previously, they returned an array of values, which wasn’t consistent.
Sequel’s emulation of bitwise operators with more than 2 arguments now works on all adapters that use the emulation. The emulation was broken in 3.28.0 when more than 2 arguments were used on H2, DB2, Microsoft SQL Server, PostgreSQL, and SQLite.
Dataset#columns now correctly handles the emulated offset support used on DB2, Oracle, and Microsoft SQL Server when using the jdbc, odbc, ado, and dbi adapters. Previously, Dataet#columns could contain the row number column, which wasn’t in the hashes yielded by Dataset#each.
Sequel can now parse primary key information on Microsoft SQL Server. Previously, the only adapter that supported this was the jdbc adapter, which uses the generic JDBC support. The shared mssql adapter now supports parsing the information directly from the database system tables. This means that if you are using Model objects with a Microsoft SQL Server database using the tinytds, odbc, or ado adapters, the model primary key information will be set automatically.
Sequel’s prepared statement support no longer defines singleton methods on the prepared statement objects.
StringMethods#like is now case sensitive on SQLite and Microsoft SQL Server, making it more similar to other databases.
Sequel now works around an SQLite column naming bug if you select columns qualified with the alias of a subselect without providing an alias for the column itself.
Sequel now handles more bound variable types when using bound variables outside of prepared statements on SQLite.
Sequel now works around a bug in certain versions of the JDBC/SQLite driver when emulating alter table support for operations such as drop_column.
Sequel now emulates the add_constraint and drop_constraint alter table operations on SQLite, though the emulation has issues.
Sequel now correctly handles composite primary keys when emulating alter_table operations on SQLite.
Sequel now applies the correct PRAGMA statements by default when connecting to SQLite via the amalgalite and swift adapters.
Sequel now supports using savepoints inside prepared transactions on MySQL.
Sequel now closes JDBC ResultSet objects as soon as it is done using them, leading to potentially lower memory usage in the JDBC adapter, and fixes issues if you try to drop a table before GC has collected a related ResultSet.
Sequel can now correctly insert all default values into a table on DB2. Before, this didn’t work correctly if the table had more than one column.
Another type of disconnection error is now recognized in the mysql2 adapter.
Sequel now uses better error messages if you attempt to execute a prepared statement without a name using the postgres, mysql, and mysql2 adapters.
Some small fixes have been made that allow Sequel to run better when $SAFE=1. However, Sequel is not officially supported using $SAFE > 0, so there could be many issues remaining.
Sequel’s core and model specs were cleaned up by using the mock adapter to eliminate a lot of redundant code.
Sequel’s integration tests were sped up considerably, halving the execution time on some adapters.

Backwards Compatibility¶ ↑

Because Model.load is now an alias for .call, plugins should no longer override load. Instead, they should override .call.
Loading model objects from the database no longer calls Model#initialize. Instead, it calls Model.allocate, Model#set_values, and Model#after_initialize. So if you were overriding initialize and expecting the changes to affect model objects loaded from the database, you need to change your code.

Additionally, @new is no longer set to false for objects retieved from the database, since setting it to false hurts performance. Model#new? still returns true or false, so this only affects you if you are checking the instance variables directly.
Dataset#<< no longer returns the autogenerated primary key for the inserted row. As mentioned above, it now returns self to allow for chaining. If you were previously relying on the return value, switch from << to insert.
Dataset#map no longer calls the row_proc if given an argument, and Dataset#to_hash no longer calls the row_proc if given two arguments. This should only affect your code if you were using a row_proc that modified the content of the hash (e.g. Model#after_initialize). If you were relying on the old behavior, switch:
```
dataset.map(:foo)
# to
dataset.map{|r| r[:foo]}

dataset.to_hash(:foo, :bar)
# to
h = {}
dataset.each{|r| h[r[:foo]] = r[:bar]}
h
```
Model classes now need to have a dataset before you can define associations on them.
Model classes now pass their dataset to Database#schema, instead of their table name.
The :eager_block association option (which defaults to the association’s block argument) is now called before the :eager_graph association option has been applied, instead of after.
The many_to_many association reflection :qualified_right_key entry is now a method named qualified_right_key. Switch any code using association_reflection to use association_reflection.qualified_right_key.
If you are using like on SQLite and Microsoft SQL Server and want it to be case insensitive, switch to using ilike:
```
# Case sensitive
DB[:foos].where(:name.like('bar%'))
# Case insensitive
DB[:foos].where(:name.ilike('bar%'))
```
Sequel now sets the case_sensitive_like PRAGMA to true by default on SQLite. To set it to false instead, pass the :case_sensitive_like=>false option to the database when creating it.
Sequel’s alter table emulation on SQLite now renames the current table then populates the replacement table, instead of populating the replacement table at a temporary name, dropping the current table, and then renaming the replacement table.
The strings ‘n’ and ‘no’ (case insensitive) when typecasted to boolean are now considered false values instead of true.
The transaction internals had extensive changes, if you have any code that depended on the transaction internals, it will probably require changes.
Using the Sequel::MySQL module settings for convert_tinyint_to_bool and convert_invalid_date_time now only affects future Database objects. You should switch to using the per-Database methods if you are currently using the Sequel::MySQL module methods.
The customized transaction support in the do (DataObjects) adapter was removed. All three subadapters (postgres, mysql, sqlite) of the do adapter implement their own transaction support, so this should have no effect unless you were using the do adapter with a different database type.
The oracle support changed dramatically, so if you were relying on the internals of the oracle support, you should take extra care when upgrading.

Advance Notice¶ ↑

The next release of Sequel will contain significant changes to how a dataset is literalized into an SQL string. If you have a custom plugin, extension, or adapter that overrides a method containing “literal”, “sql”, or “quote”, or you make other modifications or extensions to how Sequel currently literalizes datasets to SQL, your code will likely need to be modified to support the next release.