[development] Referential integrity -- finally?

[Derek Wright]

On Jan 23, 2007, at 4:04 AM, Gerhard Killesreiter wrote:

> His argument is IIRC that we should stick to as plain SQL as  
> possible to not further increase the barrier of entry for people  
> already knowing SQL.

the main problem with this argument is the there's no "Standard" in  
the "Standard Query Language" for table creation and schema  
modifications across all the databases.  so, while anyone who knows  
any variant of SQL (preferably the ANSI standard) can write SELECT  
and UPDATE and JOIN and the rest, you *have* to have DB-specific  
knowledge to create tables and add/remove/alter indexes or columns.   
to me, the lack of a standard here is the central point -- if there  
was a standard a) our situation would be totally different and b) we  
wouldn't need an abstraction layer.

IMHO, it's unwise to expect every drupal developer to learn all the  
DB-specific quirks of creating tables on *every* possible DB we're  
planning to support.  furthermore, it's crazy to expect that they're  
going to be able to test on all of them, since for example, i have no  
interest in paying top dollar for an oracle license anytime soon. ;)

but, if the people who care about each of these DBs can spend the  
time to write, test, and maintain a small layer of DB abstraction  
functions that at least properly create/modify schemas, and drupal  
developers mostly stick to ANSI SQL everywhere else, we're 95% done  
with supporting whatever DBs people want to use.

-derek

p.s. relatively speaking, table creation and schema modification is  
small percentage of the SQL queries in drupal.  basically, it's  
everything in the .install files, but nothing in the .module or .inc  
files.  so, having a reasonable DB abstraction layer for the .install  
files doesn't change the fact that most of drupal development can  
still happen in "raw" SQL (except, of course, for all the drupalisms  
we already have, like {table_name}, db_query() %d and %s handling,  
db_rewrite_sql(), etc).

p.p.s. if people really don't want to be bothered, there'd be nothing  
stopping them (except peer pressure) from continuing to use DB- 
specific schema queries directly in their .install files.  so, if new  
drupal developers are doing a custom module for a site that will only  
ever run on some DB, and they don't care about writing portable code,  
they don't have to learn anything new and there's no further barrier  
of entry.  however, it they're going to become responsible  
maintainers of important contribs, they're already expected to learn  
more than nothing, and in this case, a layer of code that hides the  
ugly details about how oracle vs. postgres vs. mssql vs. sqlite vs.  
mysql vs. whatever creates tables would be welcomed as a blessing,  
not cursed as another weird API to learn.