class ActiveRecord::Base

Public Class Methods

bulk_write_mysql(fields, rows, upsert = nil) click to toggle source

fields = %w(name email city created_at updated_at) rows = [

["smile", '2268571581@qq.com', 'shanghai', '2016-01-01', '2016-01-01'],
['malzahar', 'm8023zsm@live.com', 'beijing', '2016-01-01', '2016-01-01'],

]

# File lib/db_upsert.rb, line 49
def self.bulk_write_mysql(fields, rows, upsert = nil)
  return 0 if rows.empty?
  rows = rows.map do |row|
    values = row.map.with_index do |val|
      connection.quote(val)
    end.join ', '
    "(#{ values })"
  end
  field_list = fields.map {|e| %Q("#{e}")}.join ', '

  sql = "INSERT INTO #{ table_name } (#{ field_list }) VALUES #{ rows.join ', ' }"
  if upsert
    if !upsert[:update]
      update = fields.map(&:to_s) - upsert[:conflict].map(&:to_s)
    else
      update = upsert[:update]
    end
    update = update.map {|field| "#{ field } = VALUES#{ field }"}

    sql += " ON DUPLICATE KEY UPDATE #{ update.join ', ' }"
    if upsert[:where]
      sql += " WHERE #{ upsert[:where] }"
    end
  end
  res = connection.execute(sql)
  res.cmd_tuples
end
bulk_write_pg(fields, rows, upsert = nil) click to toggle source

fields = %w(name email city created_at updated_at) rows = [

["smile", '2268571581@qq.com', 'shanghai', '2016-01-01', '2016-01-01'],
['malzahar', 'm8023zsm@live.com', 'beijing', '2016-01-01', '2016-01-01'],

] BulkUpsert.bulk_write(fields, rows, :conflict => [:name]) conflict: uniq index columns update: if conflict update columns

# File lib/db_upsert.rb, line 14
def self.bulk_write_pg(fields, rows, upsert = nil)
  return 0 if rows.empty?
  rows = rows.map do |row|
    values = row.map.with_index do |val|
      connection.quote(val)
    end.join ', '
    "(#{ values })"
  end
  field_list = fields.map {|e| %Q("#{e}")}.join ', '

  sql = "INSERT INTO #{ table_name } (#{ field_list }) VALUES #{ rows.join ', ' }"
  if upsert
    if !upsert[:update]
      update = fields.map(&:to_s) - upsert[:conflict].map(&:to_s)
    else
      update = upsert[:update]
    end
    update = update.map {|field| "#{ field } = EXCLUDED.#{ field }"}

    sql += " ON CONFLICT (#{ upsert[:conflict].join ', ' }) DO UPDATE SET #{ update.join ', ' }"
    if upsert[:where]
      sql += " WHERE #{ upsert[:where] }"
    end
  end
  res = connection.execute(sql)
  res.cmd_tuples
end