module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 560 def complex_expression_sql_append(sql, op, args) 561 case op 562 when :'||' 563 super(sql, :+, args) 564 when :LIKE, :"NOT LIKE" 565 super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)")) 566 when :ILIKE, :"NOT ILIKE" 567 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)")) 568 when :<<, :>> 569 complex_expression_emulate_append(sql, op, args) 570 when :extract 571 part = args[0] 572 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 573 if part == :second 574 expr = args[1] 575 sql << "CAST((datepart(" << format.to_s << ', ' 576 literal_append(sql, expr) 577 sql << ') + datepart(ns, ' 578 literal_append(sql, expr) 579 sql << ")/1000000000.0) AS double precision)" 580 else 581 sql << "datepart(" << format.to_s << ', ' 582 literal_append(sql, args[1]) 583 sql << ')' 584 end 585 else 586 super 587 end 588 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 603 def count(*a, &block) 604 if (@opts[:sql] && a.empty? && !block) 605 naked.to_a.length 606 else 607 super 608 end 609 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 612 def cross_apply(table) 613 join_table(:cross_apply, table) 614 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 617 def disable_insert_output 618 clone(:disable_insert_output=>true) 619 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 625 def empty? 626 if @opts[:sql] 627 naked.each{return false} 628 true 629 else 630 super 631 end 632 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 635 def escape_like(string) 636 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 637 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 640 def full_text_search(cols, terms, opts = OPTS) 641 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 642 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 643 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 648 def insert_select(*values) 649 return unless supports_insert_select? 650 with_sql_first(insert_select_sql(*values)) || false 651 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 655 def insert_select_sql(*values) 656 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 657 ds.insert_sql(*values) 658 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 661 def into(table) 662 clone(:into => table) 663 end
Use the database's mssql_unicode_strings
setting if the dataset hasn't overridden it.
# File lib/sequel/adapters/shared/mssql.rb 551 def mssql_unicode_strings 552 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 553 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 666 def nolock 667 lock_style(:dirty) 668 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 671 def outer_apply(table) 672 join_table(:outer_apply, table) 673 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 687 def output(into, values) 688 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 689 output = {} 690 case values 691 when Hash 692 output[:column_list], output[:select_list] = values.keys, values.values 693 when Array 694 output[:select_list] = values 695 end 696 output[:into] = into 697 clone(:output => output) 698 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 701 def quoted_identifier_append(sql, name) 702 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 703 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 706 def returning(*values) 707 values = values.map do |v| 708 unless r = unqualified_column_for(v) 709 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 710 end 711 r 712 end 713 clone(:returning=>values) 714 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 720 def select_sql 721 if @opts[:offset] 722 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 723 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 724 end 725 super 726 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 729 def server_version 730 db.server_version(@opts[:server]) 731 end
# File lib/sequel/adapters/shared/mssql.rb 733 def supports_cte?(type=:select) 734 is_2005_or_later? 735 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 738 def supports_group_cube? 739 is_2005_or_later? 740 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 743 def supports_group_rollup? 744 is_2005_or_later? 745 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 748 def supports_grouping_sets? 749 is_2008_or_later? 750 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 753 def supports_insert_select? 754 supports_output_clause? && !opts[:disable_insert_output] 755 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 758 def supports_intersect_except? 759 is_2005_or_later? 760 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 763 def supports_is_true? 764 false 765 end
MSSQL
doesn't support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 768 def supports_join_using? 769 false 770 end
MSSQL
2008+ supports MERGE
# File lib/sequel/adapters/shared/mssql.rb 773 def supports_merge? 774 is_2008_or_later? 775 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 778 def supports_modifying_joins? 779 is_2005_or_later? 780 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 783 def supports_multiple_column_in? 784 false 785 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 788 def supports_nowait? 789 true 790 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 798 def supports_output_clause? 799 is_2005_or_later? 800 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 803 def supports_returning?(type) 804 supports_insert_select? 805 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 808 def supports_skip_locked? 809 true 810 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 818 def supports_where_true? 819 false 820 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 813 def supports_window_functions? 814 true 815 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 556 def with_mssql_unicode_strings(v) 557 clone(:mssql_unicode_strings=>v) 558 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 824 def with_ties 825 clone(:limit_with_ties=>true) 826 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 834 def _import(columns, values, opts=OPTS) 835 if opts[:return] == :primary_key && !@opts[:output] 836 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 837 elsif @opts[:output] 838 # no transaction: our multi_insert_sql_strategy should guarantee 839 # that there's only ever a single statement. 840 sql = multi_insert_sql(columns, values)[0] 841 naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v} 842 else 843 super 844 end 845 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 854 def compound_from_self 855 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 856 clone(:limit=>LIMIT_ALL).from_self 857 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 858 unordered 859 else 860 super 861 end 862 end
Private Instance Methods
Normalize conditions for MERGE WHEN.
# File lib/sequel/adapters/shared/mssql.rb 867 def _merge_when_conditions_sql(sql, data) 868 if data.has_key?(:conditions) 869 sql << " AND " 870 literal_append(sql, _normalize_merge_when_conditions(data[:conditions])) 871 end 872 end
MSSQL
requires a semicolon at the end of MERGE.
# File lib/sequel/adapters/shared/mssql.rb 890 def _merge_when_sql(sql) 891 super 892 sql << ';' 893 end
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
# File lib/sequel/adapters/shared/mssql.rb 876 def _normalize_merge_when_conditions(conditions) 877 case conditions 878 when nil, false 879 {1=>0} 880 when true 881 {1=>1} 882 when Sequel::SQL::DelayedEvaluation 883 Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))} 884 else 885 conditions 886 end 887 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 896 def aggregate_dataset 897 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 898 end
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 901 def check_not_limited!(type) 902 return if @opts[:skip_limit_check] && type != :truncate 903 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 904 super if type == :truncate || @opts[:offset] 905 end
Determine whether to add the COLLATE for LIKE arguments, based on the Database
setting.
# File lib/sequel/adapters/shared/mssql.rb 923 def complex_expression_sql_like_args(args, collation) 924 if db.like_without_collate 925 args 926 else 927 args.map{|a| Sequel.lit(["(", collation], a)} 928 end 929 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 934 def default_timestamp_format 935 "'%Y-%m-%dT%H:%M:%S.%3N'" 936 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 945 def delete_from2_sql(sql) 946 if joined_dataset? 947 select_from_sql(sql) 948 select_join_sql(sql) 949 end 950 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 939 def delete_from_sql(sql) 940 sql << ' FROM ' 941 source_list_append(sql, @opts[:from][0..0]) 942 end
# File lib/sequel/adapters/shared/mssql.rb 953 def delete_output_sql(sql) 954 output_sql(sql, :DELETED) 955 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 961 def emulate_function?(name) 962 name == :char_length || name == :trim 963 end
# File lib/sequel/adapters/shared/mssql.rb 965 def emulate_function_sql_append(sql, f) 966 case f.name 967 when :char_length 968 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 969 when :trim 970 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 971 end 972 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 975 def emulate_offset_with_row_number? 976 super && !(is_2012_or_later? && @opts[:order]) 977 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 981 def first_primary_key 982 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 983 end
# File lib/sequel/adapters/shared/mssql.rb 985 def insert_output_sql(sql) 986 output_sql(sql, :INSERTED) 987 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 908 def is_2005_or_later? 909 server_version >= 9000000 910 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 913 def is_2008_or_later? 914 server_version >= 10000000 915 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 918 def is_2012_or_later? 919 server_version >= 11000000 920 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 991 def join_type_sql(join_type) 992 case join_type 993 when :cross_apply 994 'CROSS APPLY' 995 when :outer_apply 996 'OUTER APPLY' 997 else 998 super 999 end 1000 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 1003 def literal_blob_append(sql, v) 1004 sql << '0x' << v.unpack("H*").first 1005 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 1009 def literal_date(v) 1010 v.strftime("'%Y%m%d'") 1011 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 1014 def literal_false 1015 '0' 1016 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 1020 def literal_string_append(sql, v) 1021 sql << (mssql_unicode_strings ? "N'" : "'") 1022 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 1023 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 1026 def literal_true 1027 '1' 1028 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 1032 def multi_insert_sql_strategy 1033 is_2008_or_later? ? :values : :union 1034 end
# File lib/sequel/adapters/shared/mssql.rb 1036 def non_sql_option?(key) 1037 super || key == :disable_insert_output || key == :mssql_unicode_strings 1038 end
# File lib/sequel/adapters/shared/mssql.rb 1137 def output_list_sql(sql, output) 1138 sql << " OUTPUT " 1139 column_list_append(sql, output[:select_list]) 1140 if into = output[:into] 1141 sql << " INTO " 1142 identifier_append(sql, into) 1143 if column_list = output[:column_list] 1144 sql << ' (' 1145 source_list_append(sql, column_list) 1146 sql << ')' 1147 end 1148 end 1149 end
# File lib/sequel/adapters/shared/mssql.rb 1151 def output_returning_sql(sql, type, values) 1152 sql << " OUTPUT " 1153 if values.empty? 1154 literal_append(sql, SQL::ColumnAll.new(type)) 1155 else 1156 values = values.map do |v| 1157 case v 1158 when SQL::AliasedExpression 1159 Sequel.qualify(type, v.expression).as(v.alias) 1160 else 1161 Sequel.qualify(type, v) 1162 end 1163 end 1164 column_list_append(sql, values) 1165 end 1166 end
# File lib/sequel/adapters/shared/mssql.rb 1128 def output_sql(sql, type) 1129 return unless supports_output_clause? 1130 if output = @opts[:output] 1131 output_list_sql(sql, output) 1132 elsif values = @opts[:returning] 1133 output_returning_sql(sql, type, values) 1134 end 1135 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1169 def requires_emulating_nulls_first? 1170 true 1171 end
# File lib/sequel/adapters/shared/mssql.rb 1040 def select_into_sql(sql) 1041 if i = @opts[:into] 1042 sql << " INTO " 1043 identifier_append(sql, i) 1044 end 1045 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 1049 def select_limit_sql(sql) 1050 if l = @opts[:limit] 1051 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 1052 shared_limit_sql(sql, l) 1053 end 1054 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 1083 def select_lock_sql(sql) 1084 lock = @opts[:lock] 1085 skip_locked = @opts[:skip_locked] 1086 nowait = @opts[:nowait] 1087 for_update = lock == :update 1088 dirty = lock == :dirty 1089 lock_hint = for_update || dirty 1090 1091 if lock_hint || skip_locked 1092 sql << " WITH (" 1093 1094 if lock_hint 1095 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 1096 end 1097 1098 if skip_locked || nowait 1099 sql << ', ' if lock_hint 1100 sql << (skip_locked ? "READPAST" : "NOWAIT") 1101 end 1102 1103 sql << ')' 1104 else 1105 super 1106 end 1107 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 1111 def select_order_sql(sql) 1112 super 1113 if is_2012_or_later? && @opts[:order] 1114 if o = @opts[:offset] 1115 sql << " OFFSET " 1116 literal_append(sql, o) 1117 sql << " ROWS" 1118 1119 if l = @opts[:limit] 1120 sql << " FETCH NEXT " 1121 literal_append(sql, l) 1122 sql << " ROWS ONLY" 1123 end 1124 end 1125 end 1126 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1175 def sqltime_precision 1176 6 1177 end
# File lib/sequel/adapters/shared/mssql.rb 1075 def update_limit_sql(sql) 1076 if l = @opts[:limit] 1077 shared_limit_sql(sql, l) 1078 end 1079 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1187 def update_table_sql(sql) 1188 sql << ' ' 1189 source_list_append(sql, @opts[:from][0..0]) 1190 end
# File lib/sequel/adapters/shared/mssql.rb 1192 def uses_with_rollup? 1193 !is_2008_or_later? 1194 end