create_and_update.rb

Path: ar-extensions/lib/ar-extensions/create_and_update.rb
Last Update: Mon Apr 13 03:11:35 -0600 2009

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)

Misc

stale_record? - returns true if the record is stale Example: animal.stale_record?

Developers

Homepage

[Validate]