module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 550 def complex_expression_sql_append(sql, op, args) 551 case op 552 when :'||' 553 super(sql, :+, args) 554 when :LIKE, :"NOT LIKE" 555 super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)")) 556 when :ILIKE, :"NOT ILIKE" 557 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)")) 558 when :<<, :>> 559 complex_expression_emulate_append(sql, op, args) 560 when :extract 561 part = args[0] 562 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 563 if part == :second 564 expr = args[1] 565 sql << "CAST((datepart(" << format.to_s << ', ' 566 literal_append(sql, expr) 567 sql << ') + datepart(ns, ' 568 literal_append(sql, expr) 569 sql << ")/1000000000.0) AS double precision)" 570 else 571 sql << "datepart(" << format.to_s << ', ' 572 literal_append(sql, args[1]) 573 sql << ')' 574 end 575 else 576 super 577 end 578 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 590 def cross_apply(table) 591 join_table(:cross_apply, table) 592 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 595 def disable_insert_output 596 clone(:disable_insert_output=>true) 597 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 600 def escape_like(string) 601 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 602 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 605 def full_text_search(cols, terms, opts = OPTS) 606 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 607 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 608 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 613 def insert_select(*values) 614 return unless supports_insert_select? 615 with_sql_first(insert_select_sql(*values)) || false 616 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 620 def insert_select_sql(*values) 621 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 622 ds.insert_sql(*values) 623 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 626 def into(table) 627 clone(:into => table) 628 end
Use the database’s mssql_unicode_strings
setting if the dataset hasn’t overridden it.
# File lib/sequel/adapters/shared/mssql.rb 541 def mssql_unicode_strings 542 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 543 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 631 def nolock 632 lock_style(:dirty) 633 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 636 def outer_apply(table) 637 join_table(:outer_apply, table) 638 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 652 def output(into, values) 653 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 654 output = {} 655 case values 656 when Hash 657 output[:column_list], output[:select_list] = values.keys, values.values 658 when Array 659 output[:select_list] = values 660 end 661 output[:into] = into 662 clone(:output => output) 663 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 666 def quoted_identifier_append(sql, name) 667 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 668 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 671 def returning(*values) 672 values = values.map do |v| 673 unless r = unqualified_column_for(v) 674 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 675 end 676 r 677 end 678 clone(:returning=>values) 679 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 685 def select_sql 686 if @opts[:offset] 687 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 688 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 689 end 690 super 691 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 694 def server_version 695 db.server_version(@opts[:server]) 696 end
# File lib/sequel/adapters/shared/mssql.rb 698 def supports_cte?(type=:select) 699 is_2005_or_later? 700 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 703 def supports_group_cube? 704 is_2005_or_later? 705 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 708 def supports_group_rollup? 709 is_2005_or_later? 710 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 713 def supports_grouping_sets? 714 is_2008_or_later? 715 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 718 def supports_insert_select? 719 supports_output_clause? && !opts[:disable_insert_output] 720 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 723 def supports_intersect_except? 724 is_2005_or_later? 725 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 728 def supports_is_true? 729 false 730 end
MSSQL
doesn’t support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 733 def supports_join_using? 734 false 735 end
MSSQL
2008+ supports MERGE
# File lib/sequel/adapters/shared/mssql.rb 738 def supports_merge? 739 is_2008_or_later? 740 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 743 def supports_modifying_joins? 744 is_2005_or_later? 745 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 748 def supports_multiple_column_in? 749 false 750 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 753 def supports_nowait? 754 true 755 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 763 def supports_output_clause? 764 is_2005_or_later? 765 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 768 def supports_returning?(type) 769 supports_insert_select? 770 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 773 def supports_skip_locked? 774 true 775 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 783 def supports_where_true? 784 false 785 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 778 def supports_window_functions? 779 true 780 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 546 def with_mssql_unicode_strings(v) 547 clone(:mssql_unicode_strings=>v) 548 end
Use WITH TIES when limiting the result set to also include additional rows matching the last row.
# File lib/sequel/adapters/shared/mssql.rb 789 def with_ties 790 clone(:limit_with_ties=>true) 791 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 799 def _import(columns, values, opts=OPTS) 800 if opts[:return] == :primary_key && !@opts[:output] 801 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 802 elsif @opts[:output] 803 statements = multi_insert_sql(columns, values) 804 ds = naked 805 @db.transaction(opts.merge(:server=>@opts[:server])) do 806 statements.map{|st| ds.with_sql(st)} 807 end.first.map{|v| v.length == 1 ? v.values.first : v} 808 else 809 super 810 end 811 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 820 def compound_from_self 821 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 822 clone(:limit=>LIMIT_ALL).from_self 823 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 824 unordered 825 else 826 super 827 end 828 end
Private Instance Methods
Normalize conditions for MERGE WHEN.
# File lib/sequel/adapters/shared/mssql.rb 833 def _merge_when_conditions_sql(sql, data) 834 if data.has_key?(:conditions) 835 sql << " AND " 836 literal_append(sql, _normalize_merge_when_conditions(data[:conditions])) 837 end 838 end
MSSQL
requires a semicolon at the end of MERGE.
# File lib/sequel/adapters/shared/mssql.rb 856 def _merge_when_sql(sql) 857 super 858 sql << ';' 859 end
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
# File lib/sequel/adapters/shared/mssql.rb 842 def _normalize_merge_when_conditions(conditions) 843 case conditions 844 when nil, false 845 {1=>0} 846 when true 847 {1=>1} 848 when Sequel::SQL::DelayedEvaluation 849 Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))} 850 else 851 conditions 852 end 853 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 862 def aggregate_dataset 863 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 864 end
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 867 def check_not_limited!(type) 868 return if @opts[:skip_limit_check] && type != :truncate 869 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 870 super if type == :truncate || @opts[:offset] 871 end
Determine whether to add the COLLATE for LIKE arguments, based on the Database
setting.
# File lib/sequel/adapters/shared/mssql.rb 889 def complex_expression_sql_like_args(args, collation) 890 if db.like_without_collate 891 args 892 else 893 args.map{|a| Sequel.lit(["(", collation], a)} 894 end 895 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 900 def default_timestamp_format 901 "'%Y-%m-%dT%H:%M:%S%N%z'" 902 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 911 def delete_from2_sql(sql) 912 if joined_dataset? 913 select_from_sql(sql) 914 select_join_sql(sql) 915 end 916 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 905 def delete_from_sql(sql) 906 sql << ' FROM ' 907 source_list_append(sql, @opts[:from][0..0]) 908 end
# File lib/sequel/adapters/shared/mssql.rb 919 def delete_output_sql(sql) 920 output_sql(sql, :DELETED) 921 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 927 def emulate_function?(name) 928 name == :char_length || name == :trim 929 end
# File lib/sequel/adapters/shared/mssql.rb 931 def emulate_function_sql_append(sql, f) 932 case f.name 933 when :char_length 934 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 935 when :trim 936 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 937 end 938 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 941 def emulate_offset_with_row_number? 942 super && !(is_2012_or_later? && @opts[:order]) 943 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 947 def first_primary_key 948 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 949 end
# File lib/sequel/adapters/shared/mssql.rb 951 def insert_output_sql(sql) 952 output_sql(sql, :INSERTED) 953 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 874 def is_2005_or_later? 875 server_version >= 9000000 876 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 879 def is_2008_or_later? 880 server_version >= 10000000 881 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 884 def is_2012_or_later? 885 server_version >= 11000000 886 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 957 def join_type_sql(join_type) 958 case join_type 959 when :cross_apply 960 'CROSS APPLY' 961 when :outer_apply 962 'OUTER APPLY' 963 else 964 super 965 end 966 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 969 def literal_blob_append(sql, v) 970 sql << '0x' << v.unpack("H*").first 971 end
Use YYYYmmdd format, since that’s the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 975 def literal_date(v) 976 v.strftime("'%Y%m%d'") 977 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 980 def literal_false 981 '0' 982 end
Optionally use unicode string syntax for all strings. Don’t double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 986 def literal_string_append(sql, v) 987 sql << (mssql_unicode_strings ? "N'" : "'") 988 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 989 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 992 def literal_true 993 '1' 994 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 998 def multi_insert_sql_strategy 999 is_2008_or_later? ? :values : :union 1000 end
# File lib/sequel/adapters/shared/mssql.rb 1002 def non_sql_option?(key) 1003 super || key == :disable_insert_output || key == :mssql_unicode_strings 1004 end
# File lib/sequel/adapters/shared/mssql.rb 1103 def output_list_sql(sql, output) 1104 sql << " OUTPUT " 1105 column_list_append(sql, output[:select_list]) 1106 if into = output[:into] 1107 sql << " INTO " 1108 identifier_append(sql, into) 1109 if column_list = output[:column_list] 1110 sql << ' (' 1111 source_list_append(sql, column_list) 1112 sql << ')' 1113 end 1114 end 1115 end
# File lib/sequel/adapters/shared/mssql.rb 1117 def output_returning_sql(sql, type, values) 1118 sql << " OUTPUT " 1119 if values.empty? 1120 literal_append(sql, SQL::ColumnAll.new(type)) 1121 else 1122 values = values.map do |v| 1123 case v 1124 when SQL::AliasedExpression 1125 Sequel.qualify(type, v.expression).as(v.alias) 1126 else 1127 Sequel.qualify(type, v) 1128 end 1129 end 1130 column_list_append(sql, values) 1131 end 1132 end
# File lib/sequel/adapters/shared/mssql.rb 1094 def output_sql(sql, type) 1095 return unless supports_output_clause? 1096 if output = @opts[:output] 1097 output_list_sql(sql, output) 1098 elsif values = @opts[:returning] 1099 output_returning_sql(sql, type, values) 1100 end 1101 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1135 def requires_emulating_nulls_first? 1136 true 1137 end
# File lib/sequel/adapters/shared/mssql.rb 1006 def select_into_sql(sql) 1007 if i = @opts[:into] 1008 sql << " INTO " 1009 identifier_append(sql, i) 1010 end 1011 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 1015 def select_limit_sql(sql) 1016 if l = @opts[:limit] 1017 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 1018 shared_limit_sql(sql, l) 1019 end 1020 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 1049 def select_lock_sql(sql) 1050 lock = @opts[:lock] 1051 skip_locked = @opts[:skip_locked] 1052 nowait = @opts[:nowait] 1053 for_update = lock == :update 1054 dirty = lock == :dirty 1055 lock_hint = for_update || dirty 1056 1057 if lock_hint || skip_locked 1058 sql << " WITH (" 1059 1060 if lock_hint 1061 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 1062 end 1063 1064 if skip_locked || nowait 1065 sql << ', ' if lock_hint 1066 sql << (skip_locked ? "READPAST" : "NOWAIT") 1067 end 1068 1069 sql << ')' 1070 else 1071 super 1072 end 1073 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 1077 def select_order_sql(sql) 1078 super 1079 if is_2012_or_later? && @opts[:order] 1080 if o = @opts[:offset] 1081 sql << " OFFSET " 1082 literal_append(sql, o) 1083 sql << " ROWS" 1084 1085 if l = @opts[:limit] 1086 sql << " FETCH NEXT " 1087 literal_append(sql, l) 1088 sql << " ROWS ONLY" 1089 end 1090 end 1091 end 1092 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1141 def sqltime_precision 1142 6 1143 end
# File lib/sequel/adapters/shared/mssql.rb 1041 def update_limit_sql(sql) 1042 if l = @opts[:limit] 1043 shared_limit_sql(sql, l) 1044 end 1045 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1153 def update_table_sql(sql) 1154 sql << ' ' 1155 source_list_append(sql, @opts[:from][0..0]) 1156 end
# File lib/sequel/adapters/shared/mssql.rb 1158 def uses_with_rollup? 1159 !is_2008_or_later? 1160 end