Libelium - Development

NOTE: This is an old article. Please go to the Meshlium development section in order to get the latest tutorials.

Storing Bluetooth Sensor Data on a Database

This code example has been tested and improved to be executed in Meshlium with the data coming from a Wireless Sensor Network created with Waspmote.

Frame structure

This example will read from a Bluetooth Serial Port frames from Waspmote with acceloremter sensor data. Valid frames will be stored on a database.

For this example the frame will have this structure:

<mote_id>#accx#Sensor_value#accy#Sensor_value#accz#Sensor_value

A valid frame can be:

w01#accx#1#accy#1#accz#1026

Programming Waspmote

Waspmote will initialize components, read accelerometer sensor values and send it to meshlium using it's Bluetooth Serial Port.

File:
"BT_accelerometer.pde"
Note:
Meshlium Bluetooth MAC is hardcoded into code. Update with your Bluetooth MAC.


/*
 *  ------Waspmote Bluetooth Sending Data Example--------
 *
 *  Explanation: This example shows how to set send data to Meshlium
 *
 *  Copyright (C) 2009 Libelium Comunicaciones Distribuidas S.L.
 *  http://www.libelium.com
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 *  Version:		0.1
 *  Design:	        David Gascón
 *  Implementation:	Alberto Bielsa
 */

int n=0;
char data[100];
long previous=0;
long timeSend=0;
int counter=0;

void setup(){
  // setup for Serial port over USB
  USB.begin();
  USB.println("USB port started...");
  USB.close();

  // Powering BT
  BT.ON();

  // Init
  if(!BT.init()) USB.println("Init OK");

  // Init ACC
  ACC.begin();    // opens I2C bus
  ACC.setMode(ACC_ON); // starts accelerometer
}

void loop(){

  // Discovering the channel service
  if(!BT.discoverDevice("0018F88981F6","1101"))
  {

    // Creating a connection to a device
    if(!BT.createConnection("0018F88981F6",BT.device_service_channel))
    {
      USB.println("");
      USB.print("--- CONNECTION -- MTU: ");
      USB.print(BT.connection_mtu[0],BYTE);
      USB.print(BT.connection_mtu[1],BYTE);
      USB.println(BT.connection_mtu[2],BYTE);
    }
    else USB.println("Connection failed");

    // Creating a transparent connection
    BT.createStreamConnection();

    // Sending the Accelerometer data to the opened connection
    while(1){
      sprintf(data,"W01#ACCX#%d#ACCY#%d#ACCZ#%d%c%c",ACC.getX(), ACC.getY(), ACC.getZ(), '\r', '\n');
      printString(data,0);
      delay(1000);
    }
  }
}

Creating Database

The database to store data will store the following values: ID of the mote, sensor values recived, and the time of the received frame will be stored.

First we need to create the database for the example.

create database cursoWasp;
use cursoWasp;

Add a user that can access the database.

GRANT ALL PRIVILEGES ON cursoWasp.* TO 'cursoUser'@'localhost' IDENTIFIED BY 'cursoPass' WITH GRANT OPTION;
flush privileges;

Finally create the database.

--
-- Table structure for table `mote_data`
--

DROP TABLE IF EXISTS `mote_data`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `mote_data` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `date` datetime NOT NULL,
  `mote_id` varchar(100) NOT NULL,
  `s1_id` varchar(100) NOT NULL,
  `s1_val` varchar(100) NOT NULL,
  `s2_id` varchar(100) NOT NULL,
  `s2_val` varchar(100) NOT NULL,
  `s3_id` varchar(100) NOT NULL,
  `s3_val` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1599 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

Configuring Meshlium to accept Bluetooth connections

First prepare bluetooth device so it can be scanned and accessed.

~# hciconfig hci0 piscan

Lauch rfcomm to listen on port 4, and stablish a Serial Port when a device connect on port 4.

~# rfcomm watch hci0 4

Then is nedded to register our new Serial Port Service so other devices can find it.

~# sdptool add --channel 4 SP

As soon as a Bluetooth Waspmote connect us the file /dev/rfcomm0 will be created and will be accesible as a Serial Port by any proccess

~# rfcomm watch hci0 4
Waiting for connection on channel 4
Connection from 00:03:19:0D:1D:B3 to /dev/rfcomm0
Press CTRL-C for hangup

Sniffer code

This program connects to /dev/rfcomm0 and reads frames received through Bluetooth module. Correct frames will be parsed and stored on a MySQL database.

File:
"sniffer_bt_ddbb.c"

Compilation on Meshlium:
gcc -o sniffer_bt_ddbb -I/usr/include/mysql sniffer_bt_ddbb.c -L/usr/lib/mysql -lmysqlclient -Wall

Usage:
sniffer_bt_ddbb S0 38400 localhost

Code



