Using an external MySQL database with OMNeT++
for presenting and sharing simulation results

Introduction

This article will describe how to interface OMNeT++ with an external MySQL database in order to ease the presentation and sharing of the simulation results through a standard web browser

The main idea is to write simulation "log" information during (or at the end) of the simulation into a database in order to use an Apache web server with PHP to parse this information and to display it in an interactive graphical form with a standard web browser. Of course for doing this we need to develop some PHP code. This is efficient only if you have to analyze several configurations for the same network simulation and where heavy use of "Plove" and "Scalar" becomes painful. The usage of an object oriented graphical library (JpGraph) will considerably facilitate this approach.

The web server can be either on your local machine or on a central server same for the MySQL database server. If it is centralized, it is also a good way to share simulation results with your colleagues by allowing them to analyze simulation results with standard HTTP requests. As far as simulation performance is concerned, a local MySQL database will be more efficient, but this depends on what kind of results you are writing.

This database can, of course, also be used to "read" information from, and to drive your simulation.

Global overview

On the right side of the following picture you will see how the database is "connected" to OMNeT++:

Display examples

Based on the example of network below I will show you the implementation I made in order to present the results of the simulation of this network.
The first page will show some summary results, represented as "speedometer" to show either failure rate or CPU usage. Other types of graphs can show on a bar graph the type of traffic generated, the queue length status, and finally another part of the window allows displaying textual results.
To have a summary overview over several runs, the following display has been selected, so it is easy to compare them graphically:
To address a more detailed view for each run and to better understand what happens, a graphical presentation of histogram objects has been developed. So you can have almost the same display presentation as during an interactive graphical simulation.
Of course these report pages are related to the network I am simulating, and so they are not generic. Nevertheless the graphical objects, like histogram graphs, can be re-used in other web pages for other networks.

Prerequisites:

The following software packages are necessary for the examples presented (version given as indication only, other versions may work) and need to be installed:

MySQL database server (5.0.18 'full' installation required)
Apache2 web server (2.0.58)
PHP (4.4.2)
JpGraph (1.2x) Object-Oriented Graph creating library for PHP (http://www.aditus.nu/jpgraph)


Implementation

The implementation part is divided in 3 parts :

OMNeT++ implementation

On the simulator side we need to write information into the database, therefore I created a dedicated module. This module will contain all necessary functions to access to the database. The functions of this module can then be called by any other module of my network.

Function to write to the simulation log table "simul_log":
  /**
   * Allows to write to the MySQL database in the "simul_log" table
   * \param moduleName : name of the module to which rely this parameter
   * \param parName : name of the parameter to store
   * \param parValue : numerical value of the parameter (float)
   * \param parText : associated text value for this parameter
   */
   void mysqlDB::DBwriteLog (char moduleName[], char parName[], double parValue, char parText[])
   {
       // write simulation log information to the MySQL database
       int currentRun = simulation.runNumber();
       const char *ntwk = ev.config()->getAsString("General","network","n/a");
       char myquery[250];
       conn = mysql_init(NULL);
       if (!mysql_real_connect(conn,host,login,pwd,db,0,NULL,0))
       {
           // indicate problems with database connection
           ev << "MySQL error" << endl;;
       }
       sprintf(myquery,"insert into simul_log (Network,Run,Module,Name,nValue,sValue)
               values (\"%s\",%d,\"%s\",\"%s\",%e,\"%s\")",ntwk,currentRun,moduleName,parName,parValue,parText);
       t = mysql_query(conn,myquery);
       if (t) {ev << "MySQL error when making query" << endl;}
       mysql_close(conn);
   }
Function to write histogram type of information in the "histogram" table:
/**
 * This method allows to store the information hold by a cDoubleHistogram
 * into the MySQL table "histogram"
 * \param histogram : pointer to the histogram object
 * \param moduleName : name for this object
 */
void mysqlDB::DBwriteDblHist (cDoubleHistogram & histogram, char moduleName[])
{
    char myquery[250];
    int run = simulation.runNumber();
    const char *ntwk = ev.config()->getAsString("General", "network", "n/a");
    char dateStr [9];
    SYSTEMTIME st;                      // only for windows systems
    GetSystemTime(&st);
    sprintf(dateStr,"%d/%02d/%02d",st.wYear,st.wMonth,st.wDay);
    // write to database
    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn,host,login,pwd,db,0,NULL,0))
    {
        // indicate problems with database connection
        ev << "MySQL error" << endl;;
    }
    else
    {
        // remove previous record for same run and same name
        sprintf(myquery,"delete from history where (network='%s')and(run=%d)and(histName='%s')and(module='%s')",
                ntwk,run,histogram.name(),moduleName);
        t = mysql_real_query(conn,myquery,(int) strlen(myquery));
        if (t) {ev << "MySQL error when making query" << endl; }
        // insert overflow value (used to see if histogram has the rigth range)
        sprintf(myquery,"insert into histogram (network,run,saveDate,module,histName,cell,CellCenter,cellWidth,
                cellCount,cellPDF) values (\"%s\",%d,\"%s\",\"%s\",\"%s\",%d,%e,%e,%d,%e)",ntwk,run,dateStr,moduleName,
                histogram.name(),-1,-1.0,0.0,histogram.overflowCell(),0.0);
        t = mysql_real_query(conn,myquery,(int) strlen(myquery));
        if (t) {ev << "MySQL error when making query" << endl; }
        for (int i=0;i<histogram.cells();i++)
        {
            double cellWidth   = histogram.basepoint(i+1)-histogram.basepoint(i);
            double cellCenter  = (histogram.basepoint(i)+histogram.basepoint(i+1))/2;
            int count          = histogram.cell(i);
            double pdf         = histogram.cellPDF(i);
            sprintf(myquery,"insert into histogram (network,run,saveDate,module,histName,cell,cellCenter,cellWidth,
                    cellCount,cellPDF) values (\"%s\",%d,\"%s\",\"%s\",\"%s\",%d,%e,%e,%d,%e)",ntwk,run,dateStr,moduleName,
                    histogram.name(),i,cellCenter,cellWidth,count,pdf);
            t = mysql_real_query(conn,myquery,(int) strlen(myquery));
            if (t) {ev << "MySQL error when making query" << endl; }
        }
    }
    // close connection to database
    mysql_close(conn);
}

