module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 588 def complex_expression_sql_append(sql, op, args) 589 case op 590 when :'||' 591 super(sql, :+, args) 592 when :LIKE, :"NOT LIKE" 593 super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)")) 594 when :ILIKE, :"NOT ILIKE" 595 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)")) 596 when :<<, :>> 597 complex_expression_emulate_append(sql, op, args) 598 when :extract 599 part = args[0] 600 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 601 if part == :second 602 expr = args[1] 603 sql << "CAST((datepart(" << format.to_s << ', ' 604 literal_append(sql, expr) 605 sql << ') + datepart(ns, ' 606 literal_append(sql, expr) 607 sql << ")/1000000000.0) AS double precision)" 608 else 609 sql << "datepart(" << format.to_s << ', ' 610 literal_append(sql, args[1]) 611 sql << ')' 612 end 613 else 614 super 615 end 616 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
.
# File lib/sequel/adapters/shared/mssql.rb 631 def count(*a, &block) 632 if (@opts[:sql] && a.empty? && !block) 633 naked.to_a.length 634 else 635 super 636 end 637 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 640 def cross_apply(table) 641 join_table(:cross_apply, table) 642 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 645 def disable_insert_output 646 clone(:disable_insert_output=>true) 647 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?
# File lib/sequel/adapters/shared/mssql.rb 653 def empty? 654 if @opts[:sql] 655 naked.each{return false} 656 true 657 else 658 super 659 end 660 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 663 def escape_like(string) 664 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 665 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 668 def full_text_search(cols, terms, opts = OPTS) 669 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 670 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 671 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 676 def insert_select(*values) 677 return unless supports_insert_select? 678 with_sql_first(insert_select_sql(*values)) || false 679 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 683 def insert_select_sql(*values) 684 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 685 ds.insert_sql(*values) 686 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 689 def into(table) 690 clone(:into => table) 691 end
Use the database’s mssql_unicode_strings
setting if the dataset hasn’t overridden it.
# File lib/sequel/adapters/shared/mssql.rb 579 def mssql_unicode_strings 580 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 581 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 694 def nolock 695 lock_style(:dirty) 696 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 699 def outer_apply(table) 700 join_table(:outer_apply, table) 701 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 715 def output(into, values) 716 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 717 output = {} 718 case values 719 when Hash 720 output[:column_list], output[:select_list] = values.keys, values.values 721 when Array 722 output[:select_list] = values 723 end 724 output[:into] = into 725 clone(:output => output) 726 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 729 def quoted_identifier_append(sql, name) 730 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 731 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 734 def returning(*values) 735 values = values.map do |v| 736 unless r = unqualified_column_for(v) 737 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 738 end 739 r 740 end 741 clone(:returning=>values) 742 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 748 def select_sql 749 if @opts[:offset] 750 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 751 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 752 end 753 super 754 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 757 def server_version 758 db.server_version(@opts[:server]) 759 end
# File lib/sequel/adapters/shared/mssql.rb 761 def supports_cte?(type=:select) 762 is_2005_or_later? 763 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 766 def supports_group_cube? 767 is_2005_or_later? 768 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 771 def supports_group_rollup? 772 is_2005_or_later? 773 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 776 def supports_grouping_sets? 777 is_2008_or_later? 778 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 781 def supports_insert_select? 782 supports_output_clause? && !opts[:disable_insert_output] 783 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 786 def supports_intersect_except? 787 is_2005_or_later? 788 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 791 def supports_is_true? 792 false 793 end
MSSQL
doesn’t support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 796 def supports_join_using? 797 false 798 end
MSSQL
2008+ supports MERGE
# File lib/sequel/adapters/shared/mssql.rb 801 def supports_merge? 802 is_2008_or_later? 803 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 806 def supports_modifying_joins? 807 is_2005_or_later? 808 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 811 def supports_multiple_column_in? 812 false 813 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 816 def supports_nowait? 817 true 818 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 826 def supports_output_clause? 827 is_2005_or_later? 828 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 831 def supports_returning?(type) 832 supports_insert_select? 833 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 836 def supports_skip_locked? 837 true 838 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 846 def supports_where_true? 847 false 848 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 841 def supports_window_functions? 842 true 843 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 584 def with_mssql_unicode_strings(v) 585 clone(:mssql_unicode_strings=>v) 586 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 852 def with_ties 853 clone(:limit_with_ties=>true) 854 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 862 def _import(columns, values, opts=OPTS) 863 if opts[:return] == :primary_key && !@opts[:output] 864 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 865 elsif @opts[:output] 866 # no transaction: our multi_insert_sql_strategy should guarantee 867 # that there's only ever a single statement. 868 sql = multi_insert_sql(columns, values)[0] 869 naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v} 870 else 871 super 872 end 873 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 882 def compound_from_self 883 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 884 clone(:limit=>LIMIT_ALL).from_self 885 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 886 unordered 887 else 888 super 889 end 890 end
Private Instance Methods
Normalize conditions for MERGE WHEN.
# File lib/sequel/adapters/shared/mssql.rb 895 def _merge_when_conditions_sql(sql, data) 896 if data.has_key?(:conditions) 897 sql << " AND " 898 literal_append(sql, _normalize_merge_when_conditions(data[:conditions])) 899 end 900 end
MSSQL
requires a semicolon at the end of MERGE.
# File lib/sequel/adapters/shared/mssql.rb 918 def _merge_when_sql(sql) 919 super 920 sql << ';' 921 end
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
# File lib/sequel/adapters/shared/mssql.rb 904 def _normalize_merge_when_conditions(conditions) 905 case conditions 906 when nil, false 907 {1=>0} 908 when true 909 {1=>1} 910 when Sequel::SQL::DelayedEvaluation 911 Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))} 912 else 913 conditions 914 end 915 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 924 def aggregate_dataset 925 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 926 end
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 929 def check_not_limited!(type) 930 return if @opts[:skip_limit_check] && type != :truncate 931 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 932 super if type == :truncate || @opts[:offset] 933 end
Determine whether to add the COLLATE for LIKE arguments, based on the Database
setting.
# File lib/sequel/adapters/shared/mssql.rb 951 def complex_expression_sql_like_args(args, collation) 952 if db.like_without_collate 953 args 954 else 955 args.map{|a| Sequel.lit(["(", collation], a)} 956 end 957 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 962 def default_timestamp_format 963 "'%Y-%m-%dT%H:%M:%S.%3N'" 964 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 973 def delete_from2_sql(sql) 974 if joined_dataset? 975 select_from_sql(sql) 976 select_join_sql(sql) 977 end 978 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 967 def delete_from_sql(sql) 968 sql << ' FROM ' 969 source_list_append(sql, @opts[:from][0..0]) 970 end
# File lib/sequel/adapters/shared/mssql.rb 981 def delete_output_sql(sql) 982 output_sql(sql, :DELETED) 983 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 989 def emulate_function?(name) 990 name == :char_length || name == :trim 991 end
# File lib/sequel/adapters/shared/mssql.rb 993 def emulate_function_sql_append(sql, f) 994 case f.name 995 when :char_length 996 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 997 when :trim 998 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 999 end 1000 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 1003 def emulate_offset_with_row_number? 1004 super && !(is_2012_or_later? && @opts[:order]) 1005 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 1009 def first_primary_key 1010 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 1011 end
# File lib/sequel/adapters/shared/mssql.rb 1013 def insert_output_sql(sql) 1014 output_sql(sql, :INSERTED) 1015 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 936 def is_2005_or_later? 937 server_version >= 9000000 938 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 941 def is_2008_or_later? 942 server_version >= 10000000 943 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 946 def is_2012_or_later? 947 server_version >= 11000000 948 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 1019 def join_type_sql(join_type) 1020 case join_type 1021 when :cross_apply 1022 'CROSS APPLY' 1023 when :outer_apply 1024 'OUTER APPLY' 1025 else 1026 super 1027 end 1028 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 1031 def literal_blob_append(sql, v) 1032 sql << '0x' << v.unpack("H*").first 1033 end
Use YYYYmmdd format, since that’s the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 1037 def literal_date(v) 1038 v.strftime("'%Y%m%d'") 1039 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 1042 def literal_false 1043 '0' 1044 end
Optionally use unicode string syntax for all strings. Don’t double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 1048 def literal_string_append(sql, v) 1049 sql << (mssql_unicode_strings ? "N'" : "'") 1050 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 1051 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 1054 def literal_true 1055 '1' 1056 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 1060 def multi_insert_sql_strategy 1061 is_2008_or_later? ? :values : :union 1062 end
# File lib/sequel/adapters/shared/mssql.rb 1064 def non_sql_option?(key) 1065 super || key == :disable_insert_output || key == :mssql_unicode_strings 1066 end
# File lib/sequel/adapters/shared/mssql.rb 1165 def output_list_sql(sql, output) 1166 sql << " OUTPUT " 1167 column_list_append(sql, output[:select_list]) 1168 if into = output[:into] 1169 sql << " INTO " 1170 identifier_append(sql, into) 1171 if column_list = output[:column_list] 1172 sql << ' (' 1173 source_list_append(sql, column_list) 1174 sql << ')' 1175 end 1176 end 1177 end
# File lib/sequel/adapters/shared/mssql.rb 1179 def output_returning_sql(sql, type, values) 1180 sql << " OUTPUT " 1181 if values.empty? 1182 literal_append(sql, SQL::ColumnAll.new(type)) 1183 else 1184 values = values.map do |v| 1185 case v 1186 when SQL::AliasedExpression 1187 Sequel.qualify(type, v.expression).as(v.alias) 1188 else 1189 Sequel.qualify(type, v) 1190 end 1191 end 1192 column_list_append(sql, values) 1193 end 1194 end
# File lib/sequel/adapters/shared/mssql.rb 1156 def output_sql(sql, type) 1157 return unless supports_output_clause? 1158 if output = @opts[:output] 1159 output_list_sql(sql, output) 1160 elsif values = @opts[:returning] 1161 output_returning_sql(sql, type, values) 1162 end 1163 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1197 def requires_emulating_nulls_first? 1198 true 1199 end
# File lib/sequel/adapters/shared/mssql.rb 1068 def select_into_sql(sql) 1069 if i = @opts[:into] 1070 sql << " INTO " 1071 identifier_append(sql, i) 1072 end 1073 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 1077 def select_limit_sql(sql) 1078 if l = @opts[:limit] 1079 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 1080 shared_limit_sql(sql, l) 1081 end 1082 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 1111 def select_lock_sql(sql) 1112 lock = @opts[:lock] 1113 skip_locked = @opts[:skip_locked] 1114 nowait = @opts[:nowait] 1115 for_update = lock == :update 1116 dirty = lock == :dirty 1117 lock_hint = for_update || dirty 1118 1119 if lock_hint || skip_locked 1120 sql << " WITH (" 1121 1122 if lock_hint 1123 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 1124 end 1125 1126 if skip_locked || nowait 1127 sql << ', ' if lock_hint 1128 sql << (skip_locked ? "READPAST" : "NOWAIT") 1129 end 1130 1131 sql << ')' 1132 else 1133 super 1134 end 1135 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 1139 def select_order_sql(sql) 1140 super 1141 if is_2012_or_later? && @opts[:order] 1142 if o = @opts[:offset] 1143 sql << " OFFSET " 1144 literal_append(sql, o) 1145 sql << " ROWS" 1146 1147 if l = @opts[:limit] 1148 sql << " FETCH NEXT " 1149 literal_append(sql, l) 1150 sql << " ROWS ONLY" 1151 end 1152 end 1153 end 1154 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1203 def sqltime_precision 1204 6 1205 end
# File lib/sequel/adapters/shared/mssql.rb 1103 def update_limit_sql(sql) 1104 if l = @opts[:limit] 1105 shared_limit_sql(sql, l) 1106 end 1107 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1215 def update_table_sql(sql) 1216 sql << ' ' 1217 source_list_append(sql, @opts[:from][0..0]) 1218 end
# File lib/sequel/adapters/shared/mssql.rb 1220 def uses_with_rollup? 1221 !is_2008_or_later? 1222 end