module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 555 def complex_expression_sql_append(sql, op, args) 556 case op 557 when :'||' 558 super(sql, :+, args) 559 when :LIKE, :"NOT LIKE" 560 super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)")) 561 when :ILIKE, :"NOT ILIKE" 562 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)")) 563 when :<<, :>> 564 complex_expression_emulate_append(sql, op, args) 565 when :extract 566 part = args[0] 567 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 568 if part == :second 569 expr = args[1] 570 sql << "CAST((datepart(" << format.to_s << ', ' 571 literal_append(sql, expr) 572 sql << ') + datepart(ns, ' 573 literal_append(sql, expr) 574 sql << ")/1000000000.0) AS double precision)" 575 else 576 sql << "datepart(" << format.to_s << ', ' 577 literal_append(sql, args[1]) 578 sql << ')' 579 end 580 else 581 super 582 end 583 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 595 def cross_apply(table) 596 join_table(:cross_apply, table) 597 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 600 def disable_insert_output 601 clone(:disable_insert_output=>true) 602 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 605 def escape_like(string) 606 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 607 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 610 def full_text_search(cols, terms, opts = OPTS) 611 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 612 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 613 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 618 def insert_select(*values) 619 return unless supports_insert_select? 620 with_sql_first(insert_select_sql(*values)) || false 621 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 625 def insert_select_sql(*values) 626 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 627 ds.insert_sql(*values) 628 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 631 def into(table) 632 clone(:into => table) 633 end
Use the database's mssql_unicode_strings
setting if the dataset hasn't overridden it.
# File lib/sequel/adapters/shared/mssql.rb 546 def mssql_unicode_strings 547 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 548 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 636 def nolock 637 lock_style(:dirty) 638 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 641 def outer_apply(table) 642 join_table(:outer_apply, table) 643 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 657 def output(into, values) 658 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 659 output = {} 660 case values 661 when Hash 662 output[:column_list], output[:select_list] = values.keys, values.values 663 when Array 664 output[:select_list] = values 665 end 666 output[:into] = into 667 clone(:output => output) 668 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 671 def quoted_identifier_append(sql, name) 672 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 673 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 676 def returning(*values) 677 values = values.map do |v| 678 unless r = unqualified_column_for(v) 679 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 680 end 681 r 682 end 683 clone(:returning=>values) 684 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 690 def select_sql 691 if @opts[:offset] 692 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 693 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 694 end 695 super 696 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 699 def server_version 700 db.server_version(@opts[:server]) 701 end
# File lib/sequel/adapters/shared/mssql.rb 703 def supports_cte?(type=:select) 704 is_2005_or_later? 705 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 708 def supports_group_cube? 709 is_2005_or_later? 710 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 713 def supports_group_rollup? 714 is_2005_or_later? 715 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 718 def supports_grouping_sets? 719 is_2008_or_later? 720 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 723 def supports_insert_select? 724 supports_output_clause? && !opts[:disable_insert_output] 725 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 728 def supports_intersect_except? 729 is_2005_or_later? 730 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 733 def supports_is_true? 734 false 735 end
MSSQL
doesn't support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 738 def supports_join_using? 739 false 740 end
MSSQL
2008+ supports MERGE
# File lib/sequel/adapters/shared/mssql.rb 743 def supports_merge? 744 is_2008_or_later? 745 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 748 def supports_modifying_joins? 749 is_2005_or_later? 750 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 753 def supports_multiple_column_in? 754 false 755 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 758 def supports_nowait? 759 true 760 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 768 def supports_output_clause? 769 is_2005_or_later? 770 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 773 def supports_returning?(type) 774 supports_insert_select? 775 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 778 def supports_skip_locked? 779 true 780 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 788 def supports_where_true? 789 false 790 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 783 def supports_window_functions? 784 true 785 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 551 def with_mssql_unicode_strings(v) 552 clone(:mssql_unicode_strings=>v) 553 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 794 def with_ties 795 clone(:limit_with_ties=>true) 796 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 804 def _import(columns, values, opts=OPTS) 805 if opts[:return] == :primary_key && !@opts[:output] 806 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 807 elsif @opts[:output] 808 # no transaction: our multi_insert_sql_strategy should guarantee 809 # that there's only ever a single statement. 810 sql = multi_insert_sql(columns, values)[0] 811 naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v} 812 else 813 super 814 end 815 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 824 def compound_from_self 825 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 826 clone(:limit=>LIMIT_ALL).from_self 827 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 828 unordered 829 else 830 super 831 end 832 end
Private Instance Methods
Normalize conditions for MERGE WHEN.
# File lib/sequel/adapters/shared/mssql.rb 837 def _merge_when_conditions_sql(sql, data) 838 if data.has_key?(:conditions) 839 sql << " AND " 840 literal_append(sql, _normalize_merge_when_conditions(data[:conditions])) 841 end 842 end
MSSQL
requires a semicolon at the end of MERGE.
# File lib/sequel/adapters/shared/mssql.rb 860 def _merge_when_sql(sql) 861 super 862 sql << ';' 863 end
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
# File lib/sequel/adapters/shared/mssql.rb 846 def _normalize_merge_when_conditions(conditions) 847 case conditions 848 when nil, false 849 {1=>0} 850 when true 851 {1=>1} 852 when Sequel::SQL::DelayedEvaluation 853 Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))} 854 else 855 conditions 856 end 857 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 866 def aggregate_dataset 867 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 868 end
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 871 def check_not_limited!(type) 872 return if @opts[:skip_limit_check] && type != :truncate 873 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 874 super if type == :truncate || @opts[:offset] 875 end
Determine whether to add the COLLATE for LIKE arguments, based on the Database
setting.
# File lib/sequel/adapters/shared/mssql.rb 893 def complex_expression_sql_like_args(args, collation) 894 if db.like_without_collate 895 args 896 else 897 args.map{|a| Sequel.lit(["(", collation], a)} 898 end 899 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 904 def default_timestamp_format 905 "'%Y-%m-%dT%H:%M:%S%N%z'" 906 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 915 def delete_from2_sql(sql) 916 if joined_dataset? 917 select_from_sql(sql) 918 select_join_sql(sql) 919 end 920 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 909 def delete_from_sql(sql) 910 sql << ' FROM ' 911 source_list_append(sql, @opts[:from][0..0]) 912 end
# File lib/sequel/adapters/shared/mssql.rb 923 def delete_output_sql(sql) 924 output_sql(sql, :DELETED) 925 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 931 def emulate_function?(name) 932 name == :char_length || name == :trim 933 end
# File lib/sequel/adapters/shared/mssql.rb 935 def emulate_function_sql_append(sql, f) 936 case f.name 937 when :char_length 938 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 939 when :trim 940 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 941 end 942 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 945 def emulate_offset_with_row_number? 946 super && !(is_2012_or_later? && @opts[:order]) 947 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 951 def first_primary_key 952 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 953 end
# File lib/sequel/adapters/shared/mssql.rb 955 def insert_output_sql(sql) 956 output_sql(sql, :INSERTED) 957 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 878 def is_2005_or_later? 879 server_version >= 9000000 880 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 883 def is_2008_or_later? 884 server_version >= 10000000 885 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 888 def is_2012_or_later? 889 server_version >= 11000000 890 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 961 def join_type_sql(join_type) 962 case join_type 963 when :cross_apply 964 'CROSS APPLY' 965 when :outer_apply 966 'OUTER APPLY' 967 else 968 super 969 end 970 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 973 def literal_blob_append(sql, v) 974 sql << '0x' << v.unpack("H*").first 975 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 979 def literal_date(v) 980 v.strftime("'%Y%m%d'") 981 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 984 def literal_false 985 '0' 986 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 990 def literal_string_append(sql, v) 991 sql << (mssql_unicode_strings ? "N'" : "'") 992 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 993 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 996 def literal_true 997 '1' 998 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 1002 def multi_insert_sql_strategy 1003 is_2008_or_later? ? :values : :union 1004 end
# File lib/sequel/adapters/shared/mssql.rb 1006 def non_sql_option?(key) 1007 super || key == :disable_insert_output || key == :mssql_unicode_strings 1008 end
# File lib/sequel/adapters/shared/mssql.rb 1107 def output_list_sql(sql, output) 1108 sql << " OUTPUT " 1109 column_list_append(sql, output[:select_list]) 1110 if into = output[:into] 1111 sql << " INTO " 1112 identifier_append(sql, into) 1113 if column_list = output[:column_list] 1114 sql << ' (' 1115 source_list_append(sql, column_list) 1116 sql << ')' 1117 end 1118 end 1119 end
# File lib/sequel/adapters/shared/mssql.rb 1121 def output_returning_sql(sql, type, values) 1122 sql << " OUTPUT " 1123 if values.empty? 1124 literal_append(sql, SQL::ColumnAll.new(type)) 1125 else 1126 values = values.map do |v| 1127 case v 1128 when SQL::AliasedExpression 1129 Sequel.qualify(type, v.expression).as(v.alias) 1130 else 1131 Sequel.qualify(type, v) 1132 end 1133 end 1134 column_list_append(sql, values) 1135 end 1136 end
# File lib/sequel/adapters/shared/mssql.rb 1098 def output_sql(sql, type) 1099 return unless supports_output_clause? 1100 if output = @opts[:output] 1101 output_list_sql(sql, output) 1102 elsif values = @opts[:returning] 1103 output_returning_sql(sql, type, values) 1104 end 1105 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1139 def requires_emulating_nulls_first? 1140 true 1141 end
# File lib/sequel/adapters/shared/mssql.rb 1010 def select_into_sql(sql) 1011 if i = @opts[:into] 1012 sql << " INTO " 1013 identifier_append(sql, i) 1014 end 1015 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 1019 def select_limit_sql(sql) 1020 if l = @opts[:limit] 1021 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 1022 shared_limit_sql(sql, l) 1023 end 1024 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 1053 def select_lock_sql(sql) 1054 lock = @opts[:lock] 1055 skip_locked = @opts[:skip_locked] 1056 nowait = @opts[:nowait] 1057 for_update = lock == :update 1058 dirty = lock == :dirty 1059 lock_hint = for_update || dirty 1060 1061 if lock_hint || skip_locked 1062 sql << " WITH (" 1063 1064 if lock_hint 1065 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 1066 end 1067 1068 if skip_locked || nowait 1069 sql << ', ' if lock_hint 1070 sql << (skip_locked ? "READPAST" : "NOWAIT") 1071 end 1072 1073 sql << ')' 1074 else 1075 super 1076 end 1077 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 1081 def select_order_sql(sql) 1082 super 1083 if is_2012_or_later? && @opts[:order] 1084 if o = @opts[:offset] 1085 sql << " OFFSET " 1086 literal_append(sql, o) 1087 sql << " ROWS" 1088 1089 if l = @opts[:limit] 1090 sql << " FETCH NEXT " 1091 literal_append(sql, l) 1092 sql << " ROWS ONLY" 1093 end 1094 end 1095 end 1096 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1145 def sqltime_precision 1146 6 1147 end
# File lib/sequel/adapters/shared/mssql.rb 1045 def update_limit_sql(sql) 1046 if l = @opts[:limit] 1047 shared_limit_sql(sql, l) 1048 end 1049 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1157 def update_table_sql(sql) 1158 sql << ' ' 1159 source_list_append(sql, @opts[:from][0..0]) 1160 end
# File lib/sequel/adapters/shared/mssql.rb 1162 def uses_with_rollup? 1163 !is_2008_or_later? 1164 end