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

[Gerhard Killesreiter]

Dries Buytaert wrote:
>
> On 13 May 2006, at 13:15, Derek Wright wrote:
>
>> i'm not exactly an amateur, but i didn't know *any* SQL syntax as of 
>> 5 months ago when i first tried setting up a drupal site for my 
>> band.  for me, the kind of abstraction layer adrian proposed (and 
>> others expanded on) would have probably helped me get up to speed 
>> more quickly.  maybe i'm in the tiny minority of potential drupal 
>> developers who aren't already SQL wizards, but who know how to write 
>> code (i didn't know php either, but the syntax is enough like perl 
>> and C and the docs are good enough that i could figure it out).  
>> however, from my perspective as still something of a drupal novice, i 
>> don't think this would have slowed me down or deterred me in any way.
>
> How can you understand the table definition proposed by Adrian (see 
> below), if you don't know any SQL?

When I started to use Drupal I did know neither PHP nor 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!

No. I never learned SQL properly. I just copy and pasted the queries 
that I found and then modified them untill they gave me what I wanted.

>       db_create_table('client_system', array(
>         'cid' => array('type' =>  'int', 'NOT NULL' => TRUE, 'PRIMARY 
> KEY' => TRUE),
>         'name' => array('type' => 'short text', 'NOT NULL', 'default' 
> => '', , 'PRIMARY KEY' => TRUE),
>         'type' => array('type' => 'short text', 'NOT NULL', 'default' 
> => ''),
>       ));
>
> Hundreds of books have been written about PHP and MySQL.  As a newbie, 
> you can buy these books and understand what Drupal's MySQL schemas mean.

While that is true, most people don't buy these books. I think that 
Adrian's proposed scheme isn't harder to learn from scratch than the 
usual SQL queries. The only drawback is that when you learn plain SQL 
you can use it elsewhere too while you can't do so with Adrian's 
abstraction layer.

>   If we add an extra abstraction layer, these books are going to be 
> less useful.  Hence, we added a barrier.
>

If nobody buys these books, this is a moot point. :p

it is also easy to add a conversion utility that shows a user how the 
query above would look in plain SQL (in whatever version they want to 
have it!).

> It does, however, help you write portable code, but that isn't 
> something most people are concerned about.  Newbies want to write code 
> that works, and that they can understand.  Being able to understand 
> something is very rewarding/motivating.  Portability is not their main 
> concern.  IMO.

Right. But as a project, it should be _our_ concern. As a module author 
I would certainly appreciate if all my modules would work with pgsql 
(and maybe other backends) without further worries.

> I'm not saying that the database definition function is a bad thing or 
> that we should drop it on the floor.  We have to carefully evaluate 
> whether the advantages (portability) outweight the drawbacks (less 
> developers).

Any developer worth the name should be easily able to understand this 
scheme if he has prior knowledge of SQL. If he hasn't then it doesn't 
matter (to us) if he learns plain SQL or our db abstraction layer.

> All I can say, is that, as a programmer, I can write SQL faster than I 
> can write db_create_table() definitions.

Use an editor which allows you to insert templates. :p

>   Plus, I value the transparency of the "raw" SQL statements; at least 
> I know _exactly_ what is going on under the hood.

That's true, but as a said a convertor is easy to write and will already 
be somewhere in Drupal's code. I think that most serious code editors 
could also have templates that call a function to convert any API 
expression on the fly.

> Here is something to think about (I'm still trying to figure out the 
> best way to formulate this):
>
>    The net effect of creating abstracting layers for everything is that
>    you're building a new programming language on top of the existing
>    languages (eg. PHP and MySQL).

Right.

> At some point, you'll loose some of
>    the underlying languages' advantages (eg. available documentation,
>    talent pool, transparency).

True.

> You see this happen in the Java world.  Because of all the layers 
> (EJBs, Struts, JSTLs, JSPs, Beans, CMP, BMP, JAXM), J2EE started to 
> live a life on its own.  Being a "regular" Java developer isn't enough 
> to get certain jobs.

I don't think I'd apply for regular plain-PHP jobs or maybe typo3 jobs 
either.

To sum it up: For the Drupal project having a truely portable database 
API is invaluable. That it inconveniences some people is a given, but 
I'd accept that. What Adrian proposes is much more readable than what 
I've seen on other abstraction stuff.

Cheers,
    Gerhard