På begäran, här kommer info om mitt experiment.
OBS: designen är fortfarande endast grovt tillyxad - detta är mitt första encounter med PHP, Java, MySQL, etc.
Databasformat:
Kod: Markera allt
mysql> desc data;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| time | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sensorid | smallint(6) | NO | | NULL | |
| property | smallint(6) | NO | | NULL | |
| value | float | NO | | NULL | |
| aggregate | smallint(6) | NO | | NULL | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
Jag har även skapat lite hjälptabeller för att hålla reda på sensorer o properties
Kod: Markera allt
mysql> desc sensors;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sensorid | smallint(6) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| onewireid | varchar(15) | YES | | NULL | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)
mysql> desc properties;
+------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+-----------------------------+
| time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| propertyid | smallint(6) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| enhet | varchar(4) | YES | | NULL | |
| sensorid | varchar(15) | NO | | NULL | |
+------------+-------------+------+-----+-------------------+-----------------------------+
För tilfället går datainsamligen som ett cronjob som läser givarna och gör en "fulinsert" utan några krusiduller
Kod: Markera allt
#!/bin/bash
sens1temp=$(cat /mnt/1wire/7E.C42600001000/EDS0068/temperature)
sens1humi=$(cat /mnt/1wire/7E.C42600001000/EDS0068/humidity)
sens1lite=$(cat /mnt/1wire/7E.C42600001000/EDS0068/light)
sens1pres=$(cat /mnt/1wire/7E.C42600001000/EDS0068/pressure)
# Central heating, outgoing
sens2temp=$(cat /mnt/1wire/28.6E5495040000/temperature)
# District heating, incoming
sens3temp=$(cat /mnt/1wire/28.3083D6020000/temperature)
mysql --user=user --password=password << ENDSQL
use onewire
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 1, $sens1temp, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 2, $sens1humi, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 3, $sens1lite, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 1, 4, $sens1pres, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 2, 1, $sens2temp, 1 );
insert into data ( time, sensorid, property, value, aggregate ) values ( now(), 3, 1, $sens3temp, 1 );
ENDSQL
Varje hel timme o dygn kör jag stored procedures som skapar medelvärden per timme o dag samt sparar in min/max punkter. Tanken är att om datamängden skulle bli för stor kan jag droppa minutvärdena o ändå ha timmedel samt dygnsmedel, max o min kvar under en längre tid utan att fylla disken.
Här är skriptet som lägger in timproceduren i databasen.
Kod: Markera allt
USE onewire
DROP PROCEDURE IF EXISTS updatehourdata;
DELIMITER //
CREATE PROCEDURE updatehourdata()
BEGIN
DECLARE thissensor SMALLINT;
DECLARE thisproperty SMALLINT;
DECLARE thistime TIMESTAMP;
DECLARE thisvalue FLOAT;
DECLARE sensorcur CURSOR FOR select sensorid from sensors order by sensorid;
DECLARE propertycur CURSOR FOR select propertyid from properties where sensorid = thissensor order by propertyid;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE sensorcur;
OPEN sensorcur;
REPEAT
FETCH sensorcur INTO thissensor;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE propertycur;
OPEN propertycur;
REPEAT
FETCH propertycur INTO thisproperty;
-- Find most recent hourvalue
SELECT MAX(time) + interval 1 hour INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 2;
IF ISNULL(thistime) THEN
-- No previous hourvalue found -> fallback on oldest minutevalue
SELECT min(time) + interval 1 hour INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1;
END IF;
-- Check if still NULL -> there is no data available -> do not proceed with calculating any averages.
IF NOT ISNULL(thistime) THEN
-- Shave off any minutes or seconds
SET thistime = DATE_SUB(thistime, interval minute(thistime) minute);
SET thistime = DATE_SUB(thistime, interval second(thistime) second);
-- Thistime does now point to next hourly average to compute
WHILE thistime < NOW() DO
-- Calculate an hourly average for this sensor, property and time frame.
SELECT AVG(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND time >= thistime - interval 1 hour AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( thistime, thissensor, thisproperty, thisvalue, 2 );
-- Increment hour
SET thistime = DATE_ADD(thistime, interval 1 hour);
END WHILE;
END IF;
UNTIL false END REPEAT;
CLOSE propertycur;
END;
UNTIL false END REPEAT;
CLOSE sensorcur;
END //
Dygnsproceduren är ganska lik, men innehåller min/max loggningen
Kod: Markera allt
USE onewire
DROP PROCEDURE IF EXISTS updatedaydata;
DELIMITER //
CREATE PROCEDURE updatedaydata()
BEGIN
DECLARE thissensor SMALLINT;
DECLARE thisproperty SMALLINT;
DECLARE thistime TIMESTAMP;
DECLARE peaktime TIMESTAMP;
DECLARE thisvalue FLOAT;
DECLARE sensorcur CURSOR FOR select sensorid from sensors order by sensorid;
DECLARE propertycur CURSOR FOR select propertyid from properties where sensorid = thissensor order by propertyid;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE sensorcur;
OPEN sensorcur;
REPEAT
FETCH sensorcur INTO thissensor;
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE propertycur;
OPEN propertycur;
REPEAT
FETCH propertycur INTO thisproperty;
-- Find most recent dayvalue
SELECT MAX(time) + interval 1 day INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 3;
IF ISNULL(thistime) THEN
-- No previous dayvalue found -> fallback on oldest hourvalue
SELECT min(time) + interval 1 day INTO thistime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 2;
END IF;
-- Check if still NULL -> there is no data available -> do not proceed with calculating any averages.
IF NOT ISNULL(thistime) THEN
-- Shave off any hours, minutes and seconds
SET thistime = DATE_SUB(thistime, interval hour(thistime) hour);
SET thistime = DATE_SUB(thistime, interval minute(thistime) minute);
SET thistime = DATE_SUB(thistime, interval second(thistime) second);
-- Thistime does now contain next daily average to compute
WHILE thistime < NOW() DO
-- Calculate an daily average for this sensor, property and time frame.
SELECT AVG(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 2
AND time >= thistime - interval 1 day AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( thistime, thissensor, thisproperty, thisvalue, 3 );
-- Find day low value
SELECT MIN(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND time >= thistime - interval 1 day AND time < thistime;
-- Find first occurrence of day low value
SELECT MIN(time) INTO peaktime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND value = thisvalue AND time >= thistime - interval 1 day AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( peaktime, thissensor, thisproperty, thisvalue, 4 );
-- Find day high value
SELECT MAX(value) INTO thisvalue FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND time >= thistime - interval 1 day AND time < thistime;
-- Find first occurrence of day high value
SELECT MIN(time) INTO peaktime FROM data WHERE sensorid=thissensor AND property=thisproperty AND aggregate = 1
AND value = thisvalue AND time >= thistime - interval 1 day AND time < thistime;
-- Update data table
INSERT INTO data ( time, sensorid, property, value, aggregate ) VALUES ( peaktime, thissensor, thisproperty, thisvalue, 5 );
-- Increment one day
SET thistime = DATE_ADD(thistime, interval 1 day);
END WHILE;
END IF;
UNTIL false END REPEAT;
CLOSE propertycur;
END;
UNTIL false END REPEAT;
CLOSE sensorcur;
END //
Cronjobben:
Kod: Markera allt
* * * * * /usr/local/bin/loggatemp > /tmp/loggatemp.log 2>&1
0 * * * * /usr/bin/mysql --user=user --password=password -e'call updatehourdata;' onewire > /tmp/updatehourdata.log 2>&1
1 0 * * * /usr/bin/mysql --user=user --password=password -e'call updatedaydata;' onewire > /tmp/updatedaydata.log 2>&1
Här är HTML/PHP/Java koden som ritar flot diagrammet:
(för tillfället lever sidan i Ajax-exempelkatalogen som följer med flot distributionen, t.ex. har jag inte ändrat något i ../examples.css)
Kod: Markera allt
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Signaler från EDS0068</title>
<link href="../examples.css" rel="stylesheet" type="text/css">
<!--[if lte IE 8]><script language="javascript" type="text/javascript" src="../../excanvas.min.js"></script><![endif]-->
<script language="javascript" type="text/javascript" src="../../jquery.js"></script>
<script language="javascript" type="text/javascript" src="../../jquery.flot.js"></script>
<script language="javascript" type="text/javascript" src="../../jquery.flot.time.js"></script>
<script type="text/javascript">
$(function() {
function degreeFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" °C"; }
function percentFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" %"; }
function hPaFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" hPa"; }
function luxFormatter(v, axis) { return v.toFixed(axis.tickDecimals) +" lux"; }
var options = {
lines: { show: true },
xaxis: { mode: "time",
timeformat: "%a-%d %H:%M" },
yaxes: [ { tickFormatter: degreeFormatter },
{ tickFormatter: percentFormatter },
{ tickFormatter: hPaFormatter, position: "right" },
{ tickFormatter: luxFormatter, position: "right" } ] };
var data = [];
function onDataReceived(series) {
data.push(series);
$.plot("#placeholder", data, options);
}
$.ajax({url: "rumstemp.php", type: "GET", dataType: "json", success: onDataReceived });
$.ajax({url: "luftfuktighet.php", type: "GET", dataType: "json", success: onDataReceived });
$.ajax({url: "ljusstyrka.php", type: "GET", dataType: "json", success: onDataReceived });
$.ajax({url: "lufttryck.php", type: "GET", dataType: "json", success: onDataReceived });
});
</script>
</head>
<body>
<div class="demo-container">
<div id="placeholder" class="demo-placeholder"></div>
</div>
</body>
</html>
I samma katalog har jag grovt yxat till 4 ytterst rudimentära PDP skript som läser upp en kurva och JSON-kodar den o dumpar den rätt ut "i Ajax-etern". Observera att jag först o sist lägger ut etikett, färg o axelnummer med lite råa "echo" anrop. Detta kan säkerligen göras på ett bättre sätt - men som sagt, detta är ihophackat under några kvällar efter att ungarna o sambon gått o lagt sig o man får lite "kvalitetstid" för sig själv...
Här är koden till
rumstemp.php, dom andra tre är väldigt lika - i pricip skiljer sensorid o property samt label/axel/färg.
Kod: Markera allt
<?php
$db = mysql_connect("localhost", "user", "password");
mysql_select_db("onewire",$db);
$query = "SELECT UNIX_TIMESTAMP(time)*1000 \"time\", value from data where sensorid=1 and property=1 and aggregate=2 order by time";
$result = @mysql_query ($query);
$arr = array();
while($row = mysql_fetch_object($result))
{
$arr[] = array($row->time*1,$row->value*1);
}
echo "{\n\"label\": \"Inomhustemp (°C)\",";
echo "\n\"data\": ";
echo json_encode($arr);
echo ",\n\"color\": 0,\n";
echo "\"yaxis\": 1\n}\n";
mysql_close();
?>
Observera att aggregate 2 motsvarar timmedelvärde (dygnsmedel, min o max följer med 3, 4 o 5).
Initiala test indikerar att prestandan blir lidande med minutdata i en veckotrend - därför har jag valt timvärden i detta exempel.
Fick jag med allt?
