haroldo has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to solve a problem derived from database handling, which is when you have to maintain two remote copies of something in sync, and somehow they went out of sync, and therefore differ. I dumped the tables to text (comma separated format), made sure the records were in ascending order of their primary keys, and wrote these scripts to obtain two files, one with the missing records of copy "two", and the other with the missing records of copy "one". I'd like some commentary on the algorithm used, its correctness, other ideas, whatever comes off the top of your head. 
#!/usr/bin/perl 
my ($prefijo, $n1, $n2)=@ARGV;
my ($endOfFile1,$endOfFile2)=(0,0);

open FILE1, "<".("0"x(8-length($n1)))."$n1"."\/$prefijo".".txt";
open FILE2, "<".("0"x(8-length($n2)))."$n2"."\/$prefijo".".txt";
open OUTPUTF1, ">$prefijo$n1"."NoEn$n2";
open OUTPUTF2, ">$prefijo$n2"."NoEn$n1";
open OUTPUTR1, ">repetidos\-$prefijo$n1"."NoEn$n2";
open OUTPUTR2, ">repetidos\-$prefijo$n2"."NoEn$n1";

my $recordf1=<FILE1> or $endOfFile1=1;
my $recordf2=<FILE2> or $endOfFile2=1;
my $key1, $key2;
my $firstpass=1;
my $prevkey1, $prevkey2;

while ( !$endOfFile1 && !$endOfFile2 ) {

  if ($recordf1=~/^ *([0-9]+)\,/) {
    $prevkey1=$key1;
    $key1=$1;
  } else {
    undef $key1;
  }

  if ($recordf2=~/^ *([0-9]+)\,/) {
    $prevkey2=$key2;
    $key2=$1;
  } else {
    undef $key2;
  }

  if ( $key1 < $key2 ) {
  
    if (($key1 eq $prevkey1) && !$firstpass)
      { print OUTPUTR1 $recordf1;}
    else
      { print OUTPUTF1 $recordf1;}
    
    $recordf1=<FILE1> or $endOfFile1=1;
 
  } elsif ( $key1 > $key2 ) {
 
    if (($key2 eq $prevkey2) && !$firstpass)
      { print OUTPUTR2 $recordf2;}
    else
      { print OUTPUTF2 $recordf2;}
 
    $recordf2=<FILE2> or $endOfFile2=1;
 
  } else {
    $recordf1=<FILE1> or $endOfFile1=1;
    $recordf2=<FILE2> or $endOfFile2=1;
  }
  $firstpass=0 if ($firstpass);
}

while ( !$endOfFile1 ) {
  if ($recordf1=~/^ *([0-9]+)\,/) {
    $prevkey1=$key1;
    $key1=$1;
  } else {
    undef $key1;
  }

  if (($key1 eq $prevkey1) && !$firstpass)
    { print OUTPUTR1 $recordf1;}
  else
    { print OUTPUTF1 $recordf1;}
 
  $recordf1=<FILE1> or $endOfFile1=1;
  $firstpass=0 if ($firstpass);
}

while ( !$endOfFile2 ) {
  if ($recordf2=~/^ *([0-9]+)\,/) {
    $prevkey2=$key2;
    $key2=$1;
  } else {
    undef $key2;
  }

  if (($key2 eq $prevkey2) && !$firstpass)
    { print OUTPUTR2 $recordf2;}
  else
    { print OUTPUTF2 $recordf2;}
    
  $recordf2=<FILE2> or $endOfFile2=1;
  $firstpass=0 if ($firstpass);
}

close FILE1;
close FILE2;
close OUTPUTF1;
close OUTPUTF2;
close OUTPUTR1;
close OUTPUTR2;

[download]
This first script uses one-field key comparison. It also saves repeated key records (from the second repeated key on) on a separate file. The second script, which for the sake of tidiness I'll post in a message under this thread, makes the two-field key comparison.

