class ActiveSqlBindings

Class for work with SQL query. You can use native SQL with bindings as hash. Auto converting JSON fields to hash.

Public Class Methods

array_to_postgres(array) click to toggle source

Convert ruby array to Postgres array

@param [Array] array data for converting

# File lib/active_sql_bindings.rb, line 76
def array_to_postgres(array)
  array = [array] if array.is_a?(String)

  '{' + array.join(',') + '}'
end
check_fields(key:, value:, fields:) click to toggle source

Check fields' type and replace it

@param [String] key for check type @param [String] value for converting

# File lib/active_sql_bindings.rb, line 58
def check_fields(key:, value:, fields:)
  return json_to_hash(value) if fields[:json].include?(key)
  return postgres_to_array(value) if fields[:array].include?(key)

  value
end
execute(sql, bind = {}) click to toggle source

Create sql query with hash named bindings

Example: ActiveSqlBindings.execute('SELECT name FROM test WHERE id = :id', id: id)

@param [String] sql SQL query @param [Hash] bind bindings data for query

@return [Array] executed SQL request data and return array with hashes

# File lib/active_sql_bindings.rb, line 18
def execute(sql, bind = {})
  bindings = []
  bind_index = 1

  # Get all bindings if exist
  unless bind.empty?
    bind.each do |key, value|
      # Change name bind to $ bind
      sql.gsub!(/(?<!:):#{key}(?=\b)/, "$#{bind_index}")
      bind_index += 1

      # Add new bind data
      bindings << [nil, value]
    end
  end

  # Execute query, convert to hash with symbol keys
  sql_result = ActiveRecord::Base.connection.exec_query(sql, 'SQL', bindings)

  # Find fields JSON/JSONb type
  json_fields = sql_result.column_types.select { |_k, v| v.class.name.split('::').last.downcase.to_sym == :json || v.class.name.split('::').last.downcase.to_sym == :jsonb }.keys

  # Find fields ARRAY type
  array_fields = sql_result.column_types.select { |_k, v| v.class.name.split('::').last.downcase.to_sym == :array }.keys

  # Convert JSON data to hash
  sql_result.map do |v|
    v.map do |key, value|
      [
        key.to_sym,
        check_fields(key: key, value: value, fields: { json: json_fields, array: array_fields })
      ]
    end.to_h
  end
end
json_to_hash(json) click to toggle source

Convert JSON to hash if correct data

@param [String] json string @return [Hash] return hash if json is correct or input data

# File lib/active_sql_bindings.rb, line 69
def json_to_hash(json)
  JSON.parse(json, symbolize_names: true) rescue json
end
postgres_to_array(array) click to toggle source

Convert Postgres array to ruby array

@param [String] array string data for converting

# File lib/active_sql_bindings.rb, line 85
def postgres_to_array(array)
  return array.gsub(/[{}]/, '').split(',') if array.is_a?(String)

  []
end