1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
|
= Performance Enhancements
* Dataset literalization for simple datasets is now faster by
creating a per-adapter SQL literalization method instead of
having all adapters share a generic method with higher overhead.
Sequel.split_symbol now caches results globally. Symbol
literalization is now cached per Database.
Combining these three optimizations, here are the performance
increases compared to 4.9.0 for a couple example datasets:
ds1 = DB[:a]
ds2 = DB[:a].select(:a, :b).where(:c=>1).order(:d, :e)
.sql .all (1 row)
ds1 140% 11%
ds2 187% 32%
* Regular association loading now uses a placeholder literalizer
in most cases, for up to an 85% improvement when loading
simple associations.
* Eager loading associations using Dataset#eager now uses a
placeholder literalizer in most cases, for up to a
20% improvement when eager loading simple associations.
* Eager loading associations with limits using Dataset#eager now
uses a UNION-based strategy by default. After extensive
testing, this was found to be the fastest strategy if the
key columns are indexed. Unfortunately, it is a much slower
strategy if the key columns are not indexed. You can override
the default UNION strategy by using the :eager_limit_strategy
association option.
On some databases, execution time of UNION queries with n subqueries
increases faster than O(n). Also, there are limits on the number of
subqueries supported in a single UNION query. Sequel chooses a
default limit of 40 subqueries per UNION query. You can increase
this via the :subqueries_per_union association option.
* Dataset#import and #multi_insert can now insert multiple rows
in a single query on H2, HSQLDB, Derby, SQLAnywhere, CUBRID,
SQLite, Oracle, DB2, and Firebird, which should be significantly
faster than previous versions that issued a separate INSERT query
per row.
* The many_to_many setter method in the association_pks plugin now
uses Dataset#import to insert many rows at once, instead of using
a seperate query per insert.
* The jdbc adapter's type conversion has been rewritten to be
more similar to the other adapters, setting up the type
conversion procs before iterating over results. This increases
performance up to 20%.
* The jdbc/oracle adapter now defaults to a fetch_size of 100,
similar to the oci8-based oracle adapter, significantly improving
performance for large datasets.
= New Features
* Database#transaction now supports an :auto_savepoint option. This
option makes it so that transactions inside the transaction block
automatically use savepoints unless they use the :savepoint=>false
option. This should make testing transactional behavior easier.
* Model.prepared_finder has been added. This has an API similar to
Model.finder, but it uses a prepared statement instead of a
placeholder literalizer. It is less flexible than Model.finder
as prepared statements have fixed SQL, but it may perform better.
* Common table expressions (WITH clauses) are now supported on SQLite
3.8.3+.
* :correlated_subquery has been added as an eager_graph and filter by
association limit strategy for one_to_one and one_to_many
associations. In certain cases it was found that this is faster
than the :window_function limit strategy. It is the default
filter by associations limit strategy on databases that do not
support window functions.
Filtering by limited associations using a correlated subquery
strategy does not work in all cases, but it should handle most
cases correctly.
* The prepared_statement_associations plugin now handles
one_through_one and one_through_many associations.
* Sequel now emulates support for offsets without limits on MySQL,
SQLite, H2, SQLAnywhere, and CUBRID.
* In the jdbc adapter, the Database#fetch_size accessor and
:fetch_size option can be used to automatically set the JDBC
fetch size for JDBC Statement objects created by the database.
* Dataset#with_fetch_size has been added to jdbc adapter datasets,
setting the fetch size to use on ResultSets generated by the
dataset. This generally has the effect of overriding the
Database fetch_size setting.
* On MySQL 5.6.5+, Sequel supports a :fractional_seconds Database
option, which will use fractional seconds for timestamp values,
and have the schema modification code create timestamp columns
that accept fractional timestamps by default.
* Database#call_mssql_sproc on Microsoft SQL Server now handles named
parameters:
DB.call_mssql_sproc(:sproc_name, :args => {
'input_arg1_name' => 'input arg1 value',
'input_arg2_name' => 'input arg2 value',
'output_arg_name' => [:output, 'int', 'result key name']
})
* Database#drop_view now supports an :if_exists option on SQLite,
MySQL, H2, and HSQLDB.
* Database#drop_table now supports an :if_exists option on HSQLDB.
* A :filter_limit_strategy association option has been added, for
choosing the strategy that will be used when filtering/excluding by
associations with limits. For backwards compatibility, Sequel will
fallback to looking at the :eager_limit_strategy option.
* A :server_version Database option is now supported on Microsoft SQL
Server, which will use the value given instead of querying for it.
= Other Improvements
* Dataset::PlaceholderLiteralizer arguments are how handled
correctly when emulating offsets via the row_number window function
on DB2, MSSQL <=2012, and Oracle.
* Dataset::PlaceholderLiteralizer now handles DelayedEvaluation
objects correctly.
* Offset emulation is skipped if static SQL is used on Access,
DB2, and MSSQL <=2008.
* Additional disconnect errors are now recognized in the postgres
adapter.
* The :foreign_key_constraint_name option is now respected when
adding a foreign key column to an existing table on MySQL.
* Sequel now attempts to work around a bug on MySQL 5.6+ when
combining DROP FOREIGN KEY and DROP INDEX in the same ALTER TABLE
statement.
* Dataset#for_update is now respected on H2.
* Timestamp with local time zone types are now returned as
Time/DateTime objects on jdbc/oracle.
* Model.include now has the same API as Module.include.
* Model#marshallable! now works correctly when using the
tactical_eager_loading plugin.
* The pg_array_associations plugin now attempts to automatically
determine the correct array type to use, and explicitly casts
to that array type in more places.
* The auto_validations plugin now handles models that select from
subqueries.
* The association_pks plugin does no longer creates getter and setter
methods for one_through_one associations.
* bin/sequel now uses the Sequel code in the related lib directory.
This makes it easier to use from a repository checkout.
= Backwards Compatibility
* AssociationReflection#associated_dataset now returns a joined
dataset for associations that require joins (e.g. many_to_many).
Anyone using this directly for associations that require joins
probably needs to update their code.
* Model.associate now adds the association instance methods instead
of relying on the def_#{association_type} method doing so. Anyone
using custom association types probably needs to update their code.
* Model.eager_loading_dataset, .apply_association_dataset_opts, and
.def_{add_method,association_dataset_methods,remove_methods}
are now deprecated.
* Key conditions for associations requiring joins have been moved
from the JOIN ON clause to the WHERE clause. This should be
optimized the same by the database, but it can break tests that
expect specific SQL.
* Dataset#_insert_sql and #_update_sql are now private instead of
protected.
* The install/uninstall rake tasks have been removed.
* Model association and association reflection internals have
changed significantly, if you were relying on them, you'll
probably need to update your code.
* Database transaction internals have changed significantly, if you
were relying on them, you'll probably need to update your code.
* Dataset literalization internals have changed significantly, with
the Dataset#*_clause_methods private methods being removed.
Custom adapters that used these methods should switch to using the
new Dataset.def_sql_method method.
* Common table expressions are no longer enabled by default in
Sequel. External adapters for databases that support common
table expressions should define Dataset#supports_cte?(type) to
return true.
* Support for RETURNING is no longer determined via introspection.
External adapters for databases that support RETURNING should
define Dataset#supports_returning?(type) to return true.
* The new jdbc adapter type conversion code may not be completely
compatible with the previous code. The currently known case
where it is different is on jdbc/postgresql, when using an
array type where no conversion proc exists, the returned object
will be a ruby array containing java objects, instead of a ruby
array containing ruby objects. It is recommended that
jdbc/postgresql users using array types use the pg_array extension
to avoid this issue.
|
