Aaron Brown > perfSONAR_PS-Services-PingER-0.09 > perfSONAR_PS::DB::SQL::PingER

Download:
perfSONAR_PS-Services-PingER-0.09.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.09   Source  

NAME ^

perfSONAR_PS::DB::SQL::PingER - A module that provides data access for PingER databases

DESCRIPTION ^

This module provides access to the relevant DBI wrapped methods given the relevant contact points for the database. It also provides some transparency to the numerous data tables that is used by PingER in order to provide performance.

SYNOPSIS ^

  # create a new database object  
  my $db =  perfSONAR_PS::DB::SQL::PingER->new();
  ## 
  #  inititalize db object
  #
  $db->init( {
   
    driver      => $db_driver,
    database    => $db_name,
    host        => $host,
    port        => $port,
    username    => $username,
    password    => $password,
    });
  
  # connect to DB
  if(   $db->connect()  == 0 ) {
  
     # everything is OK
  } else {
     $logger->logdie( $db->ERRORMSG );
  }
  #
  #  or redefine  some parameters
  #
  if(   $db->connect(
    {
   
    driver      => $db_driver,
    database    => $db_name,
    host        => $host,
    port        => $port,
    username    => $username,
    password    => $password,
    }
  ) == 0 ) {
        #      ......................do something useful with DB ........
  } else {
     $logger->logdie( $db->ERRORMSG );
  }
 
  
         
        #
        # automatically insert   entries into the host table if it does not exist
         if($db->soi_host( {ip_name => 'localhost', ip_number => '127.0.0.1' }) < 0) {
            ### NOT OK
             $logger->logdie( $db->ERRORMSG );
         }  
        if(  $db->soi_host( {ip_name => 'iepm-resp.slac.stanford.edu' } )< 0) {
            ### NOT OK 
             $logger->logdie( $db->ERRORMSG );
         }

        # setup some values for the metadata entry
        my $transport = 'ICMP';
        my $packetSize = '1008';
        my $count = 10;
        my $packetInterval = 1;
        my $ttl = '64';
        
        # get the metaID  for the metadata, again, this will automatically
        # insert the entry into the database if it does not exist  
         
        
        my $metaID  = $db->soi_metadata(  { ip_name_src => $src, ip_name_dst => $dst, 
                                   transport => $transport,  packetSize => $packetSize, 
                                   count => $count, packetINterval => $packetInterval, ttl => $ttl });
        
        #
        #
        
        #  one can  also query for ip_number_src and ip_number_dst - in this case it will query host table
        #  it returns hashref keyd by metaIDs ( see DBI docs about selectall_hashref )
        
        my $metaIDs  = $db->getMetaID(  [ ip_name_src => { like =>  '%fnal.gov'}, 
                                      ip_number_dst => '127.0.0.1']);
        # 
        #  or just ip_number_dst
        # then query is:  
         
        my $metaIDs = $db->getMetaID(   [  ip_number_dst => '134.79.240.30']);
        #
         
        # there is  method insertTable to provide just insert functionality and updateTable for updating one
        
         if( $db->insertTable(  { ip_name_src => $src, ip_name_dst => $dst, 
                                   transport => $transport,  packetSize => $packetSize, 
                                   count => $count, packetINterval => $packetInterval, ttl => $ttl }, 'metaData') < 0) {
           ### NOT OK
           $logger->error( $db->ERRORMSG );                        
        }
        
         
        
        if( $db->updateTable(  {  ip_name_src => $src, ip_name_dst => $dst, 
                                   transport => $transport,  packetSize => $packetSize, 
                                   count => $count, packetINterval => $packetInterval, ttl => $ttl }, 'metaData', [metaID => '3345' ]) < 0 ) {
        
             $logger->error( $db->ERRORMSG );
           
        }
        
         } 
        
        
        # say we have the data we want to insert  
        my $hash = {             table => 'data_200803',
                                'metaID' =>      '3402',
                                'timestamp' => '1000000000', # REQUIRED
                                'minRtt'        => '0.023',
                                'maxRtt'        => '0.030',
                                'meanRtt'       => '0.026',
                                'minIpd'        => '0.0',
                                'maxIpd'        => '0.002',
                                'meanIpd'       => '0.006',
                                'iqrIpd'        => '0.0001',
                                'lossPercent'   => '0.0',
                                'outOfOrder'    => 'true',                                                                      
                                'duplicates'    => 'false',     
        }'
        
        # now,  insert some data into database
        my $data = $db->insertTable(   $hash, 'data_200803' );
        
        # or update some Data
        my $data = $db->updatTable(   $hash  , 'data_200803' , [metaID =>  '3402', 'timestamp' => '1000000000']  );
        
        #
        #
        #  there are 2 helper methods for data insertion and update
        #   they designed for the case when data table name should be found by the timestamp in the $hash or where clause part
        #
        
        # now,  insert some data into database
        my $data = $db->insertData(   $hash );
        
        # or update some Data, the second argument is where clause
        my $data = $db->updateData(   $hash  ,  [metaID =>  '3402', 'timestamp' => '1000000000']  );
        
        
        
        #
        ## also if table name is missed then it will  find it by timestamp
        my $tablename = $db->get_table_for_timestamp({startime => $timestamp});
        #####
        #
        #   query for  data, will return hashref keyd by metaID - timestamp pair
        # 
        #  for example $data_ref->{30034}->{10222223323}->{meanRtt} will give you the meanRtt value for metaID=3--34 and timestamp=10222223323
        #
        my $data_ref = $db->getData( [ metaID => '30034', timestamp => { gt => '1000000'}, timestamp => {lt => '999999999'}] );
        
  } else 
        print "Something went wrong with the database init.";
  }

