#!/usr/bin/perl
                                                                                
use warnings;
use strict;
$|++;
                                                                                
                                                                                
use DBI;
use POSIX;
use Time::HiRes qw/gettimeofday/;
use Data::Dumper;
                                                                                
my $db = "test_condDb_perform";
my $cond_name = "COND_dummy";
                                                                                
print "Connect to database...";
my $dbh = DBI->connect("DBI:mysql:host=localhost;db=$db", "", "",
                       {RaiseError=>1, AutoCommit=>1});
print "Done.\n";
                                                                                
# reset the DB
print "Reset database...";
$dbh->do( qq[ DROP DATABASE $db ] );
$dbh->do( qq[ CREATE DATABASE $db ]);
$dbh->do( qq[ USE $db ]);
print "Done.\n";
                                                                                
                                                                                
# define the database
print "Define the database...";
$dbh->begin_work();
my $sql;
$sql = qq[ CREATE TABLE channelView ( logic_id INT PRIMARY KEY )
           TYPE=InnoDB ];
                                                                                
$dbh->do($sql);
                                                                                
$sql = qq[ CREATE TABLE $cond_name ( logic_id INT,
                                     since DATETIME,
                                     till DATETIME,
                                     value FLOAT,
                                     PRIMARY KEY (logic_id, since, till),
                                     INDEX IoV (since, till),
                                     FOREIGN KEY (logic_id) REFERENCES
                                       channelView (logic_id)
                                   ) TYPE=InnoDB ];
$dbh->do($sql);
$dbh->commit();
print "Done.\n";
                                                                                
                                                                                
# fill up the channelView
print "Fill channelView...";
$dbh->begin_work();
$sql = qq[ INSERT INTO channelView SET logic_id = ? ];
my $fill_chview_h = $dbh->prepare_cached($sql);
for (1..10000) {
  $fill_chview_h->execute($_);
}
$dbh->commit();
print "Done.\n";

# insert and write performance
print "Begin insertions...\n\n";
my $iov_seed = time;
my $logic_id = 9999;
                                                                                
my $count=0;
my $MAXCOUNT = 1000;
my $total=0;
my $start = gettimeofday;
my $commit_cnt = 0;
my $commit_size = 100;
                                                                                
$sql = qq[ SELECT logic_id, since, till FROM $cond_name
             WHERE logic_id = ?
             AND ((since >= ? AND since < ?)
             OR (till  >  ? AND till  < ?)
             OR (? >= since AND ? < till))
             LIMIT 1 ];
my $overlap_check_h = $dbh->prepare_cached($sql);
                                                                                
$sql = qq[INSERT INTO $cond_name SET logic_id = ?,
                                     since = ?,
                                     till = ?,
                                     value = ?];
my $insert_h = $dbh->prepare_cached($sql);
                                                                                
$dbh->begin_work();
for (0..10000) {
  my ($t1, $t2) = make_iov();
  $overlap_check_h->execute($logic_id, $t1, $t2, $t1, $t2, $t1, $t2);
  my @overlap = $overlap_check_h->fetchrow_array();
  if (@overlap) { print "OVERLAP!  That's impossible!\n"; }
  else {
    $insert_h->execute($logic_id, $t1, $t2, 1);
    $count++;
    $commit_cnt++;
    if ($commit_cnt == $commit_size) {
      $dbh->commit();
      $dbh->begin_work();
      $commit_cnt = 0;
    }
    if ($count == $MAXCOUNT) { report(); }
  }
}
$dbh->commit();
                                                                                
sub report {
  printf "%9s %19s %9s\n", "[#insert]", "[date time]", "[ins/s]"
    if $total == 0;
  my $now = gettimeofday;
  $total += $count;
  printf "%9d %s %9.3f\n", $total,
    strftime("%Y-%m-%d %H:%M:%S", localtime),
      $count/(($now - $start));
  $count = 0;
  $start = $now;
}
sub make_iov {
  return ( strftime("%Y-%m-%d %H:%M:%S", localtime($iov_seed) ),
           strftime("%Y-%m-%d %H:%M:%S", localtime(++$iov_seed) ) );
}