Class: Appstats::Query

Inherits:
Object
  • Object
show all
Defined in:
lib/appstats/query.rb

Constant Summary collapse

@@parser_template = 
Appstats::Parser.new(:rules => ":operation :action :date on :host where :contexts group by :group_by")
@@contexts_parser_template = 
Appstats::Parser.new(:rules => ":context", :repeating => true, :tokenize => "( ) and or || && = <= >= <> < > != like 'not like' in 'not in'")
@@group_by_parser_template = 
Appstats::Parser.new(:rules => ":filter", :repeating => true, :tokenize => ",")
@@nill_query = 
"select 0 from appstats_entries LIMIT 1"
@@default = 
"1=1"

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(data = {}) ⇒ Query

Returns a new instance of Query.



13
14
15
16
17
18
 
# File 'lib/appstats/query.rb', line 13

def initialize(data = {})
  @name = data[:name]
  @query_type = data[:query_type]
  @result_type = data[:result_type] || "on_demand"
  self.query=(data[:query])
end

Instance Attribute Details

#action ⇒ Object

Returns the value of attribute action.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def action
  @action
end

#contexts ⇒ Object

Returns the value of attribute contexts.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def contexts
  @contexts
end

#date_range ⇒ Object

Returns the value of attribute date_range.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def date_range
  @date_range
end

#group_by ⇒ Object

Returns the value of attribute group_by.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def group_by
  @group_by
end

#group_query_to_sql ⇒ Object

Returns the value of attribute group_query_to_sql.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def group_query_to_sql
  @group_query_to_sql
end

#host ⇒ Object

Returns the value of attribute host.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def host
  @host
end

#name ⇒ Object

Returns the value of attribute name.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def name
  @name
end

#parsed_contexts ⇒ Object

Returns the value of attribute parsed_contexts.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def parsed_contexts
  @parsed_contexts
end

#query ⇒ Object

Returns the value of attribute query.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def query
  @query
end

#query_to_sql ⇒ Object

Returns the value of attribute query_to_sql.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def query_to_sql
  @query_to_sql
end

#query_type ⇒ Object

Returns the value of attribute query_type.



11
12
13
 
# File 'lib/appstats/query.rb', line 11

def query_type
  @query_type
end

Class Method Details

.comparator?(raw_input) ⇒ Boolean

Returns:

  • (Boolean) 


195
196
197
198
 
# File 'lib/appstats/query.rb', line 195

def self.comparator?(raw_input)
  return false if raw_input.nil?
  comparators.include?(raw_input)
end

.comparators ⇒ Object 



200
201
202
 
# File 'lib/appstats/query.rb', line 200

def self.comparators
  ["=","!=","<>",">","<",">=","<=","like","not like","in","not in"]
end

.host_filter_to_sql(raw_input) ⇒ Object 



124
125
126
127
128
129
130
131
132
 
# File 'lib/appstats/query.rb', line 124

