Feedback please, DB conveniance class extends PDO

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
jixor
Forum Newbie
Posts: 2
Joined: Fri Nov 06, 2009 10:39 pm

Feedback please, DB conveniance class extends PDO

Post by jixor »

This is a work in progress, its basically a conveniance extension to PDO. I'm just looking for people's thoughts. Obviously there are a few fairly self-explanatory classes that go along with this so I won't include them. Thanks in advance.
(My coding standard is Zend with C influence in case your curious)

Mainly I want feedback on DB::getDOM() as I'm not really satisfied with it and undecided what I want to change.

BTW its part of a much larger collection of scripts. I'm compiling an open source helper library from all the bits and pieces I have built over the past few years and still use.

Code: Select all

 
<?php
 
namespace MPL;
 
class DB extends \PDO
{
 
    /**
     * Flag to format JSON output as human readable
     *
     * @see MPL\DB::getJSON()
     * @var int
     */
    const JSON_READABLE = 1;
 
 
 
    /**
     * Constructor
     *
     * Initializes the connection.
     *
     * @see   PDO::__construct()
     * @param DB_Options $Options
     */
    public function __construct(DB_Options $Options)
    {
 
        switch($Options->type)
        {
 
        case 'mysql':
        case 'pgsql':
            $strDSN = "$Options->type:host=$Options->host;dbname=$Options->name";
            if ($Options->port)
                $strDSN .= ";$Options->port";
            break;
 
        default:
            throw new DB_Exception("Unsupported type, $Options->type");
            return;
 
        }
 
        parent::__construct($strDSN, $Options->user, $Options->pass);
 
    }
 
 
 
    /**
     * Execute an insert statement
     *
     * @see    PDO::query()
     * @param  string $strSQL The SQL statement
     * @param  bool   $bDeep  Set to true if the SQL doesn't start with INSERT
     *                        but still creates a new record.
     * @return int    Last insert ID, if there was one. Or NULL on fail.
     */
    public function insert($strSQL, $bDeep = false)
    {
 
        if (!$bDeep && substr(trim(strtoupper($strSQL)), 0, 6) != 'INSERT')
            throw new DB_Query_Exception(
                'MPL\DB::insert(): Require INSERT statement'
                );
 
        if($this->query($strSQL))
            return $this->lastInsertId();
 
        return null;
 
    }
 
 
 
    /**
     * Get results as object
     *
     * @see    PDO::query()
     * @param  string       $strSQL SQL statement
     * @return PDOStatement
     */
    public function queryObj($strSQL)
    {
 
        return $this->query($strSQL, self::FETCH_OBJ);
 
    }
 
 
 
    /**
     * Get results an XML format
     *
     * @see    \PDO::query()
     * @param  string $strSQL     The SQL statement.
     * @param  string $strRoot    The XML root element.
     * @param  string $strRow     The row element
     * @return string
     */
    public function getXML($strSQL)
    {
/*
see todo on getDOM regarding pointer to root node
        $Dom = new DOMDocument;
 
        if (!$this->getDOM($Dom, $strSQL, null, $strRoot, $strRow))
            return false;
 
        return $Dom->saveXML();
*/
    }
 
 
 
    /**
     * Execute An SQL Statement
     *
     * @todo   Able to specify a pointer to the root node to fill the results
     *         into.
     * @todo   Might be better to accept an array or structure of options. This
     *         may be the single argument or substitute $oRoot onwards.
     *         Backwards compatibility would be preferable.
     * @see    \PDO::query()
     * @param  DOMDocument $oDoc     The DOMDocument, or derrived object.
     * @param  string      $strSQL   The SQL statement.
     * @param  DOMElement  $oRoot    The root element, defaults to the
     *                               document's firstChild. Technically could
     *                               be a member of a different Doc?
     * @param  mixed       $mRes     The tag name of the container element,
     *                               defaults to 'dataset' for <dataset />. Or
     *                               a DOMElement attached to the DOMDocument.
     * @param  string      $strRow   The tag name of the result row element,
     *                               defaults to 'data' for <data />.
     * @param  string      $strResID The container elments id, optional.
     * @return bool
     */
    public function getDOM(\DOMDocument $Doc, $strSQL, \DOMElement $oRoot = null, $mRes = 'dataset', $strRow = 'data', $strResID = null)
    {
 
        $res = $this->query($strSQL, self::FETCH_OBJ);
 
        if ($res->columnCount() < 1)
            return false;
 
 
 
        if (is_string($mRes))
        {
 
            $oRes = $Doc->createElement($strRes);
 
            if (!$oRoot)
                $Doc->firstChild->appendChild($oRes);
 
            else
                $oRoot->appendChild($oRes);
 
        }
        elseif ($mRes instanceof \DOMElement)
        {
 
            $oRes = $mRes;
 
        }
        else
        {
 
            throw new DB_DOM_Exception(
                "MPL\DB::queryDOM() Fouth argument must be either a DOMElement"
                    . " or string name of an element"
                );
 
        }
 
 
 
        if ($strResID)
            $oRes->setAttribute('id', $strResID);
 
        foreach ($res as $row)
        {
 
            $oRow = $Doc->createElement($strRow);
 
            foreach($row as $e => $v)
            {
                $oCol = $Doc->createElement($e, $v);
                $oRow->appendChild($oCol);
            }
 
            $oRes->appendChild($oRow);
 
        }
 
        return true;
 
    }
 
 
 
