[development] Referential integrity -- finally?

[Larry Garfield]

On Tuesday 23 January 2007 4:08 pm, Gerhard Killesreiter wrote:

> > Conclusion: there is no win-win situation -- each direction has
> > advantages and disadvantages.
>
> Yeah, but we need a decision on what to do.
>
> This is not only valid for db_add_columns & friends, but also for other
> things like db_insert &tc that have been proposed.

db_insert() and friends have been in the helpers module for several months 
now.  I've used them with great success.  (I'd be happy to see them moved 
into core, but I don't expect miracles. <g>)

However, I also disagree that there's no win-win situation, because it doesn't 
have to be either/or.

I maintain a few small modules.  None have particularly complex SQL.  My SQL 
experience is 95% MySQL, with a wee bit of MS SQL Server thrown in for bad 
measure.  Currently none of my modules support PostgreSQL.  I simply don't 
know it, nor do I have any personal motivation to learn it.  Similarly I know 
almost nothing about Oracle.

Really, there's no way that any of my modules are ever going to support 
PostgreSQL and Oracle and SQLite unless there's a common abstracted 
create_table_schema() function of some sort, or someone else writes the 
install code for it.  I'm sure some would say I'm being 
lazy/selfish/irresponsible (and maybe I won't disagree with them <g>), but 
the way I see it, they're a side hobby for me, for the most part, so I don't 
see a reason to spend extra time on learning and supporting a database that I 
never use and can't even test on.  However, I *would* be willing to learn a 
few extra PHP function calls that would let me support "all supported 
databases", at least enough to be fully functional if not fully optimized.  

For more complex tables or where heavy field-order optimization matters, 
there's still raw db_query().  I see nothing at all wrong with an install 
function that looks like this:

create_table('foo', array('nid'=>'int', 'title'=>'string'));

switch ($db_type) {
  case 'mysql':
    // MySQL is faster if we do X instead
    db_query("ALTER TABLE {foo} CHANGE ...");
    break;
}

Allowing both a "generic" DDL API and an option for more specific tweaks where 
needed does create a win-win situation.  Module authors can easily make their 
code function on all databases Drupal supports while still having the option 
of making database-specific optimizations.  My modules can "just work" 
everywhere, and CCK can be fine-tuned to each database as needed, and 
everyone wins.


> Also, I have found a few people in #drupal that help me with with SQL
> related questions. Nobody has yet complained about our API. Of course i
> usually feed them the processed queries from the database logs.
>
> Cheers,
> 	Gerhard

Well some of us are just PHP people who spend too much time writing SQL. :-)

-- 
Larry Garfield			AIM: LOLG42
larry at garfieldtech.com		ICQ: 6817012

"If nature has made any one thing less susceptible than all others of 
exclusive property, it is the action of the thinking power called an idea, 
which an individual may exclusively possess as long as he keeps it to 
himself; but the moment it is divulged, it forces itself into the possession 
of every one, and the receiver cannot dispossess himself of it."  -- Thomas 
Jefferson