#!/usr/local/bin/perl
#
# restore.cgi v0.21
#
# This script will parse mysql dump file created by mysqldumo,
# creates modified .sql file (restore.tmp) stripped of INSERT + data lines,
# as well as data file for each tables (TBL.dat), and restore the table using them.
#
# Compared to using original .sql file with INSERT, 
# it will finish the job a lot quicker.
#
# Installation:
#   Put it in a well protected directory, and set permission of 700 (Not 755)
#   Edit and replace configuration section.
#
# To use it via browser:  http://example.com/protected/restore.cgi?dump.sql
# As a command or from other script: ./restore.cgi dump.sql
#
# Please read MySQL manual for more info:
#   http://dev.mysql.com/doc/mysql/en/mysql.html
#
select(STDOUT);
$| = 1;
print "Content-type: text/plain\n\nRestoring mysql: ";
open(STDERR,">&STDOUT");

######## Setup section ############
my $U = 'USERNAME'; # Username for MySQL
my $P = 'PASSWORD'; # Password for MySQL
my $H = 'mysql0X.powweb.com'; # MySQL server name
my $D = 'DBNAME'; # Database name
my $sqld = "backup.sql"; # Default backup file name
######### End of setup ############


$sqld = $ARGV[0] if $ARGV[0];
print "Dumpfile=$sqld\n\n";
my $dat = '';
my $dat2 = '';
my $pos = 0;
my $blen = 8160;
my $tmpdir = "minisqlimport.tmp";

# Open the dump file
print "Opening $sqld ... ";
open(FH, "$sqld") || die("Failed to open MySql dumpfile: $sqld\n");
print "done.\n";

mkdir("$tmpdir");
chdir("$tmpdir") ||  die("Failed to chdir into temporary directory: $tmpdir\n");

$restore = "$sqld.r.sql";
# Open temp file that hold mysql commands
print "Opening $restore ...";
open(TMP, ">$restore") || die("Failed to open temp file\n");
print "done.\n\n";

read(FH, $dat, $blen)  || die("Dump file format error: empty file\n");
#print "<hr>$dat<hr>\n";

if( $dat =~m#SET NAMES (.*?) #m){
  $chardb ="SET character_set_database = '$1';\n";
}

while(1){
  $pos = index($dat, "\nINSE"); ### Find \nINSE ###
#print "START pos=$pos\n";
  if($pos < 0){  # Get next chunk if not found in the buffer
    $datlen = length($dat);
    print TMP substr($dat,0, $datlen-4);
    read(FH, $dat2, $blen) || last;
    $dat = substr($dat,$datlen-4).$dat2;
    next;
  }else{
    print TMP substr($dat,0,$pos+1);
    $dat = substr($dat,$pos +1);
  } ### \nINSE found ###

  if( ($posy = index($dat, "(")) < 0 ){ ### Find ( ###
    read(FH, $dat2, $blen) || last;
    $posy = index($dat2, "(");
    last if $posy < 0;
    $posy += length($dat);
    $dat .= $dat2;
  } ### \nINSEERT .... ( found ###

  substr($dat,0,$posy) =~m/^INSERT INTO \`([^\`]+)\` VALUES /;
  $t = $1;
  print "Table=$t : ";
  
  ### Replace 'INSERT INTO' lines with LOAD DATA LOCAL INFINLE ...'
  print TMP $chardb;
  print TMP "LOAD DATA LOCAL INFILE '$t.dat' INTO TABLE $t FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY \"'\" LINES TERMINATED BY '),(';\n";

  ### Create a data file for the table removing up to the first (, and last );\n
  print "Opening $t.dat ...";
  open(FT,">$t.dat") || die("Failed to open for write: $t.dat\n");
  print "done.\ndat=".substr($dat,0,80)."\n\n";
    
  $x = 0;
  while(index($dat,'I') == 0){ ### Treat all INSERT lines ###
    print FT '),(' if $x;  # Connect INSERT lines with ),(
    $x++;
    $pos = index($dat, '('); ### Find ( --- the start of a dataline ###
    if($pos < 0){
      read(FH, $dat2, $blen) || last;
      $pos = index($dat2, "(");
      last if $pos < 0;
      $pos += length($dat);
      $dat .=$dat2;
    } #### ( found ###
    
    $dat = substr($dat,$pos+1);
    $posy = index($dat, ");\n"); ### Find );\n --- the end of a dataline ###
    if( $posy < 0){
      while(read(FH, $dat2, $blen)){
        $posy = index(substr($dat, length($dat) -2).$dat2, ");\n");
        last if $posy >= 0;
        print FT $dat;
        $dat = $dat2;
      } ### Found );\n after multiple attempts ###
      $posy -= 2;
      if($posy < 0){
        print FT substr($dat,0,length($dat)+$posy);
      }else{
        print FT $dat;
        print FT substr($dat2,0,$posy);
      }
      $dat = substr($dat2, $posy+3);
    }else{
      print FT substr($dat,0,$posy);
      $dat = substr($dat,$posy+3);
    } ### Found );\n at the first attempt ###
  } ### Data file done ###
  close(FT);
}        
print TMP $dat;
close(FH);
close(TMP);

print "Finished preparing $restore and data file(s).\nStarting to restore ...\n\n";
#print `cat $restore 2>&1`;
#exit; # If you just want to create data file(s) and $restore, end here.

# Do actual restore using 'mysql' command. Add/Modify options as you want.
# For more detail ==> http://dev.mysql.com/doc/mysql/en/mysql.html
print `time mysql -v -v -v -u$U -p$P -h$H -D$D <$restore 2>&1`;
print "All done\n";
exit 0;
1;