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

.get_primary_key(order, table_name) ⇒ Object

table_name might be quoted



85
86
87
88
89
90
91
92
93
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 85

def get_primary_key(order, table_name) # table_name might be quoted
  if order =~ /(\w*id\w*)/i
    $1
  else
    unquoted_name = unquote_table_name(table_name)
    model = descendants.find { |m| m.table_name == table_name || m.table_name == unquoted_name }
    model ? model.primary_key : 'id'
  end
end

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



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
75
76
77
78
79
80
81
82
83
 
# File 'lib/arjdbc/mssql/limit_helpers.rb', line 49

def replace_limit_offset!(sql, limit, offset, order)
  if limit
    offset ||= 0
    start_row = offset + 1
    end_row = offset + limit.to_i
    _, 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 = Utils.get_table_name(sql, true)
      primary_key = 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