#!/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};
}
[download]