Interfacing the Sensor Network with the Web 2.0 (Twitter and Wordpress)
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.
This example will define a simple trigger based on an arbitrary value. Values over the trigger will be considered an alert and will be twitted .
Note that on real applications each sensor usage has it's own alert parameters, and several alerts can be launched over different sensors.
For this example the frame will have the same structure used in previous tutorials
ID_<mote>#Sensor_value
A valid frame can be:
ID_12#239
Creating Database
The database to store data will store the following values: ID of the mote, sensor value recived, and the time of the received frame will be stored.
First we need to create the database for the example.
create database sensordemo; use sensordemo;
Add a user that can access the database.
GRANT ALL PRIVILEGES ON sensordemo.* TO 'sdemo'@'localhost' IDENTIFIED BY 'sdemopass' WITH GRANT OPTION; flush privileges;
Finally create the database.
CREATE TABLE sensor_data ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, date timestamp not null, mote_id INT UNSIGNED NOT NULL, sensor_value INT UNSIGNED NOT NULL, PRIMARY KEY (id) );
Sniffer with twitter alert code
This program reads frames received through a Bluetooth Serial Port or a XBee module and store it on a MySQL database. If a detected value if greater that alert_value, an alarm is processed and a post on twitter is made.
File:
"sniffer_ddbb_twitter_alarm.c"
Compilation on Meshlium:
gcc -o sniffer_ddbb_twitter_alarm -I/usr/include/mysql sniffer_ddbb_twitter_alarm.c -L/usr/lib/mysql -lmysqlclient -Wall
Usage:
sniffer_ddbb_twitter_alarm S0 localhost
Note:
Serial port speed should be set on sniffer execution.
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 *serialPort0 = "/dev/ttyS0";
char *serialPort1 = "/dev/ttyS1";
char *USBserialPort0 = "/dev/ttyUSB0";
char *USBserialPort1 = "/dev/ttyUSB1";
char *USBserialPort2 = "/dev/ttyUSB2";
char *USBserialPort3 = "/dev/ttyUSB3";
char *USBserialPort4 = "/dev/ttyUSB4";
char *USBserialPort5 = "/dev/ttyUSB5";
char *BTserialPort0 = "/dev/rfcomm0";
char *BTserialPort1 = "/dev/rfcomm1";
char *BTserialPort2 = "/dev/rfcomm2";
char valor[MAX] = "";
char c;
struct termios opciones;
char *s0 = "S0";
char *s1 = "S1";
char *u0 = "USB0";
char *u1 = "USB1";
char *u2 = "USB2";
char *u3 = "USB3";
char *u4 = "USB4";
char *u5 = "USB5";
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;
/**************************************
* Alarm values
*************************************/
int alarm_value = 612;
/* 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], s0)) {
//fprintf(stderr,"ttyS0 chosen\n...");
serialPort = serialPort0;
}
if (!strcmp(argv[1], s1)) {
//fprintf(stderr,"ttyS1 chosen\n...");
serialPort = serialPort1;
}
if (!strcmp(argv[1], u0)) {
//fprintf(stderr,"ttyUSB0 chosen\n...");
serialPort = USBserialPort0;
}
if (!strcmp(argv[1], u1)) {
//fprintf(stderr,"ttyUSB1 chosen\n...");
serialPort = USBserialPort1;
}
if (!strcmp(argv[1], u2)) {
//fprintf(stderr,"ttyUSB2 chosen\n...");
serialPort = USBserialPort2;
}
if (!strcmp(argv[1], u3)) {
//fprintf(stderr,"ttyUSB3 chosen\n...");
serialPort = USBserialPort3;
}
if (!strcmp(argv[1], u4)) {
//fprintf(stderr,"ttyUSB4 chosen\n...");
serialPort = USBserialPort4;
}
if (!strcmp(argv[1], u5)) {
//fprintf(stderr,"ttyUSB5 chosen\n...");
serialPort = USBserialPort5;
}
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 (S0, S1, USB0, USB1, USB2, USB3, USB4, USB5, rfcomm0, rfcomm1, rfcomm2)\n");
exit(0);
}
if ((sd = open(serialPort, O_RDWR | O_NOCTTY | O_NONBLOCK)) == -1) {
fprintf(stderr, "Unable to open the serial port %s - \n", serialPort);
exit(-1);
} else {
if (!sd) {
/*Sometimes the first time you call open it does not return the
* right value (3) of the free file descriptor to use, for this
* reason you can set manually the sd value to 3 or call again
* the open function (normally returning 4 to sd), advised!*/
sd = open(serialPort, O_RDWR | O_NOCTTY | O_NONBLOCK);
}
//fprintf(stderr,"Serial Port open at: %i\n", sd);
fcntl(sd, F_SETFL, 0);
}
tcgetattr(sd, &opciones);
cfsetispeed(&opciones, speed);
cfsetospeed(&opciones, speed);
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);
int j = 0;
int ret;
int mote_id;
int sensor_value;
mysql_init(&mysql);
mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, "your_prog_name");
if (!mysql_real_connect(&mysql, argv[3], "sdemo", "sdemopass", "sensordemo", 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));
}
while (1) {
read(sd, &c, 1);
valor[j] = c;
j++;
if ((c == '\n') || (j == (MAX - 1))) {
valor[j] = '\0';
ret = sscanf(valor, "ID_%d#%d", &mote_id, &sensor_value);
if (ret == 2) {
sprintf(sql, "insert into sensor_data values(NULL,NULL,'%d','%d');", mote_id, sensor_value);
if (mysql_query(&mysql, sql) != 0) {
fprintf(stderr, "BBDDFailed:%s\n", sql);
} else {
fprintf(stdout, "BBDDSucceeded:%s\n", sql);
}
if (sensor_value > alarm_value) {
// update twitter with the alarm
int pid;
pid = fork();
if (pid == 0) {
char order[255];
char *user = "twitter_user";
char *pass = "twitter_pass";
sprintf(order, "curl -s -u %s:%s -d status=\"Mote:%d Detected value %d\" http://twitter.com/statuses/update.xml >/dev/null", user, pass, mote_id, sensor_value);
system(order);
return (0);
}
}
} else {
fprintf(stderr, "Bad frame received\n");
}
j = 0;
valor[j] = '\0';
}
}
mysql_close(&mysql);
close(sd);
exit(0);
}
Wordpress
This example will define a set of PHP functions that will make possible integrate sensor data as wordpress posts.
<?php
/*
* 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
* Authors: Octavio Benedi Sanchez
* Manuel Calvo Catalan
*/
function Connect()
{
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;
}
$g_link;
$BBDD_host='localhost';
$BBDD_dbuser='ddbbuser';
$BBDD_dbpass='ddbbpass';
$BBDD_database='ddbbname';
$db_conn=Connect();
function add_category($_category){
// Create category
$sql='insert into wp_terms (term_id, name, slug, term_group) values (NULL, "'.$_category.'", "'.$_category.'", "0")';
query($sql);
$term_id=ifexist_category($_category);
// Create taxonomy
$sql='insert into wp_term_taxonomy (term_taxonomy_id,term_id, taxonomy, description, parent, count) values (NULL, "'.$term_id.'", "category", "", "0" ,"0")';
query($sql);
return $term_id;
}
function ifexist_category($_category){
$sql="Select term_id from wp_terms where name='".$_category."'";
$query_result=query($sql);
if ($row=mysql_fetch_array($query_result)){
return $row['term_id'];
}
else{
return -1;
}
}
function add_post($_title, $_body, $_category)
{
$post_date=date("Y-m-d G:i:s");
$x=date("G");
$post_date_gmt=date("Y-m-d ");
$post_date_gmt.=$x-2;
$post_date_gmt.=date(":i:s");
$sql="INSERT INTO wp_posts (ID ,post_author ,post_date ,post_date_gmt ,post_content ,post_title ,post_excerpt ,post_status ,comment_status ,ping_status ,post_password ,post_name ,to_ping ,pinged ,post_modified ,post_modified_gmt ,post_content_filtered ,post_parent ,guid ,menu_order ,post_type ,post_mime_type ,comment_count) VALUES (NULL , '0', '".$post_date."', '".$post_date_gmt."', '".$_body."', '".$_title."', '', 'publish', 'open', 'open', '', '".$_title."', '', '', '".$post_date."', '".$post_date_gmt."', '', '0', '', '0', 'post', '', '0')";
query($sql);
$sql="SELECT ID FROM wp_posts ORDER BY ID DESC LIMIT 1 ";
$query_result=query($sql);
if ($row=mysql_fetch_array($query_result)){
$last_id_post=$row['ID'];
}
$sql="UPDATE wp_posts SET guid='http://localhost/wordpress/?p=".$last_id_post."' WHERE ID=".$last_id_post." LIMIT 1";
query($sql);
$_cat_id = ifexist_category($_category);
if ($_cat_id == '-1'){
//If you are here there is an error you can log this error on any file.
$_cat_id = add_category($_category);
}
// Once the category is selected or is created,we need to add the post.
$sql="select term_taxonomy_id, count from wp_term_taxonomy where term_id='".$_cat_id."'";
$query_result=query($sql);
if ($row=mysql_fetch_array($query_result)){
$count = $row['count'];
$term_taxonomy_id = $row['term_taxonomy_id'];
$sql="insert into wp_term_relationships (object_id, term_taxonomy_id, term_order) values ('".$last_id_post."', '".$term_taxonomy_id."', '0')";
query($sql);
// Now update number of post in category
$sql="UPDATE wp_term_taxonomy SET count=count+1 WHERE term_id=".$_cat_id.";";
query($sql);
}
}
// Example of use:
add_post("Title of the post","Body of the post","Category for the post");
?>
Download code
You can download the code of this tutorial.