File Coverage

blib/lib/Fsdb/Filter/sqlselect_to_db.pm

Criterion	Covered	Total	%
statement	18	70	25.7
branch	0	14	0.0
condition			n/a
subroutine	6	15	40.0
pod	5	5	100.0
total	29	104	27.8

#!/usr/bin/perl

#
# sql_to_db.pm
# Copyright (C) 2014 by John Heidemann
# $Id: dd1b62c899ec2f2f31abaa9131d4f053b42c038e $
#
# This program is distributed under terms of the GNU general
# public license, version 2.  See the file COPYING
# in $dblib for details.
#

package Fsdb::Filter::sqlselect_to_db;

=head1 NAME

sqlselect_to_db - convert MySQL or MariaDB selected tables to fsdb

=head1 SYNOPSIS

    sqlselect_to_db dest.fsdb

=head1 DESCRIPTION

Converts a MySQL or MariaDB tables to Fsdb format.

The input is I fsdb.
The first non-box row is taken to be the names of the columns.

The output is two-space-separated fsdb.
(Someday more general field separators should be supported.)

=for comment
begin_standard_fsdb_options

This module also supports the standard fsdb options:

=over 4

=item B<-d>

Enable debugging output.

=item B<-i> or B<--input> InputSource

Read from InputSource, typically a file name, or C<-> for standard input,
or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.

=item B<-o> or B<--output> OutputDestination

Write to OutputDestination, typically a file name, or C<-> for standard output,
or (if in Perl) a IO::Handle, Fsdb::IO or Fsdb::BoundedQueue objects.

=item B<--autorun> or B<--noautorun>

By default, programs process automatically,
but Fsdb::Filter objects in Perl do not run until you invoke
the run() method.
The C<--(no)autorun> option controls that behavior within Perl.

=item B<--help>

Show help.

=item B<--man>

Show full manual.

=back

=for comment
end_standard_fsdb_options


=head1 SAMPLE USAGE

=head2 Input:

    +----------------+---------------+--------------------+------+-------------------------+
    | username       | firstname     | lastname           | id   | email                   |
    +----------------+---------------+--------------------+------+-------------------------+
    | johnh          | John          | Heidemann          |  134 | [e-mail]           |
    +----------------+---------------+--------------------+------+-------------------------+
    1 row in set (0.01 sec)

=head2 Command:

    sqlselect_to_db

=head2 Output:

    #fsdb -F S username firstname lastname id email
    johnh  John  Heidemann  134  [e-mail]
    #   | sqlselect_to_db


=head1 SEE ALSO

L.
L.


=head1 CLASS FUNCTIONS

=cut

@ISA = qw(Fsdb::Filter);
$VERSION = 2.0;

use strict;
use Pod::Usage;
use Carp;

use Text::CSV_XS;

use Fsdb::Filter;
use Fsdb::IO::Writer;


=head2 new

    $filter = new Fsdb::Filter::csv_to_db(@arguments);

Create a new csv_to_db object, taking command-line arguments.

=cut

sub new ($@) {
    my $class = shift @_;
    my $self = $class->SUPER::new(@_);
    bless $self, $class;
    $self->set_defaults;
    $self->parse_options(@_);
    $self->SUPER::post_new();
    return $self;
}


=head2 set_defaults

    $filter->set_defaults();

Internal: set up defaults.

=cut

sub set_defaults ($) {
    my($self) = @_;
    $self->SUPER::set_defaults();
}

=head2 parse_options

    $filter->parse_options(@ARGV);

Internal: parse command-line arguments.

=cut

sub parse_options ($@) {
    my $self = shift @_;

    my(@argv) = @_;
    $self->get_options(
	\@argv,
 	'help|?' => sub { pod2usage(1); },
	'man' => sub { pod2usage(-verbose => 2); },
	'autorun!' => \$self->{_autorun},
	'd|debug+' => \$self->{_debug},
	'i|input=s' => sub { $self->parse_io_option('input', @_); },
	'log!' => \$self->{_logprog},
	'o|output=s' => sub { $self->parse_io_option('output', @_); },
	) or pod2usage(2);
    pod2usage(2) if ($#argv >= 0);
}

=head2 setup

    $filter->setup();

Internal: setup, parse headers.

=cut

sub setup ($) {
    my($self) = @_;

    $self->finish_fh_io_option('input');

    $self->{_lineno} = 0;
    my($header);
    for (;;) {
	$header = $self->{_in}->getline;
	$self->{_lineno}++;
	croak $self->{_prog} . ": cound not find header\n"
	    if (!defined($header));
	chomp($header);
	last if ($header !~ /^[-+]+$/);
    };
    my(@raw_columns) = split(/\|/, $header);
    shift(@raw_columns);  #  REQUIRE leading |
        #if ($raw_columns[0] eq '');
    my(@columns) = Fsdb::IO::clean_potential_columns(@raw_columns);
    $self->finish_io_option('output', -fscode => 'S', -cols => \@columns);
}

=head2 run

    $filter->run();

Internal: run over each rows.

=cut
sub run ($) {
    my($self) = @_;

    my $write_fastpath_sub = $self->{_out}->fastpath_sub();

    for (;;) {
	my $line = $self->{_in}->getline;
	$self->{_lineno}++;
	last if (!defined($line));
        chomp $line;
	next if ($line =~ /^[-+]+$/);
	next if ($line =~ /\d+ rows? in set/);
	my(@row) = split(/\|/, $line);
	shift(@row);  # require leading |
	grep { s/^ +//g; } @row;
	grep { s/ +$//g; } @row;
	grep { s/  +/ /g; } @row;   # clean up for fsdb double-space separator
	grep { s/^ *$/-/g; } @row;  # add null values for fields
	&{$write_fastpath_sub}(\@row);
    };
}


=head1 AUTHOR and COPYRIGHT

Copyright (C) 2014 by John Heidemann

This program is distributed under terms of the GNU general
public license, version 2.  See the file COPYING
with the distribution for details.

=cut

1;