Executes SQL commands against server; uploads results to Google Spreadsheets

sql2xls.pl

#!/usr/bin/perl

# Allows the user to take the result of a series of SQL queries
# and place them in Google docs, so that data can be easily shared

# Author: Glen Solsberry <http://dp.cx/blog>
# Copyright: Public Domain

use warnings;
use strict;

use Data::Printer;
use DBI;
use Getopt::Long::Descriptive;
use Net::Google::Spreadsheets;

my ($opt, $usage) = describe_options(
        '%c %o',
        [ 'sqlfile|s=s', "sql file to run commands from" ],
        [ 'commandlist|c=s@', "list of sql commands to run; multiple values may be passed", { default => [ ] } ],
        [ 'spreadsheet|n=s', "the name of the spreadsheet to use in google docs; defaults to $0", { default => $0 } ],
        [ 'worksheet|n=s', "the name of the worksheet to create in google docs" ],
        [ 'speed=i', "the number of rows to update at once", { default => 50 } ],
        [ 'dryrun', "perform the query and display results, but do not store in GDocs", { default => 0 } ],
        [],
        [ 'docsuser=s', "the username to log in to google docs with" ],
        [ 'docspass=s', "the password to log in to google docs with" ],
        [],
        [ 'dbhost=s', "the host to connect to for sql data" ],
        [ 'dbport=s', "the port to connect to for sql data" ],
        [ 'dbname=s', "the name of the database" ],
        [ 'dbuser=s', "the user to connect with" ],
        [ 'dbpass=s', "the password to connect with" ],
        [],
        [ 'help',       "print usage message and exit" ],
        );

print($usage->text), exit if $opt->help;
print($usage->text), exit unless (
    ($opt->sqlfile || $opt->commandlist)
    && $opt->worksheet
    && ($opt->docsuser && $opt->docspass)
    && ($opt->dbhost && $opt->dbport && $opt->dbname && $opt->dbuser && $opt->dbpass)
    );

my $service = Net::Google::Spreadsheets->new(
        username => $opt->docsuser,
        password => $opt->docspass
        );

my $dbh = DBI->connect(sprintf("dbi:mysql:%s:%s:%s", $opt->dbname, $opt->dbhost, $opt->dbport), $opt->dbuser, $opt->dbpass, { AutoCommit => 1 });
die DBI->errstr unless $dbh;


my @commands;
if ($opt->sqlfile) {
    if (! -e $opt->sqlfile) {
        print $opt->sqlfile . " does not exist. Please create it";
        print $usage->text;
        exit;
    }

    open(my $fh, "<", $opt->sqlfile) || die "$!";
    @commands = <$fh>;
    close($fh);
} elsif ($opt->commandlist) {
    @commands = @{$opt->commandlist};
} else {
    warn "At least one of --sqlfile or --commandlist must be supplied";
    die $usage->text;
}

# find a spreadsheet by title
my $spreadsheet = $service->spreadsheet({ title => $opt->spreadsheet });

die sprintf("No sheet found called %s. Please create it.", $opt->spreadsheet) unless $spreadsheet;

my $worksheet = $spreadsheet->worksheet({ title => $opt->worksheet }) unless $opt->dryrun;

my $sth;
my $command_id = 0;
foreach my $command (@commands) {
    $command_id++;
    $sth = $dbh->prepare($command);
    $sth->execute();
    if ($command_id == @commands) {
        my @names = $sth->{NAME};
        my $row = $sth->fetchrow_hashref();
        if (! $opt->dryrun) {
            if (! $worksheet) {
                $worksheet = $spreadsheet->add_worksheet({ title => $opt->sheetname, row_count => $sth->rows + 1, col_count => scalar(@{$names[0]}) });
            }
        }

        my $row_id = 1;
        my @row_map = create_row_map($row, $row_id, \@names);

        if (! $opt->dryrun) {
            $worksheet->batchupdate_cell(@row_map);
        } else {
            p(@row_map);
        }

        $row_id = 2;
        $row = clean_row($row);
        @row_map = create_row_map($row, $row_id, \@names);

        if (! $opt->dryrun) {
            $worksheet->batchupdate_cell(@row_map);
        } else {
            p(@row_map);
        }

        my @rows = ();
        while (my $row = $sth->fetchrow_hashref()) {
            $row_id++;
            $row = clean_row($row);
            push(@rows, create_row_map($row, $row_id, \@names));

            if ($row_id % $opt->speed == 0) {
                if (! $opt->dryrun) {
                    $worksheet->batchupdate_cell(@rows);
                } else {
                    p(@rows);
                }
                @rows = ();
                print "\rUpdating " . $opt->speed . " rows";
            }
        }
        # p(@rows);
        if (! $opt->dryrun) {
            $worksheet->batchupdate_cell(@rows);
        }
        print "\rUpdating " . scalar(@rows) . " rows";
    }
    $sth->finish();
}
print "\nDone";
$dbh->disconnect();

sub clean_row {
    my ($row) = @_;
    foreach my $key (keys %{$row}) {
        my $orig_key =  $key;
        $key =~ s/\W/_/g;
        $row->{$key} = $row->{$orig_key};
        delete($row->{$orig_key}) if $orig_key ne $key;
    }
    return $row;
}

sub create_row_map {
    my ($row, $row_id, $names) = @_;

    my @cell_map;
    my $col = 0;
    foreach my $key (@{$names->[0]}) {
        $cell_map[$col] = {};
        $cell_map[$col]->{'col'} = $col+1;
        $cell_map[$col]->{'row'} = $row_id;


        if ($row_id == 1) {
            $cell_map[$col]->{'input_value'} = $key || '';
        } else {
            $cell_map[$col]->{'input_value'} = $row->{$key} || '';
        }
        $col++;
    }

    return @cell_map;
}