[CENTER]Array of PHP functions shuffle and pop
Tell us which function we’ll study this stop…
[fphp]pg_insert[/fphp][/CENTER]
Uhm, no.
Ok, here’s the deal, PHP has a LOT of functions floating around, some dating back to PHP 3. Many are useful, some are dubious, and some are outright harmful to use in modern code because there are better options out there. pg_insert and it’s brethren in the pg_* family of functions (ah, the days before namespaces) are in that last group.
Use [fphp]PDO[/fphp]. But rather than flatly give that fiat let me explain what PDO is and why you should use it.
PDO was introduced by PHP 5.2 - maybe a little before that - and was a PECL library for some time before being coopted to the PSL (PHP Standard Library). By itself it provides a uniform interface for access to database systems. An argument for PDO is not an argument against your favorite database engine - the whole point of PDO is that you can use it with your favorite database engine. For central library authors it means they can provide solutions without worrying overmuch about which database the end user decides to deploy.
PDO’s most powerful ability is the concept of prepared statements and bound data. Prepared statements help to avoid SQL injection hacks by sending the query data and the variables it is acting on in separate steps. Also prepared statements allow the database engine to preserve and reuse prepared statements.
Can PDO replicate pg_insert? No, but PNL\Database can, and it’s built on PDO. I’m going to present it now as a preview to the rest of that framework (Hey what can I say, it’s my pet) and as a way of presenting an implementation of PDO and an extension of it.
You may have used database classes before or even wrote one. After all, passing connection resource identifiers and query identifiers is a bit of a pain in the tail. PDO saves us the trouble of doing that, but it’s not without a few shortcomings and having some shortcuts to some of its more cryptic commands won’t hurt. PDO is divided into a database core class - PDO - and a statement class - PDO_Statement. To have a truly flexible extension of PDO we must address both.
We’ll begin with the code that extends PDO. The code that follows is PHP 5.4+ safe, so be careful with the bleeding edge there.
<?php
namespace PNL;
/**
* PNL core database extends the PDO library.
* @author Michael
*
*/
class Database extends \\PDO {
protected $tierCollator = null;
protected $treeCollator = null;
One nice thing about namespaces - we don’t have to worry with obscure names for things. PDO is in PHP’s core namespace, so it really can’t have a straightforward name like Database without backward compatibility breaks. PNL\Database doesn’t have this problem. As for the collators and what they do - we’ll worry with them for another day or maybe later in the thread but I will say they are kinda awesome.
/**
* CONSTRUCT. Convert the configuration array into a DSN and pass that down to
* PDO.
* @param array $config
*/
public function __construct( array $config ) {
assert (
((isset($config['database']) && $config['database']) ||
(isset($config['dsn']) && $config['dsn'])) &&
isset($config['user']) && isset($config['password'])
);
[fphp]assert[/fphp] might be the most powerful underused function in PHP. Use it - a well placed assert can avoid hours of bug hunting. Here we are asserting that we have some sort of config.
if (isset($config['dsn'])) {
// If a DSN is set use it without question.
parent::__construct($config['dsn'], $config['user'], $config['password']);
} else {
// Otherwise hash the vars we were given into a DSN and pass that.
$params = array(
'driver' => isset($config['driver']) ? $config['driver'] : 'mysql',
'database' => $config['database'],
'user' => $config['user'],
'password' => $config['password'],
'server' => isset($config['server']) ? $config['server'] : 'localhost',
'port' => isset($config['port']) ? $config['port'] : '3306'
);
try {
// Start underlying PDO library.
parent::__construct("{$params['driver']}:dbname={$params['database']};host={$params['server']};port={$params['port']}",
$params['user'],
$params['password']
);
} catch ( \\PDOException $e ) {
throw new ConnectivityException($e);
}
}
One criticism I have of PDO’s design is creating an instance of the class implicitly calls a connect request on the target database. In my mind that should be a separate step to make testing easier even if it makes end use a little harder. Constructors should get the object to a ready for use state, and that’s all they should do. Then again, the argument can be made that connecting is part of that ready for use state.
// Set the error mode and the two most frequently used collators.
$this->setAttribute(self::ATTR_ERRMODE, self::ERRMODE_EXCEPTION);
$this->tierCollator = isset($config['tierCollator']) ? $config['tierCollator'] : __NAMESPACE__.'\\\\TierCollator';
$this->treeCollator = isset($config['treeCollator']) ? $config['treeCollator'] : __NAMESPACE__.'\\\\TreeCollator';
PDO allows us to choose whether it kicks errors or throws exceptions when a query doesn’t parse. I prefer exceptions so that a try/catch approach can be used to the data handling. Our last statement in the construct is the trickiest.
// Now set the standard behaviors of the PNL Framework
$this->setAttribute(self::ATTR_STATEMENT_CLASS, array(isset($config['statement']) ? $config['statement'] : __NAMESPACE__.'\\\\Statement', array($this)));
}
The statment class PDO uses is a settable attribute. PNL goes further to make it a configurable one ($config ultimately comes from a config.ini file). If a statement isn’t specified we default to the framework’s own which will be covered later in this post. The final array is the arguments that will be passed to that statement. PNL’s Statement object receives a reference to the database that spawned it.
public function __get( $var ) {
if ($var == 'tierCollator') {
return $this->tierCollator;
} else if ($var == 'treeCollator') {
return $this->treeCollator;
} else {
trigger_error( "Database::__get: No Access to {$var} or it does not exist", E_USER_NOTICE);
return null;
}
}
public function __set ( $var, $val ) {
if ($var == 'tierCollator' || $var == 'treeCollator') {
if (!class_exists($val)) {
throw new Exception("Collator must be set to a valid class");
}
$this->$var = $val;
} else {
trigger_error( "Database::__set: No Access to {$var} or it does not exist", E_USER_NOTICE);
return null;
}
}
}
PDO has no public variables. The collators can be fetched or set. Their own public properties and modes are accessed in this manner.
So much for the database class. There isn’t much here because PDO already does a lot and there isn’t much I can add to it to make it any better. Now to the statement object. Most of what I have to say about it is in the comment text.
<?php
namespace PNL;
/**
* PNL core database extends the PDO library to make it more chaining friendly. The
* result and results methods handle most of the more common fetch cases with PDO.
* That said, all PDO functionality remains exposed.
*
* @author Michael
*
*/
class Statement extends \\PDOStatement {
/**
* The database object that created us.
* @var Database
*/
protected $db = null;
protected $key = null;
/**
* Protected is NOT an error here, the PDOStatement object is a PHP internal
* construct and for whatever reason it is protected instead of being public
* like all other PHP constructors. Thank you Zend Engine team - this makes
* it impossible to test this object without also testing the Database object.
*
* @param PDO $db
*/
protected function __construct( $db ) {
$this->db = $db;
}
/**
* Replacement for the execute statement under most circumstances, but parse
* returns the statement object itself to allow for chaining. Also, the
* input array does not have to have leading colons on the key names. If present
* they are left alone, but any string key without a lead colon will have one
* appended.
*
* @param array input parameters for query.
* @see PDOStatement::execute()
* @return this
*/
public function parse( $params = null ) {
if (is_array($params) && !hasAllNumericKeys($params)) {
$params = $this->prepareArray($params);
}
$this->execute( $params );
return $this;
}
The sharp eyed will not hasAllNumericKeys isn’t a PHP core function - it is a PNL core function. This is what is known as a dependency, and tracking these things is what keeps programmers up late at night coding something that mysteriously broke. So, as an aside let’s look at that function…
function hasAllNumericKeys( array $array = array() ) {
foreach( array_keys($array) as $key) {
if (!is_numeric($key)) {
return false;
}
}
return true;
}
Tiny little bugger. In a Class based world the trend has been in PHP to move all functions into classes. I believe that in a few corner cases that’s a mistake. This little function has nothing to it that calls out “I’m a database function” as opposed to “I’m a controller function”. It’s task as straightforward as its name, so it exists as a separate function from the class in the \PNL namespace where any class of the framework might reference it. Is this a dependency nightmare waiting to happen?
Answer - it could be. Indeed, that ‘call from anywhere approach’ that you can see a lot of in PHP 4 programs is what makes them so tangled. But there are ways to ensure testability with a function like this. First - it has no internal state. That is, it doesn’t remember anything from one call to the next about what it did. Second, it has no global references. global is the single most evil statement in PHP - and the use of global variables will in short order insure that a program becomes utterly untestable. Third, it returns what it returns and doesn’t affect anything else. The array it receives is not altered in any way. The function could perhaps take the array by reference, but even that’s a mistake because the PHP engine already preserves memory by deferring copying the value until an alteration will occur, which with this function, will not.
With these guidelines followed universal functions can be used. There won’t be a lot of them though - PHP already has most of these straightforward situations covered.
We now return to the statement object
/**
* Prepares an array for use as a query parameter set.
*
* @param array $params
* @return array
*/
protected function prepareArray( $params ) {
$return = array();
// No pass by reference here because key values will be changing.
foreach ( $params as $key => $value ) {
if (!is_numeric($key) && strpos($key, ':') !== 0) {
$key = ':'.$key;
}
if (is_array($value)) {
throw DatabaseException('Array Values not permitted');
}
$return[$key] = $value;
}
return $return;
}
/**
* A chainable #closeCursor.
* @see PDOStatement::closeCursor()
* @return this
*/
public function closeQuery() {
$this->closeCursor();
return $this;
}
/**
* A chainable #bindValue;
* @see PDOStatement::bindValue()
* @return this
*/
public function bindVal($parameter, $value, $data_type = \\PDO::PARAM_STR ) {
parent::bindValue($parameter, $value, $data_type);
return $this;
}
Chainable means you can write code in a sentence form like this
$db->prepare($sql)->bindVal('col1', $col1)->bindVal('col2', $col2)->bindVal('col3', $col3)->results();
You’ll see this coding style a lot more often in Java and Java Script than PHP, but I believe that’s due to lack of support for the technique in frameworks and core code moreso than in comprehension problems. If anything, I find statement chains much easier to read than statement series.
/**
* Bind an array to the statement. If the keys are named you must use named
* placeholders in your statement. If the keys are not named you must use
* question mark placeholders.
* @param array $array
*/
public function bindArray( $array ) {
$array = $this->prepareArray($array);
foreach ( $array as $key => $value ) {
$this->bindValue( is_numeric($key) ? $key+1 : $key, $value, is_int($value) ? \\PDO::PARAM_INT : \\PDO::PARAM_STR );
}
return $this;
}
This is the function that replicates what pg_insert can do in this manner…
$db->prepare("INSERT INTO myTable ( col1, col2, col3 ) VALUES ( :col1, :col2, col3 )")->bindArray($values)->parse();
And now to a couple of icing functions.
/**
* Return a single value, or a single row, as determined by your query structure.
* @return string
*/
public function result() {
return $this->columnCount() == 1 ?
$this->fetchColumn() :
$this->fetch( \\PDO::FETCH_ASSOC );
}
/**
* The return of this function is influenced by your query structure.
* If your query only has one column of results, that column is returned.
* If your query has two columns of results, the first column is returned as the key
* and the second column is returned as the value.
* If there are three or more columns to your query the results are indexed by the first
* field of the query and then grouped on that field.
*
* @return array
*/
public function results() {
return $this->columnCount() == 1 ?
$this->fetchAll( \\PDO::FETCH_COLUMN ) :
$this->fetchAll(\\PDO::FETCH_GROUP|\\PDO::FETCH_UNIQUE|( $this->columnCount() == 2 ? \\PDO::FETCH_COLUMN : \\PDO::FETCH_ASSOC));
}
}
Results isn’t without limitations. For the most part it assumes you’re going to be getting keyed results. If this isn’t the case then it’s appropriate to fall back to PDO’s fetch and fetchAll statements.
Not that there’s anything wrong with PDO’s fetch statements - but these aliases are quicker to read and use in most cases. There are two more functions in the class that deal with the collators, but as they are way outside of scope for a forum post, and since they are still buggy (which is the reason the framework hasn’t been released yet) I’ll omit them.
I hope all of this has been informative and useful.
