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 resultsgetMeta
helper method to get hashref keyd by metaID with metadata
accepts query and limit arg
returns metadata as hashref index by metaIDgetData
helper method to get data for some query
arguments: query , tablename ( if missed then it wil lbe defined from timestamp), limit on resultsinsertData ( $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 clauseReturns 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