abstract.class.php
Go to the documentation of this file.
00001 <?php 00010 abstract class db_abstract extends object { 00011 00017 protected $connection; 00018 00024 public function getInstanceCfg() { 00025 return $this->cfg->getInstanceCfg; 00026 } 00027 00034 public function getConnection() { 00035 $this->_connect(); 00036 return $this->connection; 00037 } 00038 00045 public function query($sql, array $bind=array()) { 00046 $this->_connect(); 00047 $stmt = $this->prepare($sql); 00048 db::log($sql, $bind); 00049 $stmt->execute($bind); 00050 return $stmt; 00051 } 00052 00058 public function beginTransaction() { 00059 $this->_connect(); 00060 $this->_beginTransaction(); 00061 return true; 00062 } 00063 00069 public function commit() { 00070 $this->_connect(); 00071 $this->_commit(); 00072 return true; 00073 } 00074 00080 public function rollBack() { 00081 $this->_connect(); 00082 $this->_rollBack(); 00083 return true; 00084 } 00085 00094 public function insert(array $prm) { 00095 if (config::initTab($prm, Array( 00096 'table'=>null, 00097 'values'=>null 00098 ))) { 00099 00100 $cols = array_map(array($this, 'quoteIdentifier'), array_keys($prm['values'])); 00101 $vals = count($cols) > 0? array_fill(0, count($cols), '?') : array(); 00102 00103 $sql = 'INSERT INTO '.$this->quoteIdentifier($prm['table']); 00104 $sql.= ' ('.implode(',',$cols).') VALUES ('.implode(',',$vals).')'; 00105 00106 $stmt = $this->query($sql, array_values($prm['values'])); 00107 return $this->lastInsertId(); 00108 } else 00109 throw new nException('db_abstract - insert: The table or the values is missing.'); 00110 } 00111 00120 public function replace(array $prm) { 00121 if (config::initTab($prm, Array( 00122 'table'=>null, 00123 'values'=>null 00124 ))) { 00125 00126 $cols = array_map(array($this, 'quoteIdentifier'), array_keys($prm['values'])); 00127 $vals = array_fill(0, count($cols), '?'); 00128 00129 $sql = 'REPLACE INTO '.$this->quoteIdentifier($prm['table']); 00130 $sql.= ' ('.implode(',',$cols).') VALUES ('.implode(',',$vals).')'; 00131 00132 $stmt = $this->query($sql, array_values($prm['values'])); 00133 return $this->lastInsertId(); 00134 } else 00135 throw new nException('db_abstract - replace: The table or the values is missing.'); 00136 } 00137 00149 public function update(array $prm) { 00150 if (config::initTab($prm, Array( 00151 'table'=>null, 00152 'values'=>null, 00153 'where'=>'', 00154 'whereOp'=>'AND' 00155 ))) { 00156 00157 $set = array(); 00158 foreach($prm['values'] as $col=>$val) 00159 $set[] = $this->quoteIdentifier($col).'=?'; 00160 00161 $sql = 'UPDATE '.$this->quoteIdentifier($prm['table']); 00162 $sql.= ' SET '.implode(',',$set); 00163 $sql.= $this->makeWhere($prm['where'], $prm['whereOp']); 00164 $stmt = $this->query($sql, array_values($prm['values'])); 00165 return $stmt->rowCount(); 00166 } else 00167 throw new nException('db_abstract - update : The table or the values is missing.'); 00168 } 00169 00180 public function delete(array $prm) { 00181 if (config::initTab($prm, Array( 00182 'table'=>null, 00183 'where'=>'', 00184 'whereOp'=>'AND', 00185 'optim'=>true 00186 ))) { 00187 00188 $sql = 'DELETE FROM '.$this->quoteIdentifier($prm['table']) 00189 .$this->makeWhere($prm['where'], $prm['whereOp']); 00190 00191 $stmt = $this->query($sql); 00192 $nb = $stmt->rowCount(); 00193 00194 if ($prm['optim']) 00195 $this->optimize($prm['table']); 00196 00197 return $nb; 00198 } else 00199 throw new nException('db_abstract - delete : The table is missing.'); 00200 } 00201 00210 public function makeWhere($where, $whereOp='AND', $incWhere=true) { 00211 $query = null; 00212 if (!empty($where)) { 00213 if ($where instanceof db_where) 00214 $query = $where->toString(); 00215 else if (is_array($where)) { 00216 $tmp = array(); 00217 $where = array_filter($where); 00218 if (empty($where)) 00219 return $query; 00220 foreach($where as $k=>$v) { 00221 $tmp[] = is_numeric($k) ? $v : $this->quoteIdentifier($k).'="'.$v.'"'; 00222 } 00223 $query = '('.implode(' '.$whereOp.' ', $tmp).')'; 00224 } else 00225 $query = $where; 00226 $query = ($incWhere && $query? ' WHERE ' : null).$query; 00227 } 00228 return $query; 00229 } 00230 00236 public function getFetchMode() { 00237 return $this->cfg->fetchMode; 00238 } 00239 00245 public function setFetchMode($mode) { 00246 $this->cfg->fetchMode = (int) $mode; 00247 } 00248 00273 public function selectQuery(array $prm) { 00274 if (config::initTab($prm, array( 00275 'fields'=>'*', 00276 'i18nFields'=>'', 00277 'table'=>null, 00278 'join'=>'', 00279 'bind'=>array(), 00280 'bindData'=>false, 00281 'where'=>'', 00282 'whereOp'=>'AND', 00283 'order'=>'', 00284 'start'=>0, 00285 'nb'=>'', 00286 'group'=>'', 00287 'groupAfter'=>'', 00288 'having'=>'' 00289 ))) { 00290 00291 $table = db::get('table', $prm['table']); 00292 $tableName = $this->quoteIdentifier($prm['table']); 00293 if (is_array($prm['fields'])) { 00294 $f = implode(',', array_map(array($this, 'quoteIdentifier'), $prm['fields'])); 00295 } else { 00296 if (strpos($prm['fields'], $this->cfg->quoteIdentifier) === false) { 00297 $f = implode(',', array_map(array($this, 'quoteIdentifier'), explode(',', $prm['fields']))); 00298 } else 00299 $f = $prm['fields']; 00300 } 00301 00302 if (!empty($prm['i18nFields'])) { 00303 $i18nTable = db::get('table', $prm['table'].db::getCfg('i18n')); 00304 $i18nTableName = $this->quoteIdentifier($i18nTable->getRawName()); 00305 $primary = $i18nTable->getPrimary(); 00306 $prm['join'][] = array( 00307 'table'=>$i18nTable->getRawName(), 00308 'on'=>$tableName.'.'.$table->getIdent().'='.$i18nTableName.'.'.$primary[0]. 00309 ' AND '.$i18nTableName.'.'.$primary[1].'="'.request::get('lang').'"' 00310 ); 00311 if (is_array($prm['i18nFields'])) { 00312 array_walk($prm['i18nFields'], array($this, 'quoteIdentifier')); 00313 $f.= ','.$i18nTableName.'.'.implode(','.$i18nTableName.'.', $prm['fields']); 00314 } else if ($prm['i18nFields']) { 00315 foreach(explode(',', $prm['i18nFields']) as $t) { 00316 $f.= ','.$i18nTableName.'.'.$t; 00317 } 00318 } 00319 } 00320 00321 $query = 'SELECT '.$f.' FROM '.$tableName; 00322 00323 $tblAlias = array(); 00324 if (is_array($prm['join'])) { 00325 $join = array(); 00326 foreach($prm['join'] as &$v) { 00327 $v = array_merge(array('dir'=>'left', 'on'=>1, 'alias'=>''), $v); 00328 $alias = null; 00329 if (!empty($v['alias'])) { 00330 $alias = ' AS '.$this->quoteIdentifier($v['alias']); 00331 if ($v['table'] != $table->getRawName()) 00332 $tblAlias[$v['table']] = $v['alias']; 00333 } 00334 $join[] = strtoupper($v['dir']).' JOIN '.$this->quoteIdentifier($v['table']).$alias.' ON '.$v['on']; 00335 } 00336 $query.= ' '.implode(' ', $join).' '; 00337 } 00338 00339 $query.= $this->makeWhere($prm['where'], $prm['whereOp']); 00340 00341 if (!empty($prm['group'])) 00342 $query.= ' GROUP BY '.$prm['group']; 00343 00344 if (!empty($prm['having'])) 00345 $query.= ' HAVING '.$prm['having']; 00346 00347 if (!empty($prm['order'])) 00348 $query.= ' ORDER BY '.$prm['order']; 00349 00350 if (!empty($prm['nb'])) { 00351 if (empty($prm['start'])) 00352 $prm['start'] = 0; 00353 $query.= ' LIMIT '.$prm['start'].','.$prm['nb']; 00354 } 00355 00356 if ($prm['bindData'] && !empty($prm['bind']) && is_array($prm['bind'])) { 00357 $tmp = explode('?', $query, count($prm['bind'])+1); 00358 array_splice($prm['bind'], count($tmp)); 00359 00360 $query = ''; 00361 while($tmp2 = array_shift($tmp)) { 00362 $query.= $tmp2.array_shift($prm['bind']); 00363 } 00364 } 00365 00366 if ($prm['groupAfter']) 00367 $query = 'SELECT * FROM ('.$query.') AS res GROUP BY '.$prm['groupAfter']; 00368 00369 return $this->tableAlias($query, $tblAlias); 00370 } else 00371 throw new nException('db_abstract - selectQuery : The table is missing.'); 00372 } 00373 00381 protected function tableAlias($query, array $tblAlias) { 00382 $search = array(); 00383 $replace = array(); 00384 foreach($tblAlias as $tbl=>$alias) { 00385 $search = array_merge($search, array( 00386 ' '.$tbl.'.', 00387 ' '.$this->quoteIdentifier($tbl).'.', 00388 '('.$this->quoteIdentifier($tbl).'.' 00389 )); 00390 $replace = array_merge($replace, array( 00391 ' '.$alias.'.', 00392 ' '.$this->quoteIdentifier($alias).'.', 00393 '('.$this->quoteIdentifier($alias).'.' 00394 )); 00395 } 00396 return str_replace($search, $replace, $query); 00397 } 00398 00406 public function select($prm) { 00407 if (is_array($prm)) { 00408 config::initTab($prm, array( 00409 'bind'=>array(), 00410 'forceFetchMode'=>0, 00411 )); 00412 $stmt = $this->query($this->selectQuery($prm), $prm['bind']); 00413 $fetchMode = $prm['forceFetchMode'] ? $prm['forceFetchMode'] : $this->cfg->fetchMode; 00414 } else { 00415 $stmt = $this->query($prm); 00416 $fetchMode = $this->cfg->fetchMode; 00417 } 00418 00419 if ($fetchMode == PDO::FETCH_CLASS) { 00420 $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC); 00421 return $tmp; 00422 00423 $cfg = array( 00424 'db'=>$this, 00425 'table'=>$prm['table'], 00426 'props'=>array_keys($tmp[0]) 00427 ); 00428 foreach($tmp as $t) { 00429 $row = factory::get($className, $cfg); 00430 $row->setValues($t); 00431 $ret[] = $row; 00432 } 00433 return $ret; 00434 } else { 00435 return $stmt->fetchAll($fetchMode); 00436 } 00437 } 00438 00446 public function count(array $prm) { 00447 $subQuery = $this->selectQuery(array_merge($prm, array('bindData'=>true))); 00448 $stmt = $this->query('SELECT COUNT(*) AS count FROM ('.$subQuery.') AS subquerycount'); 00449 $tmp = $stmt->fetch(MYSQL_ASSOC); 00450 $count = $tmp['count']; 00451 $stmt->fetchAll(); 00452 $stmt->closeCursor(); 00453 $stmt = null; 00454 return $count; 00455 } 00456 00471 public function fetchAssoc(array $prm) { 00472 $prm['forceFetchMode'] = PDO::FETCH_ASSOC; 00473 return $this->select($prm); 00474 } 00475 00486 public function fetchPairs(array $prm) { 00487 $prm['forceFetchMode'] = PDO::FETCH_NUM; 00488 return $this->select($prm); 00489 } 00490 00499 public function fetchRow(array $prm) { 00500 $prm['nb'] = 1; 00501 $prm['start'] = 0; 00502 return array_pop($this->select($prm)); 00503 } 00504 00511 public function quoteIdentifier($ident) { 00512 if (strpos($ident, '(') !== false) 00513 return $ident; 00514 $tmpSpace = explode(' ', $ident); 00515 $tmp = explode('.', $tmpSpace[0]); 00516 if (count($tmp) == 1 && $tmp[0] == '*') 00517 return '*'; 00518 else if (count($tmp) == 2 && $tmp[1] == '*') 00519 return $this->cfg->quoteIdentifier.$tmp[0].$this->cfg->quoteIdentifier.'.*'; 00520 $tmpSpace[0] = $this->cfg->quoteIdentifier 00521 .implode($this->cfg->quoteIdentifier.'.'.$this->cfg->quoteIdentifier, $tmp) 00522 .$this->cfg->quoteIdentifier; 00523 return implode(' ', $tmpSpace); 00524 } 00525 00532 public function quoteValue($value) { 00533 return $this->cfg->quoteValue.addcslashes($value, $this->cfg->quoteValue).$this->cfg->quoteValue; 00534 } 00535 00541 public function optimize($table) { 00542 $this->query('OPTIMIZE TABLE '.$table); 00543 } 00544 00554 public function getTablesWith(array $prm) { 00555 $tmp = array_fill(0, 3, ''); 00556 00557 if (array_key_exists('start', $prm)) 00558 $tmp[0] = $prm['start']; 00559 00560 if (array_key_exists('contains', $prm)) 00561 $tmp[1] = $prm['contains']; 00562 00563 if (array_key_exists('end', $prm)) 00564 $tmp[2]= $prm['end']; 00565 00566 $regex = '/^'.implode('(.*)', $tmp).'$/'; 00567 00568 return array_merge(array_filter($this->getTables(), 00569 create_function('$val', 'return preg_match("'.$regex.'", $val);'))); 00570 } 00571 00578 public function getI18nTable($table) { 00579 $tmp = $this->getTablesWith(array( 00580 'end'=>$table.db::getCfg('i18n') 00581 )); 00582 if (count($tmp) == 1) 00583 return $tmp[0]; 00584 return null; 00585 } 00586 00593 public function getWhere(array $prm = array()) { 00594 return factory::get('db_where', array_merge(array( 00595 'db'=>$this 00596 ), $prm)); 00597 } 00598 00604 public function getCache() { 00605 return cache::getInstance($this->cfg->cache); 00606 } 00607 00613 public function __sleep() { 00614 return array('cfg'); 00615 } 00616 00627 abstract public function getTables($unPrefix = true); 00628 00635 abstract public function prefixTable($table); 00636 00643 abstract public function fields($table); 00644 00648 abstract protected function _connect(); 00649 00655 abstract public function closeConnection(); 00656 00663 abstract public function prepare($sql); 00664 00670 abstract public function lastInsertId(); 00671 00675 abstract protected function _beginTransaction(); 00676 00680 abstract protected function _commit(); 00681 00685 abstract protected function _rollBack(); 00686 00687 }
