[development] PDO and database limitations

[Allister Beharry]

Database abstraction is generally a good thing - you get to write one
set of data access layer code and once you follow the best practices,
you can let the vendor focus on optimizing the driver for their
database engine. So in theory with PDO you could call
$rowset->rowCount() and MySQL could implement this as a call to a
C-Level native API and not even run any SQL code. You get support for
more platforms, optimization by the people who know the most about it,
and much less data access code. You also get a the ability to
implement transactions transparently, and you get closer to being able
to run transactions which enlist other apps. Goodness +4

However there are hazards. ADO worked really well on Windows because
it only ran on Windows, and there was one binary standard for Windows
which was COM and one standard for data access which was OLE DB/ODBC
and every database vendor had to play by those rules.  I don't know
how mature PDO is wrt being able to talk to different databases on
different architectures. It would really suck if you rewrote your
data-access code and then had to thrown in kludges to work around
specific issues with specific versions of different databases which is
what you were trying to get away from in the first place.

Also all modules would have to rewritten to use the new data access
layer - the performance benefits of DOs are inside methods like
rowCount(), you don't gain anything by passing in a raw SQL statement
to execute - It's in methods like setFetchmode, prepare(), execute()
where the advanyages are to be found. All db_query()s would have to be
exorcised, trying to run with a mix of DO and raw SQL code would be a
maintenance nightmare

Dropping support for MyISAM means that Drupal newbies may need to take
extra steps when creating their database - right now MySQL and MyISAM
are the lowest common denominator - just about anybody can run it.

On 04/04/07, Larry Garfield <larry at garfieldtech.com> wrote:
>
> On Wed, 4 Apr 2007 21:33:49 +0200, Dries Buytaert <dries.buytaert at gmail.com> wrote:
> >
> > On 04 Apr 2007, at 08:05, Larry Garfield wrote:
> >
> >> 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.
> >
> > I'd go for A.  :)
> >
> > If we don't need the exact count, we might be able to replace
> > db_num_rows() with something like db_table_is_empty().
>
> Seems most people are OK with A, so A it is. :-)  I'll keep an eye on the queries as I'm fixing them to try and avoid double-queries when possible.  I don't know if db_result_set_is_empty() is a good solution or not.  I guess I'll find out.
>
> > Let us know if you run into issues that need further attention/
> > discussion.
>
> One other FYI that is probably not going to break TOO much, I hope...  Since this affects db_query(), this patch will also involve moving db_query into each database.*.inc file rather than database.inc.  For the moment I'm just looking at copying and pasting them into the mysql and pgsql implementations, which so far has caused no issues.  We may, later, want to then refactor the relationship between db_query and _db_query(), but for the moment that is not something I'm worrying about.  (I suspect the pdo db_query() won't even have a _db_query(), as I'll just merge them.  TBD.)
>
> --Larry Garfield
>
>


-- 

Muppet Show > Monty Python