First of all i have to say at the beginning of my research, i had no idea of mysql query and ruby. So please be forgiving with my maybe bad ideas.
My goal is to feed informations from mysql database into the graph widget of Dashing.
Dashing uses ājobsā to pull information. Like the openhab.rb from smar.
So i tried to find a way to use a job to pull the data from my sql db to the widget. Dashing has in its faqs a code snippet, how you can connect to a mysql database.
require 'mysql2'
SCHEDULER.every '15m', :first_in => 0 do |job|
# Myql connection
db = Mysql2::Client.new(:host => "192.168.1.1", :username => "dashing", :password => "SECRET", :port => 3306, :database => "users" )
# Mysql query
sql = "SELECT acct AS account, COUNT( acct ) AS count FROM users ORDER BY COUNT(*) DESC LIMIT 0 , 5"
# Execute the query
results = db.query(sql)
# Sending to List widget, so map to :label and :value
acctitems = results.map do |row|
row = {
:label => row['account'],
:value => row['count']
}
end
# Update the List widget
send_event('account_count', { items: acctitems } )
end
This is basically what i want. It pulls information via mysql2 and transform it so the list widget can use it.
Jason, the autor of Rickshaw writes, that rickshaw can display more than one graph.
series = [
{
name: "Temp_Wohnzimmer",
data: [{x:1, y: 4}, {x:2, y:27}, {x:3, y:6}]
},
{
name: "Heating_Wohnzimmer",
data: [{x:1, y: 5}, {x:2, y:2}, {x:3, y:9}]
}
]
send_event('Temp_Wohnzimmer', series: series)
It has to be possible to pull the information from the db and transform it into this form. My first try looks like this:
require 'mysql2'
SCHEDULER.every '15m', :first_in => 0 do |job|
# Myql connection
db = Mysql2::Client.new(:host => "127.0.0.1", :username => "openhab", :password => "*****", :port => 3306, :database => "openhab_temp" )
client.query("SELECT Time, Value FROM Item3, LIMIT 30").each do |row|
puts row[0] : row[1];
end
print("points=[");
i = 0;
while (i < 30)
{
if (i > 0) print(", ")
print("{x:" + x + ", y:" + y + "}");
i++;
}
print("]");
send_event('convergence', points: points)
end
Maybe somebody with ruby knowledge can help me with this problem.