ANN: Sequel 3.13.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, ODBC, OpenBase, Oracle,
    PostgreSQL and SQLite3.

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

= New Plugins

  • A json_serializer plugin was added that allows you to serialize
    model instances or datasets to JSON using to_json. It requires
    the json library. The API was modeled on ActiveRecord’s JSON
    serialization support. You can use :only and :except options
    to specify the columns included, :include to specify associations
    to include, as well pass options to nested associations using a
    hash. In addition to serializing to JSON, it also adds support
    for parsing JSON to model objects via JSON.parse or #from_json.

  • An xml_serializer plugin was added that allows you to serialize
    model instances or datasets to XML. It requries the nokogiri
    library. It has a similar API to the json_serializer plugin, using
    to_xml instead of to_json, and the from_xml class method instead
    of JSON.parse.

  • A tree plugin was added that allows you to treat Sequel::Model
    objects as being part of a tree. It provides similar features to
    rcte_tree, but works on databases that don’t support recursive
    common table expressions. In addition to the standard parent
    and children associations, it provides instance methods to get
    the ancestors, descendants, and siblings of the given tree node,
    and class methods to get the roots of the tree.

  • A list plugin was added that allows you to treat Sequel::Model
    objects as being part of a list. This adds instance methods to
    get the next and prev items in the list, or to move the item
    to a specific place in the list. You can specify that all rows
    in the table belong to the same list, or specify arbitrary scopes
    so that the same table can contain many separate lists.

= Other New Features

  • Sequel is now compatible with Ruby 1.9.2pre3.

  • Sequel now supports prepared transactions/two-phase commit on
    PostgreSQL, MySQL, and H2. You can specify that you want to
    use prepared transactions using the :prepare option which
    should be some transaction id string:

    DB.transaction(:prepare=>‘some string’) do … end

    Assuming that no exceptions are raised in the transaction block,
    Sequel will prepare the transaction. You can then commit the
    transaction later:

    DB.commit_prepared_transaction(‘some string’)

    If you need to rollback the prepared transaction, you can do
    so as well:

    DB.rollback_prepared_transaction(‘some string’)

  • Sequel now supports customizable transaction isolation levels on
    PostgreSQL, MySQL, and Microsoft SQL Server. You can specify the
    transaction isolation level to use for any transaction using the
    :isolation option with an :uncommitted, :committed, :repeatable,
    or :serializable value:

    DB.transaction(:isolation=>:serializable) do … end

    You can also set the default isolation level for transactions via
    the transaction_isolation_level Database attribute:

    DB.transaction_isolation_level = :committed

    If you are connecting to Microsoft SQL Server, it is recommended
    that you set a default transaction isolation level if you plan
    on using this feature.

  • You can specify a NULLS FIRST/LAST ordering by using the
    :nulls=>:first/:last option to asc and desc:

    Album.filter(:release_date.desc(:nulls=>:first),
    :name.asc(:nulls=>:last))

    ORDER BY release_date DESC NULLS FIRST,

    name ASC NULLS LAST

    This syntax is supported by PostgreSQL 8.3+, Firebird 1.5+,
    Oracle, and probably some other databases as well, and makes it
    possible for the user to specify whether NULL values should sort
    before or after other values.

  • Sequel::Model.find_or_create now accepts a block that is a yielded
    a new model object to be created if an existing model object is
    not found.

    Node.find_or_create(:name=>‘A’){|i| i.parent_id = 4}

  • The :frame option for windows and window functions can now be a
    string that is used literally in the SQL. This is necessary if you
    want to specify a custom frame, such as one that uses a specific
    number of rows preceding or following.

  • Savepoints are now supported on H2.

  • A :methods_module association option was added, allowing you to
    specify the module into which association instance methods are
    placed. By default, it uses the module containing the column
    accessor methods.

= Other Improvements

  • The :encoding option for the native MySQL adapter should now work
    correctly in all cases. This fix was included in 3.12.1.

  • Sequel now handles arrays of two element arrays automatically when
    using them as the value of a filter hash:

    DB[a].filter([:a, :b]=>[[1, 2], [3, 4]])

    Previously, you had to call .sql_array on the array in order to
    tell Sequel that it was a value list and not a conditions
    specifier.

  • Sequel no longer attempts to use class polymorphism in the
    class_table_inheritance plugin if you don’t specify a cti_key.

  • When using the native SQLite adapter, prepared statements are now
    cached per connection for increased performance. Previously,
    Sequel prepared a new statement for every query.

  • tinyint(1) columns are now handled as booleans when connecting to
    MySQL via JDBC.

  • On PostgreSQL, if no :schema option is provided for
    Database#tables, #table_exists?, or #schema, and no default_schema
    is used, assume all schemas except the default non-public ones.
    Previously, it assumed the public schema for tables and
    table_exists?, but did not assume any schema for #schema.

    This fixes issues if you use table names that overlap with table
    names in the information_schema, such as domains. It’s still
    recommended that you specify a default_schema if you are using a
    schema other than public.

  • Unsigned integers are now handled correctly in the schema dumper.

  • Sequel::SQL::PlaceholderLiteralString is now a GenericExpression
    subclass, allowing you to treat it like most other Sequel
    expression objects:

    ‘(a || ?)’.lit(:b).like(‘Test%’)

    ((a || b) LIKE ‘Test%’)

  • Sequel now supports the bitwise shift operators (<< and >>) on
    Microsoft SQL Server by emulating them.

  • Sequel now supports most bitwise operators (&, |, ^, <<, >>) on H2
    by emulating them. The bitwise complement operator is not yet
    supported.

  • Sequel now logs the SQL queries that are sent when connecting to
    MySQL.

  • If a plugin cannot be loaded, Sequel now gives a more detailed
    error message.

= Backwards Compatibility

  • Array#sql_array and the Sequel::SQL::SQLArray class are now
    considered deprecated. Use the Array#sql_value_list and the
    Sequel::SQL::ValueList class instead. SQLArray is now just
    an alias for ValueList, but it now is an Array subclass instead
    of a Sequel::SQL::Expression subclass.

  • Using the ruby bitwise xor operator (^) on PostgreSQL now uses
    PostgreSQL’s bitwise xor operator (#) instead of PostgreSQL’s
    exponentiation operator (^). If you want exponentiation, use
    the power function.

  • Using the ruby bitwise complement operator (~) on MySQL now returns
    a signed integer instead of an unsigned integer, for better
    compatibility with other databases.

  • Using nil as a case expression value (the 2nd argument to Hash#case
    and Array#case) will now use NULL as the case expression value,
    instead of omitting the case expression value:

    3.12.0

    {1=>2}.case(0, nil)

    CASE WHEN 1 THEN 2 ELSE 0 END

    3.13.0

    {1=>2}.case(0, nil)

    CASE NULL WHEN 1 THEN 2 ELSE 0 END

    In general, you would never use nil explicitly, but the new
    behavior makes more sense if you have a variable that might be nil:

    parent_id = Node[1].parent_id
    {1=>2}.case(0, parent_id)

    If parent_id IS NULL/nil, then previously Sequel would have
    generated unexpected SQL. If you don’t want a case expression
    value to be used, do not pass a second argument to #case.

  • Some internal transaction methods now take an optional options
    hash, so if you have a custom adapter, you will need to make
    changes.

  • Some internal association methods now take an optional options
    hash.

  • Some Rakefile task names were modified in the name of consistency:

    spec_coverage → spec_cov
    integration → spec_integration
    integration_cov → spec_integration_cov

Thanks,
Jeremy