module Sequel::MSSQL::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- LIMIT_ALL
Public Instance Methods
# File lib/sequel/adapters/shared/mssql.rb 525 def complex_expression_sql_append(sql, op, args) 526 case op 527 when :'||' 528 super(sql, :+, args) 529 when :LIKE, :"NOT LIKE" 530 super(sql, op, args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CS_AS)"], a)}) 531 when :ILIKE, :"NOT ILIKE" 532 super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CI_AS)"], a)}) 533 when :<<, :>> 534 complex_expression_emulate_append(sql, op, args) 535 when :extract 536 part = args[0] 537 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 538 if part == :second 539 expr = args[1] 540 sql << "CAST((datepart(" << format.to_s << ', ' 541 literal_append(sql, expr) 542 sql << ') + datepart(ns, ' 543 literal_append(sql, expr) 544 sql << ")/1000000000.0) AS double precision)" 545 else 546 sql << "datepart(" << format.to_s << ', ' 547 literal_append(sql, args[1]) 548 sql << ')' 549 end 550 else 551 super 552 end 553 end
Uses CROSS APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 565 def cross_apply(table) 566 join_table(:cross_apply, table) 567 end
Disable the use of INSERT OUTPUT
# File lib/sequel/adapters/shared/mssql.rb 570 def disable_insert_output 571 clone(:disable_insert_output=>true) 572 end
MSSQL
treats [] as a metacharacter in LIKE expresions.
# File lib/sequel/adapters/shared/mssql.rb 575 def escape_like(string) 576 string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"} 577 end
MSSQL
uses the CONTAINS keyword for full text search
# File lib/sequel/adapters/shared/mssql.rb 580 def full_text_search(cols, terms, opts = OPTS) 581 terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array) 582 where(Sequel.lit("CONTAINS (?, ?)", cols, terms)) 583 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 588 def insert_select(*values) 589 return unless supports_insert_select? 590 with_sql_first(insert_select_sql(*values)) || false 591 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 595 def insert_select_sql(*values) 596 ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)]) 597 ds.insert_sql(*values) 598 end
Specify a table for a SELECT … INTO query.
# File lib/sequel/adapters/shared/mssql.rb 601 def into(table) 602 clone(:into => table) 603 end
Use the database's mssql_unicode_strings
setting if the dataset hasn't overridden it.
# File lib/sequel/adapters/shared/mssql.rb 516 def mssql_unicode_strings 517 opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings 518 end
Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).
# File lib/sequel/adapters/shared/mssql.rb 606 def nolock 607 lock_style(:dirty) 608 end
Uses OUTER APPLY to join the given table into the current dataset.
# File lib/sequel/adapters/shared/mssql.rb 611 def outer_apply(table) 612 join_table(:outer_apply, table) 613 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 627 def output(into, values) 628 raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause? 629 output = {} 630 case values 631 when Hash 632 output[:column_list], output[:select_list] = values.keys, values.values 633 when Array 634 output[:select_list] = values 635 end 636 output[:into] = into 637 clone(:output => output) 638 end
MSSQL
uses [] to quote identifiers.
# File lib/sequel/adapters/shared/mssql.rb 641 def quoted_identifier_append(sql, name) 642 sql << '[' << name.to_s.gsub(/\]/, ']]') << ']' 643 end
Emulate RETURNING using the output clause. This only handles values that are simple column references.
# File lib/sequel/adapters/shared/mssql.rb 646 def returning(*values) 647 values = values.map do |v| 648 unless r = unqualified_column_for(v) 649 raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}") 650 end 651 r 652 end 653 clone(:returning=>values) 654 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 660 def select_sql 661 if @opts[:offset] && !@opts[:order] && is_2012_or_later? 662 order(1).select_sql 663 else 664 super 665 end 666 end
The version of the database server.
# File lib/sequel/adapters/shared/mssql.rb 669 def server_version 670 db.server_version(@opts[:server]) 671 end
# File lib/sequel/adapters/shared/mssql.rb 673 def supports_cte?(type=:select) 674 is_2005_or_later? 675 end
MSSQL
2005+ supports GROUP BY CUBE.
# File lib/sequel/adapters/shared/mssql.rb 678 def supports_group_cube? 679 is_2005_or_later? 680 end
MSSQL
2005+ supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/mssql.rb 683 def supports_group_rollup? 684 is_2005_or_later? 685 end
MSSQL
2008+ supports GROUPING SETS
# File lib/sequel/adapters/shared/mssql.rb 688 def supports_grouping_sets? 689 is_2008_or_later? 690 end
MSSQL
supports insert_select
via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 693 def supports_insert_select? 694 supports_output_clause? && !opts[:disable_insert_output] 695 end
MSSQL
2005+ supports INTERSECT and EXCEPT
# File lib/sequel/adapters/shared/mssql.rb 698 def supports_intersect_except? 699 is_2005_or_later? 700 end
MSSQL
does not support IS TRUE
# File lib/sequel/adapters/shared/mssql.rb 703 def supports_is_true? 704 false 705 end
MSSQL
doesn't support JOIN USING
# File lib/sequel/adapters/shared/mssql.rb 708 def supports_join_using? 709 false 710 end
MSSQL
2005+ supports modifying joined datasets
# File lib/sequel/adapters/shared/mssql.rb 713 def supports_modifying_joins? 714 is_2005_or_later? 715 end
MSSQL
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/mssql.rb 718 def supports_multiple_column_in? 719 false 720 end
MSSQL
supports NOWAIT.
# File lib/sequel/adapters/shared/mssql.rb 723 def supports_nowait? 724 true 725 end
MSSQL
2005+ supports the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 733 def supports_output_clause? 734 is_2005_or_later? 735 end
MSSQL
2005+ can emulate RETURNING via the OUTPUT clause.
# File lib/sequel/adapters/shared/mssql.rb 738 def supports_returning?(type) 739 supports_insert_select? 740 end
MSSQL
uses READPAST to skip locked rows.
# File lib/sequel/adapters/shared/mssql.rb 743 def supports_skip_locked? 744 true 745 end
MSSQL
cannot use WHERE 1.
# File lib/sequel/adapters/shared/mssql.rb 753 def supports_where_true? 754 false 755 end
MSSQL
2005+ supports window functions
# File lib/sequel/adapters/shared/mssql.rb 748 def supports_window_functions? 749 true 750 end
Return a cloned dataset with the mssql_unicode_strings
option set.
# File lib/sequel/adapters/shared/mssql.rb 521 def with_mssql_unicode_strings(v) 522 clone(:mssql_unicode_strings=>v) 523 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 763 def _import(columns, values, opts=OPTS) 764 if opts[:return] == :primary_key && !@opts[:output] 765 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 766 elsif @opts[:output] 767 statements = multi_insert_sql(columns, values) 768 ds = naked 769 @db.transaction(opts.merge(:server=>@opts[:server])) do 770 statements.map{|st| ds.with_sql(st)} 771 end.first.map{|v| v.length == 1 ? v.values.first : v} 772 else 773 super 774 end 775 end
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 778 def aggregate_dataset 779 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 780 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 789 def compound_from_self 790 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 791 clone(:limit=>LIMIT_ALL).from_self 792 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 793 unordered 794 else 795 super 796 end 797 end
Private Instance Methods
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 802 def check_not_limited!(type) 803 return if @opts[:skip_limit_check] && type != :truncate 804 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 805 super if type == :truncate || @opts[:offset] 806 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 826 def default_timestamp_format 827 "'%Y-%m-%dT%H:%M:%S%N%z'" 828 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 837 def delete_from2_sql(sql) 838 if joined_dataset? 839 select_from_sql(sql) 840 select_join_sql(sql) 841 end 842 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 831 def delete_from_sql(sql) 832 sql << ' FROM ' 833 source_list_append(sql, @opts[:from][0..0]) 834 end
# File lib/sequel/adapters/shared/mssql.rb 845 def delete_output_sql(sql) 846 output_sql(sql, :DELETED) 847 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 853 def emulate_function?(name) 854 name == :char_length || name == :trim 855 end
# File lib/sequel/adapters/shared/mssql.rb 857 def emulate_function_sql_append(sql, f) 858 case f.name 859 when :char_length 860 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 861 when :trim 862 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 863 end 864 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 867 def emulate_offset_with_row_number? 868 super && !(is_2012_or_later? && @opts[:order]) 869 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 873 def first_primary_key 874 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 875 end
# File lib/sequel/adapters/shared/mssql.rb 877 def insert_output_sql(sql) 878 output_sql(sql, :INSERTED) 879 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 809 def is_2005_or_later? 810 server_version >= 9000000 811 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 814 def is_2008_or_later? 815 server_version >= 10000000 816 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 819 def is_2012_or_later? 820 server_version >= 11000000 821 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 883 def join_type_sql(join_type) 884 case join_type 885 when :cross_apply 886 'CROSS APPLY' 887 when :outer_apply 888 'OUTER APPLY' 889 else 890 super 891 end 892 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 895 def literal_blob_append(sql, v) 896 sql << '0x' << v.unpack("H*").first 897 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 901 def literal_date(v) 902 v.strftime("'%Y%m%d'") 903 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 906 def literal_false 907 '0' 908 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 912 def literal_string_append(sql, v) 913 sql << (mssql_unicode_strings ? "N'" : "'") 914 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 915 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 918 def literal_true 919 '1' 920 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 924 def multi_insert_sql_strategy 925 is_2008_or_later? ? :values : :union 926 end
# File lib/sequel/adapters/shared/mssql.rb 928 def non_sql_option?(key) 929 super || key == :disable_insert_output || key == :mssql_unicode_strings 930 end
# File lib/sequel/adapters/shared/mssql.rb 1025 def output_list_sql(sql, output) 1026 sql << " OUTPUT " 1027 column_list_append(sql, output[:select_list]) 1028 if into = output[:into] 1029 sql << " INTO " 1030 identifier_append(sql, into) 1031 if column_list = output[:column_list] 1032 sql << ' (' 1033 source_list_append(sql, column_list) 1034 sql << ')' 1035 end 1036 end 1037 end
# File lib/sequel/adapters/shared/mssql.rb 1039 def output_returning_sql(sql, type, values) 1040 sql << " OUTPUT " 1041 if values.empty? 1042 literal_append(sql, SQL::ColumnAll.new(type)) 1043 else 1044 values = values.map do |v| 1045 case v 1046 when SQL::AliasedExpression 1047 Sequel.qualify(type, v.expression).as(v.alias) 1048 else 1049 Sequel.qualify(type, v) 1050 end 1051 end 1052 column_list_append(sql, values) 1053 end 1054 end
# File lib/sequel/adapters/shared/mssql.rb 1016 def output_sql(sql, type) 1017 return unless supports_output_clause? 1018 if output = @opts[:output] 1019 output_list_sql(sql, output) 1020 elsif values = @opts[:returning] 1021 output_returning_sql(sql, type, values) 1022 end 1023 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1057 def requires_emulating_nulls_first? 1058 true 1059 end
# File lib/sequel/adapters/shared/mssql.rb 932 def select_into_sql(sql) 933 if i = @opts[:into] 934 sql << " INTO " 935 identifier_append(sql, i) 936 end 937 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 941 def select_limit_sql(sql) 942 if l = @opts[:limit] 943 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 944 shared_limit_sql(sql, l) 945 end 946 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 971 def select_lock_sql(sql) 972 lock = @opts[:lock] 973 skip_locked = @opts[:skip_locked] 974 nowait = @opts[:nowait] 975 for_update = lock == :update 976 dirty = lock == :dirty 977 lock_hint = for_update || dirty 978 979 if lock_hint || skip_locked 980 sql << " WITH (" 981 982 if lock_hint 983 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 984 end 985 986 if skip_locked || nowait 987 sql << ', ' if lock_hint 988 sql << (skip_locked ? "READPAST" : "NOWAIT") 989 end 990 991 sql << ')' 992 else 993 super 994 end 995 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 999 def select_order_sql(sql) 1000 super 1001 if is_2012_or_later? && @opts[:order] 1002 if o = @opts[:offset] 1003 sql << " OFFSET " 1004 literal_append(sql, o) 1005 sql << " ROWS" 1006 1007 if l = @opts[:limit] 1008 sql << " FETCH NEXT " 1009 literal_append(sql, l) 1010 sql << " ROWS ONLY" 1011 end 1012 end 1013 end 1014 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1063 def sqltime_precision 1064 6 1065 end
# File lib/sequel/adapters/shared/mssql.rb 963 def update_limit_sql(sql) 964 if l = @opts[:limit] 965 shared_limit_sql(sql, l) 966 end 967 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1075 def update_table_sql(sql) 1076 sql << ' ' 1077 source_list_append(sql, @opts[:from][0..0]) 1078 end
# File lib/sequel/adapters/shared/mssql.rb 1080 def uses_with_rollup? 1081 !is_2008_or_later? 1082 end