class FastInserter::Base

Constants

DEFAULT_GROUP_SIZE

Public Class Methods

new(params) click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 48
def initialize(params)
  @table_name = params[:table]
  @static_columns = params[:static_columns]
  @additional_columns = params[:additional_columns]
  @variable_columns = Array(params[:variable_columns] || params[:variable_column])
  @options = params[:options] || {}

  # We want to break up the insertions into multiple transactiosn in case there
  # is a very large amount of values. This avoids PG:OutOfMemory errors and smooths
  # out the load. The second 'false' param means don't fill in the last group with nil elements.
  all_values = params[:values].map { |value| Array(value) }
  all_values.uniq! if @options[:unique]
  group_size = Integer(params[:group_size] || ENV['FAST_INSERTER_GROUP_SIZE'] || DEFAULT_GROUP_SIZE)
  @value_groups = all_values.in_groups_of(group_size, false)
end

Public Instance Methods

fast_insert() click to toggle source

Iterates through the value groups (which is all values in groups of smaller sizes) and generates and executes a transaction to insert those groups one at a time

# File lib/fast_inserter/fast_inserter_base.rb, line 66
def fast_insert
  return if nothing_to_insert?

  @value_groups.each do |group|
    fast_insert_group(group)
  end
end

Private Instance Methods

all_static_columns() click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 140
def all_static_columns
  @all_static_columns ||= begin
    rv = @static_columns.dup

    if @options[:timestamps]
      time = Time.now
      rv[:created_at] = time
      rv[:updated_at] = time
    end

    if @additional_columns.present?
      @additional_columns.each do |key, value|
        rv[key] = value
      end
    end

    rv
  end
end
column_definitions() click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 172
def column_definitions
  @column_definitions ||= begin
    ActiveRecord::Base.connection.columns(@table_name).reduce({}) do |memo, column|
      memo.merge!(column.name => column)
    end
  end
end
column_names() click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 136
def column_names
  "#{all_static_columns.keys.join(', ')}, #{@variable_columns.join(', ')}"
end
existing_values(group_of_values) click to toggle source

Queries for the existing values for a given group of values

# File lib/fast_inserter/fast_inserter_base.rb, line 96
def existing_values(group_of_values)
  sql = "SELECT #{@variable_columns.join(', ')} FROM #{@table_name} WHERE #{existing_values_static_columns}"

  # NOTE: There are more elegant ways to get this field out of the resultset, but each database adaptor returns a different type
  # of result from 'execute(sql)'. Potential classes for 'result' is Array (sqlite), Mysql2::Result (mysql2), PG::Result (pg). Each
  # result can be enumerated into a list of arrays (mysql) or list of hashes (sqlite, pg)
  results = ActiveRecord::Base.connection.execute(sql)
  existing_values = stringify_values(results)

  # Rather than a giant IN query in the sql statement (which can be bad for database performance),
  # do the filtering of relevant values here in a ruby select.
  group_of_values_strings = stringify_values(group_of_values)
  existing_values & group_of_values_strings
end
existing_values_static_columns() click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 121
def existing_values_static_columns
  @static_columns.map do |key, value|
    if value.nil?
      "#{key} IS NULL"
    else
      sanitized_value = ActiveRecord::Base.send(:sanitize_sql_array, ["?", value])
      "#{key} = #{sanitized_value}"
    end
  end.join(' AND ')
end
fast_insert_group(group) click to toggle source

For a given group of IDS, generates the transaction to execute and does. If we want to check existing we make sure that we check the existing within the same transaction.

# File lib/fast_inserter/fast_inserter_base.rb, line 82
def fast_insert_group(group)
  if @options[:check_for_existing]
    ActiveRecord::Base.transaction do
      non_existing_values = stringify_values(group) - existing_values(group)
      sql_string = insertion_sql_for_group(non_existing_values)
      ActiveRecord::Base.connection.execute(sql_string) unless non_existing_values.empty?
    end
  else
    sql_string = insertion_sql_for_group(group)
    ActiveRecord::Base.connection.execute(sql_string)
  end
end
insert_values(group_of_values) click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 160
def insert_values(group_of_values)
  rv = []
  static_column_values = ActiveRecord::Base.send(:sanitize_sql_array, ["?", all_static_columns.values])

  group_of_values.each do |values|
    values = values.map { |value| ActiveRecord::Base.send(:sanitize_sql_array, ["?", value]) }
    rv << "(#{static_column_values},#{values.join(',')})"
  end

  rv.join(', ')
end
insertion_sql_for_group(group_of_values) click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 132
def insertion_sql_for_group(group_of_values)
  "INSERT INTO #{@table_name} (#{column_names}) VALUES #{insert_values(group_of_values)}"
end
nothing_to_insert?() click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 76
def nothing_to_insert?
  @value_groups.empty?
end
stringify_values(results) click to toggle source
# File lib/fast_inserter/fast_inserter_base.rb, line 111
def stringify_values(results)
  results.to_a.map do |result|
    if result.is_a?(Hash)
      @variable_columns.map { |col| ActiveRecord::Base.connection.type_cast(result[col], column_definitions[col]) }
    elsif result.is_a?(Array)
      result.map.with_index { |val, i| ActiveRecord::Base.connection.type_cast(val, column_definitions[@variable_columns[i]]) }
    end
  end
end