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

disable_insert_output()

Disable the use of INSERT OUTPUT

[show source]

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

escape_like(string)

MSSQL treats [] as a metacharacter in LIKE expressions.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
679 def escape_like(string)
680   string.gsub(/[\\%_\[\]]/){|m| "\\#{m}"}
681 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
684 def full_text_search(cols, terms, opts = OPTS)
685   case terms
686   when Array, Set
687     terms = Sequel.array_or_set_join(terms.map{|term| "\"#{term.to_s.gsub('"', '""')}\""}, " OR ")
688   end
689   where(Sequel.lit("CONTAINS (?, ?)", cols, terms))
690 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
695 def insert_select(*values)
696   return unless supports_insert_select?
697   with_sql_first(insert_select_sql(*values)) || false
698 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
702 def insert_select_sql(*values)
703   ds = (opts[:output] || opts[:returning]) ? self : output(nil, [SQL::ColumnAll.new(:inserted)])
704   ds.insert_sql(*values)
705 end

into(table)

Specify a table for a SELECT … INTO query.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
708 def into(table)
709   clone(:into => table)
710 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
595 def mssql_unicode_strings
596   opts.has_key?(:mssql_unicode_strings) ? opts[:mssql_unicode_strings] : db.mssql_unicode_strings
597 end

nolock()

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

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
713 def nolock
714   cached_lock_style_dataset(:_nolock_ds, :dirty)
715 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
718 def outer_apply(table)
719   join_table(:outer_apply, table)
720 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
734 def output(into, values)
735   raise(Error, "SQL Server versions 2000 and earlier do not support the OUTPUT clause") unless supports_output_clause?
736   output = {}
737   case values
738   when Hash
739     output[:column_list], output[:select_list] = values.keys, values.values
740   when Array
741     output[:select_list] = values
742   end
743   output[:into] = into
744   clone(:output => output)
745 end

quoted_identifier_append(sql, name)

MSSQL uses [] to quote identifiers.

[show source]

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

server_version()

The version of the database server.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
776 def server_version
777   db.server_version(@opts[:server])
778 end

supports_cte?(type=:select)

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
780 def supports_cte?(type=:select)
781   is_2005_or_later?
782 end

supports_group_cube?()

MSSQL 2005+ supports GROUP BY CUBE.

[show source]

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

supports_group_rollup?()

MSSQL 2005+ supports GROUP BY ROLLUP

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
790 def supports_group_rollup?
791   is_2005_or_later?
792 end

supports_grouping_sets?()

MSSQL 2008+ supports GROUPING SETS

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
795 def supports_grouping_sets?
796   is_2008_or_later?
797 end

supports_insert_select?()

MSSQL supports insert_select via the OUTPUT clause.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
800 def supports_insert_select?
801   supports_output_clause? && !opts[:disable_insert_output]
802 end

supports_intersect_except?()

MSSQL 2005+ supports INTERSECT and EXCEPT

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
805 def supports_intersect_except?
806   is_2005_or_later?
807 end

supports_is_true?()

MSSQL does not support IS TRUE

[show source]

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

supports_join_using?()

MSSQL doesn’t support JOIN USING

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
815 def supports_join_using?
816   false
817 end

supports_merge?()

MSSQL 2008+ supports MERGE

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
820 def supports_merge?
821   is_2008_or_later?
822 end

supports_modifying_joins?()

MSSQL 2005+ supports modifying joined datasets

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
825 def supports_modifying_joins?
826   is_2005_or_later?
827 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
830 def supports_multiple_column_in?
831   false
832 end

supports_nowait?()

MSSQL supports NOWAIT.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
835 def supports_nowait?
836   true
837 end

supports_offsets_in_correlated_subqueries?()

MSSQL 2012+ supports offsets in correlated subqueries.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
840 def supports_offsets_in_correlated_subqueries?
841   is_2012_or_later?
842 end

supports_output_clause?()

MSSQL 2005+ supports the OUTPUT clause.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
845 def supports_output_clause?
846   is_2005_or_later?
847 end

supports_returning?(type)

MSSQL 2005+ can emulate RETURNING via the OUTPUT clause.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
850 def supports_returning?(type)
851   supports_insert_select?
852 end

supports_skip_locked?()

MSSQL uses READPAST to skip locked rows.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
855 def supports_skip_locked?
856   true
857 end

supports_where_true?()

MSSQL cannot use WHERE 1.

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
865 def supports_where_true?
866   false
867 end

supports_window_functions?()

MSSQL 2005+ supports window functions

[show source]

    # File lib/sequel/adapters/shared/mssql.rb
860 def supports_window_functions?
861   true
862 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
600 def with_mssql_unicode_strings(v)
601   clone(:mssql_unicode_strings=>v)
602 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
871 def with_ties
872   clone(:limit_with_ties=>true)
873 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
881 def _import(columns, values, opts=OPTS)
882   if opts[:return] == :primary_key && !@opts[:output]
883     output(nil, [SQL::QualifiedIdentifier.new(:inserted, first_primary_key)])._import(columns, values, opts)
884   elsif @opts[:output]
885     # no transaction: our multi_insert_sql_strategy should guarantee
886     # that there's only ever a single statement.
887     sql = multi_insert_sql(columns, values)[0].freeze
888     naked.with_sql(sql).map{|v| v.length == 1 ? v.values.first : v}
889   else
890     super
891   end
892 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
901 def compound_from_self
902   if @opts[:offset] && !@opts[:limit] && !is_2012_or_later?
903     clone(:limit=>LIMIT_ALL).from_self
904   elsif @opts[:order]  && !(@opts[:sql] || @opts[:limit] || @opts[:offset])
905     unordered
906   else
907     super
908   end
909 end

module Sequel::MSSQL::DatasetMethods

Methods

Public Instance

Protected Instance

Included modules

Constants

Public Instance methods

Protected Instance methods