package DBManager;

=head1 NAME

DBManager -- A database manager object for extropy.

=head1 SYNOPSIS

$boolean = $db_manager->connect;
$db_manager->disconnect;
$db_manager = DBManager->new;
$boolean = get_username($database_name);

Object hash entries:
  database_name  string
  user_name      string
  password       string (not kept in a file)
  connected      boolean
  dbh            int
  reset          boolean (database just created)

=head1 DESCRIPTION

This object accesses the MySQL database.

=head1 VERSION

0.001 (last update:  6/30/04)

=head1 AUTHOR

Chet Langin, clangin@siu.edu

SIU Plant Biotechnology and Genomics Core-facility

=head1 BUGS

None known.

=head1 SEE ALSO

extropy ExtropyConstants ExtropyUtils Extropy::MenuMain

=head1 COPYRIGHT

Copyright 2004, Chet Langin, All Rights Reserved.

This program is free software.  You may copy or
redistribute it under the same terms as Perl itself.

=head1 METHODS

The remainder of this document describes the methods available to the
programmer.

=cut

# load the pragmas
use warnings;
use strict;

# load other modules
use DBI;
use ExtropyConstants;
use ExtropyUtils;

# package variables
my $kbd_input_line = "";
my $extropy_database = "the local MySQL database";

# ********************************  connect  **************************

=head2 connect

  $db_manager->connect

Connects $db_manager to the database.

=cut

# ---------------------------------------------------------------------

sub connect {
  my $self = shift;
  my $database_name = $self->{database_name};
  my $user_name = $self->{user_name};
  my $user_password = $self->{password};

  my $connected = TRUE;
  my $dbh = DBI->connect("dbi:mysql:$database_name",
                         $user_name,
                         $user_password,
                         { PrintError => FALSE }
                        )
    or $connected = FALSE;
  my $db_error = DBI::errstr if(!$connected);
  if($connected) {
    $self->{connected} = TRUE;
    $self->{dbh} = $dbh;
    TRUE;
  } # if
  else {
    # not connected
    message("Could not connect to database $database_name.");
    message("$db_error");
    FALSE;
  } # else 
} # connect

# ********************************  disconnect  ***********************

=head2 disconnect

  $db_manager->disconnect

Disconnects $db_manager from the database.

=cut

# ---------------------------------------------------------------------

sub disconnect {
  my $self = shift;
  my $dbh = $self->{dbh};

  my $error = FALSE;
  $dbh->disconnect
    or $error = TRUE;
  if($error) {
    message_start;
    message("Cannot disconnect from the database");
    my $error_str = $DBI::errstr;
    message("$error_str");
  } # if
  else {
    $self->{connected} = FALSE;
    $self->{dbh} = -1;
  } # else
} # disconnect

# ********************************  execute  **************************

=head2 execute

  $db_manager->execute($command_string)

Executes a database command.  Returns $sth in case the caller needs
to access the results of a select statement.

=cut

# ---------------------------------------------------------------------

sub execute {
  my $self = shift;
  my $command_string = shift;
  my $dbh = $self->{dbh};

  # execute the command
  my $ex_ok = TRUE;
  my $sth = $dbh->prepare("$command_string");
  $sth->execute()
    or $ex_ok = FALSE;
  if(!$ex_ok) {
    my $error = $DBI::errstr;
    if($error !~ /Duplicate entry/) {
      message("Could not execute database command $command_string.");
      message("$error");
    } # if
  } # if

  $sth;

} # execute

# ********************************  get_username($database_name)  *****

=head2 get_username($database_name)

  DBManager->get_username($database_name)

Gets a username and password to access the given database.  If the
database does not exists, it tries to create it.

=cut

# ---------------------------------------------------------------------

