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
519 def complex_expression_sql_append(sql, op, args)
520   case op
521   when :'||'
522     super(sql, :+, args)
523   when :LIKE, :"NOT LIKE"
524     super(sql, op, args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CS_AS)"], a)})
525   when :ILIKE, :"NOT ILIKE"
526     super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|a| Sequel.lit(["(", " COLLATE Latin1_General_CI_AS)"], a)})
527   when :<<, :>>
528     complex_expression_emulate_append(sql, op, args)
529   when :extract
530     part = args[0]
531     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
532     if part == :second
533       expr = args[1]
534       sql << "CAST((datepart(" << format.to_s << ', '
535       literal_append(sql, expr)
536       sql << ') + datepart(ns, '
537       literal_append(sql, expr)
538       sql << ")/1000000000.0) AS double precision)"
539     else
540       sql << "datepart(" << format.to_s << ', '
541       literal_append(sql, args[1])
542       sql << ')'
543     end
544   else
545     super
546   end
547 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
550 def constant_sql_append(sql, constant)
551   if c = CONSTANT_MAP[constant]
552     sql << c
553   else
554     super
555   end
556 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
559 def cross_apply(table)
560   join_table(:cross_apply, table)
561 end
disable_insert_output() click to toggle source

Disable the use of INSERT OUTPUT

    # File lib/sequel/adapters/shared/mssql.rb
564 def disable_insert_output
565   clone(:disable_insert_output=>true)
566 end
escape_like(string) click to toggle source

MSSQL treats [] as a metacharacter in LIKE expresions.

    # File lib/sequel/adapters/shared/mssql.rb
569 def escape_like(string)
570   string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"}
571 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
582 def insert_select(*values)
583   return unless supports_insert_select?
584   with_sql_first(insert_select_sql(*values)) || false
585 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
589 def insert_select_sql(*values)
590   ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)])
591   ds.insert_sql(*values)
592 end
into(table) click to toggle source

Specify a table for a SELECT … INTO query.

    # File lib/sequel/adapters/shared/mssql.rb
595 def into(table)
596   clone(:into => table)
597 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
510 def mssql_unicode_strings
511   opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings
512 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
600 def nolock
601   lock_style(:dirty)
602 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
605 def outer_apply(table)
606   join_table(:outer_apply, table)
607 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
621 def output(into, values)
622   raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause?
623   output = {}
624   case values
625   when Hash
626     output[:column_list], output[:select_list] = values.keys, values.values
627   when Array
628     output[:select_list] = values
629   end
630   output[:into] = into
631   clone(:output => output)
632 end
quoted_identifier_append(sql, name) click to toggle source

MSSQL uses [] to quote identifiers.

    # File lib/sequel/adapters/shared/mssql.rb
635 def quoted_identifier_append(sql, name)
636   sql << '[' << name.to_s.gsub(/\]/, ']]') << ']'
637 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
640 def returning(*values)
641   values = values.map do |v|
642     unless r = unqualified_column_for(v)
643       raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}")
644     end
645     r
646   end
647   clone(:returning=>values)
648 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
654 def select_sql
655   if @opts[:offset] && !@opts[:order] && is_2012_or_later?
656     order(1).select_sql
657   else
658     super
659   end
660 end
server_version() click to toggle source

The version of the database server.

    # File lib/sequel/adapters/shared/mssql.rb
663 def server_version
664   db.server_version(@opts[:server])
665 end
supports_cte?(type=:select) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
667 def supports_cte?(type=:select)
668   is_2005_or_later?
669 end
supports_group_cube?() click to toggle source

MSSQL 2005+ supports GROUP BY CUBE.

    # File lib/sequel/adapters/shared/mssql.rb
672 def supports_group_cube?
673   is_2005_or_later?
674 end
supports_group_rollup?() click to toggle source

MSSQL 2005+ supports GROUP BY ROLLUP

    # File lib/sequel/adapters/shared/mssql.rb
677 def supports_group_rollup?
678   is_2005_or_later?
679 end
supports_grouping_sets?() click to toggle source

MSSQL 2008+ supports GROUPING SETS

    # File lib/sequel/adapters/shared/mssql.rb
682 def supports_grouping_sets?
683   is_2008_or_later?
684 end
supports_insert_select?() click to toggle source

MSSQL supports insert_select via the OUTPUT clause.

    # File lib/sequel/adapters/shared/mssql.rb
687 def supports_insert_select?
688   supports_output_clause? && !opts[:disable_insert_output]
689 end
supports_intersect_except?() click to toggle source

MSSQL 2005+ supports INTERSECT and EXCEPT

    # File lib/sequel/adapters/shared/mssql.rb
692 def supports_intersect_except?
693   is_2005_or_later?
694 end
supports_is_true?() click to toggle source

