db/abstract.class.php

Go to the documentation of this file.

<?php
abstract class db_abstract extends object {

        protected $connection;

        public function getInstanceCfg() {
                return $this->cfg->getInstanceCfg;
        }

        public function getConnection() {
                $this->_connect();
                return $this->connection;
        }

        public function query($sql, array $bind=array()) {
                $this->_connect();
                $stmt = $this->prepare($sql);
                db::log($sql, $bind);
                $stmt->execute($bind);
                return $stmt;
        }

        public function beginTransaction() {
                $this->_connect();
                $this->_beginTransaction();
                return true;
        }

        public function commit() {
                $this->_connect();
                $this->_commit();
                return true;
        }

        public function rollBack() {
                $this->_connect();
                $this->_rollBack();
                return true;
        }

    public function insert(array $prm) {
                if (config::initTab($prm, Array(
                                        'table'=>null,
                                        'values'=>null
                                ))) {

                        $cols = array_map(array($this, 'quoteIdentifier'), array_keys($prm['values']));
                        $vals = count($cols) > 0? array_fill(0, count($cols), '?') : array();

                        $sql = 'INSERT INTO '.$this->quoteIdentifier($prm['table']);
                        $sql.= ' ('.implode(',',$cols).') VALUES ('.implode(',',$vals).')';

                $stmt = $this->query($sql, array_values($prm['values']));
                return $this->lastInsertId();
                } else
                        throw new nException('db_abstract - insert: The table or the values is missing.');
    }

    public function replace(array $prm) {
                if (config::initTab($prm, Array(
                                        'table'=>null,
                                        'values'=>null
                                ))) {

                        $cols = array_map(array($this, 'quoteIdentifier'), array_keys($prm['values']));
                        $vals = array_fill(0, count($cols), '?');

                        $sql = 'REPLACE INTO '.$this->quoteIdentifier($prm['table']);
                        $sql.= ' ('.implode(',',$cols).') VALUES ('.implode(',',$vals).')';

                $stmt = $this->query($sql, array_values($prm['values']));
                return $this->lastInsertId();
                } else
                        throw new nException('db_abstract - replace: The table or the values is missing.');
    }

        public function update(array $prm) {
                if (config::initTab($prm, Array(
                                'table'=>null,
                                'values'=>null,
                                'where'=>'',
                                'whereOp'=>'AND'
                        ))) {

                        $set = array();
                        foreach($prm['values'] as $col=>$val)
                                $set[] = $this->quoteIdentifier($col).'=?';

                        $sql = 'UPDATE '.$this->quoteIdentifier($prm['table']);
                        $sql.= ' SET '.implode(',',$set);
                        $sql.= $this->makeWhere($prm['where'], $prm['whereOp']);
                $stmt = $this->query($sql, array_values($prm['values']));
                return $stmt->rowCount();
                } else
                        throw new nException('db_abstract - update : The table or the values is missing.');
    }

    public function delete(array $prm) {
                if (config::initTab($prm, Array(
                                        'table'=>null,
                                        'where'=>'',
                                        'whereOp'=>'AND',
                                        'optim'=>true
                                ))) {

                        $sql = 'DELETE FROM '.$this->quoteIdentifier($prm['table'])
                                                .$this->makeWhere($prm['where'], $prm['whereOp']);

                        $stmt = $this->query($sql);
                        $nb = $stmt->rowCount();

                        if ($prm['optim'])
                                $this->optimize($prm['table']);

                        return $nb;
                } else
                        throw new nException('db_abstract - delete : The table is missing.');
    }

    public function makeWhere($where, $whereOp='AND', $incWhere=true) {
        $query = null;
                if (!empty($where)) {
                        if ($where instanceof db_where)
                                $query = $where->toString();
                        else if (is_array($where)) {
                                $tmp = array();
                                $where = array_filter($where);
                                if (empty($where))
                                        return $query;
                                foreach($where as $k=>$v) {
                                        $tmp[] = is_numeric($k) ? $v : $this->quoteIdentifier($k).'="'.$v.'"';
                                }
                                $query = '('.implode(' '.$whereOp.' ', $tmp).')';
                        } else
                                $query = $where;
                        $query = ($incWhere && $query? ' WHERE ' : null).$query;
                }
                return $query;
    }

        public function getFetchMode() {
                return $this->cfg->fetchMode;
        }

        public function setFetchMode($mode) {
                $this->cfg->fetchMode = (int) $mode;
        }

