[development] PDO and database limitations

[David Strauss]

As long as we're discussing major database overhauls, my main concern is
moving to proper use of referential integrity and transactions. Locking
entire tables has a very detrimental effect on high-traffic site
performance. It also messes with existing transactions. Of course, this
would require dropping MyISAM support.

Transactions need not be annoying. I very recently released a module to
make transactions much easier:

http://drupal.org/project/pressflow_transaction

Yes, it requires PHP 5 to perform its magic, but so does PDO.

Larry Garfield wrote:
> 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
> 
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 186 bytes
Desc: OpenPGP digital signature
Url : http://lists.drupal.org/pipermail/development/attachments/20070404/bbab0349/attachment.pgp