Slartitorto: BAsic Remote Sensor su mysql

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;

+-----------+------------+------+-----+-------------------+-----------------------------+

+-----------+------------+------+-----+-------------------+-----------------------------+

| data_type | int(11) | YES | | NULL | |

| counter | int(11) | 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:

// 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;

}

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`

Accendere i sensori

Controllare la tabella che viene popolata:

mysql> select * from rec_data;

+---------------------+-----------+--------+---------+-------+---------+

+---------------------+-----------+--------+---------+-------+---------+

| 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>

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 "

<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 "

<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();

Slartitorto

giovedì 12 febbraio 2015

BAsic Remote Sensor su mysql

Nessun commento:

Posta un commento

Translate