        public function selectQuery(array $prm) {
                if (config::initTab($prm, array(
                                        'fields'=>'*',
                                        'i18nFields'=>'',
                                        'table'=>null,
                                        'moreTables'=>false,
                                        'join'=>'',
                                        'bind'=>array(),
                                        'bindData'=>false,
                                        'where'=>'',
                                        'whereOp'=>'AND',
                                        'order'=>'',
                                        'start'=>0,
                                        'nb'=>'',
                                        'group'=>'',
                                        'groupAfter'=>'',
                                        'having'=>''
                                ))) {

                        $table = db::get('table', $prm['table']);
                        $tableName = $this->quoteIdentifier($prm['table']);
                        if (is_array($prm['fields'])) {
                                $f = implode(',', array_map(array($this, 'quoteIdentifier'), $prm['fields']));
                        } else {
                                if (strpos($prm['fields'], $this->cfg->quoteIdentifier) === false) {
                                        $f = implode(',', array_map(array($this, 'quoteIdentifier'), explode(',', $prm['fields'])));
                                } else
                                        $f = $prm['fields'];
                        }

                        if (!empty($prm['i18nFields'])) {
                                $i18nTable = db::get('table', $prm['table'].db::getCfg('i18n'));
                                $i18nTableName = $this->quoteIdentifier($i18nTable->getRawName());
                                $primary = $i18nTable->getPrimary();
                                $prm['join'][] = array(
                                        'table'=>$i18nTable->getRawName(),
                                        'on'=>$tableName.'.'.$table->getIdent().'='.$i18nTableName.'.'.$primary[0].
                                                        ' AND '.$i18nTableName.'.'.$primary[1].'="'.request::get('lang').'"'
                                );
                                if (is_array($prm['i18nFields'])) {
                                        array_walk($prm['i18nFields'], array($this, 'quoteIdentifier'));
                                        $f.= ','.$i18nTableName.'.'.implode(','.$i18nTableName.'.', $prm['fields']);
                                } else if ($prm['i18nFields']) {
                                        foreach(explode(',', $prm['i18nFields']) as $t) {
                                                $f.= ','.$i18nTableName.'.'.$t;
                                        }
                                }
                        }

                        $query = 'SELECT '.$f.' FROM '.$tableName;
                        
                        if ($prm['moreTables']) {
                                if (is_array($prm['moreTables'])) {
                                        foreach($prm['moreTables'] as $k=>$v) {
                                                if (is_numeric($k)) {
                                                        $query.= ', '.$this->quoteIdentifier($v);
                                                } else {
                                                        $query.= ', '.$this->quoteIdentifier($v).' '.$this->quoteIdentifier($k);
                                                }
                                        }
                                } else {
                                        $query.= ', '.$prm['moreTables'];
                                }
                        }

                        $tblAlias = array();
                        if (is_array($prm['join'])) {
                                $join = array();
                                foreach($prm['join'] as &$v) {
                                        $v = array_merge(array('dir'=>'left', 'on'=>1, 'alias'=>''), $v);
                                        $alias = null;
                                        if (!empty($v['alias'])) {
                                                $alias = ' AS '.$this->quoteIdentifier($v['alias']);
                                                if ($v['table'] != $table->getRawName())
                                                        $tblAlias[$v['table']] = $v['alias'];
                                        }
                                        $join[] = strtoupper($v['dir']).' JOIN '.$this->quoteIdentifier($v['table']).$alias.' ON '.$v['on'];
                                }
                                $query.= ' '.implode(' ', $join).' ';
                        }

                        $query.= $this->makeWhere($prm['where'], $prm['whereOp']);

                        if (!empty($prm['group']))
                                $query.= ' GROUP BY '.$prm['group'];

                        if (!empty($prm['having']))
                                $query.= ' HAVING '.$prm['having'];

                        if (!empty($prm['order']))
                                $query.= ' ORDER BY '.$prm['order'];

                        if (!empty($prm['nb'])) {
                                if (empty($prm['start']))
                                        $prm['start'] = 0;
                                $query.= ' LIMIT '.$prm['start'].','.$prm['nb'];
                        }

                        if ($prm['bindData'] && !empty($prm['bind']) && is_array($prm['bind'])) {
                                $tmp = explode('?', $query, count($prm['bind'])+1);
                                array_splice($prm['bind'], count($tmp));

                                $query = '';
                                while($tmp2 = array_shift($tmp)) {
                                        $query.= $tmp2.array_shift($prm['bind']);
                                }
                        }

                        if ($prm['groupAfter'])
                                $query = 'SELECT * FROM ('.$query.') AS res GROUP BY '.$prm['groupAfter'];

                        return $this->tableAlias($query, $tblAlias);
                } else
                        throw new nException('db_abstract - selectQuery : The table is missing.');
        }

        protected function tableAlias($query, array $tblAlias) {
                $search = array();
                $replace = array();
                foreach($tblAlias as $tbl=>$alias) {
                        $search = array_merge($search, array(
                                ' '.$tbl.'.',
                                ' '.$this->quoteIdentifier($tbl).'.',
                                '('.$this->quoteIdentifier($tbl).'.'
                        ));
                        $replace = array_merge($replace, array(
                                ' '.$alias.'.',
                                ' '.$this->quoteIdentifier($alias).'.',
                                '('.$this->quoteIdentifier($alias).'.'
                        ));
                }
                return str_replace($search, $replace, $query);
        }

