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