Replies are listed 'Best First'.
Re: Key-based diffs
by haroldo (Acolyte) on Sep 20, 2004 at 05:58 UTC
    This is the two field key comparison script. Here I also am trying to exclude those records that contain some binary (corrupted) data. Look for the "next unless" lines. Tell me please what you think about it. The script worked fine until I tryied to add this feature. I'm screwing it up now, since the diffs are bigger than expected. (sorry for posting this undebugged version) 
    
#!/usr/bin/perl 
my ($prefijo, $n1, $n2)=@ARGV;
my ($endOfFile1,$endOfFile2)=(0,0);

open FILE1, "<".("0"x(8-length($n1)))."$n1"."\/$prefijo".".txt";
open FILE2, "<".("0"x(8-length($n2)))."$n2"."\/$prefijo".".txt";
open OUTPUTF1, ">$prefijo$n1"."NoEn$n2";
open OUTPUTF2, ">$prefijo$n2"."NoEn$n1";
open OUTPUTR1, ">repetidos\-$prefijo$n1"."NoEn$n2";
open OUTPUTR2, ">repetidos\-$prefijo$n2"."NoEn$n1";

my $recordf1=<FILE1> or $endOfFile1=1;
my $recordf2=<FILE2> or $endOfFile2=1;
my $key1, $key2;
my $keyDos1, $keyDos2;
my $firstpass=1;
my $prevkey1, $prevkey2;
my $prevkeyDos1, $prevkeyDos2;

while ( !$endOfFile1 && !$endOfFile2 ) {
  unless ($recordf1 =~ /^[ -\~]*\n$/) {
    $recordf1=<FILE1> or $endOfFile1=1;
    next;
  }
  unless ($recordf2 =~ /^[ -\~]*\n$/) {
    $recordf2=<FILE1> or $endOfFile2=1;
    next;
  }
  if ($recordf1=~/^ *([0-9]+)\,/) {
    $prevkey1=$key1;
    $key1=$1;
  } else {
    undef $key1;
  }

  if ($recordf2=~/^ *([0-9]+)\,/) {
    $prevkey2=$key2;
    $key2=$1;
  } else {
    undef $key2;
  }

  if ( $key1 < $key2 ) {

    if (($key1 eq $prevkey1) && !$firstpass)
      { print OUTPUTR1 $recordf1 if $key1;}
    else
      { print OUTPUTF1 $recordf1 if $key1;}

    $recordf1=<FILE1> or $endOfFile1=1;
    
  } elsif ( $key1 > $key2 ) {
  
    if (($key2 eq $prevkey2) && !$firstpass)
      { print OUTPUTR2 $recordf2 if $key2;}
    else
      { print OUTPUTF2 $recordf2 if $key2;}

    $recordf2=<FILE2> or $endOfFile2=1;

  } else {
 
    if ($recordf1=~/\, *([0-9]+)\,/) {
        $prevkeyDos1=$keyDos1;
        $keyDos1=$1;
    } else {
      undef $keyDos1;
    }

    if ($recordf2=~/\, *([0-9]+)\,/) {
        $prevkeyDos2=$keyDos2;
        $keyDos2=$1;
    } else {
      undef $keyDos2;
    }

    if ( $keyDos1 < $keyDos2 ) {
 
      if (($keyDos1 eq $prevkeyDos1) && !$firstpass)
        { print OUTPUTR1 $recordf1 if $key1;}
      else
        { print OUTPUTF1 $recordf1 if $key1;}
 
      $recordf1=<FILE1> or $endOfFile1=1;
 
    } elsif ( $keyDos1 > $keyDos2 ) {
 
      if (($keyDos2 eq $prevkeyDos2) && !$firstpass)
        { print OUTPUTR2 $recordf2 if $key2;}
      else
        { print OUTPUTF2 $recordf2 if $key2;}
 
      $recordf2=<FILE2> or $endOfFile2=1;
 
    } else {
      $recordf1=<FILE1> or $endOfFile1=1;
      $recordf2=<FILE2> or $endOfFile2=1;
    }
  }
  $firstpass=0 if ($firstpass);
}