Do not forget to add this in the .h file of the code containing the previous functions:

...
#include <mysql.h>
...

Then a module who needs to save for example the information of one of its histogram object will use the following code. The class hosting the database interface code is here named "dbControl" and its instance name is "db".

The calling module will first have to get a pointer to the "db" module:

cDoubleHistogram cpuLoadStat;
...
// pointer to the database interface module

cModule *mod = simulation.moduleByPath("db");
dbControl *Cmod = check_and_cast<dbControl *>(mod); // this writes all info related to 'cpuLoadStat' histogram to the database
Cmod->DBwriteDblHist(cpuLoadStat,"CPU"); ...

This example shows how to write into the 'simul_log' table:

Cmod->DBwriteLog("MyModule","Traffic",((totalSendCallDuration+totalReceiveCallDuration)/simTime())/nbChannel,"Erlang");

NOTE: Since version 3.2, OMNeT++ itself also includes supports for recording simulation results into a MySQL database. It is implemented as a configuration option (omnetpp.ini), requiring no change to the simulation model's C++ code. Configuration (parameter settings, etc) and model topology may also be read from the database. By using that code (the Database sample in the OMNeT++ package), you can spare some or all of the above C++ code. OMNeT++ database support was inspired by Patrick's present work. --Andras

WEB page code

The web page code is a mixture of HTML and PHP. With PHP we have direct access to the MySQL database and so we can generate a dynamic web page. The graphs are generated using a object oriented PHP library (JpGraph), which makes this job very easy.

Graphical object code

This PHP code (here histo_graph.php) will generate a PNG image which will be used in the calling page like:

<?PHP
 print("<img src=histo_graph.php?run=".$simrun."&ntwk=".$ntwk."&hname=".$hname."&module=".$module.">");
 ?>

In addition, we will pass parameters with the code call like (simulation run, network name, histogram name, related module). These parameters will then be used to create the SQL query towards the database within histo_graph.php in order to retrieve the wanted information.

histo_graph.php file example

