#!/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 projectRatings") || print "done. No previous ratings file found...\n";

$query = qq{ 
  create table projectRatings (
    movieNum   varchar(8), 
    username   varchar(8), 
    comment    varchar(290), 
    rating     int, 
    primary key (movieNum, username)
  )
};   

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

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

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

open (INPUT, "ratings.txt"); 
$line = <INPUT>; 
@names = split(/,/, $line); 
while ($line = <INPUT>) {
  ($username, $movieNum, $rating) = split(/,/, $line, 3); 
  $movieNum =~ s/\s//g; 
  $username =~ s/\s//g; 
  $rating =~ s/\s//g; 
  if ($movieNum =~ /^\s*$/) { } else { 
    $query = qq{
      insert into projectRatings (movieNum, username, rating) values 
        ('$movieNum', 
         '$username', 
         '$rating' 
        )
    }; 

    print $query, "\n"; 

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