SQL Greatest and Least

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 [, ...])

Read more

Adding more data types to schema API for Drupal 7

I've written a patch for Drupal 7 to enable date and time data types to be defined for the schema API.

http://drupal.org/node/200953

There seems to be some resistance to this idea due to concerns about compatibility between database versions (Drupal now supports MySQL, PostgreSQL and sqlite).

Personally I don't think Drupal can afford to work at lowest common denominator level if the aim is to be a serious option for the enterprise

Read more

Restoring an old postgresql database

I’ve just had cause to restore a two year old backup of a database I haven’t used in a while

Since then postgresql (on Debian at least) has added the option to
have multiple ‘clusters’ and multple versions running on the same
machine.

Fortunately the old version (7.4) was still available

Steps

Read more

Project : Use Your Paths

An innovative project which saw thousands of people involved in updating a UK map
screenshot

The whole site is fully bi-lingual (English/Welsh) with a Custom Content Management System enabling all text elements to be easily edited in either language.

The site uses a custom Map interface - this used satellite images at large scale conveying the feel of the green countryside, and detailed walking maps at the small scale.

Read more

Full text searching on PostgreSQL with tsearch2

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

It’s worth noting that most of the commands in the above tutorial
assume you are running as the database superuser, this is required to
run the setup script but then you can grant permission for an ordinary
user for the search tables (in the public schema names starting pg_ts_)

Read more