MSSQL does not support IS TRUE

    # File lib/sequel/adapters/shared/mssql.rb
697 def supports_is_true?
698   false
699 end
supports_join_using?() click to toggle source

MSSQL doesn't support JOIN USING

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

MSSQL 2005+ supports modifying joined datasets

    # File lib/sequel/adapters/shared/mssql.rb
707 def supports_modifying_joins?
708   is_2005_or_later?
709 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
712 def supports_multiple_column_in?
713   false
714 end
supports_nowait?() click to toggle source

MSSQL supports NOWAIT.

    # File lib/sequel/adapters/shared/mssql.rb
717 def supports_nowait?
718   true
719 end
supports_offsets_in_correlated_subqueries?() click to toggle source

MSSQL 2012+ supports offsets in correlated subqueries.

    # File lib/sequel/adapters/shared/mssql.rb
722 def supports_offsets_in_correlated_subqueries?
723   is_2012_or_later?
724 end
supports_output_clause?() click to toggle source

MSSQL 2005+ supports the OUTPUT clause.

    # File lib/sequel/adapters/shared/mssql.rb
727 def supports_output_clause?
728   is_2005_or_later?
729 end
supports_returning?(type) click to toggle source

MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.

    # File lib/sequel/adapters/shared/mssql.rb
732 def supports_returning?(type)
733   supports_insert_select?
734 end
supports_skip_locked?() click to toggle source

MSSQL uses READPAST to skip locked rows.

    # File lib/sequel/adapters/shared/mssql.rb
737 def supports_skip_locked?
738   true
739 end
supports_where_true?() click to toggle source

MSSQL cannot use WHERE 1.

    # File lib/sequel/adapters/shared/mssql.rb
747 def supports_where_true?
748   false
749 end
supports_window_functions?() click to toggle source

MSSQL 2005+ supports window functions

    # File lib/sequel/adapters/shared/mssql.rb
742 def supports_window_functions?
743   true
744 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
515 def with_mssql_unicode_strings(v)
516   clone(:mssql_unicode_strings=>v)
517 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
757 def _import(columns, values, opts=OPTS)
758   if opts[:return] == :primary_key && !@opts[:output]
759     output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts)
760   elsif @opts[:output]
761     statements = multi_insert_sql(columns, values)
762     @db.transaction(opts.merge(:server=>@opts[:server])) do
763       statements.map{|st| with_sql(st)}
764     end.first.map{|v| v.length == 1 ? v.values.first : v}
765   else
766     super
767   end
768 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
771 def aggregate_dataset
772   (options_overlap(Sequel::Dataset::COUNT_FROM_SELF_OPTS) && !options_overlap([:limit])) ? unordered.from_self : super
773 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
782 def compound_from_self
783   if @opts[:offset] && !@opts[:limit] && !is_2012_or_later?
784     clone(:limit=>LIMIT_ALL).from_self
785   elsif @opts[:order]  && !(@opts[:sql] || @opts[:limit] || @opts[:offset])
786     unordered
787   else
788     super
789   end
790 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
795 def check_not_limited!(type)
796   return if @opts[:skip_limit_check] && type != :truncate
797   raise Sequel::InvalidOperation, "Dataset##{type} not suppored on ordered, limited datasets" if opts[:order] && opts[:limit]
798   super if type == :truncate || @opts[:offset]
799 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
819 def default_timestamp_format
820   "'%Y-%m-%dT%H:%M:%S%N%z'"
821 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
830 def delete_from2_sql(sql)
831   if joined_dataset?
832     select_from_sql(sql)
833     select_join_sql(sql)
834   end
835 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
824 def delete_from_sql(sql)
825   sql << ' FROM '
826   source_list_append(sql, @opts[:from][0..0])
827 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
838 def delete_output_sql(sql)
839   output_sql(sql, :DELETED)
840 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
846 def emulate_function?(name)
847   name == :char_length || name == :trim
848 end
emulate_function_sql_append(sql, f) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
850 def emulate_function_sql_append(sql, f)
851   case f.name
852   when :char_length
853     literal_append(sql, SQL::Function.new(:len, Sequel.join([f.args.first, 'x'])) - 1)
854   when :trim
855     literal_append(sql, SQL::Function.new(:ltrim, SQL::Function.new(:rtrim, f.args.first)))
856   end
857 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
860 def emulate_offset_with_row_number?
861   super && !(is_2012_or_later? && @opts[:order])
862 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
866 def first_primary_key
867   @db.schema(self).map{|k, v| k if v[:primary_key] == true}.compact.first
868 end
insert_output_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
870 def insert_output_sql(sql)
871   output_sql(sql, :INSERTED)
872 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
802 def is_2005_or_later?
803   server_version >= 9000000
804 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
807 def is_2008_or_later?
808   server_version >= 10000000
809 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
812 def is_2012_or_later?
813   server_version >= 11000000
814 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
876 def join_type_sql(join_type)
877   case join_type
878   when :cross_apply
879     'CROSS APPLY'
880   when :outer_apply
881     'OUTER APPLY'
882   else
883     super
884   end
885 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
888 def literal_blob_append(sql, v)
889   sql << '0x' << v.unpack("H*").first
890 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
894 def literal_date(v)
895   v.strftime("'%Y%m%d'")
896 end
literal_false() click to toggle source

