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@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Had to Smile - with 45 years programming, I think I see a possible goal of the oo design;
By treating every data element as a separate object the API can scan better for SQL injection and other hacks, while improving the ability to be database engine independent. Think about, sometime in the future, supporting a Chinese language version of SQL, as terrifying as that might appear on the surface, the "better" version OO API might just be able to interface to it (might being the operative word here). :-)
If it ain't broke,,,,,, some day it will be.
Warren Vail
-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Tim Gustafson Sent: Tuesday, January 10, 2012 2:54 PM To: support@drupal.org Subject: [support] Drupal 7 Database API
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@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -
Ah yes we ol' dinosaurs are still worth our salt! Thanks for that Warren!
Tony
tony mac is building web sites.
-----Original Message----- From: "Warren Vail" warren@vailtech.net Sender: support-bounces@drupal.org Date: Tue, 10 Jan 2012 15:54:07 To: support@drupal.org Reply-To: support@drupal.org Subject: Re: [support] Drupal 7 Database API
Had to Smile - with 45 years programming, I think I see a possible goal of the oo design;
By treating every data element as a separate object the API can scan better for SQL injection and other hacks, while improving the ability to be database engine independent. Think about, sometime in the future, supporting a Chinese language version of SQL, as terrifying as that might appear on the surface, the "better" version OO API might just be able to interface to it (might being the operative word here). :-)
If it ain't broke,,,,,, some day it will be.
Warren Vail
-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Tim Gustafson Sent: Tuesday, January 10, 2012 2:54 PM To: support@drupal.org Subject: [support] Drupal 7 Database API
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@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -
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/d... http://api.drupal.org/api/drupal/includes--database--database.inc/group/data...
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@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A couple of notes about the Drupal 7 API:
The Object Relational Mapping(ORM) construct is a powerful one when writing query builders. I've recently used this in building an "advanced search" piece of functionality in a product and can really see the benefits there.
Even the DB experts at Drupal agree that using the db_select should only occur when you need to build queries dynamically. You should not "always" use db_select. Db_query still works and is preferential in most cases for the performance reasons you stated.
ORM can be beneficial to database interoperability, provided that there are functions that abstract the differences. (Not all of these are there yet). Your use of "range" is a good example here as those ranges might be expressed differently in different database platforms.
You can still write queries that aren't ANSII compliant as you've aptly demonstrated. Date_sub and Now() are not the same across all database platforms and so isn't likely to be cross db compliant, but then there aren't abstraction functions that I can find for these yet either.
Mostly though, there is a recent trend in web application development to think of relational databases as just another method of storage, but I think that this is really only true in a limited set of problem domains, and as you're hinting at really sells short the power of SQL. I've seen a number of db programmers write PHP code to rollup totals from results retrieved from a query rather than using GROUP BY and ROLLUP expressions. Increasingly drupal developers are storing object data from SQL in single fields in the name of efficiency, but I think these efforts are misguided.
Stick to your guns on this. Keep using SQL to its fullest potential and leave those db_query statements in your modules unless you have a compelling reason to use the ORM. My rule in porting has been to switch INSERT and UPDATE to use ORM, but leave the selects using db_query.
Dave (22 years as code monkey)
-----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Tim Gustafson Sent: Tuesday, January 10, 2012 2:54 PM To: support@drupal.org Subject: [support] Drupal 7 Database API
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@soe.ucsc.edu Baskin School of Engineering 831-459-5354 UC Santa Cruz Baskin Engineering 317B -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -=-=-
-
Jason Flatt -
Metzler, David -
Tim Gustafson -
Tony MAC -
Warren Vail