Sequel::MSSQL::DatasetMethods

Included modules

EmulateOffsetWithRowNumber

Constants

 CONSTANT_MAP
 EXTRACT_MAP
 LIMIT_ALL

Public Instance methods

complex_expression_sql_append(sql, op, args)

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
600 def complex_expression_sql_append(sql, op, args)
601   case op
602   when :'||'
603     super(sql, :+, args)
604   when :LIKE, :"NOT LIKE"
605     super(sql, op, complex_expression_sql_like_args(args, " COLLATE Latin1_General_CS_AS)"))
606   when :ILIKE, :"NOT ILIKE"
607     super(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), complex_expression_sql_like_args(args, " COLLATE Latin1_General_CI_AS)"))
608   when :<<, :>>
609     complex_expression_emulate_append(sql, op, args)
610   when :extract
611     part = args[0]
612     raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part]
613     if part == :second
614       expr = args[1]
615       sql << "CAST((datepart(" << format.to_s << ', '
616       literal_append(sql, expr)
617       sql << ') + datepart(ns, '
618       literal_append(sql, expr)
619       sql << ")/1000000000.0) AS double precision)"
620     else
621       sql << "datepart(" << format.to_s << ', '
622       literal_append(sql, args[1])
623       sql << ')'
624     end
625   else
626     super
627   end
628 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
631 def constant_sql_append(sql, constant)
632   if c = CONSTANT_MAP[constant]
633     sql << c
634   else
635     super
636   end
637 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
643 def count(*a, &block)
644   if (@opts[:sql] && a.empty? && !block)
645     naked.to_a.length
646   else
647     super
648   end
649 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
652 def cross_apply(table)
653   join_table(:cross_apply, table)
654 end

disable_insert_output()

Disable the use of INSERT OUTPUT

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
657 def disable_insert_output
658   clone(:disable_insert_output=>true)
659 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
665 def empty?
666   if @opts[:sql]
667     naked.each{return false}
668     true
669   else
670     super
671   end
672 end

escape_like(string)

MSSQL treats [] as a metacharacter in LIKE expresions.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
675 def escape_like(string)
676   string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"}
677 end

full_text_search(cols, terms, opts = OPTS)

MSSQL uses the CONTAINS keyword for full text search

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
680 def full_text_search(cols, terms, opts = OPTS)
681   terms = "\"#{terms.join('" OR "')}\"" if terms.is_a?(Array)
682   where(Sequel.lit("CONTAINS (?, ?)", cols, terms))
683 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
688 def insert_select(*values)
689   return unless supports_insert_select?
690   with_sql_first(insert_select_sql(*values)) || false
691 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
695 def insert_select_sql(*values)
696   ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)])
697   ds.insert_sql(*values)
698 end

into(table)

Specify a table for a SELECT … INTO query.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
701 def into(table)
702   clone(:into => table)
703 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
591 def mssql_unicode_strings
592   opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings
593 end

nolock()

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

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
706 def nolock
707   lock_style(:dirty)
708 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
711 def outer_apply(table)
712   join_table(:outer_apply, table)
713 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
727 def output(into, values)
728   raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause?
729   output = {}
730   case values
731   when Hash
732     output[:column_list], output[:select_list] = values.keys, values.values
733   when Array
734     output[:select_list] = values
735   end
736   output[:into] = into
737   clone(:output => output)
738 end

quoted_identifier_append(sql, name)

MSSQL uses [] to quote identifiers.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
741 def quoted_identifier_append(sql, name)
742   sql << '[' << name.to_s.gsub(/\]/, ']]') << ']'
743 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
746 def returning(*values)
747   values = values.map do |v|
748     unless r = unqualified_column_for(v)
749       raise(Error, "cannot emulate RETURNING via OUTPUT for value: #{v.inspect}")
750     end
751     r
752   end
753   clone(:returning=>values)
754 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
760 def select_sql
761   if @opts[:offset]
762     raise(Error, "Using with_ties is not supported with an offset on Microsoft SQL Server") if @opts[:limit_with_ties]
763     return order(1).select_sql if is_2012_or_later? && !@opts[:order]
764   end
765   super
766 end