        public function select($prm) {
                if (is_array($prm)) {
                        config::initTab($prm, array(
                                        'bind'=>array(),
                                        'forceFetchMode'=>0,
                                ));
                        $stmt = $this->query($this->selectQuery($prm), $prm['bind']);
                        $fetchMode = $prm['forceFetchMode'] ? $prm['forceFetchMode'] : $this->cfg->fetchMode;
                } else {
                        $stmt = $this->query($prm);
                        $fetchMode = $this->cfg->fetchMode;
                }

                if ($fetchMode == PDO::FETCH_CLASS) {
                        $tmp = $stmt->fetchAll(PDO::FETCH_ASSOC);
                        return $tmp;

                        $cfg = array(
                                'db'=>$this,
                                'table'=>$prm['table'],
                                'props'=>array_keys($tmp[0])
                        );
                        foreach($tmp as $t) {
                                $row = factory::get($className, $cfg);
                                $row->setValues($t);
                                $ret[] = $row;
                        }
                        return $ret;
                } else {
                        return $stmt->fetchAll($fetchMode);
                }
        }

        public function count(array $prm) {
                $subQuery = $this->selectQuery(array_merge($prm, array('bindData'=>true)));
                $stmt = $this->query('SELECT COUNT(*) AS count FROM ('.$subQuery.') AS subquerycount');
                $tmp = $stmt->fetch(PDO::FETCH_ASSOC);
                $count = $tmp['count'];
                $stmt->fetchAll();
                $stmt->closeCursor();
                $stmt = null;
                return $count;
        }

        public function fetchAssoc(array $prm) {
                $prm['forceFetchMode'] = PDO::FETCH_ASSOC;
                return $this->select($prm);
        }

        public function fetchPairs(array $prm) {
                $prm['forceFetchMode'] = PDO::FETCH_NUM;
                return $this->select($prm);
        }

        public function fetchRow(array $prm) {
                $prm['nb'] = 1;
                $prm['start'] = 0;
                return array_pop($this->select($prm));
        }

        public function quoteIdentifier($ident) {
                if (strpos($ident, '(') !== false)
                        return $ident;
                $tmpSpace = explode(' ', $ident);
                $tmp = explode('.', $tmpSpace[0]);
                if (count($tmp) == 1 && $tmp[0] == '*')
                        return '*';
                else if (count($tmp) == 2 && $tmp[1] == '*')
                        return $this->cfg->quoteIdentifier.$tmp[0].$this->cfg->quoteIdentifier.'.*';
                $tmpSpace[0] = $this->cfg->quoteIdentifier
                        .implode($this->cfg->quoteIdentifier.'.'.$this->cfg->quoteIdentifier, $tmp)
                        .$this->cfg->quoteIdentifier;
                return implode(' ', $tmpSpace);
        }

        public function quoteValue($value) {
                return $this->cfg->quoteValue.addcslashes($value, $this->cfg->quoteValue).$this->cfg->quoteValue;
        }

        public function optimize($table) {
                $this->query('OPTIMIZE TABLE '.$table);
        }

        public function getTablesWith(array $prm) {
                $tmp = array_fill(0, 3, '');

                if (array_key_exists('start', $prm))
                        $tmp[0] = $prm['start'];

                if (array_key_exists('contains', $prm))
                        $tmp[1] = $prm['contains'];

                if (array_key_exists('end', $prm))
                        $tmp[2]= $prm['end'];

                $regex = '/^'.implode('(.*)', $tmp).'$/';

                return array_merge(array_filter($this->getTables(),
                        create_function('$val', 'return preg_match("'.$regex.'", $val);')));
        }

        public function getI18nTable($table) {
                $tmp = $this->getTablesWith(array(
                        'end'=>$table.db::getCfg('i18n')
                ));
                if (count($tmp) == 1)
                        return $tmp[0];
                return null;
        }

        public function getWhere(array $prm = array()) {
                return factory::get('db_where', array_merge(array(
                        'db'=>$this
                ), $prm));
        }

        public function getCache() {
                return cache::getInstance($this->cfg->cache);
        }
        
        public function __sleep() {
                return array('cfg');
        }

        abstract public function getTables($unPrefix = true);

        abstract public function prefixTable($table);

        abstract public function fields($table);

        abstract protected function _connect();

        abstract public function closeConnection();

        abstract public function prepare($sql);

        abstract public function lastInsertId();

        abstract protected function _beginTransaction();

        abstract protected function _commit();

        abstract protected function _rollBack();

}