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
588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 588 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
619 620 621 622 623 624 625 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 619 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.
631 632 633 634 635 636 637 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 631 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.
640 641 642 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 640 def cross_apply(table) join_table(:cross_apply, table) end |
#disable_insert_output ⇒ Object
Disable the use of INSERT OUTPUT
645 646 647 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 645 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.
653 654 655 656 657 658 659 660 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 653 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.
663 664 665 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 663 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
668 669 670 671 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 668 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.
676 677 678 679 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 676 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.
683 684 685 686 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 683 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.
689 690 691 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 689 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.
579 580 581 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 579 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).
694 695 696 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 694 def nolock lock_style(:dirty) end |
#outer_apply(table) ⇒ Object
Uses OUTER APPLY to join the given table into the current dataset.
699 700 701 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 699 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])
715 716 717 718 719 720 721 722 723 724 725 726 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 715 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.
729 730 731 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 729 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.
734 735 736 737 738 739 740 741 742 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 734 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.
748 749 750 751 752 753 754 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 748 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.
757 758 759 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 757 def server_version db.server_version(@opts[:server]) end |
#supports_cte?(type = :select) ⇒ Boolean
761 762 763 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 761 def supports_cte?(type=:select) is_2005_or_later? end |
#supports_group_cube? ⇒ Boolean
MSSQL 2005+ supports GROUP BY CUBE.
766 767 768 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 766 def supports_group_cube? is_2005_or_later? end |
#supports_group_rollup? ⇒ Boolean
MSSQL 2005+ supports GROUP BY ROLLUP
771 772 773 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 771 def supports_group_rollup? is_2005_or_later? end |
#supports_grouping_sets? ⇒ Boolean
MSSQL 2008+ supports GROUPING SETS
776 777 778 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 776 def supports_grouping_sets? is_2008_or_later? end |
#supports_insert_select? ⇒ Boolean
MSSQL supports insert_select via the OUTPUT clause.
781 782 783 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 781 def supports_insert_select? supports_output_clause? && !opts[:disable_insert_output] end |
#supports_intersect_except? ⇒ Boolean
MSSQL 2005+ supports INTERSECT and EXCEPT
786 787 788 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 786 def supports_intersect_except? is_2005_or_later? end |
#supports_is_true? ⇒ Boolean
MSSQL does not support IS TRUE
791 792 793 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 791 def supports_is_true? false end |
#supports_join_using? ⇒ Boolean
MSSQL doesn’t support JOIN USING
796 797 798 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 796 def supports_join_using? false end |
#supports_merge? ⇒ Boolean
MSSQL 2008+ supports MERGE
801 802 803 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 801 def supports_merge? is_2008_or_later? end |
#supports_modifying_joins? ⇒ Boolean
MSSQL 2005+ supports modifying joined datasets
806 807 808 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 806 def is_2005_or_later? end |
#supports_multiple_column_in? ⇒ Boolean
MSSQL does not support multiple columns for the IN/NOT IN operators
811 812 813 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 811 def supports_multiple_column_in? false end |
#supports_nowait? ⇒ Boolean
MSSQL supports NOWAIT.
816 817 818 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 816 def supports_nowait? true end |
#supports_offsets_in_correlated_subqueries? ⇒ Boolean
MSSQL 2012+ supports offsets in correlated subqueries.
821 822 823 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 821 def is_2012_or_later? end |
#supports_output_clause? ⇒ Boolean
MSSQL 2005+ supports the OUTPUT clause.
826 827 828 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 826 def supports_output_clause? is_2005_or_later? end |
#supports_returning?(type) ⇒ Boolean
MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.
831 832 833 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 831 def supports_returning?(type) supports_insert_select? end |
#supports_skip_locked? ⇒ Boolean
MSSQL uses READPAST to skip locked rows.
836 837 838 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 836 def supports_skip_locked? true end |
#supports_where_true? ⇒ Boolean
MSSQL cannot use WHERE 1.
846 847 848 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 846 def supports_where_true? false end |
#supports_window_functions? ⇒ Boolean
MSSQL 2005+ supports window functions
841 842 843 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 841 def supports_window_functions? true end |
#with_mssql_unicode_strings(v) ⇒ Object
Return a cloned dataset with the mssql_unicode_strings option set.
584 585 586 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 584 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.
852 853 854 |
# File 'lib/sequel/adapters/shared/mssql.rb', line 852 def with_ties clone(:limit_with_ties=>true) end |