Class: ActiveSqlBindings

Inherits:
Object
  • Object
show all
Defined in:
lib/active_sql_bindings.rb

Overview

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

Class Method Summary collapse

Class Method Details

.array_to_postgres(array) ⇒ Object

Convert ruby array to Postgres array

Parameters:

  • array (Array) —

    data for converting



76
77
78
79
80
 
# 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:) ⇒ Object

Check fields’ type and replace it

Parameters:

  • key (String) —

    for check type

  • value (String) —

    for converting



58
59
60
61
62
63
 
# 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 = {}) ⇒ Array

Create sql query with hash named bindings

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

Parameters:

  • sql (String) —

    SQL query

  • bind (Hash) (defaults to: {}) —

    bindings data for query

Returns:

  • (Array) —

    executed SQL request data and return array with hashes



18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
# 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) ⇒ Hash

Convert JSON to hash if correct data

Parameters:

  • json (String) —

    string

Returns:

  • (Hash) —

    return hash if json is correct or input data



69
70
71
 
# 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) ⇒ Object

Convert Postgres array to ruby array

Parameters:

  • array (String) —

    string data for converting



85
86
87
88
89
 
# File 'lib/active_sql_bindings.rb', line 85

def postgres_to_array(array)
  return array.gsub(/[{}]/, '').split(',') if array.is_a?(String)

  []
end