Extended PDO API

PHP
1

I’ve been playing with extensions to PDO and PDOStatement, trying to get database statements working even more smoothly. Without going through the exact code, this is what I’m doing in the extension. I’m wanting help spotting potential problems because this is so low level that tons of code are going to end up sitting on it and I don’t want to be rewriting it.

Gazelle\Database extends PDO

override __construct:
@param mixed (string or array)

Extended to accept a configuration array rather than just a DSN - however it will still accept a DSN. Also sets PDOAttribute defaults for the framework - placing PDO into Throw Exceptions mode and setting the Statement class below as the default statement object.

Gazelle\Statement extends PDOStatement

override __construct:
@param Database object

Accept a reference to the creating db object? At the moment I haven’t needed this. The plan was to have the statement update the main database object with performance information like query execution time. With PHP 5.4 on the horizon though I’ve decided to hold off to a later version of the framework and farm such debug related tasks to Traits that can be attached to the object only when the Debug mode is invoked explicitly. For the moment I’ve left this alone.

override execute
[@param array]
@return this

Parent returns Bool. The override function returns this for chaining statements and throws an error if the parent method returned false.

bindArray
@param array

Bind an array by its keys to the statement. The draft of this function has me uneasy because it can get messed up by the calling code pretty easy and I’d like to harden it. Currently if the array has named keys then the statement should have named parameter tokens. If the array has numeric keys the statement should have question mark tokens. I’d like to tolerate receiving an array with numeric keys even if the statement has named tokens, but to do this I need to be able to retrieve a token list in order. PDOStatement has no public method that does this, does it have an undocumented protected method that provides this?

The function returns $this for chaining purposes.

key
[@param mixed (string or integer)]

This statement causes the statement to index the return on the argument (if present) or first column (if not) instead of generating a key. return self for chaining. If this function never gets called the first column is used when indexed functions are explicitly requested.

fetchMixed
@return mixed

Fetches the data based on the nature of the return. The using programmer should be able to predict what this is going to return based on the structure of the query. For example, “SELECT name FROM table WHERE id = 1 LIMIT 1” will return a single value, so fetchMixed returns just that value, not an array of 1 row with an array of 1 column with the value. fetchMixed is a very powerful shorthand, but if queries aren’t watched it can cause trouble. There are times when we need to force the return to stay an array, and other functions exist for this as we go down the list.

fetchAllAssoc
@return container array

This started life as an alias to fetchAll( PDO::FETCH_ASSOC ), but when I wrote the key function I realized this should be affected by that. If key is called then this function will move the contents of the key column into the key position of the return array.

fetchIndexedResults
Returns the results with the key set on the return. If there’s only two columns this function will return a single array as if you’d called fetchAll(PDO::FETCH_COLUMN) but with the keys being one of the columns (by default the first) and the values being the other.

fetchCollection
@parameter Collator

This function collates the results into a structured return. By default the structure is inferred from the key names. Collation of sql returns into multidimensional arrays is perhaps one of the most common tasks in PHP. The function can accept a collator object to do the collation - otherwise it creates one of class TierCollator.

fetchTree
This function uses two collators - first collating the rows into multidimensional array, and then passes that to a tree collator that creates a result tree. This is used by the core framework, but its use is infrequent enough I’m considering pulling it out of the base statement object.

The addition of chaining allows for these statements;


$ret = $db->prepare("SELECT * FROM table WHERE name = :name")->bindArray($array)->execute()->fetchMixed();

$ret = $db->query("SELECT * FROM table WHERE name = '{$name}'")->fetchMixed();

Thoughts?

2

Ah, FETCH_COLUMN didn’t think to try that. Rarely use PDO.

3 

$pdo = new PDO('sqlite::memory:', '', '');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE Test(id INTEGER NOT NULL PRIMARY KEY, name TEXT)');

$pdo->exec('INSERT INTO Test(name) VALUES(\\'Rod\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Jane\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Freddy\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Tom\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Dick\\')');
$pdo->exec('INSERT INTO Test(name) VALUES(\\'Harry\\')');

$q = $pdo->prepare('SELECT id, name FROM Test ORDER BY name ASC');
$q->execute();
$r = $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

var_dump($r);



