monkey_patch_sql2005_limit.rb

# monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination
module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter
      def find_table_primary_key_columns(table_name)
        @find_table_primary_key_columns_cache ||= {}
        if @find_table_primary_key_columns_cache[table_name].nil?
        
          table_name = table_name.split('.').last.delete("[]")
          sql = "SELECT KU.COLUMN_NAME
                  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
                  ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                  TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
                  WHERE KU.TABLE_NAME = '#{table_name}'
                  ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION"
          primary_columns = select_rows(sql)
        
          @find_table_primary_key_columns_cache[table_name] =
            primary_columns.collect {|row| "[#{table_name}].[#{row[0]}]"}.join ', '
        end
        
        @find_table_primary_key_columns_cache[table_name]
      end
      
      def add_limit_offset!(sql, options)
        options[:offset] ||= 0
        
        if options[:offset] > 0
          options[:order] ||= if order_by = sql.match(/ORDER BY (.*$)/i)
                              order_by[1]
                            else
                              table_name = sql.match('FROM ([\[\]a-zA-Z0-9_\.]+)')[1]
                              
                              find_table_primary_key_columns(table_name)
                            end
           sql.sub!(/ORDER BY.*$/i, '')
 
          sql.sub!(/SELECT/i, "SELECT * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY #{options[:order] } ) AS row_num, ")
          sql << ") AS t WHERE row_num > #{options[:offset]}"
        
        end
     
        sql.sub!(/^SELECT/i, "SELECT TOP #{options[:limit]}") if options[:limit]

        sql
      end
    end
  end
end