while ( !$endOfFile1 ) {
  unless ($recordf1 =~ /^[ -\~]*\n$/) {
    $recordf1=<FILE1> or $endOfFile1=1;
    next;
  }
  if ($recordf1=~/^ *([0-9]+)\, *([0-9]+)\,/) {
    $prevkey1=$key1;
    $prevkeyDos1=$keyDos1;
    $key1=$1;
    $keyDos1=$2;
  } else {
    undef $key1;
    undef $keyDos1;
  }

  if (($key1 eq $prevkey1) && ($keyDos1 eq $prevkeyDos1) && !$firstpas
+s)
    { print OUTPUTR1 $recordf1 if $key1;}
  else
    { print OUTPUTF1 $recordf1 if $key1;}
    
  $recordf1=<FILE1> or $endOfFile1=1;
  $firstpass=0 if ($firstpass);
}

while ( !$endOfFile2 ) {
  unless ($recordf2 =~ /^[ -\~]*\n$/) {
    $recordf2=<FILE1> or $endOfFile1=1;
    next;
  }
  if ($recordf2=~/^ *([0-9]+)\, *([0-9]+)\,/) {
    $prevkey2=$key2;
    $prevkeyDos2=$keyDos2;
    $key2=$1;
    $keyDos2=$2;
  } else {
    undef $key2;
    undef $keyDos2;
  }

  if (($key2 eq $prevkey2) && ($keyDos2 eq $prevkeyDos2) && !$firstpas
+s)
    { print OUTPUTR2 $recordf2 if $key2;}
  else
    { print OUTPUTF2 $recordf2 if $key2;}
 
  $recordf2=<FILE2> or $endOfFile2=1;
  $firstpass=0 if ($firstpass);
}

close FILE1;
close FILE2;
close OUTPUTF1;
close OUTPUTF2;
close OUTPUTR1;
close OUTPUTR2;

    [download]
[reply]
[d/l]
Re: Key-based diffs
by TedPride (Priest) on Sep 20, 2004 at 06:13 UTC
    Can you give us some sample source files to test the algorithm on? It may take some time to go through this otherwise.
[reply]
      Sure. Consider: ./00000001/lines.txt 
      1017,0,984,20030115,"18:07:54",20030301,191009,5975,3,0.00,0.00,27
1018,0,985,20030115,"18:09:19",20030301,191010,5311,3,0.00,0.00,27
1019,0,986,20030115,"18:10:49",20030301,191011,6370,3,0.00,0.00,27
1020,0,987,20030115,"18:13:32",20030301,191012,1022,3,0.00,0.00,30
1022,0,989,20030115,"18:18:28",20030301,191014,6618,3,0.00,0.00,27
1023,0,990,20030115,"18:19:59",20030301,191015,5081,3,0.00,0.00,27
1024,0,991,20030115,"18:21:08",20030301,191016,5763,3,0.00,0.00,27
1025,0,992,20030115,"18:21:39",20030301,191017,5650,3,0.00,0.00,30

      [download]
      ./00000002/lines.txt 
      1019,0,986,20030115,"18:10:49",20030301,191011,6370,3,0.00,0.00,27
1020,0,987,20030115,"18:13:32",20030301,191012,1022,3,0.00,0.00,30
1021,0,988,20030115,"18:18:09",20030301,191013,7191,3,0.00,0.00,30
1022,0,989,20030115,"18:18:28",20030301,191014,6618,3,0.00,0.00,27
1023,0,990,20030115,"18:19:59",20030301,191015,5081,3,0.00,0.00,27
1024,0,991,20030115,"18:21:08",20030301,191016,5763,3,0.00,0.00,27
1026,0,993,20030115,"18:21:47",20030301,191018,1125,3,0.00,0.00,27

      [download]
      The invoking line would look like this: 
      $ ./missing.pl lines 1 2

      [download]
      (i've already noted bad output from the first "missing.pl" script, gosh!)
[reply]
[d/l]
[select]
        If it's supposed to output two files, what are the other two for? Also, you could make the code significantly simpler by loading both files at the start and removing the bad lines - do you expect to be handling huge numbers of records, or is this a possibility? I can't really get more specific without writing my own version of this script, and that's going to take a bit of time. Maybe after I get some sleep.
[reply]

Back to Seekers of Perl Wisdom