line noise

parse_db.pl

use strict;
use warnings;

my @element;
my @element_ids;
my @element_index;
while (<>) {
  if (m/INSERT INTO `element` /) {
    parse_element($_);
    next;
  }
  if (m/INSERT INTO `element_index` /) {
    parse_element_index($_);
  }
}

sub parse_element {
  my $line = shift;
  my @columns = qw/element_id parent_id root_id class ord data/;
  while ($line =~ m/\((\d+),((?:\d+|NULL)),(\d+),'((?:[^']+|\\')*)',(\d+),(?:(NULL)|'((?:[^']+|\\')*)')\)/g) {
    my %row;
    my $i = 1;
    foreach my $c (@columns) {
      no strict 'refs';
      $row{$c} = ${"$i"};
      no warnings 'uninitialized';
      undef $row{$c} if $row{$c} eq 'NULL';
      $i++;
    }
    $row{data} ||= $7;
    next unless $row{class} eq 'tags';
    push @element, \%row;
    push @element_ids, $row{element_id};
  }
}

sub parse_element_index {
  my $line = shift;
  my @columns = qw/element_id value/;
  while ($line =~ m/\((\d+),(?:(NULL)|'((?:[^']+|\\')*)')\)/g) {
    my %row;
    my $i = 1;
    foreach my $c (@columns) {
      no strict 'refs';
      $row{$c} = ${"$i"};
      $i++;
    }
    no warnings 'uninitialized';
    if ($row{value} eq 'NULL') {
      undef $row{value};
    } else {
      $row{value} = $3;
    }
    next unless grep($row{element_id} == $_, @element_ids);
    push @element_index, \%row;
  }
}

foreach my $row (@element) {
  my @columns = qw/parent_id root_id/;
  foreach my $k (@columns) {
    $row->{$k} = 'NULL' if not defined $row->{$k};
  }
  if (defined $row->{data}) {
    $row->{data} = "'" . $row->{data} . "'";
  } else {
    $row->{data} = 'NULL';
  }
  $row->{class}= "'" . $row->{class} . "'";
  my $sql = "INSERT IGNORE INTO `element` SET ";
  foreach my $k (qw/element_id parent_id root_id class ord data/) {
    $sql .= "$k = $row->{$k}, ";
  }
  $sql =~ s/, $/;\n/;
  print $sql;
}

foreach my $row (@element_index) {
  if (defined $row->{value}) {
    $row->{value} = "'" . $row->{value} . "'";
  } else {
    $row->{value} = 'NULL';
  }
  my $sql = "INSERT IGNORE INTO `element_index` SET ";
  foreach my $k (qw/element_id value/) {
    $sql .= "$k = $row->{$k}, ";
  }
  $sql =~ s/, $/;\n/;
  print $sql;
}