#!/usr/bin/perl -w

use DBI ;
use strict;

#connect to Access file via ODBC

my $accessDSN = q(driver=Microsoft Access Driver (*.mdb);).
		q(dbq=D:\\Course 1 Case Study Files\\Order Entry System.mdb);
my $dbhA = DBI->connect("dbi:ODBC:$accessDSN",'','') or die "$DBI::errstr\n"; 


#prepare handles for each table. figure out how to do this w/o hardcoded names
my @tables = qw(Customers OrderLineItems Orders Products); 
my %tblSth = map {getHandle($_,$dbhA)} @tables;	
#return_data(%tblSth);
my $oPrepare = create_tables(%tblSth);
#insert_data();



sub getHandle{
	my ($table,$dHandle) = @_;
	return $table,$dHandle->prepare("select * from $table");
}

sub create_tables{
my %tblSth = @_;
my %typemap = ( 12=>"VARCHAR", 2=>"NUMBER", -6=>"NUMBER", 11=>"DATE", 4=>"NUMBER");
my $oPrepare ="SET echo on;\n\n";
  while( my ($table,$handle) = each %tblSth){
	$handle->execute or die "$dbhA->errstr";
  	my $index = (keys %{$handle->fetchrow_hashref("NAME")});
	$oPrepare .=	"DROP TABLE $table;\n".
	 	"CREATE TABLE $table (";
  	for my $col(0..$index){
		my($column,$type,$size,$digits,$null) =  $handle->func($col,"DescribeCol");
		if (defined $column){
			if ($digits>0) {$size .= ",$digits"};
			$oPrepare .= $column. " ".$typemap{$type};
			if ($type != 11){$oPrepare .= "(".$size.")"};
			if ($col < $index){ $oPrepare .= ","}
		}
		$oPrepare .= "\n";
	}
	$oPrepare .= ");\n\n";
   }
   $oPrepare .= "/";	
   return $oPrepare;
}