It's often useful to be able to recreate an empty copy of your database. Most popular database systems come with utilities to export your database's DDL to a flat file so you can recreate the structure elsewhere. Here's a list of how to do it on various popular database systems:

IBM DB2

db2look -d databasename [-z schemaname] -e -o outputfile

I've only tested this one on DB2 8.1, but I believe the db2look utility also comes with DB2 7.

PostgreSQL

pg_dump -s dbname > outputfile

MySQL

mysqldump --no-data databasename > outputfile

I've never had to do this in SQL Server or Oracle. If anyone knows the commands for those - or any other popular DB - please post comments!

I spent a little while tonight getting ramped up to finally implement a database layer for Blosxonomy, and discovered that getting Ruby DBI working was more difficult that I expected. In particular, it turns out my Mac had multiple versions of Ruby installed, resulting in mis-linked binaries that cause abnormal terminations when I tried to use DBI. The specific error I got was:

/usr/lib/ruby/site_ruby/1.8/powerpc-darwin8.3.0/postgres.bundle: [BUG] Bus Error

Here's what I did to resolve this error and get DBI working:

Cleanup your Rubies

OS X 10.4 ships with Ruby 1.8.2. I had also installed Fink, which installed Ruby 1.8.1. I don't recall if I caused it to do that, or if it was a dependency of something else. This was my first problem. You can check to see if you have them both installed by doing:

/sw/bin/ruby --version
/usr/bin/ruby --version

Ideally, only /usr/bin/ruby will work, and /sw/bin/ruby won't exist. If this is not the case, you should remove the Fink installation. The latest version of Ruby DBI wouldn't link against Ruby 1.8.1, it would only link against 1.8.2 or 1.8.3. To remove Fink's Ruby, I did:

fink remove ruby18-dev ruby

This removed Ruby 1.8.1 and the development header files from your system.

Next, we'll upgrade to Ruby 1.8.3 - Download 1.8.3 Here, extract the tar bundle, and perform the following steps:

configure --prefix=/usr
make
sudo make install

I have XCode 2.2 installed, and use GCC 4.0 by default and did not have any problems. You shouldn't either. This will install the new Ruby over the old one, and also install the new development headers. Confirm that the installation was successful:

ruby --version

Should now show version 1.8.3

Install Ruby PostgreSQL

Download the latest snapshot PostgreSQL Ruby bindings from http://ruby.scripting.ca/postgres/. At the time of this writing, the latest snapshot is 20051127, which is compatible with PostgreSQL 6, 7 and 8. The installation is simple:

ruby extconf.rb \
	--with-pgsql-include-dir=/path/to/postgres/includes \
	--with-pgsql-lib-dir=/path/to/postgres/libs

make
sudo make install

You can omit the --with-pgsql... options if you have PostgreSQL installed in a standard location. I installed PostgreSQL 8.1 using Fink, and did not need to specify these options.

Ruby DBI

Finally, we'll install the DBI library. You can download it from their Rubyforge project page. At the time of this writing, version 0.0.23 is the latest, released on May 20, 2004

Again, installation is very easy, just do:

ruby setup.rb config --with=dbi,dbd_pg
ruby setup.rb setup
ruby setup.rb install

--with=dbi,dbd_pg tells the config to prepare the DBI library, and the PostgreSQL driver. You can specify as many plugin drivers as you like in that. Popular ones will include dbd_mysql, dbd_odbc and dbd_db2. Once this step is complete, you'll be able to connect to your PostgreSQL databases using DBI.

A very very very short sample

Here's the quick sample I was using to test that DBI was properly installed. It's very simple and doesn't do anything at all, but it makes calls to DBI enough to have caused the error earlier.

require 'dbi'
dbh = DBI.connect('DBI:pg:databasename')

dbh.disconnect