Bulky

bulky.rb

# Bulky
#
# Bulky is a simple ActiveRecord extension to insert or update a bulk of rows in a single statement.
#
module Bulky
  # Bulk insert multiple rows into a table
  #
  # Usage:
  #
  # Post.bulk_insert(['name', 'label'], [['ch', 'Label 1'], ['ch_2', 'Label 2']])
  #
  def bulk_insert(cols, data, options = {})
    return if data.empty?
    ActiveRecord::Base.transaction do
      connection.execute(SqlBuilder.new(cols, data, options).for_insert)
    end
  end

  # Bulk update multiple rows into a table
  # If a row id defined in first array doesn't exist, it will be created.
  #
  # Usage:
  #
  # Post.bulk_update(['id', 'label', 'description'], [[1, 'A', 'a'], [2, 'B', 'b']])
  #
  def bulk_update(cols, data)
    return if data.empty?
    ActiveRecord::Base.transaction do
      connection.execute(SqlBuilder.new(cols, data, options).for_update)
    end
  end
 
  class SqlBuilder
    attr_reader :cols, :data

    def initialize(cols, data, updatable_cols = [])
      @cols = cols
      @data = data
      @updatable_cols = cols & (updatable_cols - [primary_key])
    end

    def for_insert
      "INSERT INTO #{quoted_table} (#{quoted_cols}) VALUES #{quoted_data}"
    end

    def for_update
      update_clauses = updatable_cols.map { |name| "#{name}=VALUES(#{name})" }.join(',')
      insert_sql << " ON DUPLICATE KEY UPDATE #{update_clauses}"
    end

    private

    # Make use of quoting helpers from ActiveRecord itself
    def quoted_table
      connection.quote_table_name(table_name)
    end

    def quoted_cols
      cols.map { |col| connection.quote_column_name(col) }.join(',')
    end

    def quoted_data
      data.map { |row| "(#{quoted_row(row)})" }.join(',')
    end

    def quoted_row(row)
      row.map { |value| connection.quote(value) }.join(',')
    end
  end
end

ActiveRecord::Base.extend(Bulky)