Skip to content

Instantly share code, notes, and snippets.

@katpadi katpadi/bulky.rb
Last active Apr 5, 2018

Embed
What would you like to do?
Bulky
# 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.