在Rails下连接SQL Server 2005 分页

lakers009

　　For a Rails/SQLServer application I’m working on, I had to deal with pagination with custom queries because of the different joins. The mislav-will_paginate plugin works great for MySQL, but for SQL Server, the paginated query generated by the current SQL Server Adapter (I’m using activerecord-sqlserver-adapter-1.0.0.9250) does not work very well. The current implementation is targetted really for SQL Server 2000 and older versions since these versions do not have support for ROW_NUMBER() method. It is a major pain in the butt to do pagination with these databases. With the newer SQL Sever 2005, the job is a bit easier. Microsoft implemented the ROW_NUMBER() method with a convoluted syntax to have better support for pagination, but it is still a drag because of the weird syntax.
　　Semergence wrote in his blog about patching the SQLServerAdapter to support pagination. Based on his post, I improved ActiveRecord::ConnectionAdapters::SQLServerAdapter::add_limit_offset! to make the query work in a more general way with free-form queries, e.g. queries ran with the paginate_by_sql() method provided by mislav-will_paginate
　　Include this script in your environment.rb file, or an external file and “require” the file within environment.rb.

view plaincopy to clipboardprint?

• # monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination   
  
• module ActiveRecord   
  
•   module ConnectionAdapters   
  
•     class SQLServerAdapter   
  
•       def add_limit_offset!(sql, options)   
  
•         puts sql   
  
•         options[:offset] ||= 0   
  
•         options_limit = options[:limit] ? "TOP #{options[:limit]}" : ""  
  
•         options[:order] ||= if order_by = sql.match(/ORDER BY(.*$)/i)   
  
•                               order_by[1]   
  
•                             else  
  
•                               sql.match('FROM (.+?)\b')[1] + '.id'  
  
•                             end  
  
•         sql.sub!(/ORDER BY.*$/i, '')   
  
•         sql.sub!(/SELECT/i, "SELECT #{options_limit} * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY #{options[:order] } ) AS row_num, ")   
  
•         sql << ") AS t WHERE row_num > #{options[:offset]}”   
  
•         puts sql   
  
•         sql   
  
•       end  
  
•     end  
  
•   end  
  
• end  
  

  # monkey-patching SQLServerAdapter to support SQL Server 2005-style pagination
module ActiveRecord
module ConnectionAdapters
class SQLServerAdapter
def add_limit_offset!(sql, options)
puts sql
options[:offset] ||= 0
options_limit = options[:limit] ? "TOP #{options[:limit]}" : ""
options[:order] ||= if order_by = sql.match(/ORDER BY(.*$)/i)
order_by[1]
else
sql.match('FROM (.+?)\b')[1] + '.id'
end
sql.sub!(/ORDER BY.*$/i, '')
sql.sub!(/SELECT/i, "SELECT #{options_limit} * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY #{options[:order] } ) AS row_num, ")
sql << ") AS t WHERE row_num > #{options[:offset]}”
puts sql
sql
end
end
end
end

　　The method above monkey-patches the SQLServerAdapter by overwriting the add_limit_offset! method.
　　Here’s a custom query that I used and the transformed result:

view plaincopy to clipboardprint?

• Resource.paginate_by_sql([   
  
•       %!SELECT  resources.*   
  
•             ,skills_count.skill_count   
  
•         FROM resources   
  
•             ,(   
  
•                 SELECT resource_id   
  
•                     , COUNT(*) AS skill_count   
  
•                 FROM resource_skills   
  
•             WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)   
  
•                 GROUP BY resource_id   
  
•             ) AS skills_count   
  
•         WHERE resources.is_active = ?   
  
•           AND resources.id = skills_count.resource_id   
  
•         ORDER BY skill_count DESC  
  
•       !, true ], :page => page, :per_page => per_page  
  

Resource.paginate_by_sql([
%!SELECT  resources.*
,skills_count.skill_count
FROM resources
,(
SELECT resource_id
, COUNT(*) AS skill_count
FROM resource_skills
WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)
GROUP BY resource_id
) AS skills_count
WHERE resources.is_active = ?
AND resources.id = skills_count.resource_id
ORDER BY skill_count DESC
!, true ], :page => page, :per_page => per_page

　　With :page = 1, :per_page = 2, the resulted SQL is:

view plaincopy to clipboardprint?

• SELECT TOP 2 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY skill_count DESC ) AS row_num, resources.*   
  
•     ,skills_count.skill_count   
  
•  FROM resources   
  
•     ,(   
  
•         SELECT resource_id   
  
•             , COUNT(*) AS skill_count   
  
•         FROM resource_skills   
  
•  WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)   
  
•         GROUP BY resource_id   
  
•     ) AS skills_count   
  
•  WHERE resources.is_active = 1   
  
•  AND resources.id = skills_count.resource_id   
  
•   
  
•  ) AS t WHERE row_num > 0  
  

SELECT TOP 2 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY skill_count DESC ) AS row_num, resources.*
,skills_count.skill_count
FROM resources
,(
SELECT resource_id
, COUNT(*) AS skill_count
FROM resource_skills
WHERE meta_skill_id IN (1,2,3,4,5,6,7,8,9,10)
GROUP BY resource_id
) AS skills_count
WHERE resources.is_active = 1
AND resources.id = skills_count.resource_id
) AS t WHERE row_num > 0

　　The will_pagination’s COUNT query is

view plaincopy to clipboardprint?

• SELECT COUNT(*) FROM (   
  
•  SELECT resources.*   
  
•     ,skills_count.skill_count   
  
•  FROM resources   
  
•     ,(   
  
•         SELECT resource_id   
  
•             , COUNT(*) AS skill_count   
  
•         FROM resource_skills   
  
•  WHERE meta_skill_id IN (21,22)   
  
•         GROUP BY resource_id   
  
•     ) AS skills_count   
  
•  WHERE resources.is_active = 1   
  
•  AND resources.id = skills_count.resource_id   
  
•  ) AS count_table  
  

SELECT COUNT(*) FROM (
SELECT resources.*
,skills_count.skill_count
FROM resources
,(
SELECT resource_id
, COUNT(*) AS skill_count
FROM resource_skills
WHERE meta_skill_id IN (21,22)
GROUP BY resource_id
) AS skills_count
WHERE resources.is_active = 1
AND resources.id = skills_count.resource_id
) AS count_table

　　The ORDER BY part is automatically removed from the main query (which becomes a sub-select) by the plugin to speed up the query. This in turns sanatizes the sql so that SQL Server doesn’t not complain about nested “ORDER BY” within a sub-select. Neat!
　　The only catch with the current add_limit_offset! is that it does not support ALIAS-ing, because the aliasing confuses the reqex to parse out the ORDER BY condition in the OVER() part of the query.
　　For regular find() queries, here’s a sample result

view plaincopy to clipboardprint?

• Resource.find(:first)   
  
• # original query:  SELECT * FROM resources   
  
• # transformed:   SELECT TOP 1 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY resources.id ) AS row_number, * FROM resources ) AS t WHERE row_num > 0  
  

Resource.find(:first)
# original query:  SELECT * FROM resources
# transformed:   SELECT TOP 1 * FROM ( SELECT ROW_NUMBER() OVER( ORDER BY resources.id ) AS row_number, * FROM resources ) AS t WHERE row_num > 0

　　Hope this helps and cheers!