#!/usr/local/bin/perl -w

# ^NOTE --- I had to change the perl path on sorrel above
#    so this would work!!!

#######################################################
# lab14_mysql_table_setup.pl
#
# quick'n'sleazy table setup for the Week 14 Lab
#    Exercise and HW #12 -
# creates the book reviewer tables for MySQL.
#
# by Sharon Tuttle
#
# last modified: 12-02-04
#######################################################

use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrowser warningsToBrowser carpout);

use DBI;

warningsToBrowser(1);  # activate warnings

# PRINT a form for user to react to

print header, "\n";

print start_html(-title=>'MySQL book review setup'), "\n";

print h1('MySQL book review setup'), "\n";

print start_form, "\n",
      br, br, "\n",

      "MySQL username: \n", 
      textfield(-name=>'mysql_username',
                -size=>15,
                -maxlength=>15), "\n",
      
      br, br, "\n",

      "MySQL password: \n",
      password_field(-name=>'mysql_password',
                     -size=>10,
                     -maxlength=>10), "\n",

      br, br, "\n",

      submit(-value=>'Create/Recreate tables'), "\n",
      endform, "\n";

print hr, "\n";

# if user has hit submit, try to connect to the database and
#    show the specified table's contents

if (param())
{
    # do not seem to need to set any environment variables
    #    for MySQL...

    # try to connect to MySQL database named mysql_username 
    #    in the account mysql_username with the password
    #    mysql_password, on sorrel
    # (and remember --- what you connect, REMEMBER TO DISCONNECT!!!

    my $username = param('mysql_username')
        or die "Must enter a MySQL username!\n";

    my $password = param('mysql_password')
        or die "Must enter a MySQL password!\n";        

    my $db_handle = DBI->connect("dbi:mysql:database=$username;host=www.humboldt.edu",
                    "$username", "$password")
        || die "Database connection not made: $DBI::errstr";

    # try to drop and create the book review scenario
    #    tables

    # this will fail the first time --- we DON'T want
    #    the script to die. BUT you may see warnings.
    $db_handle->do("drop table book");

    $db_handle->do("create table book
                    (book_id           char(5) not null,
                     book_author_lname varchar(20),
                     book_title        varchar(50),
                     book_type         varchar(15),
                     primary key       (book_id))");

    $db_handle->do("drop table reviewer");

    $db_handle->do("create table reviewer
                    (reviewer_id      char(3) not null,
                     reviewer_lname   varchar(20),
                     reviewer_email   varchar(30),
                     primary key     (reviewer_id))");

    $db_handle->do("drop table review");

    $db_handle->do("create table review
                    (review_id         char(4) not null,
                     book_id           char(5),
                     reviewer_id       char(3),
                     review_date       date,
                     review_rating     integer(1),
                     review_text       varchar(30),
                     primary key       (review_id),
                     foreign key       (book_id) references book,
                     foreign key       (reviewer_id) references reviewer)");

    # let's show the names of the tables now in their database
    my $stmt_handle = $db_handle->prepare( 'show tables' )
        or die $db_handle->errstr . "\n";

    $stmt_handle->execute();

    my $next_tbl;

    print hr, "\n";
    while ($next_tbl = $stmt_handle->fetchrow_array())
    {
        print "$next_tbl", br, "\n";
    }

    $stmt_handle->finish;
    print hr, "\n";

    # DON'T FORGET THIS! Not only is it POOR STYLE to do so, but
    #   might cause performance problems if you leave
    #   it off!!!!!!!!!!!!!

    $db_handle->disconnect;

}

print end_html, "\n";

# end of lab12_table_setup.pl