module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
Source
# File lib/sequel/adapters/shared/mssql.rb 601 def complex_expression_sql_append(sql, op, args) 602 case op 603 when :'||' 604 super(sql, :+, args) 605 when :LIKE, :"NOT LIKE" 606 super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)")) 607 when :ILIKE, :"NOT ILIKE" 608 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)")) 609 when :<<, :>> 610 complex_expression_emulate_append(sql, op, args) 611 when :extract 612 part = args[0] 613 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 614 if part == :second 615 expr = args[1] 616 sql << "CAST((datepart(" << format.to_s << ', ' 617 literal_append(sql, expr) 618 sql << ') + datepart(ns, ' 619 literal_append(sql, expr) 620 sql << ")/1000000000.0) AS double precision)" 621 else 622 sql << "datepart(" << format.to_s << ', ' 623 literal_append(sql, args[1]) 624 sql << ')' 625 end 626 else 627 super 628 end 629 end
Source
Source
# File lib/sequel/adapters/shared/mssql.rb 644 def count(*a, &block) 645 if (@opts[:sql] && a.empty? && !block) 646 naked.to_a.length 647 else 648 super 649 end 650 end
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
.
Source
# File lib/sequel/adapters/shared/mssql.rb 653 def cross_apply(table) 654 join_table(:cross_apply, table) 655 end
Uses CROSS APPLY to join the given table into the current dataset.
Source
# File lib/sequel/adapters/shared/mssql.rb 658 def disable_insert_output 659 clone(:disable_insert_output=>true) 660 end
Disable the use of INSERT OUTPUT
Source
# File lib/sequel/adapters/shared/mssql.rb 666 def empty? 667 if @opts[:sql] 668 naked.each{return false} 669 true 670 else 671 super 672 end 673 end
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
.
Sequel::EmulateOffsetWithRowNumber#empty?
Source
# File lib/sequel/adapters/shared/mssql.rb 676 def escape_like(string) 677 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 678 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
Source
# File lib/sequel/adapters/shared/mssql.rb 681 def full_text_search(cols, terms, opts = OPTS) 682 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 683 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 684 end
MSSQL
uses the CONTAINS keyword for full text search
Source
# File lib/sequel/adapters/shared/mssql.rb 689 def insert_select(*values) 690 return unless supports_insert_select? 691 with_sql_first(insert_select_sql(*values)) || false 692 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.
Source
# File lib/sequel/adapters/shared/mssql.rb 696 def insert_select_sql(*values) 697 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 698 ds.insert_sql(*values) 699 end
Add OUTPUT clause unless there is already an existing output clause, then return the SQL
to insert.
Source
# File lib/sequel/adapters/shared/mssql.rb 702 def into(table) 703 clone(:into => table) 704 end
Specify a table for a SELECT … INTO query.
Source
# File lib/sequel/adapters/shared/mssql.rb 592 def mssql_unicode_strings 593 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 594 end
Use the database’s mssql_unicode_strings
setting if the dataset hasn’t overridden it.
Source
# File lib/sequel/adapters/shared/mssql.rb 707 def nolock 708 lock_style(:dirty) 709 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
Source
# File lib/sequel/adapters/shared/mssql.rb 712 def outer_apply(table) 713 join_table(:outer_apply, table) 714 end
Uses OUTER APPLY to join the given table into the current dataset.
Source
# File lib/sequel/adapters/shared/mssql.rb 728 def output(into, values) 729 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 730 output = {} 731 case values 732 when Hash 733 output[:column_list], output[:select_list] = values.keys, values.values 734 when Array 735 output[:select_list] = values 736 end 737 output[:into] = into 738 clone(:output => output) 739 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])
Source
# File lib/sequel/adapters/shared/mssql.rb 742 def quoted_identifier_append(sql, name) 743 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 744 end
MSSQL
uses [] to quote identifiers.
Source
# File lib/sequel/adapters/shared/mssql.rb 747 def returning(*values) 748 values = values.map do |v| 749 unless r = unqualified_column_for(v) 750 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 751 end 752 r 753 end 754 clone(:returning=>values) 755 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
Source
# File lib/sequel/adapters/shared/mssql.rb 761 def select_sql 762 if @opts[:offset] 763 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 764 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 765 end 766 super 767 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
Source
# File lib/sequel/adapters/shared/mssql.rb 770 def server_version 771 db.server_version(@opts[:server]) 772 end
The version of the database server.
Source
# File lib/sequel/adapters/shared/mssql.rb 774 def supports_cte?(type=:select) 775 is_2005_or_later? 776 end
Source
# File lib/sequel/adapters/shared/mssql.rb 779 def supports_group_cube? 780 is_2005_or_later? 781 end
MSSQL
2005+ supports GROUP BY CUBE.
Source
# File lib/sequel/adapters/shared/mssql.rb 784 def supports_group_rollup? 785 is_2005_or_later? 786 end
MSSQL
2005+ supports GROUP BY ROLLUP
Source
# File lib/sequel/adapters/shared/mssql.rb 789 def supports_grouping_sets? 790 is_2008_or_later? 791 end
MSSQL
2008+ supports GROUPING SETS
Source
# File lib/sequel/adapters/shared/mssql.rb 794 def supports_insert_select? 795 supports_output_clause? && !opts[:disable_insert_output] 796 end
MSSQL
supports insert_select
via the OUTPUT clause.
Source
# File lib/sequel/adapters/shared/mssql.rb 799 def supports_intersect_except? 800 is_2005_or_later? 801 end
MSSQL
2005+ supports INTERSECT and EXCEPT
Source
# File lib/sequel/adapters/shared/mssql.rb 804 def supports_is_true? 805 false 806 end
MSSQL
does not support IS TRUE
Source
# File lib/sequel/adapters/shared/mssql.rb 809 def supports_join_using? 810 false 811 end
MSSQL
doesn’t support JOIN USING
Source
# File lib/sequel/adapters/shared/mssql.rb 814 def supports_merge? 815 is_2008_or_later? 816 end
MSSQL
2008+ supports MERGE
Source
# File lib/sequel/adapters/shared/mssql.rb 819 def supports_modifying_joins? 820 is_2005_or_later? 821 end
MSSQL
2005+ supports modifying joined datasets
Source
# File lib/sequel/adapters/shared/mssql.rb 824 def supports_multiple_column_in? 825 false 826 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
Source
# File lib/sequel/adapters/shared/mssql.rb 829 def supports_nowait? 830 true 831 end
MSSQL
supports NOWAIT.
Source
# File lib/sequel/adapters/shared/mssql.rb 839 def supports_output_clause? 840 is_2005_or_later? 841 end
MSSQL
2005+ supports the OUTPUT clause.
Source
# File lib/sequel/adapters/shared/mssql.rb 844 def supports_returning?(type) 845 supports_insert_select? 846 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
Source
# File lib/sequel/adapters/shared/mssql.rb 849 def supports_skip_locked? 850 true 851 end
MSSQL
uses READPAST to skip locked rows.
Source
# File lib/sequel/adapters/shared/mssql.rb 859 def supports_where_true? 860 false 861 end
MSSQL
cannot use WHERE 1.
Source
# File lib/sequel/adapters/shared/mssql.rb 854 def supports_window_functions? 855 true 856 end
MSSQL
2005+ supports window functions
Source
# File lib/sequel/adapters/shared/mssql.rb 597 def with_mssql_unicode_strings(v) 598 clone(:mssql_unicode_strings=>v) 599 end
Return a cloned dataset with the mssql_unicode_strings
option set.
Source
# File lib/sequel/adapters/shared/mssql.rb 865 def with_ties 866 clone(:limit_with_ties=>true) 867 end
Use WITH TIES when limiting the result set to also include additional rows matching the last row.
Protected Instance Methods
Source
# File lib/sequel/adapters/shared/mssql.rb 875 def _import(columns, values, opts=OPTS) 876 if opts[:return] == :primary_key && !@opts[:output] 877 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 878 elsif @opts[:output] 879 # no transaction: our multi_insert_sql_strategy should guarantee 880 # that there's only ever a single statement. 881 sql = multi_insert_sql(columns, values)[0] 882 naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v} 883 else 884 super 885 end 886 end
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.
Source
# File lib/sequel/adapters/shared/mssql.rb 895 def compound_from_self 896 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 897 clone(:limit=>LIMIT_ALL).from_self 898 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 899 unordered 900 else 901 super 902 end 903 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.
Private Instance Methods
Source
# File lib/sequel/adapters/shared/mssql.rb 908 def _merge_when_conditions_sql(sql, data) 909 if data.has_key?(:conditions) 910 sql << " AND " 911 literal_append(sql, _normalize_merge_when_conditions(data[:conditions])) 912 end 913 end
Normalize conditions for MERGE WHEN.
Source
# File lib/sequel/adapters/shared/mssql.rb 931 def _merge_when_sql(sql) 932 super 933 sql << ';' 934 end
MSSQL
requires a semicolon at the end of MERGE.
Source
# File lib/sequel/adapters/shared/mssql.rb 917 def _normalize_merge_when_conditions(conditions) 918 case conditions 919 when nil, false 920 {1=>0} 921 when true 922 {1=>1} 923 when Sequel::SQL::DelayedEvaluation 924 Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))} 925 else 926 conditions 927 end 928 end
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
Source
# File lib/sequel/adapters/shared/mssql.rb 937 def aggregate_dataset 938 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 939 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
Source
# File lib/sequel/adapters/shared/mssql.rb 942 def check_not_limited!(type) 943 return if @opts[:skip_limit_check] && type != :truncate 944 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 945 super if type == :truncate || @opts[:offset] 946 end
Allow update and delete for unordered, limited datasets only.
Source
# File lib/sequel/adapters/shared/mssql.rb 964 def complex_expression_sql_like_args(args, collation) 965 if db.like_without_collate 966 args 967 else 968 args.map{|a| Sequel.lit(["(", collation], a)} 969 end 970 end
Determine whether to add the COLLATE for LIKE arguments, based on the Database
setting.
Source
# File lib/sequel/adapters/shared/mssql.rb 975 def default_timestamp_format 976 "'%Y-%m-%dT%H:%M:%S.%3N'" 977 end
Use strict ISO-8601 format with T between date and time, since that is the format that is multilanguage and not DATEFORMAT dependent.
Source
# File lib/sequel/adapters/shared/mssql.rb 986 def delete_from2_sql(sql) 987 if joined_dataset? 988 select_from_sql(sql) 989 select_join_sql(sql) 990 end 991 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
Source
# File lib/sequel/adapters/shared/mssql.rb 980 def delete_from_sql(sql) 981 sql << ' FROM ' 982 source_list_append(sql, @opts[:from][0..0]) 983 end
Only include the primary table in the main delete clause
Source
# File lib/sequel/adapters/shared/mssql.rb 994 def delete_output_sql(sql) 995 output_sql(sql, :DELETED) 996 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1002 def emulate_function?(name) 1003 name == :char_length || name == :trim 1004 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.
Source
# File lib/sequel/adapters/shared/mssql.rb 1006 def emulate_function_sql_append(sql, f) 1007 case f.name 1008 when :char_length 1009 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 1010 when :trim 1011 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 1012 end 1013 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1016 def emulate_offset_with_row_number? 1017 super && !(is_2012_or_later? && @opts[:order]) 1018 end
Microsoft SQL
Server 2012+ has native support for offsets, but only for ordered datasets.
Sequel::EmulateOffsetWithRowNumber#emulate_offset_with_row_number?
Source
# File lib/sequel/adapters/shared/mssql.rb 1022 def first_primary_key 1023 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 1024 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
.
Source
# File lib/sequel/adapters/shared/mssql.rb 1026 def insert_output_sql(sql) 1027 output_sql(sql, :INSERTED) 1028 end
Source
# File lib/sequel/adapters/shared/mssql.rb 949 def is_2005_or_later? 950 server_version >= 9000000 951 end
Whether we are using SQL
Server 2005 or later.
Source
# File lib/sequel/adapters/shared/mssql.rb 954 def is_2008_or_later? 955 server_version >= 10000000 956 end
Whether we are using SQL
Server 2008 or later.
Source
# File lib/sequel/adapters/shared/mssql.rb 959 def is_2012_or_later? 960 server_version >= 11000000 961 end
Whether we are using SQL
Server 2012 or later.
Source
# File lib/sequel/adapters/shared/mssql.rb 1032 def join_type_sql(join_type) 1033 case join_type 1034 when :cross_apply 1035 'CROSS APPLY' 1036 when :outer_apply 1037 'OUTER APPLY' 1038 else 1039 super 1040 end 1041 end
Handle CROSS APPLY and OUTER APPLY JOIN types
Source
# File lib/sequel/adapters/shared/mssql.rb 1044 def literal_blob_append(sql, v) 1045 sql << '0x' << v.unpack("H*").first 1046 end
MSSQL
uses a literal hexidecimal number for blob strings
Source
# File lib/sequel/adapters/shared/mssql.rb 1050 def literal_date(v) 1051 v.strftime("'%Y%m%d'") 1052 end
Use YYYYmmdd format, since that’s the only format that is multilanguage and not DATEFORMAT dependent.
Source
# File lib/sequel/adapters/shared/mssql.rb 1055 def literal_false 1056 '0' 1057 end
Use 0 for false on MSSQL
Source
# File lib/sequel/adapters/shared/mssql.rb 1061 def literal_string_append(sql, v) 1062 sql << (mssql_unicode_strings ? "N'" : "'") 1063 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 1064 end
Optionally use unicode string syntax for all strings. Don’t double backslashes.
Source
# File lib/sequel/adapters/shared/mssql.rb 1067 def literal_true 1068 '1' 1069 end
Use 1 for true on MSSQL
Source
# File lib/sequel/adapters/shared/mssql.rb 1073 def multi_insert_sql_strategy 1074 is_2008_or_later? ? :values : :union 1075 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
Source
# File lib/sequel/adapters/shared/mssql.rb 1077 def non_sql_option?(key) 1078 super || key == :disable_insert_output || key == :mssql_unicode_strings 1079 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1178 def output_list_sql(sql, output) 1179 sql << " OUTPUT " 1180 column_list_append(sql, output[:select_list]) 1181 if into = output[:into] 1182 sql << " INTO " 1183 identifier_append(sql, into) 1184 if column_list = output[:column_list] 1185 sql << ' (' 1186 source_list_append(sql, column_list) 1187 sql << ')' 1188 end 1189 end 1190 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1192 def output_returning_sql(sql, type, values) 1193 sql << " OUTPUT " 1194 if values.empty? 1195 literal_append(sql, SQL::ColumnAll.new(type)) 1196 else 1197 values = values.map do |v| 1198 case v 1199 when SQL::AliasedExpression 1200 Sequel.qualify(type, v.expression).as(v.alias) 1201 else 1202 Sequel.qualify(type, v) 1203 end 1204 end 1205 column_list_append(sql, values) 1206 end 1207 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1169 def output_sql(sql, type) 1170 return unless supports_output_clause? 1171 if output = @opts[:output] 1172 output_list_sql(sql, output) 1173 elsif values = @opts[:returning] 1174 output_returning_sql(sql, type, values) 1175 end 1176 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1210 def requires_emulating_nulls_first? 1211 true 1212 end
MSSQL
does not natively support NULLS FIRST/LAST.
Source
# File lib/sequel/adapters/shared/mssql.rb 1081 def select_into_sql(sql) 1082 if i = @opts[:into] 1083 sql << " INTO " 1084 identifier_append(sql, i) 1085 end 1086 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1090 def select_limit_sql(sql) 1091 if l = @opts[:limit] 1092 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 1093 shared_limit_sql(sql, l) 1094 end 1095 end
MSSQL
2000 uses TOP N for limit. For MSSQL
2005+ TOP (N) is used to allow the limit to be a bound variable.
Source
# File lib/sequel/adapters/shared/mssql.rb 1124 def select_lock_sql(sql) 1125 lock = @opts[:lock] 1126 skip_locked = @opts[:skip_locked] 1127 nowait = @opts[:nowait] 1128 for_update = lock == :update 1129 dirty = lock == :dirty 1130 lock_hint = for_update || dirty 1131 1132 if lock_hint || skip_locked 1133 sql << " WITH (" 1134 1135 if lock_hint 1136 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 1137 end 1138 1139 if skip_locked || nowait 1140 sql << ', ' if lock_hint 1141 sql << (skip_locked ? "READPAST" : "NOWAIT") 1142 end 1143 1144 sql << ')' 1145 else 1146 super 1147 end 1148 end
Handle dirty, skip locked, and for update locking
Source
# File lib/sequel/adapters/shared/mssql.rb 1152 def select_order_sql(sql) 1153 super 1154 if is_2012_or_later? && @opts[:order] 1155 if o = @opts[:offset] 1156 sql << " OFFSET " 1157 literal_append(sql, o) 1158 sql << " ROWS" 1159 1160 if l = @opts[:limit] 1161 sql << " FETCH NEXT " 1162 literal_append(sql, l) 1163 sql << " ROWS ONLY" 1164 end 1165 end 1166 end 1167 end
On 2012+ when there is an order with an offset, append the offset (and possible limit) at the end of the order clause.
Source
# File lib/sequel/adapters/shared/mssql.rb 1216 def sqltime_precision 1217 6 1218 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
Source
Source
# File lib/sequel/adapters/shared/mssql.rb 1116 def update_limit_sql(sql) 1117 if l = @opts[:limit] 1118 shared_limit_sql(sql, l) 1119 end 1120 end
Source
# File lib/sequel/adapters/shared/mssql.rb 1228 def update_table_sql(sql) 1229 sql << ' ' 1230 source_list_append(sql, @opts[:from][0..0]) 1231 end
Only include the primary table in the main update clause
Source
# File lib/sequel/adapters/shared/mssql.rb 1233 def uses_with_rollup? 1234 !is_2008_or_later? 1235 end