SQL Logging in Rails

Posted by scientific on December 12, 2006


Getting good SQL logs can be a pain, at least in PostgreSQL for Rails. Presumably other SQL logs are also cluttered with meta-data queries and other noise. To solve this problem I built a simple tool that (in development or test modes) will log your SQL commands to a file.

To use the code, you basically execute statements just like normal, but if you run the following code on application startup, you’ll get the power to log only the SQL commands issued by Rails and your application directly to a SQL log file. To use following code, make sure it gets loaded by application.rb. You also need to define two constants on startup: RAILS_ENV_TEST and RAILS_ENV_DEV (Code for setting these constants is at the end of this list) Here’s the code itself for you to play with:

SQL_LOGGING = true
SQL_LOG_MAX_LINES = 5000
SQL_LOG_FILE = File::join(RAILS_ROOT, '/log/sql_log.txt')
# $sql_log is a global var that will hold the results of the last sql statement executed
$sql_log = ""
# permit logging if we are in development environment
if RAILS_ENV_DEV || RAILS_ENV_TEST
  connection = ActiveRecord::Base.connection
  class << connection
    alias :original_exec :execute
    def execute(sql, *name)
      # try to log sql command but ignore any errors that occur in this block
      # we log before executing, in case the execution raises an error
      begin
        if SQL_LOGGING
          if File::exists?(SQL_LOG_FILE) : lines = IO::readlines(SQL_LOG_FILE)
          else lines = Array.new(); end
          log = File.new(SQL_LOG_FILE, "w+")
          # keep the log to specified max lines
          if lines.length > SQL_LOG_MAX_LINES
            lines.slice!(0..(lines.length-SQL_LOG_MAX_LINES))
          end
          lines << Time.now.strftime("%x %I:%M:%S %p")+": "+sql+"n"
          log.write(lines)
          log.close
          $sql_log = sql
        end # if
      rescue Exception => e
        ;
      end
      # execute original statement
      original_exec(sql, *name)
    end # def execute
  end # class <<
end # if RAILS_ENV_DEV

Here’s the constant setting code (put in application.rb or something it requires before the code above gets run):

class CoreERR_RailsEnvironment < StandardError; end
#RAILS specific constants
  #setup global constants for manipulating states
  RAILS_ENV_VAR = "RAILS_ENV"
  #set ENV to development if explicit or not present
  RAILS_ENV_DEV = (ENV[RAILS_ENV_VAR]=="development" || (not ENV[RAILS_ENV_VAR]))
  RAILS_ENV_LIVETEST = ENV[RAILS_ENV_VAR]=="livetest"
  RAILS_ENV_TEST = ENV[RAILS_ENV_VAR]=="test"
  if (RAILS_ENV_DEV) or (RAILS_ENV_TEST)
    RAILS_ENV_PRODUCTION = false
  else
    RAILS_ENV_PRODUCTION = true
  end
  # check positively: if production environment is implied because test and development are not found
  # but production doesn't show up positively, raise exception
  if RAILS_ENV_PRODUCTION and (RAILS_ENV_PRODUCTION != (ENV[RAILS_ENV_VAR]=="production"))
    raise CoreERR_RailsEnvironment, "Production environment implied but not detected: "+ENV[RAILS_ENV_VAR]
  end
  RAILS_DB_SU = ENV[RAILS_ENV_VAR]+'_su'
  RAILS_ENV_DEV.freeze
  RAILS_ENV_TEST.freeze
  RAILS_ENV_PRODUCTION.freeze
Trackbacks

Use this link to trackback from your own site.

Comments

Leave a response

Comments