On Tue, Feb 14, 2012 at 07:59:18PM +0200, Shlomi Fish wrote:
> Hi Brandon,

Hi Shlomi:

tl;dr: Fixed. See bottom of message for diff.

> What I would do is write another program (possibly in Perl) to
> compare the differences and report immediately the location
> within the two files, and the context before it and after it.
> It may be a \r\n vs. \n problem or it may be something more
> subtle. Make sure you open the files in a "binmode $fh, 1" mode
> so it will read binary data. It may also be variations on
> Unicode encodings, which would be harmless.

Your encouragement got me to thinking about this. I was
envisioning a simple C program that would read one byte at a time
from the sets of files until it found an error and then output
the offset and each byte's value.

While thinking about that and evaluating how effective it would
be debugging it, I began to think about my mail message and the
lack of test data for anybody else to try the program with. I
quickly concluded that anybody could test the program by creating
a file with lorem ipsum delimited by lines containing only "GO",
or whatever the -K or --batch-keyword option was set to.

This got me to thinking that I should be testing this program
with a much smaller input. That would make it much easier to test
with. I originally tried to use MSYS diff to see the differences,
but the files were too large for it to handle (diff: memory
exhausted). I created a small sample file to test with:

> SELECT * FROM table1;
> 
> GO
> 
> SELECT * FROM table2;
> 
> GO
> 
> INSERT INTO table3 (x, y, z)
> VALUES (1, 2, 3);
> 

I ran the program and diffed the original with the concatenation
of the output and saw that an entire batch was missing (the
INSERT statement). I immediately recognized that unless the input
file ended with a batch keyword (and whitespace) some of the data
would be unwritten to output files!

I modified the program to fix this, and after debugging that and
getting it working as expected, I tried again with the large
input file. I hashed the results again and it matches! Success!

Thanks for getting me going, Shlomi! :) The diff follows[1] (the
fixed program is attached[2]).

diff -r 66d9870b3f36 -r e1df0178d049 data/split_batches.pl
--- a/data/split_batches.pl     Tue Feb 14 12:50:09 2012 -0500
+++ b/data/split_batches.pl     Tue Feb 14 14:04:34 2012 -0500
@@ -40,10 +40,7 @@
     {
         if(length($buf) + length($next_batch) > $max_size)
         {
-            open my $out_fh, '> :encoding(utf-8)',
-                    next_file($output, $i++) or die "output: $!";
-            $out_fh->print($buf);
-            close $out_fh or warn "output: $!";
+            $i++ if write_file(next_file($output, $i), $buf);
             $buf = '';
         }
         $buf .= $next_batch;
@@ -51,6 +48,9 @@
     }
 }
 
+$buf .= $next_batch;
+write_file(next_file($output, $i++), $buf);
+
 close $fh or warn "input: $!";
 
 sub next_file
@@ -85,3 +85,13 @@
     return $size;
 }
 
+sub write_file
+{
+    my ($name, $buf) = @_;
+    return 0 if ! defined $buf || length($buf) <= 0 || $buf =~ /\A\s*\z/m;
+    open my $out_fh, '> :encoding(utf-8)', $name or die "output: $!";
+    $out_fh->print($buf);
+    close $out_fh or warn "output: $!";
+    return 1;
+}
+


Regards,


-- 
Brandon McCaig <[email protected]> <[email protected]>
Castopulence Software <https://www.castopulence.org/>
Blog <http://www.bamccaig.com/>
perl -E '$_=q{V zrna gur orfg jvgu jung V fnl. }.
q{Vg qbrfa'\''g nyjnlf fbhaq gung jnl.};
tr/A-Ma-mN-Zn-z/N-Zn-zA-Ma-m/;say'


[1] Unwrapped, to prevent me breaking anything try to touch it
up..

[2] Unless I forget again...

diff -r 66d9870b3f36 -r e1df0178d049 data/split_batches.pl
--- a/data/split_batches.pl	Tue Feb 14 12:50:09 2012 -0500
+++ b/data/split_batches.pl	Tue Feb 14 14:04:34 2012 -0500
@@ -40,10 +40,7 @@
     {
         if(length($buf) + length($next_batch) > $max_size)
         {
-            open my $out_fh, '> :encoding(utf-8)',
-                    next_file($output, $i++) or die "output: $!";
-            $out_fh->print($buf);
-            close $out_fh or warn "output: $!";
+            $i++ if write_file(next_file($output, $i), $buf);
             $buf = '';
         }
         $buf .= $next_batch;
@@ -51,6 +48,9 @@
     }
 }
 
+$buf .= $next_batch;
+write_file(next_file($output, $i++), $buf);
+
 close $fh or warn "input: $!";
 
 sub next_file
@@ -85,3 +85,13 @@
     return $size;
 }
 
+sub write_file
+{
+    my ($name, $buf) = @_;
+    return 0 if ! defined $buf || length($buf) <= 0 || $buf =~ /\A\s*\z/m;
+    open my $out_fh, '> :encoding(utf-8)', $name or die "output: $!";
+    $out_fh->print($buf);
+    close $out_fh or warn "output: $!";
+    return 1;
+}
+
#!/usr/bin/perl

use 5.010;
use strict;
use utf8;
use warnings;

use Data::Dumper;
use IO::Handle;
use Getopt::Long qw/GetOptionsFromArray HelpMessage
        :config auto_help bundling no_auto_abbrev no_getopt_compat
        no_ignore_case_always no_require_order permute/;

my $batch_keyword = 'GO';
my $output = 'out.sql';
my $max_size = '10M';

GetOptions(
        'keyword|K=s' => \$batch_keyword,
        'max-size|S=s' => \$max_size,
        'output|O=s' => \$output,
        );

$max_size = parse_stream_size($max_size);

my ($sql_file) = @ARGV;

open my $fh, '< :encoding(utf-8)', $sql_file or die "input: $!";

my $i = 0;

my $buf = '';
my $next_batch = '';

while(my $line = <$fh>)
{
    $next_batch .= $line;
    chomp $line;
    if($line =~ /^\Q$batch_keyword\E$/)
    {
        if(length($buf) + length($next_batch) > $max_size)
        {
            $i++ if write_file(next_file($output, $i), $buf);
            $buf = '';
        }
        $buf .= $next_batch;
        $next_batch = '';
    }
}

$buf .= $next_batch;
write_file(next_file($output, $i++), $buf);

close $fh or warn "input: $!";

sub next_file
{
    my ($orig, $i) = @_;
    my $format;

    if($orig =~ /\.sql$/)
    {
        ($format = $orig) =~ s/\.sql$/_%04d.sql/;
    }
    else
    {
        $format = $orig . '_%04d';
    }

    return sprintf $format, $i;
}

sub parse_stream_size
{
    my ($size) = @_;
    my ($num, $mult) = $size =~ /((?:[0-9]+)?\.?(?:[0-9]+)?)\s*([BKMG])?/i;

    die "invalid stream size '$size'" unless defined $num && $num > 0;

    my %exp = ('B' => 0, 'K' => 1, 'M' => 2, 'G' => 3);
    my $exp = $exp{uc($mult // 'B')};

    $size = $num * 1024 ** $exp;

    return $size;
}

sub write_file
{
    my ($name, $buf) = @_;
    return 0 if ! defined $buf || length($buf) <= 0 || $buf =~ /\A\s*\z/m;
    open my $out_fh, '> :encoding(utf-8)', $name or die "output: $!";
    $out_fh->print($buf);
    close $out_fh or warn "output: $!";
    return 1;
}

Attachment: signature.asc
Description: Digital signature

Reply via email to