- The :limit option is now respected when eager loading via either eager or
eager_graph. By default, Sequel
will just do an array slice of the resulting ruby array, which gets the
correct answer, but does not offer any performance improvements. Sequel also offers a new
:eager_limit_strategy option for using more advanced query types that only
load the related records from the database. The available values for the
:eager_limit_strategy option are:
:window_function - This uses the row_number window function
partitioned by the related key fields. It can only be used
on databases that support window functions (PostgreSQL 8.4+,
Microsoft SQL Server 2005+, DB2).
:correlated_subquery - This uses a correlated subquery that is
limited. It works on most databases except MySQL and DB2.
You can provide a value of true as the option to have Sequel pick a strategy to use. Sequel will never use a correlated
subquery for true, since in some cases it can perform worse than loading
all related records and doing the array slice in ruby.
If you want to enable an eager_limit_strategy globally, you can set
Sequel::Model.default_eager_limit_strategy to a value, and all associations
that use :limit will default to using that strategy.
- one_to_one associations that do not represent true one-to-one database
relationships, but represent one-to-many relationships where you are only
returning the first object based on a given order are also now handled
correctly when eager loading. Previously, eager loading such associations
resulted in the last matching object being associated instead of the first
matching object being associated.
You can also use an :eager_limit_strategy for one_to_one associations. In
addition to the :window_function and :correlated_subquery values, there is
also a :distinct_on value that is available on PostgreSQL for using
DISTINCT ON, which is the fastest strategy if you are using PostgreSQL.
- Dataset#map, to_hash, select_map, select_order_map, and select_hash now
accept arrays of symbols, and if given arrays of symbols, use arrays of
results. For example:
DB[:items].map([:id, :name])
# => [[1, 'foo'], [2, 'bar'], ...]
DB[:items].to_hash([:id, :foo_id], [:name, :bar_id])
# => {[1, 3]=>['foo', 5], [2, 4]=>['bar', 6], ...}
- For SQL expression objects where Sequel cannot deduce the type of
the object, it now will consider the type of the argument when a &, |,
or + operator is used. For example:
:x & 1
Previously, this did "x AND 1", now it does "x &
1". Using a logical operator on an integer doesn‘t make sense,
but it‘s possible people did so if the database uses 1/0 for
true/false. Likewise:
:x + 'foo'
Previously, this did "x + ‘foo’" (addition), now it
does "x || ‘foo’" (string concatenation).
- The sql_string, sql_number, and sql_boolean methods are now available on
SQL::ComplexExpressions, so you can do:
(:x + 1).sql_string + ' foos'
# (x + 1) || ' foos'
Previously, there was not an easy way to generate such SQL expressions.
- :after_load association hooks are now applied when using eager_graph.
Previously, they were only applied when using eager, not when using
eager_graph.
- Database#copy_table has been added to the postgres adapter if pg is used as
the underlying driver. It allows you to get very fast exports of table data
in text or CSV format. It also accepts datasets, allowing fast exports of
arbitrary queries in text or CSV format.
- SQL extract support (:timestamp.extract(:year)) is now emulated on the
databases that don‘t natively support it, such as SQLite, Microsoft
SQL Server, and DB2. At least the following values are supported for
extraction: :year, :month, :day, :hour, :minute, and :second.
- The bitwise XOR operator is now emulated on SQLite. Previously, attempting
to use it would cause the database to raise an error.
- A Database#use_timestamp_timezones accessor has been added on SQLite. This
allows you to turn off the use of timezones in timestamps by setting the
value to false. This is necessary if you want you want to use the SQLite
datetime functions, or the new ability to emulate extract.
Note that this setting does not affect the current database content. To
convert old databases to the new format, you‘ll have to resave all
rows that have timestamps.
At some point in the future, Sequel may default to not using
timezones in timestamps by default on SQLite, so if you would like to rely
on the current behavior, you should set this accessor to true now.
- Sequel now works around bugs in
MySQL when using a subselect with a LIMIT by using a nested subselect.
- Sequel now works around issues
in Microsoft SQL Server and DB2 when using a subselect with IN/NOT IN that
uses the emulated offset support.
- The jdbc adapter now returns java.sql.Clob objects as Sequel::SQL::Blobs.
- Sequel now considers database
clob types as the :blob schema type.
- Sequel::SQLTime.create has been added for more easily creating instances:
Sequel::SQLTime.create(hour, minute, second, usec)
- Dataset#select_all now accepts SQL::AliasedExpression and SQL::JoinClause
arguments and returns the appropriate SQL::ColumnAll value that selects all
columns from the related table.
- Model.set_dataset now accepts Sequel::LiteralString
objects that represent table names. This usage is not encouraged except in
rare cases such as using a set returning function in PostgreSQL.
- Dataset#supports_cte? now takes an optional argument specifying the type of
query (:insert, :update, :delete, :select). It defaults to :select.
- Dataset#supports_returning? has been added. It requires an argument
specifying the type of query (:insert, :update, or :delete).
- Dataset#supports_cte_in_subqueries? has been added for checking for support
for this ability. Apparently, only PostgreSQL currently supports this. For
other adapters that support CTEs but not in subqueries, if a subquery with
a CTE is used in a JOIN, the CTE is moved from the subquery to the main
query.
- Dataset#supports_select_all_and_column has been added for seeing if
"SELECT *, foo …" style queries are supported. This is
false on DB2, which doesn‘t allow such queries. When it is false,
using select_append on a dataset that doesn‘t specifically select
columns will now change the query to do "SELECT table.*, foo
…" instead, working around the limitation on DB2.
- Dataset#supports_ordered_distinct_on? has been added. Currently, this is
only true on PostgreSQL. MySQL can emulate DISTINCT ON using GROUP BY, but
it doesn‘t respect ORDER BY, so it some cases it cannot be used
equivalently.
- Dataset#supports_where_true? has been added for checking for support of
WHERE TRUE (or WHERE 1 if 1 is true). Not all databases support using such
a construct, and on the databases that do not, you have to use WHERE (1 =
1) or something similar.