#!/usr/bin/perl
use strict;
use warnings;
use utf8;
use DBI;
use Time::HiRes;
use List::Util qw( sum );
use English qw( -no_match_vars );

for my $table ( qw( pages_1000 pages_10000 pages_100000 pages_1000000 pages_10000000 ) ) {
# for my $table ( qw( pages_1000 pages_10000 ) ) {
    my @times = ();

    for my $i ( 1 .. 10 ) {
        my $start_time = Time::HiRes::time();
        my $filename = export_data( $table );
        my $runtime = Time::HiRes::time - $start_time;
        push @times, $runtime;
        unlink $filename if $i < 10;
    }

    my @sorted_times = sort { $a <=> $b } @times;

    # Average without extremes
    my $avg = sum( @sorted_times[ 2 .. 7 ] ) / 6;

    printf "%-15s : %5.3fs : %s\n", $table, $avg, join(", ", map { sprintf '%.3fs', $_ } @sorted_times );
}

exit;

sub export_data {
    my $table_name = shift;

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

    my $output_file_name = $table_name . '.xml';
    open my $fh, '>:encoding(UTF-8)', $output_file_name or die "Cannot write to $output_file_name: $OS_ERROR\n";

    print $fh <<_END_OF_HEADER_;
<?xml version="1.0" encoding="utf-8"?>
<sphinx:docset>
<sphinx:schema>
<sphinx:attr name="title" type="str2ordinal"/> 
<sphinx:field name="body"/>
<sphinx:attr name="created" type="timestamp"/>
</sphinx:schema>
_END_OF_HEADER_

    $dbh->do( 'SET TRANSACTION READ ONLY' );
    $dbh->do( 'DECLARE export NO SCROLL CURSOR FOR SELECT id, title, body, extract(epoch from created) FROM ' . $table_name );

    while ( 1 ) {
        my $rows = $dbh->selectall_arrayref( 'FETCH 100 FROM export' );
        last if 0 == scalar @{ $rows };
        for my $r ( @{ $rows } ) {
            printf $fh '<sphinx:document id="%u">%s', $r->[ 0 ], "\n";
            printf $fh '<title>%s</title>%s', xml_escape( $r->[ 1 ] ), "\n";
            printf $fh '<body>%s</body>%s',   xml_escape( $r->[ 2 ] ), "\n";
            printf $fh '<created>%u</created>%s', $r->[ 3 ], "\n";
            printf $fh '</sphinx:document>%s', "\n";
        }
    }

    $dbh->rollback();
    $dbh->disconnect();

    print $fh "</sphinx:docset>\n";
    close $fh;

    return $output_file_name;
}

sub xml_escape {
    my $what = shift;
    $what =~ s/&/\&amp;/g;
    $what =~ s/"/\&quot;/g;
    $what =~ s/'/\&apos;/g;
    $what =~ s/</\&lt;/g;
    $what =~ s/>/\&gt;/g;
    return $what;
}
