<?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>';
}
}
|
Stumble It!
Bookmark in del.icio.us
Download
