Module: ActiveRecord::Extensions::CreateAndUpdate

Defined in:
lib/ar-extensions/create_and_update.rb

Overview

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)

Defined Under Namespace

Modules: ClassMethods Classes: NoDuplicateFound

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.included(base) ⇒ Object

:nodoc:



147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
 
# File 'lib/ar-extensions/create_and_update.rb', line 147

def self.included(base) #:nodoc:
  base.extend(ClassMethods)
  base.extend(ActiveRecord::Extensions::SqlGeneration)

  #alias chain active record methods if they have not already
  #been chained
  unless base.method_defined?(:save_without_extension)
    base.class_eval do
      [:save, :update, :save!, :create_or_update, :create].each { |method|  alias_method_chain method, :extension }

      class << self
        [:create, :create!].each {|method| alias_method_chain method, :extension }
      end

    end
  end
end

Instance Method Details

#create_or_update_with_extension(options = {}) ⇒ Object

overwrite the create_or_update to call into the appropriate method create or update with the new options call the callbacks here

Raises:

  • (ReadOnlyRecord) 


224
225
226
227
228
229
230
231
232
233
 
# File 'lib/ar-extensions/create_and_update.rb', line 224

def create_or_update_with_extension(options={})#:nodoc:
  return create_or_update_without_extension unless options.any?

  return false if callback(:before_save) == false
  raise ReadOnlyRecord if readonly?
  result = new_record? ? create(options) : update(@attributes.keys, options)
  callback(:after_save)

  result != false
end

#create_with_extension(options = {}) ⇒ Object

Creates a new record with values matching those of the instance attributes.



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
 
# File 'lib/ar-extensions/create_and_update.rb', line 293

def create_with_extension(options={})#:nodoc:
  return create_without_extension unless options.any?

  check_insert_and_update_arguments(options)

  return 0 if callback(:before_create) == false
  insert_with_timestamps(true)

  if self.id.nil? && connection.prefetch_primary_key?(self.class.table_name)
    self.id = connection.next_sequence_value(self.class.sequence_name)

  end

  quoted_attributes = attributes_with_quotes

  statement = if quoted_attributes.empty?
    connection.empty_insert_statement(self.class.table_name)
  else
    options[:command]||='INSERT'
    sql = self.class.construct_ar_extension_sql(options) do |sql, options|
      sql << "INTO #{self.class.table_name} (#{quoted_column_names.join(', ')}) "
      sql << "VALUES(#{attributes_with_quotes.values.join(', ')})"
    end
  end

  self.id = connection.insert(statement, "#{self.class.name} Create X",
    self.class.primary_key, self.id, self.class.sequence_name)


  @new_record = false

  #most adapters update the insert id number even if nothing was
  #inserted. Reset to 0 for all :on_duplicate_key_update
  self.id = 0 if options[:on_duplicate_key_update]


  #the record was not created. Set the value to stale
  if self.id == 0
    @stale_record = true
    load_duplicate_record(options) if options[:reload]
  end

  callback(:after_create)

  self.id
end

#reload_duplicate(options = {}) ⇒ Object

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



380
381
382
383
384
 
# File 'lib/ar-extensions/create_and_update.rb', line 380

def reload_duplicate(options={})
  reload_duplicate!(options)
rescue NoDuplicateFound => e
  return false
end

#reload_duplicate!(options = {}) ⇒ Object

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

Raises:



369
370
371
372
373
 
# File 'lib/ar-extensions/create_and_update.rb', line 369

def reload_duplicate!(options={})
  options.assert_valid_keys(:duplicate_columns, :force, :delete)
  raise NoDuplicateFound.new("Record is not stale") if !stale_record? and !options[:force].is_a?(TrueClass)
  load_duplicate_record(options.merge(:reload => true))
end

#replace(options = {}) ⇒ Object

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



353
354
355
356
 
# File 'lib/ar-extensions/create_and_update.rb', line 353

def replace(options={})
  options.assert_valid_keys(:pre_sql, :post_sql, :keywords)
  create_with_extension(options.merge(:command => 'REPLACE'))
end

#save_with_extension(options = {}) ⇒ Object

:nodoc:



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/ar-extensions/create_and_update.rb', line 195

def save_with_extension(options={})#:nodoc:

  #invoke save_with_validation if the argument is not a hash
  return save_without_extension(options) if !options.is_a?(Hash)
  return save_without_extension unless options.any?

  perform_validation = options.delete(:perform_validation)
  raise_exception = options.delete(:raise_exception)

  if (perform_validation.is_a?(FalseClass)) || valid?
    raise ActiveRecord::ReadOnlyRecord if readonly?
    create_or_update(options)
  else
    raise ActiveRecord::RecordInvalid.new(self) if raise_exception
    false
  end
end

#save_with_extension!(options = {}) ⇒ Object

:nodoc:



213
214
215
216
217
218
219
 
# File 'lib/ar-extensions/create_and_update.rb', line 213

def save_with_extension!(options={})#:nodoc:

  return save_without_extension!(options) if !options.is_a?(Hash)
  return save_without_extension! unless options.any?

  save_with_extension(options.merge(:raise_exception => true)) || raise(ActiveRecord::RecordNotSaved)
end

#stale_record? ⇒ Boolean

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

Returns:

  • (Boolean) 


365
 
# File 'lib/ar-extensions/create_and_update.rb', line 365

def stale_record?; @stale_record.is_a?(TrueClass); end

#supports_create_and_update? ⇒ Boolean

:nodoc:

Returns:

  • (Boolean) 


165
166
167
 
# File 'lib/ar-extensions/create_and_update.rb', line 165

def supports_create_and_update? #:nodoc:
  true
end

#update_with_extension(attribute_names = @attributes.keys, options = {}) ⇒ Object

Updates the associated record with values matching those of the instance attributes.



237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
 
# File 'lib/ar-extensions/create_and_update.rb', line 237

def update_with_extension(attribute_names = @attributes.keys, options={})#:nodoc:

  return update_without_extension unless options.any?

  check_insert_and_update_arguments(options)

  return false if callback(:before_update) == false
  insert_with_timestamps(false)

  #set the command to update unless specified
  #remove the duplicate_update_key if any
  sql_options = options.dup
  sql_options[:command]||='UPDATE'
  sql_options.delete(:on_duplicate_key_update)

  quoted_attributes = attributes_with_quotes(false, false, attribute_names)
  return 0 if quoted_attributes.empty?

  locking_sql = update_locking_sql

  sql = self.class.construct_ar_extension_sql(sql_options) do |sql, o|
    sql << "#{self.class.quoted_table_name} "
    sql << "SET #{quoted_comma_pair_list(connection, quoted_attributes)} " +
      "WHERE #{connection.quote_column_name(self.class.primary_key)} = #{quote_value(id)}"
    sql << locking_sql if locking_sql
  end


  reloaded = false

  begin
    affected_rows = connection.update(sql,
      "#{self.class.name} Update X #{'With optimistic locking' if locking_sql} ")
    #raise exception if optimistic locking is enabled and no rows were updated
    raise ActiveRecord::StaleObjectError, "#{affected_rows} Attempted to update a stale object" if locking_sql && affected_rows != 1
    @stale_record = (affected_rows == 0)
    callback(:after_update)

    #catch the duplicate error and update the existing record
  rescue Exception => e
    if (duplicate_columns(options) && options[:on_duplicate_key_update] &&
          connection.respond_to?('duplicate_key_update_error?') &&
          connection.duplicate_key_update_error?(e))
      update_existing_record(options)
      reloaded = true
    else
      raise
    end
  end

  load_duplicate_record(options) if options[:reload] && !reloaded

  return true
end