AR Perf Toolkit Base ActiveRecord enhancements to optimize queries and insertion functionality. Includes support for finder index hints, ignore and duplicate update, insert select, record duplicate deletion, bulk insert, and eager loading of selected columns.

This has only been tested with MySQL.

######################################################################################### Single Active Record Operations ######################################################################################### The following methods are overwritten to accept a hash map parameter of options

* save
* create
* create!
* save!
* create_or_update

Options:

* duplicate - On duplicate key update  update old rows on duplicate
     Example: updates fields birthdate and email_address_id if a duplicate exists when the contact is created or updated
     contact.save :duplicate => [:birthdate, :email_address_id ]
 Set the existing duplicate email address id to 5
     contact.save :duplicate => 'email_address_id = 5'
* keyword(ignore) adds a keyword after mysql command (example INSERT IGNORE)
      Example. Ignore a duplicate record.
  contact.save :keywords => :ignore
  command. The sql command to use. Example:  uses replace insead of insert
  contact.save :command => :replace

######################################################################################### Bulk Insert Operations #########################################################################################

* insert_all
* insert_select

insert_all(objs, options={}) Note use instead of former upsert.

Insert a group of records in one transaction.

objs can be:

* Array of hash maps {:column_name => :value} where column_names are specified in the options[:select]

EmailAddress.insert_all( [=> ‘blythe@spongecell.com’, :name =>‘Blythe Dunham’, => ‘blythedunham@gmail.com’, :name =>‘Blythe Dunham’], {:select => [:text, :name] })

* Array of active records (slower)

upsert_taggings << Tagging.new(:tag_id=>tag_id, :taggable_type=>child.to_s, :taggable_id=>record.id)

Tagging.insert_all(upsert_taggings, :keywords => :ignore)

Options

* skip_validation does not run valid? on each object
* duplicate
* keywords
* post_sql - sql appended to the end (indexes, rollup, etc)
* pre_sql - sql appended to the beginning (ex, priorities)
* command - choose the columns you wish to insert

insert_select(select_obj, select_options, insert_options={}) Insert records with a select statement

add all the books written by the author into the cart
   Cart.insert_select Book, {:select => ['books.id, ?', Time.now], :conditions => ['books.author_id = ?', 1 }, {:select => [:book_id, :added-on]}
     generated sql
   INSERT INTO carts (`book_id`, `added_on`) SELECT books.id,  '2007-05-01 01:24:28' FROM books where books.author_id = 1

select_options

* select => select porition of query. like conditions (santizes sql)

insert_options - See options of insert_all

######################################################################################### Finder Operations #########################################################################################

* finder_sql_to_string - prints out the query to string
* find - enhanced to accept additional params for sql customization

finder_sql_to_string Print out the sql that would be executed. Same signature as ActiveRecord::Base.find

>> Contact.finder_sql_to_string :piggy => :primary_email_address

> “SELECT contacts.*, email_addresses.crypted_text AS primary_email_address_crypted_text FROM contacts LEFT JOIN email_addresses ON email_addresses.id=contacts.email_address_id ”

find

Adds these options:

* post_sql - sql appended to the end (indexes, rollup, etc)
* pre_sql - sql appended to the beginning (ex, priorities)
* keywords