module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 525 def complex_expression_sql_append(sql, op, args) 526 case op 527 when :'||' 528 super(sql, :+, args) 529 when :LIKE, :"NOT LIKE" 530 super(sql, op, args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CS_AS)"], a)}) 531 when :ILIKE, :"NOT ILIKE" 532 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CI_AS)"], a)}) 533 when :<<, :>> 534 complex_expression_emulate_append(sql, op, args) 535 when :extract 536 part = args[0] 537 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 538 if part == :second 539 expr = args[1] 540 sql << "CAST((datepart(" << format.to_s << ', ' 541 literal_append(sql, expr) 542 sql << ') + datepart(ns, ' 543 literal_append(sql, expr) 544 sql << ")/1000000000.0) AS double precision)" 545 else 546 sql << "datepart(" << format.to_s << ', ' 547 literal_append(sql, args[1]) 548 sql << ')' 549 end 550 else 551 super 552 end 553 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 565 def cross_apply(table) 566 join_table(:cross_apply, table) 567 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 570 def disable_insert_output 571 clone(:disable_insert_output=>true) 572 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 575 def escape_like(string) 576 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 577 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 580 def full_text_search(cols, terms, opts = OPTS) 581 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 582 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 583 end
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.
# File lib/sequel/adapters/shared/mssql.rb 588 def insert_select(*values) 589 return unless supports_insert_select? 590 with_sql_first(insert_select_sql(*values)) || false 591 end
Add OUTPUT clause unless there is already an existing output clause, then return the SQL
to insert.
# File lib/sequel/adapters/shared/mssql.rb 595 def insert_select_sql(*values) 596 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 597 ds.insert_sql(*values) 598 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 601 def into(table) 602 clone(:into => table) 603 end
Use the database's mssql_unicode_strings
setting if the dataset hasn't overridden it.
# File lib/sequel/adapters/shared/mssql.rb 516 def mssql_unicode_strings 517 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 518 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 606 def nolock 607 lock_style(:dirty) 608 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 611 def outer_apply(table) 612 join_table(:outer_apply, table) 613 end
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])
# File lib/sequel/adapters/shared/mssql.rb 627 def output(into, values) 628 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 629 output = {} 630 case values 631 when Hash 632 output[:column_list], output[:select_list] = values.keys, values.values 633 when Array 634 output[:select_list] = values 635 end 636 output[:into] = into 637 clone(:output => output) 638 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 641 def quoted_identifier_append(sql, name) 642 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 643 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 646 def returning(*values) 647 values = values.map do |v| 648 unless r = unqualified_column_for(v) 649 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 650 end 651 r 652 end 653 clone(:returning=>values) 654 end
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.
Sequel::EmulateOffsetWithRowNumber#select_sql
# File lib/sequel/adapters/shared/mssql.rb 660 def select_sql 661 if @opts[:offset] && !@opts[:order] && is_2012_or_later? 662 order(1).select_sql 663 else 664 super 665 end 666 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 669 def server_version 670 db.server_version(@opts[:server]) 671 end
# File lib/sequel/adapters/shared/mssql.rb 673 def supports_cte?(type=:select) 674 is_2005_or_later? 675 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 678 def supports_group_cube? 679 is_2005_or_later? 680 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 683 def supports_group_rollup? 684 is_2005_or_later? 685 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 688 def supports_grouping_sets? 689 is_2008_or_later? 690 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 693 def supports_insert_select? 694 supports_output_clause? && !opts[:disable_insert_output] 695 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 698 def supports_intersect_except? 699 is_2005_or_later? 700 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 703 def supports_is_true? 704 false 705 end
MSSQL
doesn't support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 708 def supports_join_using? 709 false 710 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 713 def supports_modifying_joins? 714 is_2005_or_later? 715 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 718 def supports_multiple_column_in? 719 false 720 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 723 def supports_nowait? 724 true 725 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 733 def supports_output_clause? 734 is_2005_or_later? 735 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 738 def supports_returning?(type) 739 supports_insert_select? 740 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 743 def supports_skip_locked? 744 true 745 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 753 def supports_where_true? 754 false 755 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 748 def supports_window_functions? 749 true 750 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 521 def with_mssql_unicode_strings(v) 522 clone(:mssql_unicode_strings=>v) 523 end
Protected Instance Methods
If returned primary keys are requested, use OUTPUT unless already set on the dataset. If OUTPUT is already set, use existing returning values. If OUTPUT is only set to return a single columns, return an array of just that column. Otherwise, return an array of hashes.
# File lib/sequel/adapters/shared/mssql.rb 763 def _import(columns, values, opts=OPTS) 764 if opts[:return] == :primary_key && !@opts[:output] 765 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 766 elsif @opts[:output] 767 statements = multi_insert_sql(columns, values) 768 @db.transaction(opts.merge(:server=>@opts[:server])) do 769 statements.map{|st| with_sql(st)} 770 end.first.map{|v| v.length == 1 ? v.values.first : v} 771 else 772 super 773 end 774 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 777 def aggregate_dataset 778 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 779 end
If the dataset using a order without a limit or offset or custom SQL
, remove the order. Compounds on Microsoft SQL
Server have undefined order unless the result is specifically ordered. Applying the current order before the compound doesn't work in all cases, such as when qualified identifiers are used. If you want to ensure a order for a compound dataset, apply the order after all compounds have been added.
# File lib/sequel/adapters/shared/mssql.rb 788 def compound_from_self 789 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 790 clone(:limit=>LIMIT_ALL).from_self 791 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 792 unordered 793 else 794 super 795 end 796 end
Private Instance Methods
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 801 def check_not_limited!(type) 802 return if @opts[:skip_limit_check] && type != :truncate 803 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 804 super if type == :truncate || @opts[:offset] 805 end
Use strict ISO-8601 format with T between date and time, since that is the format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 825 def default_timestamp_format 826 "'%Y-%m-%dT%H:%M:%S%N%z'" 827 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 836 def delete_from2_sql(sql) 837 if joined_dataset? 838 select_from_sql(sql) 839 select_join_sql(sql) 840 end 841 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 830 def delete_from_sql(sql) 831 sql << ' FROM ' 832 source_list_append(sql, @opts[:from][0..0]) 833 end
# File lib/sequel/adapters/shared/mssql.rb 844 def delete_output_sql(sql) 845 output_sql(sql, :DELETED) 846 end
There is no function on Microsoft SQL
Server that does character length and respects trailing spaces (datalength respects trailing spaces, but counts bytes instead of characters). Use a hack to work around the trailing spaces issue.
# File lib/sequel/adapters/shared/mssql.rb 852 def emulate_function?(name) 853 name == :char_length || name == :trim 854 end
# File lib/sequel/adapters/shared/mssql.rb 856 def emulate_function_sql_append(sql, f) 857 case f.name 858 when :char_length 859 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 860 when :trim 861 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 862 end 863 end
Microsoft SQL
Server 2012+ has native support for offsets, but only for ordered datasets.
Sequel::EmulateOffsetWithRowNumber#emulate_offset_with_row_number?
# File lib/sequel/adapters/shared/mssql.rb 866 def emulate_offset_with_row_number? 867 super && !(is_2012_or_later? && @opts[:order]) 868 end
Return the first primary key for the current table. If this table has multiple primary keys, this will only return one of them. Used by #_import.
# File lib/sequel/adapters/shared/mssql.rb 872 def first_primary_key 873 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 874 end
# File lib/sequel/adapters/shared/mssql.rb 876 def insert_output_sql(sql) 877 output_sql(sql, :INSERTED) 878 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 808 def is_2005_or_later? 809 server_version >= 9000000 810 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 813 def is_2008_or_later? 814 server_version >= 10000000 815 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 818 def is_2012_or_later? 819 server_version >= 11000000 820 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 882 def join_type_sql(join_type) 883 case join_type 884 when :cross_apply 885 'CROSS APPLY' 886 when :outer_apply 887 'OUTER APPLY' 888 else 889 super 890 end 891 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 894 def literal_blob_append(sql, v) 895 sql << '0x' << v.unpack("H*").first 896 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 900 def literal_date(v) 901 v.strftime("'%Y%m%d'") 902 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 905 def literal_false 906 '0' 907 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 911 def literal_string_append(sql, v) 912 sql << (mssql_unicode_strings ? "N'" : "'") 913 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 914 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 917 def literal_true 918 '1' 919 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 923 def multi_insert_sql_strategy 924 is_2008_or_later? ? :values : :union 925 end
# File lib/sequel/adapters/shared/mssql.rb 927 def non_sql_option?(key) 928 super || key == :disable_insert_output || key == :mssql_unicode_strings 929 end
# File lib/sequel/adapters/shared/mssql.rb 1024 def output_list_sql(sql, output) 1025 sql << " OUTPUT " 1026 column_list_append(sql, output[:select_list]) 1027 if into = output[:into] 1028 sql << " INTO " 1029 identifier_append(sql, into) 1030 if column_list = output[:column_list] 1031 sql << ' (' 1032 source_list_append(sql, column_list) 1033 sql << ')' 1034 end 1035 end 1036 end
# File lib/sequel/adapters/shared/mssql.rb 1038 def output_returning_sql(sql, type, values) 1039 sql << " OUTPUT " 1040 if values.empty? 1041 literal_append(sql, SQL::ColumnAll.new(type)) 1042 else 1043 values = values.map do |v| 1044 case v 1045 when SQL::AliasedExpression 1046 Sequel.qualify(type, v.expression).as(v.alias) 1047 else 1048 Sequel.qualify(type, v) 1049 end 1050 end 1051 column_list_append(sql, values) 1052 end 1053 end
# File lib/sequel/adapters/shared/mssql.rb 1015 def output_sql(sql, type) 1016 return unless supports_output_clause? 1017 if output = @opts[:output] 1018 output_list_sql(sql, output) 1019 elsif values = @opts[:returning] 1020 output_returning_sql(sql, type, values) 1021 end 1022 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1056 def requires_emulating_nulls_first? 1057 true 1058 end
# File lib/sequel/adapters/shared/mssql.rb 931 def select_into_sql(sql) 932 if i = @opts[:into] 933 sql << " INTO " 934 identifier_append(sql, i) 935 end 936 end
MSSQL
2000 uses TOP N for limit. For MSSQL
2005+ TOP (N) is used to allow the limit to be a bound variable.
# File lib/sequel/adapters/shared/mssql.rb 940 def select_limit_sql(sql) 941 if l = @opts[:limit] 942 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 943 shared_limit_sql(sql, l) 944 end 945 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 970 def select_lock_sql(sql) 971 lock = @opts[:lock] 972 skip_locked = @opts[:skip_locked] 973 nowait = @opts[:nowait] 974 for_update = lock == :update 975 dirty = lock == :dirty 976 lock_hint = for_update || dirty 977 978 if lock_hint || skip_locked 979 sql << " WITH (" 980 981 if lock_hint 982 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 983 end 984 985 if skip_locked || nowait 986 sql << ', ' if lock_hint 987 sql << (skip_locked ? "READPAST" : "NOWAIT") 988 end 989 990 sql << ')' 991 else 992 super 993 end 994 end
On 2012+ when there is an order with an offset, append the offset (and possible limit) at the end of the order clause.
# File lib/sequel/adapters/shared/mssql.rb 998 def select_order_sql(sql) 999 super 1000 if is_2012_or_later? && @opts[:order] 1001 if o = @opts[:offset] 1002 sql << " OFFSET " 1003 literal_append(sql, o) 1004 sql << " ROWS" 1005 1006 if l = @opts[:limit] 1007 sql << " FETCH NEXT " 1008 literal_append(sql, l) 1009 sql << " ROWS ONLY" 1010 end 1011 end 1012 end 1013 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1062 def sqltime_precision 1063 6 1064 end
# File lib/sequel/adapters/shared/mssql.rb 962 def update_limit_sql(sql) 963 if l = @opts[:limit] 964 shared_limit_sql(sql, l) 965 end 966 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1074 def update_table_sql(sql) 1075 sql << ' ' 1076 source_list_append(sql, @opts[:from][0..0]) 1077 end
# File lib/sequel/adapters/shared/mssql.rb 1079 def uses_with_rollup? 1080 !is_2008_or_later? 1081 end