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

disable_insert_output()

Disable the use of INSERT OUTPUT

[show source]

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

escape_like(string)

MSSQL treats [] as a metacharacter in LIKE expresions.

[show source]

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

into(table)

Specify a table for a SELECT … INTO query.

[show source]

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

nolock()

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

[show source]

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

quoted_identifier_append(sql, name)

MSSQL uses [] to quote identifiers.

[show source]

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

server_version()

The version of the database server.

[show source]

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

supports_cte?(type=:select)

[show source]

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

supports_group_cube?()

MSSQL 2005+ supports GROUP BY CUBE.

[show source]

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

supports_group_rollup?()

MSSQL 2005+ supports GROUP BY ROLLUP

[show source]

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

supports_grouping_sets?()

MSSQL 2008+ supports GROUPING SETS

[show source]

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

supports_insert_select?()

MSSQL supports insert_select via the OUTPUT clause.

[show source]

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

supports_intersect_except?()

MSSQL 2005+ supports INTERSECT and EXCEPT

[show source]

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

supports_is_true?()

MSSQL does not support IS TRUE

[show source]

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

supports_join_using?()

MSSQL doesn’t support JOIN USING

[show source]

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

supports_merge?()

MSSQL 2008+ supports MERGE

[show source]

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

supports_modifying_joins?()

MSSQL 2005+ supports modifying joined datasets

[show source]

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

supports_nowait?()

MSSQL supports NOWAIT.

[show source]

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

supports_offsets_in_correlated_subqueries?()

MSSQL 2012+ supports offsets in correlated subqueries.

[show source]

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

supports_output_clause?()

MSSQL 2005+ supports the OUTPUT clause.

[show source]

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

supports_returning?(type)

MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.

[show source]

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

supports_skip_locked?()

MSSQL uses READPAST to skip locked rows.

[show source]

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

supports_where_true?()

MSSQL cannot use WHERE 1.

[show source]

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

supports_window_functions?()

MSSQL 2005+ supports window functions

[show source]

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

module Sequel::MSSQL::DatasetMethods

Methods

Public Instance

Protected Instance

Included modules

Constants

Public Instance methods

Protected Instance methods