METHODS ^

soi_host( $param )

'select or insert host': wrapper method to look for the table host for the row with $param = { ip_name => '', ip_number => ''}

returns

   -1 = somethign went wrong 
   everything else is good ( could be 0 or ip_name )

soi_metadata

wrapper method to retrieve the relevant metadata entry given the parameters hashref

        'ip_name_src' => 
         'ip_name_dst' =>
         'ip_number_src' =>    ##  this one will be converted into name by quering host table
          'ip_number_dst' =>   ## this one will be converted into name by quering host table
          
        'transport' = # ICMP, TCP, UDP
        'packetSize' = # packet size of pings in bytes
        'count'         = # number of packets sent
        'packetInterval' = # inter packet time in seconds
        'ttl'           = # time to live of packets

}

returns

    0 = if everything is okay
   -1 = somethign went wrong 

getMetaID

  helper method to get sorted list of metaID for some query
  arguments: query , limit on results

getMeta

  helper method to get hashref keyd by metaID with metadata
  accepts query  and limit arg
  returns metadata as hashref index by metaID

getData

  helper method to get data for some query
  arguments: query , tablename ( if missed then it wil lbe defined from timestamp), limit on results

insertData ( $hashref );

inserts info from the required hashref paremater into the database data table, where $hash = { metaID => 'metaid', # REQUIRED values 'timestamp' => # epoch seconds timestamp of test

        # RTT values
        'minRtt'        => # minimum rtt of ping measurement
        'meanRtt'       => # mean rtt of ping measurement
        'maxRtt'        => # maximum rtt of ping measurement
        
        # IPD
        'minIpd'        => # minimum ipd of ping measurement
        'meanIpd'       => # mean ipd of ping measurement
        'maxIpd'        => # maximum ipd of ping measurement
        
        # LOSS
        'lossPercent' => # percentage of packets lost
        'clp'           => # conditional loss probability of measurement
        
        # JITTER
        'iqrIpd'        => # interquartile range of ipd value of measurement
        'medianRtt'     => # median value of rtts
        
        # OTHER
        'outOfOrder'    => # boolean value of whether any packets arrived out of order
        'duplicates'    => # boolean value of whether any duplicate packets were recvd.

        # LOG
        'rtts'          => [] # array of rtt values of the measurement
        'seqNums'       => [] # array of the order in which sequence numbers are recvd
}

Returns 0 = everything okay -1 = somethign went wrong

}

updateData ( $hashref, $where_clause );

updates info from the required hashref parameter in the database data tables, where $hash = {

        # RTT values
        'minRtt'        => # minimum rtt of ping measurement
        'meanRtt'       => # mean rtt of ping measurement
        'maxRtt'        => # maximum rtt of ping measurement
        
        # IPD
        'minIpd'        => # minimum ipd of ping measurement
        'meanIpd'       => # mean ipd of ping measurement
        'maxIpd'        => # maximum ipd of ping measurement
        
        # LOSS
        'lossPercent' => # percentage of packets lost
        'clp'           => # conditional loss probability of measurement
        
        # JITTER
        'iqrIpd'        => # interquartile range of ipd value of measurement
        'medianRtt'     => # median value of rtts
        
        # OTHER
        'outOfOrder'    => # boolean value of whether any packets arrived out of order
        'duplicates'    => # boolean value of whether any duplicate packets were recvd.

        # LOG
        'rtts'          => [] # array of rtt values of the measurement
        'seqNums'       => [] # array of the order in which sequence numbers are recvd
}

please note than primary key - (timestamp,metaID) is skipped here

and $where_clause is query formatted as Rose::DB::Object query

   usualy it looks as ['timestamp' => { 'eq' => $nowTime } , metaID => 'metaid' ] 
   it will update several tables at once if there is a time range in the $where clause

Returns 0 = everything okay -1 = somethign went wrong

}

get_table_for_timestamp

from the provided timestamps (in epoch seconds), determines the names of the data tables used in PingER. arg: $param - hashref to keys parameters: startTime, endTime, createNewTables

the argument createNewTables defines a boolean for whether tables within the timetange should be created or not if it does not exist in the database. if createNewTables is not set and table does not exist then it wont be returned in the list of tables

If endTime is provided, will assume that a time range is given and will load all necessary tables;

Returns array ref of array refs to tablename => date_formatted or -1 if something failed

getDataTables

  auxiliary   function, 
  accepts single argument - timequery which is hashref  with { gt => | lt =>  | eq => } keys
  get the name of the data table ( data_yyyyMM format ) for specific time period
  returns array ref  of array refs of data_yyyyMM  => yyyyMM 
  or retuns undef if  failed
syntax highlighting: