class SAAL::DBStore

Constants

MAX_LAST_VAL_AGE

Only give out last_value if it's less than 5 min old

Public Class Methods

new(conffile=SAAL::DBCONF) click to toggle source
   # File lib/dbstore.rb
 7 def initialize(conffile=SAAL::DBCONF)
 8   @dbopts = YAML::load(File.new(conffile))
 9   @db = nil
10   db_initialize
11 end

Public Instance Methods

average(sensor, from, to) click to toggle source
   # File lib/dbstore.rb
34 def average(sensor, from, to)     
35   db_range("AVG", sensor, from, to)
36 end
db_initialize() click to toggle source
   # File lib/dbstore.rb
13 def db_initialize
14   db_query "CREATE TABLE IF NOT EXISTS sensor_reads
15                (sensor VARCHAR(100), 
16                 date INT, 
17                 value FLOAT) ENGINE=InnoDB"
18   db_query "ALTER TABLE sensor_reads ADD INDEX sensor_date_value (sensor,date,value) USING BTREE",
19            :ignoreerr => 1061
20 end
db_wipe() click to toggle source
   # File lib/dbstore.rb
22 def db_wipe
23   db_query "DROP TABLE sensor_reads"
24 end
each() { |row,to_i, to_f| ... } click to toggle source
   # File lib/dbstore.rb
85 def each
86   db_query "SELECT sensor,date,value FROM sensor_reads" do |r|
87     r.each do |row|
88       yield [row["sensor"],row["date"].to_i, row["value"].to_f]
89     end
90   end
91 end
last_value(sensor) click to toggle source
   # File lib/dbstore.rb
70 def last_value(sensor)
71   db_query "SELECT date,value FROM sensor_reads 
72               WHERE sensor = '#{db_quote(sensor.to_s)}'
73               AND date > '#{Time.now.utc.to_i - MAX_LAST_VAL_AGE}'
74               ORDER BY date DESC LIMIT 1" do |r|
75     row = r.first
76     if row
77       _date, value = [row["date"].to_i, row["value"].to_f]
78       value
79     else
80       nil
81     end
82   end
83 end
maximum(sensor, from, to) click to toggle source
   # File lib/dbstore.rb
67 def maximum(sensor, from, to)     
68   db_range("MAX", sensor, from, to)
69 end
minimum(sensor, from, to) click to toggle source
   # File lib/dbstore.rb
64 def minimum(sensor, from, to)     
65   db_range("MIN", sensor, from, to)
66 end
weighted_average(sensor, from, to) click to toggle source
   # File lib/dbstore.rb
38 def weighted_average(sensor, from, to)
39   total_time = 0
40   total_value = 0.0
41   previous_value = nil
42   start_time = nil
43   initialized = false
44   db_query "SELECT date,value FROM sensor_reads
45                    WHERE sensor = '#{db_quote(sensor.to_s)}'
46                      AND date >= #{from.to_s}
47                      AND date <= #{to.to_s}" do |r|
48     r.each do |row|
49       date = row["date"].to_i
50       value = row["value"].to_f
51       if start_time
52         elapsed = date - start_time
53         total_value += elapsed * previous_value
54         total_time += elapsed
55         initialized = true
56       end
57       start_time = date
58       previous_value = value
59     end
60   end
61   initialized ? total_value / total_time : nil
62 end
write(sensor, date, value) click to toggle source
   # File lib/dbstore.rb
26 def write(sensor, date, value)
27   raise ArgumentError, "Trying to store an empty sensor read" if !value
28   raise ArgumentError, "Trying to store an empty timestamp" if !date
29   raise ArgumentError, "Trying to store a timestamp <= 0" if date <= 0
30   db_query "INSERT INTO sensor_reads VALUES
31               ('"+db_quote(sensor.to_s)+"',"+date.to_s+","+value.to_s+")"
32 end

Private Instance Methods

db_query(query, opts={}) { |res| ... } click to toggle source
    # File lib/dbstore.rb
112 def db_query(query, opts={})
113   db = nil
114   begin
115     # connect to the MySQL server
116     db = Mysql2::Client.new(@dbopts)
117     res = db.query(query)
118     yield res if block_given?
119   rescue Mysql2::Error => e
120     $stderr.puts "MySQL Error #{e.errno}: #{e.error}" if !(e.errno == opts[:ignoreerr])
121   ensure
122     db.close if db
123   end
124 end
db_quote(text) click to toggle source
    # File lib/dbstore.rb
108 def db_quote(text)
109   Mysql2::Client.escape(text)
110 end
db_range(function, sensor, from, to) click to toggle source
    # File lib/dbstore.rb
 94 def db_range(function, sensor, from, to)
 95   db_query "SELECT #{function}(value) AS func FROM sensor_reads
 96                    WHERE sensor = '#{db_quote(sensor.to_s)}' 
 97                      AND date >= #{from.to_s} 
 98                      AND date <= #{to.to_s}" do |r|
 99     row = r.first
100     if row && row["func"]
101       row["func"].to_f
102     else
103       nil
104     end
105   end
106 end