w9pdPut9

1module SAAL
2  class DBStore
3    include Enumerable
4    def initialize(conffile=SAAL::DBCONF)
5      @dbopts = YAML::load(File.new(conffile))
6      @db = nil
7      db_initialize
8    end
9
10    def db_initialize
11      db_query "CREATE TABLE IF NOT EXISTS sensor_reads
12                   (sensor VARCHAR(100), 
13                    date INT, 
14                    value FLOAT,
15                    INDEX USING HASH (sensor),
16                    INDEX USING BTREE (date))"
17    end
18    
19    def db_wipe
20      db_query "DROP TABLE sensor_reads"
21    end
22
23    def write(sensor, date, value)
24      raise ArgumentError, "Trying to store an empty sensor read" if !value
25      raise ArgumentError, "Trying to store an empty timestamp" if !date
26      raise ArgumentError, "Trying to store a timestamp <= 0" if date <= 0
27      db_query "INSERT INTO sensor_reads VALUES
28                  ('"+db_quote(sensor.to_s)+"',"+date.to_s+","+value.to_s+")"
29    end
30    
31    def average(sensor, from, to)     
32      db_query "SELECT AVG(value) AS average FROM sensor_reads
33                       WHERE sensor = '#{db_quote(sensor.to_s)}' 
34                         AND date >= #{from.to_s} 
35                         AND date <= #{to.to_s}" do |r|
36        if r.num_rows == 0 
37          nil
38        else
39          row = r.fetch_row
40          row[0] ? row[0].to_f : nil
41        end
42      end
43    end
44
45    def each
46      db_query "SELECT sensor,date,value FROM sensor_reads" do |r|
47        r.num_rows.times do
48          row = r.fetch_row
49          yield [row[0],row[1].to_i, row[2].to_f]
50        end
51      end
52    end
53    
54    private
55    def db_quote(text)
56      Mysql.quote(text)
57    end
58
59    def db_query(query)
60      db = nil
61      begin
62        # connect to the MySQL server
63        db = Mysql.new(@dbopts['host'],@dbopts['user'],@dbopts['pass'],
64                       @dbopts['db'],@dbopts['port'],@dbopts['socket'],
65                       @dbopts['flags'])
66        res = db.query(query)
67        yield res if block_given?
68      rescue Mysql::Error => e
69        $stderr.puts "MySQL Error \#{e.errno}: \#{e.error}"
70      ensure
71        db.close if db
72      end
73    end      
74  end
75end