    /**
     * Get Results As JSON
     *
     * @todo   Should also be possible to explode dsv columns like sets or
     *         compiled columns.
     * @param  string $strSQL       SQL statement
     * @param  int    $iFlags       Flags, only DB_Conn::JSON_READABLE
     * @return string
     */
    public function getJSON($strSQL, $iFlags = 0)
    {
 
        $bReadable = ($iFlags & self::JSON_READABLE);
 
        $res = $this->query($strSQL, parent::FETCH_ASSOC);
 
        if ($res->rowCount() < 1)
            return '[]';
 
 
 
        $out = $bReadable ? "[\n" : '[';
 
        foreach($res as $row)
        {
 
            $out .= $bReadable ? "\n\t{" : '{';
 
            foreach($row as $col => $val)
                $out .= ($bReadable ? "\n\t\t" : '') . "\"$col\":\"$val\",";
 
            $out = substr($out, 0, -1) . ($bReadable ? "\n\t}," : '},');
 
        }
 
        return substr($out, 0, -1) . ($bReadable ? "\n]" : ']');
 
    }
 
 
 
    /**
     * Get A Single Column From A Single Row
     *
     * Assumedly your SQL statement will fetch only a single column from a
     * single row, otherwise you will simply get the first column from the first
     * row.
     *
     * @param  string $strSQL The SQL statement.
     * @return string Data, or FALSE on failure.
     */
    public function getVar($strSQL)
    {
 
        if (!$oRes = $this->query($strSQL, self::FETCH_COLUMN, 1))
            return null;
 
        if ($oRes->rowCount() < 1)
            return null;
 
        $mResult = $oRes->fetchColumn();
 
        $oRes->closeCursor();
 
        return $mResult;
 
    }
 
 
 
    /**
     * Get A Single Row Object
     *
     * If multiple rows are selected only the first row will be returned.
     *
     * @param  string $strSQL  The SQL statement.
     * @param  int    $iFlags  Flags
     * @param  object $oObject Generic object to fill results into
     * @return string Data, or FALSE on failure.
     */
    public function getRow($strSQL, $iFlags = 0, $oObject = null)
    {
 
        if ($oObject)
            if (!($iFlags & self::FETCH_INTO))
                $iFlags = self::FETCH_INTO;
 
        elseif (!$iFlags)
            $iFlags = $iFlags | self::FETCH_OBJ;
 
 
 
        if ($oObject)
            $res = $this->query($strSQL, self::FETCH_INTO, $oObject);
 
        else
            $res = $this->query($strSQL, self::FETCH_OBJ);
 
 
 
        if ($res->columnCount() < 1)
            return false;
 
 
 
        foreach($res as $r)
            return $r;
 
    }
 
 
 
    public function getCol($strSQL)
    {
    }
 
}
 
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Feedback please, DB conveniance class extends PDO

Post by Christopher »

It seems like this should be split into several classes. The getDom() and getJson() methods seem like View Helpers and out of place here.
(#10850)
jixor
Forum Newbie
Posts: 2
Joined: Fri Nov 06, 2009 10:39 pm

Re: Feedback please, DB conveniance class extends PDO

Post by jixor »

So make the methods static in a seperate helper class maybe?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Feedback please, DB conveniance class extends PDO

Post by Christopher »

I wouldn't make them static. And you can decide whether they are standard helpers that just take an array of data, or if they take a DB object. I think the former is cleaner.
(#10850)
Post Reply