add_tab_to_db.pl

#!/usr/bin/env perl

=head1 NAME

add_tab_to_db.pl - add tab separated file to Database

=head1 SYNOPSIS

    perl add_tab_to_db.pl [OPTION]... 

    -v, --verbose  use verbose mode
    --help         print this help message
    --file         tab separated file
    --action       create: create table
                   select: select from table
                   drop:  drop table
Examples:

    #insert data
    perl add_tab_to_db.pl --file clients_banners.csv
    
    #create table
    perl add_tab_to_db.pl --action create
    
    #select from table
    perl add_tab_to_db.pl --action select

    #drop table
    perl add_tab_to_db.pl --action drop

=head1 DESCRIPTION

This program add tab separated file to table banners

=head1 LICENSE

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.

=head1 AUTHOR

=over 1

=item * Nikolay Mishin (L<MISHIN|https://metacpan.org/author/MISHIN>)

=back

=cut

use strict;
use warnings;
use 5.010;
use utf8;
use open qw/:std :utf8/;

use Getopt::Long qw(:config auto_help);
use Pod::Usage;
use Cwd;
use FindBin '$RealBin';
use Log::Log4perl qw(:easy);
use Text::CSV;
use Data::Dumper;
use DBI;

exit main();

sub main {

    # Argument parsing
    my $verbose = 0;    # frequently referred

    my $log_file = $RealBin . "/add_tab_to_db.log";

    #Init logging
    Log::Log4perl->easy_init(
        {
            level  => $DEBUG,
            file   => ":utf8>>$log_file",
            layout => '%d %p> %m%n'
        }
    );

    my %options = ( 'verbose' => $verbose, );
    GetOptions( \%options, 'verbose', 'file=s', 'action=s' ) or pod2usage(1);
    if ( !exists $options{file} && !exists $options{action} ) {
        if ( defined $ARGV[0] ) {
            $options{file} = $ARGV[0];
        }
        else {
            pod2usage(1);
        }
    }
    my $dbh = make_connect();
    if ( exists $options{file} ) {
        add_file_to_db( \%options, $dbh );
    }
    if ( exists $options{action} ) {
        my $disp = make_dispatch_table();
        if ( defined $disp->{ $options{action} } ) {
            $disp->{ $options{action} }->($dbh);
        }
        else {
            die "$options{action}:no such action!";
        }
    }

    $dbh->disconnect();
    return 0;
}

sub make_dispatch_table {
    my $dispatch = {
        create => \&create_table,
        select => \&select_from_table,
        drop   => \&drop_table
    };
    return $dispatch;
}

sub add_file_to_db {
    my ( $options, $dbh ) = @_;
    my $filename = $options->{file};
    my $tab_data = read_csv($filename);
    add_data_to_banners( $tab_data, $dbh )
      or die "Unable to add data to banners table $!";
    my $msg = qq{read $filename with data } . Dumper($tab_data);
    INFO($msg);
}

sub add_data_to_banners {
    my ( $data, $dbh ) = @_;
    my $insert_handle = $dbh->prepare('INSERT INTO banners VALUES (?,?,?)');

    #  start new transaction #
    $dbh->begin_work();

    foreach my $row (@$data) {
        $insert_handle->execute(@$row);
    }

    #  end the transaction #
    $dbh->commit();

    my $cnt = @$data + 0;
    print "$cnt rows added to table banners\n";
    my $rc = $dbh->disconnect;
    return $rc;
}

sub read_csv {
    my ($file) = @_;
    my $csv = Text::CSV->new(
        {
            binary    => 1,
            auto_diag => 1,
            sep_char  => "\t",
        }
    );
    my @result = ();
    open( my $data, '<:encoding(utf8)', $file )
      or die "Could not open '$file' $!\n";

    while ( my $fields = $csv->getline($data) ) {
        push @result, $fields;
    }
    if ( not $csv->eof ) {
        $csv->error_diag();
    }
    close $data;
    return \@result;

}

sub make_connect {
    my $dbh =
      DBI->connect( "dbi:SQLite:dbname=test.db", "", "", { RaiseError => 1 } )
      or die $DBI::errstr;
    return $dbh;
}

sub create_table {
    my ($dbh) = @_;
    $dbh->do(<<'END_SQL');
create table banners (
     banner_id int unsigned not null primary key,
     title varchar(200),
     url varchar(4000)
)
END_SQL

    print "table banners created\n";
}

sub select_from_table {
    my ($dbh) = @_;
    my $stmt  = qq(SELECT banner_id, title, url from banners;);
    my $sth   = $dbh->prepare($stmt);
    my $rv = $sth->execute() or die $DBI::errstr;
    if ( $rv < 0 ) {
        print $DBI::errstr;
    }
    while ( my @row = $sth->fetchrow_array() ) {
        print "banner_id = " . $row[0] . "\n";
        print "title = " . $row[1] . "\n";
        print "url = " . $row[2] . "\n\n";
    }
    print "Select done successfully\n";

}

sub drop_table {
    my ($dbh) = @_;
    $dbh->do(<<'END_SQL');
drop table banners
END_SQL

    print "table banners dropped\n";
}