#!/usr/bin/perl -w

# db_import.plx
# by Brad Smithart (AKA Impossible Robot)
# OOP version
# $Revision: 1.5 $

# Uses tab-separated data file with header column

use strict;

my $exh = new Data::Reader( filename => 'myfile.txt',
                            _debug   => 1 );

$exh->connect() or croak $exh->error;
print "Columns: ",join(', ', $exh->columns), "\n";

print "# of Cols: ", scalar($exh->columns), "\n";

my $imp = new Data::Import(	reader   => $exh,
							dsn      => 'DBI:mysql:exhibit:localhost',
							username => 'user',
							password => 'pass',
							_debug   => 1 );
$imp->connect() or die $imp->error;

my @values;
while (@values = $exh->row)
{
	$imp->insert(@values);
}

$exh->disconnect();
$imp->disconnect();

exit;

######################################################################
# Classes
######################################################################

package Data::Reader;

use Carp;

sub new
{
	my $pkg = shift;
	bless {
		_error     => 'No error',
		_connected => 0,
		_debug     => 0,
		@_
	}, $pkg;
}

sub connect
{
	my $self = shift;
	my %attr = @_;

	# Connect string params can override constructer params
	for (keys %attr)
	{
		$self->{$_} = $attr{$_};
	}
	print "Data::Reader Connect:\n" if $self->{_debug};
	eval
	{
		# Open file

		open(FILE, $self->{filename})
			or croak "Data::Reader couldn't connect: $!";
		print "    Filename: ", $self->{filename},"\n"
			if $self->{_debug};
		$self->{_handle} = *FILE;

		# Get field names from header
		chomp(my $header = readline($self->{_handle}));
		$header =~ tr/"//d;
		$self->{_fieldnames} = [ split(/\t/, $header) ];
		@{ $self->{_fieldnames} }
			or croak "Data::Reader - No valid header";
		$self->{_connected} = 1;
	};
	if ( $@ )
	{
		$self->{_error} = $@;
		$self->{_connected} = 0;
		return 0;
	}
	return 1;
}

sub disconnect
{
	my $self = shift;
	close(FILE) or return 0;
	$self->{_connected} = 0;
	print "Data::Reader Disconnect.\n" if $self->{_debug};
	return 1;
}

# Accessor methods

sub columns
{
	my $self = shift;
	unless ($self->{_connected})
	{
		carp ("Data::Reader - No connection\n");
		return;
	}
	return @{ $self->{_fieldnames} };
}

sub row
{
	my $self = shift;
	my $row = readline($self->{_handle});
	return unless $row;
	chomp($row);
	$row =~ tr/"//d;
	my @values = split(/\t/, $row);
	return @values;
}

sub connected
{
	my $self =shift;
	$self->{_connected};
}

sub error
{
	my $self = shift;
	$self->{_error};
}

sub filename
{
	my $self = shift;
	if (@_) { $self->{filename} = shift };
	return $self->{filename};
}

######################################################################

package Data::Import;

use DBI;
use Carp;

sub new
{
	my $pkg = shift;
	bless {
		dsn      => "DBI:mysql:database:localhost",
		username => 'root',
		password => '',
		table    => 'exhibit',
		reader   => undef,
		_error   => 'No error',
		_debug   => 0,
		@_
	}, $pkg;
}

sub connect
{
	my $self = shift;
	my %attr = @_;
	for (keys %attr)
	{
		$self->{$_} = $attr{$_};
	}
	print "Import Connect:\n" if $self->{_debug};
	eval
	{
		$self->{dsn} or croak "Data::Import - No DSN";
		my $dsn = $self->{dsn};
		my $username = $self->{username};
		my $password = $self->{password};

 		$self->{_database} = DBI->connect($dsn,
                                          $username,
                                          $password )
                                             or croak $DBI::errstr;

        print "    Connect string: ", $self->{dsn}, ',',
								  $self->{username}, ',',
								  $self->{password}, "\n"
								  		if $self->{_debug};
		$self->{reader} or croak "Data::Import - No Reader";
		my $reader = $self->{reader};
		unless ($reader->{_connected})
		{
			$reader->connect() or croak $exh->error;
		}
		my $insert_fields = join(', ', $reader->columns);
		my $placeholders = join(', ', ('?') x $reader->columns);
		my $table = $self->{table};
		my $query = <<"EOQ";
			INSERT INTO $table ( $insert_fields )
			VALUES ( $placeholders )
EOQ
		$query =~ tr/\r\n//d;
		$query =~ s/\s{2,}/ /g;
		$query =~ s/^\s|\s$/ /g;
		print "    Query: $query\n" if $self->{_debug};
		$self->{_query} = $self->{_database}->prepare($query);
	};
	if ( $@ )
	{
		$self->{_error} = $@;
		return 0;
	}
	return 1;
}

sub disconnect
{
	my $self = shift;
	$self->{_database}->disconnect();
	print "Import Disconnect.\n" if $self->{_debug};
}

sub DESTROY
{
	shift->disconnect();
}

sub insert
{
	my $self = shift;
	$self->{_query}->execute(@_);
	print "Execute: ", join(',', @_), "\n" if $self->{_debug};
}

# Accessor methods
sub dsn
{
	my $self = shift;
	if (@_) { $self->{dsn} = shift };
	return $self->{dsn};
}

sub username
{
	my $self = shift;
	if (@_) { $self->{username} = shift };
	return $self->{username};
}

sub password
{
	my $self = shift;
	if (@_) { $self->{password} = shift };
	return $self->{password};
}

sub table
{
	my $self = shift;
	if (@_) { $self->{table} = shift };
	return $self->{table};
}

sub error
{
	my $self = shift;
	$self->{_error};
}