ActiveRecord raw insert/update

Sometimes, usually for performance reasons, it might be necessary to do raw SQL statements. Most of the time save_without_transactions is all you might need. But, if you still want to explicitly call an insert or an update for whatever reason, you're able to use the model connection's execute function. However, using (ActiveRecord)AR I've gotten quite lazy with writing queries. Here's something quick dirty I whipped up to generate queries.

class ActiveRecord::Base
  def return_value_string(value)
    case value.class.to_s
      when "Time": "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
      when "NilClass": "NULL"
      when "Fixnum": value
      when "String": "'#{value.escape_single_quotes}'"
      when "FalseClass": '0'
      when "TrueClass": '1'
      else "'#{value}'"
    end
  end
 
  def generate_update_query
    "UPDATE #{self.class.table_name} SET " + 
    self.attributes.keys.sort.collect{ |key| 
    "`#{key}` = #{return_value_string(self.send(key))}" }.join(", ") + 
    " WHERE id = #{self.id}"
  end
 
  def generate_insert_query
    @key_vals = self.attributes.collect{ |key,value| 
    [key, return_value_string(value)] }
    "INSERT INTO #{self.class.table_name} " +
    "( #{@key_vals.collect{ |item| item[0].to_s }.join(", ") } ) " + 
    "VALUES( #{@key_vals.collect{ |item| item[1].to_s }.join(", ") } ) "
  end

  def raw_update
    self.class.connection.execute(self.generate_update_query)
  end
 
  def raw_insert
    self.class.connection.execute(self.generate_insert_query)
  end
 
end

Now I can just do object.raw_insert or object.raw_update. I tried cover most of the data types I can think of in the 'return_value_string' function (I'm working with MySQL), but let me know of anything I might have missed. Another thing to play with is connection.insert and connection.update. I believe connection.insert can return the id of the row you just created so that might some slight bit of overhead that can be avoided. Doing queries this way might be better suited for data migrations rather than normal application requests. There's still the option of tracing through AR and see how it generates queries but this works well enough for now. As always, feedback is welcome.

UPDATE:
here is the revamped version for raw_insert and raw_update ripped directly from activerecord source. now you can get the id back on your inserts too. yay!

def raw_update
  quoted_attributes = attributes_with_quotes(false)
  return 0 if quoted_attributes.empty?
  connection.update(
   "UPDATE #{self.class.table_name} " +
   "SET #{quoted_comma_pair_list(connection, quoted_attributes)} " +
   "WHERE #{connection.quote_column_name(self.class.primary_key)} = #{quote_value(id)}",
   "#{self.class.name} Update"
  )
 end
 
 def raw_insert
  if self.id.nil? && self.class.connection.prefetch_primary_key?(self.class.table_name)
   self.id = self.class.connection.next_sequence_value(self.class.sequence_name)
  end

  quoted_attributes = attributes_with_quotes

  statement = if quoted_attributes.empty?
   self.class.connection.empty_insert_statement(self.class.table_name)
  else
   "INSERT INTO #{self.class.table_name} " +
   "(#{quoted_column_names.join(', ')}) " +
   "VALUES(#{quoted_attributes.values.join(', ')})"
  end

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

  @new_record = false
  id
 end

ActiveRecord sans transactions

So we're doing this massive migration from this legacy schema for a client, but our estimates put our import time at around 75 hours. So I thought transactions should help out but there was a problem. I'd wrap my insert/update loops with ModelClass.connection.(begin / commit)_db_transaction, but I noticed that every save call would automatically do BEGIN and COMMIT. After digging around for turning off this behavior at the connection level and getting nowhere, I saw method on my object. The function "save_without_transactions" was exactly what I was looking for. Of course there wasn't much documentation that I could find on this function. This might be useful to some newbies going through similar ActiveRecord issues as myself. This function also comes with the exclamation flavor that throws up an Exception. Hope this was helpful to someone looking to use ActiveRecord without transactions.