How do I execute queries upon DB connection in Rails?

Question

I have certain initializing functions that I use to set up audit logging on the DB server side (ie, not rails) in PostgreSQL. At least one has to be issued (setting the current user) before inserting data into or updating any of the audited tables, or else the whole query will fail spectacularly.

I can easily call these every time before running any save operation in the code, but DRY makes me think I should have the code repeated in as few places as possible, particularly since this diverges greatly from the ideal of database agnosticism. Currently I'm attempting to override ActiveRecord::Base.establish_connection in an initializer to set it up so that the queries are run as soon as I connect automatically, but it doesn't behave as I expect it to. Here is the code in the initializer:

class ActiveRecord::Base
  # extend the class methods, not the instance methods
  class << self
    alias :old_establish_connection :establish_connection # hide the default

    def establish_connection(*args)
      ret = old_establish_connection(*args) # call the default

      # set up necessary session variables for audit logging
      # call these after calling default, to make sure conn is established 1st
      db = self.class.connection
      db.execute("SELECT SV.set('current_user', 'test@localhost')")
      db.execute("SELECT SV.set('audit_notes', NULL)") # end "empty variable" err

      ret # return the default's original value
    end
  end
end

puts "Loaded custom establish_connection into ActiveRecord::Base"

sycobuny:~/rails$ ruby script/server 
=> Booting WEBrick
=> Rails 2.3.5 application starting on http://0.0.0.0:3000
Loaded custom establish_connection into ActiveRecord::Base

This doesn't give me any errors, and unfortunately I can't check what the method looks like internally (I was using ActiveRecord::Base.method(:establish_connection), but apparently that creates a new Method object each time it's called, which is seemingly worthless cause I can't check object_id for any worthwhile information and I also can't reverse the compilation).

However, the code never seems to get called, because any attempt to run a save or an update on a database object fails as I predicted earlier. If this isn't a proper way to execute code immediately on connection to the database, then what is?

Answer 1

Do you know that your initializer is being called before the database connection is established?

Without authoritative information on what could be wrong, I'd take advantage of the fact that the source to ActiveRecord is on my hard drive.

Dig into the base class and do a little puts debugging to see when ActiveRecord establish_connection is being called relative to when your initializer is called. Either that or just turn on extra verbose debugging.

Answer 2

Rails uses connection pooling, so your best bet is to use an alias_method_chain for ActiveRecord::ConnectionAdapters::ConnectionPool#new_connection

module ActiveRecord
  Module ConnectionAdapters
    class ConnectionPool
      alias_method_chain :new_connection, :my_stuff
      private
      def new_connection_with_my_stuff
        c = new_connection_without_my_stuff
        # Do your stuff with 
        # c.exec(<your sql command>)
        c
      end
    end
  end
end

Answer 3

In terms of writing code to hook rails database calls and initializations, you might want to use the code from db-charmer as a template. It demonstrates a number of interesting ways of interacting with ActiveRecord and one of them will probably do what you want.