[development] PDO and database limitations

[Larry Garfield]

This is an RFC of sorts. :-)

At Drupalcon, I was shopping around the idea of a PDO[1] backend for Drupal.  
For brevity, I'll just reference the blog entry I made on why I think PDO is 
a good idea[2].  The goal is that PHP 5 users get a nice speed bump and we 
get a start on eventually shifting Drupal's database layer from the 1997 APIs 
to the more robust 21st century PHP standards.  Most people I talked to liked 
the idea, so after getting a nod from Dries I've been working on it since the 
Hackfest and made decent progress.  However, I've run into a few snags I want 
to get a 2nd opinion on (or 3rd, or 4th, or whatever).

1) It turns out that PDO from PECL running under PHP 5.1.6, at least, has 
issues.  Specifically, segfault issues on otherwise perfectly sane queries.  
This is apparently a known issue with 5.1.6, and one of the reasons 5.2 
exists. :-)  At present, I have no solution for it other than saying "well, 
the PDO support is only if you're running 5.2, otherwise use the existing 
MySQL/PostgreSQL drivers, deal".  Does anyone have a problem with that, and 
if so, an alternate solution?

2) Like any wrapper, PDO, while it offers some really nice features with a 
common API (like C-level prepared statements, which are what I'm mainly 
after), has some "lowest common denominator" issues.  The main one I've run 
into so far is that there is no reliable equivalent of mysql_num_rows() for 
SELECT statements, only for data-changing statements[3].  In testing it 
doesn't look like the MySQL PDO driver returns anything useful for rowCount() 
on SELECT.  That gives us 3 options.

A) Stop using db_num_rows() on result sets.  (It's a database-specific feature 
in the first place.)  In places where we use it, use a separate count(*) 
query instead.  That's the more database-agnostic method, and is what the PHP 
manual recommends[3].  If we go this route, it will involve removing the 
db_num_rows() function from the existing mysql and postgres drivers and 
refactoring core queries accordingly.  

B) Instead, have the PDO wrapper do a full ->fetchAll() on the result set.  
That gives an array of array or object records (specified in the fetchAll()), 
which can then be simply sizeof()ed in the PDO/db_num_rows() implementation.  
The downside here is that you need to specify in the fetchAll() whether you 
want objects or arrays.  To not horribly break any existing APIs (I'm trying 
to minimize the footprint for now; we can break everything later), we'd need 
to therefore fetchAll() as an array in the PDO driver and then cast back to 
an object in db_fetch_object() (or vice versa).  That feels quite nasty to 
me, honestly, and is probably a not-unnoticeable performance hit.

C) Drop the PDO idea.  It should come as no surprise that this is my least 
favorite option. :-(

As my goal is that in some mythical future when we can require PHP 5 PDO 
becomes our main database backend (and we can leverage it even more for a 
cleaner API, faster/more stable prepared statements, etc.), I would favor 
slimming down and generalizing our database calls now (option A) so that 
we're better prepared in the future.  (It would also make adding support for 
other database engines like Oracle or MS SQL easier.)  I am, however, open to 
input and suggestions.

I am hoping I can get this into Drupal 6, but time will tell if I can get it 
ready in time.

[1] http://www.php.net/pdo
[2] http://www.garfieldtech.com/blog/drupalcon-php5
[3] http://us.php.net/manual/en/function.PDOStatement-rowCount.php


-- 
Larry Garfield			AIM: LOLG42
larry at garfieldtech.com		ICQ: 6817012

"If nature has made any one thing less susceptible than all others of 
exclusive property, it is the action of the thinking power called an idea, 
which an individual may exclusively possess as long as he keeps it to 
himself; but the moment it is divulged, it forces itself into the possession 
of every one, and the receiver cannot dispossess himself of it."  -- Thomas 
Jefferson