A take on DB Abstraction

Question

It's a little bit more code but I wanted to show the full class. I highlight the points I'd like input after the source.

I've cut comments since they where not in English and translated the important ones.

The class is inspired by PearDb (too old) and Zend_DB (to cluttered / unfinished at the time) and is used in a inhouse application. I know it's not ideal to write your own database handler (I'd go so far to say it's pretty pointless).

<?php

class DbStatement {

    private $oStatement;
    private $aFieldnames = array();
    private $aResultRow = array();
    private $aResultSet = array();
    private $bMetadata = true;
    private $bPreparedFetch = false;
    private $iNumRows = false;
    private $sQuery;
    private $aArgs;
    private $fQuerytime;

    /**
     * @throws DbException
     * @param mysqli $oDb Datenbankverbindung
     * @param string $sQuery
     */
    public function __construct(mysqli $oDb, $sQuery) {
        $this->oStatement = $oDb->prepare($sQuery);
        $this->sQuery = $sQuery;
        if($this->oStatement === false) {
            switch($oDb->errno) {
                case 1054:
                    throw new DbNoSuchFieldException($oDb->error, $oDb->errno);
                case 1146:
                    throw new DbNoSuchTableException($oDb->error, $oDb->errno);
                default:
                    throw new DbException(
                        "Prepared Statement could not be created: ".
                        $oDb->error." (".$oDb->errno."). Query was: '$sQuery'",
                        $oDb->errno
                    );
            }
        }
    }

    /**#@+
     *
     * @param mixed $mParams,...
     */

    public function execute() {
        $this->_execute(func_get_args());
        $this->_done();
    }

    /**
     * @return null|bool|int|string|float
     */
    public function getOne() {
        $this->_execute(func_get_args());
        $this->_fetchRow();
        if(isset($this->aResultSet[0][$this->aFieldnames[0]])) {
            return $this->aResultSet[0][$this->aFieldnames[0]];
        }
        return null;
    }

    /**
     * @return array
     */
    public function getCol() {
        $this->_execute(func_get_args());
        $this->_fetchAll();
        $sIndex = $this->aFieldnames[0];
        $aReturn = array();
        foreach($this->aResultSet as $aResultRow) {
            $aReturn[] = $aResultRow[$sIndex];
        }
        return $aReturn;
    }

    /**
     * @return array
     */
    public function getRow() {
        $this->_execute(func_get_args());
        $this->_fetchRow();
        if(isset($this->aResultSet[0])) {
            return $this->aResultSet[0];
        }
        return array();
    }

    /**
     * @return array
     */
    public function getAssoc() {
        $this->_execute(func_get_args());
        $this->_fetchAll();
        if(isset($this->aFieldnames[0]) && isset($this->aFieldnames[1])) {
            $sIndexKey = $this->aFieldnames[0];
            $sIndexValue = $this->aFieldnames[1];
            $aReturn = array();
            foreach($this->aResultSet as $aResultRow) {
                $aReturn[$aResultRow[$sIndexKey]] = $aResultRow[$sIndexValue];
            }
            return $aReturn;
        }
        return array();
    }

    /**
     * @return array
     */
    public function getAll() {
        $this->_execute(func_get_args());
        $this->_fetchAll();
        return $this->aResultSet;
    }
    /**#@-*/

    /**
     * @return false|int
     */
    public function numRows() {
        return $this->iNumRows;
    }

    /**
     * @return int
     */
    public function affectedRows() {
        return $this->oStatement->affected_rows;
    }

    /**
     * @return int
     */
    public function lastInsertId() {
        return $this->oStatement->insert_id;
    }

    public function getLastExecutedQuery() {
        $sReturn = $this->sQuery;
        if($this->aArgs) {
            $sReturn .= "; -- Argumente: ~".implode("~,~", $this->aArgs)."~";
        }
        return $sReturn;
    }

    /**
     * @throws DbException
     *
     * @param array $aArgs
     */
    private function _execute($aArgs) {
        $aArgs = $this->_parseFuncArgs($aArgs);
        $this->aArgs = $aArgs;
        $iArgs = count($aArgs);
        if($iArgs) {
            if($this->oStatement->param_count !== $iArgs ) {
                throw new DbException(
                    "Inserting parameters failed: ".$this->oStatement->param_count.
                    " Parameters expected but ".$iArgs." passed."
                );
            }
            $aRefArgs = array();
            foreach(array_keys($aArgs) as $mIndex) {
                $aRefArgs[$mIndex] = &$aArgs[$mIndex];
            }
            array_unshift($aRefArgs, str_repeat("s", $iArgs));
            // Needs References
            call_user_func_array(array($this->oStatement, "bind_param"), $aRefArgs);
        }
        $bWorked = $this->oStatement->execute();
        if($bWorked === false) {
            $sError = sprintf(
                "Query failed: %s (%s) Query was: '%s'",
                $this->oStatement->error,
                $this->oStatement->errno,
                $this->sQuery
            );
            switch($this->oStatement->errno) {
                case 1062:
                    throw new DbKeyViolationException($sError, $this->oStatement->errno);
                default:
                    throw new DbException($sError, $this->oStatement->errno);
            }

        }

        $this->_prepareFetch();
    }

