<?php
/*
database.class.min.php v1.1 2019-06-06
Copyright (C) 2015-2019 Tony Phelps
---------------------------------------------------------------------
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
---------------------------------------------------------------------
email: dev@tweezy.net.au
post: P O Box 200 Kingston Tas 7051 AUSTRALIA
---------------------------------------------------------------------
*/
abstract class dbSQLbase { protected $v1=false; protected $v2=0; protected $v3=array(); abstract public function connect(); abstract public function command($v4); abstract public function query($v4, $v5=false); abstract public function quote($v6); abstract public function getIdent(); abstract public function lastError(); abstract public function getTables(); abstract public function getFields($v7, $v8=false); abstract public function trimZeros($v9, $v10=3); abstract protected function resultType($v6); protected function _addError($v11=false) { $this->v3[] = $v11 ? $v11 : $this->lastError(); } public function escape($v6) { return substr($this->quote($v6), 1, -1); } public function insert($v7, $v12) { if(!$this->v1) { $this->_text('nodb', __FUNCTION__); return false; } $v13=array(); $v14=array(); foreach($v12 as $v15 => $v16) { $v13[]=$v15; if(is_null($v16) or strtolower($v16)=='null') { $v14[]='NULL'; } else { if(get_magic_quotes_gpc()) { $v16=stripslashes($v16);} $v14[]=$this->quote($v16); } } $v4='insert into '.$v7.' ('.implode(', ', $v13).') values ('.implode(', ', $v14).')'; if($this->command($v4)) { $v17=$this->getIdent(); } else { $this->_addError(); $v17=false; } return $v17; } public function update($v7, $v12, $v18) { if(!$this->v1) { $this->_text('nodb', __FUNCTION__); return false; } $v19=array(); foreach($v12 as $v15 => $v16) { if(is_null($v16) or strtolower($v16)=='null') { $v19[]="$v15=NULL"; } else { if(get_magic_quotes_gpc()) { $v16=stripslashes($v16);} $v19[]=$v15.'='.$this->quote($v16); } } $v20=$this->command('update '.$v7.' set '.implode(', ', $v19).' where '.$v18); if(!$v20) { $this->_addError(); } return $v20; } public function delete($v7, $v18) { if(!$this->v1) { $this->_text('nodb', __FUNCTION__); return false; } $v20=$this->command("delete from $v7 where $v18"); if(!$v20) { $this->_addError(); } return $v20; } protected function _insertMulti($v7, $v13, $v21) { $v4="insert into $v7 ($v13) values ".implode(', ', $v21); if($this->command($v4)) { $v17=$this->getIdent(); } else { $this->_addError(); $v17=false; } return $v17; } public function insertMulti($v7, $v22, $v23=20) { $v2=0; if(!isset($v22[0])) { return false; } $v13=implode(', ', array_keys($v22[0])); $v24=array(); foreach($v22 as $v25) { $v14=array(); foreach($v25 as $v26) { $v14[]=(is_null($v26) or strtolower($v26)=='null') ? 'NULL' : $this->quote($v26); } $v24[]='('.implode(', ', $v14).')'; if(count($v24)==$v23) { $v17=$this->_insertMulti($v7, $v13, $v24); $v2 += $this->v2; $v24=array(); } } if($v24) { $v17=$this->_insertMulti($v7, $v13, $v24); $v2 += $this->v2; } $this->v2=$v2; return $v17; } public function insertSelect($v7, $v12, $v27, $v18) { foreach($v12 as $v28=>$v29) { if(is_null($v29) or strtolower($v29)==='null') { $v12[$v28] = 'NULL'; } elseif(is_string($v29) and $v29 and substr($v29,0,1)==='[' and substr($v29,-1)===']') { $v12[$v28] = substr($v29,1,-1); } else { $v12[$v28] = $this->quote($v29); } } $v4 = 'insert into '.$v7.' ('.implode(', ', array_keys($v12)).') select '.implode(', ', $v12).' from '.$v27.' where '.$v18; if($this->command($v4)) { $v17=$this->getIdent(); } else { $this->_addError(); $v17=false; } return $v17; } public function getErrors($v30=false) { return ($v30) ? count($this->v3) : $this->v3; } public function getRow($v4, $v5=false) { $v31 = $this->query($v4, $v5); if($v31) { return (1==count($v31[0])) ? reset($v31[0]) : $v31[0]; } else { return false; } } public function getValue($v4) { $v31 = $this->query($v4); return ($v31) ? reset($v31[0]): false; } public function getInt($v4) { return (int)$this->getValue($v4); } public function htmlTable($v32, $v33='border="1"', $v34=true, $v35=false) { if(is_string($v32)) { $v32=$this->query($v32); if(!$v32) { $this->_addError(); return false; } } if($v35) { if(is_string($v35)) { $v35=explode(',', $v35); } } else { $v35=array(); } if($v32) { $this->v2=count($v32); $v36="<table $v33>\r\n"; if($v34) { $v36.="\t<tr>\r\n"; foreach(array_keys($v32[0]) as $v15) { $v36.="\t\t<th>$v15</th>\r\n"; } $v36.="\t</tr>\r\n"; } foreach($v32 as $v37) { $v36.="\t<tr>\r\n"; foreach($v37 as $v15=>$v38) { if(is_null($v38) or $v38=='') { $v38=' '; } elseif(in_array($v15, $v35)) { $v38=htmlspecialchars($v38); } $v36.="\t\t<td>$v38</td>\r\n"; } $v36.="\t</tr>\r\n"; } $v36.="</table>\r\n"; return $v36; } else { return false; } } public function csv($v4, $v39=true, $v40=',', $v41=true, $v42='""') { $v43=$this->query($v4); if(!is_array($v43) or 0==count($v43)) return false; if(!isset($v43[0]) or !is_array($v43[0])) return false; $v44=''; if($v39) { $v45=array(); foreach(array_keys($v43[0]) as $v15) { $v45[]=$this->_csvQuote($v15, $v41, $v42); } $v44.=implode($v40, $v45)."\r\n"; } foreach($v43 as $v37) { $v45=array(); foreach($v37 as $v9) { $v45[]=$this->_csvQuote($v9, $v41, $v42); } $v44.=implode($v40, $v45)."\r\n"; } return $v44; } public function csvDL($v4, $v46='result.csv', $v47='') { $this->csvDownload($this->csv($v4), $v46, $v47); } public function csvDownload($v48, $v46='result.csv', $v47='') { if(0==strlen($v48)) return false; if($v47) { $v48=$v47."\r\n".$v48; } header("Content-type: text/csv"); header('Content-Length: '.strlen($v48)); header("Content-Disposition: attachment; filename=$v46"); echo $v48; exit; } protected function _csvQuote($v49, $v50=false, $v42='""') { if(strpos($v49, '"')===false) { if($v50 or preg_match('/[,\r\n]/', $v49)) $v49='"'.$v49.'"'; } else { $v49='"'.str_replace('"',$v42,$v49).'"'; } return $v49; } public function getArray($v51, $v5=false) { if(!$this->v1) { $this->_text('nodb', __FUNCTION__); return false; } $v43=$this->query($v51, $v5); if($v43) { $v52=($this->resultType('both')===$v5) ? 3 : count($v43[0]); if($v52<=2) { $v53=array(); foreach($v43 as $v37) { if(1==$v52) { $v53[]=reset($v37); } else { $v53[reset($v37)]=end($v37); } } return $v53; } } return $v43; } public function rowsAffected() { return $this->v2; } private function _text($v54, $v55=false) { if('nodb'==$v54) { $this->_addError($v55.'() called without a database connection'); } } }
class dbMysqli extends dbSQLbase { public function __construct($v56=false) { if($v56) { $this->connect($v56); } } public function connect($v57=false, $v58=false) { if(is_array($v57)) { if(''==$v57[0]) { $v57[0]='localhost'; } if (@($v59 = mysqli_connect($v57[0], $v57[2], $v57[3], $v57[1]))) { $this->v1 = $v59; } else { $this->_addError($v58 ? $this->lastError() : 'Cannot connect to MySQLi'); } } elseif(is_object($v57) and 'mysqli'==get_class($v57)) { $this->v1 = $v57; } elseif(!$this->v1) { $this->_addError('No database connection'); } return $this->v1; } public function command($v4) { $v31=mysqli_query($this->v1, $v4); $this->v2=mysqli_affected_rows($this->v1); return $v31; } public function query($v4, $v5=false) { if(false===$v5) { $v5=MYSQLI_ASSOC; } elseif(is_string($v5)) { $v5=$this->resultType($v5); } $v43=array(); if ($v17=mysqli_query($this->v1, $v4)) { while($v37=mysqli_fetch_array($v17, $v5)) { $v43[]=$v37; } } return $v43; } protected function resultType($v6) { if(!$v6) { return MYSQLI_ASSOC; } switch(strtolower($v6{0})) { case 'n': $v60=MYSQLI_NUM; break; case 'b': $v60=MYSQLI_BOTH; break; case 'a': default: $v60=MYSQLI_ASSOC; } return $v60; } public function getIdent() { $v61=mysqli_insert_id($this->v1); return ($v61>0) ? $v61 : -1; } public function quote($v6) { return "'".mysqli_real_escape_string($this->v1, $v6)."'"; } public function lastError() { if($this->v1) { return mysqli_error($this->v1) ? mysqli_errno($this->v1).': '.mysqli_error($this->v1) : ''; } else { return mysqli_connect_errno().': '.mysqli_connect_error(); } } public function optimizeTables($v62=false, $v63=4096) { if(!$v62) { $v62 = $this->getTables(); } elseif(is_string($v62)) { $v62 = array_map('trim', explode(',', $v62)); } $v64 = $this->getArray('SHOW TABLE STATUS'); if($v64) { $v65=array(); $v66=0; foreach($v64 as $v37) { $v67=$v37['Name']; $v68=$v37['Data_free']; if(in_array($v67, $v62) and $v68 > $v63) { $v65[] = $v67; $v66 += $v68; } } if($v65) { if($this->command('OPTIMIZE TABLE '.implode(', ', $v65))) { return $v66; } } else { return 0; } } return false; } public function truncateTable($v7) { return $this->command('truncate table '.$v7); } public function getTables() { return $this->getArray('show tables'); } public function getFields($v7, $v8=false) { $v69=array(); $v70=$this->getArray('show columns from '.$v7); foreach($v70 as $v71) { if(preg_match('/([^(]+)\(([,0-9]+)\)/', $v71['Type'], $v72)) { $v54=trim($v72[1]); $v73=$v72[2]; } else { $v54=$v71['Type']; $v73=0; } $v74=(isset($v71['Extra'])) ? strtolower($v71['Extra']) : ''; $v69[$v71['Field']]=array('name'=>$v71['Field'], 'type'=>$v54, 'size'=>$v73, 'null'=>('YES'==$v71['Null']), 'ident'=>('auto_increment'==$v74)); } return ($v8) ? $v69 : array_keys($v69); } public function trimZeros($v9, $v10=3) { return 'TRIM(TRAILING "." FROM ( TRIM( TRAILING "0" FROM round('.$v9.', '.$v10.') ) ) )'; } protected function _getBackupFilename($v75, $v76) { $v77 = $this->getValue('select database()'); if(!$v77) { return false; } $v46 = $v77.'_'.($v75 ? 'part_' : '').date('Y-m-d').'.sql'; if($v76) { $v46 .= '.gz'; } return $v46; } public function getBackup($v75=false, $v76=true) { $v23 = 50; $v78 = false; $this->command('SET NAMES "utf8"'); $v79 = $this->getTables(); if(is_string($v75)) { if(strpos($v75, ',') === false) { $v75 = array($v75); } else { $v75 = array_map('trim', explode(',', $v75)); } } if(is_array($v75)) { $v79 = array_intersect($v79, $v75); } $v80 = ''; foreach($v79 as $v81) { $v82 = $this->query('select * from '.$v81, 'num'); $v83 = $v82 ? count($v82[0]) : 0; $v84 = count($v82); $v85 = $this->getRow('show create table '.$v81); $v85 = end($v85); $v80 .= "\n\n".$v85.";\n\n"; $v86 = 0; foreach($v82 as $v37) { if ($v86 % $v23 == 0 || $v86 == 0 ) { $v80 .= "\ninsert into ".$v81." values"; } $v80 .= "\n("; for($v87=0; $v87 < $v83; $v87++) { $v80 .= is_null($v37[$v87]) ? 'null' : $this->quote($v37[$v87]); if ($v87 < ($v83-1)) { $v80 .= ','; } } $v80 .= ')'; if ( (($v86+1) % $v23==0 && $v86!=0) || $v86+1==$v84) { $v80 .= ";"; } else { $v80 .= ","; } $v86++; } $v80 .="\n\n\n"; } if($v78) { echo '<pre>'.htmlspecialchars($v80).'</pre>'; die; } return $v80; } public function saveBackup($v88, $v75=false, $v76=true, $v89='') { $v46 = $this->_getBackupFilename($v75, $v76); if(!$v46) { return false; } $v46 = $v89.$v46; if(is_dir($v88) and is_writable($v88)) { if(substr($v88,-1) != '/') { $v88 .= '/'; } if(file_exists($v88.$v46)) { return -2; } } else { return -1; } $v82 = $this->getBackup($v75, $v76); if($v76) { $v90 = gzopen($v88.$v46, 'w9'); gzwrite($v90, $v82); gzclose($v90); } else { $v90 = fopen($v88.$v46, 'w'); fwrite($v90, $v82); fclose($v90); } return $v88.$v46; } public function downloadBackup($v75=false, $v76=true) { $v46 = $this->_getBackupFilename($v75, $v76); if(!$v46) { return false; } $v82 = $this->getBackup($v75, $v76); if($v76) { $v82 = gzencode($v82, 9); } header('Content-Type: application/octet-stream'); header('Content-Transfer-Encoding: Binary'); header('Content-Length: '.strlen($v82)); header('Content-disposition: attachment; filename="'.$v46.'"'); echo $v82; exit; } }
trait dbAuditing { private $v91=false;private $v92=false; private $v93, $v94, $v95; public function insert($v67, $v12) { return parent::insert($v67, $this->_addFields($v12)); } public function insertMulti($v67, $v22, $v23=20) { return parent::insertMulti($v67, $this->_addFields($v22, true), $v23); } public function insertSelect($v7, $v12, $v27, $v18) { return parent::insertSelect($v7, $this->_addFields($v12), $v27, $v18); } public function update($v7, $v12, $v18) { if($this->v1 and $this->v91) { $this->_audit($v7, $v18, false); } return parent::update($v7, $this->_addFields($v12), $v18); } public function delete($v7, $v96) { if($this->v1 and $this->v91) { $this->_audit($v7, $v96, true); } return parent::delete($v7, $v96); } public function getTables($v97=true) { $v98 = parent::getTables(); if(!$v97) { foreach($v98 as $v99=>$v81) { if($this->v93 and substr($v81, -strlen($this->v93)) === $this->v93) { unset($v98[$v99]); } } } return $v98; } public function setAuditing($v93='_audit', $v100='uid', $v95='tmst', $v101=0) { if(is_string($v100)) { if(strpos($v100, ',') === false) { $v100 = array($v100); } else { $v100 = array_map('trim', explode(',', $v100)); } } if(is_int($v101)) { $v101 = array($v101); } elseif(is_string($v101)) { if(strpos($v101, ',') === false) { $v101 = array($v101); } else { $v101 = array_map('trim', explode(',', $v101)); } } foreach(array($v93, implode('', $v100), $v95) as $v55) { if(!is_string($v55) or !preg_match('/^[_a-z][_a-z0-9]*$/i', $v55)) { $this->_addError('All setAuditing() parameters must be simple identifiers'); return false; } } while(count($v100) > count($v101)) { $v101[]=0; } $this->v94 = array(); foreach($v100 as $v99=>$v102) { $this->v94[$v102] = (int)$v101[$v99]; } $this->v93=$v93; $this->v95=$v95; $this->v91=true; $this->v92=true; return true; } public function doAuditTrail($v103=null) { if(!$this->v92) { $this->_addError('doAuditTrail() cannot be called before setAuditing()'); return null; } elseif(is_bool($v103)) { $this->v91=$v103; } else { return $this->v91; } } private function _addFields($v12, $v104=false) { if($this->v92) { if($v104) { foreach(array_keys($v12) as $v99) { foreach($this->v94 as $v87=>$v29) { $v12[$v99][$v87]=$v29; } $v12[$v99][$this->v95]=date('Y-m-d H:i:s'); } } else { foreach($this->v94 as $v87=>$v29) { $v12[$v87]=$v29; } $v12[$this->v95]=date('Y-m-d H:i:s'); } } return $v12; } private function _audit($v7, $v18, $v105) { if ($v105) { $this->command('insert into '.$v7.$this->v93.' select * from '.$v7.' where '.$v18); $v106=''; foreach($this->v94 as $v87=>$v29) { $v106 .= "$v87=$v29, "; } $this->command('update '.$v7.' set '.$v106.$this->v95.'="'.date('Y-m-d H:i:s').'" where '.$v18); } $this->command('insert into '.$v7.$this->v93.' select * from '.$v7.' where '.$v18); } public function checkAuditing($v107=true, $v108=false) { if(!$this->v92) { return false; } $v109=array(); $v67=$this->getTables(); foreach($v67 as $v110) { $v111=$this->getFields($v110, $v107); if($v111 and isset($v111[0])) { $v112=array(); foreach($v111 as $v15) { $v112[$v15]=array('name'=>$v15); } $v12[$v110]=$v112; if($v107) { $v109[]='Field types requested but not provided - checking names only'; $v107=false; } } else { $v12[$v110]=$v111; } } foreach($v67 as $v110) { if(strpos($v110, $this->v93)===false) { if(isset($v12[$v110.$this->v93])) { if(array_keys($v12[$v110.$this->v93])==array_keys($v12[$v110])) { if($v108) $v109[]=$v110.' field names match with '.$v110.$this->v93.' - OK'; if($v107) { foreach($v12[$v110] as $v15=>$v113) { $v114=$v113['type']; $v115=$v113['size']; $v116=$v113['null'] ? 'NULL' : 'NOT NULL'; $v117=$v12[$v110.$this->v93][$v15]['type']; $v118=$v12[$v110.$this->v93][$v15]['size']; $v119=$v12[$v110.$this->v93][$v15]['null'] ? 'NULL' : 'NOT NULL'; if($v114.$v115.$v116 != $v117.$v118.$v119) { $v109[]="$v110.$v15 $v115 $v114 $v116 -vs- $v110$this->v93.$v15 $v118 $v117 $v119"; } } } } else { $v109[]='Field names in '.$v110.' differ from those in '.$v110.$this->v93; if($v108) { $v109[]='.. '.$v110.': '.implode(',', array_keys($v12[$v110])); $v109[]='.. '.$v110.$this->v93.': '.implode(',', array_keys($v12[$v110.$this->v93])); } } } else { $v109[]=$v110.' has no audit table'; } foreach(array_keys($this->v94) as $v120) { if(!isset($v12[$v110][$v120])) { $v109[]=$v110.'.'.$v120.' field is missing (UserIdField)'; } } if(!isset($v12[$v110][$this->v95])) { $v109[]=$v110.'.'.$this->v95.' field is missing (TmstField)'; } } } return $v109; } public function deletedList($v81, $v121, $v122=null, $v123=null) { $v124 = $v81.$this->v93; $v125 = "max($v124.$this->v95)"; if($v122) { if($v123) { $v126 = $v125.' between "'.$v122.'" and "'.$v123.'"'; } else { $v126 = $v125.' >= "'.$v122.'"'; } } elseif($v123) { $v126 = $v125.' <= "'.$v123.'"'; } else { $v126 = ''; } if($v126) { $v126 = 'having '.$v126; } $v4 = "select tblaud.* from $v124 as tblaud inner join (select tblaud.$v121, max(tblaud.$this->v95) as twzDelDate from $v124 as tblaud" ." left join $v81 as tblmain on tblmain.$v121=tblaud.$v121 where tblmain.$v121 is null group by tblaud.$v121 $v126) as subq" ." on subq.$v121 = tblaud.$v121 and subq.twzDelDate = tblaud.$this->v95" ." group by tblaud.$v121 " ." order by tblaud.$this->v95 desc"; return $this->getArray($v4); } public function deletedInfo($v81, $v127, $v128=null, $v129=2) { $v129 = (int)$v129; if(preg_match('/^([^=]+)=(.+)$/', $v127, $v72)) { $v130 = trim($v72[1]); $v131 = $v72[2]; } else { return false; } if($v128) { if(strpos($v128, '.')) { list($v132, $v133) = explode('.', $v128, 2); } else { $v132 = $v128; $v133 = $v130; } } $v124 = $v81.$this->v93; $v4="select tblaud.* from $v124 as tblaud inner join (select tblaud.$v130, max(tblaud.$this->v95) as twzDelDate from $v124 as tblaud" ." left join $v81 as tblmain on tblmain.$v130=tblaud.$v130 where tblaud.$v127 and tblmain.$v130 is null group by tblaud.$v130) as subq" ." on subq.$v130 = tblaud.$v130 and subq.twzDelDate = tblaud.$this->v95" ." group by tblaud.$v130" ." order by tblaud.$this->v95 desc"; $v134 = $this->getRow($v4); if($v134) { reset($this->v94); $v94 = key($this->v94); $v31 = array( 'row'=>$v134, 'deletedOn' => $v134[$this->v95], 'deletedBy' => $v134[$v94], ); if($v128) { $v4 = 'select * from '.$v132.$this->v93.' where '.$v133.'='.$v131 .' and ABS(TIMESTAMPDIFF(SECOND, "'.$v134[$this->v95].'", tmst)) <= '.$v129; $v31[$v132.'-rows'] = $this->getArray($v4); } return $v31; } else { return false; } } public function deletedRestore($v81, $v127, $v128=null, $v129=2) { $v135 = $this->deletedInfo($v81, $v127, $v128, $v129); if($v135) { $v136 = $v135['row']; reset($this->v94); $v94 = key($this->v94); unset($v136[$v94]); unset($v136[$this->v95]); if($this->insert($v81, $v136)) { foreach($v135 as $v137=>$v138) { if(preg_match('/^(.+)-rows$/', $v137, $v72)) { foreach($v138 as $v139) { unset($v139[$v94]); unset($v139[$this->v95]); $this->insert($v72[1], $v139); } } } return true; } } return false; } }
class dbMysqliA extends dbMysqli { use dbAuditing; }
?>
|
Download
