Module ActiveRecord::Extensions::CreateAndUpdate
In: ar-extensions/lib/ar-extensions/create_and_update.rb

ActiveRecord::Extensions::CreateAndUpdate extends ActiveRecord adding additionaly functionality for insert and updates. Methods create, update, and save accept additional hash map of parameters to allow customization of database access.

Include the appropriate adapter file in environment.rb to access this functionality 

  require 'ar-extenstion/create_and_update/mysql'

Options

  • :pre_sql inserts SQL before the INSERT or UPDATE command
  • :post_sql appends additional SQL to the end of the statement
  • :keywords additional keywords to follow the command. Examples include LOW_PRIORITY, HIGH_PRIORITY, DELAYED
  • :on_duplicate_key_update - an array of fields (or a custom string) specifying which parameters to update if there is a duplicate row (unique key violoation)
  • :ignore => true - skips insert or update for duplicate existing rows on a unique key value
  • :command an additional command to replace INSERT or UPDATE
  • :reload - If a duplicate is ignored (ignore) or updated with on_duplicate_key_update, the instance is reloaded to reflect the data in the database. If the record is not reloaded, it may contain stale data and stale_record? will evaluate to true. If the object is discared after create or update, it is preferrable to avoid reloading the record to avoid superflous queries
  • :duplicate_columns - an Array required with reload to specify the columns used to locate the duplicate record. These are the unique key columns. Refer to the documentation under the duplicate_columns method.

Create Examples

Assume that there is a unique key on the name field

Create a new giraffe, and ignore the error if a giraffe already exists If a giraffe exists, then the instance of animal is stale, as it may not reflect the data in the database. 

 animal = Animal.create!({:name => 'giraffe', :size => 'big'}, :ignore => true)

Create a new giraffe; update the existing size and updated_at fields if the giraffe already exists. The instance of animal is not stale and reloaded to reflect the content in the database. 

 animal = Animal.create({:name => 'giraffe', :size => 'big'},
                :on_duplicate_key_update => [:size, :updated_at],
                :duplicate_columns => [:name], :reload => true)

Save a new giraffe, ignoring existing duplicates and inserting a comment in the SQL before the insert. 

 giraffe = Animal.new(:name => 'giraffe', :size => 'small')
 giraffe.save!(:ignore => true, :pre_sql => '/* My Comment */')

Update Examples

Update the giraffe with the low priority keyword 

 big_giraffe.update(:keywords => 'LOW_PRIORITY')

Update an existing record. If a duplicate exists, it is updated with the fields specified by +:on_duplicate_key_update+. The original instance(big_giraffe) is deleted, and the instance is reloaded to reflect the database (giraffe). 

 big_giraffe = Animal.create!(:name => 'big_giraffe', :size => 'biggest')
 big_giraffe.name = 'giraffe'
 big_giraffe.save(:on_duplicate_key_update => [:size, :updated_at],
                  :duplicate_columns => [:name], :reload => true)

Methods

duplicate_columns   reload_duplicate   reload_duplicate!   replace   stale_record?  

Classes and Modules

Class ActiveRecord::Extensions::CreateAndUpdate::NoDuplicateFound

Public Instance methods

reload_duplicate(options={})

Reload the record‘s duplicate based on the the duplicate_columns. Returns true if the reload was successful. :duplicate_columns - the columns to search on :force - force a reload even if the record is not stale :delete - delete the existing record if there is one. Defaults to true

reload_duplicate!(options={})

Reload Duplicate records like reload_duplicate but throw an exception if no duplicate record is found

replace(options={})

Replace deletes the existing duplicate if one exists and then inserts the new record. Foreign keys are updated only if performed by the database.

The options hash accepts the following attributes:

  • :pre_sql - sql that appears before the query
  • :post_sql - sql that appears after the query
  • :keywords - text that appears after the ‘REPLACE’ command

Examples

Replace a single object 

  user.replace
stale_record?()

Returns true if the record data is stale This can occur when creating or updating a record with options :on_duplicate_key_update or :ignore without reloading( :reload => true)

In other words, the attributes of a stale record may not reflect those in the database

Protected Instance methods

duplicate_columns(options={})

Returns the list of fields for which there is a unique key. When reloading duplicates during updates, with the :reload => true the reloaded existing duplicate record is the one matching the attributes specified by duplicate_columns.

This data can either be passed into the save command, or the duplicate_columns method can be overridden in the ActiveRecord subclass to return the columns with a unique key

Example

User has a unique key on name. If a user exists already the user object will be replaced by the existing user 

  user.name = 'blythe'
  user.save(:ignore => true, :duplicate_columns => 'name', :reload => true)

Alternatively, the User class can be overridden 

  class User
    protected
      def duplicate_columns(options={}); [:name]; end
  end

Then, the :duplicate_columns field is not needed during save 

  user.update(:on_duplicate_key_update => [:password, :updated_at], :reload => true)

[Validate]