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
DATABASE_URL_VARIABLE_SUFFIX = 
'_database_url'
QUERY_VARIABLE_PREFIX = 
'query_'
/(.*?)\s*\{\{\s*\w+_database_url\s*\}\}/i.freeze
UnknownDatabase = 
Class.new(StandardError)
UnsafeDatabaseUrlUsage = 
Class.new(StandardError)

Class Method Summary collapse

Class Method Details

.build_columns_hash(result) ⇒ Hash

Parameters:

  • result (ActiveRecord::Result)

Returns:

  • (Hash) 


87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/motor/queries/run_query.rb', line 87

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) 


55
56
57
 
# File 'lib/motor/queries/run_query.rb', line 55

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) 


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

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) 


158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
 
# File 'lib/motor/queries/run_query.rb', line 158

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>) 


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

def build_statement_attributes(variables)
  variables.map do |variable_name, value|
    [value].flatten.map do |val|
      val = fetch_variable_database_url(variable_name) if variable_name.ends_with?(DATABASE_URL_VARIABLE_SUFFIX)
      val = fetch_query_data(variable_name) if variable_name.starts_with?(QUERY_VARIABLE_PREFIX)

      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:



47
48
49
50
51
 
# File 'lib/motor/queries/run_query.rb', line 47

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:



32
33
34
35
36
37
38
39
40
41
 
# File 'lib/motor/queries/run_query.rb', line 32

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

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

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

.default_connection_class ⇒ Object 



282
283
284
285
286
 
# File 'lib/motor/queries/run_query.rb', line 282

def default_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) 


64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/motor/queries/run_query.rb', line 64

def execute_query(query, limit, variables_hash, filters)
  connection_class = fetch_connection_class(query)

  statement = prepare_sql_statement(connection_class, query, limit, variables_hash, filters)

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

    connection_class.connection.exec_query(*statement)
  end
end

.fetch_ar_configurations_connection(database_name, ar_configurations) ⇒ Object 



264
265
266
267
268
269
270
271
272
273
274
 
# File 'lib/motor/queries/run_query.rb', line 264

def fetch_ar_configurations_connection(database_name, ar_configurations)
  Motor::DatabaseClasses.const_get(database_name.classify)
rescue NameError
  klass = Class.new(ActiveRecord::Base)

  Motor::DatabaseClasses.const_set(database_name.classify, klass)

  klass.establish_connection(ar_configurations.name.to_sym)

  klass
end

.fetch_column_type_from_value(value) ⇒ String

Parameters:

  • value (Object)

Returns:

  • (String) 


117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/motor/queries/run_query.rb', line 117

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

.fetch_connection_class(query) ⇒ Object 



247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'lib/motor/queries/run_query.rb', line 247

def fetch_connection_class(query)
  database_name = query.preferences[:database]

  return default_connection_class if database_name.blank? || database_name == 'default'

  return default_connection_class if database_name == 'primary'

  ar_configurations = ActiveRecord::Base.configurations.configurations
                                        .find { |c| c.name == database_name && c.env_name == Rails.env }

  if ar_configurations
    fetch_ar_configurations_connection(database_name, ar_configurations)
  else
    Motor::DatabaseClasses.const_get(database_name.sub(/\A\d+/, '').parameterize.underscore.classify)
  end
end

.fetch_query_data(variable_name) ⇒ Object 



211
212
213
214
215
216
217
218
 
# File 'lib/motor/queries/run_query.rb', line 211

def fetch_query_data(variable_name)
  query = Motor::Query.find(variable_name.split('_').last)

  result = Motor::Queries::RunQuery.call(query)
  columns = result.columns.pluck(:name)

  result.data.map { |row| columns.zip(row).to_h }.to_json
end

.fetch_variable_database_url(variable_name) ⇒ Object 



203
204
205
206
207
208
209
 
# File 'lib/motor/queries/run_query.rb', line 203

def fetch_variable_database_url(variable_name)
  class_name = variable_name.delete_suffix(DATABASE_URL_VARIABLE_SUFFIX).classify

  Motor::DatabaseClasses.const_get(class_name).connection_db_config.url
rescue NameError
  raise UnknownDatabase, "#{class_name} database is not defined"
end

.find_connection_in_pool(database_name) ⇒ Object 



276
277
278
279
280
 
# File 'lib/motor/queries/run_query.rb', line 276

def find_connection_in_pool(database_name)
  ActiveRecord::Base.connection_pool.connections.find do |conn|
    conn.pool.db_config.name == database_name
  end
end

.merge_variable_default_values(variable_configs, variables_hash) ⇒ Hash

Parameters:

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

Returns:

  • (Hash) 


239
240
241
242
243
244
245
 
# File 'lib/motor/queries/run_query.rb', line 239

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 



232
233
234
 
# File 'lib/motor/queries/run_query.rb', line 232

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

.normalize_statement_for_sql(conn, statement) ⇒ Array

Parameters:

  • array (Array)

Returns:

  • (Array) 


222
223
224
225
226
227
228
229
230
 
# File 'lib/motor/queries/run_query.rb', line 222

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

  params = [sql.gsub(STATEMENT_VARIABLE_REGEXP, '?'), attributes.map(&:value)]
  params.unshift(conn) if Rails.version.to_f >= 7.2
  sql = ActiveRecord::Base.send(:replace_bind_variables, *params)

  [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) 


140
141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/motor/queries/run_query.rb', line 140

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

  validate_query!(query.sql_body)

  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

.validate_query!(sql) ⇒ Object

Raises:



79
80
81
82
83
 
# File 'lib/motor/queries/run_query.rb', line 79

def validate_query!(sql)
  return if sql.scan(DB_LINK_VALIDATE_REGEXP).flatten.all? { |line| line.ends_with?('dblink(') }

  raise UnsafeDatabaseUrlUsage, 'Database URL variable is allowed only with dblink'
end