#!/usr/bin/perl

use DBI; 

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16248"; 
$username  = "dgerman";             
$password  = "sp00n"; 

$dbh = DBI->connect($DB, 
                    $username, 
                    $password, {PrintError => 0}) || 
       die "Couldn't open database: ", $DBI::errstr; 

print "I have opened the database...\n"; 

$dbh->do("drop table projectUsers") || print "There was no need to drop the table first...";

$query = qq{ 
  create table projectUsers (
    username   varchar(8) primary key, 
    lastName   varchar(20), 
    firstName  varchar(20) 
  )
};   

print " Getting ready to create the projectUsers table... "; 

$dbh->do($query) || die $dbh->errstr;

print "done.\n Getting ready to populate the table with data. \n"; 

open (INPUT, "users.txt"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($uname, $last, $first) = split(/,/, $line, 3); 
  $uname =~ s/\s//g; 
  $last =~ s/\s//g; 
  $first =~ s/\s//g; 
  $query = qq{
    insert into projectUsers values 
      ('$uname', 
       '$last', 
       '$first' 
      )
  }; 

  print $query, "\n"; 

  $dbh->do($query) || die $dbh->errstr; 

}
close(INPUT);