ANN: Sequel 3.29.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, IBM_DB, Informix, JDBC, MySQL, Mysql2, ODBC,
    OpenBase, Oracle, PostgreSQL, SQLite3, Swift, and TinyTDS.

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

= 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 << “UPADTE 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[:qualified_right_key] 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.

Thanks,
Jeremy