ANN: Sequel 3.24.0 Released

Ruby
1

Sequel is a lightweight database access toolkit for Ruby.

  • Sequel provides thread safety, connection pooling and a concise
    DSL for constructing SQL queries and table schemas.
  • Sequel includes a 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, savepoints,
    two-phase commit, transaction isolation, master/slave
    configurations, and database sharding.
  • Sequel currently has adapters for ADO, Amalgalite, DataObjects,
    DB2, DBI, Firebird, Informix, JDBC, MySQL, Mysql2, ODBC, OpenBase,
    Oracle, PostgreSQL, SQLite3, Swift, and TinyTDS.

Sequel 3.24.0 has been released and should be available on the gem
mirrors.

= 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.

Thanks,
Jeremy