What is the point behind building an abstraction layer PDO Adapter class instead of using native PDO?

Question

I have built a PDO adapter class because I thought, at the time anyway, it would be a good idea. After fighting with it, it makes no sense to me. Isn't the design of PDO the way it is to keep you from having to create special adapters for a given database?

For a connection, I understand, but I seem to be replacing PDO with my own version of PDO.

I do not understand why I would need this extra layer of abstraction.

Here are some examples:

public function prepare($sql, array $options = array()) {
    $this->connect();
    try {
        $this->statement = $this->connection->prepare($sql, 
            $options);
        return $this;
    }
    catch (PDOException $e) {
        throw new RunTimeException($e->getMessage());
    }
}

public function execute(array $parameters = array()) {
    try {
        $this->getStatement()->execute($parameters);
        return $this;
    }
    catch (PDOException $e) {
        throw new RunTimeException($e->getMessage());
    }
}

public function countAffectedRows() {
    try {
        return $this->getStatement()->rowCount();
    }
    catch (PDOException $e) {
        throw new RunTimeException($e->getMessage());
    }
}

public function getLastInsertId($name = null) {
    $this->connect();
    return $this->connection->lastInsertId($name);
}

EDIT: I found this. I think I may be in overkill. I could possibly have this class for other things, but not native PDO without a more compelling reason.

https://stackoverflow.com/questions/20664450/is-a-pdo-wrapper-really-overkill

Answer 1

Indeed, you could have been using PDO directly. The only real thing it does is to rethrow PDOException as RunTimeException, a practice which should be avoided at all costs.

This being said, PDO doesn't keep you from having to create special adapters for a given database, since different databases have different syntax and functionality which cannot possibly be handled by PDO. It works for simple stuff like PDOStatement::rowCount(), but PDO won't help you if, for example, you need to LIMIT/OFFSET the number of results in MySQL and Microsoft SQL, forcing you to write LIMIT/OFFSET query for one and WHERE rowNumber BETWEEN ... AND ... for another.

Moreover, the presence of PDO doesn't mean you shouldn't have a database layer either: your business layer shouldn't call PDO directly.

Example

If a website needs to display the number of users, business layer will call database layer IDatabase similarly to this:

$countUsers = $this->data->countAllUsers();

Then, you'll have an implementation for Microsoft SQL Server:

class SqlServerDatabase implements IDatabase
{
    ...
    public function countAllUsers()
    {
        $query = 'select count(1) from [Community].[User]';
        $statement = $this->connection->prepare($query);
        $statement->execute();
        return $statement->fetchColumn();
    }
    ...
}

and another one for MySQL:

class SqlServerDatabase implements IDatabase
{
    ...
    public function countAllUsers()
    {
        $query = 'select count(*) from `user`';
        $statement = $this->connection->prepare($query);
        $statement->execute();
        return $statement->fetchColumn();
    }
    ...
}

If you have a lot of table-counting, you may want to refactor that to reduce code duplication. Without PDO, you won't be able to do that. With PDO, you can:

abstract class DatabaseCommon
{
    protected function countAllRows(TableName $tableName, $preferOneToAsterisk = false)
    {
        $query = $preferOneToAsterisk ?
            'select count(1) from ' . $tableName->sanitize() :
            'select count(*) from ' . $tableName->sanitize();

        $statement = $this->connection->prepare($query);
        $statement->execute();
        return $statement->fetchColumn();
    }
}

class SqlServerDatabase extends DatabaseCommon implements IDatabase
{
    public function countAllUsers()
    {
        return $this->countAllRows(
            new MicrosoftSqlTableName('Community', 'User'),
            true
        );
    }
}

class SqlServerDatabase extends DatabaseCommon implements IDatabase
{
    public function countAllUsers()
    {
        return $this->countAllRows(new MySqlTableName('user'));
    }
}