Prepared Statement Plugins¶ ↑

The prepared_statements plugin makes Sequel::Model classes use prepared statements for creating, updating, and destroying model instances, as well as looking up model objects by primary key. With this plugin, all of the following will use prepared statements:
```
Artist.plugin :prepared_statements
Artist.create(:name=>'Foo')
a = Artist[1]
a.update(:name=>'Bar')
a.destroy
```
The prepared_statements_safe plugin reduces the number of prepared statements that can be created by doing two things. First, it makes the INSERT statements used when creating instances to use as many columns as possible, setting specific values for all columns with parseable default values. Second, it changes save_changes to just use save, saving all columns instead of just the changed ones.

The reason for this plugin is that Sequel’s default behavior of using only the values specifically set when creating instances and having update only set changed columns by default can lead to a large number of prepared statements being created.

For prepared statements to be used, each set of columns in the insert and update statements needs to have its own prepared statement. If you have a table with 1 primary key column and 4 other columns, you can have up to 2^4 = 16 prepared statements created, one for each subset of the 4 columns. If you have 1 primary key column and 20 other columns, there are over a million subsets, and you could hit your database limit for prepared statements (a denial of service attack).

Using the prepared_statements_safe plugin mitigates this issue by reducing the number of columns that may or may not be present in the query, in many cases making sure that each model will only have a single INSERT and a single UPDATE prepared statement.
The prepared_statements_associations plugin allows normal association method calls to use prepared statements if possible. For example:
```
Artist.plugin :prepared_statements_associations
Artist.many_to_one :albums
Artist[1].albums
```
Will use a prepared statement to return the albums for that artist. This plugin works for all supported association types. There are some associations (filtered and custom associations) that Sequel cannot currently use a prepared statement reliably, for those Sequel will use a regular query.
The prepared_statements_with_pk plugin allows the new Dataset#with_pk method (explained below) to use prepared statements. For example:
```
Artist.plugin :prepared_statements_with_pk
Artist.filter(...).with_pk(1)
```
Will use a prepared statement for this query. The most benefit from prepared statements come from queries that are expensive to parse and plan but quick to execute, so using this plugin with a complex filter can in certain cases yield significant performance improvements.

However, this plugin should be considered unsafe as it is possible that it will create an unbounded number of prepared statements. It extracts parameters from the dataset using Dataset#unbind (explained below), so if your code has conditions that vary per query but that Dataset#unbind does not handle, an unbounded number of prepared statements can be created. For example:
```
Artist.filter(:a=>params[:b].to_i).with_pk[1]
Artist.exclude{a > params[:b].to_i}.with_pk[1]
```
are safe, but:
```
Artist.filter(:a=>[1, params[:b].to_i]).with_pk[1]
Artist.exclude{a > params[:b].to_i + 2}.with_pk[1]
```
are not. For queries that are not safe, Dataset#with_pk should not be used with this plugin, you should switch to looking up by primary key manually (for a regular query):
```
Artist.filter(:a=>[1, params[:b].to_i])[:id=>1]
```
or using the prepared statement API to create a custom prepared statement:
```
# PS = {}
PS[:name] ||= Artist.filter(:a=>[1, :$b], :id=>:$id).
       prepare(:select, :name)
PS[:name].call(:b=>params[:b].to_i, :id=>1)
```

Other New Features¶ ↑

Filtering by associations got a lot more powerful. Sequel 3.23.0 introduced filtering by associations:
```
Album.filter(:artist=>artist)
```
This capability is much expanded in 3.24.0, allowing you to exclude by associations:
```
Album.exclude(:artist=>artist)
```
This will match all albums not by that artist.

You can also filter or exclude by multiple associated objects:
```
Album.filter(:artist=>[artist1, artist2])
Album.exclude(:artist=>[artist1, artist2])
```
The filtered dataset will match all albums by either of those two artists, and the excluded dataset will match all albums not by either of those two artists.

You can also filter or exclude by using a model dataset:
```
Album.filter(:artist=>Artist.filter(:name.like('A%'))).all
Album.exclude(:artist=>Artist.filter(:name.like('A%'))).all
```
Here the filtered dataset will match all albums where the associated artist has a name that begins with A, and the excluded dataset will match all albums where the associated artist does not have a name that begins with A.

All of these types of filtering and excluding work with all of association types that ship with Sequel, even the many_through_many plugin.
Sequel now supports around hooks, which wrap the related before hook, behavior, and after hook. Like other Sequel hooks, these are implemented as instance methods. For example, if you wanted to log DatabaseErrors raised during save:
```
class Artist < Sequel::Model
  def around_save
    super
  rescue Sequel::DatabaseError => e
    # log the error
    raise
  end
end
```
All around hooks should call super, not yield. If an around hook doesn’t call super or yield, it is treated as a hook failure, similar to before hooks returning false.

