Perl Script to Dump Database

Here we provide a script to dump desirable information from the database.

The script did:

• dump the sender-side table and receiver-side table from one or more OML databases
• count how many records are in each table
• put those number pairs in a file

The comparison of those two numbers could give an indication of the packet loss of OTG/OTR application

The program take database names as input arguments and save the result in "tempres" file. Basically,the script read database_name from command-line and process it one by one. First, the restuls are selected from database and put into two files "tx_table", "rx_table". If you want to omit the awk part, you can comment out them and just look into the two tables mentioned above.

#!/usr/bin/perl

# this script read one or more databases and extract how many packets are sent and received respectively.
#

use Mysql;

$hostname = 'idb1.orbit-lab.org';
$user     = 'orbit';
$database =  @ARGV;
$password = 'orbit';
$resultfile ='tempres';


$table1 = "tx_table";
$table2 = "rx_table";

$driver   = 'mysql';

my $noiselevel = 18;
my $now = localtime time;
open(res, ">>$resultfile")
                || die "Can't open result file: $!\n";
print res "$now test results are \n";
close res;

foreach(@ARGV)
{
   dumpDB($_,$table1,$table2);
   system("awk 'BEGIN {cnt=0} {cnt =cnt+1} END {printf(\"%d \", cnt) >> \"$resultfile\" }' $table2");
   system("awk 'BEGIN {cnt=0} {cnt =cnt+1} END {printf(\"%d \\n\", cnt) >> \"$resultfile\" }' $table1");
}

print "Done. check tempres file! \n";

#we need a subfunction to read 1 database 2 table and dump to two files
sub dumpDB
{
  my ($dbname,$f1, $f2) = @_;
  open (OUTFILE1, "> $f1")
         || die "Can't open file to save temporary TX data $!\n";
  open (OUTFILE2, "> $f2")
         || die "Can't open file to save temporary RX data $!\n";
 my $dbh = Mysql->connect($hostname, $dbname, $user, $password);
 my $sql_query1="SELECT sequence_no, pkt_seqno, pkt_size_sample_sum, tx_timestamp from sender_otg_senderport";
 my $sql_query2="SELECT sequence_no, pkt_seqno, rcvd_pkt_size_sample_sum, xmitrate, rx_timestamp from receiver_otr_receiverport";
  my $sth1 = $dbh->query($sql_query1);
  while(my @record1 = $sth1->FetchRow) {
      print OUTFILE1 "@record1 \n";
  }

  my $sth2 = $dbh->query($sql_query2);
  while(my @record2 = $sth2->FetchRow) {
    print OUTFILE2 "@record2 \n";
   }
  close OUTFILE1;
  close OUTFILE2;
}
                                    

Anoter example of post-processing database can be found in wiki:Tutorial/AnalyzeResults