array(6) {
  [5]=>
  array(1) {
    ["name"]=>
    string(4) "Dick"
  }
  [3]=>
  array(1) {
    ["name"]=>
    string(6) "Freddy"
  }
  [6]=>
  array(1) {
    ["name"]=>
    string(5) "Harry"
  }
  [2]=>
  array(1) {
    ["name"]=>
    string(4) "Jane"
  }
  [1]=>
  array(1) {
    ["name"]=>
    string(3) "Rod"
  }
  [4]=>
  array(1) {
    ["name"]=>
    string(3) "Tom"
  }
}
4

It shows how to do exactly what your function does, already built into PDO:

$q = $pdo->prepare('SELECT id, name FROM Test ORDER BY name ASC'); 
$q->execute(); 
$r = $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

PDO’s FETCH_ flags are doorways to some very useful things.

5

? What is that supposed to be?

6

Screw materialized views – hierarchical queries like Oracle is what MySQL needs!

7
Off Topic:

Now if only MySQL would support materialized views, that would really speed stuff up a lot. Without them I don’t really see the need for views unless the queries are extremely complex. Unless you’re using another DBMS of course.

8

You sure you tried my version here? Because the output did not change at all, while the flags did… I’m quite sure it works, because I use it in my projects quite often…

9

NO, It will not. That statement returns:


Array
(
    [5] => Array
        (
            [name] => Dick
        )

    [3] => Array
        (
            [name] => Freddy
        )

    [6] => Array
        (
            [name] => Harry
        )

    [2] => Array
        (
            [name] => Jane
        )

    [1] => Array
        (
            [name] => Rod
        )

    [4] => Array
        (
            [name] => Tom
        )

)

The desired output is:

Array
(
    [5] => Dick
    [3] => Freddy
    [6] => Harry
    [2] => Jane
    [1] => Rod
    [4] => Tom
)

Most of my frequently used read queries exist in the database as views. Write queries have a rudimentary ORM to compose them. This extender class doesn’t have the responsibility of managing that though.

10 
$r = $q->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_COLUMN);

That would do the job, no need for array_walk :wink:

11

Ah I see what you mean. Well yeah I suppose, though the difference is small. It’d just be a case of array_walking it; don’t know how that would benefit in terms of optimisation but yeah.

I extend PDO simply to return customised PDO Statements, so that on error it’s saved to a log with the information I require to fix it. However, I use a wrapper class which handles every type query which I’ll use, because I can’t stand using queries in the middle of code. Because only one class handles the queries, adding functionality to PDO is usually at an unnecessary cost.

But if we all programmed the same, there would be no progress in the world.

12

I would suppose a fluid interface is it’s own reward - that and code reuse. I’d worry about using closures to tack functionality onto the statement object because it feels like a kludge. I can understand using it if you have to maintain compatibility with existing code, but that isn’t the case here.

Still, maybe this will make it clearer. This is one of the functions out of the new class (Actually, I’ve written all of the functions but haven’t completed writing the corresponding tests and running them. This is a rare instance of writing the test second for me).


	public function fetchIndexedResults() {
		$k = $this->key ? $this->key : 0;
		
		$return = array();
		
		// If the key is numeric, fetch the results with numeric keys, else fetch using named keys.
		while ($row = $s->fetch( is_numeric($k) ? \\PDO::FETCH_NUM : \\PDO::FETCH_ASSOC )){	
			// Set the key
			$key = $row[$k];
			// Remove the key from the row result.
			unset($row[$k]);
			
			// If this leaves the row with one column only, reduce down to just that value.
			if (count($row) == 1) {
				$row = array_shift($row);
			}
			
			// Now bind to the return.
			$return[$key] = $row;
		}
			
		return $return;
	}

While not the world’s longest function, it does provide a clean way to get this common use case - a SQL query of values off one column, with the keys being the primary key of the table. So the results we’re looking for look like this


array (
  1 => 'James',
  2 => 'John',
  3 => 'Jerry'
);

PDO has no way to do this as written - if you fetch the name column there is no association retained with the primary keys in the database. And calling fetchAll gives this structure.


