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