[support] Drupal 7 Database API

[Jason Flatt]

Here's a comment by the DBTNG author, Lerry Garfield on the subject:
http://lists.drupal.org/pipermail/support/2011-October/019771.html

It was changed to utilize PHP Data Objects:
http://php.net/manual/en/book.pdo.php

I suspect there's a really long thread somewhere in Drupal space that 
outlines all the pros and cons, but one of the big things it does is 
gives a better way to deal with multiple types of database servers, w/o 
having to write a specific query for each one. For the vast majority of 
users who are on MySQL, that is of no importance, but for the growing 
everyone else, it is.

You don't have to do it the way you're doing it.  Here is an alternative 
method, which is closer to the old way:

$query = "select * from {foo} where bar>=:day order by bar desc limit 
:limit";
$results = db_query($query, array(':day' => date_sub(now(), interval 1 
month), ':limit' => variable_get("baz", 10)));

You can get more information on these pages:
http://api.drupal.org/api/drupal/includes--database--database.inc/function/db_query/7
http://api.drupal.org/api/drupal/includes--database--database.inc/group/database/7


On 01/10/2012 02:53 PM, Tim Gustafson wrote:
> Hi,
>
> Before I ask my question, I'd like to throw out a disclaimer here: I thought pretty long and hard about sending this e-mail because I know that it is going to rake up the inevitable "procedural versus OO" war, but I feel this is worth asking.  I've been programming for more than 20 years, and I know that "procedural vs OO" is not something that anyone will ever win.
>
> That said...
>
> I'm in the process of converting about three dozen modules I've written for my organization from Drupal 6 to Drupal 7.
>
> I get that when you go from one API version to the next in a system like Drupal that there are always changes.  Those changes are usually needed to make some new feature available or to make something "better" in some sense.  I get that.
>
> What I don't get is: why did Drupal decide to re-invent SQL as an object-oriented interface?
>
> I'm finding is that a simple on-line SQL query in a Drupal 6 module, for example:
>
> $foo = db_query(
>    "select * from {foo} where bar>= date_sub(now(), interval 1 month) order by bar desc limit %d",
>    variable_get("baz", 10)
> );
>
> has changed to:
>
> $foo = db_select("foo", "f")
>    ->fields("f")
>    ->condition("bar", "date_sub(now(), interval 1 month)", ">=")
>    ->orderBy("bar", "desc")
>    ->range(0, variable_get("baz", 10));
>
> I'm having a hard time understanding how taking a well-defined, extremely popular, gold-standard language (SQL) and changing it into this OO code makes anything better.  The code is not any more readable than the straight SQL statement, in my opinion.  I can only imagine how it looks when I get into doing complex joins and unions and such.  And I can't debug it by copying-and-pasting it into a mySQL client window.  I'm guessing that from the standpoint of raw code efficiency, it has got to be slower than the single-line-query approach because you're iterating through PHP's internal function calling routines five times now instead of just once and each one of those calls has some overhead related to decoding the variables it receives.
>
> What does this switch from regular SQL statements to OO get us?
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Tim Gustafson                                                tjg at soe.ucsc.edu
> Baskin School of Engineering                                     831-459-5354
> UC Santa Cruz                                         Baskin Engineering 317B
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>

-- 
Jason Flatt
* Custom Web development solutions: http://www.oadaeh.net/
* Internet hosting solutions: http://www.flattnet.com/
* Computer and network service and support: http://www.flattandsons.com/
* Drupal: http://drupal.org, Linux: http://ubuntu.com, Android: 
http://android.com
* Father of seven: http://www.flattfamily.com/ (Joseph, 18; Cramer, 16; 
Travis, 14; Angela; Harry, 11; William, 5; and Dolly, 3)