sub get_username {
  my $self = shift;
  my $database_name = shift;
  my $connected = TRUE;
  my $user_password = "";
  my $dbh;
  my $db_error = "";

  # Note:  $self->{connected} is not used in this function
  #        because the connection/usage/disconnection is
  #        all done within the function.

  my $loop = TRUE;
  while($loop) {
    message_start();
    message("You must be able to access database $database_name");
    message("on $extropy_database.");
    blank_line;
    my $user_name = prompt("Enter a user name for this database");
    print "\n";
    if($user_name eq "Q") {
      $user_password = "Q";
    } # if
    else {
      $user_password = prompt_password("Enter a password for this user");
      print "\n";
    } # if

    # test the username and password
    if($user_password eq "Q") {
      $connected = FALSE;
    } # if
    else {
      $connected = TRUE;
      $dbh = DBI->connect("dbi:mysql:$database_name",
                           $user_name,
                           $user_password,
                           { PrintError => FALSE }
                          )
        or $connected = FALSE;
      $db_error = DBI::errstr if(!$connected);
      message("Disconnecting from database.");
      $dbh->disconnect if($connected);
    } # else

    if($connected) {
      message("You successfully accessed the database.");
      $loop = FALSE;
      $self->{user_name} = $user_name;
      $self->{password} = $user_password;
    } # if
    else {
      if($user_password eq "Q") {
        $loop = FALSE;
        $connected = FALSE;
      } # if
      else {
        message_start;
        message("Could not connect.");
        message("$db_error");
        if($db_error =~ /Unknown database/) {
          if(yes("Create database named $database_name?")) {
            # sign on to the database manager
            $connected = TRUE;
            $dbh = DBI->connect("dbi:mysql:",
                                 $user_name,
                                 $user_password,
                                 { PrintError => FALSE }
                                )
              or $connected = FALSE;
            if($connected) {

              # create the database
              my $sth = $dbh->prepare("create database $database_name");
              my $ex_ok = TRUE;
              $sth->execute()
                or $ex_ok = FALSE;
              message_start;
              if($ex_ok) {
                message("Database $database_name created.");
                $self->{reset} = TRUE;
              } # if
              else {
                my $error = $DBI::errstr;
                message("Could not create database $database_name");
                message("$error");
                $connected = FALSE;
              } # if

              # use the new database
              if($ex_ok) {
                my $sth = $dbh->prepare("use $database_name");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Using database $database_name.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not use database.");
                  message("$error");
                } # if
              } # if

              # create an mlg table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table mlg(eid int not null primary key auto_increment, mlg varchar(10), cM float, length bigint, index(mlg))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created mlg table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create mlg table.");
                  message("$error");
                } # if
              } # if

              # create a loci table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table loci(eid int not null primary key auto_increment, locus varchar(10), mlg varchar(10), cM float, anchor bigint, comment varchar(20), type varchar(10), index(locus))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created loci table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create loci table.");
                  message("$error");
                } # if
              } # if

              # create a clones table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clones(eid int not null primary key auto_increment, clone varchar(10) unique, type varchar(10), anchors int)");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clones table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clones table.");
                  message("$error");
                } # if
              } # if

              # create a clone2locus table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone2locus(eid int not null primary key auto_increment, clone varchar(10), locus varchar(10), mlg varchar(10))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone2locus table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone2locus table.");
                  message("$error");
                } # if
              } # if

              # create a clone2locus2 table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone2locus2(eid int not null primary key auto_increment, clone varchar(10), locus varchar(10), mlg varchar(10))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone2locus2 table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone2locus2 table.");
                  message("$error");
                } # if
              } # if

              # create a clone2locus3 table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone2locus3(eid int not null primary key auto_increment, clone varchar(10), locus varchar(10), mlg varchar(10))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone2locus3 table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone2locus3 table.");
                  message("$error");
                } # if
              } # if

              # create a clone_anchors table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone_anchors(eid int not null primary key auto_increment, clone varchar(10), mlg varchar(10), anchor bigint, dup_i int, dup_t int)");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone_anchors table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone_anchors table.");
                  message("$error");
                } # if
              } # if

              # create a clone_locations table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone_locations(eid int not null primary key auto_increment, clone varchar(10), mlg varchar(10), start bigint, end bigint, type varchar(10), index(clone))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone_locations table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone_locations table.");
                  message("$error");
                } # if
              } # if

              # create a bad_clone2locus table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table bad_clone2locus(eid int not null primary key auto_increment, clone varchar(10), locus varchar(10), mlg varchar(10))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created bad_clone2locus table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create bad_clone2locus table.");
                  message("$error");
                } # if
              } # if

              # create a clone_loci table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone_loci(eid int not null primary key auto_increment, locus varchar(10) unique, good varchar(5))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone_loci table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone_loci table.");
                  message("$error");
                } # if
              } # if

              # create a clone_loci2 table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table clone_loci2(eid int not null primary key auto_increment, locus varchar(10) unique, good varchar(5))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created clone_loci2 table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create clone_loci2 table.");
                  message("$error");
                } # if
              } # if

              # create a contig2clone table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table contig2clone(eid int not null primary key auto_increment, ctg varchar(10), clone varchar(10), start int, end int, length int, dup_t int, valid varchar(3), index(clone))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created contig2clone table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create contig2clone table.");
                  message("$error");
                } # if
              } # if

              # create a contigs table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table contigs(eid int not null primary key auto_increment, ctg varchar(10) unique, type varchar(10), anchors int, start int, end int, length int, clone_c int, index(ctg))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created contigs table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create contigs table.");
                  message("$error");
                } # if
              } # if

              # create a contig_anchors table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table contig_anchors(eid int not null primary key auto_increment, ctg varchar(10), mlg varchar(10), anchor bigint, start bigint, end bigint, rev_start bigint, rev_end bigint, spread_start bigint, spread_end bigint, dup_i int, dup_t int, flip varchar(3))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created contig_anchors table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create contig_anchors table.");
                  message("$error");
                } # if
              } # if

              # create a contigs_onQ table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table contigs_onQ(eid int not null primary key auto_increment, ctg varchar(10) unique, start bigint, end bigint, index(ctg))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created contigs_onQ table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create contigs_onQ table.");
                  message("$error");
                } # if
              } # if

              # create a qtl table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table qtl(eid int not null primary key auto_increment, qtl varchar(20), mlg varchar(10), cm_start float, cm_end float, start bigint, end bigint, gene varchar(10), index(qtl))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created qtl table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create qtl table.");
                  message("$error");
                } # if
              } # if

              # create a bad_qtl table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table bad_qtl(eid int not null primary key auto_increment, qtl varchar(20), index(qtl))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created bad_qtl table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create bad_qtl table.");
                  message("$error");
                } # if
              } # if

              # create a qtl2locus table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table qtl2locus(eid int not null primary key auto_increment, qtl varchar(20), locus varchar(10), index(qtl), index(locus))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created qtl2locus table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create qtl2locus table.");
                  message("$error");
                } # if
              } # if

              # create an end_matches table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table end_matches(eid int not null primary key auto_increment, ctg1 varchar(10), side1 varchar(5), clone1 varchar(10), bands1 int, ctg2 varchar(10), side2 varchar(5), clone2 varchar(10), bands2 int, bandsm int, score varchar(10), index(ctg1))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created end_matches table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create end_matches table.");
                  message("$error");
                } # if
              } # if

              # create an end_counts table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table end_counts(eid int not null primary key auto_increment, ctg varchar(10), side varchar(5), count int, oscount int, index(ctg, side), index(ctg))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created end_counts table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create end_counts table.");
                  message("$error");
                } # if
              } # if

              # create an mtp table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table mtp(eid int not null primary key auto_increment, clone varchar(10), mtp varchar(10), index(clone))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created mtp table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create mtp table.");
                  message("$error");
                } # if
              } # if

              # create an est table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table est(eid int not null primary key auto_increment, clone varchar(10), est varchar(10), comment varchar(120), index(clone))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created est table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create est table.");
                  message("$error");
                } # if
              } # if

              # create a sequence table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table sequence(eid int not null primary key auto_increment, sequence varchar(10), hit varchar(10), index(hit))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created sequence table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create sequence table.");
                  message("$error");
                } # if
              } # if

              # create a related table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table related(eid int not null primary key auto_increment, related varchar(60), hit varchar(10), mlg varchar(10), start bigint, end bigint, index(hit))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created related table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create sequence table.");
                  message("$error");
                } # if
              } # if

              # create a confirmed table
              if($ex_ok) {
                my $sth = $dbh->prepare("create table confirmed(eid int not null primary key auto_increment, probe varchar(15), probe2 varchar(10), clone varchar(10), mlg varchar(10), cM float, method varchar(80), index(clone))");
                $sth->execute()
                  or $ex_ok = FALSE;
                if($ex_ok) {
                  message("Created confirmed table.");
                } # if
                else {
                  my $error = $DBI::errstr;
                  message("Could not create confirmed table.");
                  message("$error");
                } # if
              } # if

              message("Disconnecting from database.");
              $dbh->disconnect;
            } # if
            else {
              # could not sign on to the database
              message_start;
              message("Could not sign on to create a new database");
              message("$db_error");
            } # else
            $loop = FALSE;
          } # if
          else {
            $connected = FALSE;
            $loop = FALSE;
          } # else
        } # if
        elsif(yes("Try another user name?")) {
          $connected = TRUE;
        } # if
        else {
          $loop = FALSE;
        } # if
      } # if
    } # if
  } # while

  if($connected) {
    $self->{database_name} = $database_name;
    return TRUE;
  } # if
  else {
    return FALSE;
  } # else
} # get_username


# ********************************  new  ******************************

=head2 new

  DBManager->new;

Creates an instance of the DBManager object.

=cut

# ---------------------------------------------------------------------

sub new {
  # instantiate the object
  my $self = {};
  bless $self;

  $self->{database_name} = "";
  $self->{user_name} = "";
  $self->{password} = "";
  $self->{connected} = FALSE;
  $self->{dbh} = -1;
  $self->{reset} = FALSE;

  # return the instance
  $self;
} # new

1