Module: InventoryRefresh::SaveCollection::Saver::SqlHelper

Extended by:
ActiveSupport::Concern
Includes:
Logging
Included in:
Base
Defined in:
lib/inventory_refresh/save_collection/saver/sql_helper.rb

Instance Method Summary collapse

Methods included from Logging

#logger

Instance Method Details

#build_multi_selection_query(hashes) ⇒ String

Builds a multiselection conditions like (table1.a = a1 AND table2.b = b1) OR (table1.a = a2 AND table2.b = b2)

Parameters:

  • hashes (Array<Hash>) —

    data we want to use for the query

Returns:

  • (String) —

    condition usable in .where of an ActiveRecord relation


37
38
39
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 37

def build_multi_selection_query(hashes)
  inventory_collection.build_multi_selection_condition(hashes, unique_index_columns)
end

#complement_of!(manager_uuids, all_manager_uuids_scope, all_manager_uuids_timestamp) ⇒ Arel::SelectManager

Effective way of doing multiselect

If we use “(col1, col2) IN [(a,e), (b,f), (b,e)]” it’s not great, just with 10k batch, we see *** ActiveRecord::StatementInvalid Exception: PG::StatementTooComplex: ERROR: stack depth limit exceeded HINT: Increase the configuration parameter “max_stack_depth” (currently 2048kB), after ensuring the platform’s stack depth limit is adequate.

If we use “(col1 = a AND col2 = e) OR (col1 = b AND col2 = f) OR (col1 = b AND col2 = e)” with 10k batch, it takes about 6s and consumes 300MB, with 100k it takes ~1h and consume 3GB in Postgre process

The best way seems to be using CTE, where the list of values we want to map is turned to ‘table’ and we just do RIGHT OUTER JOIN to get the complement of given identifiers. Tested on getting complement of 100k items, using 2 cols (:ems_ref and :uid_ems) from total 150k rows. It takes ~1s and 350MB in Postgre process

Parameters:

  • manager_uuids (Array<String>, Array[Hash]) —

    Array with manager_uuids of entities. The keys have to match inventory_collection.manager_ref. We allow passing just array of strings, if manager_ref.size ==1, to spare some memory

Returns:

  • (Arel::SelectManager) —

    Arel for getting complement of uuids. This method modifies the passed manager_uuids to spare some memory


106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 106

def complement_of!(manager_uuids, all_manager_uuids_scope, all_manager_uuids_timestamp)
  all_attribute_keys       = inventory_collection.manager_ref
  all_attribute_keys_array = inventory_collection.manager_ref.map(&:to_s)

  active_entities     = Arel::Table.new(:active_entities)
  active_entities_cte = Arel::Nodes::As.new(
    active_entities,
    Arel.sql("(#{active_entities_query(all_attribute_keys_array, manager_uuids)})")
  )

  all_entities     = Arel::Table.new(:all_entities)
  all_entities_cte = Arel::Nodes::As.new(
    all_entities,
    Arel.sql("(#{all_entities_query(all_manager_uuids_scope, all_manager_uuids_timestamp).select(:id, *all_attribute_keys_array).to_sql})")
  )
  join_condition   = all_attribute_keys.map { |key| active_entities[key].eq(all_entities[key]) }.inject(:and)
  where_condition  = all_attribute_keys.map { |key| active_entities[key].eq(nil) }.inject(:and)

  active_entities
    .project(all_entities[:id])
    .join(all_entities, Arel::Nodes::RightOuterJoin)
    .on(join_condition)
    .with(active_entities_cte, all_entities_cte)
    .where(where_condition)
end

#get_connection ⇒ ActiveRecord::ConnectionAdapters::AbstractAdapter

Returns ActiveRecord connection.

Returns:

  • (ActiveRecord::ConnectionAdapters::AbstractAdapter) —

    ActiveRecord connection


29
30
31
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 29

def get_connection
  ActiveRecord::Base.connection
end

#pg_type_cast(value, sql_type) ⇒ String

Returns a type casted value in format needed by PostgreSQL

Parameters:

  • value (Object) —

    value we want to quote

  • sql_type (String) —

    PostgreSQL column type

Returns:

  • (String) —

    type casted value in format needed by PostgreSQL


79
80
81
82
83
84
85
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 79

def pg_type_cast(value, sql_type)
  if sql_type.nil?
    value
  else
    "#{value}::#{sql_type}"
  end
end

#quote(connection, value, name = nil, type_cast_for_pg = nil) ⇒ String

Quotes a value. For update query, the value also needs to be explicitly casted, which we can do by type_cast_for_pg param set to true.

Parameters:

  • connection (ActiveRecord::ConnectionAdapters::AbstractAdapter) —

    ActiveRecord connection

  • value (Object) —

    value we want to quote

  • name (Symbol) (defaults to: nil) —

    name of the column

  • type_cast_for_pg (Boolean) (defaults to: nil) —

    true if we want to also cast the quoted value

Returns:

  • (String) —

    quoted and based on type_cast_for_pg param also casted value


49
50
51
52
53
54
55
56
57
58
59
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 49

def quote(connection, value, name = nil, type_cast_for_pg = nil)
  # TODO(lsmola) needed only because UPDATE FROM VALUES needs a specific PG typecasting, remove when fixed in PG
  if type_cast_for_pg
    quote_and_pg_type_cast(connection, value, name)
  else
    connection.quote(value)
  end
rescue TypeError => e
  logger.error("Can't quote value: #{value}, of :#{name} and #{inventory_collection}")
  raise e
end

#quote_and_pg_type_cast(connection, value, name) ⇒ String

Quotes and type casts the value.

Parameters:

  • connection (ActiveRecord::ConnectionAdapters::AbstractAdapter) —

    ActiveRecord connection

  • value (Object) —

    value we want to quote

  • name (Symbol) —

    name of the column

Returns:

  • (String) —

    quoted and casted value


67
68
69
70
71
72
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 67

def quote_and_pg_type_cast(connection, value, name)
  pg_type_cast(
    connection.quote(value),
    pg_types[name]
  )
end

#quote_column_name(key) ⇒ Object

Returns quoted column name

Parameters:

  • key (Symbol) —

    key that is column name


24
25
26
 
# File 'lib/inventory_refresh/save_collection/saver/sql_helper.rb', line 24

def quote_column_name(key)
  get_connection.quote_column_name(key)
end