[Database] Toward better databases

[Barry Jaspan]

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. :-)

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).

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).

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.

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. :-)

Thanks,

Barry

-------
Barry Jaspan
Code Jester, Acquia Inc.
barry.jaspan at acquia.com




On Jul 6, 2008, at 3:32 PM, Larry Garfield wrote:

> Hi all.  Curiously Dries hasn't joined the list yet, but everyone  
> else has so
> I'll make a welcome post. :-)
>
> I'd like to introduce:
>
> Joshua Drake - PostgreSQL guru extraordinaire that Dries put me in  
> touch with
> when we hit a wall on the Postgres driver.
>
> Barry Jaspan - Barry stepped forward to write the postgres driver  
> for the new
> database API, which is a good thing, too, because it forced me to  
> rewrite
> most of it. :-)
>
> David Strauss - Drupal's resident MySQL-fu master.
>
> Narayan Newton - The guy who actually runs our server cluster, and  
> also knows
> MySQL better than most.
>
> Larry Garfield - The poor sod who thought rewriting the database  
> layer would
> only take a month or two. :-)
>
> If there's any other MySQL or Postgres or SQLite or PDO gurus you  
> know, feel
> free to invite them.  Long term there is plenty that we can and  
> should do to
> improve our database handling.  Short-term, though, I just want to  
> get the
> damned patch[1] committed. :-)
>
> Right now, as far as I can determine the core system and MySQL are  
> done and
> working and solid.  The problem right now is the PostgreSQL driver.  I
> finally have a working postgres install, but every time I try to  
> install
> Drupal with the Postgres driver PHP itself dies on me.  I've been  
> trying to
> get a debugger working at home so that I can investigate, but so far  
> nothing
> has actually worked. :-)  Barry, is that something you'll be able to  
> look
> into in the near future?  You've spent far more time with the  
> postgres driver
> than I have.  If not, I'll keep trying to get a debugger working or  
> Josh, if
> you're willing I can bring you up to speed on the architecture and  
> you can
> have a look-see.
>
> The other problem we realized recently is that we are relying on  
> Schema API to
> tell us if we need to BLOB-escape a bytea field in PostgreSQL (which  
> does
> seem to be the case), and then similar information for other, sillier
> databases like Oracle.  Schema API only tells us about the main Drupal
> database.  That means only MySQL and SQLite can be used for foreign
> databases.  This is a problem.
>
> The solution we came up with is to integrate a table-schema- 
> derivation method
> into the new API so that we can pick such things up on the fly as  
> needed.  At
> that point I just sort of ran out of steam and Barry got busy, so  
> that's not
> happened yet.  Since you can't mix and match database drivers in D6  
> anyway, I
> am tempted to ask Dries if we can add that in a follow-up patch  
> because right
> now this patch is blocking a lot of other work (including cleaning  
> up Schema
> API, which may not even happen in D7 but needs to now that it's been
> OOPified).
>
> So, that's where we stand right now.  Postgres support is blocking  
> pretty much
> everything.  So, Barry, Josh, can either of you lend a hand while I  
> keep
> trying to get a debugger to play nice?
>
> [1] http://drupal.org/node/225450
>
> -- 
> Larry Garfield
> larry at garfieldtech.com
> _______________________________________________
> Database mailing list
> Database at drupal.org
> http://mail.drupal.org/mailman/listinfo/database