view release on metacpan or  search on metacpan

t/03-write_sql.t  view on Meta::CPAN

use strict;
use warnings;
use Test::More;

use Path::Tiny;
use Spreadsheet::XLSX;
use DBI;
use Archive::Zip;

use AlignDB::ToXLSX;

# cd ~/Scripts/alignDB
# perl util/query_sql.pl -d ScervsRM11_1a_Spar -t csv -o isw.csv \
#     -q "SELECT isw_id, isw_length, isw_distance, isw_pi FROM isw LIMIT 1000"

my $temp = Path::Tiny->tempfile;

{
    #@type DBI
    my $dbh = DBI->connect("DBI:CSV:");
    $dbh->{csv_tables}->{isw} = {
        eol            => "\n",
        sep_char       => ",",
        file           => "t/isw.csv",
        skip_first_row => 1,
        quote_char     => '',
        col_names      => [qw{ isw_id isw_length isw_distance isw_pi }],
    };

    my $sql_query = q{
        SELECT
            isw.isw_distance distance,
            AVG(isw.isw_pi) AVG_pi,
            COUNT(*) COUNT
        FROM
            isw
        WHERE
            isw.isw_distance <= 20
        GROUP BY
            distance
        ORDER BY
            distance
    };

    my $toxlsx = AlignDB::ToXLSX->new(
        dbh => $dbh,

        outfile => $temp->stringify,

        #                outfile => "03.xlsx",
    );

    my $sheet_name = 'd1_pi';
    my $sheet;

    {    # header
        my @names = $toxlsx->sql2names($sql_query);
        $sheet = $toxlsx->write_header( $sheet_name, { header => \@names, } );
    }

    my $data;    # for scales
    {            # content
        $data = $toxlsx->write_sql(
            $sheet,
            {   sql_query => $sql_query,
                data      => 1,
            }
        );
    }

    {            # draw_y
        my %opt = (
            x_column  => 0,
            y_column  => 1,
            first_row => 2,
            last_row  => 17,

            #            x_max_scale => 15,
            x_scale_unit => 5,
            y_data       => $data->[1],
            x_title      => "Distance to indels (d1)",
            y_title      => "Nucleotide diversity",