Page 1 of 1

Feedback please, DB conveniance class extends PDO

Posted: Mon Jan 11, 2010 4:59 am
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)
    {
    }
 
}
 

Re: Feedback please, DB conveniance class extends PDO

Posted: Mon Jan 11, 2010 3:26 pm
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.

Re: Feedback please, DB conveniance class extends PDO

Posted: Mon Jan 11, 2010 9:45 pm
by jixor
So make the methods static in a seperate helper class maybe?

Re: Feedback please, DB conveniance class extends PDO

Posted: Tue Jan 12, 2010 1:39 am
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.