[development] Extend database abstraction layer, to include table creation

[Syscrusher]

On Friday 12 May 2006 15:27, moshe weitzman wrote:
> Pople might misuse it is not a justification for omitting code from
> core. Building a collection from a db recordset an extremely common
> pattern and including this convenience function reduces code and makes
> it more readable.

+1

Every time we can turn this:

     do_a_query()
     while (not_done_with_rows) {
         put_another_row_into_array
     }
     return rows_array

into this:

     return fetch_all_from_query()

we have made some Drupal module smaller, faster to compile, easier to read, and
more maintainable. It's not a matter of whether programmers are *able* to use
a while() loop -- it's a matter of whether they should have to code the same
loop pattern again and again and again.

I would go even further, to suggest three other very common usage patterns that
should get dedicated functions:

1. Execute a query that will return exactly one row, and return that row into
   an object. If the query returns multiple rows, return only the first one.

   Example use case:  SELECT * FROM {node} WHERE nid=1234;

   Suggested function signature: $object = db_query_row($sql [, $params])

2. Execute a query that will return exactly one column of one row, and return
   that data value as a simple variable of mixed type.

   Example use case: SELECT COUNT(*) FROM {node} WHERE type='story';

   Suggested function signature: $mixed = db_query_value($sql [, $params])

3. Execute a query that will return exactly one column of an arbitrary number
   of rows, and return those values as an integer-subscripted array.

   Example use case: SELECT DISTINCT type FROM {node} ORDER BY type;

   Suggested functoin signature: $array = db_query_column($sql [, $params])

Aside from the reduction in code size of all the modules that use the new
functions, there is an additional advantage. Right now, there is no way to tell
the database layer that one is finished with a query, to free up the result
set resources. Since these functions -- as well as the db_fetch_all() function
previously discussed by others -- all have *internal* knowledge of when the
underlying SQL query is finished, they can transparently release the internal
result set resource before they return.

The PEAR code, which is GPL, could be easily adapted to provide these functions.
I'm willing to volunteer to do it, though it sounds as if there are others who
are eager to work on this project.

Scott

-- 
-------------------------------------------------------------------------------
Syscrusher (Scott Courtney)          Drupal page:   http://drupal.org/user/9184
syscrusher at 4th dot com            Home page:     http://4th.com/