#!/usr/bin/perl -w
# NAME: test-DBI-sqlite.pl
# AIM:
use strict;
use warnings;
use File::Basename;  # split path ($name,$dir,$ext) = fileparse($file [, qr/\.[^.]*/] )
use Cwd;
use DBI;
use Time::HiRes qw(gettimeofday tv_interval);       # provide more accurate timings

my $begin = [ gettimeofday ];
my $os = $^O;
my $perl_dir = '/home/geoff/bin';
my $PATH_SEP = '/';
my $temp_dir = '/tmp';
if ($os =~ /win/i) {
    $perl_dir = 'C:\GTools\perl';
    $temp_dir = $perl_dir;
    $PATH_SEP = "\\";
}
unshift(@INC, $perl_dir);
require 'lib_utils.pl' or die "Unable to load 'lib_utils.pl' Check paths in \@INC...\n";
# log file stuff
our ($LF);
my $pgmname = $0;
if ($pgmname =~ /(\\|\/)/) {
    my @tmpsp = split(/(\\|\/)/,$pgmname);
    $pgmname = $tmpsp[-1];
}
my $outfile = $temp_dir.$PATH_SEP."temp.$pgmname.txt";
open_log($outfile);

# user variables
my $VERS = "0.0.5 2015-01-09";
my $load_log = 0;
my $in_file = '';
my $verbosity = 0;
my $out_file = '';

# ### DEBUG ###
my $debug_on = 0;
my $def_file = 'def_file';

### program variables
my @warnings = ();
my $cwd = cwd();
my $msd_elap = 0;

sub VERB1() { return $verbosity >= 1; }
sub VERB2() { return $verbosity >= 2; }
sub VERB5() { return $verbosity >= 5; }
sub VERB9() { return $verbosity >= 9; }

sub show_warnings($) {
    my ($val) = @_;
    if (@warnings) {
        prt( "\nGot ".scalar @warnings." WARNINGS...\n" );
        foreach my $itm (@warnings) {
           prt("$itm\n");
        }
        prt("\n");
    } else {
        prt( "\nNo warnings issued.\n\n" ) if (VERB9());
    }
}

sub pgm_exit($$) {
    my ($val,$msg) = @_;
    if (length($msg)) {
        $msg .= "\n" if (!($msg =~ /\n$/));
        prt($msg);
    }
    show_warnings($val);
    close_log($outfile,$load_log);
    exit($val);
}


sub prtw($) {
   my ($tx) = shift;
   $tx =~ s/\n$//;
   prt("$tx\n");
   push(@warnings,$tx);
}

sub process_in_file($) {
    my ($inf) = @_;
    if (! open INF, "<$inf") {
        pgm_exit(1,"ERROR: Unable to open file [$inf]\n"); 
    }
    my @lines = <INF>;
    close INF;
    my $lncnt = scalar @lines;
    prt("Processing $lncnt lines, from [$inf]...\n");
    my ($line,$inc,$lnn);
    $lnn = 0;
    foreach $line (@lines) {
        chomp $line;
        $lnn++;
        if ($line =~ /\s*#\s*include\s+(.+)$/) {
            $inc = $1;
            prt("$lnn: $inc\n");
        }
    }
}

sub get_time_stg($) {
    my $elap = shift;
    my $negative = 0;
    my $units = '';
    if ($elap < 0) {
        $negative = 1;
        $elap = -$elap;
    }
    if ( !($elap > 0.0) ) {
        return "0.0 s";
    }
    if ($elap < 1e-21) {
        #// yocto - 10^-24
        $elap *= 1e+21;
        $units = "ys";
    } elsif ($elap < 1e-18) {
        #// zepto - 10^-21
        $elap *= 1e+18;
        $units = "zs";
    } elsif ($elap < 1e-15) {
        #// atto - 10^-18
        $elap *= 1e+15;
        $units = "as";
    } elsif ($elap < 1e-12) {
        #// femto - 10^-15
        $elap *= 1e+12;
        $units = "fs";
    } elsif ($elap < 1e-9) {
        #// pico - 10^-12
        $elap *= 1e+9;
        $units = "ps";
    } elsif ($elap < 1e-6) {
        #// nanosecond - one thousand millionth (10?9) of a second
        $elap *= 1e+6;
        $units = "ns";
    } elsif ($elap < 1e-3) {
        #// microsecond - one millionth (10?6) of a second
        $elap *= 1e+3;
        $units = "us";
    } elsif ($elap < 1.0) {
        #// millisecond
        $elap *= 1000.0;
        $units = "ms";
    } elsif ($elap < 60.0) {
        $units = "s";
    } else {
        my $secs = int($elap + 0.5);
        my $mins = int($secs / 60);
        $secs = ($secs % 60);
        if ($mins >= 60) {
            my $hrs = int($mins / 60);
            $mins = $mins % 60;
            if ($hrs >= 24) {
                my $days = int($hrs / 24);
                $hrs = $hrs % 24;
                return sprintf("%d days %2d:%02d:%02d hh:mm:ss", $days, $hrs, $mins, $secs);
            } else {
                return sprintf("%2d:%02d:%02d hh:mm:ss", $hrs, $mins, $secs);
            }
        } else {
            return sprintf("%2d:%02d mm:ss", $mins, $secs);
        }
    }
    my $res = '';
    if ($negative) {
        $res = '-';
    }
    $res .= "$elap $units";
    return $res;
}


