package sqlitedb;
use strict;
use DBI;                        # need this to talk to the db
use Exporter;
use vars qw( @ISA @EXPORT );
@ISA = qw(Exporter);

# tell Exporter which subroutines you want to export
@EXPORT = qw(open_or_create_db closedb);

# sub open_or_create_db
# given a file name, either opens or creates a new sqlite database.
# Returns the DBI db handle
sub open_or_create_db {
  my($file,$dbstruct) = @_;
  $file or die "No file name given to open or create!\n";
  my $dbh;       # this will hold the DBI db handle

  if ( -e $file ) {
    # file exists, so try to open it
    print STDERR "File exists - assuming it's a sqlite db\n";
    $dbh = DBI->connect("dbi:SQLite:dbname=$file", "", "", { AutoCommit => 0 })
                or die "Couldn't open the DB\n";
      #previous line uses method connect from DBI with parameters ending in hash assignment
  } else {
    # no such file - so we need to create it
    # check to make sure a hashref was also sent in
    if ( not defined $dbstruct ) {
      die "To create a db, a hashref of table names => hashref of column names => column types should be given\n";
    }
    print STDERR "No such file exists - creating a new sqlite db\n";
    $dbh = DBI->connect("dbi:SQLite:dbname=$file", "", "", { AutoCommit => 0 })
                or die "Couldn't open the DB\n";

    # go through dbstruct and create all the tables defined inside
    foreach my $tablename ( keys %{ $dbstruct } ) {
      my $createsql = "CREATE table $tablename ( ";
      foreach my $colname ( keys %{ $dbstruct->{$tablename} } ) {
        $createsql .= $colname . " ";
        $createsql .= $dbstruct->{$tablename}->{$colname};
        $createsql .= ",";
      }
      chop($createsql) if ( substr( $createsql, -1 ) eq "," ); #

      $createsql .= ");";
      $dbh->do( $createsql );                       # create the table
    }
    $dbh->commit() if ( not $dbh->{AutoCommit} ); # commit all the
                                                  # table creates - if autocommit is off
  }
	return($dbh);
}

sub closedb {
  my $dbh = shift || die "Need a dbh to close\n";
  # commit anything before closing the handle
  $dbh->commit() if ( not $dbh->{AutoCommit} );
  $dbh->disconnect();     # shut down the connection to the db
  return;
}