Compter le nombre de requêtes effectuées

Question

J'aimerais tester qu'un certain morceau de code exécute le moins de requêtes SQL possible.

ActiveRecord::TestCase semble avoir sa propre assert_queries qui fera exactement cela. Mais comme je ne suis pas Parcheando ActiveRecord, cela ne me sert pas à grand chose.

RSpec ou ActiveRecord fournissent-ils un moyen officiel et public de compter le nombre de requêtes SQL exécutées dans un bloc de code ?

Answer 1

Voici une version qui permet de compter facilement les requêtes correspondant à un modèle donné.

module QueryCounter

  def self.count_selects(&block)
    count(pattern: /^(\s+)?SELECT/, &block)
  end

  def self.count(pattern: /(.*?)/, &block)
    counter = 0

    callback = ->(name, started, finished, callback_id, payload) {
      counter += 1 if payload[:sql].match(pattern)
      # puts "match? #{!!payload[:sql].match(pattern)}: #{payload[:sql]}"
    }

    # http://api.rubyonrails.org/classes/ActiveSupport/Notifications.html
    ActiveSupport::Notifications.subscribed(callback, "sql.active_record", &block)

    counter
  end

end

Utilisation :

test "something" do
  query_count = count_selects {
    Thing.first
    Thing.create!(size: "huge")
  }
  assert_equal 1, query_count
end

Answer 2

J'ai fini par créer une petite gemme pour abstraire ce problème : sql_spy .

Il suffit de l'ajouter à votre Gemfile :

gem "sql_spy"

Enveloppez votre code dans SqlSpy.track { ... } :

queries = SqlSpy.track do
  # Some code that triggers ActiveRecord queries
  users = User.all
  posts = BlogPost.all
end

...et utilisez la valeur de retour du bloc dans vos assertions :

expect(queries.size).to eq(2)
expect(queries[0].sql).to eq("SELECT * FROM users;")
expect(queries[0].model_name).to eq("User")
expect(queries[0].select?).to be_true
expect(queries[0].duration).to eq(1.5)

Answer 3

J'ai ajouté la possibilité de vérifier les requêtes par table en me basant sur la solution de Yuriy.

# spec/support/query_counter.rb
require 'support/matchers/query_limit'

module ActiveRecord
  class QueryCounter
    attr_reader :queries

    def initialize
      @queries = Hash.new 0
    end

    def to_proc
      lambda(&method(:callback))
    end

    def callback(name, start, finish, message_id, values)
      sql = values[:sql]

      if sql.include? 'SAVEPOINT'
        table = :savepoints
      else
        finder = /select.+"(.+)"\..+from/i if sql.include? 'SELECT'
        finder = /insert.+"(.+)".\(/i if sql.include? 'INSERT'
        finder = /update.+"(.+)".+set/i if sql.include? 'UPDATE'
        finder = /delete.+"(.+)" where/i if sql.include? 'DELETE'
        table = sql.match(finder)&.send(:[],1)&.to_sym
      end

      @queries[table] += 1 unless %w(CACHE SCHEMA).include?(values[:name])

      return @queries
    end

    def query_count(table = nil)
      if table
        @queries[table]
      else
        @queries.values.sum
      end
    end
  end
end

Les matchers RSpec ressemblent à

# spec/support/matchers/query_limit.rb
RSpec::Matchers.define :exceed_query_limit do |expected, table|
  supports_block_expectations

  match do |block|
    query_count(table, &block) > expected
  end

  def query_count(table, &block)
    @counter = ActiveRecord::QueryCounter.new
    ActiveSupport::Notifications.subscribed(@counter.to_proc, 'sql.active_record', &block)
    @counter.query_count table
  end

  failure_message_when_negated do |actual|
    queries = 'query'.pluralize expected
    table_name = table.to_s.singularize.humanize.downcase if table

    out = "expected to run a maximum of #{expected}"
    out += " #{table_name}" if table
    out += " #{queries}, but got #{@counter.query_count table}"
  end
end

RSpec::Matchers.define :meet_query_limit do |expected, table|
  supports_block_expectations

  match do |block|
    if expected.is_a? Hash
      results = queries_count(table, &block)
      expected.all? { |table, count| results[table] == count }
    else
      query_count(&block) == expected
    end
  end

  def queries_count(table, &block)
    @counter = ActiveRecord::QueryCounter.new
    ActiveSupport::Notifications.subscribed(@counter.to_proc, 'sql.active_record', &block)
    @counter.queries
  end

  def query_count(&block)
    @counter = ActiveRecord::QueryCounter.new
    ActiveSupport::Notifications.subscribed(@counter.to_proc, 'sql.active_record', &block)
    @counter.query_count
  end

  def message(expected, table, negated = false)
    queries = 'query'.pluralize expected
    if expected.is_a? Hash
      results = @counter.queries
      table, expected = expected.find { |table, count| results[table] != count }
    end

    table_name = table.to_s.singularize.humanize.downcase if table

    out = 'expected to'
    out += ' not' if negated
    out += " run exactly #{expected}"
    out += " #{table_name}" if table
    out += " #{queries}, but got #{@counter.query_count table}"
  end

  failure_message do |actual|
    message expected, table
  end

  failure_message_when_negated do |actual|
    message expected, table, true
  end
end

Utilisation

expect { MyModel.do_the_queries }.to_not meet_query_limit(3)
expect { MyModel.do_the_queries }.to meet_query_limit(3)
expect { MyModel.do_the_queries }.to meet_query_limit(my_models: 2, other_tables: 1)