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     @db.transaction(opts.merge(:server=>@opts[:server])) do
769       statements.map{|st| with_sql(st)}
770     end.first.map{|v| v.length == 1 ? v.values.first : v}
771   else
772     super
773   end
774 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
777 def aggregate_dataset
778   (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super
779 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
788 def compound_from_self
789   if @opts[:offset] && !@opts[:limit] && !is_2012_or_later?
790     clone(:limit=>LIMIT_ALL).from_self
791   elsif @opts[:order]  && !(@opts[:sql] || @opts[:limit] || @opts[:offset])
792     unordered
793   else
794     super
795   end
796 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
801 def check_not_limited!(type)
802   return if @opts[:skip_limit_check] && type != :truncate
803   raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit]
804   super if type == :truncate || @opts[:offset]
805 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
825 def default_timestamp_format
826   "'%Y-%m-%dT%H:%M:%S%N%z'"
827 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
836 def delete_from2_sql(sql)
837   if joined_dataset?
838     select_from_sql(sql)
839     select_join_sql(sql)
840   end
841 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
830 def delete_from_sql(sql)
831   sql << ' FROM '
832   source_list_append(sql, @opts[:from][0..0])
833 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
844 def delete_output_sql(sql)
845   output_sql(sql, :DELETED)
846 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
852 def emulate_function?(name)
853   name == :char_length || name == :trim
854 end
emulate_function_sql_append(sql, f) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
856 def emulate_function_sql_append(sql, f)
857   case f.name
858   when :char_length
859     literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1)
860   when :trim
861     literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first)))
862   end
863 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
866 def emulate_offset_with_row_number?
867   super && !(is_2012_or_later? && @opts[:order])
868 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
872 def first_primary_key
873   @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first
874 end
insert_output_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
876 def insert_output_sql(sql)
877   output_sql(sql, :INSERTED)
878 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
808 def is_2005_or_later?
809   server_version >= 9000000
810 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
813 def is_2008_or_later?
814   server_version >= 10000000
815 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
818 def is_2012_or_later?
819   server_version >= 11000000
820 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
882 def join_type_sql(join_type)
883   case join_type
884   when :cross_apply
885     'CROSS APPLY'
886   when :outer_apply
887     'OUTER APPLY'
888   else
889     super
890   end
891 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
894 def literal_blob_append(sql, v)
895   sql << '0x' << v.unpack("H*").first
896 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
900 def literal_date(v)
901   v.strftime("'%Y%m%d'")
902 end
literal_false() click to toggle source

Use 0 for false on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
905 def literal_false
906   '0'
907 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
911 def literal_string_append(sql, v)
912   sql << (mssql_unicode_strings ? "N'" : "'")
913   sql << v.gsub("'", "''").gsub(/\\((?:\r\n)|\n)/, '\\\\\\\\\\1\\1') << "'"
914 end
literal_true() click to toggle source

Use 1 for true on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
917 def literal_true
918   '1'
919 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
923 def multi_insert_sql_strategy
924   is_2008_or_later? ? :values : :union
925 end
non_sql_option?(key) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
927 def non_sql_option?(key)
928   super || key == :disable_insert_output || key == :mssql_unicode_strings
929 end
output_list_sql(sql, output) click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1024 def output_list_sql(sql, output)
1025   sql << " OUTPUT "
1026   column_list_append(sql, output[:select_list])
1027   if into = output[:into]
1028     sql << " INTO "
1029     identifier_append(sql, into)
1030     if column_list = output[:column_list]
1031       sql << ' ('
1032       source_list_append(sql, column_list)
1033       sql << ')'
1034     end
1035   end
1036 end
output_returning_sql(sql, type, values) click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1038 def output_returning_sql(sql, type, values)
1039   sql << " OUTPUT "
1040   if values.empty?
1041     literal_append(sql, SQL::ColumnAll.new(type))
1042   else
1043     values = values.map do |v|
1044       case v
1045       when SQL::AliasedExpression
1046         Sequel.qualify(type, v.expression).as(v.alias)
1047       else
1048         Sequel.qualify(type, v)
1049       end
1050     end
1051     column_list_append(sql, values)
1052   end
1053 end
output_sql(sql, type) click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1015 def output_sql(sql, type)
1016   return unless supports_output_clause?
1017   if output = @opts[:output]
1018     output_list_sql(sql, output)
1019   elsif values = @opts[:returning]
1020     output_returning_sql(sql, type, values)
1021   end
1022 end
requires_emulating_nulls_first?() click to toggle source

MSSQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mssql.rb
1056 def requires_emulating_nulls_first?
1057   true
1058 end
select_into_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
931 def select_into_sql(sql)
932   if i = @opts[:into]
933     sql << " INTO "
934     identifier_append(sql, i)
935   end
936 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
940 def select_limit_sql(sql)
941   if l = @opts[:limit]
942     return if is_2012_or_later? && @opts[:order] && @opts[:offset]
943     shared_limit_sql(sql, l)
944   end
945 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
970 def select_lock_sql(sql)
971   lock = @opts[:lock]
972   skip_locked = @opts[:skip_locked]
973   nowait = @opts[:nowait]
974   for_update = lock == :update
975   dirty = lock == :dirty
976   lock_hint = for_update || dirty
977 
978   if lock_hint || skip_locked
979     sql << " WITH ("
980 
981     if lock_hint
982       sql << (for_update ? 'UPDLOCK' : 'NOLOCK')
983     end
984 
985     if skip_locked || nowait
986       sql << ', ' if lock_hint
987       sql << (skip_locked ? "READPAST" : "NOWAIT")
988     end
989 
990     sql << ')'
991   else
992     super
993   end
994 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
 998 def select_order_sql(sql)
 999   super
1000   if is_2012_or_later? && @opts[:order]
1001     if o = @opts[:offset]
1002       sql << " OFFSET "
1003       literal_append(sql, o)
1004       sql << " ROWS"
1005 
1006       if l = @opts[:limit]
1007         sql << " FETCH NEXT "
1008         literal_append(sql, l)
1009         sql << " ROWS ONLY"
1010       end
1011     end
1012   end
1013 end
shared_limit_sql(sql, l) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
947 def shared_limit_sql(sql, l)
948   if is_2005_or_later?
949     if l == LIMIT_ALL
950       sql << " TOP (100) PERCENT"
951     else
952       sql << " TOP ("
953       literal_append(sql, l)
954       sql << ')'
955     end
956   else
957     sql << " TOP "
958     literal_append(sql, l)
959   end
960 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
1062 def sqltime_precision
1063   6
1064 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
1069 def timestamp_precision
1070   3
1071 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
962 def update_limit_sql(sql)
963   if l = @opts[:limit]
964     shared_limit_sql(sql, l)
965   end
966 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
1074 def update_table_sql(sql)
1075   sql << ' '
1076   source_list_append(sql, @opts[:from][0..0])
1077 end
uses_with_rollup?() click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1079 def uses_with_rollup?
1080   !is_2008_or_later?
1081 end