package Text::CSV::Auto::ExportTo::MySQL;
BEGIN {
$Text::CSV::Auto::ExportTo::MySQL::VERSION = '0.02';
}
use Moose;
=head1 NAME
Text::CSV::Auto::ExportTo::MySQL - Export a CSV file to MySQL.
=head1 SYNOPSIS
use Text::CSV::Auto;
use Text::CSV::Auto::ExportTo::MySQL;
my $auto = Text::CSV::Auto->new('path/to/file.csv');
my $exporter = Text::CSV::Auto::ExportTo::MySQL(
auto => $auto,
connection => $dbh,
);
Or a simpler interface can be used:
$auto->export_to_mysql(
connection => $dbh,
);
=head1 DESCRIPTION
This module provides the ability to export a CSV file straight in to MySQL
without much fuss.
Note that if the table already exists it will be dropped.
=head1 ATTRIBUTES
=head2 auto
The L<Text::CSV::Auto> instance to copy headers and rows from. Required.
=cut
with 'Text::CSV::Auto::ExportTo';
=head2 connection
Can be either a L<DBIx::Connector> (recommended) or a DBI handle. DBIx::Connector
is recommended as it provides a robust connection and transation management layer
on top of DBI. Required.
=head2 table
The table name to export to. Defaults to a nicely formatted version of the
CSV's file name.
=head2 method
If using DBIx::Connector for the connection then this states what method to use
such as "run", "txn", or "svp". Defaults to "svp" which degrades well on
non-transactional databases.
=head2 mode
If using DBIx::Connector for the connection this this dictates what connection
mode to use such as "ping", "fixup", and "no_ping". The default is "fixup".
=cut
with 'Text::CSV::Auto::ExportToDB';
=head2 create_sql
Returns the SQL that will be used to CREATE the table.
=cut
has 'create_sql' => (
is => 'ro',
isa => 'Str',
lazy_build => 1,
);
sub _build_create_sql {
my ($self) = @_;
my $columns = $self->auto->analyze();
my @columns_sql;
foreach my $column (@$columns) {
my $sql = $column->{header} . ' ';
if ($column->{data_type} eq 'string') {
$sql .= 'VARCHAR(' . ($column->{string_length} || 1) . ') NOT NULL';
}
elsif ($column->{data_type} eq 'decimal') {
$sql .= sprintf(
'DECIMAL( %d, %d ) NOT NULL',
$column->{integer_length} + $column->{fractional_length},
$column->{fractional_length},
);
}
elsif ($column->{data_type} eq 'integer') {
my $type;
if ($column->{signed}) {
$type = 'BIGINT';
$type = 'INT' if $column->{max} <= 2147483647 and $column->{min} >= -2147483648;
$type = 'MEDIUMINT' if $column->{max} <= 8388607 and $column->{min} >= -8388608;
$type = 'SMALLINT' if $column->{max} <= 32767 and $column->{min} >= -32768;
$type = 'TINYINT' if $column->{max} <= 127 and $column->{min} >= -128;
}
else {
$type = 'BIGINT';
$type = 'INT' if $column->{max} <= 4294967295;
$type = 'MEDIUMINT' if $column->{max} <= 16777215;
$type = 'SMALLINT' if $column->{max} <= 65535;
$type = 'TINYINT' if $column->{max} <= 255;
}
$sql .= sprintf(
'%s %s NOT NULL',
$type,
($column->{signed} ? 'SIGNED' : 'UNSIGNED'),
);
}
elsif ($column->{data_type} eq 'mdy_date' or $column->{data_type} eq 'ymd_date') {
$sql .= 'DATE NOT NULL';
}
push @columns_sql, $sql;
}
return sprintf(
'CREATE TABLE %s (%s)',
$self->table(),
join(', ', @columns_sql),
);
}
=head1 METHODS
=head2 export
$exporter->export();
Exports the CSV data to MySQL.
=cut
sub export {
my ($self) = @_;
my $create_sql = $self->create_sql();
my $table = $self->table();
my $headers = $self->auto->headers();
my $auto = $self->auto();
my $columns = $self->auto->analyze();
$self->_run(sub{
my ($dbh) = @_;
$dbh->do('DROP TABLE IF EXISTS ' . $table );
$dbh->do( $create_sql );
my $sth = $dbh->prepare( sprintf(
'INSERT INTO %s (%s) VALUES (%s)',
$table,
join(',', @$headers),
join(',', map {'?'} @$headers ),
) );
$auto->_raw_process(sub{
my ($row) = @_;
my $i = 0;
foreach my $column (@$columns) {
if ($column->{data_type} eq 'mdy_date') {
$row->[$i] = sprintf('%04d-%02d-%02d', (split(/\//, $row->[$i]))[2,0,1] );
}
$i ++;
}
$sth->execute( @$row );
}, 1);
});
}
__PACKAGE__->meta->make_immutable();
1;
__END__
=head1 AUTHOR
Aran Clary Deltac <bluefeet@gmail.com>
=head1 LICENSE
This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.