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
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
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
557 def constant_sql_append(sql, constant)
558   if c = CONSTANT_MAP[constant]
559     sql << c
560   else
561     super
562   end
563 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
566 def cross_apply(table)
567   join_table(:cross_apply, table)
568 end
disable_insert_output() click to toggle source

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

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
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
589 def insert_select(*values)
590   return unless supports_insert_select?
591   with_sql_first(insert_select_sql(*values)) || false
592 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
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
into(table) click to toggle source

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
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
517 def mssql_unicode_strings
518   opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings
519 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
607 def nolock
608   lock_style(:dirty)
609 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
612 def outer_apply(table)
613   join_table(:outer_apply, table)
614 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
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
quoted_identifier_append(sql, name) click to toggle source

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

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

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

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

MSSQL 2008+ supports GROUPING SETS

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

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

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

MSSQL does not support IS TRUE

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

MSSQL doesn't support JOIN USING

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

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
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
719 def supports_multiple_column_in?
720   false
721 end
supports_nowait?() click to toggle source

MSSQL supports NOWAIT.

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

MSSQL 2012+ supports offsets in correlated subqueries.

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

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

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

MSSQL uses READPAST to skip locked rows.

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

MSSQL cannot use WHERE 1.

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

MSSQL 2005+ supports window functions

    # File lib/sequel/adapters/shared/mssql.rb
749 def supports_window_functions?
750   true
751 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
522 def with_mssql_unicode_strings(v)
523   clone(:mssql_unicode_strings=>v)
524 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
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
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
779 def aggregate_dataset
780   (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super
781 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
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

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
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
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
827 def default_timestamp_format
828   "'%Y-%m-%dT%H:%M:%S%N%z'"
829 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
838 def delete_from2_sql(sql)
839   if joined_dataset?
840     select_from_sql(sql)
841     select_join_sql(sql)
842   end
843 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
832 def delete_from_sql(sql)
833   sql << ' FROM '
834   source_list_append(sql, @opts[:from][0..0])
835 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
846 def delete_output_sql(sql)
847   output_sql(sql, :DELETED)
848 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
854 def emulate_function?(name)
855   name == :char_length || name == :trim
856 end
emulate_function_sql_append(sql, f) click to toggle source
    # 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
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
868 def emulate_offset_with_row_number?
869   super && !(is_2012_or_later? && @opts[:order])
870 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
874 def first_primary_key
875   @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first
876 end
insert_output_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
878 def insert_output_sql(sql)
879   output_sql(sql, :INSERTED)
880 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
810 def is_2005_or_later?
811   server_version >= 9000000
812 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
815 def is_2008_or_later?
816   server_version >= 10000000
817 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
820 def is_2012_or_later?
821   server_version >= 11000000
822 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
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
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
896 def literal_blob_append(sql, v)
897   sql << '0x' << v.unpack("H*").first
898 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
902 def literal_date(v)
903   v.strftime("'%Y%m%d'")
904 end
literal_false() click to toggle source

Use 0 for false on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
907 def literal_false
908   '0'
909 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
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
literal_true() click to toggle source

Use 1 for true on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
919 def literal_true
920   '1'
921 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
925 def multi_insert_sql_strategy
926   is_2008_or_later? ? :values : :union
927 end
non_sql_option?(key) click to toggle source
Calls superclass method
    # 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
output_list_sql(sql, output) click to toggle source
     # 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
output_returning_sql(sql, type, values) click to toggle source
     # 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
output_sql(sql, type) click to toggle source
     # 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
requires_emulating_nulls_first?() click to toggle source

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
select_into_sql(sql) click to toggle source
    # 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
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
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
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
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
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
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
shared_limit_sql(sql, l) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
949 def shared_limit_sql(sql, l)
950   if is_2005_or_later?
951     if l == LIMIT_ALL
952       sql << " TOP (100) PERCENT"
953     else
954       sql << " TOP ("
955       literal_append(sql, l)
956       sql << ')'
957     end
958   else
959     sql << " TOP "
960     literal_append(sql, l)
961   end
962 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
1064 def sqltime_precision
1065   6
1066 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
1071 def timestamp_precision
1072   3
1073 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
964 def update_limit_sql(sql)
965   if l = @opts[:limit]
966     shared_limit_sql(sql, l)
967   end
968 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
1076 def update_table_sql(sql)
1077   sql << ' '
1078   source_list_append(sql, @opts[:from][0..0])
1079 end
uses_with_rollup?() click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1081 def uses_with_rollup?
1082   !is_2008_or_later?
1083 end