def self.host_filter_to_sql(raw_input)
  return @@default if raw_input.nil?
  input = raw_input.strip
  m = raw_input.strip.match(/(^[^\s']*$)/)
  return @@default if m.nil?
  host = m[1]
  return @@default if host == '' or host.nil?
  "EXISTS (select * from appstats_log_collectors where appstats_entries.appstats_log_collector_id = appstats_log_collectors.id and host = '#{host}' )"
end

.sqlclean(raw_input) ⇒ Object 



187
188
189
190
191
192
193
 
# File 'lib/appstats/query.rb', line 187

def self.sqlclean(raw_input)
  return raw_input if raw_input.blank?
  m = raw_input.match(/^['"](.*)['"]$/)
  input = m.nil? ? raw_input : m[1]
  input = input.gsub(/\\/, '\&\&').gsub(/'/, "''")
  input
end

.sqlize(input) ⇒ Object 



171
172
173
174
175
176
 
# File 'lib/appstats/query.rb', line 171

def self.sqlize(input)
  return "and" if input == "&&"
  return "or" if input == "||"
  return "<>" if input == "!="
  input
end

.sqlquote(raw_input, comparator = '=') ⇒ Object 



178
179
180
181
182
183
184
185
 
# File 'lib/appstats/query.rb', line 178

def self.sqlquote(raw_input,comparator = '=')
  return "NULL" if raw_input.nil?
  if ["in","not in"].include?(comparator)
    return "(" + raw_input.split(",").collect { |x| sqlquote(x) }.join(",") + ")"
  else
    return "'#{sqlclean(raw_input)}'"  
  end
end

Instance Method Details

#contexts_filter_to_sql ⇒ Object 



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
 
# File 'lib/appstats/query.rb', line 134

def contexts_filter_to_sql
  context_parser = @@contexts_parser_template.dup
  return @@default if @contexts.blank? || !context_parser.parse(@contexts)
  sql = "EXISTS (select * from appstats_contexts where appstats_entries.id = appstats_contexts.appstats_entry_id and ("
  
  status = :next
  comparator = "="
  context_parser.raw_results.each do |entry|
    if entry.kind_of?(String)
      sqlentry = Query.sqlize(entry)
      if Query.comparator?(entry) && status == :waiting_comparator
        comparator = Query.sqlize(entry)
        status = :waiting_operand
      else
        sql += ")" if status == :waiting_comparator
        sql += " #{sqlentry}"
        @parsed_contexts<< sqlentry
        status = :next
      end
      next
    end
    if status == :next
      status = :waiting_comparator
      @parsed_contexts<< { :context_key => entry[:context] }
      sql += " (context_key = #{Query.sqlquote(entry[:context])}"
    else
      status = :next
      @parsed_contexts.last[:context_value] = entry[:context]
      @parsed_contexts.last[:comparator] = comparator
      sql += " and context_value #{comparator} #{Query.sqlquote(entry[:context],comparator)})"
    end
  end
  sql += ")" if status == :waiting_comparator
  sql += "))"
  sql
end

#find(job_frequency_if_not_available = 'once') ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'lib/appstats/query.rb', line 38

def find(job_frequency_if_not_available = 'once')
  result = Appstats.rails3? ? Appstats::Result.where("query = ? and is_latest = 1",@query).first : Appstats::Result.find(:first,:conditions => [ "query = ? and is_latest = 1",@query ]) 
  if result.nil?
    if job_frequency_if_not_available.nil?
      result = run
    else
      job_frequency_if_not_available = "once" if job_frequency_if_not_available == true
      existing = Appstats.rails3? ? Appstats::ResultJob.where("query = ? and (last_run_at is null or frequency <> 'once')",@query).first : Appstats::ResultJob.find(:first,:conditions => [ "query = ? and (last_run_at is null or frequency <> 'once')",@query ]) 
      if existing.nil?
        Appstats::ResultJob.create(:name => "Missing Query#find requested", :frequency => job_frequency_if_not_available, :query => @query, :query_type => @query_type)  
      end
    end
  end
  result
end

#run ⇒ Object 



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/appstats/query.rb', line 54

def run
  result = Appstats::Result.new(:name => @name, :result_type => @result_type, :query => @query, :query_to_sql => @query_to_sql, :action => @action, :host => @host, :from_date => @date_range.from_date, :to_date => @date_range.to_date, :contexts => @contexts, :query_type => @query_type)
  unless @group_by.empty?
    result.group_by = @group_by.join(", ")
    result.group_query_to_sql = @group_query_to_sql
  end

  result.group_by = @group_by.join(", ") unless @group_by.empty?
  
  data = run_query { |conn| conn.select_one(@query_to_sql)["num"].to_i }
  unless data.nil?
    result.count = data[:results]
    result.query_duration_in_seconds = data[:duration]
    result.db_username = data[:db_config][:username]
    result.db_name = data[:db_config][:database]
    result.db_host = data[:db_config][:host]
  end
  result.save

  if !@group_by.empty? && !result.count.nil?
    running_total = 0
    data = run_query { |conn| conn.select_all(@group_query_to_sql) }
    result.group_query_duration_in_seconds = data[:duration] unless data.nil?
    all_sub_results = data.nil? ? [] : data[:results]
    all_sub_results.each do |data|
      if data["context_key_filter"].nil? || data["num"].nil?
        Appstats.log(:error,"Missing context_key_filter, or num in #{data.inspect}")
        next 
      end

      if data["context_value_filter"].nil?
        Appstats.log(:error,"Missing context_value_filter, setting to empty string ''")
        data["context_value_filter"] = ""
      end
      
      keys = data["context_key_filter"].split(",")
      values = data["context_value_filter"].split(",")
      key_values = {} and keys.each_with_index { |k,i| key_values[k] = values[i] }
      current_count = data["num"].to_i
      ratio_of_total = current_count.to_f / result.count
      running_total += current_count
      sub_result = Appstats::SubResult.new(:context_filter => @group_by.collect { |k| key_values[k] }.join(", "), :count => current_count, :ratio_of_total => ratio_of_total)
      sub_result.result = result
      sub_result.save
    end
    
    if running_total < result.count
      remaining_total = result.count - running_total
      ratio_of_total = remaining_total.to_f / result.count
      sub_result = Appstats::SubResult.new(:context_filter => nil, :count => remaining_total, :ratio_of_total => ratio_of_total)
      sub_result.result = result
      sub_result.save
    end

    result.save
  end
  
  if @operation == "#!"
    if Appstats.rails3?
      Result.where("(query = ? or query = ?) and id <> ?",@query,@query.sub("#!","#"),result.id).delete_all
    else
      Result.delete_all(["(query = ? or query = ?) and id <> ?",@query,@query.sub("#!","#"),result.id])
    end
    result.save
  end
  
  result.reload
  result
end