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.
-
#count(*a, &block) ⇒ Object
For a dataset with custom SQL, since it may include ORDER BY, you cannot wrap it in a subquery.
-
#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.
-
#empty? ⇒ Boolean
For a dataset with custom SQL, since it may include ORDER BY, you cannot wrap it in a subquery.
-
#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_merge? ⇒ Boolean
MSSQL 2008+ supports MERGE.
-
#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.
Instance Method Details
#complex_expression_sql_append(sql, op, args) ⇒ Object
560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 560 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
591 592 593 594 595 596 597 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 591 def constant_sql_append(sql, constant) if c = CONSTANT_MAP[constant] sql << c else super end end |
#count(*a, &block) ⇒ Object
For a dataset with custom SQL, since it may include ORDER BY, you cannot wrap it in a subquery. Load entire query in this case to get the number of rows. In general, you should avoid calling this method on datasets with custom SQL.
603 604 605 606 607 608 609 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 603 def count(*a, &block) if (@opts[:sql] && a.empty? && !block) naked.to_a.length else super end end |
#cross_apply(table) ⇒ Object
Uses CROSS APPLY to join the given table into the current dataset.
612 613 614 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 612 def cross_apply(table) join_table(:cross_apply, table) end |
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT
617 618 619 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 617 def disable_insert_output clone(:disable_insert_output=>true) end |
#empty? ⇒ Boolean
For a dataset with custom SQL, since it may include ORDER BY, you cannot wrap it in a subquery. Run query, and if it returns any records, return true. In general, you should avoid calling this method on datasets with custom SQL.
625 626 627 628 629 630 631 632 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 625 def empty? if @opts[:sql] naked.each{return false} true else super end end |
#escape_like(string) ⇒ Object
MSSQL treats [] as a metacharacter in LIKE expresions.
635 636 637 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 635 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
640 641 642 643 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 640 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.
648 649 650 651 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 648 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.
655 656 657 658 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 655 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.
661 662 663 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 661 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.
551 552 553 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 551 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).
666 667 668 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 666 def nolock lock_style(:dirty) end |
#outer_apply(table) ⇒ Object
Uses OUTER APPLY to join the given table into the current dataset.
671 672 673 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 671 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])
687 688 689 690 691 692 693 694 695 696 697 698 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 687 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.
701 702 703 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 701 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.
706 707 708 709 710 711 712 713 714 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 706 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.
720 721 722 723 724 725 726 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 720 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.
729 730 731 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 729 def server_version db.server_version(@opts[:server]) end |
#supports_cte?(type = :select) ⇒ Boolean
733 734 735 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 733 def supports_cte?(type=:select) is_2005_or_later? end |
#supports_group_cube? ⇒ Boolean
MSSQL 2005+ supports GROUP BY CUBE.
738 739 740 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 738 def supports_group_cube? is_2005_or_later? end |
#supports_group_rollup? ⇒ Boolean
MSSQL 2005+ supports GROUP BY ROLLUP
743 744 745 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 743 def supports_group_rollup? is_2005_or_later? end |
#supports_grouping_sets? ⇒ Boolean
MSSQL 2008+ supports GROUPING SETS
748 749 750 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 748 def supports_grouping_sets? is_2008_or_later? end |
#supports_insert_select? ⇒ Boolean
MSSQL supports insert_select via the OUTPUT clause.
753 754 755 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 753 def supports_insert_select? supports_output_clause? && !opts[:disable_insert_output] end |
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT
758 759 760 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 758 def supports_intersect_except? is_2005_or_later? end |
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE
763 764 765 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 763 def supports_is_true? false end |
#supports_join_using? ⇒ Boolean
MSSQL doesn’t support JOIN USING
768 769 770 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 768 def supports_join_using? false end |
#supports_merge? ⇒ Boolean
MSSQL 2008+ supports MERGE
773 774 775 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 773 def supports_merge? is_2008_or_later? end |
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets
778 779 780 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 778 def is_2005_or_later? end |
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators
783 784 785 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 783 def supports_multiple_column_in? false end |
#supports_nowait? ⇒ Boolean
MSSQL supports NOWAIT.
788 789 790 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 788 def supports_nowait? true end |
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
MSSQL 2012+ supports offsets in correlated subqueries.
793 794 795 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 793 def is_2012_or_later? end |
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the OUTPUT clause.
798 799 800 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 798 def supports_output_clause? is_2005_or_later? end |
#supports_returning?(type) ⇒ Boolean
MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.
803 804 805 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 803 def supports_returning?(type) supports_insert_select? end |
#supports_skip_locked? ⇒ Boolean
MSSQL uses READPAST to skip locked rows.
808 809 810 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 808 def supports_skip_locked? true end |
#supports_where_true? ⇒ Boolean
MSSQL cannot use WHERE 1.
818 819 820 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 818 def supports_where_true? false end |
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions
813 814 815 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 813 def supports_window_functions? true end |
#with_mssql_unicode_strings(v) ⇒ Object
Return a cloned dataset with the mssql_unicode_strings option set.
556 557 558 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 556 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.
824 825 826 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 824 def with_ties clone(:limit_with_ties=>true) end |