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
550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 550 def complex_expression_sql_append(sql, op, args) case op when :'||' super(sql, :+, args) when :LIKE, :"NOT LIKE" super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)")) when :ILIKE, :"NOT ILIKE" super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)")) 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
581 582 583 584 585 586 587 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 581 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.
590 591 592 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 590 def cross_apply(table) join_table(:cross_apply, table) end |
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT
595 596 597 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 595 def disable_insert_output clone(:disable_insert_output=>true) end |
#escape_like(string) ⇒ Object
MSSQL treats [] as a metacharacter in LIKE expresions.
600 601 602 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 600 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
605 606 607 608 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 605 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.
613 614 615 616 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 613 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.
620 621 622 623 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 620 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.
626 627 628 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 626 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.
541 542 543 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 541 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).
631 632 633 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 631 def nolock lock_style(:dirty) end |
#outer_apply(table) ⇒ Object
Uses OUTER APPLY to join the given table into the current dataset.
636 637 638 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 636 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])
652 653 654 655 656 657 658 659 660 661 662 663 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 652 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.
666 667 668 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 666 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.
671 672 673 674 675 676 677 678 679 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 671 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.
685 686 687 688 689 690 691 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 685 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.
694 695 696 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 694 def server_version db.server_version(@opts[:server]) end |
#supports_cte?(type = :select) ⇒ Boolean
698 699 700 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 698 def supports_cte?(type=:select) is_2005_or_later? end |
#supports_group_cube? ⇒ Boolean
MSSQL 2005+ supports GROUP BY CUBE.
703 704 705 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 703 def supports_group_cube? is_2005_or_later? end |
#supports_group_rollup? ⇒ Boolean
MSSQL 2005+ supports GROUP BY ROLLUP
708 709 710 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 708 def supports_group_rollup? is_2005_or_later? end |
#supports_grouping_sets? ⇒ Boolean
MSSQL 2008+ supports GROUPING SETS
713 714 715 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 713 def supports_grouping_sets? is_2008_or_later? end |
#supports_insert_select? ⇒ Boolean
MSSQL supports insert_select via the OUTPUT clause.
718 719 720 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 718 def supports_insert_select? supports_output_clause? && !opts[:disable_insert_output] end |
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT
723 724 725 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 723 def supports_intersect_except? is_2005_or_later? end |
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE
728 729 730 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 728 def supports_is_true? false end |
#supports_join_using? ⇒ Boolean
MSSQL doesn't support JOIN USING
733 734 735 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 733 def supports_join_using? false end |
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets
738 739 740 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 738 def is_2005_or_later? end |
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators
743 744 745 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 743 def supports_multiple_column_in? false end |
#supports_nowait? ⇒ Boolean
MSSQL supports NOWAIT.
748 749 750 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 748 def supports_nowait? true end |
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
MSSQL 2012+ supports offsets in correlated subqueries.
753 754 755 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 753 def is_2012_or_later? end |
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the OUTPUT clause.
758 759 760 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 758 def supports_output_clause? is_2005_or_later? end |
#supports_returning?(type) ⇒ Boolean
MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.
763 764 765 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 763 def supports_returning?(type) supports_insert_select? end |
#supports_skip_locked? ⇒ Boolean
MSSQL uses READPAST to skip locked rows.
768 769 770 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 768 def supports_skip_locked? true end |
#supports_where_true? ⇒ Boolean
MSSQL cannot use WHERE 1.
778 779 780 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 778 def supports_where_true? false end |
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions
773 774 775 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 773 def supports_window_functions? true end |
#with_mssql_unicode_strings(v) ⇒ Object
Return a cloned dataset with the mssql_unicode_strings option set.
546 547 548 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 546 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.
784 785 786 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 784 def with_ties clone(:limit_with_ties=>true) end |