Module: Motor::Queries::RunQuery

Defined in:
lib/motor/queries/run_query.rb

Defined Under Namespace

Classes: QueryResult

Constant Summary collapse

DEFAULT_LIMIT = 
100_000
SUBQUERY_NAME = 
'__query__'
STATEMENT_VARIABLE_REGEXP = 
/\$\d+/.freeze
PG_ERROR_REGEXP = 
/\APG.+ERROR:/.freeze
RESERVED_VARIABLES = 
%w[current_user_id current_user_email].freeze

Class Method Summary collapse

Class Method Details

.build_columns_hash(result) ⇒ Hash

Parameters:

  • result (ActiveRecord::Result)

Returns:

  • (Hash) 


78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/motor/queries/run_query.rb', line 78

def build_columns_hash(result)
  result.columns.map.with_index do |column_name, index|
    column_type_class = result.column_types[column_name]

    column_type = ActiveRecordUtils::Types.find_name_for_type(column_type_class) if column_type_class

    column_type ||=
      begin
        not_nil_value = result.rows.reduce(nil) do |acc, row|
          column = row[index]

          break column unless column.nil?

          acc
        end

        fetch_column_type_from_value(not_nil_value)
      end

    {
      name: column_name,
      display_name: column_name.humanize,
      column_type: column_type,
      is_array: column_type.class.to_s == 'ActiveRecord::ConnectionAdapters::PostgreSQL::OID::Array'
    }
  end
end

.build_error_message(exception) ⇒ String

Parameters:

  • exception (ActiveRecord::StatementInvalid)

Returns:

  • (String) 


46
47
48
 
# File 'lib/motor/queries/run_query.rb', line 46

def build_error_message(exception)
  exception.message.sub(PG_ERROR_REGEXP, '').strip.upcase_first
end

.build_filters_arel(filters) ⇒ Arel::Nodes?

Parameters:

  • filters (Hash)

Returns:

  • (Arel::Nodes, nil) 


165
166
167
168
169
170
171
172
173
 
# File 'lib/motor/queries/run_query.rb', line 165

def build_filters_arel(filters)
  return nil if filters.blank?

  table = Arel::Table.new(SUBQUERY_NAME)

  arel_filters = filters.map { |key, value| table[key].in(value) }

  arel_filters[1..].reduce(arel_filters.first) { |acc, arel| acc.and(arel) }
end

.build_select_sql(connection_class, sql, limit, filters) ⇒ String

Parameters:

  • connection_class (Class)
  • sql (String)
  • limit (Number)
  • filters (Hash)

Returns:

  • (String) 


147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/motor/queries/run_query.rb', line 147

def build_select_sql(connection_class, sql, limit, filters)
  sql = normalize_sql(sql)

  subquery_sql = Arel.sql("(#{sql})").as(connection_class.connection.quote_column_name(SUBQUERY_NAME))

  arel_filters = build_filters_arel(filters)

  rel = connection_class.from(subquery_sql)
                        .select(Arel::Table.new(SUBQUERY_NAME)[Arel.star])
                        .where(arel_filters)

  rel = rel.limit(limit.to_i) unless connection_class.connection.class.name.include?('SQLServerAdapter')

  rel.to_sql
end

.build_statement_attributes(variables) ⇒ Array<ActiveRecord::Relation::QueryAttribute>

Parameters:

  • variables (Array<(String, Object)>)

Returns:

  • (Array<ActiveRecord::Relation::QueryAttribute>) 


177
178
179
180
181
182
183
184
185
186
187
 
# File 'lib/motor/queries/run_query.rb', line 177

def build_statement_attributes(variables)
  variables.map do |variable_name, value|
    [value].flatten.map do |val|
      ActiveRecord::Relation::QueryAttribute.new(
        variable_name,
        val,
        ActiveRecord::Type::Value.new
      )
    end
  end.flatten
end

.call(query, variables_hash: nil, limit: nil, filters: nil) ⇒ Motor::Queries::RunQuery::QueryResult

Parameters:

  • query (Motor::Query)
  • variables_hash (Hash) (defaults to: nil)
  • limit (Integer) (defaults to: nil)

Returns:



38
39
40
41
42
 
# File 'lib/motor/queries/run_query.rb', line 38

def call(query, variables_hash: nil, limit: nil, filters: nil)
  call!(query, variables_hash: variables_hash, limit: limit, filters: filters)
rescue ActiveRecord::StatementInvalid => e
  QueryResult.new(error: build_error_message(e))
end

.call!(query, variables_hash: nil, limit: nil, filters: nil) ⇒ Motor::Queries::RunQuery::QueryResult

