Class: QueryReviewer::SqlQuery

Inherits:
Object
  • Object
show all
Defined in:
lib/query_reviewer/sql_query.rb

Overview

a single SQL SELECT query

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql, rows, full_trace, duration = 0.0, profile = nil, command = "SELECT", affected_rows = 1, sanitized_sql = nil) ⇒ SqlQuery

Returns a new instance of SqlQuery.



11
12
13
14
15
16
17
18
19
20
21
22
23
 
# 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

Instance Attribute Details

#affected_rows ⇒ Object (readonly)

Returns the value of attribute affected_rows.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def affected_rows
  @affected_rows
end

#command ⇒ Object (readonly)

Returns the value of attribute command.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def command
  @command
end

#durations ⇒ Object (readonly)

Returns the value of attribute durations.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def durations
  @durations
end

#id ⇒ Object (readonly)

Returns the value of attribute id.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def id
  @id
end

#profiles ⇒ Object (readonly)

Returns the value of attribute profiles.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def profiles
  @profiles
end

#rows ⇒ Object (readonly)

Returns the value of attribute rows.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def rows
  @rows
end

#sanitized_sql ⇒ Object (readonly)

Returns the value of attribute sanitized_sql.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def sanitized_sql
  @sanitized_sql
end

#sqls ⇒ Object (readonly)

Returns the value of attribute sqls.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def sqls
  @sqls
end

#subqueries ⇒ Object (readonly)

Returns the value of attribute subqueries.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def subqueries
  @subqueries
end

#trace ⇒ Object (readonly)

Returns the value of attribute trace.



6
7
8
 
# File 'lib/query_reviewer/sql_query.rb', line 6

def trace
  @trace
end

Class Method Details

.generate_full_trace(trace = Kernel.caller) ⇒ Object 



113
114
115
 
# 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

.sanitize_strings_and_numbers_from_sql(sql) ⇒ Object 



117
118
119
120
121
122
123
124
125
126
127
128
129
 
# 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

Instance Method Details

#add(sql, duration, profile) ⇒ Object 



25
26
27
28
29
 
# File 'lib/query_reviewer/sql_query.rb', line 25

def add(sql, duration, profile)
  sqls << sql
  durations << duration
  profiles << profile
end

#analyze! ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# 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 ⇒ Object 



35
36
37
 
# File 'lib/query_reviewer/sql_query.rb', line 35

def count
  durations.size
end

#duration ⇒ Object 



43
44
45
 
# File 'lib/query_reviewer/sql_query.rb', line 43

def duration
  durations.sum
end

#duration_stats ⇒ Object 



47
48
49
 
# 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 ⇒ Object 



99
100
101
 
# File 'lib/query_reviewer/sql_query.rb', line 99

def full_trace
  self.class.generate_full_trace(trace)
end

#has_warnings? ⇒ Boolean

Returns:

  • (Boolean) 


59
60
61
 
# File 'lib/query_reviewer/sql_query.rb', line 59

def has_warnings?
  !self.warnings.empty?
end

#max_severity ⇒ Object 



63
64
65
 
# File 'lib/query_reviewer/sql_query.rb', line 63

def max_severity
  self.warnings.empty? ? 0 : self.warnings.collect(&:severity).max
end

#profile ⇒ Object 



39
40
41
 
# File 'lib/query_reviewer/sql_query.rb', line 39

def profile
  profiles.first
end

#relevant_trace ⇒ Object 



92
93
94
95
96
97
 
# 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? ⇒ Boolean

Returns:

  • (Boolean) 


109
110
111
 
# File 'lib/query_reviewer/sql_query.rb', line 109

def select?
  self.command == "SELECT"
end

#sql ⇒ Object 



31
32
33
 
# File 'lib/query_reviewer/sql_query.rb', line 31

def sql
  sqls.first
end

#table ⇒ Object 



67
68
69
 
# File 'lib/query_reviewer/sql_query.rb', line 67

def table
  @subqueries.first.try(:table)
end

#to_hash ⇒ Object 



88
89
90
 
# File 'lib/query_reviewer/sql_query.rb', line 88

def to_hash
  @sql.hash
end

#to_table ⇒ Object 



51
52
53
 
# File 'lib/query_reviewer/sql_query.rb', line 51

def to_table
  rows.qa_columnized
end

#warn(options) ⇒ Object 



103
104
105
106
107
 
# 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 ⇒ Object 



55
56
57
 
# File 'lib/query_reviewer/sql_query.rb', line 55

def warnings
  self.subqueries.collect(&:warnings).flatten + @warnings
end