#!/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 projectMovies") || print "done.\n No projectMovies table found... ";

$query = qq{ 
  create table projectMovies (
    movieNum   varchar(8) primary key, 
    title      varchar(80)
  )
};   

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

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

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

open (INPUT, "movies.txt"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($movieNum, $title) = split(/,/, $line, 2); 
  $movieNum =~ s/\s//g; 
  $title =~ s/\s//g; 
  if ($movieNum =~ /^\s*$/) { } else { 
    $query = qq{
      insert into projectMovies values 
        ("$movieNum", 
         "$title" 
        )
    }; 

    print $query, "\n"; 

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