AR helpers to easily write different queries on different backends

agnostic_sql_helpers.rb

# ActiveRecord extension adding a "query_for" instance method to write
# different queries on different backends and an "rs_to_ary" helper to
# bypass AR completely. The DBMS often does the job better than AR :-)
# (C) 2010 Panmind - Released under the terms of the Ruby License
#
module PM
  module AgnosticSQLHelpers
    # Initializes the helpers on ActiveRecord::Base.
    #
    def self.included(ar)
      return if ar.respond_to? :adapter

      unless adapter = ar.configurations[Rails.env]['adapter']
        raise ArgumentError, "Database adapter for #{Rails.env} environment "
                             "not found. Please check your database.yml"
      end

      adapter = adapter.sub(/^jdbc/, '').downcase.to_sym

      ar.singleton_class.instance_eval do
        define_method(:adapter) { adapter }
      end

      if ar.adapter.blank?
        raise ArgumentError, 'No adapter name found. Please check your database.yml'
      end

      ar.extend ClassMethods

      if RUBY_PLATFORM == 'java'
        ar.extend RsToAry::Hash
      elsif ar.adapter == :postgresql    &&
        PGconn.const_defined?('VERSION') &&
        PGconn.const_get('VERSION').to_f >= 0.9
        ar.extend RsToAry::Hash
      elsif ar.adapter == :mysql || ar.adapter == :postgresql
        ar.extend RsToAry::Enumerable
      else
        raise ArgumentError, "No suitable rs_to_ary implementation for your "
                             "database adapter #{adapter} found. :-("
      end
    end

    def query_for(hash); self.class.query_for(hash); end #:nodoc:
    def rs_to_ary(hash); self.class.rs_to_ary(hash); end #:nodoc:

    module ClassMethods
      # Chooses from given hash, keyed by adapter symbol, the value matching
      # the current adapter symbol.
      #
      # e.g.:
      #   query_for(:mysql => 'foo', :postgresql => 'bar')
      #
      # returns 'foo' if running on MySQL or 'bar' if running on Postgres.
      #
      def query_for(hash)
        unless hash.has_key?(adapter)
          raise ArgumentError, "No query defined for current adapter \"#{adapter}\""
        end

        hash[adapter]
      end
    end

    module RsToAry
      module Hash
        # Transforms the given low-level database +Hash+ into an +Array+.
        # This is returned by JDBC and PG 0.9
        #
        def rs_to_ary(rs)
          rs.map(&:values)
        end
      end

      module Enumerable
        # Transforms the given low-level database Recordset into an +Array+.
        # This is returned by MySQL and PG 0.8
        #
        def rs_to_ary(rs)
          returning([]) do |ary|
            rs.each {|row| ary.push(row) }
          end
        end
      end
    end
  end
end

ActiveRecord::Base.instance_eval { include PM::AgnosticSQLHelpers }