    private function _prepareFetch() {
        if($this->bMetadata && !$this->bPreparedFetch) {
            $oMeta = $this->oStatement->result_metadata();
            if($oMeta === false) {
                $this->bMetadata = false;
            } else {
                $this->_prepareMetadata($oMeta);

                $this->aResultRow = array_fill(0, count($this->aFieldnames), null);
                // Ugly but 'bind_result' forces you to pass references
                $aRefs = array();
                foreach ($this->aResultRow as $iIndex => &$rmValue) {
                    $aRefs[$iIndex] = &$rmValue;
                }

                call_user_func_array(array($this->oStatement, "bind_result"), $this->aResultRow);
                $this->bPreparedFetch = true;
            }
        }
    }

    /**
     * @param mysqli_result $oMeta
     */
    private function _prepareMetadata(mysqli_result $oMeta) {
        $aFields = $oMeta->fetch_fields();
        foreach($aFields as $oField) {
            $this->aFieldnames[] = $oField->name;
        }
    }

    private function _fetchRow() {
        $this->_fetch(true);
    }

    private function _fetchAll() {
        $this->_fetch(false);
    }

    /*
     * @param bool $bOne One line ?
     */
    private function _fetch($bOne) {
        $this->aResultSet = array();
        if($bOne !== true) {
            $this->oStatement->store_result();
        }
        while($this->oStatement->fetch()) {

            // Deref
            $aTmp = array();
            foreach($this->aResultRow as $mValue) {
                $aTmp[] = $mValue;
            }
            $this->aResultSet[] = array_combine($this->aFieldnames, $aTmp);

            if($bOne === true) {
                break;
            }
        }

        $this->iNumRows = $this->oStatement->num_rows;
        $this->_done();
    }

    private function _done() {
        $this->oStatement->free_result();
    }

    /**
     * @param array $aArgs
     * @return array
     */
    private function _parseFuncArgs($aArgs) {
        if(isset($aArgs[0]) && is_array($aArgs[0])) {
            return $aArgs[0];
        }
        return $aArgs;
    }

}

The ugly bits (I think, that is why we are here I guess), are in _execute and _prepareFetch as the MySQLi API forced us to use references.

The class takes for granted that the passed in MySQLi object is already connected to a database.

If I missed something please let me know.

Answer 1

Maybe you could extract the common massaging before call_user_func_array in _execute and _prepareFetch into a helper?

private function _execute($aArgs) {
/// [...]
        $aRefArgs = array();
        foreach(array_keys($aArgs) as $mIndex) {
            $aRefArgs[$mIndex] = &$aArgs[$mIndex];
        }
        array_unshift($aRefArgs, str_repeat("s", $iArgs));
        // Needs References
        call_user_func_array(array($this->oStatement, "bind_param"), $aRefArgs);
///-^ This part...
/// [...]

private function _prepareFetch() {
/// [...]
            $this->aResultRow = array_fill(0, count($this->aFieldnames), null);
            // Ugly but 'bind_result' forces you to pass references
            $aRefs = array();
            foreach ($this->aResultRow as $iIndex => &$rmValue) {
                $aRefs[$iIndex] = &$rmValue;
            }

            call_user_func_array(array($this->oStatement, "bind_result"), $this->aResultRow);
///-^ ... matches this one loosely. 
/// [...]

Probably not worth it, good enough as-is.

This is one of the most readable bits of PHP I've ever seen, should be even better with the original comments.

Answer 2

While I have not reviewed the private methods closely, I wanted to provide some feedback on several of the public methods.

$dbStatement = new dbStatement($mysqliDb, 'SELECT * FROM users');
echo $dbStatement->numRows(); // false
$users = $dbStatement->getAll(); // Users array
echo $dbStatement->numRows(); // Some int

1. There seems to be some inconsistency how some methods work. Some methods will trigger the execution of the statement while others will not. As a result, I believe this will result in some unexpected behavior. See above for code example.

2. You may have good reason for this however I am also curious why numRows() is defaulted to false, while affectedRows() and lastInsertId() are not.

3. I also noticed that getLastExecutedQuery() returns the query that was passed in to the class regardless of whether or not it was executed. It is prepared in the constructor, but is not executed until the private _execute() method.

The class takes for granted that the passed in mysqli object is already connected to a database.

Lastly, I would recommend ensuring that the class throws an exception or gracefully handles this another way if the mysqli object is not connected to a database.