/*
 *  Copyright (C) 2008 Libelium Comunicaciones Distribuidas S.L.
 *  http://www.libelium.com
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 2 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 *  Version 0.1
 *  Author: Octavio Benedi Sanchez
 */

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <fcntl.h>
#include <errno.h>
#include <stdlib.h>
#include <termios.h> /* Terminal control library (POSIX) */
#include <ctype.h>
#include <mysql.h>


#define MAX 500
#define MAX_SQL 700

int main(int argc, char *argv[]) {
    int sd = 3;
    char *serialPort = "";
    char *BTserialPort0 = "/dev/rfcomm0";
    char *BTserialPort1 = "/dev/rfcomm1";
    char *BTserialPort2 = "/dev/rfcomm2";
    char valor[MAX] = "";
    char c;
    struct termios opciones;
    char *bt0 = "rfcomm0";
    char *bt1 = "rfcomm1";
    char *bt2 = "rfcomm2";

    int speed = B19200;

    typedef struct {
        char *name;
        int flag;
    } speed_spec;

    speed_spec speeds[] = {
        {"1200", B1200},
        {"2400", B2400},
        {"4800", B4800},
        {"9600", B9600},
        {"19200", B19200},
        {"38400", B38400},
        {"57600", B57600},
        {"115200", B115200},
        {NULL, 0}
    };

    /**************************************
     * Defines for MYSQL
     *************************************/
    char sql[MAX_SQL] = "";
    MYSQL mysql;

    /* STARTING MAIN */
    if (argc != 4) {
        fprintf(stderr, "Usage: %s port speed bbdd_ip\n", argv[0]);
        exit(0);
    }
    if (argc == 4) {
        speed_spec *s;
        for (s = speeds; s->name; s++) {
            if (strcmp(s->name, argv[2]) == 0) {
                speed = s->flag;
                fprintf(stderr, "setting speed %s\n", s->name);
                break;
            }
        }
    }
    if (!strcmp(argv[1], bt0)) {
        //fprintf(stderr,"rfcomm0 chosen\n...");
        serialPort = BTserialPort0;
    }
    if (!strcmp(argv[1], bt1)) {
        //fprintf(stderr,"rfcomm1 chosen\n...");
        serialPort = BTserialPort1;
    }
    if (!strcmp(argv[1], bt2)) {
        //fprintf(stderr,"rfcomm2 chosen\n...");
        serialPort = BTserialPort2;
    }
    if (!strcmp(serialPort, "")) {
        fprintf(stderr, "Choose a valid port (rfcomm0, rfcomm1, rfcomm2)\n");
        exit(0);
    }

    if ((sd = open(serialPort, O_RDWR | O_NOCTTY | O_NDELAY)) == -1) {
        fprintf(stderr, "Unable to open the serial port %s - \n", serialPort);
        exit(-1);
    } else {
        if (!sd) {
            sd = open(serialPort, O_RDWR | O_NOCTTY | O_NDELAY);
        }
        fcntl(sd, F_SETFL, 0);
    }
    tcgetattr(sd, &opciones);
    cfsetispeed(&opciones, B38400);
    cfsetospeed(&opciones, B38400);
    opciones.c_cflag |= (CLOCAL | CREAD);
    /*No parity*/
    opciones.c_cflag &= ~PARENB;
    opciones.c_cflag &= ~CSTOPB;
    opciones.c_cflag &= ~CSIZE;
    opciones.c_cflag |= CS8;
    /*raw input:
     * making the applycation ready to receive*/
    opciones.c_lflag &= ~(ICANON | ECHO | ECHOE | ISIG);
    /*Ignore parity errors*/
    opciones.c_iflag |= ~(INPCK | ISTRIP | PARMRK);
    opciones.c_iflag |= IGNPAR;
    opciones.c_iflag &= ~(IXON | IXOFF | IXANY | IGNCR | IGNBRK);
    opciones.c_iflag |= BRKINT;
    /*raw output
     * making the applycation ready to transmit*/
    opciones.c_oflag &= ~OPOST;
    /*aply*/
    tcsetattr(sd, TCSANOW, &opciones);

    mysql_init(&mysql);
    mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, "your_prog_name");
    if (!mysql_real_connect(&mysql, argv[3], "cursoUser", "cursoPass", "cursoWasp", 0, NULL, 0)) {
        fprintf(stderr, "Failed to connect to database: Error: %s\n", mysql_error(&mysql));
        exit(-1);
    } else {
        fprintf(stderr, "Connected to database: return: %s\n", mysql_error(&mysql));
    }


    int j = 0;
    int ret;
    char mote_id[50]="";
    char s1_id[50]="";
    int s1_val;
    char s2_id[50]="";
    int s2_val;
    char s3_id[50]="";
    int s3_val;

    while (1) {

        read(sd, &c, 1);
        if ((isprint(c) != 0) || (c == '\n')) {
            valor[j] = c;
            j++;
        }
        if ((c == '\n') || (j == (MAX - 1))) {
            valor[j] = '\0';
            ret = sscanf(valor, "%[^#]#%[^#]#%d#%[^#]#%d#%[^#]#%d", &mote_id[0], &s1_id[0], &s1_val, &s2_id[0], &s2_val, &s3_id[0], &s3_val);
            if (ret == 7) {
                fprintf(stderr, "\nFrame: %s\n", valor);
                fprintf(stderr, "\nWasp id: %s\n%s:%d\n%s:%d\n%s:%d\n\n", mote_id, s1_id, s1_val, s2_id, s2_val, s3_id, s3_val);

                // Create SQL sentence for insertion on Database.
                sprintf(sql, "insert into mote_data values(NULL,now(),'%s','%s','%d','%s','%d','%s','%d','',0);", mote_id, s1_id, s1_val, s2_id, s2_val, s3_id, s3_val);
                if (mysql_query(&mysql, sql) != 0) {
                    fprintf(stderr, "DDBB Failed: %s\n", sql);
                } else {
                    fprintf(stdout, "DDBB Succeeded: %s\n", sql);
                }
            } else {
                fprintf(stderr, "\ncadena: %s\n", valor);
                fprintf(stderr, "\nret:%d\nWasp id: %s\n%s:%d\n%s:%d\n%s:%d\n\n", ret, mote_id, s1_id, s1_val, s2_id, s2_val, s3_id, s3_val);
                fprintf(stderr, "\nBad frame received\n");
            }
            j = 0;
            valor[j] = '\0';
        }
    }
    mysql_close(&mysql);
    close(sd);
    exit(0);
}

