class QueryReviewer::SqlQuery

a single SQL SELECT query

Attributes

affected_rows[R]
command[R]
durations[R]
id[R]
profiles[R]
rows[R]
sanitized_sql[R]
sqls[R]
subqueries[R]
trace[R]

Public Class Methods

generate_full_trace(trace = Kernel.caller) click to toggle source
# File lib/query_reviewer/sql_query.rb, line 113
def self.generate_full_trace(trace = Kernel.caller)
  trace.collect(&:strip).select{|t| !t.starts_with?("#{Rails.root}/vendor/plugins/query_reviewer") }
end
new(sql, rows, full_trace, duration = 0.0, profile = nil, command = "SELECT", affected_rows = 1, sanitized_sql = nil) click to toggle source
# File lib/query_reviewer/sql_query.rb, line 11
def initialize(sql, rows, full_trace, duration = 0.0, profile = nil, command = "SELECT", affected_rows = 1, sanitized_sql = nil)
  @trace = full_trace
  @rows = rows
  @sqls = [sql]
  @sanitized_sql = sanitized_sql
  @subqueries = rows ? rows.collect{|row| SqlSubQuery.new(self, row)} : []
  @id = (self.class.next_id += 1)
  @profiles = profile ? [profile.collect { |p| OpenStruct.new(p) }] : [nil]
  @durations = [duration.to_f]
  @warnings = []
  @command = command
  @affected_rows = affected_rows
end
sanitize_strings_and_numbers_from_sql(sql) click to toggle source
# File lib/query_reviewer/sql_query.rb, line 117
def self.sanitize_strings_and_numbers_from_sql(sql)
  new_sql = sql.clone
  new_sql = new_sql.to_sql if new_sql.respond_to?(:to_sql)
  new_sql.gsub!(/\b\d+\b/, "N")
  new_sql.gsub!(/\b0x[0-9A-Fa-f]+\b/, "N")
  new_sql.gsub!(/''/, "'S'")
  new_sql.gsub!(/""/, "\"S\"")
  new_sql.gsub!(/\\'/, "")
  new_sql.gsub!(/\\"/, "")
  new_sql.gsub!(/'[^']+'/, "'S'")
  new_sql.gsub!(/"[^"]+"/, "\"S\"")
  new_sql
end

Public Instance Methods

add(sql, duration, profile) click to toggle source
# File lib/query_reviewer/sql_query.rb, line 25
def add(sql, duration, profile)
  sqls << sql
  durations << duration
  profiles << profile
end
analyze!() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 71
def analyze!
  self.subqueries.collect(&:analyze!)
  if duration
    if duration >= QueryReviewer::CONFIGURATION["critical_duration_threshold"]
      warn(:problem => "Query took #{duration} seconds", :severity => 9)
    elsif duration >= QueryReviewer::CONFIGURATION["warn_duration_threshold"]
      warn(:problem => "Query took #{duration} seconds", :severity => QueryReviewer::CONFIGURATION["critical_severity"])
    end
  end

  if affected_rows >= QueryReviewer::CONFIGURATION["critical_affected_rows"]
    warn(:problem => "#{affected_rows} rows affected", :severity => 9, :description => "An UPDATE or DELETE query can be slow and lock tables if it affects many rows.")
  elsif affected_rows >= QueryReviewer::CONFIGURATION["warn_affected_rows"]
    warn(:problem => "#{affected_rows} rows affected", :severity => QueryReviewer::CONFIGURATION["critical_severity"], :description => "An UPDATE or DELETE query can be slow and lock tables if it affects many rows.")
  end
end
count() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 35
def count
  durations.size
end
duration() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 43
def duration
  durations.sum
end
duration_stats() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 47
def duration_stats
  "TOTAL:#{'%.3f' % duration}  AVG:#{'%.3f' % (durations.sum / durations.size)}  MAX:#{'%.3f' % (durations.max)}  MIN:#{'%.3f' % (durations.min)}"
end
full_trace() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 99
def full_trace
  self.class.generate_full_trace(trace)
end
has_warnings?() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 59
def has_warnings?
  !self.warnings.empty?
end
max_severity() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 63
def max_severity
  self.warnings.empty? ? 0 : self.warnings.collect(&:severity).max
end
profile() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 39
def profile
  profiles.first
end
relevant_trace() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 92
def relevant_trace
  trace.collect(&:strip).select{|t| t.starts_with?(Rails.root.to_s) &&
      (!t.starts_with?("#{Rails.root}/vendor") || QueryReviewer::CONFIGURATION["trace_includes_vendor"]) &&
      (!t.starts_with?("#{Rails.root}/lib") || QueryReviewer::CONFIGURATION["trace_includes_lib"]) &&
      !t.starts_with?("#{Rails.root}/vendor/plugins/query_reviewer") }
end
select?() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 109
def select?
  self.command == "SELECT"
end
sql() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 31
def sql
  sqls.first
end
table() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 67
def table
  @subqueries.first.try(:table)
end
to_hash() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 88
def to_hash
  @sql.hash
end
to_table() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 51
def to_table
  rows.qa_columnized
end
warn(options) click to toggle source
# File lib/query_reviewer/sql_query.rb, line 103
def warn(options)
  options[:query] = self
  options[:table] ||= self.table
  @warnings << QueryWarning.new(options)
end
warnings() click to toggle source
# File lib/query_reviewer/sql_query.rb, line 55
def warnings
  self.subqueries.collect(&:warnings).flatten + @warnings
end