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)



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



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 



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



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.



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.



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



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