ProcessWire 2.4 compatibility for modules

[ryan]

One of the changes coming in ProcessWire 2.4 (and the 2.3 dev branch) is that we're switching the database driver from mysqli to PDO. Over the next few days, you'll see the ProcessWire dev branch begin to be compatible with both mysqli and PDO. By the time we hit version 2.4, it will only be compatible with PDO. If you develop modules or templates that perform SQL queries, and you want to have a head start, you can begin make your module(s) compatible now by checking what the DB driver is. Though I'm thinking most modules out there don't do any kind of SQL queries, so it won't matter. But for those that do, I wanted to go ahead and mention it since it'll be showing up on the dev branch shortly (I already have it switched locally). Here is an example that issues the same exact query with the two different DB drivers. 

$db = wire('db');
$name = "example"; 

if($db instanceof PDO) {
  // driver is PDO
  $query = $db->prepare("SELECT COUNT(*) FROM my_fake_table WHERE name=:name");
  $query->execute(array(":name" => $name)); 
  $count = $query->fetchColumn();
} else {
  // driver is mysqli
  $result = $db->query("SELECT COUNT(*) FROM my_fake_table WHERE name='" . $db->escape_string($name) . "'"); 
  $row = $result->fetch_row();
  $count = $row[0];
}

If you have any queries that you aren't sure how to convert from mysqli to PDO, just post them here and I'll code it for you.The PDO queries tend to be a little more verbose most of the time (even if not above). But the use of named parameters is something that we need, and mysqli just doesn't have them (unless you like using question marks). There are other benefits to using PDO, but the named parameters are the main benefit in the short term. 

[lucas]

Hey Ryan! 

Speaking of future updates, by any chance do you plan on releasing a new default theme for the admin soon?

[diogo]

Lucas, that was so off topic

[teppo]

Thanks for the heads-up Ryan, just started converting one of my modules

So far everything is just fine when it comes to simple queries (an extra "if" I can live with), but for complicated stuff this seems to result in either creating duplicate code with relatively minor differences or rewriting large chunks of existing logic to support both in a sensible way. This is most likely (at least) partly a result of my total lack of experience with PDO, but as far as I can tell there are also differences that make these two fundamentally incompatible.

There are many advantages that come with PDO, but this transition has a negative effect on backwards compatibility for modules and possibly even some sites.. although in the case of sites simply not updating them to 2.4 is a viable solution, especially if they're something you know you won't have to expand / alter that much in the future and thus won't be missing all the goodies brought in by new PW versions.

Anyway, I still can't help wondering one thing:

Have you considered letting PDO and mysqli co-exist within PW, ie. by referring to them with something like $db_pdo / $db_mysqli and letting $db default to $db_pdo -- or perhaps just adding $db_mysqli / $mysqli as an alternative to PDO? Or is there perhaps a bigger plan behind this change, such as cutting ties with MySQL, that this co-existence would undermine?

Then again, I'm not sure if this would help that much really. Obviously it would make converting existing code to 2.4 easier, but some work would still need to be done. I'm just a bit worried about this creating a wall between PW < 2.4 and >= 2.4 and can't help wondering if this could be somehow avoided or (more likely) conversion could be simplified a bit.

Other than that: for anyone looking for a quick introduction to PDO, this tutorial on Nettuts+ really filled in the blanks for me (see the comments section for good point about prepare, though..)

[ryan]

So far everything is just fine when it comes to simple queries (an extra "if" I can live with), but for complicated stuff this seems to result in either creating duplicate code with relatively minor differences or rewriting large chunks of existing logic to support both in a sensible way.

I agree, there is tons of duplication, but just temporarily. You'll see plenty of duplication when I commit the PDO updates to dev. But I think it's okay since it's only for a few months until the 2.4 transition is complete. There's also the factor that PW doesn't have a lot of SQL queries in the first place–most of the application logic is behind the PW API. Converting all the queries in the core took me less than a day. Once 2.4 is released, I'm going to go back and remove all of the redundant mysqli statements, since they won't be relevant anymore. 

This is most likely (at least) partly a result of my total lack of experience with PDO, but as far as I can tell there are also differences that make these two fundamentally incompatible.

The syntax is a lot different between the two. I'd not ever used PDO before, but after playing around with it a lot, I thought it had a lot of benefits over mysqli. Most significant to me are the benefits with prepared queries and ability to use named parameters. I'd always thought mysqli prepared statements were awkward and annoying in almost every sense, so tended to avoid them. But in PDO, they are very clean and easy to use, and help to make not just safer queries, but more readable queries. 

Btw, if you don't need to check the return value, a wire('db')->query('sql statement'); will work the same between mysqli and PDO. So there are some instances that may not need to be accounted for. 

There are many advantages that come with PDO, but this transition has a negative effect on backwards compatibility for modules and possibly even some sites.. although in the case of sites simply not updating them to 2.4 is a viable solution, especially if they're something you know you won't have to expand / alter that much in the future and thus won't be missing all the goodies brought in by new PW versions.

Backwards compatibility isn't a major factor here just because our API isn't based on DB queries. So usually when DB queries are used, they are doing something out of the ordinary. 

There aren't a lot of modules using SQL queries. I'm guessing less than a dozen. I figured better to get this change in place sooner rather than later, as it's a lot easier to do now than it will be a year or two from now. 

Have you considered letting PDO and mysqli co-exist within PW, ie. by referring to them with something like $db_pdo / $db_mysqli and letting $db default to $db_pdo -- or perhaps just adding $db_mysqli / $mysqli as an alternative to PDO?