Parameters:

  • query (Motor::Query)
  • variables_hash (Hash) (defaults to: nil)
  • limit (Integer) (defaults to: nil)
  • filters (Hash) (defaults to: nil)

Returns:



24
25
26
27
28
29
30
31
32
 
# File 'lib/motor/queries/run_query.rb', line 24

def call!(query, variables_hash: nil, limit: nil, filters: nil)
  variables_hash ||= {}
  limit ||= DEFAULT_LIMIT
  filters ||= {}

  result = execute_query(query, limit, variables_hash, filters)

  QueryResult.new(data: result.rows, columns: build_columns_hash(result))
end

.connection_class ⇒ Object 



216
217
218
219
220
221
 
# File 'lib/motor/queries/run_query.rb', line 216

def connection_class
  @connection_class ||=
    'ResourceRecord'.safe_constantize ||
    'ApplicationRecord'.safe_constantize ||
    Class.new(ActiveRecord::Base).tap { |e| e.abstract_class = true }
end

.execute_query(query, limit, variables_hash, filters) ⇒ ActiveRecord::Result

Parameters:

  • query (Motor::Query)
  • limit (Integer)
  • variables_hash (Hash)
  • filters (Hash)

Returns:

  • (ActiveRecord::Result) 


55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/motor/queries/run_query.rb', line 55

def execute_query(query, limit, variables_hash, filters)
  result = nil
  statement = prepare_sql_statement(connection_class, query, limit, variables_hash, filters)

  connection_class.transaction do
    result =
      case connection_class.connection.class.name
      when 'ActiveRecord::ConnectionAdapters::PostgreSQLAdapter'
        PostgresqlExecQuery.call(connection_class.connection, statement)
      else
        statement = normalize_statement_for_sql(statement)

        connection_class.connection.exec_query(*statement)
      end

    raise ActiveRecord::Rollback
  end

  result
end

.fetch_column_type_from_value(value) ⇒ String

Parameters:

  • value (Object)

Returns:

  • (String) 


108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/motor/queries/run_query.rb', line 108

def fetch_column_type_from_value(value)
  case value
  when Integer
    'integer'
  when Float
    'float'
  when Time
    'datetime'
  when Date
    'date'
  when TrueClass, FalseClass
    'boolean'
  else
    'string'
  end
end

.merge_variable_default_values(variable_configs, variables_hash) ⇒ Hash

Parameters:

  • variable_configs (Array<Hash>)
  • variables_hash (Hash)

Returns:

  • (Hash) 


208
209
210
211
212
213
214
 
# File 'lib/motor/queries/run_query.rb', line 208

def merge_variable_default_values(variable_configs, variables_hash)
  variable_configs.each_with_object(variables_hash.slice(*RESERVED_VARIABLES)) do |variable, acc|
    next if RESERVED_VARIABLES.include?(variable[:name])

    acc[variable[:name]] ||= variables_hash[variable[:name]] || variable[:default_value]
  end
end

.normalize_sql(sql) ⇒ Object 



201
202
203
 
# File 'lib/motor/queries/run_query.rb', line 201

def normalize_sql(sql)
  sql.strip.delete_suffix(';').gsub(/\A\)+/, '').gsub(/\z\(+/, '')
end

.normalize_statement_for_sql(statement) ⇒ Array

Parameters:

  • array (Array)

Returns:

  • (Array) 


191
192
193
194
195
196
197
198
199
 
# File 'lib/motor/queries/run_query.rb', line 191

def normalize_statement_for_sql(statement)
  sql, _, attributes = statement

  sql = ActiveRecord::Base.send(:replace_bind_variables,
                                sql.gsub(STATEMENT_VARIABLE_REGEXP, '?'),
                                attributes.map(&:value))

  [sql, 'SQL', []]
end

.prepare_sql_statement(connection_class, query, limit, variables_hash, filters) ⇒ Array

Parameters:

  • connection_class (Class)
  • query (Motor::Query)
  • limit (Integer)
  • variables_hash (Hash)
  • filters (Hash)

Returns:

  • (Array) 


131
132
133
134
135
136
137
138
139
140
 
# File 'lib/motor/queries/run_query.rb', line 131

def prepare_sql_statement(connection_class, query, limit, variables_hash, filters)
  variables = merge_variable_default_values(query.preferences.fetch(:variables, []), variables_hash)

  sql, query_variables = RenderSqlTemplate.call(query.sql_body, variables)
  select_sql = build_select_sql(connection_class, sql, limit, filters)

  attributes = build_statement_attributes(query_variables)

  [select_sql, 'SQL', attributes]
end