BAsic Remote Sensor su mysql
BARS trasmette i dati dal sensore remoto ad un Raspberry; il semplice ricevitore proposto scrive su standard output, redirezionabile su file. L'evoluzione naturale consiste nell'inserire i dati su database e mostrarli tramite web e web application.
Per essere inseriti nel db e gestiti da un'applicazione più complessa, e' opportuno normalizzare i dati alla sorgente.
Su Raspberry e' necessario installare il software per MySQL e creare il database:
# sudo apt-get install mysql-server
# sudo apt-get install mysql-client
# sudo apt-get install libmysqlclient-dev
# mysql -u root -p
mysql> create database sensors;
mysql> create user 'dbusername'@'%' IDENTIFIED BY 'password';
mysql> grant all privileges on sensors.* to 'dbusername'@'%';
mysql> use sensors;
mysql> create table rec_data (timestamp TIMESTAMP, data_type VARCHAR(4), serial VARCHAR(4), counter INT, data FLOAT, battery FLOAT);
mysql> describe rec_data;
+-----------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+-------------------+-----------------------------+
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| data_type | int(11) | YES | | NULL | |
| serial | varchar(5) | YES | | NULL | |
| counter | int(11) | YES | | NULL | |
| data | float | YES | | NULL | |
| battery | float | YES | | NULL | |
+-----------+------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.01 sec)
Il software in ascolto su Raspberry deve essere modificato per gestire i nuovi dati e la connessione al db.
creare il file nRF24l01_receive.cpp:
creare il file nRF24l01_receive.cpp:
// nRF24l01_receive.cpp
// nRF24L01+ su RaspberryPi
// code for data RX and store on mysql DB
// compile with: g++ -Ofast -mfpu=vfp -mfloat-abi=hard -march=armv6zk -mtune=arm1176jzf-s -Wall -I../ -lrf24-bcm nRF24l01_receive.cpp -o nRF24l01_receive `mysql_config --cflags` `mysql_config --libs`
// see http://slartitorto.blogspot.it/2015/02/basic-remote-sensor-su-mysql-bars.html for details and DB preparation
// connect:
// nrf24L01: 1 2 3 4 5 6 7
// RaspberryPi: 6 1 22 24 23 19 21
#include <cstdlib>
#include <iostream>
#include <RF24/RF24.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#define DATABASE_SERVER "localhost"
#define DATABASE_NAME "sensors"
#define DATABASE_USERNAME "dbusername"
#define DATABASE_PASSWORD "password"
MYSQL *mysql1;
using namespace std;
RF24 radio(RPI_V2_GPIO_P1_22, RPI_V2_GPIO_P1_24, BCM2835_SPI_SPEED_8MHZ);
void setup(void)
{
// init radio for reading
radio.begin();
radio.enableDynamicPayloads();
radio.setAutoAck(1);
radio.setRetries(15,15);
radio.setDataRate(RF24_250KBPS);
radio.setPALevel(RF24_PA_MAX);
radio.setChannel(76);
radio.setCRCLength(RF24_CRC_16);
radio.openReadingPipe(1,0xF0F0F0F0E1LL);
radio.startListening();
//initialize MYSQL object for connections
mysql1 = mysql_init(NULL);
if(mysql1 == NULL)
{
fprintf(stderr, "ABB : %s\n", mysql_error(mysql1));
return;
}
//Connect to the database
if(mysql_real_connect(mysql1, DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, 0) == NULL)
{
fprintf(stderr, "%s\n", mysql_error(mysql1));
}
else
{
printf("Database connection successful.\r\n");
}
}
void loop(void)
{
// 32 byte character array is max payload
char receivePayload[32]="";
// sleep 20 ms
usleep(20000);
while (radio.available())
{
// read from radio until payload size is reached
uint8_t len = radio.getDynamicPayloadSize();
radio.read(receivePayload, len);
cout << receivePayload << endl;
// conto i separatori ":"
const char * z = receivePayload;
int separator_count;
int m;
separator_count = 0;
for (m=0; z[m]; m++) {
if(z[m] == ':') {
separator_count ++;
}
}
// se sono 5, ok
if (separator_count == 5) {
int data_type = 0;
char * serial;
int counter = 0;
float data = 0;
float battery = 0;
char query[256];
data_type = atoi(strtok (receivePayload, ":"));
serial = strtok (NULL, ":");
counter = atoi(strtok (NULL, ":"));
data = atof(strtok (NULL, ":")) / 100;
battery = atof(strtok (NULL, ":")) / 1000;
printf("%04d %s %04d %.2f %.3f \n",data_type,serial,counter,data,battery);
sprintf(query, "INSERT INTO rec_data (data_type,serial,counter,data,battery) VALUES (%04d,'%s',%04d,%.2f,%.3f)", data_type, serial, counter, data, battery);
// printf("%s \n",query);
mysql_query(mysql1,query);
}
}
}
int main(int argc, char** argv)
{
setup();
while(1)
loop();
return 0;
}
// nRF24L01+ su RaspberryPi
// code for data RX and store on mysql DB
// compile with: g++ -Ofast -mfpu=vfp -mfloat-abi=hard -march=armv6zk -mtune=arm1176jzf-s -Wall -I../ -lrf24-bcm nRF24l01_receive.cpp -o nRF24l01_receive `mysql_config --cflags` `mysql_config --libs`
// see http://slartitorto.blogspot.it/2015/02/basic-remote-sensor-su-mysql-bars.html for details and DB preparation
// connect:
// nrf24L01: 1 2 3 4 5 6 7
// RaspberryPi: 6 1 22 24 23 19 21
#include <cstdlib>
#include <iostream>
#include <RF24/RF24.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <mysql/mysql.h>
#define DATABASE_SERVER "localhost"
#define DATABASE_NAME "sensors"
#define DATABASE_USERNAME "dbusername"
#define DATABASE_PASSWORD "password"
MYSQL *mysql1;
using namespace std;
RF24 radio(RPI_V2_GPIO_P1_22, RPI_V2_GPIO_P1_24, BCM2835_SPI_SPEED_8MHZ);
void setup(void)
{
// init radio for reading
radio.begin();
radio.enableDynamicPayloads();
radio.setAutoAck(1);
radio.setRetries(15,15);
radio.setDataRate(RF24_250KBPS);
radio.setPALevel(RF24_PA_MAX);
radio.setChannel(76);
radio.setCRCLength(RF24_CRC_16);
radio.openReadingPipe(1,0xF0F0F0F0E1LL);
radio.startListening();
//initialize MYSQL object for connections
mysql1 = mysql_init(NULL);
if(mysql1 == NULL)
{
fprintf(stderr, "ABB : %s\n", mysql_error(mysql1));
return;
}
//Connect to the database
if(mysql_real_connect(mysql1, DATABASE_SERVER, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, 0, NULL, 0) == NULL)
{
fprintf(stderr, "%s\n", mysql_error(mysql1));
}
else
{
printf("Database connection successful.\r\n");
}
}
void loop(void)
{
// 32 byte character array is max payload
char receivePayload[32]="";
// sleep 20 ms
usleep(20000);
while (radio.available())
{
// read from radio until payload size is reached
uint8_t len = radio.getDynamicPayloadSize();
radio.read(receivePayload, len);
cout << receivePayload << endl;
// conto i separatori ":"
const char * z = receivePayload;
int separator_count;
int m;
separator_count = 0;
for (m=0; z[m]; m++) {
if(z[m] == ':') {
separator_count ++;
}
}
// se sono 5, ok
if (separator_count == 5) {
int data_type = 0;
char * serial;
int counter = 0;
float data = 0;
float battery = 0;
char query[256];
data_type = atoi(strtok (receivePayload, ":"));
serial = strtok (NULL, ":");
counter = atoi(strtok (NULL, ":"));
data = atof(strtok (NULL, ":")) / 100;
battery = atof(strtok (NULL, ":")) / 1000;
printf("%04d %s %04d %.2f %.3f \n",data_type,serial,counter,data,battery);
sprintf(query, "INSERT INTO rec_data (data_type,serial,counter,data,battery) VALUES (%04d,'%s',%04d,%.2f,%.3f)", data_type, serial, counter, data, battery);
// printf("%s \n",query);
mysql_query(mysql1,query);
}
}
}
int main(int argc, char** argv)
{
setup();
while(1)
loop();
return 0;
}
E' possibile inviare i dati ad un database server remoto (opportunamente predisposto con db, utente, previlegi e tabella) sostituendo "localhost" con l'indirizzo ip del server.
Compilare con
g++ -Ofast -mfpu=vfp -mfloat-abi=hard -march=armv6zk -mtune=arm1176jzf-s -Wall -I../ -lrf24-bcm nRF24l01_receive.cpp -o nRF24l01_receive `mysql_config --cflags` `mysql_config --libs`
g++ -Ofast -mfpu=vfp -mfloat-abi=hard -march=armv6zk -mtune=arm1176jzf-s -Wall -I../ -lrf24-bcm nRF24l01_receive.cpp -o nRF24l01_receive `mysql_config --cflags` `mysql_config --libs`
Accendere i sensori
Controllare la tabella che viene popolata:
mysql> select * from rec_data;
+---------------------+-----------+--------+---------+-------+---------+
| timestamp | data_type | serial | counter | data | battery |
+---------------------+-----------+--------+---------+-------+---------+
| 2015-02-12 21:51:28 | 1 | 0001 | 0 | 19.88 | 3.587 |
| 2015-02-12 21:52:36 | 1 | 0001 | 1 | 21.39 | 3.587 |
| 2015-02-12 21:53:39 | 1 | 00A2 | 0 | 20.07 | 4.037 |
| 2015-02-12 21:53:44 | 1 | 0001 | 2 | 21.34 | 3.587 |
| 2015-02-12 21:53:57 | 1 | 00A2 | 1 | 22.05 | 4.037 |
| 2015-02-12 21:54:14 | 1 | 00A2 | 2 | 22 | 4.037 |
| 2015-02-12 21:54:31 | 1 | 00A2 | 3 | 21.76 | 4.037 |
| 2015-02-12 21:54:49 | 1 | 00A2 | 4 | 21.58 | 4.037 |
| 2015-02-12 21:54:52 | 1 | 0001 | 3 | 21.06 | 3.587 |
+---------------------+-----------+--------+---------+-------+---------+
9 rows in set (0.00 sec)
Mettiamo tutto su web
<?php
print "<head><title>Test Sensors</title>
<meta name=\"apple-mobile-web-app-capable\" content=\"yes\">
<link rel=\"apple-touch-icon\" href=\"/app_icon128.png\">
<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\" />
<BR><BR><CENTER>
<style type=\"text/css\">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #666666;
background-color: #dedede;
}
table.gridtable td {
border-width: 1px;
padding: 8px;
text-align: center;
border-style: solid;
border-color: #666666;
background-color: #ffffff;
}
</style>";
$servername = "localhost";
$username = "dbusername";
$password = "password";
$dbname = "sensors";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT timestamp, data, battery FROM rec_data where serial = '0001' order by timestamp desc limit 10";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
print "
<table class=\"gridtable\">
<tr><th colspan=3>Frigorifero</th></tr>
<tr><th>Timestamp</th><th>Temperatura</th><th>Batteria</th></tr>
";
while($row = $result->fetch_assoc()) {
echo "<TR><TD>".$row["timestamp"]. "</TD><TD>" . $row["data"]. "</TD><TD>" . $row["battery"]. "</TD></TR>";
}
echo "</table>";
} else {
echo "0 results";
}
echo "<BR><BR>";
$sql = "SELECT timestamp, data, battery FROM rec_data where serial = '00B3' order by timestamp desc limit 10";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
print "
<table class=\"gridtable\">
<tr><th colspan=3>Congelatore</th></tr>
<tr><th>Timestamp</th><th>Temperatura</th><th>Batteria</th></tr>";
while($row = $result->fetch_assoc()) {
echo "<TR><TD>".$row["timestamp"]. "</TD><TD>" . $row["data"]. "</TD><TD>" . $row["battery"]. "</TD></TR>";
}
echo "</table>";
} else {
echo "0 results";
}
$conn->close();
?>
