module ArelExtensions::Visitors::MSSQL
Constants
- LOADED_VISITOR
Public Instance Methods
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