Module: Sequel::MSSQL::DatasetMethods
- Includes:
- EmulateOffsetWithRowNumber
- Included in:
- ADO::MSSQL::Dataset, ODBC::MSSQL::Dataset, TinyTDS::Dataset
- Defined in:
- lib/sequel/adapters/shared/mssql.rb
Constant Summary collapse
- CONSTANT_MAP =
{:CURRENT_DATE=>'CAST(CURRENT_TIMESTAMP AS DATE)'.freeze, :CURRENT_TIME=>'CAST(CURRENT_TIMESTAMP AS TIME)'.freeze}.freeze
- EXTRACT_MAP =
{:year=>"yy", :month=>"m", :day=>"d", :hour=>"hh", :minute=>"n", :second=>"s"}.freeze
- LIMIT_ALL =
Object.new.freeze
Instance Method Summary collapse
- #complex_expression_sql_append(sql, op, args) ⇒ Object
-
#constant_sql_append(sql, constant) ⇒ Object
MSSQL doesn't support the SQL standard CURRENT_DATE or CURRENT_TIME.
-
#cross_apply(table) ⇒ Object
Uses CROSS APPLY to join the given table into the current dataset.
-
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT.
-
#escape_like(string) ⇒ Object
MSSQL treats [] as a metacharacter in LIKE expresions.
-
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
MSSQL uses the CONTAINS keyword for full text search.
-
#insert_select(*values) ⇒ Object
Insert a record, returning the record inserted, using OUTPUT.
-
#insert_select_sql(*values) ⇒ Object
Add OUTPUT clause unless there is already an existing output clause, then return the SQL to insert.
-
#into(table) ⇒ Object
Specify a table for a SELECT …
-
#mssql_unicode_strings ⇒ Object
Use the database's mssql_unicode_strings setting if the dataset hasn't overridden it.
-
#nolock ⇒ Object
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
-
#outer_apply(table) ⇒ Object
Uses OUTER APPLY to join the given table into the current dataset.
-
#output(into, values) ⇒ Object
Include an OUTPUT clause in the eventual INSERT, UPDATE, or DELETE query.
-
#quoted_identifier_append(sql, name) ⇒ Object
MSSQL uses [] to quote identifiers.
-
#returning(*values) ⇒ Object
Emulate RETURNING using the output clause.
-
#select_sql ⇒ Object
On MSSQL 2012+ add a default order to the current dataset if an offset is used.
-
#server_version ⇒ Object
The version of the database server.
- #supports_cte?(type = :select) ⇒ Boolean
-
#supports_group_cube? ⇒ Boolean
MSSQL 2005+ supports GROUP BY CUBE.
-
#supports_group_rollup? ⇒ Boolean
MSSQL 2005+ supports GROUP BY ROLLUP.
-
#supports_grouping_sets? ⇒ Boolean
MSSQL 2008+ supports GROUPING SETS.
-
#supports_insert_select? ⇒ Boolean
MSSQL supports insert_select via the OUTPUT clause.
-
#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_nowait? ⇒ Boolean
MSSQL supports NOWAIT.
-
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
MSSQL 2012+ supports offsets in correlated subqueries.
-
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the OUTPUT clause.
-
#supports_returning?(type) ⇒ Boolean
MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.
-
#supports_skip_locked? ⇒ Boolean
MSSQL uses READPAST to skip locked rows.
-
#supports_where_true? ⇒ Boolean
MSSQL cannot use WHERE 1.
-
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions.
-
#with_mssql_unicode_strings(v) ⇒ Object
Return a cloned dataset with the mssql_unicode_strings option set.
-
#with_ties ⇒ Object
Use WITH TIES when limiting the result set to also include additional rows matching the last row.
Methods included from EmulateOffsetWithRowNumber
Instance Method Details
#complex_expression_sql_append(sql, op, args) ⇒ Object
526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 526 def complex_expression_sql_append(sql, op, args) case op when :'||' super(sql, :+, args) when :LIKE, :"NOT LIKE" super(sql, op, args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CS_AS)"], a)}) when :ILIKE, :"NOT ILIKE" super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CI_AS)"], a)}) when :<<, :>> complex_expression_emulate_append(sql, op, args) when :extract part = args[0] raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] if part == :second expr = args[1] sql << "CAST((datepart(" << format.to_s << ', ' literal_append(sql, expr) sql << ') + datepart(ns, ' literal_append(sql, expr) sql << ")/1000000000.0) AS double precision)" else sql << "datepart(" << format.to_s << ', ' literal_append(sql, args[1]) sql << ')' end else super end end |
#constant_sql_append(sql, constant) ⇒ Object
MSSQL doesn't support the SQL standard CURRENT_DATE or CURRENT_TIME
557 558 559 560 561 562 563 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 557 def constant_sql_append(sql, constant) if c = CONSTANT_MAP[constant] sql << c else super end end |
#cross_apply(table) ⇒ Object
Uses CROSS APPLY to join the given table into the current dataset.
566 567 568 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 566 def cross_apply(table) join_table(:cross_apply, table) end |
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT
571 572 573 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 571 def disable_insert_output clone(:disable_insert_output=>true) end |
#escape_like(string) ⇒ Object
MSSQL treats [] as a metacharacter in LIKE expresions.
576 577 578 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 576 def escape_like(string) string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
MSSQL uses the CONTAINS keyword for full text search
581 582 583 584 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 581 def full_text_search(cols, terms, opts = OPTS) terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) end |
#insert_select(*values) ⇒ Object
Insert a record, returning the record inserted, using OUTPUT. Always returns nil without running an INSERT statement if disable_insert_output is used. If the query runs but returns no values, returns false.
589 590 591 592 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 589 def insert_select(*values) return unless supports_insert_select? with_sql_first(insert_select_sql(*values)) || false end |
#insert_select_sql(*values) ⇒ Object
Add OUTPUT clause unless there is already an existing output clause, then return the SQL to insert.
596 597 598 599 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 596 def insert_select_sql(*values) ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) ds.insert_sql(*values) end |
#into(table) ⇒ Object
Specify a table for a SELECT … INTO query.
602 603 604 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 602 def into(table) clone(:into => table) end |
#mssql_unicode_strings ⇒ Object
Use the database's mssql_unicode_strings setting if the dataset hasn't overridden it.
517 518 519 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 517 def mssql_unicode_strings opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings end |
#nolock ⇒ Object
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
607 608 609 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 607 def nolock lock_style(:dirty) end |
#outer_apply(table) ⇒ Object
Uses OUTER APPLY to join the given table into the current dataset.
612 613 614 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 612 def outer_apply(table) join_table(:outer_apply, table) 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, [Sequel[:deleted][:id], Sequel[:deleted][:name]])
dataset.output(:output_table, id: Sequel[:inserted][:id], name: Sequel[:inserted][:name])
628 629 630 631 632 633 634 635 636 637 638 639 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 628 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 |
#quoted_identifier_append(sql, name) ⇒ Object
MSSQL uses [] to quote identifiers.
642 643 644 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 642 def quoted_identifier_append(sql, name) sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' end |
#returning(*values) ⇒ Object
Emulate RETURNING using the output clause. This only handles values that are simple column references.
647 648 649 650 651 652 653 654 655 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 647 def returning(*values) values = values.map do |v| unless r = unqualified_column_for(v) raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") end r end clone(:returning=>values) end |
#select_sql ⇒ Object
On MSSQL 2012+ add a default order to the current dataset if an offset is used. The default offset emulation using a subquery would be used in the unordered case by default, and that also adds a default order, so it's better to just avoid the subquery.
661 662 663 664 665 666 667 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 661 def select_sql if @opts[:offset] raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] return order(1).select_sql if is_2012_or_later? && !@opts[:order] end super end |
#server_version ⇒ Object
The version of the database server.
670 671 672 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 670 def server_version db.server_version(@opts[:server]) end |
#supports_cte?(type = :select) ⇒ Boolean
674 675 676 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 674 def supports_cte?(type=:select) is_2005_or_later? end |
#supports_group_cube? ⇒ Boolean
MSSQL 2005+ supports GROUP BY CUBE.
679 680 681 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 679 def supports_group_cube? is_2005_or_later? end |
#supports_group_rollup? ⇒ Boolean
MSSQL 2005+ supports GROUP BY ROLLUP
684 685 686 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 684 def supports_group_rollup? is_2005_or_later? end |
#supports_grouping_sets? ⇒ Boolean
MSSQL 2008+ supports GROUPING SETS
689 690 691 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 689 def supports_grouping_sets? is_2008_or_later? end |
#supports_insert_select? ⇒ Boolean
MSSQL supports insert_select via the OUTPUT clause.
694 695 696 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 694 def supports_insert_select? supports_output_clause? && !opts[:disable_insert_output] end |
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT
699 700 701 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 699 def supports_intersect_except? is_2005_or_later? end |
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE
704 705 706 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 704 def supports_is_true? false end |
#supports_join_using? ⇒ Boolean
MSSQL doesn't support JOIN USING
709 710 711 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 709 def supports_join_using? false end |
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets
714 715 716 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 714 def is_2005_or_later? end |
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators
719 720 721 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 719 def supports_multiple_column_in? false end |
#supports_nowait? ⇒ Boolean
MSSQL supports NOWAIT.
724 725 726 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 724 def supports_nowait? true end |
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
MSSQL 2012+ supports offsets in correlated subqueries.
729 730 731 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 729 def is_2012_or_later? end |
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the OUTPUT clause.
734 735 736 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 734 def supports_output_clause? is_2005_or_later? end |
#supports_returning?(type) ⇒ Boolean
MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.
739 740 741 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 739 def supports_returning?(type) supports_insert_select? end |
#supports_skip_locked? ⇒ Boolean
MSSQL uses READPAST to skip locked rows.
744 745 746 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 744 def supports_skip_locked? true end |
#supports_where_true? ⇒ Boolean
MSSQL cannot use WHERE 1.
754 755 756 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 754 def supports_where_true? false end |
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions
749 750 751 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 749 def supports_window_functions? true end |
#with_mssql_unicode_strings(v) ⇒ Object
Return a cloned dataset with the mssql_unicode_strings option set.
522 523 524 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 522 def with_mssql_unicode_strings(v) clone(:mssql_unicode_strings=>v) end |
#with_ties ⇒ Object
Use WITH TIES when limiting the result set to also include additional rows matching the last row.
760 761 762 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 760 def with_ties clone(:limit_with_ties=>true) end |