active_record.rb

class ActiveRecord::Base
  class << self
    def copy(columns, rows, name = nil)
      connection.copy table_name, columns, rows, name
    end
    def indexes
      connection.indexes table_name
    end
  end
end

class ActiveRecord::ConnectionAdapters::AbstractAdapter
  def disable_indexes(table_name, &block)
    raise ArgumentError, 'block missing' unless block
    indexes = indexes(table_name)

    # Drop indexes
    indexes.each do |index|
      remove_index index.table, :name => index.name
    end

    # Call the block
    block.call

    # Recreate indexes
    indexes.each do |index|
      add_index index.table, index.columns, :name => index.name
    end
  end
end

description.txt

Originally posted on http://pastie.org/pastes/26489 (8 December 2006)

COPY FROM STDIN / LOAD DATA INFILE method for Rails PostgreSQL and MySQL Adapters.
The mysql adapter is completely untested. It is self-explanatory, isn't it? :)

Public domain, or DWTFWYW License, at your option.

-vjt@openssl.it

mysql.rb

class ActiveRecord::ConnectionAdapters::MySQLAdapter
  def copy(table_name, columns, rows, name = nil)
    Tempfile.open('mysql_infile', "#{RAILS_ROOT}/tmp/db") do |tmp|
      tmp.write rows.join "\n"
      path = Pathname.new(tmp.path).realpath
      transaction do
        disable_indexes(table_name) do
          sql = "LOAD DATA INFILE #{path} INTO #{table_name}"
          log(sql, name) {
            @connection.exec sql
          }
        end
      end
      @connection.exec "OPTIMIZE TABLE #{table_name}"
      tmp.close!
    end

    true
  end
end

postgresql.rb

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  def copy(table_name, columns, rows, name = nil)
    transaction do
      disable_indexes(table_name) do
        sql = "COPY #{table_name} (#{columns.join ','}) FROM STDIN"
        log(sql, name) {
          @connection.exec sql
          @connection.putline rows.join("\n") + "\\.\n"
          @connection.endcopy
        }
      end
    end

    @connection.exec "VACUUM ANALYZE #{table_name}"
    true
  end
end