#!perl
use strict;
use warnings;
use DBI;
#create db
my $dbfile = 'database.sqlite';
unlink($dbfile);
my $dbh = DBI->connect('dbi:SQLite:dbname='.$dbfile , undef , undef ,
{RaiseError =>1, AutoCommit =>0}) or die $DBI::errstr;
$dbh->do('CREATE TABLE DATA (F1 datetime,F2 INTEGER,F3,F4)');
$dbh->do('CREATE TABLE STARTED (F1 datetime,F2,F3)');
$dbh->commit;
# load data
my $sth = $dbh->prepare('INSERT INTO DATA VALUES (?,?,?,?)');
while (<DATA>){
chomp;
my @f = split ',';
if (@f==4){
$sth->execute(@f);
} else {
print "Input Line $. Skipped : $_\n";
}
}
$dbh->commit;
# select started records
$dbh->do("INSERT INTO STARTED
SELECT F1,F2,F3 FROM DATA WHERE F4 LIKE '%started%'");
$dbh->commit;
# calculate duration from start
my $ar = $dbh->selectall_arrayref("
SELECT D.F1,D.F2,D.F3,D.F4,
(1000*strftime('%s',D.F1))+D.F2 - (1000*strftime('%s',S.F1))-S.F2
FROM DATA AS D
LEFT JOIN STARTED AS S
ON D.F3 = S.F3");
for (@$ar){
printf "@$_\n";
}
$dbh->disconnect;
__DATA__
2014-05-29 10:22:21,880,165ab6a8-e736-11e3-8748-8d365226be24,TP Servic
+e Request
2014-05-29 10:22:21,962,165ab6a8-e736-11e3-8748-8d365226be24,ProcessNa
+me: TC
2014-05-29 10:22:21,965,165ab6a8-e736-11e3-8748-8d365226be24,P5 Starte
+d
2014-05-29 10:22:21,965,165ab6a8-e736-11e3-8748-8d365226be24,ProcessNa
+me: SL
2014-05-29 10:22:21,968,165ab6a8-e736-11e3-8748-8d365226be24,Process M
+essage :PreProcess
2014-05-29 10:22:21,969,165ab6a8-e736-11e3-8748-8d365226be24,Input Mes
+sage to P5
2014-05-29 10:22:22,159,165ab6a8-e736-11e3-8748-8d365226be24,ProcessNa
+me: PP
2014-05-29 10:22:22,170,165ab6a8-e736-11e3-8748-8d365226be24,Process M
+essage :ValidationAndCompliance
2014-05-29 10:22:22,174,165ab6a8-e736-11e3-8748-8d365226be24,Input Mes
+sage to P5
2014-05-29 10:22:58,488,165ab6a8-e736-11e3-8748-8d365226be24,ProcessNa
+me: VC
2014-05-29 10:22:58,493,165ab6a8-e736-11e3-8748-8d365226be24,Process M
+essage :TranslateAndLoad
2014-05-29 10:22:58,493,165ab6a8-e736-11e3-8748-8d365226be24,Input Mes
+sage to P5
2014-05-29 10:23:08,301,165ab6a8-e736-11e3-8748-8d365226be24,ProcessNa
+me: TL
2014-05-29 10:23:08,306,165ab6a8-e736-11e3-8748-8d365226be24,P5 Move r
+equest
2014-05-29 07:12:15,966,1770ebca-e722-11e3-b793-c6903cc19f13,P5 Starte
+d
2014-05-29 07:12:15,966,1770ebca-e722-11e3-b793-c6903cc19f13,ProcessNa
+me: SL
2014-05-29 07:12:16,644,1770ebca-e722-11e3-b793-c6903cc19f13,Process M
+essage :PreProcess
2014-05-29 07:12:16,644,1770ebca-e722-11e3-b793-c6903cc19f13,Input Mes
+sage to P5
2014-05-29 07:12:16,923,1770ebca-e722-11e3-b793-c6903cc19f13,ProcessNa
+me: PP
2014-05-29 07:12:17,730,1770ebca-e722-11e3-b793-c6903cc19f13,Process M
+essage :TranslateAndLoad
2014-05-29 07:12:17,731,1770ebca-e722-11e3-b793-c6903cc19f13,Input Mes
+sage to P5
2014-05-29 07:14:03,187,1770ebca-e722-11e3-b793-c6903cc19f13,ProcessNa
+me: TL
2014-05-29 07:14:04,048,1770ebca-e722-11e3-b793-c6903cc19f13,P5 Move r
+equest