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] 663 raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties] 664 return order(1).select_sql if is_2012_or_later? && !@opts[:order] 665 end 666 super 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
Use WITH TIES when limiting the result set to also include additional rows matching the last row.
# File lib/sequel/adapters/shared/mssql.rb 760 def with_ties 761 clone(:limit_with_ties=>true) 762 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 770 def _import(columns, values, opts=OPTS) 771 if opts[:return] == :primary_key && !@opts[:output] 772 output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts) 773 elsif @opts[:output] 774 statements = multi_insert_sql(columns, values) 775 ds = naked 776 @db.transaction(opts.merge(:server=>@opts[:server])) do 777 statements.map{|st| ds.with_sql(st)} 778 end.first.map{|v| v.length == 1 ? v.values.first : v} 779 else 780 super 781 end 782 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 791 def compound_from_self 792 if @opts[:offset] && !@opts[:limit] && !is_2012_or_later? 793 clone(:limit=>LIMIT_ALL).from_self 794 elsif @opts[:order] && !(@opts[:sql] || @opts[:limit] || @opts[:offset]) 795 unordered 796 else 797 super 798 end 799 end
Private Instance Methods
MSSQL
does not allow ordering in sub-clauses unless TOP (limit) is specified
# File lib/sequel/adapters/shared/mssql.rb 804 def aggregate_dataset 805 (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super 806 end
Allow update and delete for unordered, limited datasets only.
# File lib/sequel/adapters/shared/mssql.rb 809 def check_not_limited!(type) 810 return if @opts[:skip_limit_check] && type != :truncate 811 raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit] 812 super if type == :truncate || @opts[:offset] 813 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 833 def default_timestamp_format 834 "'%Y-%m-%dT%H:%M:%S%N%z'" 835 end
MSSQL
supports FROM clauses in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mssql.rb 844 def delete_from2_sql(sql) 845 if joined_dataset? 846 select_from_sql(sql) 847 select_join_sql(sql) 848 end 849 end
Only include the primary table in the main delete clause
# File lib/sequel/adapters/shared/mssql.rb 838 def delete_from_sql(sql) 839 sql << ' FROM ' 840 source_list_append(sql, @opts[:from][0..0]) 841 end
# File lib/sequel/adapters/shared/mssql.rb 852 def delete_output_sql(sql) 853 output_sql(sql, :DELETED) 854 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 860 def emulate_function?(name) 861 name == :char_length || name == :trim 862 end
# File lib/sequel/adapters/shared/mssql.rb 864 def emulate_function_sql_append(sql, f) 865 case f.name 866 when :char_length 867 literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1) 868 when :trim 869 literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first))) 870 end 871 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 874 def emulate_offset_with_row_number? 875 super && !(is_2012_or_later? && @opts[:order]) 876 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 880 def first_primary_key 881 @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first 882 end
# File lib/sequel/adapters/shared/mssql.rb 884 def insert_output_sql(sql) 885 output_sql(sql, :INSERTED) 886 end
Whether we are using SQL
Server 2005 or later.
# File lib/sequel/adapters/shared/mssql.rb 816 def is_2005_or_later? 817 server_version >= 9000000 818 end
Whether we are using SQL
Server 2008 or later.
# File lib/sequel/adapters/shared/mssql.rb 821 def is_2008_or_later? 822 server_version >= 10000000 823 end
Whether we are using SQL
Server 2012 or later.
# File lib/sequel/adapters/shared/mssql.rb 826 def is_2012_or_later? 827 server_version >= 11000000 828 end
Handle CROSS APPLY and OUTER APPLY JOIN types
# File lib/sequel/adapters/shared/mssql.rb 890 def join_type_sql(join_type) 891 case join_type 892 when :cross_apply 893 'CROSS APPLY' 894 when :outer_apply 895 'OUTER APPLY' 896 else 897 super 898 end 899 end
MSSQL
uses a literal hexidecimal number for blob strings
# File lib/sequel/adapters/shared/mssql.rb 902 def literal_blob_append(sql, v) 903 sql << '0x' << v.unpack("H*").first 904 end
Use YYYYmmdd format, since that's the only format that is multilanguage and not DATEFORMAT dependent.
# File lib/sequel/adapters/shared/mssql.rb 908 def literal_date(v) 909 v.strftime("'%Y%m%d'") 910 end
Use 0 for false on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 913 def literal_false 914 '0' 915 end
Optionally use unicode string syntax for all strings. Don't double backslashes.
# File lib/sequel/adapters/shared/mssql.rb 919 def literal_string_append(sql, v) 920 sql << (mssql_unicode_strings ? "N'" : "'") 921 sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'" 922 end
Use 1 for true on MSSQL
# File lib/sequel/adapters/shared/mssql.rb 925 def literal_true 926 '1' 927 end
MSSQL
2008+ supports multiple rows in the VALUES clause, older versions can use UNION.
# File lib/sequel/adapters/shared/mssql.rb 931 def multi_insert_sql_strategy 932 is_2008_or_later? ? :values : :union 933 end
# File lib/sequel/adapters/shared/mssql.rb 935 def non_sql_option?(key) 936 super || key == :disable_insert_output || key == :mssql_unicode_strings 937 end
# File lib/sequel/adapters/shared/mssql.rb 1036 def output_list_sql(sql, output) 1037 sql << " OUTPUT " 1038 column_list_append(sql, output[:select_list]) 1039 if into = output[:into] 1040 sql << " INTO " 1041 identifier_append(sql, into) 1042 if column_list = output[:column_list] 1043 sql << ' (' 1044 source_list_append(sql, column_list) 1045 sql << ')' 1046 end 1047 end 1048 end
# File lib/sequel/adapters/shared/mssql.rb 1050 def output_returning_sql(sql, type, values) 1051 sql << " OUTPUT " 1052 if values.empty? 1053 literal_append(sql, SQL::ColumnAll.new(type)) 1054 else 1055 values = values.map do |v| 1056 case v 1057 when SQL::AliasedExpression 1058 Sequel.qualify(type, v.expression).as(v.alias) 1059 else 1060 Sequel.qualify(type, v) 1061 end 1062 end 1063 column_list_append(sql, values) 1064 end 1065 end
# File lib/sequel/adapters/shared/mssql.rb 1027 def output_sql(sql, type) 1028 return unless supports_output_clause? 1029 if output = @opts[:output] 1030 output_list_sql(sql, output) 1031 elsif values = @opts[:returning] 1032 output_returning_sql(sql, type, values) 1033 end 1034 end
MSSQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mssql.rb 1068 def requires_emulating_nulls_first? 1069 true 1070 end
# File lib/sequel/adapters/shared/mssql.rb 939 def select_into_sql(sql) 940 if i = @opts[:into] 941 sql << " INTO " 942 identifier_append(sql, i) 943 end 944 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 948 def select_limit_sql(sql) 949 if l = @opts[:limit] 950 return if is_2012_or_later? && @opts[:order] && @opts[:offset] 951 shared_limit_sql(sql, l) 952 end 953 end
Handle dirty, skip locked, and for update locking
# File lib/sequel/adapters/shared/mssql.rb 982 def select_lock_sql(sql) 983 lock = @opts[:lock] 984 skip_locked = @opts[:skip_locked] 985 nowait = @opts[:nowait] 986 for_update = lock == :update 987 dirty = lock == :dirty 988 lock_hint = for_update || dirty 989 990 if lock_hint || skip_locked 991 sql << " WITH (" 992 993 if lock_hint 994 sql << (for_update ? 'UPDLOCK' : 'NOLOCK') 995 end 996 997 if skip_locked || nowait 998 sql << ', ' if lock_hint 999 sql << (skip_locked ? "READPAST" : "NOWAIT") 1000 end 1001 1002 sql << ')' 1003 else 1004 super 1005 end 1006 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 1010 def select_order_sql(sql) 1011 super 1012 if is_2012_or_later? && @opts[:order] 1013 if o = @opts[:offset] 1014 sql << " OFFSET " 1015 literal_append(sql, o) 1016 sql << " ROWS" 1017 1018 if l = @opts[:limit] 1019 sql << " FETCH NEXT " 1020 literal_append(sql, l) 1021 sql << " ROWS ONLY" 1022 end 1023 end 1024 end 1025 end
MSSQL
supports 100-nsec precision for time columns, but ruby by default only supports usec precision.
# File lib/sequel/adapters/shared/mssql.rb 1074 def sqltime_precision 1075 6 1076 end
# File lib/sequel/adapters/shared/mssql.rb 974 def update_limit_sql(sql) 975 if l = @opts[:limit] 976 shared_limit_sql(sql, l) 977 end 978 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/mssql.rb 1086 def update_table_sql(sql) 1087 sql << ' ' 1088 source_list_append(sql, @opts[:from][0..0]) 1089 end
# File lib/sequel/adapters/shared/mssql.rb 1091 def uses_with_rollup? 1092 !is_2008_or_later? 1093 end