5
\$\begingroup\$

The main reasoning behind this PDO wrapper, is that I find myself using unique constraints quite frequently in my designs, and I have if ($ex->errorInfo[1] == 1062) littered throughout my code, and I thought there has to be a better way.

<?php

class PDOMysql extends \PDO {

    public function __construct($dsn, $username = null, $passwd = null, $options = null)
    {
        parent::__construct($dsn, $username, $passwd, $options);
        $this->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_OBJ);
    }

    public function prepareAndExecute($statement, array $params = []): \PDOStatement
    {
        try {
            $stmt = $this->prepare($statement);
            $stmt->execute($params);
            return $stmt;
        } catch (\PDOException $ex) {
            if ($ex->errorInfo[1] == 1062) {
                throw new PDOMysqlUniqueConstraintException($ex);
            } else {
                throw $ex;
            }
        }
    }

}


class PDOMysqlUniqueConstraintException extends \PDOException {

    public function __construct(\PDOException $ex)
    {
        parent::__construct($ex->getMessage(), $ex->getCode(), $ex->getPrevious());
    }

}


// example usage 
$user = 'vps'; 
$pass = 'vps'; 
$db = new PDOMysql("mysql:host=localhost;dbname=testdb", $user, $pass);

// create table 
$sql = "create table if not exists `mytable` (col1 varchar(100) null, constraint idx_u unique (col1) )";
$db->prepareAndExecute($sql);

// insert first row 

$params = ['col1' => uniqid('')]; 
$sql = "insert into mytable (col1) values (:col1)"; 
$db->prepareAndExecute($sql, $params);

// select row 
$sql = "select * from mytable where col1 = :col1"; 
$stmt = $db->prepareAndExecute($sql, $params); 
var_dump($stmt->fetchAll());

// insert duplicate row 
try {
    $sql = "insert into mytable (col1) values (:col1)"; 
    $db->prepareAndExecute($sql, $params);
} catch (\PDOMysqlUniqueConstraintException $ex) {
   die('Unique Constraint Detected');
}
\$\endgroup\$
9
  • 1
    \$\begingroup\$ Do you run into this situation often ? Isn't is better to fix existing code to avoid the exception, rather than having to handle it ? Last but not least, is your exception class doing anything special that a generic, application-wide exception handler could not do ? \$\endgroup\$ – Anonymous Oct 2 '20 at 22:22
  • \$\begingroup\$ approx 15 uses in total across 5 projects. I was under the impression using the db constraint was the correct approach. Take for example users signing up, with a unique email address. I could go and select the user first to see if the email exists, but that would result in a race condition (however unlikely). I would like to be able to detect and handle that particular exception differently, which is why it isn't just a generic exception. \$\endgroup\$ – bumperbox Oct 4 '20 at 9:08
  • \$\begingroup\$ Database locks are meant to avoid race conditions. In Mysql you may want to use SELECT FOR UPDATE for example, at least if your tables are InnoDB. \$\endgroup\$ – Anonymous Oct 4 '20 at 13:15
  • \$\begingroup\$ I don't quite understand how SELECT FOR UPDATE would help in the case of inserting a user with a duplicate email address? If the email address doesn't exist in the db, there will be no row to select? \$\endgroup\$ – bumperbox Oct 4 '20 at 18:45
  • 1
    \$\begingroup\$ "For index records the search encounters, locks the rows and any associated index entries" So if there is a unique index, it gets locked and modification of the row or the index (range) is not possible until the transaction ends. And this applies even if there is no such row, the index entry is virtual, yet part of the locked index range. \$\endgroup\$ – slepic Oct 6 '20 at 14:37
4
\$\begingroup\$

This code is so good that it's the first time I have nothing to say.

Your approach to the problem is absolutely correct and you should keep using it.

There could be only microscopic nuances, such as one mentioned by Sam. Or, the fact that your constructor would override the configuration settings passed in the $options array. Say, if someone would want to use FETCH_ASSOC as a default fetch mode, your class won't let them. I would rather make it this way

$defaults = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_EMULATE_PREPARES   => false, // I would throw in this one too
];
$options = array_merge($defaults,$options); 
parent::__construct($dsn, $username, $passwd, $options);

So this way you would have your preferred defaults that, however, could be overwritten, making your class flexible, without artificial restrictions.

\$\endgroup\$
3
\$\begingroup\$

In this presentation about cleaning up code Rafael Dohms talks about many ways to keep code lean - like avoiding the else keyword. (see the slides here).

It is wise to avoid the else keyword - especially when it isn't needed - e.g. when a previous block contains a throw statement - like in the catch section of the the method PDOMysql::prepareAndExecute(), there are two throw statements:

if ($ex->errorInfo[1] == 1062) {
    throw new PDOMysqlUniqueConstraintException($ex);
} else {
    throw $ex;
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.