PHP Classes
elePHPant
Icontem

File: datafromdump.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Alexander Selifonov  >  Retrieving, executing and saving partial data from SQL dump  >  datafromdump.php  >  Download  
File: datafromdump.php
Role: Class source
Content type: text/plain
Description: Main class source
Class: Retrieving, executing and saving partial data from SQL dump
Extract table dumps from full MySQL database dumps
Author: By
Last change:
Date: 4 years ago
Size: 9,370 bytes
 

Contents

Class file image Download
<?PHP
/**
* @name datafromdump.php
* Grabbing partial SQL INSERT lines from (Mysql) DUMP file
* and executing OR saving them into destination file
 @Author Alexander Selifonov <as-works@narod.ru>
 @link http://www.selifan.ru
 @last_modified : 13.09.2010
*/
class CDataFromDump {
    private
$tablelist = array();
    private
$stoppedOffset = 0;
    private
$inserted = array();
    private
$srcfilename = '';
    private
$outfile = '';
    private
$_usemetadata = true;
    private
$_metainfo = array();
    private
$_verbose = false;
    private
$eol = '<br />';
    private
$_createSql = true; # include "CREATE TABLE" operators
   
private $in_han = 0;
    private
$out_han = 0;

    function
__construct($verbose=false, $usemeta=true) {
       
$this->_verbose = !empty($verbose);
       
$this->_usemetadata = !empty($usemeta);
        if(!isset(
$_SERVER['REMOTE_ADDR'])) $this->eol = "\r\n"; # run from console, so EOL must be non-HTML CRLF
   
}

   
/**
    * Parses SQL dump file and saves or executes "INSERT INTO ..." for desired tables
    *
    * @param mixed $srcfilename
    * @param mixed $offset
    * @param mixed $destfilename
    */
   
public function ParseDump($srcfilename, $tablelist='', $destfilename='',$createsql=true, $offset=0) {
        global
$as_dbengine;
       
set_time_limit(0); # process can take long time.
       
$this->_createSql = !empty($createsql);
        if(
$destfilename == $srcfilename) {
            echo
"ERROR: Never set output filename identical to the source DUMP file !".$this->eol;
            return
false; # no table names to extract from dump
       
}
        if(
is_array($tablelist)) $this->tablelist = $tablelist;
        elseif(
is_string($tablelist)) $this->tablelist = explode(',',$tablelist);
        if(!
sizeof($this->tablelist)) {
            echo
"ERROR: No table names passed".$this->eol;
            return
false; # no table names to extract from dump
       
}

       
$metaw = 0;
       
$this->srcfilename = $srcfilename;
       
$this->in_han = @fopen($srcfilename,'r');
        if(!
is_resource($this->in_han)) {
            echo
"ERROR: could not open source dump $srcfilename".$this->eol;
            return
false;
        }
       
$this->out_han = false;
       
$this->inserted = array();
       
$this->outfile = $destfilename;
        if(
$destfilename) {
           
$this->out_han = fopen($destfilename,'w');
            if(!
$this->out_han) {
                echo
"ERROR: Creating output file error, $destfilename".$this->eol;
                @
fclose($this->in_han);
                return
false;
            }
        }

       
$metafilename = $srcfilename.'.metadata'; # generate file with meta-data
       
if($this->_usemetadata) {
           
$bymeta = (file_exists($metafilename) and filemtime($metafilename) == filemtime($this->srcfilename));
            if(!
$bymeta) $metaw = @fopen($metafilename,'w'); # create (and re-fill) metadata file
       
}
        if(
$bymeta) $this->ScanWithMeta($metafilename);
        else {
#<3>
           
if($offset>0) fseek($this->in_han,$offset);
           
$lineno = 0;
           
$curtable = '';
            while(!
feof($this->in_han)) { #<4>
               
$curpos = ftell($this->in_han);
               
$line = trim(fgets($this->in_han));
               
$lineno++;
                if(
$line==='') continue;
               
$lbegin = strtoupper(substr($line,0,12));
                if(
$lbegin !=='INSERT INTO ' AND $lbegin!=='CREATE TABLE' ) continue;
               
$arr = explode(' ',$line);
               
$tname = isset($arr[2]) ? $arr[2] : '';
                if(
$curtable != $tname) {
                   
$curtable = $tname;
                   
# Save found beginning of INSERT INTO ... in metadata file, for future use
                   
if(is_resource($metaw)) {
                       
fwrite($metaw, "$curtable|$curpos\n");
                        if(
$this->_verbose) echo "Offset for $curtable is $curpos".$this->eol;
                    }
                }
                if(!
in_array($curtable, $this->tablelist)) continue; # no meta-data mode
               
if(substr($line,-1)!=';') { #<5>
                    # read multi-line INSERT operator until ';' char found
                   
while(!feof($this->in_han)) {
                       
$line2 = rtrim(fgets($this->in_han));
                       
$line .= "\n$line2";
                        if(
substr($line,-1)==';') break;
                    }
                }
#<5>
               
if($this->_createSql OR $lbegin =='INSERT INTO ') $this->processOneSql($curtable, $line);
               
$this->stoppedOffset = ftell($this->in_han);
            }
#<4>
       
} #<3>
       
fclose($this->in_han);
        if(
$this->out_han) fclose($this->out_han);
       
# and close/touch generated meta-data file
       
if(is_resource($metaw)) {
           
fclose($metaw);
           
touch($metafilename, filemtime($this->srcfilename));
            if(
$this->_verbose) echo 'Metainfo file created : '.$metafilename.$this->eol;
        }
        return
true;
    }
    private function
processOneSql($tablename,$sqlbody) {
        global
$as_dbengine;
        if(!isset(
$this->inserted[$tablename])) $this->inserted[$tablename]=0;
        if(
$this->out_han) {
           
fwrite($this->out_han, $sqlbody."\n");
           
$this->inserted[$tablename] += 1;
        }
        else {
# run SQL operator
           
if(isset($as_dbengine) && is_object($as_dbengine)) {
               
$as_dbengine->sql_query($sqlbody);
               
$this->inserted[$tablename] += $as_dbengine->affected_rows();
            }
            else {
               
mysql_query($sqlbody);
               
$this->inserted[$tablename] += mysql_affected_rows();
            }
        }
    }
   
/**
    * Load metadata from metadata file, created earlier
    *
    * @param mixed $fname metadata filename
    */
   
private function ScanWithMeta($fname) {
       
$itable = 0;
       
$lns = @file($fname);
       
$this->_metainfo = array();
        if(!
is_array($lns)) return;
        foreach(
$lns as $ln) {
           
$ln = explode('|', trim($ln));
            if(
count($ln)<2) continue;
            if(isset(
$ln[1])) $this->_metainfo[$ln[0]] = $ln[1];
        }
        if(
count($this->_metainfo)<1) {
            echo
"ERROR: empty or wrong Metadata file $fname".$this->eol;
            return
false;
        }
        if(
$this->_verbose) echo "Metainfo loaded for ".count($this->_metainfo)." tables".$this->eol; # debug
       
$toffset = 0;
        for(
$itable=0; $itable<count($this->tablelist); $itable++) { #<3>
         
if(isset($this->_metainfo[$this->tablelist[$itable]])) { #<4>
             
$curtable = $this->tablelist[$itable];
             
$toffset = $this->_metainfo[$this->tablelist[$itable]];
              @
fseek($this->in_han, $toffset);
             
$curtable = $this->tablelist[$itable];
              if(
$this->_verbose) echo "Found offset $toffset for ".$this->tablelist[$itable].$this->eol;

              while(!
feof($this->in_han)) { #<5>
               
$curpos = ftell($this->in_han);
               
$line = trim(fgets($this->in_han));
                if(
$line==='') continue;
               
$lbegin = strtoupper(substr($line,0,12));
               
$skipit = true;
                if(
$lbegin =='INSERT INTO ') $skipit = false;
                elseif(
$this->_createSql && $lbegin=='CREATE TABLE') $skipit = false;
                else continue;

               
$arr = explode(' ',$line);
               
$tname = isset($arr[2]) ? $arr[2] : '';
                if(
$curtable != $tname) break; # get next table to process
               
if(substr($line,-1)!=';') {
                   
# read multi-line INSERT operator until ';' char found
                   
while(!feof($this->in_han)) {
                       
$line2 = rtrim(fgets($this->in_han));
                       
$line .= "\n$line2";
                        if(
substr($line,-1)==';') break;
                    }
                }
                if(
$this->_createSql OR $lbegin =='INSERT INTO ') $this->processOneSql($curtable, $line);
               
$this->stoppedOffset = ftell($this->in_han);
              }
#<5>
         
} #<4>
       
} #<3>
   
}

   
/**
    * Printing statistics about performed job (HTML format)
    */
   
public function PrintStatistics() {
        echo
"Passed table names :"; foreach($this->tablelist as $tname) echo " $tname";
        echo
$this->eol."Parsed/Inserted data statistics<table border='1'><tr><td>table</td><td>SQL operator count</td></tr>\n";
        foreach(
$this->inserted as $tname => $added) {
            echo
"<tr><td>$tname</td><td style=\"text-align:right\">$added</td></tr>\n";
        }
        echo
"<tr><td>Source file <b>{$this->srcfilename}</b> size:</td><td style=\"text-align:right\">".number_format(filesize($this->srcfilename))."</td></tr>\n";
        if(
$this->outfile) {
           
$outsize = @filesize($this->outfile);
            echo
"<tr><td>Generated file <b>{$this->outfile}</b> size :</td><td style=\"text-align:right\">".number_format($outsize)."</td></tr>\n";
        }
        echo
'</table>';
    }
}