module ArelExtensions::Visitors::MSSQL

Constants

LOADED_VISITOR

Public Instance Methods

old_visit_Arel_Nodes_As(o, collector)
Alias for: visit_Arel_Nodes_As
visit_ArelExtensions_Nodes_AiIMatches(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 407
def visit_ArelExtensions_Nodes_AiIMatches o, collector
  collector = visit o.left.collate(true, true), collector
  collector << ' LIKE '
  collector = visit o.right.collate(true, true), collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end
visit_ArelExtensions_Nodes_AiMatches(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 395
def visit_ArelExtensions_Nodes_AiMatches o, collector
  collector = visit o.left.ai_collate, collector
  collector << ' LIKE '
  collector = visit o.right.ai_collate, collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end
visit_ArelExtensions_Nodes_Blank(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 265
def visit_ArelExtensions_Nodes_Blank o, collector
  visit o.expr.coalesce('').trim.length.eq(0), collector
end
visit_ArelExtensions_Nodes_Cast(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 513
def visit_ArelExtensions_Nodes_Cast o, collector
  as_attr =
    case o.as_attr
    when :string
      'varchar'
    when :time
      'time'
    when :date
      'date'
    when :datetime
      'datetime'
    when :number, :decimal, :float
      'decimal(10,6)'
    when :int
      collector << 'CAST(CAST('
      collector = visit o.left, collector
      collector << ' AS decimal(10,0)) AS int)'
      return collector
    when :binary
      'binary'
    else
      o.as_attr.to_s
    end
  collector << 'CAST('
  collector = visit o.left, collector
  collector << ' AS '
  collector = visit Arel::Nodes::SqlLiteral.new(as_attr), collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Ceil(o, collector) click to toggle source

Math Functions

# File lib/arel_extensions/visitors/mssql.rb, line 53
def visit_ArelExtensions_Nodes_Ceil o, collector
  collector << 'CEILING('
  collector = visit o.expr, collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Collate(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 431
def visit_ArelExtensions_Nodes_Collate o, collector
  if o.ai && o.ci
    collector = visit o.expressions.first, collector
    collector << ' COLLATE Latin1_General_CI_AI'
  elsif o.ai
    collector = visit o.expressions.first, collector
    collector << ' COLLATE Latin1_General_CS_AI'
  elsif o.ci
    collector = visit o.expressions.first, collector
    collector << ' COLLATE Latin1_General_CI_AS'
  else
    collector = visit o.expressions.first, collector
    collector << ' COLLATE Latin1_General_CS_AS'
  end
  collector
end
visit_ArelExtensions_Nodes_Concat(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 94
def visit_ArelExtensions_Nodes_Concat o, collector
  collector << 'CONCAT('
  o.expressions.each_with_index { |arg, i|
    collector << LOADED_VISITOR::COMMA if i != 0
    collector = visit arg, collector
  }
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_DateAdd(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 144
def visit_ArelExtensions_Nodes_DateAdd o, collector
  collector << 'DATEADD('
  collector = visit o.mssql_datepart(o.right), collector
  collector << LOADED_VISITOR::COMMA
  collector = visit o.mssql_value(o.right), collector
  collector << LOADED_VISITOR::COMMA
  collector = visit o.left, collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_DateDiff(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 116
def visit_ArelExtensions_Nodes_DateDiff o, collector
  case o.right_node_type
  when :ruby_date, :ruby_time, :date, :datetime, :time
    collector << case o.left_node_type
                when :ruby_time, :datetime, :time then 'DATEDIFF(second'
                else                                   'DATEDIFF(day'
                end
    collector << LOADED_VISITOR::COMMA
    collector = visit o.right, collector
    collector << LOADED_VISITOR::COMMA
    collector = visit o.left, collector
    collector << ')'
  else
    da = ArelExtensions::Nodes::DateAdd.new([])
    collector << 'DATEADD('
    collector = visit da.mssql_datepart(o.right), collector
    collector << LOADED_VISITOR::COMMA
    collector << '-('
    collector = visit da.mssql_value(o.right), collector
    collector << ')'
    collector << LOADED_VISITOR::COMMA
    collector = visit o.left, collector
    collector << ')'
    collector
  end
  collector
end
visit_ArelExtensions_Nodes_Duration(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 155
def visit_ArelExtensions_Nodes_Duration o, collector
  if o.with_interval && o.left.end_with?('i')
    collector = visit o.right, collector
  else
    left = o.left.end_with?('i') ? o.left[0..-2] : o.left
    conv = ['h', 'mn', 's'].include?(o.left)
    collector << 'DATEPART('
    collector << LOADED_VISITOR::DATE_MAPPING[left]
    collector << LOADED_VISITOR::COMMA
    collector << 'CONVERT(datetime,' if conv
    collector = visit o.right, collector
    collector << ')' if conv
    collector << ')'
  end
  collector
end
visit_ArelExtensions_Nodes_FindInSet(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 359
def visit_ArelExtensions_Nodes_FindInSet o, collector
  collector << 'dbo.FIND_IN_SET('
  o.expressions.each_with_index { |arg, i|
    collector << LOADED_VISITOR::COMMA if i != 0
    collector = visit arg, collector
  }
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Format(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 273
def visit_ArelExtensions_Nodes_Format o, collector
  f = ArelExtensions::Visitors::strftime_to_format(o.iso_format, LOADED_VISITOR::DATE_FORMAT_DIRECTIVES)
  if fmt = LOADED_VISITOR::DATE_CONVERT_FORMATS[f]
    collector << "CONVERT(VARCHAR(#{f.length})"
    collector << LOADED_VISITOR::COMMA
    if o.time_zone
      collector << 'CONVERT(datetime'
      collector << LOADED_VISITOR::COMMA
      collector << ' '
    end
    collector = visit o.left, collector
    case o.time_zone
    when Hash
      src_tz, dst_tz = o.time_zone.first
      collector << ') AT TIME ZONE '
      collector = visit Arel.quoted(src_tz), collector
      collector << ' AT TIME ZONE '
      collector = visit Arel.quoted(dst_tz), collector
    when String
      collector << ') AT TIME ZONE '
      collector = visit Arel.quoted(o.time_zone), collector
    end
    collector << LOADED_VISITOR::COMMA
    collector << fmt.to_s
    collector << ')'
    collector
  else
    s = StringScanner.new o.iso_format
    collector << '('
    sep = ''
    while !s.eos?
      collector << sep
      sep = ' + '
      case
      when s.scan(LOADED_VISITOR::DATE_FORMAT_REGEX)
        dir = LOADED_VISITOR::DATE_FORMAT_DIRECTIVES[s.matched]
        fmt = LOADED_VISITOR::DATE_FORMAT_FORMAT[dir]
        date_name = LOADED_VISITOR::DATE_NAME.include?(s.matched)
        collector << 'TRIM('
        collector << 'FORMAT(' if fmt
        collector << 'STR('    if !fmt && !date_name
        collector << (date_name ? 'DATENAME(' : 'DATEPART(')
        collector << dir
        collector << LOADED_VISITOR::COMMA
        if o.time_zone
          collector << 'CONVERT(datetime'
          collector << LOADED_VISITOR::COMMA
          collector << ' '
        end
        collector = visit o.left, collector
        case o.time_zone
        when Hash
          src_tz, dst_tz = o.time_zone.first.first, o.time_zone.first.second
          collector << ') AT TIME ZONE '
          collector = visit Arel.quoted(src_tz), collector
          collector << ' AT TIME ZONE '
          collector = visit Arel.quoted(dst_tz), collector
        when String
          collector << ') AT TIME ZONE '
          collector = visit Arel.quoted(o.time_zone), collector
        end
        collector << ')'
        collector << ')'                                  if !fmt && !date_name
        collector << LOADED_VISITOR::COMMA << "'#{fmt}')" if fmt
        collector << ')'
      when s.scan(/^%%/)
        collector = visit Arel.quoted('%'), collector
      when s.scan(/[^%]+|./)
        collector = visit Arel.quoted(s.matched), collector
      end
    end
    collector << ')'
    collector
  end
end
visit_ArelExtensions_Nodes_FormattedNumber(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 544
def visit_ArelExtensions_Nodes_FormattedNumber o, collector
  col = o.left.coalesce(0)
  locale = Arel.quoted(o.locale.tr('_', '-'))
  param = Arel.quoted("N#{o.precision}")
  sign = Arel.when(col < 0).
            then('-').
            else(o.flags.include?('+') ? '+' : (o.flags.include?(' ') ? ' ' : ''))
  sign_length = o.flags.include?('+') || o.flags.include?(' ') ?
        Arel.quoted(1) :
        Arel.when(col < 0).then(1).else(0)

  number =
    if o.scientific_notation
      ArelExtensions::Nodes::Concat.new([
            Arel::Nodes::NamedFunction.new('FORMAT', [
              col.abs / Arel.quoted(10).pow(col.abs.log10.floor),
              param,
              locale
            ]),
            o.type,
            Arel::Nodes::NamedFunction.new('FORMAT', [
              col.abs.log10.floor,
              Arel.quoted('N0'),
              locale
            ])
          ])
    else
      Arel::Nodes::NamedFunction.new('FORMAT', [
          Arel.quoted(col.abs),
          param,
          locale
        ])
    end

  repeated_char = (o.width == 0) ? Arel.quoted('') : ArelExtensions::Nodes::Case.new.
    when(Arel.quoted(o.width).abs - (number.length + sign_length) > 0).
    then(Arel.quoted(
        o.flags.include?('-') ? ' ' : (o.flags.include?('0') ? '0' : ' ')
      ).repeat(Arel.quoted(o.width).abs - (number.length + sign_length))
    ).
    else('')
  before = (!o.flags.include?('0')) && (!o.flags.include?('-')) ? repeated_char : ''
  middle = (o.flags.include?('0')) && (!o.flags.include?('-'))  ? repeated_char : ''
  after  = o.flags.include?('-') ? repeated_char : ''
  full_number =
    ArelExtensions::Nodes::Concat.new([
      before,
      sign,
      middle,
      number,
      after
    ])
  collector = visit ArelExtensions::Nodes::Concat.new([Arel.quoted(o.prefix), full_number, Arel.quoted(o.suffix)]), collector
  collector
end
visit_ArelExtensions_Nodes_GroupConcat(o, collector) click to toggle source

TODO;

# File lib/arel_extensions/visitors/mssql.rb, line 483
def visit_ArelExtensions_Nodes_GroupConcat o, collector
  collector << '(STRING_AGG('
  collector = visit o.left, collector
  collector << Arel::Visitors::Oracle::COMMA
  collector =
    if o.separator && o.separator != 'NULL'
      visit o.separator, collector
    else
      visit Arel.quoted(','), collector
    end
  collector << ') WITHIN GROUP (ORDER BY '
  if o.order.present?
    o.order.each_with_index do |order, i|
      collector << Arel::Visitors::Oracle::COMMA if i != 0
      collector = visit order, collector
    end
  else
    collector = visit o.left, collector
  end
  collector << '))'
  collector
end
visit_ArelExtensions_Nodes_IDoesNotMatch(o, collector) click to toggle source

TODO; manage case insensitivity

# File lib/arel_extensions/visitors/mssql.rb, line 383
def visit_ArelExtensions_Nodes_IDoesNotMatch o, collector
  collector = visit o.left.ci_collate, collector
  collector << ' NOT LIKE '
  collector = visit o.right.ci_collate, collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end
visit_ArelExtensions_Nodes_IMatches(o, collector) click to toggle source

TODO; manage case insensitivity

# File lib/arel_extensions/visitors/mssql.rb, line 370
def visit_ArelExtensions_Nodes_IMatches o, collector
  collector = visit o.left.ci_collate, collector
  collector << ' LIKE '
  collector = visit o.right.ci_collate, collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end
visit_ArelExtensions_Nodes_IsNotNull(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 87
def visit_ArelExtensions_Nodes_IsNotNull o, collector
  collector << '('
    collector = visit o.expr, collector
    collector << ' IS NOT NULL)'
    collector
end
visit_ArelExtensions_Nodes_IsNull(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 80
def visit_ArelExtensions_Nodes_IsNull o, collector
  collector << '('
  collector = visit o.expr, collector
  collector << ' IS NULL)'
  collector
end
visit_ArelExtensions_Nodes_JsonGet(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 625
def visit_ArelExtensions_Nodes_JsonGet o, collector
  collector << 'JSON_VALUE('
  collector = visit o.dict, collector
  collector << Arel::Visitors::MySQL::COMMA
  if o.key.is_a?(Integer)
    collector << "\"$[#{o.key}]\""
  else
    collector = visit Arel.quoted('$.') + o.key, collector
  end
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Length(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 172
def visit_ArelExtensions_Nodes_Length o, collector
  if o.bytewise
    collector << '(DATALENGTH('
    collector = visit o.expr, collector
    collector << ') / ISNULL(NULLIF(DATALENGTH(LEFT(COALESCE('
    collector = visit o.expr, collector
    collector << ", '#' ), 1 )), 0), 1))"
    collector
  else
    collector << 'LEN('
    collector = visit o.expr, collector
    collector << ')'
    collector
  end
end
visit_ArelExtensions_Nodes_LevenshteinDistance(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 615
def visit_ArelExtensions_Nodes_LevenshteinDistance o, collector
  collector << 'dbo.LEVENSHTEIN_DISTANCE('
  collector = visit o.left, collector
  collector << Arel::Visitors::ToSql::COMMA
  collector = visit o.right, collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Locate(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 202
def visit_ArelExtensions_Nodes_Locate o, collector
  collector << 'CHARINDEX('
  collector = visit o.right, collector
  collector << LOADED_VISITOR::COMMA
  collector = visit o.left, collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Log10(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 60
def visit_ArelExtensions_Nodes_Log10 o, collector
  collector << 'LOG10('
    o.expressions.each_with_index { |arg, i|
      collector << Arel::Visitors::ToSql::COMMA if i != 0
      collector = visit arg, collector
    }
    collector << ')'
    collector
end
visit_ArelExtensions_Nodes_Ltrim(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 231
def visit_ArelExtensions_Nodes_Ltrim o, collector
  if o.right
    collector << 'REPLACE(REPLACE(LTRIM(REPLACE(REPLACE('
    collector = visit o.left, collector
    collector << ", ' ', '~'), "
    collector = visit o.right, collector
    collector << ", ' ')), ' ', "
    collector = visit o.right, collector
    collector << "), '~', ' ')"
  else
    collector << 'LTRIM('
    collector = visit o.left, collector
    collector << ')'
  end
  collector
end
visit_ArelExtensions_Nodes_MD5(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 506
def visit_ArelExtensions_Nodes_MD5 o, collector
  collector << "LOWER(CONVERT(NVARCHAR(32),HashBytes('MD5',CONVERT(VARCHAR,"
  collector = visit o.left, collector
  collector << ')),2))'
  collector
end
visit_ArelExtensions_Nodes_NotBlank(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 269
def visit_ArelExtensions_Nodes_NotBlank o, collector
  visit o.expr.coalesce('').trim.length.gt(0), collector
end
visit_ArelExtensions_Nodes_Power(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 70
def visit_ArelExtensions_Nodes_Power o, collector
  collector << 'POWER('
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::ToSql::COMMA if i != 0
    collector = visit arg, collector
  }
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Repeat(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 104
def visit_ArelExtensions_Nodes_Repeat o, collector
  collector << 'REPLICATE('
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::ToSql::COMMA if i != 0
    collector = visit arg, collector
  }
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Replace(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 349
def visit_ArelExtensions_Nodes_Replace o, collector
  collector << 'REPLACE('
  o.expressions.each_with_index { |arg, i|
    collector << LOADED_VISITOR::COMMA if i != 0
    collector = visit arg, collector
  }
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Round(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 188
def visit_ArelExtensions_Nodes_Round o, collector
  collector << 'ROUND('
  o.expressions.each_with_index { |arg, i|
    collector << LOADED_VISITOR::COMMA if i != 0
    collector = visit arg, collector
  }
  if o.expressions.length == 1
    collector << LOADED_VISITOR::COMMA
    collector << '0'
  end
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Rtrim(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 248
def visit_ArelExtensions_Nodes_Rtrim o, collector
  if o.right
    collector << 'REPLACE(REPLACE(RTRIM(REPLACE(REPLACE('
    collector = visit o.left, collector
    collector << ", ' ', '~'), "
    collector = visit o.right, collector
    collector << ", ' ')), ' ', "
    collector = visit o.right, collector
    collector << "), '~', ' ')"
  else
    collector << 'RTRIM('
    collector = visit o.left, collector
    collector << ')'
  end
  collector
end
visit_ArelExtensions_Nodes_SMatches(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 419
def visit_ArelExtensions_Nodes_SMatches o, collector
  collector = visit o.left.collate, collector
  collector << ' LIKE '
  collector = visit o.right.collate, collector
  if o.escape
    collector << ' ESCAPE '
    visit o.escape, collector
  else
    collector
  end
end
visit_ArelExtensions_Nodes_Std(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 600
def visit_ArelExtensions_Nodes_Std o, collector
  collector << (o.unbiased_estimator ? 'STDEV(' : 'STDEVP(')
  visit o.left, collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Substring(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 211
def visit_ArelExtensions_Nodes_Substring o, collector
  collector << 'SUBSTRING('
  collector = visit o.expressions[0], collector
  collector << LOADED_VISITOR::COMMA
  collector = visit o.expressions[1], collector
  collector << LOADED_VISITOR::COMMA
  collector = o.expressions[2] ? visit(o.expressions[2], collector) : visit(o.expressions[0].length, collector)
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Trim(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 222
def visit_ArelExtensions_Nodes_Trim o, collector
  collector << 'TRIM( '
  collector = visit o.right, collector
  collector << ' FROM '
  collector = visit o.left, collector
  collector << ')'
  collector
end
visit_ArelExtensions_Nodes_Variance(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 607
def visit_ArelExtensions_Nodes_Variance o, collector
  collector << (o.unbiased_estimator ? 'VAR(' : 'VARP(')
  visit o.left, collector
  collector << ')'
  collector
end
visit_Arel_Nodes_As(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 449
def visit_Arel_Nodes_As o, collector
  if o.left.is_a?(Arel::Nodes::Binary)
    collector << '('
    collector = visit o.left, collector
    collector << ')'
  else
    collector = visit o.left, collector
  end
  collector << ' AS '

  # sometimes these values are already quoted, if they are, don't double quote it
  quote = o.right.is_a?(Arel::Nodes::SqlLiteral) && o.right[0] != '"' && o.right[-1] != '"'

  collector << '"' if quote
  collector = visit o.right, collector
  collector << '"' if quote

  collector
end
Also aliased as: old_visit_Arel_Nodes_As
visit_Arel_Nodes_NotRegexp(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 476
def visit_Arel_Nodes_NotRegexp o, collector
  collector = visit o.left, collector
  collector << "NOT LIKE '%#{o.right}%'"
  collector
end
visit_Arel_Nodes_Regexp(o, collector) click to toggle source

SQL Server does not know about REGEXP

# File lib/arel_extensions/visitors/mssql.rb, line 470
def visit_Arel_Nodes_Regexp o, collector
  collector = visit o.left, collector
  collector << "LIKE '%#{o.right}%'"
  collector
end