Feedback please, DB conveniance class extends PDO
Posted: Mon Jan 11, 2010 4:59 am
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.
(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)
{
}
}