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
-
#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).
-
#complement_of!(manager_uuids, all_manager_uuids_scope, all_manager_uuids_timestamp) ⇒ Arel::SelectManager
Effective way of doing multiselect.
-
#get_connection ⇒ ActiveRecord::ConnectionAdapters::AbstractAdapter
ActiveRecord connection.
-
#pg_type_cast(value, sql_type) ⇒ String
Returns a type casted value in format needed by PostgreSQL.
-
#quote(connection, value, name = nil, type_cast_for_pg = nil) ⇒ String
Quotes a value.
-
#quote_and_pg_type_cast(connection, value, name) ⇒ String
Quotes and type casts the value.
-
#quote_column_name(key) ⇒ Object
Returns quoted column name.
Methods included from Logging
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_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, ).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.
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 |