Create a gist now

Instantly share code, notes, and snippets.

# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment