module ArelExtensions::Visitors::MSSQL

Public Instance Methods

visit_ArelExtensions_Nodes_AiIMatches(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 349
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 337
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 249
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 434
def visit_ArelExtensions_Nodes_Cast o, collector
  case o.as_attr
  when :string
    as_attr = Arel::Nodes::SqlLiteral.new('varchar')
  when :time
    as_attr = Arel::Nodes::SqlLiteral.new('time')
  when :date
    as_attr = Arel::Nodes::SqlLiteral.new('date')
  when :datetime
    as_attr = Arel::Nodes::SqlLiteral.new('datetime')
  when :number,:decimal, :float
    as_attr = Arel::Nodes::SqlLiteral.new('decimal(10,6)')
  when :int
    collector << "CAST(CAST("
    collector = visit o.left, collector
    collector << " AS decimal(10,0)) AS int)"
    return collector
  when :binary
    as_attr = Arel::Nodes::SqlLiteral.new('binary')
  else
    as_attr = Arel::Nodes::SqlLiteral.new(o.as_attr.to_s)
  end
  collector << "CAST("
  collector = visit o.left, collector
  collector << " AS "
  collector = visit 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 38
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 373
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 79
def visit_ArelExtensions_Nodes_Concat o, collector
  collector << "CONCAT("
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::MSSQL::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 129
def visit_ArelExtensions_Nodes_DateAdd o, collector
  collector << "DATEADD("
  collector = visit o.mssql_datepart(o.right), collector
  collector << Arel::Visitors::MSSQL::COMMA
  collector = visit o.mssql_value(o.right), collector
  collector << Arel::Visitors::MSSQL::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 101
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 << Arel::Visitors::MSSQL::COMMA
    collector = visit o.right, collector
    collector << Arel::Visitors::MSSQL::COMMA
    collector = visit o.left, collector
    collector << ')'
  else
    da = ArelExtensions::Nodes::DateAdd.new([])
    collector << "DATEADD("
    collector = visit da.mssql_datepart(o.right), collector
    collector << Arel::Visitors::MSSQL::COMMA
    collector << "-("
    collector = visit da.mssql_value(o.right), collector
    collector << ")"
    collector << Arel::Visitors::MSSQL::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 140
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 << Arel::Visitors::MSSQL::DATE_MAPPING[left]
    collector << Arel::Visitors::MSSQL::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 301
def visit_ArelExtensions_Nodes_FindInSet o, collector
  collector << "dbo.FIND_IN_SET("
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::MSSQL::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 257
def visit_ArelExtensions_Nodes_Format o, collector
  f = ArelExtensions::Visitors::strftime_to_format(o.iso_format, Arel::Visitors::MSSQL::DATE_FORMAT_DIRECTIVES)
  if fmt = Arel::Visitors::MSSQL::DATE_CONVERT_FORMATS[f]
    collector << "CONVERT(VARCHAR(#{f.length})"
    collector << Arel::Visitors::MSSQL::COMMA
    collector = visit o.left, collector
    collector << Arel::Visitors::MSSQL::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(Arel::Visitors::MSSQL::DATE_FORMAT_REGEX)
        dir = Arel::Visitors::MSSQL::DATE_FORMAT_DIRECTIVES[s.matched]
        collector << 'LTRIM(STR(DATEPART('
        collector << dir
        collector << Arel::Visitors::MSSQL::COMMA
        collector = visit o.left, collector
        collector << ')))'
      when s.scan(/[^%]+|./)
        collector = visit Arel::Nodes.build_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 464
def visit_ArelExtensions_Nodes_FormattedNumber o, collector
  col = o.left.coalesce(0)
  locale = Arel::Nodes.build_quoted(o.locale.tr('_','-'))
  param = Arel::Nodes.build_quoted("N#{o.precision}")
  sign = ArelExtensions::Nodes::Case.new.when(col<0).
            then('-').
            else(o.flags.include?('+') ? '+' : (o.flags.include?(' ') ? ' ' : ''))
  sign_length = o.flags.include?('+') || o.flags.include?(' ') ?
        Arel::Nodes.build_quoted(1) :
        ArelExtensions::Nodes::Case.new.when(col<0).then(1).else(0)

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

  repeated_char = (o.width == 0) ? Arel::Nodes.build_quoted('') : ArelExtensions::Nodes::Case.new().
    when(Arel::Nodes.build_quoted(o.width).abs-(number.length+sign_length)>0).
    then(Arel::Nodes.build_quoted(
        o.flags.include?('-') ? ' ' : (o.flags.include?('0') ? '0' : ' ')
      ).repeat(Arel::Nodes.build_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::Nodes.build_quoted(o.prefix),full_number,Arel::Nodes.build_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 404
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::Nodes.build_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 325
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 312
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 72
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 65
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 545
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::Nodes.build_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 157
def visit_ArelExtensions_Nodes_Length o, collector
  collector << "#{o.bytewise ? 'DATALENGTH' : 'LEN'}("
  collector = visit o.expr, collector
  collector << ")"
  collector
end
visit_ArelExtensions_Nodes_LevenshteinDistance(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 535
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 178
def visit_ArelExtensions_Nodes_Locate o, collector
  collector << "CHARINDEX("
  collector = visit o.right, collector
  collector << Arel::Visitors::MSSQL::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 45
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 215
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 427
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 253
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 55
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 89
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 291
def visit_ArelExtensions_Nodes_Replace o, collector
  collector << "REPLACE("
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::MSSQL::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 164
def visit_ArelExtensions_Nodes_Round o, collector
  collector << "ROUND("
  o.expressions.each_with_index { |arg, i|
    collector << Arel::Visitors::MSSQL::COMMA if i != 0
    collector = visit arg, collector
  }
  if o.expressions.length == 1
    collector << Arel::Visitors::MSSQL::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 232
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 361
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 520
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 187
def visit_ArelExtensions_Nodes_Substring o, collector
  collector << 'SUBSTRING('
  collector = visit o.expressions[0], collector
  collector << Arel::Visitors::MSSQL::COMMA
  collector = visit o.expressions[1], collector
  collector << Arel::Visitors::MSSQL::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 198
def visit_ArelExtensions_Nodes_Trim o, collector
  if o.right
    collector << "REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE("
    collector = visit o.left, collector
    collector << ", ' ', '~'), "
    collector = visit o.right, collector
    collector << ", ' '))), ' ', "
    collector = visit o.right, collector
    collector << "), '~', ' ')"
  else
    collector << "LTRIM(RTRIM("
    collector = visit o.left, collector
    collector << "))"
  end
  collector
end
visit_ArelExtensions_Nodes_Variance(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 527
def visit_ArelExtensions_Nodes_Variance o, collector
  collector << (o.unbiased_estimator ? "VAR(" : "VARP(")
  visit o.left, collector
  collector << ")"
  collector
end
visit_Arel_Nodes_NotRegexp(o, collector) click to toggle source
# File lib/arel_extensions/visitors/mssql.rb, line 397
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 391
def visit_Arel_Nodes_Regexp o, collector
  collector = visit o.left, collector
  collector << "LIKE '%#{o.right}%'"
  collector
end