module RawSqlBuilder
Constants
- VERSION
Public Class Methods
execute(query)
click to toggle source
# File lib/raw_sql_builder.rb, line 20 def execute(query) ActiveRecord::Base.connection.execute(query) end
mass_create(objects)
click to toggle source
Public methods ######
Raw SQL methods
# File lib/raw_sql_builder.rb, line 8 def mass_create(objects) run([*objects], :create) end
mass_create_or_update(objects)
click to toggle source
# File lib/raw_sql_builder.rb, line 16 def mass_create_or_update(objects) run([*objects], :both) end
mass_update(objects)
click to toggle source
# File lib/raw_sql_builder.rb, line 12 def mass_update(objects) run([*objects], :update) end
Private Class Methods
check_and_execute(type)
click to toggle source
# File lib/raw_sql_builder.rb, line 45 def check_and_execute(type) if create?(type) results = execute(get_create_query) if @creates.size > 0 end if update?(type) execute(get_update_query) if @updates_hash.size > 0 get_update_exception_queries.each do |query| execute(query) end end results end
convert_hash()
click to toggle source
Hashes
# File lib/raw_sql_builder.rb, line 274 def convert_hash { datetime: 'timestamp', string: 'text', fixnum: 'integer', boolean: 'boolean' } end
convert_type(attributes)
click to toggle source
# File lib/raw_sql_builder.rb, line 263 def convert_type(attributes) if attributes.type == :text return 'text[]' if attributes.array 'text' else convert_hash[attributes.type] || attributes.type.to_s end end
create?(type)
click to toggle source
# File lib/raw_sql_builder.rb, line 102 def create?(type) type == :create || type == :both end
create_query(keys_string, values_string)
click to toggle source
# File lib/raw_sql_builder.rb, line 155 def create_query(keys_string, values_string) "INSERT INTO #{@table_name} (\"#{keys_string}\") VALUES (#{values_string}) RETURNING id;" end
create_values()
click to toggle source
# File lib/raw_sql_builder.rb, line 146 def create_values @creates.map do |o| o.attributes.except('id').map do |k, v| @type = convert_type(@columns_hash[k]) format_value(v, 'DEFAULT') end end end
create_values_string()
click to toggle source
Create sub-methods
# File lib/raw_sql_builder.rb, line 142 def create_values_string create_values.map { |v| v.join(', ') }.join('), (') end
filter_changes(changes)
click to toggle source
Helper methods
# File lib/raw_sql_builder.rb, line 220 def filter_changes(changes) return {} unless changes.size > 0 filtered_changes = {} changes.each do |k, v| filtered_changes[k] = v[1] unless v[0].blank? && v[1].blank? end filtered_changes end
format_value(value, default = 'NULL')
click to toggle source
# File lib/raw_sql_builder.rb, line 239 def format_value(value, default = 'NULL') if value.present? || value == false "'#{type_formatting(value)}'" else default end end
get_create_query()
click to toggle source
Getting create/update queries
# File lib/raw_sql_builder.rb, line 120 def get_create_query keys_string = (@columns_hash.keys - ['id']).join('", "') values_string = create_values_string create_query(keys_string, values_string) end
get_table_name(name)
click to toggle source
# File lib/raw_sql_builder.rb, line 229 def get_table_name(name) name = name.deconstantize unless name.deconstantize.blank? name.tableize end
get_update_arrays(columns_info)
click to toggle source
Update sub-methods
# File lib/raw_sql_builder.rb, line 161 def get_update_arrays(columns_info) keys_array = [] values_array = [] columns_info.each do |k, v| @column = k @type = convert_type(v) values_array << update_values_string keys_array << update_keys_string unless @column == 'id' end [keys_array, values_array] end
get_update_exception_queries()
click to toggle source
# File lib/raw_sql_builder.rb, line 134 def get_update_exception_queries @update_exceptions_hash.map do |exception| update_exception_query(exception) end end
get_update_query()
click to toggle source
# File lib/raw_sql_builder.rb, line 126 def get_update_query changed_columns = @updates_hash.map { |c| c.values.map(&:keys) }.flatten.uniq columns_info = @columns_hash.slice(*(['id'] + changed_columns)) return unless columns_info.size > 1 update_query(*get_update_arrays(columns_info)) end
objects_count(type)
click to toggle source
# File lib/raw_sql_builder.rb, line 234 def objects_count(type) return @creates.size if type == :create @updates_hash.size + @update_exceptions_hash.size end
organize_objects(objects, type)
click to toggle source
# File lib/raw_sql_builder.rb, line 75 def organize_objects(objects, type) objects.each do |o| next if o.blank? if o.new_record? && create?(type) @creates << o elsif o.changed? && update?(type) filtered_changes = filter_changes(o.changes) next if filtered_changes.blank? organize_updates(o, filtered_changes) @updates << o end set_created_at_and_updated_at(o) o.changes_applied end end
organize_updates(object, filtered_changes)
click to toggle source
# File lib/raw_sql_builder.rb, line 92 def organize_updates(object, filtered_changes) check_changes = filtered_changes.values.map { |v| true if v.blank? }.compact if check_changes.any? @update_exceptions_hash << { object.id => filtered_changes } else @updates_hash << { object.id => filtered_changes } end end
response_hash()
click to toggle source
# File lib/raw_sql_builder.rb, line 283 def response_hash { create: { skip: "No eligible objects to create for #{@object_class}... Skipping.", complete: "#{@object_class} creating complete." }, update: { skip: "No changed columns for update for #{@object_class}... Skipping.", complete: "#{@object_class} updating complete." } } end
run(objects, type)
click to toggle source
Private methods ######
Raw SQL private methods
# File lib/raw_sql_builder.rb, line 29 def run(objects, type) return puts 'No objects passed through.' if objects.blank? setup_and_loop_objects(objects, type) puts "Creates: #{@creates.size}, Updates: #{@updates.size}" results = check_and_execute(type) set_changes_applied(results) true end
set_changes_applied(results = nil)
click to toggle source
# File lib/raw_sql_builder.rb, line 58 def set_changes_applied(results = nil) return if @creates.none? && @updates.none? @creates.each_with_index do |c, i| c.id = results.values[i][0] end end
set_created_at_and_updated_at(object)
click to toggle source
# File lib/raw_sql_builder.rb, line 110 def set_created_at_and_updated_at(object) object.created_at = Time.zone.now if @columns_hash['created_at'].present? && object.created_at.blank? object.updated_at = Time.zone.now if @columns_hash['updated_at'].present? && object.updated_at.blank? object end
setup_and_loop_objects(objects, type)
click to toggle source
Run sub-methods
# File lib/raw_sql_builder.rb, line 40 def setup_and_loop_objects(objects, type) setup_globals(objects) organize_objects(objects, type) end
setup_globals(objects)
click to toggle source
Run methods sub-methods
# File lib/raw_sql_builder.rb, line 68 def setup_globals(objects) @object_class = objects.first.class @table_name = get_table_name(@object_class.name) @columns_hash = @object_class.columns_hash @creates, @updates, @updates_hash, @update_exceptions_hash = [], [], [], [] end
type_formatting(value)
click to toggle source
# File lib/raw_sql_builder.rb, line 247 def type_formatting(value) @type = 'json' if @type == 'text' && (value.is_a?(Hash) || value.is_a?(Array)) if @type == 'text[]' value = value.flatten.uniq.to_s if value.is_a?(Array) value = "{#{value.trim_ends(%w([ ]))}}" elsif @type == 'hstore' && value.is_a?(Hash) value = value.map do |k, v| "\"#{k}\"=>\"#{v.to_s.gsub('=>', ':').gsub('"', '\"')}\"" end.join(', ') elsif @type == 'json' value = "#{value.to_s.gsub('=>', ':')}" end value.to_s.gsub("'nil'", "'null'").gsub("'", "''").gsub("''''", "''") end
update?(type)
click to toggle source
# File lib/raw_sql_builder.rb, line 106 def update?(type) type == :update || type == :both end
update_array()
click to toggle source
# File lib/raw_sql_builder.rb, line 185 def update_array @updates_hash.map do |c| c.values.map do |v| format_value(v[@column]) end end end
update_exception_query(exception)
click to toggle source
# File lib/raw_sql_builder.rb, line 213 def update_exception_query(exception) "UPDATE #{@table_name} SET #{update_exception_values_array(exception).join(', ')} WHERE #{@table_name}.id = #{exception.keys.first};" end
update_exception_values_array(exception)
click to toggle source
Update Exception sub-methods
# File lib/raw_sql_builder.rb, line 204 def update_exception_values_array(exception) exception.values.map do |hash| hash.map do |k, v| @type = convert_type(@columns_hash[k]) "\"#{k}\" = #{format_value(v)}::#{@type}" end end end
update_keys_string()
click to toggle source
# File lib/raw_sql_builder.rb, line 193 def update_keys_string "\"#{@column}\" = COALESCE(source.#{@column}::#{@type}, #{@table_name}.#{@column}::#{@type})" end
update_query(keys_array, values_array)
click to toggle source
# File lib/raw_sql_builder.rb, line 197 def update_query(keys_array, values_array) "UPDATE #{@table_name} SET #{keys_array.join(', ')} FROM ( SELECT #{values_array.join(', ')}) as source WHERE #{@table_name}.id = source.id;" end
update_values()
click to toggle source
# File lib/raw_sql_builder.rb, line 177 def update_values if @column == 'id' @updates_hash.map(&:keys) else update_array.join(', ') end end
update_values_string()
click to toggle source
# File lib/raw_sql_builder.rb, line 173 def update_values_string "unnest(array[#{update_values}])#{"::#{@type}"} as #{@column}" end