create csv file of relevant device_validate entries; commit 097cb34c4

nopaste

use strict;
use warnings;
use feature 'say';
use Text::CSV_XS;
use DBI;

# dump (the relevant bits of) device_validate entries in a db to a csv file,
# for later processing on live systems.

# to run in a triton instance, first do:
# eval $(perl -Mlocal::lib); cpanm Text::CSV_XS

my $start_time = time;

my $csv = Text::CSV_XS->new({ binary => 1, eol => $/ });

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($start_time);
my $timestamp = sprintf('%04d%02d%02d-%02d%02d%02d', $year, $mon, $mday, $hour, $min, $sec);
my $outfile = 'device_validate_'.$timestamp.'.csv';

# no utf8 encoding, for speed.
open my $fh, '>', $outfile or die "could not open fh for writing to $outfile: $!";

$csv->print($fh, [ qw(report_id device_id created status) ]);

my $dbh = DBI->connect(
    'dbi:Pg:dbname=conch;host=localhost', 'conch', undef,
    {
        AutoCommit          => 1,
        AutoInactiveDestroy => 1,
        PrintError          => 0,
        PrintWarn           => 0,
        RaiseError          => 1,
    },
);

# expected schema:
# CREATE TABLE public.device_validate (
#    id uuid DEFAULT public.gen_random_uuid() NOT NULL,
#    report_id uuid NOT NULL,
#    device_id text NOT NULL,
#    validation jsonb NOT NULL,
#    created timestamp with time zone DEFAULT now() NOT NULL
# );

# read from the db one row at a time:
my $sth = $dbh->prepare_cached(q{SELECT id, report_id, device_id, validation->'status', created FROM device_validate order by created ASC});
$sth->execute;

my $rows = 0;

# @row data is (id, report_id, device_id, status, created)
while (my @row = $sth->fetchrow_array) {
    ++$rows;

    my $status =
        !defined($row[3]) ? 'error'
        # treat status as a bool - undef, 0, false -> fail
        # 1, true, any other string -> pass
      : $row[3] ? 'pass'
      : 'fail';

    # output data is (report_id, device_id, created, status;
    $csv->print($fh, [ $row[1], $row[2], $row[4], $status ]);
}

close $fh;

my $end_time = time;
my $elapsed = $end_time - $start_time;
my $hours = $elapsed / 60 / 60;
my $minutes = $elapsed - ($hours * 60 * 60) / 60;
my $seconds = $elapsed - ($hours * 60 * 60) - ($minutes * 60);

say 'done. rows processed successfully: '.$rows,
    '; elapsed time: '.$hours.'h'.$minutes.'m'.$seconds.'s';

# vim: set ts=4 sts=4 sw=4 et :