module Sequel::MSSQL::DatasetMethods

  1. lib/sequel/adapters/shared/mssql.rb

Included modules

  1. EmulateOffsetWithRowNumber

Constants

CONSTANT_MAP = {:CURRENT_DATE=>'CAST(CURRENT_TIMESTAMP AS DATE)'.freeze, :CURRENT_TIME=>'CAST(CURRENT_TIMESTAMP AS TIME)'.freeze}.freeze  
EXTRACT_MAP = {:year=>"yy", :month=>"m", :day=>"d", :hour=>"hh", :minute=>"n", :second=>"s"}.freeze  
LIMIT_ALL = Object.new.freeze  

Public Instance methods

complex_expression_sql_append(sql, op, args)
[show source]
    # File lib/sequel/adapters/shared/mssql.rb
588 def complex_expression_sql_append(sql, op, args)
589   case op
590   when :'||'
591     super(sql, :+, args)
592   when :LIKE, :"NOT LIKE"
593     super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)"))
594   when :ILIKE, :"NOT ILIKE"
595     super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)"))
596   when :<<, :>>
597     complex_expression_emulate_append(sql, op, args)
598   when :extract
599     part = args[0]
600     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
601     if part == :second
602       expr = args[1]
603       sql << "CAST((datepart(" << format.to_s << ', '
604       literal_append(sql, expr)
605       sql << ') + datepart(ns, '
606       literal_append(sql, expr)
607       sql << ")/1000000000.0) AS double precision)"
608     else
609       sql << "datepart(" << format.to_s << ', '
610       literal_append(sql, args[1])
611       sql << ')'
612     end
613   else
614     super
615   end
616 end
constant_sql_append(sql, constant)

MSSQL doesn’t support the SQL standard CURRENT_DATE or CURRENT_TIME

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
619 def constant_sql_append(sql, constant)
620   if c = CONSTANT_MAP[constant]
621     sql << c
622   else
623     super
624   end
625 end
count(*a, &block)

For a dataset with custom SQL, since it may include ORDER BY, you cannot wrap it in a subquery. Load entire query in this case to get the number of rows. In general, you should avoid calling this method on datasets with custom SQL.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
631 def count(*a, &block)
632   if (@opts[:sql] && a.empty? && !block)
633     naked.to_a.length
634   else
635     super
636   end
637 end
cross_apply(table)

Uses CROSS APPLY to join the given table into the current dataset.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
640 def cross_apply(table)
641   join_table(:cross_apply, table)
642 end
disable_insert_output()

Disable the use of INSERT OUTPUT

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
645 def disable_insert_output
646   clone(:disable_insert_output=>true)
647 end
empty?()

For a dataset with custom SQL, since it may include ORDER BY, you cannot wrap it in a subquery. Run query, and if it returns any records, return true. In general, you should avoid calling this method on datasets with custom SQL.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
653 def empty?
654   if @opts[:sql]
655     naked.each{return false}
656     true
657   else
658     super
659   end
660 end
escape_like(string)

MSSQL treats [] as a metacharacter in LIKE expresions.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
663 def escape_like(string)
664   string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"}
665 end
insert_select(*values)

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.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
676 def insert_select(*values)
677   return unless supports_insert_select?
678   with_sql_first(insert_select_sql(*values)) || false
679 end
insert_select_sql(*values)

Add OUTPUT clause unless there is already an existing output clause, then return the SQL to insert.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
683 def insert_select_sql(*values)
684   ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)])
685   ds.insert_sql(*values)
686 end
into(table)

Specify a table for a SELECT … INTO query.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
689 def into(table)
690   clone(:into => table)
691 end
mssql_unicode_strings()

Use the database’s mssql_unicode_strings setting if the dataset hasn’t overridden it.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
579 def mssql_unicode_strings
580   opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings
581 end
nolock()

Allows you to do a dirty read of uncommitted data using WITH (NOLOCK).

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
694 def nolock
695   lock_style(:dirty)
696 end
outer_apply(table)

Uses OUTER APPLY to join the given table into the current dataset.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
699 def outer_apply(table)
700   join_table(:outer_apply, table)
701 end
output(into, values)

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])
[show source]
    # File lib/sequel/adapters/shared/mssql.rb
715 def output(into, values)
716   raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause?
717   output = {}
718   case values
719   when Hash
720     output[:column_list], output[:select_list] = values.keys, values.values
721   when Array
722     output[:select_list] = values
723   end
724   output[:into] = into
725   clone(:output => output)
726 end
quoted_identifier_append(sql, name)

