[development] PDO and database limitations

Fri Apr 6 20:47:37 UTC 2007

On 06/04/07, Larry Garfield <larry at garfieldtech.com> wrote:
>
> I think you're misunderstanding what PDO is.  PDO is not an ActiveRecord implementation.  It's a unification of the disparate mysql_*, pgsql_*, odbc_*, etc. functions in PHP, which has always been ugly.  It provides a prepared statement emulation layer for databases that don't support them directly as well as a transaction implementation that still works transparently (albeit with no effect) on systems that don't natively support transactions.  It does not provide any sort of abstraction for SQL statements themselves.
>
> http://www.php.net/pdo
>

"PDO provides a data-access abstraction layer, which means that,
regardless of which database you're using, you use the same functions
to issue queries and fetch data. PDO does not provide a database
abstraction; it doesn't rewrite SQL or emulate missing features. You
should use a full-blown abstraction layer if you need that facility."
ohh...ok  *face is red*

> At this point I'm not looking to change the entire database API.  At least not until Drupal 7. :-)  For now I'm just trying to provide an alternate backend in addition to the existing mysql and pgsql backends because PDO's prepared statement support is way better than ours could ever be, since it's all happening down in C code.  That should (I believe) give a nice performance boost for those running PHP 5.2 where PDO is included natively (and give a nice "hey, stop using PHP 4 would you!" nudge without removing support for PHP 4 from Drupal itself).  I'm trying to keep the API changes as minimal as possible for now.

Ok, sounds like a plan and it will reap some of the benefits I
mentioned. Hmm..I think I just volunteered to help.

>
> ActiveRecord and object relational mapping (what you're talking about here) in PHP 4 is ugly, nasty, and evil.  In PHP 5, it *can*, if done right, be very thin and very powerful and very cool.  (I've been doing some research on that separately from Drupal.)  The question of using an ActiveRecord/ORM in Drupal is really something that is not worth thinking about until we know we can demand PHP 5, which I figure is at least another year away.

Actually I wasn't specifically talking about the ActiveRecord pattern
or ORM, I meant any type of unified data access layer - Windows ADO
came to mind because the methods in PDO reminded me of it. But my
point is moot because PDO isn't trying to do database abstraction like
ADO.

>Database table type (MyISAM vs. InnoDB) is also completely unrelated
to what I'm doing.
>
I had a brain fart there, what I meant to write was PHP 4 and MyISAM
are the lowest common denominator; and can run practically everywhere;
whereas if Drupal started to require more advanced database
functionality to support PDO and transactions and what not, it would
raise the bar out of reach of many people. Ok, no more posting to MLs
before breakfast.

> I will, however, agree with you that Muppet Show > Monty Python. :-)
>
The work continues...

>
> On Fri, 6 Apr 2007 09:54:18 -0400, "Allister Beharry" <allister.beharry at gmail.com> wrote:
> > 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
>
>