module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 519 def complex_expression_sql_append(sql, op, args) 520 case op 521 when :'||' 522 super(sql, :+, args) 523 when :LIKE, :"NOT LIKE" 524 super(sql, op, args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CS_AS)"], a)}) 525 when :ILIKE, :"NOT ILIKE" 526 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CI_AS)"], a)}) 527 when :<<, :>> 528 complex_expression_emulate_append(sql, op, args) 529 when :extract 530 part = args[0] 531 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 532 if part == :second 533 expr = args[1] 534 sql << "CAST((datepart(" << format.to_s << ', ' 535 literal_append(sql, expr) 536 sql << ') + datepart(ns, ' 537 literal_append(sql, expr) 538 sql << ")/1000000000.0) AS double precision)" 539 else 540 sql << "datepart(" << format.to_s << ', ' 541 literal_append(sql, args[1]) 542 sql << ')' 543 end 544 else 545 super 546 end 547 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 559 def cross_apply(table) 560 join_table(:cross_apply, table) 561 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 564 def disable_insert_output 565 clone(:disable_insert_output=>true) 566 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 569 def escape_like(string) 570 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 571 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 574 def full_text_search(cols, terms, opts = OPTS) 575 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 576 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 577 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 582 def insert_select(*values) 583 return unless supports_insert_select? 584 with_sql_first(insert_select_sql(*values)) || false 585 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 589 def insert_select_sql(*values) 590 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 591 ds.insert_sql(*values) 592 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 595 def into(table) 596 clone(:into => table) 597 end
Use the database's mssql_unicode_strings
setting if the dataset hasn't overridden it.
# File lib/sequel/adapters/shared/mssql.rb 510 def mssql_unicode_strings 511 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 512 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 600 def nolock 601 lock_style(:dirty) 602 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 605 def outer_apply(table) 606 join_table(:outer_apply, table) 607 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 621 def output(into, values) 622 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 623 output = {} 624 case values 625 when Hash 626 output[:column_list], output[:select_list] = values.keys, values.values 627 when Array 628 output[:select_list] = values 629 end 630 output[:into] = into 631 clone(:output => output) 632 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 635 def quoted_identifier_append(sql, name) 636 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 637 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 640 def returning(*values) 641 values = values.map do |v| 642 unless r = unqualified_column_for(v) 643 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 644 end 645 r 646 end 647 clone(:returning=>values) 648 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 654 def select_sql 655 if @opts[:offset] && !@opts[:order] && is_2012_or_later? 656 order(1).select_sql 657 else 658 super 659 end 660 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 663 def server_version 664 db.server_version(@opts[:server]) 665 end
# File lib/sequel/adapters/shared/mssql.rb 667 def supports_cte?(type=:select) 668 is_2005_or_later? 669 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 672 def supports_group_cube? 673 is_2005_or_later? 674 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 677 def supports_group_rollup? 678 is_2005_or_later? 679 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 682 def supports_grouping_sets? 683 is_2008_or_later? 684 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 687 def supports_insert_select? 688 supports_output_clause? && !opts[:disable_insert_output] 689 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 692 def supports_intersect_except? 693 is_2005_or_later? 694 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 697 def supports_is_true? 698 false 699 end
MSSQL
doesn't support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 702 def supports_join_using? 703 false 704 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 707 def supports_modifying_joins? 708 is_2005_or_later? 709 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 712 def supports_multiple_column_in? 713 false 714 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 717 def supports_nowait? 718 true 719 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 727 def supports_output_clause? 728 is_2005_or_later? 729 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 732 def supports_returning?(type) 733 supports_insert_select? 734 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 737 def supports_skip_locked? 738 true 739 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 747 def supports_where_true? 748 false 749 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 742 def supports_window_functions? 743 true 744 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 515 def with_mssql_unicode_strings(v) 516 clone(:mssql_unicode_strings=>v) 517 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 757 def _import(columns, values, opts=OPTS) 758 if opts[:return] == :primary_key && !@opts[:output] 759 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 760 elsif @opts[:output] 761 statements = multi_insert_sql(columns, values) 762 @db.transaction(opts.merge(:server=>@opts[:server])) do 763 statements.map{|st| with_sql(st)} 764 end.first.map{|v| v.length == 1 ? v.values.first : v} 765 else 766 super 767 end 768 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 771 def aggregate_dataset 772 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 773 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 782 def compound_from_self 783 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 784 clone(:limit=>LIMIT_ALL).from_self 785 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 786 unordered 787 else 788 super 789 end 790 end
Private Instance Methods
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 795 def check_not_limited!(type) 796 return if @opts[:skip_limit_check] && type != :truncate 797 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 798 super if type == :truncate || @opts[:offset] 799 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 819 def default_timestamp_format 820 "'%Y-%m-%dT%H:%M:%S%N%z'" 821 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 830 def delete_from2_sql(sql) 831 if joined_dataset? 832 select_from_sql(sql) 833 select_join_sql(sql) 834 end 835 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 824 def delete_from_sql(sql) 825 sql << ' FROM ' 826 source_list_append(sql, @opts[:from][0..0]) 827 end
# File lib/sequel/adapters/shared/mssql.rb 838 def delete_output_sql(sql) 839 output_sql(sql, :DELETED) 840 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 846 def emulate_function?(name) 847 name == :char_length || name == :trim 848 end
# File lib/sequel/adapters/shared/mssql.rb 850 def emulate_function_sql_append(sql, f) 851 case f.name 852 when :char_length 853 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 854 when :trim 855 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 856 end 857 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 860 def emulate_offset_with_row_number? 861 super && !(is_2012_or_later? && @opts[:order]) 862 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 866 def first_primary_key 867 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 868 end
# File lib/sequel/adapters/shared/mssql.rb 870 def insert_output_sql(sql) 871 output_sql(sql, :INSERTED) 872 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 802 def is_2005_or_later? 803 server_version >= 9000000 804 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 807 def is_2008_or_later? 808 server_version >= 10000000 809 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 812 def is_2012_or_later? 813 server_version >= 11000000 814 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 876 def join_type_sql(join_type) 877 case join_type 878 when :cross_apply 879 'CROSS APPLY' 880 when :outer_apply 881 'OUTER APPLY' 882 else 883 super 884 end 885 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 888 def literal_blob_append(sql, v) 889 sql << '0x' << v.unpack("H*").first 890 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 894 def literal_date(v) 895 v.strftime("'%Y%m%d'") 896 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 899 def literal_false 900 '0' 901 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 905 def literal_string_append(sql, v) 906 sql << (mssql_unicode_strings ? "N'" : "'") 907 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 908 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 911 def literal_true 912 '1' 913 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 917 def multi_insert_sql_strategy 918 is_2008_or_later? ? :values : :union 919 end
# File lib/sequel/adapters/shared/mssql.rb 921 def non_sql_option?(key) 922 super || key == :disable_insert_output || key == :mssql_unicode_strings 923 end
# File lib/sequel/adapters/shared/mssql.rb 1018 def output_list_sql(sql, output) 1019 sql << " OUTPUT " 1020 column_list_append(sql, output[:select_list]) 1021 if into = output[:into] 1022 sql << " INTO " 1023 identifier_append(sql, into) 1024 if column_list = output[:column_list] 1025 sql << ' (' 1026 source_list_append(sql, column_list) 1027 sql << ')' 1028 end 1029 end 1030 end
# File lib/sequel/adapters/shared/mssql.rb 1032 def output_returning_sql(sql, type, values) 1033 sql << " OUTPUT " 1034 if values.empty? 1035 literal_append(sql, SQL::ColumnAll.new(type)) 1036 else 1037 values = values.map do |v| 1038 case v 1039 when SQL::AliasedExpression 1040 Sequel.qualify(type, v.expression).as(v.alias) 1041 else 1042 Sequel.qualify(type, v) 1043 end 1044 end 1045 column_list_append(sql, values) 1046 end 1047 end
# File lib/sequel/adapters/shared/mssql.rb 1009 def output_sql(sql, type) 1010 return unless supports_output_clause? 1011 if output = @opts[:output] 1012 output_list_sql(sql, output) 1013 elsif values = @opts[:returning] 1014 output_returning_sql(sql, type, values) 1015 end 1016 end
# File lib/sequel/adapters/shared/mssql.rb 925 def select_into_sql(sql) 926 if i = @opts[:into] 927 sql << " INTO " 928 identifier_append(sql, i) 929 end 930 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 934 def select_limit_sql(sql) 935 if l = @opts[:limit] 936 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 937 shared_limit_sql(sql, l) 938 end 939 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 964 def select_lock_sql(sql) 965 lock = @opts[:lock] 966 skip_locked = @opts[:skip_locked] 967 nowait = @opts[:nowait] 968 for_update = lock == :update 969 dirty = lock == :dirty 970 lock_hint = for_update || dirty 971 972 if lock_hint || skip_locked 973 sql << " WITH (" 974 975 if lock_hint 976 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 977 end 978 979 if skip_locked || nowait 980 sql << ', ' if lock_hint 981 sql << (skip_locked ? "READPAST" : "NOWAIT") 982 end 983 984 sql << ')' 985 else 986 super 987 end 988 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 992 def select_order_sql(sql) 993 super 994 if is_2012_or_later? && @opts[:order] 995 if o = @opts[:offset] 996 sql << " OFFSET " 997 literal_append(sql, o) 998 sql << " ROWS" 999 1000 if l = @opts[:limit] 1001 sql << " FETCH NEXT " 1002 literal_append(sql, l) 1003 sql << " ROWS ONLY" 1004 end 1005 end 1006 end 1007 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1051 def sqltime_precision 1052 6 1053 end
# File lib/sequel/adapters/shared/mssql.rb 956 def update_limit_sql(sql) 957 if l = @opts[:limit] 958 shared_limit_sql(sql, l) 959 end 960 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1063 def update_table_sql(sql) 1064 sql << ' ' 1065 source_list_append(sql, @opts[:from][0..0]) 1066 end
# File lib/sequel/adapters/shared/mssql.rb 1068 def uses_with_rollup? 1069 !is_2008_or_later? 1070 end