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