server_version()

The version of the database server.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
769 def server_version
770   db.server_version(@opts[:server])
771 end

supports_cte?(type=:select)

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
773 def supports_cte?(type=:select)
774   is_2005_or_later?
775 end

supports_group_cube?()

MSSQL 2005+ supports GROUP BY CUBE.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
778 def supports_group_cube?
779   is_2005_or_later?
780 end

supports_group_rollup?()

MSSQL 2005+ supports GROUP BY ROLLUP

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
783 def supports_group_rollup?
784   is_2005_or_later?
785 end

supports_grouping_sets?()

MSSQL 2008+ supports GROUPING SETS

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
788 def supports_grouping_sets?
789   is_2008_or_later?
790 end

supports_insert_select?()

MSSQL supports insert_select via the OUTPUT clause.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
793 def supports_insert_select?
794   supports_output_clause? && !opts[:disable_insert_output]
795 end

supports_intersect_except?()

MSSQL 2005+ supports INTERSECT and EXCEPT

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
798 def supports_intersect_except?
799   is_2005_or_later?
800 end

supports_is_true?()

MSSQL does not support IS TRUE

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
803 def supports_is_true?
804   false
805 end

supports_join_using?()

MSSQL doesn’t support JOIN USING

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
808 def supports_join_using?
809   false
810 end

supports_merge?()

MSSQL 2008+ supports MERGE

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
813 def supports_merge?
814   is_2008_or_later?
815 end

supports_modifying_joins?()

MSSQL 2005+ supports modifying joined datasets

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
818 def supports_modifying_joins?
819   is_2005_or_later?
820 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
823 def supports_multiple_column_in?
824   false
825 end

supports_nowait?()

MSSQL supports NOWAIT.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
828 def supports_nowait?
829   true
830 end

supports_offsets_in_correlated_subqueries?()

MSSQL 2012+ supports offsets in correlated subqueries.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
833 def supports_offsets_in_correlated_subqueries?
834   is_2012_or_later?
835 end

supports_output_clause?()

MSSQL 2005+ supports the OUTPUT clause.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
838 def supports_output_clause?
839   is_2005_or_later?
840 end

supports_returning?(type)

MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
843 def supports_returning?(type)
844   supports_insert_select?
845 end

supports_skip_locked?()

MSSQL uses READPAST to skip locked rows.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
848 def supports_skip_locked?
849   true
850 end

supports_where_true?()

MSSQL cannot use WHERE 1.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
858 def supports_where_true?
859   false
860 end

supports_window_functions?()

MSSQL 2005+ supports window functions

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
853 def supports_window_functions?
854   true
855 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
596 def with_mssql_unicode_strings(v)
597   clone(:mssql_unicode_strings=>v)
598 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
864 def with_ties
865   clone(:limit_with_ties=>true)
866 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
874 def _import(columns, values, opts=OPTS)
875   if opts[:return] == :primary_key && !@opts[:output]
876     output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts)
877   elsif @opts[:output]
878     # no transaction: our multi_insert_sql_strategy should guarantee
879     # that there's only ever a single statement.
880     sql = multi_insert_sql(columns, values)[0]
881     naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v}
882   else
883     super
884   end
885 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
894 def compound_from_self
895   if @opts[:offset] && !@opts[:limit] && !is_2012_or_later?
896     clone(:limit=>LIMIT_ALL).from_self
897   elsif @opts[:order]  && !(@opts[:sql] || @opts[:limit] || @opts[:offset])
898     unordered
899   else
900     super
901   end
902 end

module Sequel::MSSQL::DatasetMethods

Methods

Public Instance

Protected Instance

Included modules

Constants

Public Instance methods

Protected Instance methods