Usage:

  Person.find :all, :conditions => ["#{sql_year 'birthday'} >= ?", year]

SQLITE = true # or false

def sql_concat(*args)
  SQLITE ? args.join(' || ') : "CONCAT(#{args.join(', ')})"
end

def sql_lcase(expr)
  SQLITE ? "LOWER(#{expr})" : "LCASE(#{expr})"
end

def sql_year(expr)
  SQLITE ? "CAST(STRFTIME('%y', #{expr}) as 'INTEGER')" : "YEAR(#{expr})"
end

def sql_month(expr)
  SQLITE ? "CAST(STRFTIME('%m', #{expr}) as 'INTEGER')" : "MONTH(#{expr})"
end

def sql_day(expr)
  SQLITE ? "CAST(STRFTIME('%d', #{expr}) as 'INTEGER')" : "DAY(#{expr})"
end

def sql_now
  SQLITE ? "CURRENT_TIMESTAMP" : "NOW()"
end

Lets you add a condition to a set of ActiveRecord conditions easily like this:

  conditions = ['active = ? and type = ?', true, 2]
  conditions.add_condition ['person_id = ?', 345]

class Array
  def add_condition(condition, conjunction='and')
    if condition.is_a? Array
      if self.empty?
        (self << condition).flatten!
      else
        self[0] += " #{conjunction} " + condition.shift
        (self << condition).flatten!
      end
    elsif condition.is_a? String
      self[0] += " #{conjunction} " + condition
    else
      raise "don't know how to handle this condition type"
    end
    self
  end
end

A quick way to cache results in a file and read from the file on subsequent requests instead of the database. Makes the initial query a bit slower, but later queries *much* faster.

class MyCachedModel < ActiveRecord::Base
  class << self
    alias_method :rails_original_find_by_sql, :find_by_sql
    def find_by_sql(sql)
      cache_filename = Base64.encode64(sql)
      if File.exists? cache_filename
        Marshal.load(File.open(cache_filename))
      else
        Marshal.dump(records = rails_original_find_by_sql(sql), File.open(cache_filename, 'w'))
        return records
      end
    end
  end
end

The SQL Server adapter for ActiveRecord uses Time objects to cast dates from the db. This fails for dates before 1970, thus some birthdates come back as nil. This is some kludge to use a DateTime in that case so we still get the value.

A better approach may be to convert this code to use DateTime objects exclusively, but I'm not sure of the speed implications of doing so. The code below first tries to cast the value to a Time object; if that fails, it tries a DateTime object; if that fails, it returns nil.

Stick this in a plugin to use with Rails.

module ActiveRecord
  module ConnectionAdapters
    class ColumnWithIdentity
      def cast_to_time(value)
        return value if value.is_a?(Time) or value.is_a?(DateTime)
        time_array = ParseDate.parsedate(value)
        time_array[0] ||= 2000
        time_array[1] ||= 1
        time_array[2] ||= 1
        Time.send(Base.default_timezone, *time_array) rescue DateTime.new(*time_array[0..5]) rescue nil
      end
      def cast_to_datetime(value)
        if value.is_a?(Time) or value.is_a?(DateTime)
          if value.year != 0 and value.month != 0 and value.day != 0
            return value
          else
            return Time.mktime(2000, 1, 1, value.hour, value.min, value.sec) rescue nil
          end
        end
        return cast_to_time(value) if value.is_a?(Date) or value.is_a?(String) rescue nil
        value
      end
    end
  end
end

I had to work with a legacy database with hundreds of tables with uppercase column names. Here is how I made my life a whole lot easier...

module ActiveRecord
  class MyCustomARClass < ActiveRecord::Base
  
    def self.reloadable?
      false
    end
    
    # all columns are uppercase
    set_primary_key 'ID'
    
    # convert to uppercase attribute if in existence
    # record.name => record.NAME
    # record.id => record.ID
    def method_missing(method_id, *args, &block)
      method_id = method_id.to_s.upcase if @attributes.include? method_id.to_s.upcase.gsub(/=/, '')
      super(method_id, *args, &block)
    end

    # strip leading and trailing spaces from attribute string values
    def read_attribute(attr_name)
      value = super(attr_name)
      value.is_a?(String) ? value.strip : value
    end

    class << self # Class methods
      
      private
        # allow for find_by and such to work with uppercase attributes
        # find_by_name => find_by_NAME
        # find_by_dob_and_height => find_by_DOB_and_HEIGHT
        def extract_attribute_names_from_match(match)
          match.captures.last.split('_and_').map { |name| name.upcase }
        end
    end
  end
end

This was defined in a plugin. Then, in each of my models I used the subclass instead of ActiveRecord::Base, like so...

class MyModel < ActiveRecord::MyCustomARClass
  # ...
end