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

def build_columns_hash(result)
  result.columns.map do |column_name|
    column_type = result.column_types[column_name]

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

.build_cte_select_sql(limit, filters) ⇒ String

Parameters:

  • limit (Number)
  • filters (Hash)

Returns:

  • (String) 


118
119
120
121
122
123
124
125
126
127
 
# File 'lib/motor/queries/run_query.rb', line 118

def build_cte_select_sql(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).to_sql
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) 


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

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


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

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

.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(query, limit, variables_hash, filters)

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

        ActiveRecord::Base.connection.exec_query(*statement)
      end

    raise ActiveRecord::Rollback
  end

  result
end

.merge_variable_default_values(variable_configs, variables_hash) ⇒ Hash

Parameters:

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

Returns:

  • (Hash) 


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

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) 


157
158
159
160
161
162
163
 
# File 'lib/motor/queries/run_query.rb', line 157

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(query, limit, variables_hash, filters) ⇒ Array

Parameters:

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

Returns:

  • (Array) 


103
104
105
106
107
108
109
110
111
112
113
 
# File 'lib/motor/queries/run_query.rb', line 103

def prepare_sql_statement(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(limit, filters)

  attributes = build_statement_attributes(query_variables)

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