PHP Classes

File: as_dbutils.php

Recommend this page to a friend!
  Classes of Alexander Selifonov   MRU manager   as_dbutils.php   Download  
File: as_dbutils.php
Role: Auxiliary script
Content type: text/plain
Description: module used by main class (accessing MySQL data)
Class: MRU manager
Manage most recently user items in a MySQL table
Author: By
Last change:
Date: 12 years ago
Size: 49,742 bytes
 

Contents

Class file image Download
<?PHP /** @name: as_dbutils.php SQL data access/modify/backup/restore wrapper @author: Alexander Selifonov <as-works@narod.ru> <alex {at} selifan.ru> @link http://www.selifan.ru last_modified (dd.mm.yyyy): 25.10.2010 @version: 1.070.160 **/ if(!class_exists('cdbengine')) { #<1>define class define('DBTYPE_MYSQL',1); define('XML_PREFIX', 'AS_table'); # root tag in backup xml-file define('ASDB_LOGFILE','./_as_dbutils.log'); # debug lines here ! define('ASDB_VERSION','1.062'); # class version 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 $_cacheFolder = false; # if non-empty, cache for SELECT queries is turned ON var $_cacheTTL = 0; # expire cached request time (seconds) var $host = ''; var $username = ''; var $password = ''; var $db_name = ''; var $dblink = null; 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 $error_threshold = 0; # tolerate up to NNN errors while importing data before aborting var $_import_limit = 0; var $_aborted = false; 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 = 1; # 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; var $_log_all_updates = ''; # filename for saving ALL updating SQL queries var $_monitored_tables = array(); # any updating query on these tables will be logget for auditing var $_compat = ''; 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); } } # add critical tables that must be hardly monitored (saving all updating queries history) function AddMonitoredTables($param) { if(is_array($param)) foreach($aparam as $tname) $this->_monitored_tables[] = $tname; elseif(is_string($param)) $this->_monitored_tables[] = $param; } /** * @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 SetCompat($strg) { $this->_compat = $strg; } /** * Turns ON/Off caching feature * * @param mixed $cachefolder if empty, caching is turned off, otherwise folder to save cached data * @param integer $ttl time-to-live for cache (seconds) */ function EnableCaching($cachefolder, $ttl=0) { $this->_cacheFolder = $cachefolder; if(!empty($cachefolder) && empty($ttl)) $ttl = 86400; # default one day TTL $this->_cacheTTL = $ttl; } /** * Deletes ALL cached data for tables from passed list * * @param mixed $tablenames table names list as array or comma-separated string */ function ClearCache($tablenames) { $tlist = is_array($tablenames) ? $tablenames : explode(',',$tablenames); foreach($tlist as $tname) { $cachefnames = $this->_cacheFolder . $tname . '_*.cache'; $filelist = glob($cachefnames); if(is_array($filelist)) foreach($filelist as $onefile) { @unlink($onefile); } } } function GetErrorMessage() { return $this->errormessage; } /** * Sets debug/logging level or logs passed string * * @param mixed $par integer value sets debug level (-1 - log only one next SQL query, 1- turn logging ON, 0-Off), string - to log string */ function Log($par=-1) { if(is_string($par)) $this->SaveLogString($par); else $this->logging = $par; } # set debuging/logging level # Setting error tolerance for Restoring from backup operation (abort after NNN errors) function SetErrorThreshold($par) { $this->error_threshold = $par; } function Connect($host=false,$user=false,$password=false,$dbname=false) { global $asdb_logerrors, $as_dbparam; # echo "debug-Connect($host,$user,$password,$dbname)...<br />"; $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)? ($this->dblink=@mysql_pconnect($this->host,$this->username,$this->password)) : ($this->dblink=@mysql_connect($this->host,$this->username,$this->password))) : true); if($this->logging) { $this->SaveLogString("Opening DB, server=[{$this->host}], db=[$dbname/$this->db_name] : message=".mysql_error()); 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,$this->dblink); 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(); if(!($this->errormessage) && isset($as_dbparam['onconnect'])) { # useful for executing "SET NAMES `cpXXXX`" right after connect if(is_array($as_dbparam['onconnect'])) foreach($as_dbparam['onconnect'] as $sql) { $this->sql_query($sql); } elseif(is_atring($as_dbparam['onconnect'])) $this->sql_query($as_dbparam['onconnect']); } break; // case other DBTYPE_... } $this->connected = $ret; #echo "debug: CDEngine::Connect done: $ret<br>"; #debug return $ret; } /** * saves string into Log file * @param mixed $strg string to write */ function SaveLogString($strg) { $flog = @fopen(ASDB_LOGFILE,'a'); if($flog) { $runinfo = debug_backtrace(); $pref = isset($runinfo[0]) ? ($runinfo[0]['file'].'['.$runinfo[0]['line']).']' : ''; for($idt=1;isset($runinfo[$idt]);$idt++) { # find script/line that called as_dbutils if($runinfo[$idt]['file'] != $runinfo[0]['file']) { $pref = $runinfo[$idt]['file'].'['.$runinfo[$idt]['line'].']'; break; } } fwrite($flog, "\n".date('Y-m-d H:i:s')."/$pref \t{$strg}"); fclose($flog); } } 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($as_string=false) { $ret = $this->sql_query('SELECT VERSION()',1); $ret = $ret[0]; if(!$as_string) $ret = floatval($ret); 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) && is_resource($this->connection)) @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 = $this->sql_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; } /** * returns all primary key fields list * * @param string table name * @return string (on key field) or an array (if more than one PK field) */ function GetPrimaryKeyField($tablename) { $ret = array(); $flds = $this->GetFieldList($tablename); foreach($flds as $no=>$f) { if($f[3]=='PRI') {$ret[]=$f[0]; } } if(count($ret)<1) return ''; return ( (count($ret)>1) ? $ret : $ret[0]); } function affected_rows() { # switch($this->dbtype) { # case DBTYPE_MYSQL: $ret = is_resource($this->dblink)? mysql_affected_rows($this->dblink) : mysql_affected_rows(); # break; # case DBTYPE_... # } return $ret; } function insert_id() { if($this->emulate) return $this->fakeinsertid; # switch($this->dbtype) { # case DBTYPE_MYSQL: return is_resource($this->dblink)? mysql_insert_id($this->dblink) : mysql_insert_id(); # } return 0; } function sql_errno() { # switch($this->dbtype) { # case DBTYPE_MYSQL: return is_resource($this->dblink)? mysql_errno($this->dblink) : mysql_errno(); # case DBTYPE_... # } return 0; } function sql_error() { switch($this->dbtype) { case DBTYPE_MYSQL: return is_resource($this->dblink)? mysql_error($this->dblink) : mysql_error(); # case DBTYPE_... } return 0; } function IsTableExist($table) { switch($this->dbtype) { case DBTYPE_MYSQL: $ret = ($this->sql_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) { if($this->emulate=='echo') echo "emulating query: $onequery\r\n<br />"; else $this->SaveLogString("emulating query: $onequery"); $this->fakeinsertid = rand(1000,999999999); $ret = $this->affectedrows = 0; } else { #<3> switch($this->dbtype) { case DBTYPE_MYSQL: if(is_resource($this->dblink)) $ret = $this->qrylink = @mysql_query($onequery,$this->dblink); else $ret = $this->qrylink = @mysql_query($onequery); if(strpos(mysql_error(),'server has gone away')!==false) { # MySQL disconnected by timeout: reconnect and try again $this->Connect(); $ret = $this->qrylink = @mysql_query($onequery,$this->dblink); } $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))) { $this->SaveLogString("execute|$onequery|{$this->qrylink}|err:{$this->errormessage}|rows: {$this->affectedrows}"); if($this->logging===-1) $this->logging=0; # one-query logging mode, end it! } # saving "transaction log" if($this->_log_all_updates) { if(empty($this->errormessage) && $this->IsQueryUpdating($onequery,$tbname)) { # save successive updating request into SQL log file $savehan = @fopen($this->_log_all_updates,'a'); if($savehan) { $mktime = explode(' ',microtime()); $timestamp = date('Y-m-d-His').$mktime[0]; @fprintf($savehan,"<SQL TIME=\"%s\">%s</SQL>\n", $timestamp,$onequery); # date('Y-m-d H:i:s') @fclose($savehan); } } } elseif(count($this->_monitored_tables)) { # find out if it's monitored table $onetable = ''; $upd = $this->IsQueryUpdating($onequery, $onetable); if($upd AND in_array($onetable,$this->_monitored_tables)) { # save SQL query $savehan = @fopen('./_audit-updates.log','a'); if($savehan) { $usrid = isset($_SESSION['userid']) ? $_SESSION['userid'] : ''; if(!$usrid && isset($_SESSION['userpin'])) $usrid=$_SESSION['userpin']; if(function_exists('fprintf')) @fprintf($savehan,"<sql time=\"%s\" user=\"%s/%s\" script=\"%s\" affected=\"%s\">\n%s\n</sql>\n", date('d.m.Y H:i:s'),$usrid,$_SERVER['REMOTE_ADDR'],$_SERVER['PHP_SELF'],$this->affectedrows,trim($onequery)); else fputs($savehan,"<sql time=\"".date('d.m.Y H:i:s') . "\" user=\"{$usrid}/". $_SERVER['REMOTE_ADDR'] ."\" script=\"" .$_SERVER['PHP_SELF']. "\" affected=\"" . $this->affectedrows. "\">\n".$onequery. "\n</sql>\n"); @fclose($savehan); } } } } #<3> $this->lastquerytext = $onequery; } #<2> return $ret; } function IsConnected() { return ($this->connected or is_resource($this->dblink)); } function sql_explain($query) { // 'explain plan' $this->lastquerytext = $query; $this->affectedrows = 0; switch($this->dbtype) { case DBTYPE_MYSQL: $ret = $this->qrylink = is_resource($this->dblink) ? mysql_query("EXPLAIN $query",$this->dblink) : 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) { # 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': case 'A': case 'ADD': # $table-table, $p1-'field'=>value assoc.array $flist = $vlist = ''; foreach($p1 as $fld=>$value) { $flist .= ($flist==''?'':',').$fld; if($value==='now' or $value==='{now}') $oneval = 'NOW()'; elseif($value==='{today}') $oneval = 'SYSDATE()'; elseif($value==='{null}') $oneval = 'NULL'; 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 (["] becomes [\"] - may be not good!) $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) { $oneval = $value; if($value==='now' or $value==='{now}') { $oneval = 'NOW()'; } elseif($value==='today' or $value==='{today}') $oneval = 'SYSDATE()'; elseif($value==='{null}') $oneval = 'NULL'; 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($cond)) $qry = "DELETE FROM $table WHERE $cond"; elseif(!empty($p1)) $qry = "DELETE FROM $table WHERE $p1"; 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_... } } function CleanTables($tables, $condition=false) { if(is_string($tables)) $tables = preg_split("/[,;| ]/",$tables); if(is_array($tables) && count($tables)>0) foreach($tables as $tbname) { if($condition) $this->sql_query("DELETE FROM $tbname WHERE $condition"); else $this->sql_query("TRUNCATE TABLE $tbname"); } } /** * @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='',$limit='') { $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") . ($limit ? " LIMIT $limit" : ''); if($this->_cacheFolder) { $cachefname = $this->_cacheFolder . $table .'_'.md5($qry)."{$multirow}_{$assoc}_{$safe}.cache"; if(file_exists($cachefname) and $this->_cacheTTL >= (@(time() - filemtime($cachefname)))) { $result = @file_get_contents($cachefname); if($result) $reta = @unserialize($result); unset($result); return $reta; } } $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); if($this->_cacheFolder) { # create cached file file_put_contents($cachefname, serialize($reta)); } 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; } /** * Saves on table into open XML backup file * * @param mixed $tablename table name * @param mixed $tfilter optional filter condition ( for ex. "username>='VVVVVV'") */ function BackupOneTable($tablename, $tfilter=null) { $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 = $this->sql_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> $filter = $tfilter; if(!$filter && !empty($this->bckp_filter[$tablename])) $filter = $this->bckp_filter[$tablename]; $qry = "SELECT * FROM $tablename".($filter ? " WHERE ($filter)" : ''); $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')." &nbsp;$tablename, records saved: $rcnt<br>\n"; $ret = 1; } else { if($this->verbose) echo date('Y.m.d H:i:s')." &nbsp;table $tablename does not exist !<br>\n"; } return $ret; } function BckpBackupTables($tlist, $fname='', $pack=0, $filters=null) { $this->tables = array(); if(!is_array($tlist) || count($tlist)<1) $this->tables = $this->GetAllTablesList(); else { foreach($tlist as $onetable) { # ignore wrong (non-existing) table names if($this->IsTableExist($onetable)) $this->tables[]=$onetable; } } 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) { $this->FileWrite("<TableList>".implode(',',$this->tables)."</TableList>\n"); } foreach($this->tables as $tname) { $tab_filter = isset($filters[$tname]) ? $filters[$tname] : null; $retcode += $this->BackupOneTable($tname, $tab_filter); } $this->FileWrite('</'.XML_PREFIX.">\n"); $this->FileClose(); return $retcode; } // BackupTables() end function GetOuputBkpFilename() { return $this->outputfile; } 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 ... } return $ret; } 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, $convfunc=null) { $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); if(is_array($convfunc) && isset($convfunc[$this->tablename][$fldname])) $fvalue = call_user_func($convfunc[$this->tablename][$fldname],$fvalue); $flds[$fldname]=str_replace("\\\"",'"',addslashes($fvalue)); $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) * @param $keepdata - keep current data in the table(s) * @param $$convfunc - can be assoc.array: $arr['tablename']['fieldname'] = 'userfunc' - so userfunc will be called on value before inserting into DB */ function BckpRestoreTables($fname, $verbose=0, $tlist='', $keepdata=false, $convfunc=null) { set_time_limit(0); # can be looong operation ! ignore_user_abort(true); $flen = strlen($fname); $this->verbose = $verbose; $this->TryOpenBackupFile($fname); if(empty($this->fhan)) return false; $lf = isset($_SERVER['REMOTE_ADDR']) ? '<br />' : "\n"; if($this->verbose && $this->emulate) echo "Emulated Restore, no real data changing...$lf"; if($this->verbose) echo date('Y.m.d H:i:s')." Restore from $fname begin $lf"; $this->_aborted = false; $ret = 0; while(!$this->_aborted) { //<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$lf"; continue; } $this->stoptag = '/as_dbutils_table'; // don't miss table end! if(!$keepdata) { 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...$lf"; if(empty($this->emulate)) { //<6> $this->sql_query($qry); # drop table first... $crtsql = $this->createSql; if($this->GetDbVersion() < 4 || $this->_compat<4) { # clean CREATE TABLE from unsupported blocks come from MySQL 4.x $pattern = array("/DEFAULT CHARSET=([A-Za-z0-9]+)/", "/ENGINE=([A-Za-z0-9]+)/"); $crtsql = preg_replace($pattern,'', $crtsql); } $created = $this->sql_query($crtsql); if(empty($created)) { //<7> $this->errormessage = "{$this->tablename}: Re-creating table error: ".mysql_error(); if($this->verbose) echo "{$this->errormessage}$lf"; 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...$lf"; 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 $rcno = 0; while(($record = $this->FindXmlValue('as_dbutils_record'))) { $rcno++; if($this->verbose && $rcno % 1000 ==0 ) echo "{$this->tablename} - record $rcno ...$lf"; # small text every 1000 records $sql = $this->BuildInsertSql($record,$convfunc); // if($this->verbose) echo " &nbsp; inserting record: $sql<br>"; $this->sql_query($sql); if($this->errormessage) { $errcnt++; if($this->error_threshold>0 && $errcnt>= $this->error_threshold) { $this->_aborted = true; break; } } else { $inscnt++; if($this->_import_limit >0 && $inscnt>= $this->_import_limit) { break; # limit per-table record inserting (debug) } } } 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 $lf"; $epos = $this->FindEndTag('as_dbutils_table','',1); 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')."\n<hr>\n Restore from $fname finished$lf"; $this->FileClose(); return $ret; } // BackupTables() end function Emulate($param = true) { $this->emulate = $param; } /** * Gets all indexes for the table and returns assoc.array holding their expression ('field,field2') and 'type' (BTREE|FULLTEXT) * * @param mixed $tablename * @param mixed $kinds * @return empty or associative array */ function GetIndexList($tablename, $kinds=null, $idxname=null) { $ret = array(); $lnk = $this->sql_query('SHOW INDEX FROM '.$tablename); $gkinds = empty($kinds)? array() : (is_array($kinds)? $kinds : explode(',',$kinds)); while(is_resource($lnk) && ($r=$this->fetch_assoc($lnk))) { $keyname = $r['Key_name']; $keytype = isset($r['Index_type']) ? $r['Index_type'] : 'BTREE'; # MySQL 3.x - no Index_type column ! if(count($gkinds) && !in_array($keytype,$gkinds)) continue; # we want only selected index types (BTREE | FULLTEXT) if(!empty($idxname) && $keyname !== $idxname) continue; # we wanted only specific named index if(!isset($ret[$keyname])) { $ret[$keyname] = array('type'=>$keytype, 'field' => $r['Column_name']); } else $ret[$keyname]['field'] .= ','.$r['Column_name']; # multi-field index } return $ret; } /** * executes FULLTEXT seacrh query and returns result link, * UNDER CONSTRUCTION, dont't use it ! * @param mixed $strg * @param mixed $indexname * @param mixed $mode */ function FullTextSearch($strg, $indexname, $mode=null) { $ret = $this->sql_query("MATCH(alldata,zvtext) AGAINST ('$strg')"); } /** * ALL updating queries will be saved into the file, a kind of "transaction log", to be applied to database * * @param mixed $dest_filename */ function SaveAllUpdates($log_fname=true) { $this->_log_all_updates = ($log_fname===true) ? ('./sql-log-.'.$date('Ymd').'log') : $log_fname; } /** * returns true if query is one of INSERT/DELETE/UPDATE/ALTER ... * * @param string query to analyze. If empty, internal query will be analyzed */ function IsQueryUpdating($sqlqry,&$tablename) { $tablename = ''; if(empty($sqlqry)) $sqlqry = $this->lastquerytext; $words = preg_split("/[\s,]+/", trim($sqlqry)); $operator = strtolower($words[0]); $op2 = isset($words[1])? strtolower($words[1]) : ''; if(in_array($operator, array('update','delete','insert','truncate','drop'))) { $ipos=1; switch($operator) { case 'update': while(isset($words[$ipos]) && in_array(strtolower($words[$ipos]),array('low_priority','ignore'))) $ipos++; break; case 'insert': # INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name while(isset($words[$ipos]) && in_array(strtolower($words[$ipos]),array('low_priority','ignore','delayed','into'))) $ipos++; break; case 'delete': # DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name ... while(isset($words[$ipos]) && in_array(strtolower($words[$ipos]),array('low_priority','quick','ignore','from'))) $ipos++; break; case 'truncate' : case 'drop': while(isset($words[$ipos]) && in_array(strtolower($words[$ipos]),array('table'))) $ipos++; break; } if(isset($words[$ipos])) $tablename = $words[$ipos]; return true; } if($operator==='create') { if($op2=='table') $tablename = isset($words[2])? $words[2] : ''; return true; } if($operator==='alter' && $op2==='table') { $tablename = isset($words[2])?$words[2]:''; return true; } return false; } /** * TODO : function for "applying" all updating queries from "log" files since desired time * * @param mixed $log_fname filename with saved queries log * @param mixed $starttime start time in "YYYY-MM-DD [HH:MI:SS]" format * @param mixed $exclude_ops list of operator types to be skipped, ('truncate','delete') */ function ApplyUpdateLog($log_fname, $starttime='', $exclude_ops=false) { # TODO return true; } /** * Bulk restoring from multiple backup files, like list-YYYY-MM-DD.xml.gz, data-YYYY-MM-DD.xml.gz * It's better called from shell/CRON, for huge backup files. * @param mixed $preflist array containing file name prefixes. ALL files must exist to begin restoring * @param mixed $dateval date value * @param mixed $bkfolder folder containing backup files, with ending slash * @param mixed $title optional, just a title for the job */ function BulkDataRestore($preflist, $dateval='',$bkfolder='backup/', $title='') { $LF = isset($_SERVER['REMOTE_ADDR']) ? '<br />' : "\n"; echo str_repeat('=',60).$LF; if(!$title) $title = 'Data restoring from backup-files'; echo "$title\n"; echo str_repeat('=',60).$LF; if(!$dateval) { echo "No date passed, right syntax:$LF"; return false; } # make backup file names to restore foreach($preflist as $bkpref) { $fnameg = $bkpref . $dateval . '.xml.gz'; $fnamex = $bkpref . $dateval . '.xml'; if(file_exists($bkfolder.$fnameg)) $fnames[] = $fnameg; elseif(file_exists($bkfolder.$fnamex)) $fnames[] = $fnamex; else { echo ("No backup file $fnameg nor $fnamex, restoring job aborted !$LF"); return false; } $foundname = $fnames[count($fnames)-1]; printf("%3d: %-36s size: %12d $LF" , count($fnames), $foundname, filesize($bkfolder.$foundname)); } echo str_repeat('=',60).$LF; printf("Begin restore : %s ...$LF", date('d.m.Y H:i:s')); foreach($fnames as $fname) { $this->BckpRestoreTables($bkfolder.$fname,true); } echo str_repeat('=',60).$LF; printf("Finished restore : %s ...$LF", date('d.m.Y H:i:s')); return true; } } // CDbEngine definition end /** * @desc cleanup OnExit - closes db connection */ } #<1>define class 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