I decided not to include PostgreSQL support officially in this release because there were a few individuals who were anxious to checkout the import functionality in MySQL with ON DUPLICATE KEY UPDATE support. The import functionality may work out of the box right now but I have no tests that validate it on a PostgreSQL database. I’ll add official PostgreSQL support for the import will be coming (official meaning that it will have tests that pass) in the next few days most likely. It will not include support for similar functionality to MySQL’s ON DUPLICATE KEY UPDATE, because PostgreSQL doesn’t seem to provide a feature like this. If any PostgreSQL users are reading this I’d be interested in a solution to providing similar functionality.

An example of the existing mass import functionality is:

class LogEntry < ActiveRecord::Base ; end

hashes = LogFileReader.read_log_entries( 'file.log' )
n = LogEntry.import hashes

puts "That took #{n} database inserts"

An example of the updated import with ON DUPLICATE KEY UPDATE support is:

columns = [ 'accountno', 'account_name' ]
updated_or_new_accounts = [
  [ 1, 'The Coffee House' ],
  [ 2, 'The Java Hut' ],
  [ 3, 'The Beanery' ] ]

Account.import columns,
  updated_or_new_accounts,
  :on_duplicate_key_update => [ 'account_name' ]

So if one of the Account records doesn’t exist it will be created otherwise the account_name will be updated.

There is more variations of this functionality. You can also use:

• array of string column names
• array of symbol column names
• hash of string to string column name mappings
• hash of symbol to symbol column name mappings
• hash of string to symbol (or vise versa) column name mappings

These example would look like:

columns = [ 'accountno', 'account_name' ]
updated_or_new_accounts = [
  [ 1, 'The Coffee House' ],
  [ 2, 'The Java Hut' ],
  [ 3, 'The Beanery' ] ]

Account.import columns,
  updated_or_new_accounts,
  :on_duplicate_key_update => [ :account_name ]

Account.import columns,
  updated_or_new_accounts,
  :on_duplicate_key_update => { 'account_name' => 'account_name' }

Account.import columns,
  updated_or_new_accounts,
  :on_duplicate_key_update => { :account_name => :account_name }

The hash mappings work well if you want to map one field to a field with a different name. This will make more sense as more features get included into this plugin.

Currently one drawback of this functionality is that you must use a index or unique key to force rows to match from the values you are importing and the existing rows in the database. Sometimes you have the primary key or a unique index available, but often times you don’t. One solution to getting around this is to use temporary tables. You can manipulate the structure of the temporary table and force it to have whatever index or unique keys you want and then you can merge that table with your actual table or vise versa to manipulate the data. Temporary table support is on the roadmap for this plugin. I have it working in another plugin I wrote for work, but I have to rewrite it to work with this plugin so it can be used by the world.

Installation

You can install this by downloading and extracting the below tgz file and putting it in you #{railsapp}/vendor/plugins directory, or if you want to run it standalone put it somewhere in your project path and load require boot.rb. This has been written to require correctly all of the depedendent files regardless of where you require boot.rb from.

2. 3 can be downloaded here