Use 0 for false on MSSQL

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

Use 1 for true on MSSQL

    # File lib/sequel/adapters/shared/mssql.rb
911 def literal_true
912   '1'
913 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
917 def multi_insert_sql_strategy
918   is_2008_or_later? ? :values : :union
919 end
non_sql_option?(key) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mssql.rb
921 def non_sql_option?(key)
922   super || key == :disable_insert_output || key == :mssql_unicode_strings
923 end
output_list_sql(sql, output) click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1018 def output_list_sql(sql, output)
1019   sql << " OUTPUT "
1020   column_list_append(sql, output[:select_list])
1021   if into = output[:into]
1022     sql << " INTO "
1023     identifier_append(sql, into)
1024     if column_list = output[:column_list]
1025       sql << ' ('
1026       source_list_append(sql, column_list)
1027       sql << ')'
1028     end
1029   end
1030 end
output_returning_sql(sql, type, values) click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1032 def output_returning_sql(sql, type, values)
1033   sql << " OUTPUT "
1034   if values.empty?
1035     literal_append(sql, SQL::ColumnAll.new(type))
1036   else
1037     values = values.map do |v|
1038       case v
1039       when SQL::AliasedExpression
1040         Sequel.qualify(type, v.expression).as(v.alias)
1041       else
1042         Sequel.qualify(type, v)
1043       end
1044     end
1045     column_list_append(sql, values)
1046   end
1047 end
output_sql(sql, type) click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1009 def output_sql(sql, type)
1010   return unless supports_output_clause?
1011   if output = @opts[:output]
1012     output_list_sql(sql, output)
1013   elsif values = @opts[:returning]
1014     output_returning_sql(sql, type, values)
1015   end
1016 end
select_into_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
925 def select_into_sql(sql)
926   if i = @opts[:into]
927     sql << " INTO "
928     identifier_append(sql, i)
929   end
930 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
934 def select_limit_sql(sql)
935   if l = @opts[:limit]
936     return if is_2012_or_later? && @opts[:order] && @opts[:offset]
937     shared_limit_sql(sql, l)
938   end
939 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
964 def select_lock_sql(sql)
965   lock = @opts[:lock]
966   skip_locked = @opts[:skip_locked]
967   nowait = @opts[:nowait]
968   for_update = lock == :update
969   dirty = lock == :dirty
970   lock_hint = for_update || dirty
971 
972   if lock_hint || skip_locked
973     sql << " WITH ("
974 
975     if lock_hint
976       sql << (for_update ? 'UPDLOCK' : 'NOLOCK')
977     end
978 
979     if skip_locked || nowait
980       sql << ', ' if lock_hint
981       sql << (skip_locked ? "READPAST" : "NOWAIT")
982     end
983 
984     sql << ')'
985   else
986     super
987   end
988 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
 992 def select_order_sql(sql)
 993   super
 994   if is_2012_or_later? && @opts[:order]
 995     if o = @opts[:offset]
 996       sql << " OFFSET "
 997       literal_append(sql, o)
 998       sql << " ROWS"
 999 
1000       if l = @opts[:limit]
1001         sql << " FETCH NEXT "
1002         literal_append(sql, l)
1003         sql << " ROWS ONLY"
1004       end
1005     end
1006   end
1007 end
shared_limit_sql(sql, l) click to toggle source
    # File lib/sequel/adapters/shared/mssql.rb
941 def shared_limit_sql(sql, l)
942   if is_2005_or_later?
943     if l == LIMIT_ALL
944       sql << " TOP (100) PERCENT"
945     else
946       sql << " TOP ("
947       literal_append(sql, l)
948       sql << ')'
949     end
950   else
951     sql << " TOP "
952     literal_append(sql, l)
953   end
954 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
1051 def sqltime_precision
1052   6
1053 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
1058 def timestamp_precision
1059   3
1060 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
956 def update_limit_sql(sql)
957   if l = @opts[:limit]
958     shared_limit_sql(sql, l)
959   end
960 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
1063 def update_table_sql(sql)
1064   sql << ' '
1065   source_list_append(sql, @opts[:from][0..0])
1066 end
uses_with_rollup?() click to toggle source
     # File lib/sequel/adapters/shared/mssql.rb
1068 def uses_with_rollup?
1069   !is_2008_or_later?
1070 end