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

[Karthik]

> How can you understand the table definition proposed by Adrian (see
> below), if you don't know any SQL?  I don't think this gets you up to
> speed any more quickly.  Quite the contrary, you'd first have to
> learn SQL and then you have to figure out how this Drupal specific
> definition format maps onto it.  Make no mistake, a Drupal-specific
> definition language is no excuse for not having to learn MySQL.  It
> does add an additional step to the learning curve!

Considering that there already is an existing DB astraction layer and
has been for x versions, I'm not entirely sure why there's a concern
for adding "table creation" to it as well. The beginner user is
already subjected to this vaunted learning curve through the use of
pager queries (and associated eccentricities), db_rewrite_sql, table
prefixing etc. IMO, Adrian's proposal just completes this process of
abstraction.

I also do not believe that the 'fiddlers' in question use the SQL in
the .install files to understand the DB structure. They use
PHPMyAdmin/PHPPgAdmin to do so (and get a neatly formatted tabular
represetation of said structure) and very likely don't give a rat's
hind-quarter as to how it got there :)

Moreover, we are already subjecting the beginner programmer to a more
complex abstration layer via the use of the forms API and this is
probably only going to get more complex. Forms are fundamentally more
important to the "tinkerer" than SQL (install) queries. Just as a
layman programmer can ditch the entire forms API and use standard HTML
forms, he/she can also just use straight SQL queries if he/she wants
to.

The immediately apparent advantages of using the proposed functions:
1. Simplify updates.inc: Some of the functions proposed are already
present, but are currently only being used for pgSQL (oddly enough).
2. Improve consistency between pgSQL and MySQL: At present, the two
structures lack congruity - for e.g. the locales_target table in pgSQL
has a UNIQUE lid whereas the equivalent MySQL table does not.
Similarly, there are discrepancies between UNSIGNED fields and so on..
3. Improve pgSQL support in contrib: Currently, a healthy (sic)
percentage of modules either don't provide pgSQL database files (in
cases where they haven't migrated to the .install system) or leave the
'pgsql' section empty in their .install files. These install functions
will effectively nullify this issue; pgSQL users won't need to know
MySQL syntax and vice versa.
4. Make thing easier if/as/when we begin to (officially) support
Oracle/MSSQL/Sybase/... I'm sure osinet/fgm who (IIRC) uses Firebird
will appreciate this.
5. Improve code brevity.
6. GOTO 1 :P

w.r.t. point 2: IMO changes to the .??sql files should only be made by
(the committer) applying an update to a fresh database and then taking
a database dump. The database should be of the same version as in
Drupal's minimum requirements.

In terms of readability, I think something along the lines of the fapi
formatting will make things less daunting:

db_create_table('client_system', array(
  'cid' => array(
    'type' =>  'int',
    'NOT NULL' => TRUE,
    'PRIMARY KEY' => TRUE),
  'name' => array(
    'type' => 'short text',
    'NOT NULL' => TRUE,
    'default' => '',
    'PRIMARY KEY' => FALSE),
  'type' => array(
    'type' => 'short text',
    'NOT NULL' => TRUE,
    'default' => '')));

If a newbie can't understand the above as it is, then he/she very
likely won't understand it when it's written in SQL either (and vice
versa heh). Users are also free to view the .??sql file.

The primary precaution that needs to be taken is in the choice of
semantics for contentious keywords.

In my opinion, the positives outweigh any negatives.

Thanks,
-K
P.S. -1 for any plans to ditch .sql files altogether.