When developing Drupal one often needs to pull recent copies of the live database into the dev environment.

Loading a dump into the dev database will update any existing tables, add any new ones - but it won't remove tables from the dev environment that re not in live.

This causes problems with Drupal as module install and update hooks may need to create tables which don't yet exist on live.

My solution which assumes you have .my.cnf set up to provide login locally is below.

Most of the time I can get what I want out of SQL using the standard aggregate functions, but today I needed to find the latest timestamp from any one of three columns.

I achieved this using the GREATEST() function, it's not party of the SQL standard but is commonly available.

GREATEST(value [, ...])

An interesting post from Lullabot today on problems with Drupal's Schema API and the Twitpocalypse

It turns out that the Schema API doesn't properly understand the difference between different types of integers 

For most of 2008 I've been working on a large Drupal installation (around 10 developers,  something like 100 custom modules, 17 languages, split over 2 sites).

This all adds up to a lot of updates, and schema changes.

Drupal's hook_install, and hook_update_N functions are pretty good, but not perfect at bringing order to this chaos.

For the last few months I've been working on a project that is part website and part data processing.

In theory the website is the bulk of the project, but in practice processing the data has taken a disproportionate amount of resources.

Data exchange with another organisation is always tricky - and in this case we have incoming data that doesn't conform to specification, business rules that change, and a daily data import to run.

The code I've been working on has been through a couple of versions, and now I have in mind a third

Drupal allows you to create multiple sites sharing some or all of the same code and optionally sharing some database tables.

The documentation for the Drupal settings file
http://api.drupal.org/api/file/sites/default/settings.php/5/source

includes the following

<?php
/*
* To have all database names prefixed, set $db_prefix as a string:
*
* $db_prefix = 'main_';
*
* To provide prefixes for specific tables, set $db_prefix as an array.
* The array's keys are the table names and the values are the prefixes.

Comments in code are widely used and generally (but not universally) accepted as a good thing.

Databases can also accommodate comments - but these seem to be very rarely used.

I'm of the opinion that database comments are very useful.

It's often hard to determine the exact meaning of a column - maybe it would be ideal to rename the column but you don't want to risk breaking code.

I’ve just been experimenting with text searches: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html