For around_validation, the return value of super should be whether the object is valid. For other around hooks, the return value of super is currently true, but it’s possible that will change in the future.
Dataset#with_pk has been added to model datasets that allows you to find the object with the matching primary key:
```
Artist.filter(:name.like('A%')).with_pk(1)
```
This should make easier the common case where you want to find a particular object that is associated to another object:
```
Artist[1].albums_dataset.with_pk(2)
```
Before, there was no way to do that without manually specifying the primary key:
```
Artist[1].albums_dataset[:id=>2]
```
To use a composite primary key with with_pk, you have to provide an array:
```
Artist[1].albums_dataset.with_pk([1, 2])
```
Dataset#[] for model datasets will now call with_pk if given a single Integer argument. This makes the above case even easier:
```
Artist[1].albums_dataset[2]
```
Note that for backwards compatibility, this only works for single integer primary keys. If you have a composite primary key or a string/varchar primary key, you have to use with_pk.
Dataset#unbind has been added, which allows you to take a dataset that uses static bound values and convert them to placeholders. Currently, the only cases handled are SQL::ComplexExpression objects that use a =, !=, <, >, <=, or >= operator where the first argument is a Symbol, SQL::Indentifier, or SQL::QualifiedIdentifier, and the second argument is a Numeric, String, Date, or Time. Dataset#unbind returns a two element array, where the first element is a modified copy of the receiver, and the second element is a bound variable hash:
```
ds, bv = DB[:table].filter(:a=>1).unbind
ds # DB[:table].filter(:a=>:$a)
bv # {:a=>1}
```
The purpose of doing this is that you can then use prepare or call on the returned dataset with the returned bound variables:
```
ds.call(:select, bv)
# SELECT * FROM table WHERE (a = ?); [1]

ps = ds.prepare(:select, :ps_name)
# PREPARE ps_name AS SELECT * FROM table WHERE (a = ?)
ps.call(bv)
# EXECUTE ps_name(1)
```
Basically, Dataset#unbind takes a specific statement and attempts to turn it into a generic statement, along with the placeholder values it extracted.

Unfortunately, Dataset#unbind cannot handle all cases. For example:
```
DB[:table].filter{a + 1 > 10}.unbind
```
will not unbind any values. Also, if you have a query with multiple different values for a variable, it will raise an UnbindDuplicate exception:
```
DB[:table].filter(:a=>1).or(:a=>2).unbind
```
A defaults_setter plugin has been added that makes it easy to automatically set default values when creating new objects. This plugin makes Sequel::Model behave more like ActiveRecord in that new model instances (before saving) will have default values parsed from the database. Unlike ActiveRecord, only values with non-NULL defaults are set. Also, Sequel allows you to easily modify the default values used:
```
Album.plugin :default_values
Album.new.values # {:copies_sold => 0}
Album.default_values[:copies_sold] = 42
Album.new.values # {:copies_sold => 42}
```
Before, this was commonly done in an after_initialize hook, but that’s slower as it is also called for model instances loaded from the database.
A Database#views method has been added that returns an array of symbols representing view names in the database. This works just like Database#tables except it returns views.
A Sequel::ASTTransformer class was added that makes it easy to write custom transformers of Sequel’s internal abstract syntax trees. Dataset#qualify now uses a subclass of ASTTransformer to do its transformations, as does the new Dataset#unbind.

Other Improvements¶ ↑

Database#create_table? now uses a single query with IF NOT EXISTS if the database supports such syntax. Previously, it issued a SELECT query to determine table existence. Sequel currently supports this syntax on MySQL, H2, and SQLite 3.3.0+.

The Database#supports_create_table_if_not_exists? method was added to allow users to determine whether this syntax is supported.
Multiple column IN/NOT IN emulation now works correctly with model datasets (or other datasets that use a row_proc).

You can now correctly invert SQL::Constant instances:

Sequel::NULL # NULL
~Sequel::NULL # NOT NULL
Sequel::TRUE # TRUE
~Sequel::TRUE # FALSE

