module ArJdbc::MSSQL::LimitHelpers::SqlServer2000ReplaceLimitOffset

@private

Public Instance Methods

get_primary_key(order, table_name) click to toggle source
# File lib/arjdbc/mssql/limit_helpers.rb, line 161
def get_primary_key(order, table_name) # table_name might be quoted
  if order =~ /(\w*id\w*)/i
    $1
  else
    unquoted_name = Utils.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) click to toggle source
# File lib/arjdbc/mssql/limit_helpers.rb, line 93
def replace_limit_offset!(sql, limit, offset, order)
  if limit
    offset ||= 0
    start_row = offset + 1
    end_row = offset + limit.to_i

    if match = FIND_SELECT.match(sql)
      select, distinct, rest_of_query = match[1], match[2], match[3]
    end
    #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 (start_row == 1) && (end_row ==1)
      new_sql = "#{select} TOP 1 #{rest_of_query} #{new_order}"
      sql.replace(new_sql)
    else
      # We are in deep trouble here. SQL Server does not have any kind of OFFSET build in.
      # Only remaining solution is adding a where condition to be sure that the ID is not in SELECT TOP OFFSET FROM SAME_QUERY.
      # To do so we need to extract each part of the query to insert our additional condition in the right place.
      query_without_select = rest_of_query[/FROM/i=~ rest_of_query.. -1]
      additional_condition = "#{table_name}.#{primary_key} NOT IN (#{select} TOP #{offset} #{table_name}.#{primary_key} #{query_without_select} #{new_order})"

      # Extract the different parts of the query
      having, group_by, where, from, selection = split_sql(rest_of_query, /having/i, /group by/i, /where/i, /from/i)

      # Update the where part to add our additional condition
      if where.blank?
        where = "WHERE #{additional_condition}"
      else
        where = "#{where} AND #{additional_condition}"
      end

      # Replace the query to be our new customized query
      sql.replace("#{select} TOP #{limit} #{selection} #{from} #{where} #{group_by} #{having} #{new_order}")
    end
  end
  sql
end
split_sql(rest_of_query, *regexs) click to toggle source

Split the rest_of_query into chunks based on regexs (applied from end of string to the beginning) The result is an array of regexs.size+1 elements (the last one being the remaining once everything was chopped away)

# File lib/arjdbc/mssql/limit_helpers.rb, line 142
def split_sql(rest_of_query, *regexs)
  results = Array.new

  regexs.each do |regex|
    if position = (regex =~ rest_of_query)
      # Extract the matched string and chop the rest_of_query
      matched       = rest_of_query[position..-1]
      rest_of_query = rest_of_query[0...position]
    else
      matched = nil
    end

    results << matched
  end
  results << rest_of_query

  results
end

Private Instance Methods

descendants() click to toggle source
# File lib/arjdbc/mssql/limit_helpers.rb, line 174
def descendants; ::ActiveRecord::Base.descendants; end