Visualization of the data received on a web page

Finally we will make a PHP script that connects to the DDBB and shows last 25 frames received.


<?php

$BBDD_host='localhost';
$BBDD_dbuser='cursoUser';
$BBDD_dbpass='cursoPass';
$BBDD_database='cursoWasp';

function GetMyConnection()
{
	global $g_link;
	global $BBDD_host;
	global $BBDD_dbuser;
	global $BBDD_dbpass;
	global $BBDD_database;
	if( $g_link ){
            return $g_link;
        }
	$g_link = mysql_connect( $BBDD_host, $BBDD_dbuser, $BBDD_dbpass) or die('Could not connect to mysql server.' );
	mysql_select_db($BBDD_database, $g_link) or die('Could not select database.');
	return $g_link;
}
function query($sql)
{
	global $g_link;
	$result = mysql_query($sql, $g_link) or die("Could not execute query $sql");
	return $result;
}

$db_conn=GetMyConnection();
$sql='SELECT * FROM mote_data ORDER BY id desc limit 25 ;';
$query_result=query($sql);
$bbdd='<tr><th>Id</th><th>Fecha</th><th>Mote Id</th><th>Sensor</th><th>Dato</th><th>Sensor</th><th>Dato</th><th>Sensor</th><th>Dato</th></tr>';
while ($row = mysql_fetch_assoc($query_result))
{
	$bbdd.='<tr><td>'.$row['id'].'</td><td>'.$row['date'].'</td><td>'.$row['mote_id'].'</td><td>'.$row['s1_id'].'</td><td>'.$row['s1_val'].'</td><td>'.$row['s2_id'].'</td><td>'.$row['s2_val'].'</td><td>'.$row['s3_id'].'</td><td>'.$row['s3_val'].'</td></tr>';
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
  <title>WSN data example</title>
</head>
    <body>
        <table style="text-align: left;" border="1" cellpadding="10" cellspacing="0">
            <tbody>
              <?=$bbdd ?>
            </tbody>
        </table>
    </body>
</html>

Generation of a CSV with the data received

Finally we will make a PHP script that connects to the DDBB and generate a CSV file with all the data received from waspmote.


<?php

$BBDD_host='localhost';
$BBDD_dbuser='cursoUser';
$BBDD_dbpass='cursoPass';
$BBDD_database='cursoWasp';

function GetMyConnection()
{
	global $g_link;
	global $BBDD_host;
	global $BBDD_dbuser;
	global $BBDD_dbpass;
	global $BBDD_database;

	if( $g_link ){
            return $g_link;
        }
	$g_link = mysql_connect( $BBDD_host, $BBDD_dbuser, $BBDD_dbpass) or die('Could not connect to mysql server.' );
	mysql_select_db($BBDD_database, $g_link) or die('Could not select database.');
	return $g_link;
}
function query($sql)
{
	global $g_link;
	$result = mysql_query($sql, $g_link) or die("Could not execute query $sql");
	return $result;
}

// /tmp/example_csv_file.csv will be generated but any filename can be used.
$fp=fopen('/tmp/example_csv_file.csv','w');

$db_conn=GetMyConnection();
$sql='SELECT * FROM mote_data ORDER BY id asc;';
$query_result=query($sql);
$last_id='0';
while ($row = mysql_fetch_assoc($query_result))
{
    $last_id=$row['id'];
    $bbdd.=$row['id'].','.$row['date'].','.$row['mote_id'].','.$row['s1_id'].','.$row['s1_val'].','.$row['s2_id'].','.$row['s2_val'].','.$row['s3_id'].','.$row['s3_val']."\n";
    fwrite($fp,$bbdd);
}
fclose($fp);
?>

Download code

You can download the code of this tutorial.

(*)

I have read and I accept the privacy policy

In order to comply with the provisions in Spanish Data Protection Act (L.O. 15/1999 of 13th December) and its implementing Regulation (R.D. 1720/2007, of 21st December), LIBELIUM hereby informs its Customers and websites users that any personal data supplied through the forms contained in its websites shall be recorded in a file named “CUSTOMERS” for which LIBELIUM is responsible, located at LIBELIUM´s registered address and which has been registered with the Spanish General Registry for Data Protection (Registro General de Protección de Datos de la Agencia Española de Protección de Datos).

Except for those fields expressly requesting compulsory filling, answers to the questions contained in any form are voluntary and therefore, the lack of answer does not imply a reduction of the quantity or quality of LIBELIUM´s services. The aim of this data collection are to provide access to LIBELIUM´s online shop, to send information on LIBELIUM´s Product(s) and services as well as to collect Customer’s acceptance to the terms of use of the forums and other sections in LIBELIUM´s websites. By filling these forms Customers consent in receiving promotional offers concerning LIBELIUM´s Product(s) and services. You further consent further that LIBELIUM may make such data available to its services suppliers to the sole purpose of enabling service provision.

Any User providing personal data through any of the forms in LIBELIUM´s websites shall count on a 30 day period to inform LIBELIUM in writing of his/her refusal to the processing of his/her personal data. Unless this communication is made, it shall be deemed that the User consents the processing of his/her personal data according to the terms set forth in these T&C. All personal data collected shall be processed by using the security measures requested by Law to avoid their loss, damage or access by any unauthorised third party. User may nevertheless be aware of the fact that the existing security measures for computer systems on the Internet are not entirely trustworthy. Should you think that your email address has been disclosed to us without your consent, do not hesitate to inform us thereof. Users may at any time exercise their rights of access, rectification, update, cancellation and objection, as well as revoke the consent granted for all of any of the above-mentioned processings, by sending a written statement to LIBELIUM, either through the “Contact” section in our website http://www.libelium.com or by post to the following address: LIBELIUM COMUNICACIONES DISTRIBUIDAS, S.L., c/ María de Luna, 11, nave 5, CEEIARAGON. 50018 Zaragoza (Spain).

To exercise the above-mentioned rights you must indicate your name, ID/passport number, full address, date and signature, including any document supporting your request, in case it is needed; a copy of your ID card/passport must be enclosed, unless you use an electronic signature. User shall be liable for the veracity of data provided and only in case of holders of parental rights concerning children under fourteen and in connection with said children, shall LIBELIUM accept the disclosure of personal data concerning third parties. Users shall assume all liability for direct or consequential damages arising out of or in connection with the provision of false, inaccurate, incomplete or non updated data.

Furthermore, LIBELIUM reserves its right to exclude from any service for which prior registration is required, to any User having provided false data or failing to comply with these T&C, notwithstanding any other legal action to which LIBELIUM may be entitled. LIBELIUM reserves its right to modify its Privacy Policy or these T&C to adapt them to the regulations in force or for any other reason. Provided that the use of LIBELIUM´s websites by Users shall be deemed as User’s acceptance of LIBELIUM´s Privacy Policy and T&C, User is hereby requested to check these T&C and any further amendments from time to time.

Privacy Policy
Users may visit LIBELIUM´s websites without disclosing neither their identity nor any personal data. LIBELIUM´s servers may only collect domain names but not email addresses of their visitors. This kind of information is used to elaborate reports on visit statistics, the time spent in the websites, websites accessed, the general origin of visitors (through “Favourites”, search engines, links from other websites, etc.) to the sole purposes of getting information on how the websites are used and improving their contents and services.

Linked sites
LIBELIUM´s websites may provide links to other sites but LIBELIUM assumes no liability on the privacy policies adopted by the linked sites, directly or indirectly. Links to other sites are provided as a suggestion only and do not imply LIBELIUM´s warranty or liability concerning their quality, accuracy or contents of the information provided therein.

LIBELIUM does not warrant the veracity or accuracy of the information disclosed by its suppliers or third parties whose products or services are offered through LIBELIUM, their origin, ownership or the use or practical implementation made by Customers.

(*) Mandatory fields

| Terms of use