#!/usr/bin/perl

use DBI;

$DB        = "dbi:mysql:demoOne:silo.cs.indiana.edu:port=16xxx";
$username  = "userxxxx";
$password  = "password";

$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 players_table") || print "There was no need to drop the table first...";

$query = qq{
  create table players_table (
    username   varchar(8) primary key,
    firstName  varchar(20),
    lastName   varchar(20),
    picture    varchar(120),
    address    varchar(120),
    city       varchar(30), 
    state      varchar(2),  
    playerRatePerMatch
               decimal(12, 2)
  )
}; 
   
print " Getting ready to create players table... ";

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

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

open (INPUT, "players.txt"); # or wherever you have the text data 
$line = <INPUT>;
@names = split(/|/, $line);
while ($line = <INPUT>) {  
  $line =~ s/^\|//g;
  $line =~ s/\|$//g;
  if ($line =~ / /) {
    ($uname, $first, $last, $pic, $addr, $city, $st, $rate) = split(/\|/, $line, 8);
    $st =~ s/\s//g;
    chop($rate);   
    $rate =~ s/\$//g; 
    $uname=~ s/\s+//g;  
    $first =~ s/^\s+//g;
    $first =~ s/\s+$//g;
    $last =~ s/^\s+//g; 
    $last =~ s/\s+$//g; 
    $city =~ s/^\s+//g; 
    $city =~ s/\s+$//g; 
    $pic =~ s/\s+//g;   
    $addr =~ s/^\s+//g; 
    $addr =~ s/\s+$//g;

    $query = qq{
      insert into players_table values
        ('$uname',
         '$first',
         '$last', 
         '$pic',  
         '$addr', 
         '$city', 
         '$st',   
         $rate
        )
    };   
    
    print $query, "\n";
    
    $dbh->do($query) || die $dbh->errstr;
    
  } 
    
}
close(INPUT);