MSSQL uses [] to quote identifiers.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
729 def quoted_identifier_append(sql, name)
730   sql << '[' << name.to_s.gsub(/\]/, ']]') << ']'
731 end
returning(*values)

Emulate RETURNING using the output clause. This only handles values that are simple column references.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
734 def returning(*values)
735   values = values.map do |v|
736     unless r = unqualified_column_for(v)
737       raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}")
738     end
739     r
740   end
741   clone(:returning=>values)
742 end
select_sql()

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.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
748 def select_sql
749   if @opts[:offset]
750     raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties]
751     return order(1).select_sql if is_2012_or_later? && !@opts[:order]
752   end
753   super
754 end
server_version()

The version of the database server.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
757 def server_version
758   db.server_version(@opts[:server])
759 end
supports_cte?(type=:select)
[show source]
    # File lib/sequel/adapters/shared/mssql.rb
761 def supports_cte?(type=:select)
762   is_2005_or_later?
763 end
supports_group_cube?()

MSSQL 2005+ supports GROUP BY CUBE.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
766 def supports_group_cube?
767   is_2005_or_later?
768 end
supports_group_rollup?()

MSSQL 2005+ supports GROUP BY ROLLUP

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
771 def supports_group_rollup?
772   is_2005_or_later?
773 end
supports_grouping_sets?()

MSSQL 2008+ supports GROUPING SETS

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
776 def supports_grouping_sets?
777   is_2008_or_later?
778 end
supports_insert_select?()

MSSQL supports insert_select via the OUTPUT clause.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
781 def supports_insert_select?
782   supports_output_clause? && !opts[:disable_insert_output]
783 end
supports_intersect_except?()

MSSQL 2005+ supports INTERSECT and EXCEPT

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
786 def supports_intersect_except?
787   is_2005_or_later?
788 end
supports_is_true?()

MSSQL does not support IS TRUE

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
791 def supports_is_true?
792   false
793 end
supports_join_using?()

MSSQL doesn’t support JOIN USING

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
796 def supports_join_using?
797   false
798 end
supports_merge?()

MSSQL 2008+ supports MERGE

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
801 def supports_merge?
802   is_2008_or_later?
803 end
supports_modifying_joins?()

MSSQL 2005+ supports modifying joined datasets

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
806 def supports_modifying_joins?
807   is_2005_or_later?
808 end
supports_multiple_column_in?()

MSSQL does not support multiple columns for the IN/NOT IN operators

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
811 def supports_multiple_column_in?
812   false
813 end
supports_nowait?()

MSSQL supports NOWAIT.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
816 def supports_nowait?
817   true
818 end
supports_offsets_in_correlated_subqueries?()

MSSQL 2012+ supports offsets in correlated subqueries.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
821 def supports_offsets_in_correlated_subqueries?
822   is_2012_or_later?
823 end
supports_output_clause?()

MSSQL 2005+ supports the OUTPUT clause.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
826 def supports_output_clause?
827   is_2005_or_later?
828 end
supports_returning?(type)

MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
831 def supports_returning?(type)
832   supports_insert_select?
833 end
supports_skip_locked?()

MSSQL uses READPAST to skip locked rows.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
836 def supports_skip_locked?
837   true
838 end
supports_where_true?()

MSSQL cannot use WHERE 1.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
846 def supports_where_true?
847   false
848 end
supports_window_functions?()

MSSQL 2005+ supports window functions

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
841 def supports_window_functions?
842   true
843 end
with_mssql_unicode_strings(v)

Return a cloned dataset with the mssql_unicode_strings option set.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
584 def with_mssql_unicode_strings(v)
585   clone(:mssql_unicode_strings=>v)
586 end
with_ties()

Use WITH TIES when limiting the result set to also include additional rows matching the last row.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
852 def with_ties
853   clone(:limit_with_ties=>true)
854 end

Protected Instance methods

_import(columns, values, opts=OPTS)

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.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
862 def _import(columns, values, opts=OPTS)
863   if opts[:return] == :primary_key && !@opts[:output]
864     output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts)
865   elsif @opts[:output]
866     # no transaction: our multi_insert_sql_strategy should guarantee
867     # that there's only ever a single statement.
868     sql = multi_insert_sql(columns, values)[0]
869     naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v}
870   else
871     super
872   end
873 end
compound_from_self()

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.

[show source]
    # File lib/sequel/adapters/shared/mssql.rb
882 def compound_from_self
883   if @opts[:offset] && !@opts[:limit] && !is_2012_or_later?
884     clone(:limit=>LIMIT_ALL).from_self
885   elsif @opts[:order]  && !(@opts[:sql] || @opts[:limit] || @opts[:offset])
886     unordered
887   else
888     super
889   end
890 end