<?php
/**
*********************************************************************
@package: as_dbutils.php
@desc SQL data accessing wrapper with backup/restore functions
@author: Alexander Selifonov <as-works@narod.ru> <alex {at} selifan.ru>
@link http://www.selifan.ru
last_modified (dd.mm.yyyy): 29.07.2009
@version: 1.02.134
*********************************************************************
**/
if(!class_exists('cdbengine')) {
define('DBTYPE_MYSQL',1);
define('XML_PREFIX', 'AS_table'); # root tag in backup xml-file
define('ASDB_LOGFILE','./_as_dbutils.log'); # debug lines here !
if(!defined('DB_DEFAULTCHARSET')) define('DB_DEFAULTCHARSET','WINDOWS-1251');
$asdb_fdcnt = 0;
$asdb_logerrors = 0; # to turn ON SQL errors auto-logging set this to true or 1
/**
* if var $as_dbparam('server'=>'hostaddr', 'dbname'=>'mybase','username'=>'login','password'=>'psw') set,
* connection will be created inside this class, and passing these vars to constructor is not nessesary
*/
class CDbEngine { // main class def.
var $dbtype = DBTYPE_MYSQL;
var $host = '';
var $username = '';
var $password = '';
var $db_name = '';
var $b_permconn = true; # use permanent connection when possible
var $connection = false;
var $connected = false;
var $qrylink = 0; # link returned by last sql_query()
var $affectedrows = 0;
var $lastquerytext = ''; # last executed query text
var $tables = array(); # table list for backup
var $outputfile = ''; # output backup filename
var $fhan = 0; # file handle for backup file read/write
var $bckp_emptyfields = 0; // 1 or true - backup with empty (default) field values
var $charset = DB_DEFAULTCHARSET;
var $rfrom = array("\\",'<','>');
var $rto = array("\\x92","\\x60","\\x62");
var $gzmode = false;
var $verbose = 0;
var $buf = '';
var $tmpbuf = '';
var $stoptag = '';
var $fileeof = false;
var $errormessage = '';
var $errorlog_file = '';
var $extract_ddl = true; // put 'CREATE TABLE...' operators into backup file
var $tablename = '';
var $createSql = '';
var $bContents = false; // create table-list in backup XML file
var $emulate = false; // restore,sql_query: no real INSERT, just emulating (debug or other purposes)
var $logging = false; # logging mode (0-don't log anything)
var $safemode = 2; # converting 'unsafe' chars in text fields method : 0:no conversion, 1:'=>", 2:mysql_real_escape_string()
var $blobfields = array(); # these fields excluded from "str_replace" before update
var $bckp_filter=array(); # $bckp_filter['mytable']= "datecreate='2006-12-31'" - backup records filter
var $fakeinsertid=0;
function CDbEngine($db_type=DBTYPE_MYSQL, $host=false,$user=false,$password=false,$dbname=false) {
global $as_dbparam;
$this->dbtype=$db_type;
if($host===false && isset($as_dbparam['server'])) $host = $as_dbparam['server'];
if($user===false && isset($as_dbparam['username'])) $user = $as_dbparam['username'];
if($password===false && isset($as_dbparam['password'])) $password = $as_dbparam['password'];
if($dbname===false && isset($as_dbparam['dbname'])) $dbname = $as_dbparam['dbname'];
# some providers ban persistent connections, so just define this CONST to force using mysql_connect()
if(defined('DB_AVOID_PERSISTENT_CONNECT')) {
$this->b_permconn = false;
}
if($host!==false) $this->Connect($host,$user,$password,$dbname);
}
/**
* @desc sets list of field names that are 'BLOB', so do not convert them with addslashes or str_replace
*/
function SetBlobFields($fldarray) {
if(is_string($fldarray)) $this->blobfields = split("[ ,;|]",$fldarray);
elseif(is_array($fldarray)) $this->blobfields = $fldarray;
}
function SaveDDLMode($flag=true) { $this->extract_ddl = $flag; }
function SetCharSet($charset) { $this->charset = strtoupper($charset); }
function AddBackupFilter($prm1,$prm2) { // add an array or one filter
if(is_array($prm1)) $this->bckp_filter = array_merge($this->bckp_filter,$prm1);
else $this->bckp_filter[$prm1]= $prm2;
}
function SetVerbose($flag=true) { $this->verbose = $flag; }
function GetErrorMessage() { return $this->errormessage; }
function Log($par=-1) { $this->logging = $par; } # set debuging/logging level
function Connect($host=false,$user=false,$password=false,$dbname=false) {
global $asdb_logerrors;
$b_reconnect= false;
if(!$this->connected) {
if($host!==false) $this->host = $host;
if($user!==false) $this->username = $user;
if($password!==false) $this->password = $password;
$b_reconnect = true;
}
$ret = false;
switch($this->dbtype) {
case DBTYPE_MYSQL:
$ret = ($b_reconnect? (($this->b_permconn)? @mysql_pconnect($this->host,$this->username,$this->password) :
@mysql_connect($this->host,$this->username,$this->password)) : true);
if($this->logging) {
$flog = @fopen(ASDB_LOGFILE,'a');
if($flog) {
fwrite($flog, "\n".date('Y-m-d H:i:s').'/'.$_SERVER['PHP_SELF']."|opening DB, server=[{$this->host}], db=[$dbname/$this->db_name] : message=".mysql_error());
fclose($flog);
}
if($this->logging===-1)$this->logging=0;
}
if($ret) {
if($b_reconnect) $this->connection = $ret;
if(!empty($dbname)) $this->db_name = $dbname;
$ret = @mysql_select_db($this->db_name);
if(!is_resource($ret)) $this->errormessage = mysql_error();
if(!$ret && !empty($db_logerrors) && function_exists('WriteDebugInfo')) WriteDebugInfo("as_dbutils- ERROR selecting database: {$this->errormessage}");
}
else {
$ret = false;
if(!empty($db_logerrors) && function_exists('WriteDebugInfo')) WriteDebugInfo("as_dbutils- ERROR connecting, host={$this->host}, user={$this->username}, pwd={$this->password}",mysql_error());
}
$this->errormessage = mysql_error();
break;
// case other DBTYPE_...
}
$this->connected = $ret;
#echo "debug: CDEngine::Connect done: $ret<br>"; #debug
return $ret;
}
function select_db($dbname) {
$ret = false;
switch($this->dbtype) {
case DBTYPE_MYSQL:
$ret = mysql_select_db($dbname);
if($ret) $this->db_name = $dbname;
$this->errormessage = mysql_error();
break;
// case other DBTYPE_...
}
return $ret;
}
function GetDbVersion() {
$ret = floatval($this->sql_query('SELECT VERSION()',1));
return $ret;
}
function CurrentDbName() {
$curdb = $this->sql_query('select DATABASE()',1,0,0);
return(is_array($curdb)? $curdb[0]: '');
}
function Disconnect() {
switch($this->dbtype) {
case DBTYPE_MYSQL:
if(empty($this->b_permconn)) mysql_close($this->connection);
$connected = false;
return true;
# case other DBTYPE_...
}
return false;
}
# GetTableList() - returns array with all table names
function GetTableList() {
$ret = array();
switch($this->dbtype) {
case DBTYPE_MYSQL:
$tlnk = mysql_query('show tables');
while($tlnk && ($trow = mysql_fetch_row($tlnk))) { $ret[] = $trow[0]; }
break;
# case DBTYPE_...
}
return $ret;
}
function GetFieldList($tablename, $assoc=false) {
$lnk = $this->sql_query("DESCRIBE $tablename");
$ret = array();
while(is_resource($lnk) && ($r=$this->fetch_row($lnk))) {
$fldid = $r[0]; # field name
if($assoc) $ret[$fldid] = $r;
else $ret[] = $r;
}
if(is_resource($lnk)) $this->free_result($lnk);
return $ret;
}
function GetPrimaryKeyField($tablename) {
$ret = '';
$flds = $this->GetFieldList($tablename);
foreach($flds as $no=>$f) { if($f[3]=='PRI') {$ret=$f[0]; break;} }
return $ret;
}
function affected_rows() {
# switch($this->dbtype) {
# case DBTYPE_MYSQL:
if(func_num_args()>0) return mysql_affected_rows(func_get_arg(0));
$ret = mysql_affected_rows();
# break;
# case DBTYPE_...
# }
return $ret;
}
function insert_id() {
if($this->emulate) return $this->fakeinsertid;
# switch($this->dbtype) {
# case DBTYPE_MYSQL:
if(func_num_args()>0) return mysql_insert_id(func_get_arg(0));
return mysql_insert_id();
# }
return 0;
}
function sql_errno() {
# switch($this->dbtype) {
if(func_num_args()>0) return mysql_errno(func_get_arg(0));
# case DBTYPE_MYSQL:
return mysql_errno();
# case DBTYPE_...
# }
return 0;
}
function sql_error() {
switch($this->dbtype) {
case DBTYPE_MYSQL: return mysql_error();
# case DBTYPE_...
}
return 0;
}
function IsTableExist($table) {
switch($this->dbtype) {
case DBTYPE_MYSQL:
$ret = (mysql_query("SELECT (1) FROM $table"))? true:false;
break;
}
return $ret;
}
function sql_query($query,$getresult=false, $assoc=false, $multirow=false) { // universal query execute
global $asdb_logerrors;
$this->lastquerytext = $query;
$this->affectedrows = 0;
$this->errormessage = '';
$queries = is_array($query)? $query : array($query);
$ret = '';
foreach($queries as $name=>$onequery) { #<2>
if(empty($onequery)) continue;
if($this->emulate) {
echo "emulate query: $onequery\r\n<br />";
$this->fakeinsertid = rand(1000,99999);
$ret = $this->affectedrows = 0;
return $ret;
}
switch($this->dbtype) {
case DBTYPE_MYSQL:
$ret = $this->qrylink = @mysql_query($onequery);
$this->affectedrows = ($ret? @mysql_affected_rows() : false);
$this->errormessage = mysql_error();
break;
# case DBTYPE_...
}
if($getresult) {
if(is_resource($this->qrylink)) {
$ret = array();
if(!$multirow) $ret = ($assoc)? $this->fetch_assoc($this->qrylink) : $this->fetch_row($this->qrylink);
else while(($r = ($assoc)? $this->fetch_assoc($this->qrylink) : $this->fetch_row($this->qrylink))) {
$ret[] = $r;
}
$this->free_result($this->qrylink);
}
else $ret = false;
}
if(($this->logging) || (!empty($asdb_logerrors) && ($this->errormessage))) {
$flog = fopen(ASDB_LOGFILE,'a');
if($flog) {
fwrite($flog, "\n".date('Y-m-d H:i:s')."/{$_SERVER['PHP_SELF']}|execute|$onequery|{$this->qrylink}|err:{$this->errormessage}|rows: {$this->affectedrows}");
fclose($flog);
}
if($this->logging===-1)$this->logging=0; # one-query logging mode, end it!
}
} #<2>
return $ret;
}
function sql_explain($query) { // 'explain plan'
$this->lastquerytext = $query;
$this->affectedrows = 0;
switch($this->dbtype) {
case DBTYPE_MYSQL:
$ret = $this->qrylink = mysql_query("EXPLAIN $query");
$this->affectedrows = ($ret? mysql_affected_rows() : false);
$this->errormessage = mysql_error();
break;
# case DBTYPE_...
}
return $ret;
}
function GetLastQuery() { return $this->lastquerytext; }
function fetch_row($link) {
if(!is_resource($link)) return false;
switch($this->dbtype) {
case DBTYPE_MYSQL:
$ret = mysql_fetch_row($link);
if(mysql_error()) {
$this->errormessage = mysql_error();
}
return $ret;
break;
# case DBTYPE_...
}
return false;
}
function fetch_assoc($link) {
if(!is_resource($link)) return false;
switch($this->dbtype) {
case DBTYPE_MYSQL: return mysql_fetch_assoc($link);
# case DBTYPE_...
}
return false;
}
function fetch_object($link) {
switch($this->dbtype) {
case DBTYPE_MYSQL: return mysql_fetch_object($link);
# case DBTYPE_...
}
return false;
}
function SQLBuildAndExecute($table,$sqltype,$p1='',$p2='',$p3=null,$p4=false) {
# builds SQL query and execute it. Returns cursor link or false, $this->affectedrows holds affected rows count
$ret = false;
$qry = '';
$sqltype = strtoupper($sqltype);
$cond = '';
if(!empty($p2) ) {
if($p3===null) {// WHERE condition in p3, so don't build filed=value
if(is_string($p2)) $cond = $p2;
elseif(is_array($p2)) foreach($p2 as $k=>$v) $cond.=(($cond)?' AND ':'')."($k='$v')"; # passed keys is assoc.array $p2['key']=value...
# WriteDebugInfo("SQLBuildAndExecute, keys (no p3 passed): $cond",$p2);
}
else { # key fieldnames passed in one array, values in second one, or '|'-delimited strings
$keys = is_array($p2)? $p2 : explode('|',$p2);
$kvals= is_array($p3)? $p3 : explode('|',$p3);
for($ik=0; $ik<min(count($keys),count($kvals));$ik++) {
$cond .=(($cond)?' AND ':'')."{$keys[$ik]}='{$kvals[$ik]}'";
}
# WriteDebugInfo("SQLBuildAndExecute, p3 passed, cond=$cond:, p2 - p3 : ",$keys, $kvals);
}
}
switch($sqltype) { #<3>
case 'S': case 'SELECT': # $table-table(s), p2-field(s) to select, $p3- WHERE conditions
$qry = "SELECT $p1 FROM $table" .(($cond)? " WHERE $cond":'');
break;
case 'I': case 'INSERT': # $table-table, $p1-'field'=>value assoc.array
$flist = $vlist = '';
foreach($p1 as $fld=>$value) {
$flist .= ($flist==''?'':',').$fld;
if($value==='now') $oneval = 'SYSDATE()';
else {
$oneval = "'";
if($this->safemode==0 || in_array($fld,$this->blobfields)) $oneval .= $value;
elseif($this->safemode==1) $oneval .= str_replace("'",'"',$value);
else $oneval .= mysql_real_escape_string($value); # safemode>1 make real_escaped value
$oneval .= "'";
}
$vlist .= ($vlist==''?'':',').$oneval;
}
$qry = "INSERT INTO $table ($flist) VALUES ($vlist)";
break;
case 'U': case 'UPDATE': # $table-table, $p1-'field'=>value assoc.array, $p2,$p3= PK field name and it's value
$flist='';
foreach($p1 as $fld=>$value) {
if($value==='now') $oneval = 'SYSDATE()';
else {
$oneval = "'";
if($this->safemode==0 || in_array($fld,$this->blobfields)) $oneval .= $value;
elseif($this->safemode==1) $oneval .= str_replace("'",'"',$value);
else $oneval .= mysql_real_escape_string($value); # safemode>1 make real_escaped value
$oneval .= "'";
}
$flist .= ($flist==''?'':',')."$fld=$oneval";
}
$qry = "UPDATE $table SET $flist".(($cond)? " WHERE $cond":'');
if($cond==='') $qry=''; # protect whole table update ?
break;
case 'D': case 'DELETE': # p1 - WHERE condition ("field=value" or what else...
if(!empty($p1)) $qry = "DELETE FROM $table WHERE $p1";
elseif(!empty($cond)) $qry = "DELETE FROM $table WHERE $cond";
break;
} #<3> switch end
if($qry !='') $ret = $this->sql_query($qry);
return $ret;
}
function free_result($link) {
switch($this->dbtype) {
case DBTYPE_MYSQL:
if(is_resource($link)) mysql_free_result($link);
break;
# case DBTYPE_...
}
}
/**
* @desc returns record count for desired table (with optional WHERE condition, if passed)
*/
function GetRecordCount($tblname,$filter='') {
# $flt = '(1)'.(empty($filter)? '':" AND $filter");
$result = $this->GetQueryResult($tblname,'COUNT(1)',$filter);
return $result;
}
/**
* returns data from table, selected by passed criteriums
*
* @param mixed $table - table name
* @param mixed $fieldlist - returned field list, separated by comma
* @param mixed $cond WHERE clause (string or assoc.array "field"->value)
* @param mixed $multirow 0-return only first row, 1 - all rows
* @param mixed $assoc - 1 - return as associative array
* @param mixed $safe - "safe" mode
* @param mixed $orderby - optional "ORDER BY " clause
*/
function GetQueryResult($table,$fieldlist,$cond='',$multirow=false, $assoc=false,$safe=false,$orderby='') {
$scond = $cond;
if(is_array($cond)) { # $cond id assoc.array: ('fieldname'=>value,...)
$scond='(1)';
foreach($cond as $ky=>$vl) { $scond.=" AND $ky='$vl'"; }
}
$qry="SELECT $fieldlist FROM $table". ($scond===''? '': " WHERE $scond").(empty($orderby)?'': " ORDER BY $orderby");
$lnk = $this->sql_query($qry);
if(!is_resource($lnk)) return false;
$reta = false;
while(($row=($assoc ? $this->fetch_assoc($lnk): $this->fetch_row($lnk) ))) {
if(($safe) && !get_magic_quotes_runtime()) {
foreach($row as $key=>$val) $row[$key] = addslashes($val);
}
if(($assoc)) $retvalue = $row;
else $retvalue = (count($row)==1) ? $row[0] : $row;
if(empty($multirow)) return $retvalue;
if(!is_array($reta)) $reta=array();
$reta[] = $retvalue;
}
$this->free_result($lnk);
return $reta;
}
/**
* copies all data from one table to another. Only fields that exist in both tables are copied
*
* @param mixed $tableFrom
* @param mixed $tableTo
* @param string $filter WHERE condition for source table (selective copiing)
* @param mixed $flds optional assoc.array with fields to copy: array('oldname1'=>'newname1',...)
*/
function CopyRecords($tableFrom,$tableTo,$filter='',$fld_fromto='',$getsql=false) {
$fld1 = $this->GetFieldList($tableFrom,1);
$fld2= array();
if($this->sql_error()) { echo "CopyRecords err:".$this->sql_error(); return -1; } # debug
if(!is_array($fld_fromto)) {
$fld2 = $this->GetFieldList($tableTo,1);
if(!is_array($fld2) || count($fld2)<1) { $this->errormessage="Unknown or non-exist table $tableTo"; return -1; }
}
if(!is_array($fld1) || !is_array($fld2)) return -1;
$flst1 = $flst2 = '';
foreach($fld1 as $fname=>$fdef) {
if(isset($fld2[$fname])) {
$flst1 .= (($flst1=='')?'':',').$fname;
$flst2 .= (($flst2=='')?'':',').$fname;
}
elseif(isset($fld_fromto[$fname])) {
$flst1 .=(($flst1=='')?'':',').$fname; # from field "name1" to field "name2"
$flst2 .=(($flst2=='')?'':',').$fld_fromto[$fname];
}
}
$wcond = empty($filter)? '': "WHERE $filter";
$cpyqry = "INSERT INTO $tableTo ($flst2) SELECT $flst1 FROM $tableFrom $wcond";
if($flst1!=='' && $flst2!=='') {
if(empty($getsql)) {
$this->sql_query($cpyqry);
return $this->affected_rows();
}
else return $cpyqry;
}
return -1; # empty field list
}
function SqlAffectedRows() { return $this->affectedrows; }
function FileWrite($strg) {
return (($this->gzmode)? gzwrite($this->fhan,$strg):fwrite($this->fhan,$strg));
}
/**
* @desc CloneRecords() duplicates record(s) in the table
* @param $atblename - table name
* @param $pk_name - primary key field name
* @param $pk_value - one value or value array of records to be cloned
*/
function CloneRecords($tablename,$pk_name,$pk_value,$desttable='') {
$ret = 0;
$totable = ($desttable=='')? $tablename:$desttable;
if(is_array($pk_value)) {
$ret = array();
foreach($pk_value as $val) {
$dta = $this->GetQueryResult($tablename,'*',"$pk_name='$val'",false,true,true);
if($totable==$tablename) unset($dta[$pk_name]);
$this->SQLBuildAndExecute($totable,'I',$dta);
if($this->affected_rows()) $ret[] = $this->insert_id();
}
}
else {
$dta = $this->GetQueryResult($tablename,'*',"$pk_name='$pk_value'",false,true);
if($totable==$tablename) unset($dta[$pk_name]);
$this->SQLBuildAndExecute($totable,'I',$dta);
if($this->affected_rows()) $ret = $this->insert_id();
}
return $ret;
}
function GetTableStructure($table) {
$qry = "DESC $table";
$rsrc = $this->sql_query($qry);
$ret = array();
switch($this->dbtype) {
case DBTYPE_MYSQL:;
while(($row=$this->fetch_row($rsrc))) {
// $ret[field_name] =[ type, Null , Key(MUL|PRI) Default Extra (auto-increment)
$ret[$row[0]] = array($row[1], $row[2],$row[3],$row[4],$row[5]);
}
break;
# case DBTYPE_...
}
return $ret;
}
// backup/restore data function
function TryOpenBackupFile($fname) {
if($this->fhan>0) $this->fileClose();
$flen = strlen($fname);
if($flen<1) return false;
$this->gzmode = ($flen>3 && strtolower(substr($fname,$flen-3))=='.gz')? 1:0;
$this->fileeof = false;
$this->fhan = ($this->gzmode? @gzopen($fname,'r') : @fopen($fname,'r'));
if(empty($this->fhan)) {
$this->errormessage = 'Cannot open backup file '.$fname;
return false;
}
$this->buf = ''; $this->tempbuf = '';
$result = $this->FindStartTag(XML_PREFIX);
return true;
}
function FileClose() {
$closed = ($this->gzmode? gzclose($this->fhan): fclose($this->fhan));
$this->fhan = 0;
}
function FileRewind() {
if($this->gzmode) gzrewind($this->fhan);
else rewind($this->fhan);
}
function CreateContents($var=true) { $this->bContents = $var; }
function BackupOneTable($tablename) {
$defval = array();
$ret = 0;
if(empty($this->bckp_emptyfields)) { # get default fields values into $defval
$defval = $this->GetTableStructure($tablename);
}
if(is_array($defval)) { #<2>
$this->FileWrite(" <as_dbutils_table><name>$tablename</name>\n");
if($this->extract_ddl) { #<3>
$lnk = mysql_query("SHOW CREATE TABLE $tablename");
if(is_resource($lnk) && ($r=mysql_fetch_row($lnk))) { #<4>
$ddl = $r[1];
$this->FileWrite(" <CreateSQL>$ddl</CreateSQL>\n");
} #<4>
} #<3>
} #<2>
$qry = "SELECT * FROM $tablename".(empty($this->bckp_filter[$tablename])?'':' WHERE '.$this->bckp_filter[$tablename]);
$lnk = $this->sql_query($qry);
if(is_resource($lnk)) {
$rcnt = 0;
while(($lnk) && ($r=$this->fetch_assoc($lnk))) {
$this->FileWrite(" <as_dbutils_record>\n");
while (list($key, $val) = each($r)) { //<3>
if($this->bckp_emptyfields || $val !=$defval[$key][3] ) {
$val = str_replace($this->rfrom, $this->rto, $val);
$this->FileWrite(" <$key>$val</$key>\n");
}
} //<3>
$rcnt++;
$this->FileWrite(" </as_dbutils_record>\n");
}
$this->FileWrite(" </as_dbutils_table>\n");
if($this->verbose) echo date('Y.m.d H:i:s')." $tablename, records saved: $rcnt<br>\n";
$ret = 1;
}
else {
if($this->verbose) echo date('Y.m.d H:i:s')." table $tablename does not exist !<br>\n";
}
return $ret;
}
function BckpBackupTables($tlist, $fname='', $pack=0) {
$this->tables = $tlist;
if(!is_array($this->tables) || count($this->tables)<1)
$this->tables = $this->GetAllTablesList();
if(count($this->tables)<1) { $this->errormessage = "Empty table list or no connection"; return 0; }
$this->gzmode = (($pack) && function_exists('gzopen'));
if($fname==='') $fname = 'backup-'.date('Y-m-d').'.xml';
if(!is_array($this->tables) || count($this->tables)<1) return false;
$this->outputfile = $fname .($this->gzmode ? '.gz':'');
$this->fhan = ($this->gzmode)? @gzopen($this->outputfile, 'w9') : @fopen($this->outputfile,'w');
if(empty($this->fhan)) { $this->errormessage='Cannot open output file for writing'; return 0; }
$this->FileWrite("<?xml version=\"1.0\" encoding=\"$this->charset\"?>\n");
$this->FileWrite('<'.XML_PREFIX.">\n");
$retcode = 0;
if($this->bContents) {
$stlist = '';
foreach($this->tables as $tname) $stlist .= ($stlist=='' ? '':',').$tname;
$this->FileWrite("<TableList>$stlist</TableList>\n");
}
reset($this->tables);
foreach($this->tables as $tname) {
$retcode += $this->BackupOneTable($tname);
}
$this->FileWrite('</'.XML_PREFIX.">\n");
$this->FileClose();
return $retcode;
} // BackupTables() end
function GetAllTablesList() {
$ret = array();
switch($this->dbtype) {
case DBTYPE_MYSQL:
$lnk=$this->sql_query('SHOW TABLES');
if($this->affected_rows()<1) { $this->errormessage = "no tables in DB or no DB connection"; return 0; }
while(($tbl=mysql_fetch_row($lnk))) { $ret[] = $tbl[0]; }
break;
# case DBTYPE_xxx ...
}
}
function BackupDatabase($fname='',$pack=0) {
$tlist = $this->GetAllTablesList();
if(is_array($tlist) && count($tlist))
$this->BackupTables($tlist, $fname, $pack);
}
function ReadFilePortion($bytes=4096) {
if(!$this->fileeof && !empty($this->fhan)) {
$this->buf .= $this->gzmode ? gzread($this->fhan,$bytes): fread($this->fhan,$bytes);
$this->fileeof = $this->gzmode ? gzeof($this->fhan) : feof($this->fhan);
# echo "<br>read file portion $bytes ...<br>"; // debug
}
return $this->fileeof;
}
function FindStartTag($tag, $dropoldbuf=false,$maxbytes=0) {
global $asdb_fdcnt;
$ftag = "<$tag>";
$readcount=0;
while(1) {
# $asdb_fdcnt++; if($asdb_fdcnt>20) break;
if(($npos = strpos($this->buf,$ftag))!==false) {
if($this->stoptag !=='') { //<4>
$endpos = strpos($this->buf,'<'.$this->stoptag.'>');
if($endpos !==false && $endpos < $npos) return -1;
} //<4>
if($dropoldbuf) { $this->buf = substr($this->buf, $npos); $npos = 0; }
return $npos;
}
if($this->fileeof || (!empty($maxbytes) && $readcount>=$maxbytes)) break;
$this->ReadFilePortion(); $readcount += 4096;
# echo "debug FindStartTag($tag): read 4096<br>"; # debug
}
return -1; // no more tags in stream!
}
function FindEndTag($tag, $stoptag='', $dropoldbuf=false ) {
$ftag = "</$tag>";
while(1) {
if(($npos = strpos($this->buf,$ftag))!==false) {
if($dropoldbuf) { $this->buf = substr($this->buf, $npos); $npos = 0; }
return $npos;
}
if(!$this->fileeof) $this->ReadFilePortion();
else break;
}
return -1; // no more tags in stream!
}
function FindXmlValue($tag,$maxbytes=0) { // read from <tag> to </tag> into result
$ret = false;
$taglen = strlen($tag);
$pos2 = $this->FindStartTag($tag,1,$maxbytes);
if($pos2>=0) {
$pos3 = $this->FindEndTag($tag);
if($pos3>0) {
$ret = substr($this->buf,$pos2+$taglen+2,$pos3-$pos2-$taglen-2);
$this->buf = substr($this->buf,$pos3+$taglen+3);
}
}
return $ret;
}
function GetNextTable() { // finds <table> beginning, read CREATE TABLE DDL
$strt = $this->FindStartTag('as_dbutils_table',1);
$ret = 0;
if($strt>=0) {
$this->tablename = $this->FindXmlValue('name');
$this->createSql = '';
if(strpos($this->buf, '<CreateSQL>')!==false) {
$this->createSql = $this->FindXmlValue('CreateSQL');
}
$ret = 1;
}
return $ret;
}
function BuildInsertSql($xmlrecord) {
$ret = '';
$flds = array();
while(1) {
$spos1 = strpos($xmlrecord,'<');
if($spos1!==false) {
$spos2 = strpos($xmlrecord,'>',$spos1+1);
if($spos2!==false) {
$fldname = substr($xmlrecord,$spos1+1, $spos2-$spos1-1);
$spos3 = strpos($xmlrecord,"</$fldname>",$spos2+1);
if($spos3> $spos2) { //<6>
$fvalue = substr($xmlrecord,$spos2+1,$spos3-$spos2-1);
$flds[$fldname]=addslashes($fvalue); // escape special chars!
$xmlrecord = substr($xmlrecord, $spos3+strlen($fldname)+2);
} //<6>
}
else break;
}
else break;
}
if(count($flds)) {
// building SQL INSERT into...
$fnames = ''; $fvals = '';
reset($flds);
foreach ($flds as $fldname => $fvalue) {
$fnames .= ($fnames==''?'':',').$fldname;
$fvalue = str_replace($this->rto, $this->rfrom, $fvalue); // decode spec chars
$fvals .= ($fvals==''?'':',')."'$fvalue'";
}
$ret = "INSERT INTO {$this->tablename} ($fnames) VALUES ($fvals)";
}
return $ret;
}
function BckpGetContents($fname) { // gets table list from XML backup file
if(empty($fname)) return '';
$this->TryOpenBackupFile($fname);
if(empty($this->fhan)) return 0;
$ret = array();
$slist = $this->FindXmlValue('TableList',4096);
if(strlen($slist)>0) { // contents (table list) exist, so get it!
$ret = explode(',',$slist);
# echo "debug:GetContents short way!";
}
else { // long way - get all table names by GetNextTable()
# echo "debug:GetContents LONG way!<br>";
$this->FileRewind();
# $ideb = 0;
while(($this->GetNextTable())) {
$ret[] = $this->tablename;
# $ideb++; if($ideb>=100) break;
}
}
$this->FileClose();
return $ret;
}
/**
* @desc BckpRestoreTables() restores SQL data from xml[.gz] backup file.
* @param $fname - backup filename to restore from
* @param $verbose - if not empty, function echoes log
* @param $tlist - can be table names array that must be restored (the rest will be skipped)
*/
function BckpRestoreTables($fname, $verbose=0, $tlist='') {
$flen = strlen($fname);
$this->verbose = $verbose;
$this->TryOpenBackupFile($fname);
if(empty($this->fhan)) return false;
if($this->verbose && $this->emulate) echo "Emulated Restore, no real data changing...<br>\n";
if($this->verbose) echo date('Y.m.d H:i:s')." Restore from $fname begin <hr>\n";
$ret = 0;
while(1) { //<3>
$result = $this->GetNextTable();
if($result) { //<4>
$inscnt = $errcnt = 0;
$ret++;
$skiptable = (is_array($tlist) && !in_array($this->tablename,$tlist));
if($skiptable) {
if($this->verbose) echo $this->tablename." - skipped<br />\n";
continue;
}
$this->stoptag = '/as_dbutils_table'; // don't miss table end!
if($this->createSql !=='') { //<5>
$qry = 'DROP TABLE '.$this->tablename;
if($this->verbose) echo date('Y.m.d H:i:s')." {$this->tablename} : Re-creating table...<br>";
if(empty($this->emulate)) { //<6>
mysql_query($qry);
$created = mysql_query($this->createSql);
if(empty($created)) { //<7>
$this->errormessage = "{$this->tablename}: Re-creating table error: ".mysql_error();
if($this->verbose) echo "{$this->errormessage}<br>\n";
return 0;
} //<7>
} //<6>
} //<5>
else { // no CREATE DDL, so just truncate table before adding records
if($this->verbose) echo date('Y.m.d H:i:s')."$this->tablename : truncating before adding data...<br>";
if(empty($this->emulate)) $this->sql_query('TRUNCATE TABLE '.$this->tablename);
}
# for MySQL 4.0+ disable index keys updating - that speedups bulk inserts !
if($this->GetDbVersion()>=4) {
$this->sql_query("ALTER TABLE {$this->tablename} DISABLE KEYS");
}
// start parse records and inserting thrm into the table
while(($record = $this->FindXmlValue('as_dbutils_record'))) {
$sql = $this->BuildInsertSql($record);
// if($this->verbose) echo " inserting record: $sql<br>";
$this->sql_query($sql);
if($this->errormessage) $errcnt++;
else $inscnt++;
}
if($this->GetDbVersion()>=4) { # validate indexes for new records
$this->sql_query("ALTER TABLE {$this->tablename} ENABLE KEYS");
}
if($this->verbose) echo date('Y.m.d H:i:s')." $this->tablename, inserted records: $inscnt, failed inserts: $errcnt<br>";
$epos = $this->FindEndTag('as_dbutils_table');
if($epos !== false) $this->buf = substr($this->buf, $epos+18);
} //<4>
else { // no more tables in backup file
break;
}
} //<3> while read loop
if($this->verbose) echo date('Y.m.d H:i:s')."<hr> Restore from $fname finished<br>";
$this->FileClose();
return $ret;
} // BackupTables() end
} // CDbEngine definition end
/**
* @desc cleanup OnExit - closes db connection
*/
function As_dbutilsCleanUp() {
@mysql_close();
# if(function_exists('WriteDebugInfo')) WriteDebugInfo("as_dbutils cleanup code done (closing Mysql connection)");
}
if(defined('DB_AVOID_PERSISTENT_CONNECT')) register_shutdown_function('As_dbutilsCleanUp');
}
/*
$as_dbhost = isset($as_dbparam['server'])? $as_dbparam['server']:'';
$as_dbname = isset($as_dbparam['dbname'])? $as_dbparam['dbname']:'';
$as_dbuser = isset($as_dbparam['username'])? $as_dbparam['username']:'';
$as_passw = isset($as_dbparam['password'])? $as_dbparam['password']:'';
$as_dbengine = new CDbEngine(DBTYPE_MYSQL,$as_dbhost,$as_dbuser,$as_passw,$as_dbname); // ready-to-use class instance
*/
$as_dbengine = new CDbEngine(); // ready-to-use class instance
?> |
Stumble It!
Bookmark in del.icio.us
Download
