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
546 547 548 549 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 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 546 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
577 578 579 580 581 582 583 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 577 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.
586 587 588 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 586 def cross_apply(table) join_table(:cross_apply, table) end |
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT
591 592 593 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 591 def disable_insert_output clone(:disable_insert_output=>true) end |
#escape_like(string) ⇒ Object
MSSQL treats [] as a metacharacter in LIKE expresions.
596 597 598 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 596 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
601 602 603 604 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 601 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.
609 610 611 612 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 609 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.
616 617 618 619 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 616 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.
622 623 624 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 622 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.
537 538 539 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 537 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).
627 628 629 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 627 def nolock lock_style(:dirty) end |
#outer_apply(table) ⇒ Object
Uses OUTER APPLY to join the given table into the current dataset.
632 633 634 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 632 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])
648 649 650 651 652 653 654 655 656 657 658 659 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 648 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.
662 663 664 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 662 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.
667 668 669 670 671 672 673 674 675 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 667 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.
681 682 683 684 685 686 687 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 681 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.
690 691 692 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 690 def server_version db.server_version(@opts[:server]) end |
#supports_cte?(type = :select) ⇒ Boolean
694 695 696 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 694 def supports_cte?(type=:select) is_2005_or_later? end |
#supports_group_cube? ⇒ Boolean
MSSQL 2005+ supports GROUP BY CUBE.
699 700 701 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 699 def supports_group_cube? is_2005_or_later? end |
#supports_group_rollup? ⇒ Boolean
MSSQL 2005+ supports GROUP BY ROLLUP
704 705 706 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 704 def supports_group_rollup? is_2005_or_later? end |
#supports_grouping_sets? ⇒ Boolean
MSSQL 2008+ supports GROUPING SETS
709 710 711 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 709 def supports_grouping_sets? is_2008_or_later? end |
#supports_insert_select? ⇒ Boolean
MSSQL supports insert_select via the OUTPUT clause.
714 715 716 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 714 def supports_insert_select? supports_output_clause? && !opts[:disable_insert_output] end |
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT
719 720 721 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 719 def supports_intersect_except? is_2005_or_later? end |
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE
724 725 726 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 724 def supports_is_true? false end |
#supports_join_using? ⇒ Boolean
MSSQL doesn't support JOIN USING
729 730 731 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 729 def supports_join_using? false end |
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets
734 735 736 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 734 def is_2005_or_later? end |
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators
739 740 741 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 739 def supports_multiple_column_in? false end |
#supports_nowait? ⇒ Boolean
MSSQL supports NOWAIT.
744 745 746 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 744 def supports_nowait? true end |
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
MSSQL 2012+ supports offsets in correlated subqueries.
749 750 751 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 749 def is_2012_or_later? end |
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the OUTPUT clause.
754 755 756 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 754 def supports_output_clause? is_2005_or_later? end |
#supports_returning?(type) ⇒ Boolean
MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.
759 760 761 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 759 def supports_returning?(type) supports_insert_select? end |
#supports_skip_locked? ⇒ Boolean
MSSQL uses READPAST to skip locked rows.
764 765 766 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 764 def supports_skip_locked? true end |
#supports_where_true? ⇒ Boolean
MSSQL cannot use WHERE 1.
774 775 776 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 774 def supports_where_true? false end |
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions
769 770 771 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 769 def supports_window_functions? true end |
#with_mssql_unicode_strings(v) ⇒ Object
Return a cloned dataset with the mssql_unicode_strings option set.
542 543 544 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 542 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.
780 781 782 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 780 def with_ties clone(:limit_with_ties=>true) end |