# just some quick test, in an existing DIRECTORY
sub test_dbi_sqlite() {
    my ($cnt,$stt,$end,$elap,$rcnt);
    my $dbdir = 'C:\GTools\perl\temp-dbi'; 
    if (! -d $dbdir) {
        pgm_exit(1,"Error: dir $dbdir does NOT exit!\n".
            "Either create it, and re-run script, or change the script!\n");
    }

    my $dbf = $dbdir.'\dbi.sqlite.1';

    # my $dbh = DBI->connect("dbi:SQLite:dbname=dbfile","","");
    # my $dsn = "DBI:SQLite:dbname=$option{'navdata-cache'}";
    # my $attrs = { ReadOnly => 1,
    #          PrintError => 0,
    #          RaiseError => 1 };
    #my $dbh = DBI->connect($dsn, '', '', $attrs);
    my $attrs = { PrintError => 0,
        RaiseError => 1 };

    my $dbh = DBI->connect("dbi:SQLite:dbname=$dbf","","",$attrs)
        or die $DBI::errstr;

    my $sth = $dbh->prepare("SELECT SQLITE_VERSION()");
    $sth->execute();
    my $ver = $sth->fetch();
    prt("Perl DBI Sqlite version: @$ver\n");
    $sth->finish();

    $stt = [ gettimeofday ];
    prt("Adding 'Cars' table...\n");

    $dbh->do("DROP TABLE IF EXISTS Cars");
    $dbh->do("CREATE TABLE Cars(Id INT PRIMARY KEY, Name TEXT, Price INT)");
    $dbh->do("INSERT INTO Cars VALUES(1,'Audi',52642)");
    $dbh->do("INSERT INTO Cars VALUES(2,'Mercedes',57127)");
    $dbh->do("INSERT INTO Cars VALUES(3,'Skoda',9000)");
    $dbh->do("INSERT INTO Cars VALUES(4,'Volvo',29000)");
    $dbh->do("INSERT INTO Cars VALUES(5,'Bentley',350000)");
    $dbh->do("INSERT INTO Cars VALUES(6,'Citroen',21000)");
    $dbh->do("INSERT INTO Cars VALUES(7,'Hummer',41400)");
    $dbh->do("INSERT INTO Cars VALUES(8,'Volkswagen',21600)");
    $cnt = 9;

    $end = [ gettimeofday ];
    $elap = tv_interval( $stt, $end );
    $msd_elap += $elap;
    prt("ELAP: Inserted $cnt records in $elap secs ...\n");

    $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id=1" );  
    $sth->execute();
    my ($pid, $name, $price) = $sth->fetchrow();
    prt("$pid $name $price\n");

    my $fields = $sth->{NUM_OF_FIELDS};
    prt("We have selected $fields field(s)\n");

    my $rows = $sth->rows();
    prt("We have selected $rows row(s)\n");

    $sth->finish();

    $sth = $dbh->prepare( "SELECT * FROM Cars WHERE Id > 1" );  
    $sth->execute();
    ### $rows = $sth->rows();
    ### print "We have selected $rows row(s)\n";
    $cnt = 0;
    while (($pid, $name, $price) = $sth->fetchrow()) {
        $cnt++;
        prt("$cnt: $pid $name $price\n");
    }

    $sth->finish();

    prt("Adding 'people' table...\n");
    $stt = [ gettimeofday ];

    $dbh->do("DROP TABLE IF EXISTS people");
    my $sql = <<'END_SQL';
CREATE TABLE people (
  id       INTEGER PRIMARY KEY,
  fname    VARCHAR(100),
  lname    VARCHAR(100),
  email    VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(20)
)
END_SQL
    # add TABLE 
    $dbh->do($sql);
    # and ENTRY
    my $fname = 'Foo';
    my $lname = 'Bar',
    my $email = 'foo@bar.com';
    $dbh->do('INSERT INTO people (fname, lname, email) VALUES (?, ?, ?)',
        undef,
        $fname, $lname, $email);
    # update entry
    my $password = 'hush hush';
    my $id = 1;
 
    $dbh->do('UPDATE people SET password = ? WHERE id = ?',
        undef,
        $password,
        $id);

    # add some records
    my ($i);
    for ($i = 0; $i < 20; $i++) {
        $dbh->do('INSERT INTO people (fname, lname, email, password) VALUES (?, ?, ?, ?)',
            undef,
            $fname.$i, $lname.$i, $email.$i, $password.$i);
    }

    $end = [ gettimeofday ];
    $elap = tv_interval( $stt, $end );
    $msd_elap += $elap;
    ## prt("Inserted $cnt records in $elap secs ...\n");
    prt("ELAP: Added 20 'people' in $elap secs... get some back...\n");

    # fetch the amended row
    $sth = $dbh->prepare( "SELECT * FROM people WHERE Id=$id" );
    $sth->execute();

    my ($f1,$f2,$f3,$f4,$f5) = $sth->fetchrow();
    prt("WHERE Id=$id: $f1,$f2,$f3,$f4,$f5\n");

    $fields = $sth->{NUM_OF_FIELDS};
    prt("We have selected $fields field(s)\n");

    $rows = $sth->rows();
    prt("We have selected $rows row(s)\n");

    $sth->finish();

    $stt = [ gettimeofday ];
    # show selected records - by variable params sql - ie the '?'
    $sql = 'SELECT fname, lname FROM people WHERE id >= ? AND id < ?';
    $sth = $dbh->prepare($sql);
    $sth->execute(3, 10);
    $cnt = 0;
    while (my @row = $sth->fetchrow_array) {
        $cnt++;
        prt( "$cnt: fname: $row[0]  lname: $row[1]\n" );
    }
    $end = [ gettimeofday ];
    $elap = tv_interval( $stt, $end );
    $msd_elap += $elap;
    prt("ELAP: Found $cnt in Id >= 3 and Id < 10, in $elap secs\n");

    $stt = [ gettimeofday ];
    $sth->execute(12, 15);
    $cnt = 0;
    while (my $row = $sth->fetchrow_hashref) {
        $cnt++;
        prt("$cnt: fname: $row->{fname}  lname: $row->{lname}\n");
    }
    $end = [ gettimeofday ];
    $elap = tv_interval( $stt, $end );
    $msd_elap += $elap;
    prt("ELAP: Found $cnt in Id >= 12 and Id < 15, in $elap secs\n");
    #######################################


    ################################################################
    # end it all
    $dbh->disconnect();
    ################################################################
}


