ramblings on PHP, SQL, the web, politics, ultimate frisbee and what else is on in my life

MDB2 2.1.0 released

I decided to go with a bump of the minor version for the next release of MDB2 because there are a number significant changes and additions. One of the big changes is dropping array_key_exists() whereever possible. I kind of got bitten by isset() in the past a lot. Just stupid little mistakes. However I somehow felt more confident with replacing things with empty() calls. The same change was made across all drivers which were released as 1.1.0 versions (except for the Frontbase driver that needs a maintainer and the beta drivers for Oracle and Querysim).

Aside from that the two main features are custom datatypes and query rewriting via the debugging infrastructure. The custom datatypes were already explained in a previous blog post, so I will not go into detail on them again here. However the debugging infrastructure is probably one of the things people were not aware of before. Now its obviously even more powerful.

Since I am kind of lazy I just adapted the custome datatypes example with as little effort as possible. Its also not necessarily a role model of clean code and I should probably warn everybody that this feature in general is a last resort solution if you have a large codebase you need to quickly adapt.

So in this example we will rewrite all queries using WITH ROLLUP to use SQL_BIG_RESULT and SQL_CACHE options, since we expect these to produce large, yet fairly non volatile result sets. If you are unware of these options check out the explanations in the MySQL manual. Anyways here is the example:

function my_handler(&$db, $scope, $message, $is_manip = null)
    if ($scope === 'query' && $db->dbsyntax === 'mysql') {
        if (preg_match('/^SELECT(.*GROUP BY.*WITH ROLLUP)/', $message, $matches)) {
            // rewrite query
            $message = 'SELECT SQL_BIG_RESULT SQL_CACHE'.$matches[1];
            // log $message
            echo "replaced: $matches[0]\nwith: $message";
    // call the default handler just so that we can later call getDebugOutput
    return MDB2_defaultDebugOutput($db, $scope, $message, $is_manip);

$mdb2->setOption('debug', true);
$mdb2->setOption('debug_handler', 'my_handler');

$location = $mdb2->quote('Berlin', 'text');
$telephone = $mdb2->quote('(030) 23615467', 'text');
$query = "INSERT INTO branches VALUES ($location, $telephone)";
$result = $mdb2->exec($query);
$query = "SELECT location, telephone FROM branches GROUP BY location, telephone WITH ROLLUP";
$result = $mdb2->queryAll($query, array('text', 'text'), MDB2_FETCHMODE_ASSOC);

echo $mdb2->getDebugOutput();

For those of you wondering what the MDB2_defaultDebugOutput() looks like you can find its definition at the bottom of the MDB2.php file. But I have pasted it here for convinience:

function MDB2_defaultDebugOutput(&$db, $scope, $message, $is_manip = null)
    $db->debug_output.= $scope.'('.$db->db_index.'): ';
    $db->debug_output.= $message.$db->getOption('log_line_break');
    return $message;

Anyways I am sure that this will come in handy in a lot of situations where you need a quick fix rather than going through your entire codebase.

Obviously you can make great use of the debugging facilities even without query rewriting. For example you could run an automatic explain on every SELECT when in debugging mode. Note that I have plans to look at how to provide a portable API to produce explains for all supported RDBMS.

Finally I would like to welcome Justin and Nathan on board. Justin will be helping with the Oracle driver as time permits. Nathan has been one of the long time users. After prodviding useful bug reports and feature requests (usually with very detailed information and patches) he is now an official developer of the SQL Server and PostgreSQL drivers.

I am really happy with how things are going for MDB2 since it has gone stable and has officially superseded DB. Just from the amount of feedback I have gotten since I can see that usage has increased dramatically. Documentation is also picking up. Takagi Masahiro has been a huge help in making sure that these additions do not break the peardoc build. This reminds me about one of the latest patches that got submitted. Tim proposed adding API documentation for several methods to the MDB2 docs. I am not sure if I really want to do this as I think we should rather focus on tutorials in the end users docs and leave the API docs to the auto generated stuff. What do you guys think?


Re: MDB2 2.1.0 released

Just be sure to never throw empty() at a string while testing for array keys ;)

Re: MDB2 2.1.0 released

Yeah the same applies to array_key_exists(), you always have to make sure you are calling the function on an array. So you have to check with is_array(). I guess the tricky part is that array_key_exists() will complain if it doesnt get an array while empty() does not.

Before you can post a comment please solve the following captcha.

your name: