Auto connecting to PDO only if needed

Question

I have a section of code that depending on the URL requested, will include one of fourteen other files. Some of these fourteen files require a connection to one of three different databases, and additional files can be added at anytime.

I don't want to open PDO connections by default to all three database as its a waste of resources and will slow the execution time down. So my thought is to wrap all SQL queries within a function. The first time that a query is executed on a non-open PDO connection, the try {} error handler can catch it, find out what the problem was (in this case connection doesnt exist), then open the connection and re-execute the query. That way, the database is only being connected to as and when needed - as long as the connection string (host, database, username, password) are all defined in advance, I can't see any problem in it working.

However, I need to push on with this, and don't have access to the dev box for about 7 days, so can anyone see any problem with that scenario? Also, can anyone give me the error message that handler->errorInfo() will return if the connection isn't opened?

Answer 1

Use this class exactly how you would use the PDO class.

class DB extends PDO {

    protected $_config = array();

    protected $_connected = false;

    public function __construct($dsn, $user = null, $pass = null, $options = null) {
        //Save connection details for later
        $this->_config = array(
            'dsn' => $dsn,
            'user' => $user,
            'pass' => $pass,
            'options' => $options
        );
    }

    public function checkConnection() {
        if (!$this->_connected) {
            extract($this->_config);
            parent::__construct($dsn, $user, $pass, $options)
            $this->_connected = true;
        }
    }

    public function query($query) {
        $this->checkConnection();
        return parent::query($query);
    }

    public function exec($query) {
        $this->checkConnection();
        return parent::exec($query);
    }

    //etc.
}

Answer 2

This is the right idea, but not the best implementation of it.

Wrapping the SQL operations is good. But why don't you do it this way:

class Wrapper {
    private static $db;

    public static function someQuery() {
        $db = self::getDatabase();
        // now go on to execute the query
    }

    private static function getDatabase() {
        if (self::$db === null) {
            self::$db = // connect here
        }
        return self::$db;
    }
}

This has a lot of advantages:

• Allows you to logically group SQL operations into one (or several!) classes
• Does not connect to database if not needed
• Does not depend on (brittle) error checks to function correctly

In your specific case, you should probably go with 3 separate Wrapper classes. Putting everything into one class is doable (three different $db variables) but probably more confusing than it's worth.