#!/opt/perl5/bin/perl

# $Revision: 1.21 $
# 19990430 - Reed White - Modify to work with Informix.

$dbType = "Informix";
$dbServer = "ffdb_soc";
$dbUser = "fastfab1";
$dbPwd = "db4fab22";
$ENV{"INFORMIXDIR"}="/usr/informix"; # Local dir.  Required.

$ENV{"INFORMIXSERVER"}=$dbServer;    # Remote server.  Required.
if (! ($dbName = $ARGV[0])) {
   print"Usage: create_tables.informix _db_name_\n";
   exit(1);
}

print "\n***** Create Database for Perl/Informix Class *****\n\n";

use DBI;
# use DBD::Informix;

$| = 1;

my( $dbh, $rv, $sth );
my( $try ) = 1;

CONNECT:
print "(Try $try) Connecting to database:\n";
print "$dbType, db $dbName\@$dbServer, user $dbUser, pwd $dbPwd ...\n";
eval {$dbh = DBI->connect("DBI:$dbType:$dbName\@$dbServer",$dbUser,$dbPwd);};
if ($@) { die "Could not install driver ($DBI::err): $DBI::errstr\n";}

if (!defined($dbh)) {
   print "Could not connect to db, try $try\n   ($DBI::err): $DBI::errstr\n";
   if ($DBI::errstr =~ /Database not found/) {
      if ($try > 1) { die "Tried once already to create DB\n"; }
      # Connect to DB server.
      print "Now try connecting WITHOUT the database specified...\n";
      $dbh = DBI->connect("DBI:$dbType:\@$dbServer",$dbUser,$dbPwd);
      if (!defined($dbh)) {
         die "Could not connect to server ($DBI::err): $DBI::errstr\n";
      }
      # Create the database.
      print "We have connect!  So, now create $dbName database.\n";
      $rv = $dbh->do(qq{CREATE DATABASE $dbName})
         or warn "CREATE DATABASE $dbName: ", $dbh->errstr, "\n";
      $dbh->disconnect;
      $try++;
      goto CONNECT;
   } else {
      die "Could not connect ($DBI::err): $DBI::errstr\n";
   }
}

print "OK, we have created database $dbName .\n";

print "\nDROPPING TABLES (Errors -206 & -111 are OK.) ...\n\n";

$rv = $dbh->do(q{DROP TABLE title})
      or warn "Drop title table: ", $dbh->errstr, "\n";

$rv = $dbh->do(q{DROP TABLE transact})
      or warn "Drop transact table: ", $dbh->errstr, "\n";

print "CREATING TABLES ...\n\n";

# $rv = $dbh->do(q{CREATE SEQUENCE title_seq START WITH 100})
#      or warn "Create title sequence: ", $dbh->errstr, "\n";

print "Creating table \"title\"...\n";
$rv = $dbh->do(q{
                CREATE TABLE title
                (title_id SERIAL NOT NULL,
                name CHAR(80) NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                edition CHAR(10) NOT NULL)
        }) or warn "Create title table: ", $dbh->errstr, "\n";

print "Creating index on \"title\"...\n";
$rv = $dbh->do(q{
                CREATE UNIQUE INDEX title_idx ON title(title_id)
        }) or warn "Create index on title table: ", $dbh->errstr, "\n";

# $rv = $dbh->do(q{
#                CREATE SEQUENCE transact_seq START WITH 100000
#        }) or warn "Create transact sequence: ", $dbh->errstr, "\n";

print "Creating table \"transact\"...\n";
$rv = $dbh->do(q{
                CREATE TABLE transact
                (transact_id SERIAL NOT NULL,
                title_id INTEGER NOT NULL,
                number_transacted INTEGER NOT NULL,
                cust_name CHAR(40) NOT NULL,
                cust_address CHAR(80) NOT NULL,
                card_type CHAR(10) NOT NULL,
                card_number CHAR(20) )
        }) or warn "Create transact table: ", $dbh->errstr, "\n";

print "Creating index on \"transact\"...\n";
$rv = $dbh->do(q{
                CREATE UNIQUE INDEX transact_idx ON transact(transact_id)
        }) or warn "Create index on transact table: ", $dbh->errstr, "\n";

print "\nLoading title table with:\n";

while(<DATA>) {

        chomp;

        # Get next title id from table.

#        $sth = $dbh->prepare(q{SELECT title_seq.next_val FROM dual});
#        $sth->execute || die "Select of sequence id: ", $dbh->errstr, "\n";
#        ($id) = $sth->fetchrow_array;
#        $sth->finish;

        ($id, $name, $price, $edition) = split('\|', $_);
	print "title_id=$id, name=$name, price=$price, edition=$edition\n";

        $rv = $dbh->do(qq{
                 INSERT INTO title (title_id, name, price, edition)
                 VALUES($id, '$name', $price, '$edition')
                }) or warn "Insert into title table: ", $dbh->errstr, "\n";
}

# Dump data from title table and display.

print "\nWhat actually got loaded into \"title\" table:\n";
$sth = $dbh->prepare(q{SELECT * FROM title});
$sth->execute || die "Select *: ", $dbh->errstr, "\n";
DBI::dump_results($sth, 100);

$sth->finish;
$dbh->disconnect;

print "DONE.\n\n";

exit 0;

#============================= Subroutines ===================================

sub create_db { # This was used for Oracle.  No workie for Informix.
        
        my ($drh) = DBI->install_driver("$dbType");
        my ($rc);
        
	print "$dbType driver installed.  Now creating db $dbName ...\n";
        $rc = $drh->func( "localhost", $dbName, '_CreateDB' );
        print "Return code is $rc\n";
}

__END__
100|Programming Perl|24.99|4th
101|Learning Perl|19.99|7th
102|Diving for Perl|99.99|9th