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