Module: ArJdbc::MsSQL::LimitHelpers::SqlServer2000ReplaceLimitOffset

Included in:
Arel::Visitors::SQLServer2000
Defined in:
lib/arjdbc/mssql/limit_helpers.rb

Class Method Summary collapse

Class Method Details

.replace_limit_offset!(sql, limit, offset, order) ⇒ Object 



39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 39

def replace_limit_offset!(sql, limit, offset, order)
  if limit
    offset ||= 0
    start_row = offset + 1
    end_row = offset + limit.to_i
    find_select = /\b(SELECT(?:\s+DISTINCT)?)\b(.*)/im
    whole, select, rest_of_query = find_select.match(sql).to_a
    if (start_row == 1) && (end_row ==1)
      new_sql = "#{select} TOP 1 #{rest_of_query}"
      sql.replace(new_sql)
    else
      #UGLY
      #KLUDGY?
      #removing out stuff before the FROM...
      rest = rest_of_query[/FROM/i=~ rest_of_query.. -1]
      #need the table name for avoiding amiguity
      table_name = LimitHelpers.get_table_name(sql)
      primary_key = LimitHelpers.get_primary_key(order, table_name)
      #I am not sure this will cover all bases.  but all the tests pass
      if order[/ORDER/].nil?
        new_order = "ORDER BY #{order}, #{table_name}.#{primary_key}" if order.index("#{table_name}.#{primary_key}").nil?
      else
        new_order ||= order
      end

      if (rest_of_query.match(/WHERE/).nil?)
        new_sql = "#{select} TOP #{limit} #{rest_of_query} WHERE #{table_name}.#{primary_key} NOT IN (#{select} TOP #{offset} #{table_name}.#{primary_key} #{rest} #{new_order}) #{order} "
      else
        new_sql = "#{select} TOP #{limit} #{rest_of_query} AND #{table_name}.#{primary_key} NOT IN (#{select} TOP #{offset} #{table_name}.#{primary_key} #{rest} #{new_order}) #{order} "
      end

      sql.replace(new_sql)
    end
  end
  sql
end