ANN: Sequel 3.32.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.32.0 has been released and should be available on the gem
mirrors.

= New Features

  • Prepared statements now support :map and :to_hash prepared
    statement types. The main reason for this is that certain
    extensions (e.g. sequel_pg) optimize map/to_hash calls, and
    there previously was not a way to use prepared statements
    with the map/to_hash optimizations.

  • Sequel.empty_array_handle_nulls has been added to change how
    IN/NOT IN operations with an empty array are handled. See
    the Backwards Compatibility section for details.

  • 5 new association options have been added that allow you to
    define associations where the underlying columns clash with
    standard ruby method names:

    many_to_one :primary_key_method
    one_to_many :key_method
    one_to_many :primary_key_column
    many_to_many :left_primary_key_column
    many_to_many :right_primary_key_method

    Using these new options, you can now define associations
    that work correctly when the underlying primary/foreign key
    columns clash with existing ruby method names. See the RDoc
    for details.

  • A use_after_commit_rollback setting has been added to models.
    This defaults to true, but can be set to false for performance
    or to allow models to be used in prepared transactions
    (which don’t support after_commit/after_rollback).

  • Dataset#update_ignore has been added when connecting to MySQL,
    enabling use of the UPDATE IGNORE syntax to skip updating a row
    if the update would cause a unique constraint to be violated.

  • Database#indexes is now supported when connecting to Microsoft
    SQL Server.

  • On Microsoft SQL Server, the :include option is now supported
    when creating indexes, for storing column values in the index,
    which can be used by the query optimizer.

= Other Improvements

  • The filtering/excluding by associations code now uses qualified
    identifiers instead of unqualified identifiers, which allows it
    to avoid ambiguous column names if you are doing your own joins.

  • Virtual row blocks that return arrays are now handled correctly
    in Dataset#select_map/select_order_map.

  • Dataset#select_map/select_order_map can now take both a block
    argument as well as a regular argument.

  • Dataset#select_order_map now handles virtual row blocks that
    return ordered expressions.

  • Database#table_exists? should no longer generate false negatives
    if you only have permission to retrieve some column values but
    not all. Note that if you lack permission to SELECT from the
    table itself, table_exists? can still generate false negatives.

  • The active_model plugin now supports ActiveModel 3.2, by adding
    support for to_partial_path.

  • The serialization_modification_detection plugin now handles
    changed_columns correctly both for new objects and after saving
    objects.

  • The serialization plugin now clears the deserialized values when
    it does the automatic refresh after saving a new object, mostly for
    consistency. You can use the skip_create_refresh plugin to skip
    refreshing when creating a new model object.

  • Column default values are now wrapped in parentheses on SQLite,
    which fixes some cases such as when the default is an SQL function
    call.

  • Alter table emulation now works correctly on SQLite when foreign
    keys reference the table being altered. The emulation requires
    a renaming/deleting the existing table and creating a new table,
    which can break foreign key references. Sequel now disables the
    foreign key PRAGMA when altering tables, so SQLite won’t track
    the table renames and break the foreign key relationships.

  • The set_column_type table alteration method no longer modifies
    default values and NULL/NOT NULL settings on Microsoft SQL
    Server, H2, and SQLite.

  • On MySQL, Time/DateTime columns now use the timestamp type if the
    default value is Sequel::CURRENT_TIMESTAMP, since it is currently
    impossible for MySQL to have a non-constant default for a
    datetime column (without using a workaround like a trigger).

  • Metadata methods such as tables, views, and view_exists? are now
    handled correctly on Oracle if custom identifier input methods
    are used.

  • Sequel now ignores errors that occur when attempting to get
    information on column defaults in Oracle (which can happen if you
    lack permission to the appropriate table). Previously, such errors
    would cause the schema parser to raise an error, now, the schema
    information is just returned without default information.

  • Database#indexes now skips the primary key index when connecting to
    DB2, Derby, HSQLDB, and Oracle via the jdbc adapter.

  • Database#indexes now works correctly on DB2.

  • The progress adapter has been fixed, it had been broken since the
    dataset literalization refactoring.

  • Dataset#naked! now works correctly. Previously, it just returned
    the receiver unmodified.

  • Dataset#paginate! has been removed, as it was broken.

  • The query extension no longer breaks Dataset#clone if an argument
    is not given.

  • Transaction related queries are no longer logged twice in the mock
    adapter.

= Backwards Compatibility

  • Sequel’s default handling of NOT IN operators with an empty array
    of values has changed, which can change which rows are returned for
    such queries.

    Previously, Sequel was inconsistent in that it tried to handle NULL
    values correctly in the IN case, but not in the NOT IN case. Now,
    it defaults to handling NULL values correctly in both cases:

    3.31.0

    DB[:a].where(:b=>[])

    SELECT * FROM a WHERE (b != b)

    DB[:a].exclude(:b=>[])

    SELECT * FROM a WHERE (1 = 1)

    3.32.0

    DB[:a].where(:b=>[])

    SELECT * FROM a WHERE (b != b)

    DB[:a].exclude(:b=>[])

    SELECT * FROM a WHERE (b = b)

    The important change in behavior is that in the NOT IN case, if
    the left hand argument is NULL, the filter returns NULL instead
    of true. This has the potential to change query results.

    “Correct” here is really an opinion and not a fact, as there are
    valid arguments for the alternative behavior:

    DB[:a].where(:b=>[])

    SELECT * FROM a WHERE (1 = 0)

    DB[:a].exclude(:b=>[])

    SELECT * FROM a WHERE (1 = 1)

    The difference is that the “correct” NULL behavior is more
    consistent with the non-empty array cases. For example, if b is
    NULL:

    “Correct” NULL handling

    Empty array: where(:b=>[])

    WHERE (b != b) # NULL
    WHERE (b = b) # NULL

    Non-empty array: where(:b=>[1, 2])

    WHERE (b IN (1, 2)) # NULL
    WHERE (b NOT IN (1, 2)) # NULL

    Static boolean handling

    Empty array: where(:b=>[])

    WHERE (1 = 0) # false
    WHERE (1 = 1) # true

    Non-empty array: where(:b=>[1, 2])

    WHERE (b IN (1, 2)) # NULL
    WHERE (b NOT IN (1, 2)) # NULL

    Sequel chooses to default to behavior consistent with the non-empty
    array cases (similar to SQLAlchemy). However, there are two
    downsides to this handling. The first is that some databases with
    poor optimizers (e.g. MySQL) might do a full table scan with the
    default syntax. The second is that the static boolean handling may
    be generally perferable, if you believe that IN/NOT IN with an
    empty array should always be true or false and never NULL even if
    the left hand argument is NULL.

    As there really isn’t a truly correct answer in this case, Sequel
    defaults to the “correct” NULL handling, and allows you to switch
    to the static boolean handling via:

    Sequel.empty_array_handle_nulls = false

    This is currently a global setting, it may be made Database or
    Dataset specific later if requested. Also, it is possible the
    default will switch in the future, so if you care about a specific
    handling, you should set your own default.

  • Database#table_exists? now only rescues Sequel::DatabaseErrors
    instead of StandardErrors, so it’s possible it will raise errors
    instead of returning false on custom adapters that don’t wrap
    their errors correctly.

Thanks,
Jeremy