#########################################
### MAIN ###
##parse_args(@ARGV);
##process_in_file($in_file);
test_dbi_sqlite();
my $end = [ gettimeofday ];
#my $interval = tv_interval( \@begin, \@end );
my $interval = tv_interval( $begin, $end );
my $un_msd = $interval - $msd_elap; 
prt("Ran for $interval seconds ... ".get_time_stg($un_msd)." outside measuring...\n");
pgm_exit(0,"");
########################################

sub need_arg {
    my ($arg,@av) = @_;
    pgm_exit(1,"ERROR: [$arg] must have a following argument!\n") if (!@av);
}

sub parse_args {
    my (@av) = @_;
    my ($arg,$sarg);
    my $verb = VERB2();
    while (@av) {
        $arg = $av[0];
        if ($arg =~ /^-/) {
            $sarg = substr($arg,1);
            $sarg = substr($sarg,1) while ($sarg =~ /^-/);
            if (($sarg =~ /^h/i)||($sarg eq '?')) {
                give_help();
                pgm_exit(0,"Help exit(0)");
            } elsif ($sarg =~ /^v/) {
                if ($sarg =~ /^v.*(\d+)$/) {
                    $verbosity = $1;
                } else {
                    while ($sarg =~ /^v/) {
                        $verbosity++;
                        $sarg = substr($sarg,1);
                    }
                }
                $verb = VERB2();
                prt("Verbosity = $verbosity\n") if ($verb);
            } elsif ($sarg =~ /^l/) {
                if ($sarg =~ /^ll/) {
                    $load_log = 2;
                } else {
                    $load_log = 1;
                }
                prt("Set to load log at end. ($load_log)\n") if ($verb);
            } elsif ($sarg =~ /^o/) {
                need_arg(@av);
                shift @av;
                $sarg = $av[0];
                $out_file = $sarg;
                prt("Set out file to [$out_file].\n") if ($verb);
            } else {
                pgm_exit(1,"ERROR: Invalid argument [$arg]! Try -?\n");
            }
        } else {
            $in_file = $arg;
            prt("Set input to [$in_file]\n") if ($verb);
        }
        shift @av;
    }

    if ($debug_on) {
        prtw("WARNING: DEBUG is ON!\n");
        if (length($in_file) ==  0) {
            $in_file = $def_file;
            prt("Set DEFAULT input to [$in_file]\n");
        }
    }
    if (length($in_file) ==  0) {
        pgm_exit(1,"ERROR: No input files found in command!\n");
    }
    if (! -f $in_file) {
        pgm_exit(1,"ERROR: Unable to find in file [$in_file]! Check name, location...\n");
    }
}

sub give_help {
    prt("$pgmname: version $VERS\n");
    prt("Usage: $pgmname [options] in-file\n");
    prt("Options:\n");
    prt(" --help  (-h or -?) = This help, and exit 0.\n");
    prt(" --verb[n]     (-v) = Bump [or set] verbosity. def=$verbosity\n");
    prt(" --load        (-l) = Load LOG at end. ($outfile)\n");
    prt(" --out <file>  (-o) = Write output to this file.\n");
}

# eof - template.pl