Module: Sequel::MSSQL::DatasetMethods
- Included in:
- ADO::MSSQL::Dataset, JDBC::MSSQL::Dataset, ODBC::MSSQL::Dataset
- Defined in:
- lib/sequel/adapters/shared/mssql.rb
Constant Summary collapse
- BOOL_TRUE =
'1'.freeze
- BOOL_FALSE =
'0'.freeze
- COMMA_SEPARATOR =
', '.freeze
- DELETE_CLAUSE_METHODS =
Dataset.clause_methods(:delete, %w'with from output from2 where')
- INSERT_CLAUSE_METHODS =
Dataset.clause_methods(:insert, %w'with into columns output values')
- SELECT_CLAUSE_METHODS =
Dataset.clause_methods(:select, %w'with distinct limit columns into from lock join where group having order compounds')
- UPDATE_CLAUSE_METHODS =
Dataset.clause_methods(:update, %w'with table set output from where')
- NOLOCK =
' WITH (NOLOCK)'.freeze
- UPDLOCK =
' WITH (UPDLOCK)'.freeze
- WILDCARD =
LiteralString.new('*').freeze
- CONSTANT_MAP =
{:CURRENT_DATE=>'CAST(CURRENT_TIMESTAMP AS DATE)'.freeze, :CURRENT_TIME=>'CAST(CURRENT_TIMESTAMP AS TIME)'.freeze}
Instance Method Summary collapse
-
#complex_expression_sql(op, args) ⇒ Object
MSSQL uses + for string concatenation, and LIKE is case insensitive by default.
-
#constant_sql(constant) ⇒ Object
MSSQL doesn’t support the SQL standard CURRENT_DATE or CURRENT_TIME.
-
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT.
-
#disable_insert_output! ⇒ Object
Disable the use of INSERT OUTPUT, modifying the receiver.
-
#fetch_rows(sql, &block) ⇒ Object
When returning all rows, if an offset is used, delete the row_number column before yielding the row.
-
#full_text_search(cols, terms, opts = {}) ⇒ Object
MSSQL uses the CONTAINS keyword for full text search.
-
#insert_select(*values) ⇒ Object
Use the OUTPUT clause to get the value of all columns for the newly inserted record.
-
#into(table) ⇒ Object
Specify a table for a SELECT …
-
#multi_insert_sql(columns, values) ⇒ Object
MSSQL uses a UNION ALL statement to insert multiple values at once.
-
#nolock ⇒ Object
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
-
#output(into, values) ⇒ Object
Include an OUTPUT clause in the eventual INSERT, UPDATE, or DELETE query.
-
#output!(into, values) ⇒ Object
An output method that modifies the receiver.
-
#quoted_identifier(name) ⇒ Object
MSSQL uses [] to quote identifiers.
-
#select_sql ⇒ Object
MSSQL Requires the use of the ROW_NUMBER window function to emulate an offset.
-
#server_version ⇒ Object
The version of the database server.
-
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT.
-
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE.
-
#supports_join_using? ⇒ Boolean
MSSQL doesn’t support JOIN USING.
-
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets.
-
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators.
-
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the output clause.
-
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions.
Instance Method Details
#complex_expression_sql(op, args) ⇒ Object
MSSQL uses + for string concatenation, and LIKE is case insensitive by default.
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 215 def complex_expression_sql(op, args) case op when :'||' super(:+, args) when :ILIKE super(:LIKE, args) when :"NOT ILIKE" super(:"NOT LIKE", args) when :<< "(#{literal(args[0])} * POWER(2, #{literal(args[1])}))" when :>> "(#{literal(args[0])} / POWER(2, #{literal(args[1])}))" else super(op, args) end end |
#constant_sql(constant) ⇒ Object
MSSQL doesn’t support the SQL standard CURRENT_DATE or CURRENT_TIME
233 234 235 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 233 def constant_sql(constant) CONSTANT_MAP[constant] || super end |
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT
238 239 240 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 238 def disable_insert_output clone(:disable_insert_output=>true) end |
#disable_insert_output! ⇒ Object
Disable the use of INSERT OUTPUT, modifying the receiver
243 244 245 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 243 def disable_insert_output! mutation_method(:disable_insert_output) end |
#fetch_rows(sql, &block) ⇒ Object
When returning all rows, if an offset is used, delete the row_number column before yielding the row.
249 250 251 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 249 def fetch_rows(sql, &block) @opts[:offset] ? super(sql){|r| r.delete(row_number_column); yield r} : super(sql, &block) end |
#full_text_search(cols, terms, opts = {}) ⇒ Object
MSSQL uses the CONTAINS keyword for full text search
254 255 256 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 254 def full_text_search(cols, terms, opts = {}) filter("CONTAINS (#{literal(cols)}, #{literal(terms)})") end |
#insert_select(*values) ⇒ Object
Use the OUTPUT clause to get the value of all columns for the newly inserted record.
259 260 261 262 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 259 def insert_select(*values) return unless supports_output_clause? naked.clone(default_server_opts(:sql=>output(nil, [:inserted.*]).insert_sql(*values))).single_record unless opts[:disable_insert_output] end |
#into(table) ⇒ Object
Specify a table for a SELECT … INTO query.
265 266 267 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 265 def into(table) clone(:into => table) end |
#multi_insert_sql(columns, values) ⇒ Object
MSSQL uses a UNION ALL statement to insert multiple values at once.
270 271 272 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 270 def multi_insert_sql(columns, values) [insert_sql(columns, LiteralString.new(values.map {|r| "SELECT #{expression_list(r)}" }.join(" UNION ALL ")))] end |
#nolock ⇒ Object
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
275 276 277 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 275 def nolock lock_style(:dirty) end |
#output(into, values) ⇒ Object
Include an OUTPUT clause in the eventual INSERT, UPDATE, or DELETE query.
The first argument is the table to output into, and the second argument is either an Array of column values to select, or a Hash which maps output column names to selected values, in the style of #insert or #update.
Output into a returned result set is not currently supported.
Examples:
dataset.output(:output_table, [:deleted__id, :deleted__name])
dataset.output(:output_table, :id => :inserted__id, :name => :inserted__name)
291 292 293 294 295 296 297 298 299 300 301 302 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 291 def output(into, values) raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? output = {} case values when Hash output[:column_list], output[:select_list] = values.keys, values.values when Array output[:select_list] = values end output[:into] = into clone({:output => output}) end |
#output!(into, values) ⇒ Object
An output method that modifies the receiver.
305 306 307 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 305 def output!(into, values) mutation_method(:output, into, values) end |
#quoted_identifier(name) ⇒ Object
MSSQL uses [] to quote identifiers
310 311 312 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 310 def quoted_identifier(name) "[#{name}]" end |
#select_sql ⇒ Object
MSSQL Requires the use of the ROW_NUMBER window function to emulate an offset. This implementation requires MSSQL 2005 or greater (offset can’t be emulated well in MSSQL 2000).
The implementation is ugly, cloning the current dataset and modifying the clone to add a ROW_NUMBER window function (and some other things), then using the modified clone in a subselect which is selected from.
If offset is used, an order must be provided, because the use of ROW_NUMBER requires an order.
324 325 326 327 328 329 330 331 332 333 334 335 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 324 def select_sql return super unless o = @opts[:offset] raise(Error, 'MSSQL requires an order be provided if using an offset') unless order = @opts[:order] dsa1 = dataset_alias(1) rn = row_number_column subselect_sql(unlimited. unordered. select_append{ROW_NUMBER(:over, :order=>order){}.as(rn)}. from_self(:alias=>dsa1). limit(@opts[:limit]). where(SQL::Identifier.new(rn) > o)) end |
#server_version ⇒ Object
The version of the database server.
338 339 340 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 338 def server_version db.server_version(@opts[:server]) end |
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT
343 344 345 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 343 def supports_intersect_except? is_2005_or_later? end |
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE
348 349 350 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 348 def supports_is_true? false end |
#supports_join_using? ⇒ Boolean
MSSQL doesn’t support JOIN USING
353 354 355 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 353 def supports_join_using? false end |
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets
358 359 360 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 358 def is_2005_or_later? end |
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators
363 364 365 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 363 def supports_multiple_column_in? false end |
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the output clause.
368 369 370 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 368 def supports_output_clause? is_2005_or_later? end |
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions
373 374 375 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 373 def supports_window_functions? true end |