<?php
include ("../jpgraph/jpgraph.php");
include ("../jpgraph/jpgraph_bar.php");
include ("../jpgraph/jpgraph_canvas.php");
include ("DBconfig.php");
// Get the parameters
$hname  = $_GET['hname'];
$module = $_GET['module'];
$run    = $_GET['run'];
$ntwk   = $_GET['ntwk'];
$table  = "histogram";
$width = 300;
$totalCells=0;
// Access to database
if (!($Lien = mySql_connect($DBhost, $DBlogin, $DBpwd))){exit();}
// select database
if ( !(mySql_select_db($DBname,$Lien))){exit();}
// Verify if some records exists
$requete = "SELECT * FROM $table WHERE (network=\"$ntwk\")and(run=$run)and(cellCenter>= 0)
        and(histName=\"$hname\")and(module=\"$module\")    ORDER BY idx" ;
$Resultat = mySql_query($requete,$Lien);
$nbr = mySql_num_rows($Resultat);
if ($nbr != 0) 
{ // Get initial planned values while($row = mysql_fetch_object($Resultat)) { $data[] = $row->cellPDF; $totalCells = $totalCells+$row->cellCount; $datax[] = sprintf("%5.2f",$row->cellCenter); } $LabelInterval = floor($nbr/15); if ($LabelInterval == 0) {$LabelInterval=1;} // Create the graph $width=($nbr*6)+65; if ($width < 180) {$width = 180;} $graph = new Graph($width,200,"auto"); $graph->SetScale("textlin"); $graph->img->SetMargin(40,25,40,45); $graph->title->Set($hname); $graph->yaxis->scale->SetGrace(10); $graph->SetShadow(); $graph->ygrid->SetFill(true,'#[email protected]','#[email protected]'); // Create the bar plots $barplot = new BarPlot($data); $barplot->SetFillColor('black'); $barplot->SetWidth(1); $graph->Add($barplot); $graph->xaxis->SetTickLabels($datax); $graph->xaxis->SetLabelAngle(90); $graph->xaxis->SetTextLabelInterval($LabelInterval); $graph->xaxis->SetTitlemargin(18); $graph->xaxis->title->Set("Value"); $graph->xaxis->title->SetFont(FF_FONT1,FS_BOLD); $graph->xaxis->SetFont(FF_FONT0); $graph->yaxis->title->Set("PDF"); $graph->yaxis->SetTitlemargin(25); $graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD); $graph->yaxis->SetFont(FF_FONT1); $graph->title->SetFont(FF_FONT1,FS_BOLD); // Add information about overflow cells (cells not taken into account in the histogram) $requete = "SELECT * FROM $table WHERE (network=\"$ntwk\")and(run=$run)and(cell=-1) and(histName=\"$hname\")and(module=\"$module\") ORDER BY idx" ; $Resultat = mySql_query($requete,$Lien); $nb = mySql_num_rows($Resultat); if ($nb != 0) { $row = mysql_fetch_object($Resultat); if ($row->cellCount != 0) { $ovftxt = sprintf("%3.2f%% Ovf->",100*($row->cellCount/$totalCells)); $graph->tabtitle->Set($ovftxt); $graph->tabtitle->SetPos('right'); } }// Display the graph $graph->Stroke(); } else { // Display text if no value exist for the selected project // Setup a basic canvas $err_msg = new CanvasGraph (180,50, 'auto'); $err_msg->SetMargin(5,11,6 ,11); $err_msg->SetShadow(); $err_msg->SetMarginColor("orange"); // We need to stroke the plotarea and margin before we add the // text since we otherwise would overwrite the text. $err_msg->InitFrame(); // Draw a text box $txt=$hname; $err_txt = new Text($txt,90,27 ); $err_txt->SetFont(FF_FONT2, FS_BOLD); $err_txt->Align('center','center'); $err_txt->ParagraphAlign('center'); // Add a box around the text, white fill, black border and gray shadow $err_txt->SetBox("white","black","gray"); // Stroke the text $err_txt->Stroke($err_msg->img); // Stroke the graph $err_msg->Stroke(); } ?>

In the "include" section you will see the JpGraph files which will be needed to create this graph (jpgraph.php, jpgraph_bar.php and jpgraph_canvas.php)
DBconfig.php contains the variables for hostname,login, password and database name information needed to access to the MySQL database ($DBhost, $DBlogin, $DBpwd, $DBname).

Depending on the parameter passed to this object you will get for example these graphical results. The graph adapt itself to the information it have to display

Result showing a CPU usage distribution

Result showing a queue length distribution

MySQL database table structure

For this example I am using two different tables, one for the simulation log information "simul_log" and another for the histogram information "histogram". The format of the table is shown hereafter. All the tables are of "MyISAM" type.

# Table "simul_log" DDL
CREATE TABLE `simul_log` (
  `Idx` int(11) NOT NULL auto_increment,
  `Network` varchar(255) NOT NULL default '',
  `Run` int(11) NOT NULL default '0',
  `Module` varchar(255) default NULL,
  `Name` varchar(255) default NULL,
  `nValue` double default NULL,
  `sValue` varchar(255) default NULL,
  PRIMARY KEY  (`Idx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
# Table "histogram" DDL
CREATE TABLE `histogram` (
  `idx` int(11) NOT NULL auto_increment,
  `network` varchar(255) NOT NULL default '',
  `run` int(11) NOT NULL default '0',
  `saveDate` date NOT NULL default '0000-00-00',
  `module` varchar(255) NOT NULL default '',
  `histName` varchar(255) NOT NULL default '',
  `cell` int(11) NOT NULL default '0',
  `cellCenter` double NOT NULL default '0',
  `cellWidth` double NOT NULL default '0',
  `cellCount` int(11) NOT NULL default '0',
  `cellPDF` double NOT NULL default '0',
  PRIMARY KEY  (`idx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The "histogram" table is only targeted to store histogram type of information, where "simul_log" is more generic and is able to store the other information. Today I more use it like the "scalar" file by writing values at the end of the simulation in order to be able to compare the results across different runs. If we intend to store information like ".vec" file content then I think we have to create another table.

How to compile your code

Here we are using the MySQL API so we need to have access to it's libaries. In the following description we assume that OMNeT++ is installed under c:\omnetpp and mysql under c:\mysql.

 opp_nmakemake -f -o yourfile.exe -x -IC:/mysql/include -LC:/mysql/lib/opt -llibmysql.lib


Questions, comments

If you have questions or ideas that you want to share, please write to the mailing list.


Patrick Haeflinger