Good and interesting idea. Is it possible to have two database drivers (PDO and mysqli) connected to the same DB at the same time? Maybe it is, in which case, I'd be open to going this route. In that case, I'd probably just leave $db the mysqli one and make it deprecated but functional till 2.5, and call the new PDO one $pdo. 

Or is there perhaps a bigger plan behind this change, such as cutting ties with MySQL, that this co-existence would undermine?

No plans to cut ties with MySQL and no plans to adopt other databases just yet. Going with PDO is motivated purely by making sure we're giving our users the best tools out there. PDO is looked upon much more positively in the PHP community, and that perception has just been increasing over the last couple of years. But I think it's beyond perception and there's a lot of good reasons to use PDO over mysqli. Given this, I think that combined with our switch to supporting namespaces and Composer (2.4), it will help ProcessWire to grow in the PHP community in addition to the web developer community.

It's also a good time to do it because 2.3 modules aren't going to be compatible with 2.4 already, purely because of namespaces. Though this one will be very simple for module developers to correct, simply by adding "namespace ProcessWire" at the top of the file (I think). 

[ryan]

After doing some testing here, it looks like we can run PDO and mysqli at the same time without any real problems that I can detect. So I'm going to backtrack a bit and go with Teppo's idea of keeping them both. Mysqli will be deprecated in PW, but remain there for a couple major versions at least just so that there's less chance of breaking backwards compatibility. The way I've set it up locally is that $db refers to mysqli as it always has, and $database (a new API variable) refers to the new PDO driver. So you can choose to use whichever one you want. The core and core modules will only be using PDO. The mysqli driver ($db) isn't actually instantiated until something calls upon it. That way there's only overhead of two database connections when/if your site needs mysqli for an older module or something. 

$db; // refers to mysqli
$database; // refers to PDO

As a result, it will not be necessary to consider PDO in order to make your templates/modules compatible with ProcessWire 2.4. But if you want to take advantage of PDO, while still being backwards compatible with older versions of ProcessWire, then you'd still have to have some duplication:

if(wire('database')) {
  // Newer ProcessWire: PDO is available
} else {
  // Older ProcessWire: only mysqli available
}

I'd imagine that once PW 2.4 has been out for a little while, people won't use $db (mysqli) at all anymore in PW. But using Teppo's suggestion, we can at least make it available for the foreseeable future, to reduce the chance of compatibility issues during future upgrades. 

[diogo]

People might think that $db is just a short for $database. Would it make sense if the new variable would be $pdo instead?

[kongondo]

+ for $pdo

Or $dbpdo?

[ryan]

I started with $pdo as an API variable name. But it didn't feel at home in ProcessWire, which uses clear names for all API variables (page, pages, users, input, sanitizer, etc.). The problem is $pdo feels like a brand name and someone not familiar with what PDO won't realize that it's referring to a database. So I thought it was best to call the API variable exactly what it is, which is a $database. Ultimately $database is more consistent with our API variable naming system than even $db, as none of the other API variables are abbreviated. As for confusion between $db and $database, I would only have that concern if we were planning to keep both mysqli and PDO long term. The $db will be deprecated and eventually dropped as an API variable. But we'll give it a major version or two. 

[teppo]

Ultimately $database is more consistent with our API variable naming system than even $db, as none of the other API variables are abbreviated. As for confusion between $db and $database, I would only have that concern if we were planning to keep both mysqli and PDO long term.

Agreed -- though this is definitely something that needs to be documented properly and promptly to minimize the likelihood of unnecessary confusion (once it's final, that is)

[kongondo]

Makes sense....

[SiNNuT]

or

$phpDatabaseObject

[ryan]

I've now got ProcessWire (local dev) fully converted to PDO and running with both PDO ($database) and mysqli ($db) available to the API. When in the admin, the debug mode info at the bottom highlights queries from each, so that you can more easily identify mysqli queries that you may want to convert to PDO. As a matter of efficiency, ProcessWire doesn't actually initiate the mysqli connection until you access something from the $db API variable. Meaning, the core and a stock install of PW doesn't use mysqli at all (or attempt a DB connection through it), but it makes it available if any modules or your site want it. In order to achieve this, I had to abstract the existing mysqli Database class behind a passthrough gatekeeper class. The only way you will notice that is if you are using any kind of type hinting for the old Database/mysqli class, OR if you are using procedural mysqli functions. I don't think I've seen any modules type hinting the old Database class or mysqli except for my own (like Form Builder). But I do think I've seen procedural use of mysqli at least once (I think it might have been in one of Teppo's modules?). Anyway, not sure if this matters to anyone, but just in case, here's the things to watch for with mysqli: 

// If you are type hinting mysql or Database like this…
function example1(Database $db) { ... } // this 
function example2(mysqli $db) { ... } // or this

// …then remove the type hinting:
function example1($db) { ... }
function example2($db) { ... }

// if you are using mysqli procedurally with a $db param:
$result = mysqli_query($db, "SELECT COUNT(*) FROM pages"); 
list($count) = mysqli_fetch_row($result);

// use it without specifying $db param…
$result = mysqli_query("SELECT COUNT(*) FROM pages"); 
list($count) = mysqli_fetch_row($result);

// …or better yet, use the OO version:
$result = $db->query("SELECT COUNT(*) FROM pages"); 
list($count) = $result->fetch_row();

Since PW 2.4 will be backwards compatible in terms of the database, lets not worry about switching our 3rd party modules over to PDO until 2.4 is actually out in a stable version, which is still months away.