A bug in the association_pks plugin has been fixed in the case where the associated table had a different primary key column name than the current table.
The emulated prepared statement support now supports nil and false as bound values.
The to_dot extension was refactored for greater readability. The only change was a small fix in the display for SQL::Subscript instances.
The Dataset#supports_insert_select? method is now available to let you know if the dataset supports insert_select. You should use this method instead of respond_to? for checking for insert_select support.
Prepared statements/bound variable can now use a new :insert_select type for preparing a statement that will insert a row and return the row inserted, if the dataset supports insert_select.
The Model#initialize_set private method now exists for easier plugin writing. It is only called for new model objects, with the hash given to initialize. By default, it just calls set.
A small bug when creating anonymous subclasses of Sequel::Model on ruby 1.9 has been fixed.
If Thread#kill is used inside a transaction on ruby 1.8 or rubinius, the transaction is rolled back. This situation is not handled correctly on JRuby or ruby 1.9, and I’m not sure it’s possible to handle correctly on those implementations.
The postgres adapter now supports the Sequel::Postgres::PG_NAMED_TYPES hash for associating conversion procs for custom types that don’t necessarily have the same type oid on different databases. This hash uses symbol keys and proc values:
```
Sequel::Postgres::PG_NAMED_TYPES[:interval] = proc{|v| ...}
```
The conversion procs now use a separate hash per Database object instead of a hash shared across all Database objects. You can now modify the types for a particular Database object, but you have to use the type oid:
```
DB.conversion_procs[42] = proc{|v| ...}
```
On SQLite and MSSQL, literalization of true and false values given directly to Dataset#filter has been fixed. So the following now works correctly on those databases:
```
DB[:table].filter(true)
DB[:table].filter(false)
```
Unfortunately, because SQLite and MSSQL don’t have a real boolean type, these will not work:
```
DB[:table].filter{a & true}
DB[:table].filter{a & false}
```
You currently have to work around the issue by doing:
```
DB[:table].filter{a & Sequel::TRUE}
DB[:table].filter{a & Sequel::FALSE}
```
It is possible that a future version of Sequel will remove the need for this workaround, but that requires having a separate literalization method specific to filters.
The MySQL bit type is no longer treated as a boolean. On MySQL, the bit type is a bitfield, which is very different than the MSSQL bit type, which is the closest thing to a boolean on MSSQL.
The bool database type is now recognized as a boolean. Some SQLite databases use bool, such as the ones used in Firefox.
SQL_AUTO_IS_NULL=0 is now set by default when connecting to MySQL using the swift or jdbc adapters. Previously, it was only set by default when using the mysql or mysql2 adapters.
Dataset#limit now works correctly on Access, using the TOP syntax.
Dataset#limit now works correctly on DB2, using the FETCH FIRST syntax.
The jdbc mssql subadapter was split into separate subadapters for sqlserver (using Microsoft’s driver) and jtds (using the open source JTDS driver).
The jdbc jtds subadapter now supports converting Java CLOB objects to ruby strings.
Tables from the INFORMATION_SCHEMA are now ignored when parsing schema on JDBC.
The informix adapter has been split into shared/specific parts, and a jdbc informix subadapter has been added.
Dataset#insert_select now works correctly on MSSQL when the core extensions are disabled.
The sqlite adapter now logs when preparing a statement.
You no longer need to be a PostgreSQL superuser to run the postgres adapter specs.
The connection pool specs are now about 10 times faster and not subject to race conditions due to using Queues instead of sleeping.

Backwards Compatibility¶ ↑

Model#save no longer calls Model#valid?. It now calls the Model#_valid? private method that Model#valid? also calls. To mark a model instance invalid, you should override the Model#validate method and add validation errors to the object.
The BeforeHookFailure exception class has been renamed to HookFailure since hook failures can now be raised by around hooks that don’t call super. BeforeHookFailure is now an alias to HookFailure, so no code should break, but you should update your code to reflect the new name.
Any custom argument mappers used for prepared statements now need to implement the prepared_arg? private instance method and have it return true.
If your databases uses bit as a boolean type and isn’t MSSQL, it’s possible that those columns will no longer be treated as booleans. Please report such an issue on the bugtracker.
It is possible that the filtering and excluding by association datasets will break backwards compatibility in some apps. This can only occur if you are using a symbol with the same name as an association with a model dataset whose model is the same as the associated class. As associations almost never have the same names as columns, this would require either aliasing or joining to another table. If for some reason this does break your app, you can work around it by changing the symbol to an SQL::Identifier or a literal string.
The Sequel::Postgres.use_iso_date_format= method now only affects future Database objects.
On MySQL, Database#tables no longer returns view names, it only returns table names. You have to use Database#views to get view names now.

3.24.0.txt

Prepared Statement Plugins¶ ↑

Other New Features¶ ↑

Other Improvements¶ ↑

Backwards Compatibility¶ ↑