array (
  0 => array (
    'id' => 1
    'name' => 'James'
  )
...

So this extending class saves a lot of time with this very common use case. The query is


$nameList = $pdo->prepare("
  SELECT id, name
  FROM owners
  WHERE status = ?
")->execute(array($status))->fetchIndexedResults();

And that’s it.

13

Except it doesn’t do what my function does…

Outputs of each are…


// Ren's example...
Array
(
    [5] => Array
        (
            [name] => Dick
        )

    [3] => Array
        (
            [name] => Freddy
        )

    [6] => Array
        (
            [name] => Harry
        )

    [2] => Array
        (
            [name] => Jane
        )

    [1] => Array
        (
            [name] => Rod
        )

    [4] => Array
        (
            [name] => Tom
        )

)

// My function's output...
Array
(
    [5] => Dick
    [3] => Freddy
    [6] => Harry
    [2] => Jane
    [1] => Rod
    [4] => Tom
)

I wasn’t aware of the unique and group flags or mixing them with bitwise operators. That I could use to optimize what I’m writing.

14

Not sure I understand

Not sure there is any advantage of a fluent interface in this case.

15

And yet you’ll have to write those closures over and over would you not?

Besides, the goal here is, externally, this would be a simplistic approach. Currently fetching an array from PDO is like this at a minimum.


$result = $pdo->query($query)->fetchAll( PDO::FETCH_ASSOC );

Chaining can already be done, but it’s pretty limited in scope. This doesn’t allow you to bind parameters or alter the array. If you need that it balloons


$statement = $pdo->prepare($query);

foreach ($array as $key => $value) {
  $statement->bindValue($key, $value);
}

$statement->execute();

$result = $statement->fetchAll( PDO::FETCH_ASSOC );

By comparison


$result = $pdo->prepare($query)->bindArray($array)->execute()->fetchAllAssoc();

That is much, much simpler. The old functionality isn’t lost for those corner cases when it is needed, but most use cases should be able to use these new functions.

16

Tend to favour a more simplistic approach, using closures rather than extending PDO.


class t
{
	function prepare($sql)
	{
		$q = $this->pdo->prepare($sql);
		return function(array $parameters) use ($q) 
		{
			if ($q->execute($parameters))
				return function() use ($q)
				{
					return $q->fetch();
				};
			return false;
		};
	}
}



	$f = $t->prepare("SELECT * FROM table WHERE name = :name");
	$i = $f(['name' => 'foo']);
	while ($row = $i())
		echo json_encode($row), "\
";
17

I didn’t catch the last argument being changed from FETCH_ASSOC to FETCH_COLUMN. Hmm… I don’t think that means my function is irrelevant, but it can be optimized.


public function fetchIndexedResults() {
	return $this->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|(  $this->columnCount() == 2 ? PDO::FETCH_COLUMN : PDO::FETCH_ASSOC)); 
}

This does lose the ability to specify the key to index on, but that’s a minor concern as, in any event, there should not be more than one unique keys for the query.

18

Thats a neat feature I wonder if my own DB abstraction classes support such functionality that if only a single field per record is returned it merges and returns a single dimensional array. If it doesn’t consider this idea borrowed :stuck_out_tongue:

Cheers,
Alex

19

Couple questions. First, I for another function idea I found I need to return a new statement. To do that I need a reference to the database. The cleanest way to do that is to have the database object provide a reference to itself as a construct argument.

However, If I so much as define a __construct function (even an entirely empty one “public function __construct(){}” ) for the PDOStatement, PDO stops using it as the statement object and instead falls back to using PDOStatement!!! This is infuriating since the documentation indicates it should be possible. Using the refection API I verified that there isn’t an internal construct statement. I’m stumped and a little angry here.

EDIT: Ok, I discovered the problem. PDOStatement::__construct is PROTECTED!!!

What the Hell PHP team??? What the Hell???

I thought __construct was ALWAYS supposed to be public???

20

This came from the comments section in the manual. The assumption is that statements are only created from the pdo object itself.


class Database extends PDO {
    function __construct($dsn, $username="", $password="", $driver_options=array()) {
        parent::__construct($dsn,$username,$password, $driver_options);
        $this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('DBStatement', array($this)));
    }
}
class DBStatement extends PDOStatement {
    public $dbh;
    protected function __construct($dbh) {
        $this->dbh = $dbh;
    }
}