module Sequel::MSSQL::DatasetMethods

Constants

CONSTANT_MAP
EXTRACT_MAP
LIMIT_ALL

Public Instance Methods

complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # 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
constant_sql_append(sql, constant) click to toggle source

MSSQL doesn't support the SQL standard CURRENT_DATE or CURRENT_TIME

Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
556 def constant_sql_append(sql, constant)
557   if c = CONSTANT_MAP[constant]
558     sql << c
559   else
560     super
561   end
562 end
cross_apply(table) click to toggle source

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_insert_output() click to toggle source

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
escape_like(string) click to toggle source

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
insert_select(*values) click to toggle source

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
insert_select_sql(*values) click to toggle source

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
into(table) click to toggle source

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
mssql_unicode_strings() click to toggle source

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
nolock() click to toggle source

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
outer_apply(table) click to toggle source

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
output(into, values) click to toggle source

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
quoted_identifier_append(sql, name) click to toggle source

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
returning(*values) click to toggle source

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
select_sql() click to toggle source

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.

    # 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
server_version() click to toggle source

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
supports_cte?(type=:select) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
673 def supports_cte?(type=:select)
674   is_2005_or_later?
675 end
supports_group_cube?() click to toggle source

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
supports_group_rollup?() click to toggle source

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
supports_grouping_sets?() click to toggle source

MSSQL 2008+ supports GROUPING SETS

    # File lib/sequel/adapters/shared/mssql.rb
688 def supports_grouping_sets?
689   is_2008_or_later?
690 end
supports_insert_select?() click to toggle source

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
supports_intersect_except?() click to toggle source

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
supports_is_true?() click to toggle source

MSSQL does not support IS TRUE

    # File lib/sequel/adapters/shared/mssql.rb
703 def supports_is_true?
704   false
705 end
supports_join_using?() click to toggle source

MSSQL doesn't support JOIN USING

    # File lib/sequel/adapters/shared/mssql.rb
708 def supports_join_using?
709   false
710 end
supports_modifying_joins?() click to toggle source

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
supports_multiple_column_in?() click to toggle source

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
supports_nowait?() click to toggle source

MSSQL supports NOWAIT.

    # File lib/sequel/adapters/shared/mssql.rb
723 def supports_nowait?
724   true
725 end
supports_offsets_in_correlated_subqueries?() click to toggle source

MSSQL 2012+ supports offsets in correlated subqueries.

    # File lib/sequel/adapters/shared/mssql.rb
728 def supports_offsets_in_correlated_subqueries?
729   is_2012_or_later?
730 end
supports_output_clause?() click to toggle source

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
supports_returning?(type) click to toggle source

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
supports_skip_locked?() click to toggle source

MSSQL uses READPAST to skip locked rows.

    # File lib/sequel/adapters/shared/mssql.rb
743 def supports_skip_locked?
744   true
745 end
supports_where_true?() click to toggle source

MSSQL cannot use WHERE 1.

    # File lib/sequel/adapters/shared/mssql.rb
753 def supports_where_true?
754   false
755 end
supports_window_functions?() click to toggle source

MSSQL 2005+ supports window functions

    # File lib/sequel/adapters/shared/mssql.rb
748 def supports_window_functions?
749   true
750 end
with_mssql_unicode_strings(v) click to toggle source

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

_import(columns, values, opts=OPTS) click to toggle source

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.

Calls superclass method
    # 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
aggregate_dataset() click to toggle source

MSSQL does not allow ordering in sub-clauses unless TOP (limit) is specified

Calls superclass method
    # 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
compound_from_self() click to toggle source

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.

Calls superclass method
    # 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

check_not_limited!(type) click to toggle source

Allow update and delete for unordered, limited datasets only.

Calls superclass method
    # 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
default_timestamp_format() click to toggle source

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
delete_from2_sql(sql) click to toggle source

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
Also aliased as: update_from_sql
delete_from_sql(sql) click to toggle source

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
delete_limit_sql(sql)
Alias for: update_limit_sql
delete_output_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
845 def delete_output_sql(sql)
846   output_sql(sql, :DELETED)
847 end
emulate_function?(name) click to toggle source

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
emulate_function_sql_append(sql, f) click to toggle source
    # 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
emulate_offset_with_row_number?() click to toggle source

Microsoft SQL Server 2012+ has native support for offsets, but only for ordered datasets.

    # File lib/sequel/adapters/shared/mssql.rb
867 def emulate_offset_with_row_number?
868   super && !(is_2012_or_later? && @opts[:order])
869 end
first_primary_key() click to toggle source

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
insert_output_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
877 def insert_output_sql(sql)
878   output_sql(sql, :INSERTED)
879 end
Also aliased as: update_output_sql
is_2005_or_later?() click to toggle source

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
is_2008_or_later?() click to toggle source

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
is_2012_or_later?() click to toggle source

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
join_type_sql(join_type) click to toggle source

Handle CROSS APPLY and OUTER APPLY JOIN types

Calls superclass method
    # 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
literal_blob_append(sql, v) click to toggle source

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
literal_date(v) click to toggle source

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
literal_false() click to toggle source

Use 0 for false on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
906 def literal_false
907   '0'
908 end
literal_string_append(sql, v) click to toggle source

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
literal_true() click to toggle source

Use 1 for true on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
918 def literal_true
919   '1'
920 end
multi_insert_sql_strategy() click to toggle source

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
non_sql_option?(key) click to toggle source
Calls superclass method
    # 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
output_list_sql(sql, output) click to toggle source
     # 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
output_returning_sql(sql, type, values) click to toggle source
     # 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
output_sql(sql, type) click to toggle source
     # 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
requires_emulating_nulls_first?() click to toggle source

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
select_into_sql(sql) click to toggle source
    # 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
select_limit_sql(sql) click to toggle source

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
select_lock_sql(sql) click to toggle source

Handle dirty, skip locked, and for update locking

Calls superclass method
    # 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
select_order_sql(sql) click to toggle source

On 2012+ when there is an order with an offset, append the offset (and possible limit) at the end of the order clause.

Calls superclass method
     # 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
shared_limit_sql(sql, l) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
948 def shared_limit_sql(sql, l)
949   if is_2005_or_later?
950     if l == LIMIT_ALL
951       sql << " TOP (100) PERCENT"
952     else
953       sql << " TOP ("
954       literal_append(sql, l)
955       sql << ')'
956     end
957   else
958     sql << " TOP "
959     literal_append(sql, l)
960   end
961 end
sqltime_precision() click to toggle source

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
timestamp_precision() click to toggle source

MSSQL supports millisecond timestamp precision for datetime columns. 100-nsec precision is supported for datetime2 columns, but Sequel does not know what the column type is when formatting values.

     # File lib/sequel/adapters/shared/mssql.rb
1070 def timestamp_precision
1071   3
1072 end
update_from_sql(sql)
Alias for: delete_from2_sql
update_limit_sql(sql) click to toggle source
    # 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
Also aliased as: delete_limit_sql
update_output_sql(sql)
Alias for: insert_output_sql
update_table_sql(sql) click to toggle source

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
uses_with_rollup?() click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1080 def uses_with_rollup?
1081   !is_2008_or_later?
1082 end