[database] [Database] Toward better databases

[Larry Garfield]

On Monday 07 July 2008 8:44:40 am you wrote:
> Howdy, everyone.  Here's my current brain-dump:
>
> 1.  Larry is correct that the pgsql driver is not currently working.
> I haven't had a chance to bring it up to date with the latest API/
> internal changes Larry made in the PDO patch.  However, there is not
> actually any difficulty related to pgsql specifically; interacting
> with pgsql through the PDO_PGSQL interface works fine and I've had
> everything (including the blob support) working fine before.  It's
> just a question of updating it for the latest version of the patch,
> which I haven't done yet.  You know, the day job keeps me pretty
> busy. :-)

I know the feeling. :-)

However, the problem I'm running into I have managed to track down (10 minutes 
with a working debugger is worth weeks without one, dear god!), and it is 
highly confusing.  The problem comes when creating the variable table.  All 
of the test queries run fine, but when it gets to this query:

CREATE TABLE variable (
	name varchar(128) NOT NULL default '',
	value text NOT NULL,
	PRIMARY KEY (name)
)

PHP dies completely.  I have not figured out why.  That query runs fine within 
the psql command line, and it runs fine in a sample script I've created that, 
I think, is mimicing the way the new Drupal driver works.  I am highly 
confused by this, and it is making me sad. :-(

Josh, do you have any idea why the above query could be failing?

> 2.  The database inspection code for pgsql (and mysql) is already
> written and working in Drupal's schema.module.  This code will allow
> the database layer to determine the type of a table column that does
> not exist in Drupal's schema.  I do not think schema.module in its
> current state is ready to be committed to core.  However, we don't
> really need all of it, or even all of the database inspection code.
> Right now all we need is something like
>
> db_inspect_column($table, $column)
>
> which returns a Schema API field structure for the column, including
> its type.  The subset of schema.module should not be hard to pull out
> and add to the PDO patch.
>
> One snafu, however: db_inspect_column() may require database
> privileges that other Drupal operations do not.  We'll have to look
> into that and whether it is an acceptable requirement (the answer will
> probably be "yes, because there is no alternative").
>
> 3.  Larry isn't going to like this, but part of the reason I haven't
> mustered the time to finish the pgsql driver is that I am not 100%
> convinced the design for the PDO-based database layer is right yet,
> and figuring out a proposal to fix it requires more time than I've had.
>
> 3a.  One issue is that the way that SQL and placeholders are generated
> seems fragile and inefficient to me.  It ends up being fairly awkward
> for the pgsql driver to keep everything straight in the way it needs
> to in order to do its most basic function of calling bindParam()
> correctly (though it is much better than in early drafts of the patch).

Once I actually get the damned thing installed, I will take a look and see if 
it can be simplified.

> 3b.  I also have some concerns that the public-facing API is more
> complex and incongruent than it needs to be (I've expressed these to
> Larry before, and he disagrees).

It is as simple as we can make it and meet the desired functionality. :-)  
(Actually it has changed considerably based on your input so far, which has 
been very good for it.)

> 3c.  Also, the new database layer is substantially more complex and
> difficult to understand than our current layer (just trying to
> determine exactly where queries get executed is a challenge) and I'm
> concerned it will both meet substantial community resistance and (more
> importantly) substantially reduce the number of people who are able to
> contribute to it.  Of course, this is equally true of the Fields in
> Core project I'm working on, so maybe it is just unavoidable at this
> point in Drupal's development.

I think it is.  The old layer was basically just a dummy wrapper around 
mysql_query() and pgsql_query().  Anything more featureful than that is going 
to be an increase in complexity.  Given what we're asking it to do, I think 
it's actually reasonably straightforward, at least to someone who is 
OOP-knowledgeable.  

> Perhaps the existence of this mailing list will motivate me to re-
> visit the pgsql driver, try to get it working again, and in the
> process figure out what I really think regarding at least 3a and 3b.
> Oh, and look into 2 as well. :-)

Yes, please, and soon. :-)  Even with the delayed code freeze there's a 
crapload of work that has to be done after the initial patch lands, and right 
now Postgres support is the reason it hasn't landed yet.  

-- 
Larry Garfield
larry at garfieldtech.com