Up:Tutorial?


The aim of this tutorial is to explain how to use mysql database using ROS.

In the example below, data are sent from SIGViewer to SIGVerse controller as messages, which will be sent after that to mysql controller using ROS topic, the mysql controller uses these data to communicate with the database server.

Packages creation

Create 2 packages, sig_mysql and db_controller:

$ cd ~/catkin_ws/src
$ catkin_create_pkg sig_mysql
$ catkin_create_pkg db_controller

Overview

When the server is launched, 2 ros nodes are created, the SIGVerse controller node and ROS mysql node, which involves the creation of a new topic called “database”, it is used to read/write data. Data are sent from the SIGViewer to the controller as messages; then they are published on the database topic to be read after that by the ROS mysql node, this later communicates with the Mysql database by sending/receiving queries.  

overview.PNG rosMysql.png

Server

SIGVerse controller

This controller aims to receive data from SIGViewer and sends them to the mysql controller node.

sigMyql.cpp file:

#include "ControllerEvent.h"
#include "Controller.h"
#include "Logger.h"
#include <unistd.h>
#include <algorithm>
#include "ros/ros.h"
#include "std_msgs/String.h"
class MyController : public Controller {
 public:
  void onInit(InitEvent &evt);
  double onAction(ActionEvent&);
  void onRecvMsg(RecvMsgEvent &evt);
  ros::Publisher chatter_pub;
private:
 std::vector <std::string> m_trashes;
 std_msgs::String msg;
};
void MyController::onInit(InitEvent &evt)
{
 int argc =0;
 char** argv = NULL;
 ros::init(argc, argv, "SIG_Mysql");
 ros::NodeHandle n;
 chatter_pub = n.advertise<std_msgs::String>("database", 1);
}
double MyController::onAction(ActionEvent &evt)
{
  return 1.0;
}
void MyController::onRecvMsg(RecvMsgEvent &evt){
 msg.data = evt.getMsg();
 if(msg.data != ""){
 LOG_MSG(("message : %s", msg.data.c_str()));
 }
 chatter_pub.publish(msg);
 msg.data ="";
}
extern "C" Controller * createController() {
 return new MyController;
}

Mysql controller

This controller receives data from the SIGVerse controller then uses them to communicate with the database.

mysql_ros.cpp file:
  // ROS includes
#include <ros/ros.h>
#include <signal.h>
#include <termios.h>
#include <stdio.h>
#include "std_msgs/String.h"
// Mysql includes
#include <my_global.h>
#include <mysql.h>
   MYSQL *con = mysql_init(NULL);
   int ID = 0;
   std::ostringstream oss;
   std::string querry;
void static finish_with_error(MYSQL *con)
{
 fprintf(stderr, "%s\n", mysql_error(con));
 mysql_close(con);
 exit(1);
}
void chatterCallback(const std_msgs::String::ConstPtr& msg)
{
 ID = ID + 1;
 oss << ID;
 std::string IDStr = oss.str();
 oss.str("");
 querry = "INSERT INTO objects VALUES("+IDStr+",'"+msg->data+"','green')";
if (mysql_query(con, querry.c_str())) {
     finish_with_error(con);
 }
}
int main(int argc, char **argv)
{
 ros::init(argc, argv, "mysql_command");
 ros::NodeHandle n;
 ros::Subscriber sub = n.subscribe("database", 1, chatterCallback);
 if (con == NULL)
 {
     fprintf(stderr, "%s\n", mysql_error(con));
     exit(1);
 }
 if (mysql_real_connect(con, "localhost", "ericgt", "", 
         "sigverse", 0, NULL, 0) == NULL) 
 {
     fprintf(stderr, "%s\n", mysql_error(con));
     mysql_close(con);
     exit(1);
 }
 if (mysql_query(con, "DROP TABLE IF EXISTS objects")) {
     finish_with_error(con);
}
 if (mysql_query(con, "CREATE TABLE objects(Id INT, Name TEXT, color TEXT)")) {      
     finish_with_error(con);
 }
 ros::spin();
 return(0);
}

Compiling

Add SIGVerse include path to CMakeLists.txt file:

include_directories (PATH-TO-SIGVERSE-LIBRARIES/include/sigverse PATH-TO-SIGVERSE-LIBRARIES/include/sigverse/comm/controller )

Then go to the catkin_ws directory to compile the project:

$ cd ~/catking_make
$ catkin_make

World file

Click on the link to download the world file:

The world file should be placed in "~/catkin_ws/devel/lib/libsigMysql", where the .so file is generated.

Running the server

To run the server you have to follow the following steps:

  • Go to catkin_ws and execute the follwing command :
    $ source ./devel/setup.bash
  • Run the ROS system:
    $ roscore
  • run the ros mysql node :
    $ rosrun database mysql_ros 
  • Go to ~/catkin_ws/devel/lib/libsigMysql and run the sigverse controller:
    $ cd ~/catkin_ws/devel/lib/libsigMysql
    $ sigserver.sh -w ./mysql_world.xml

Download the project

To download the project from GIT repository, use the following link:

git@socio4.iir.nii.ac.jp:~/SigverseGitServer/unstable/usersContribution/tafifet/mysql_ros.git

Counter: 4327, today: 1, yesterday: 0

Attach file: filerosMysql.png 1600 download [Information] filemysql_world.xml 1303 download [Information] fileoverview.PNG 1675 download [Information]

Front page   Edit Freeze Diff Backup Upload Copy Rename Reload   New List of pages Search Recent changes   Help   RSS of recent changes
Last-modified: 2014-02-06 (Thu) 13:34:19 (2727d)