module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 546 def complex_expression_sql_append(sql, op, args) 547 case op 548 when :'||' 549 super(sql, :+, args) 550 when :LIKE, :"NOT LIKE" 551 super(sql, op, args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CS_AS)"], a)}) 552 when :ILIKE, :"NOT ILIKE" 553 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CI_AS)"], a)}) 554 when :<<, :>> 555 complex_expression_emulate_append(sql, op, args) 556 when :extract 557 part = args[0] 558 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 559 if part == :second 560 expr = args[1] 561 sql << "CAST((datepart(" << format.to_s << ', ' 562 literal_append(sql, expr) 563 sql << ') + datepart(ns, ' 564 literal_append(sql, expr) 565 sql << ")/1000000000.0) AS double precision)" 566 else 567 sql << "datepart(" << format.to_s << ', ' 568 literal_append(sql, args[1]) 569 sql << ')' 570 end 571 else 572 super 573 end 574 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 586 def cross_apply(table) 587 join_table(:cross_apply, table) 588 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 591 def disable_insert_output 592 clone(:disable_insert_output=>true) 593 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 596 def escape_like(string) 597 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 598 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 601 def full_text_search(cols, terms, opts = OPTS) 602 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 603 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 604 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 609 def insert_select(*values) 610 return unless supports_insert_select? 611 with_sql_first(insert_select_sql(*values)) || false 612 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 616 def insert_select_sql(*values) 617 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 618 ds.insert_sql(*values) 619 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 622 def into(table) 623 clone(:into => table) 624 end
Use the database's mssql_unicode_strings
setting if the dataset hasn't overridden it.
# File lib/sequel/adapters/shared/mssql.rb 537 def mssql_unicode_strings 538 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 539 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 627 def nolock 628 lock_style(:dirty) 629 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 632 def outer_apply(table) 633 join_table(:outer_apply, table) 634 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 648 def output(into, values) 649 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 650 output = {} 651 case values 652 when Hash 653 output[:column_list], output[:select_list] = values.keys, values.values 654 when Array 655 output[:select_list] = values 656 end 657 output[:into] = into 658 clone(:output => output) 659 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 662 def quoted_identifier_append(sql, name) 663 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 664 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 667 def returning(*values) 668 values = values.map do |v| 669 unless r = unqualified_column_for(v) 670 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 671 end 672 r 673 end 674 clone(:returning=>values) 675 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 681 def select_sql 682 if @opts[:offset] 683 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 684 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 685 end 686 super 687 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 690 def server_version 691 db.server_version(@opts[:server]) 692 end
# File lib/sequel/adapters/shared/mssql.rb 694 def supports_cte?(type=:select) 695 is_2005_or_later? 696 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 699 def supports_group_cube? 700 is_2005_or_later? 701 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 704 def supports_group_rollup? 705 is_2005_or_later? 706 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 709 def supports_grouping_sets? 710 is_2008_or_later? 711 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 714 def supports_insert_select? 715 supports_output_clause? && !opts[:disable_insert_output] 716 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 719 def supports_intersect_except? 720 is_2005_or_later? 721 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 724 def supports_is_true? 725 false 726 end
MSSQL
doesn't support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 729 def supports_join_using? 730 false 731 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 734 def supports_modifying_joins? 735 is_2005_or_later? 736 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 739 def supports_multiple_column_in? 740 false 741 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 744 def supports_nowait? 745 true 746 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 754 def supports_output_clause? 755 is_2005_or_later? 756 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 759 def supports_returning?(type) 760 supports_insert_select? 761 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 764 def supports_skip_locked? 765 true 766 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 774 def supports_where_true? 775 false 776 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 769 def supports_window_functions? 770 true 771 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 542 def with_mssql_unicode_strings(v) 543 clone(:mssql_unicode_strings=>v) 544 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 780 def with_ties 781 clone(:limit_with_ties=>true) 782 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 790 def _import(columns, values, opts=OPTS) 791 if opts[:return] == :primary_key && !@opts[:output] 792 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 793 elsif @opts[:output] 794 statements = multi_insert_sql(columns, values) 795 ds = naked 796 @db.transaction(opts.merge(:server=>@opts[:server])) do 797 statements.map{|st| ds.with_sql(st)} 798 end.first.map{|v| v.length == 1 ? v.values.first : v} 799 else 800 super 801 end 802 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 811 def compound_from_self 812 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 813 clone(:limit=>LIMIT_ALL).from_self 814 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 815 unordered 816 else 817 super 818 end 819 end
Private Instance Methods
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 824 def aggregate_dataset 825 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 826 end
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 829 def check_not_limited!(type) 830 return if @opts[:skip_limit_check] && type != :truncate 831 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 832 super if type == :truncate || @opts[:offset] 833 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 853 def default_timestamp_format 854 "'%Y-%m-%dT%H:%M:%S%N%z'" 855 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 864 def delete_from2_sql(sql) 865 if joined_dataset? 866 select_from_sql(sql) 867 select_join_sql(sql) 868 end 869 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 858 def delete_from_sql(sql) 859 sql << ' FROM ' 860 source_list_append(sql, @opts[:from][0..0]) 861 end
# File lib/sequel/adapters/shared/mssql.rb 872 def delete_output_sql(sql) 873 output_sql(sql, :DELETED) 874 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 880 def emulate_function?(name) 881 name == :char_length || name == :trim 882 end
# File lib/sequel/adapters/shared/mssql.rb 884 def emulate_function_sql_append(sql, f) 885 case f.name 886 when :char_length 887 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 888 when :trim 889 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 890 end 891 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 894 def emulate_offset_with_row_number? 895 super && !(is_2012_or_later? && @opts[:order]) 896 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 900 def first_primary_key 901 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 902 end
# File lib/sequel/adapters/shared/mssql.rb 904 def insert_output_sql(sql) 905 output_sql(sql, :INSERTED) 906 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 836 def is_2005_or_later? 837 server_version >= 9000000 838 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 841 def is_2008_or_later? 842 server_version >= 10000000 843 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 846 def is_2012_or_later? 847 server_version >= 11000000 848 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 910 def join_type_sql(join_type) 911 case join_type 912 when :cross_apply 913 'CROSS APPLY' 914 when :outer_apply 915 'OUTER APPLY' 916 else 917 super 918 end 919 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 922 def literal_blob_append(sql, v) 923 sql << '0x' << v.unpack("H*").first 924 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 928 def literal_date(v) 929 v.strftime("'%Y%m%d'") 930 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 933 def literal_false 934 '0' 935 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 939 def literal_string_append(sql, v) 940 sql << (mssql_unicode_strings ? "N'" : "'") 941 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 942 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 945 def literal_true 946 '1' 947 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 951 def multi_insert_sql_strategy 952 is_2008_or_later? ? :values : :union 953 end
# File lib/sequel/adapters/shared/mssql.rb 955 def non_sql_option?(key) 956 super || key == :disable_insert_output || key == :mssql_unicode_strings 957 end
# File lib/sequel/adapters/shared/mssql.rb 1056 def output_list_sql(sql, output) 1057 sql << " OUTPUT " 1058 column_list_append(sql, output[:select_list]) 1059 if into = output[:into] 1060 sql << " INTO " 1061 identifier_append(sql, into) 1062 if column_list = output[:column_list] 1063 sql << ' (' 1064 source_list_append(sql, column_list) 1065 sql << ')' 1066 end 1067 end 1068 end
# File lib/sequel/adapters/shared/mssql.rb 1070 def output_returning_sql(sql, type, values) 1071 sql << " OUTPUT " 1072 if values.empty? 1073 literal_append(sql, SQL::ColumnAll.new(type)) 1074 else 1075 values = values.map do |v| 1076 case v 1077 when SQL::AliasedExpression 1078 Sequel.qualify(type, v.expression).as(v.alias) 1079 else 1080 Sequel.qualify(type, v) 1081 end 1082 end 1083 column_list_append(sql, values) 1084 end 1085 end
# File lib/sequel/adapters/shared/mssql.rb 1047 def output_sql(sql, type) 1048 return unless supports_output_clause? 1049 if output = @opts[:output] 1050 output_list_sql(sql, output) 1051 elsif values = @opts[:returning] 1052 output_returning_sql(sql, type, values) 1053 end 1054 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1088 def requires_emulating_nulls_first? 1089 true 1090 end
# File lib/sequel/adapters/shared/mssql.rb 959 def select_into_sql(sql) 960 if i = @opts[:into] 961 sql << " INTO " 962 identifier_append(sql, i) 963 end 964 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 968 def select_limit_sql(sql) 969 if l = @opts[:limit] 970 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 971 shared_limit_sql(sql, l) 972 end 973 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 1002 def select_lock_sql(sql) 1003 lock = @opts[:lock] 1004 skip_locked = @opts[:skip_locked] 1005 nowait = @opts[:nowait] 1006 for_update = lock == :update 1007 dirty = lock == :dirty 1008 lock_hint = for_update || dirty 1009 1010 if lock_hint || skip_locked 1011 sql << " WITH (" 1012 1013 if lock_hint 1014 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 1015 end 1016 1017 if skip_locked || nowait 1018 sql << ', ' if lock_hint 1019 sql << (skip_locked ? "READPAST" : "NOWAIT") 1020 end 1021 1022 sql << ')' 1023 else 1024 super 1025 end 1026 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 1030 def select_order_sql(sql) 1031 super 1032 if is_2012_or_later? && @opts[:order] 1033 if o = @opts[:offset] 1034 sql << " OFFSET " 1035 literal_append(sql, o) 1036 sql << " ROWS" 1037 1038 if l = @opts[:limit] 1039 sql << " FETCH NEXT " 1040 literal_append(sql, l) 1041 sql << " ROWS ONLY" 1042 end 1043 end 1044 end 1045 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1094 def sqltime_precision 1095 6 1096 end
# File lib/sequel/adapters/shared/mssql.rb 994 def update_limit_sql(sql) 995 if l = @opts[:limit] 996 shared_limit_sql(sql, l) 997 end 998 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1106 def update_table_sql(sql) 1107 sql << ' ' 1108 source_list_append(sql, @opts[:from][0..0]) 1109 end
# File lib/sequel/adapters/shared/mssql.rb 1111 def uses_with_rollup? 1112 !is_2008_or_later? 1113 end