Module: Motor::Queries::RunQuery

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

Defined Under Namespace

Classes: QueryResult

Constant Summary collapse

DEFAULT_LIMIT = 
100_000
CTE_NAME = 
'__query__'
WITH_STATEMENT_START = 
"WITH #{CTE_NAME} AS ("
WITH_STATEMENT_TEMPLATE = 
<<~SQL
  #{WITH_STATEMENT_START}%<sql_body>s
  )
SQL
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) 


85
86
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
 
# File 'lib/motor/queries/run_query.rb', line 85

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_cte_select_sql(connection, limit, filters) ⇒ String

Parameters:

  • limit (Number)
  • filters (Hash)

Returns:

  • (String) 


153
154
155
156
157
158
159
160
161
162
163
164
 
# File 'lib/motor/queries/run_query.rb', line 153

def build_cte_select_sql(connection, limit, filters)
  table = Arel::Table.new(CTE_NAME)

  arel_filters = build_filters_arel(filters)

  expresion = table.project(table[Arel.star])
  expresion = expresion.where(arel_filters) if arel_filters.present?

  expresion.take(limit.to_i) unless connection.class.name.include?('SQLServerAdapter')

  expresion.to_sql.delete('"')
end

.build_error_message(exception) ⇒ String

Parameters:

  • exception (ActiveRecord::StatementInvalid)

Returns:

  • (String) 


53
54
55
 
# File 'lib/motor/queries/run_query.rb', line 53

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

.build_filters_arel(filters) ⇒ Arel::Nodes?

Parameters:

  • filters (Hash)

Returns:

  • (Arel::Nodes, nil) 


168
169
170
171
172
173
174
175
176
 
# File 'lib/motor/queries/run_query.rb', line 168

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

  table = Arel::Table.new(CTE_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_statement_attributes(variables) ⇒ Array<ActiveRecord::Relation::QueryAttribute>

Parameters:

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

Returns:

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


180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/motor/queries/run_query.rb', line 180

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:



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

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:



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

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 



213
214
215
 
# File 'lib/motor/queries/run_query.rb', line 213

def connection_class
  defined?(ResourceRecord) ? ResourceRecord : ActiveRecord::Base
end

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

Parameters:

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

Returns:

  • (ActiveRecord::Result) 


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

def execute_query(query, limit, variables_hash, filters)
  result = nil
  statement = prepare_sql_statement(connection_class.connection, 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) 


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

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) 


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

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_statement_for_sql(statement) ⇒ Array

Parameters:

  • array (Array)

Returns:

  • (Array) 


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

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

  sql = ActiveRecord::Base.sanitize_sql([sql.gsub(STATEMENT_VARIABLE_REGEXP, '?'), attributes.map(&:value)])

  [sql, 'SQL', attributes]
end

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

Parameters:

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

Returns:

  • (Array) 


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

def prepare_sql_statement(connection, 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)
  cte_sql = format(WITH_STATEMENT_TEMPLATE, sql_body: sql.strip.delete_suffix(';'))
  cte_select_sql = build_cte_select_sql(connection, limit, filters)

  attributes = build_statement_attributes(query_variables)

  [[cte_sql, cte_select_sql].join, 'SQL', attributes]
end