Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
# 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
You can’t perform that action at this time.