PostgreSQL, templates, and test databases

I'm having a bit of a problem with an application I'm currently
developing with Rails 1.2.3 and PostgreSQL, attempting to use test-first
development. Here's the scenario:

* I have the usual three databases, with migrations. Testing has been
working beautifully.

* I need to store geographic data, so I add PostGIS to all three
databases (along with SpatialAdapter -- thanks, Guilhem!). Development
and production databases are happy.

* However, testing no longer works. Why? Because the test database is
always dropped and recreated -- so that all the PostGIS goodness goes
bye-bye before each round of tests.

It is not feasible to put the PostGIS infrastructure in a YAML fixture
file or a Ruby migration (the damned thing is huge!). However,
PostgreSQL allows a template to be specified when creating a database --
so my troubles would be over if I could specify something like
"template: template_postgis" in database.yml. But that doesn't seem to
work.

So...is there a way to specify which template the test database will be
created from in PostgreSQL? If not, what should I do?

Perhaps not ideal for you, but you could add PostGIS to the template1 database... so that by default any new database you create would get it...

-philip

A different approach is to modify the purge task that is in
railties/lib/tasks/databases.rake. I have changed the one I use to
delete everything out of the test database instead of dropping it and
recreating it. The diff of my version is:

     desc "Empty the test database"
     task :purge => :environment do
       abcs = ActiveRecord::Base.configurations
       case abcs["test"]["adapter"]
         when "mysql"
           ActiveRecord::Base.establish_connection(:test)
           ActiveRecord::Base.connection.recreate_database(abcs["test"]["database"])
         when "postgresql"
           ENV['PGHOST'] = abcs["test"]["host"] if abcs["test"]["host"]
           ENV['PGPORT'] = abcs["test"]["port"].to_s if abcs["test"]["port"]
           ENV['PGPASSWORD'] = abcs["test"]["password"].to_s if abcs["test"]["password"]
- enc_option = "-E #{abcs["test"]["encoding"]}" if abcs["test"]["encoding"]

           ActiveRecord::Base.clear_active_connections!
- `dropdb -U "#{abcs["test"]["username"]}" #{abcs["test"]["database"]}`
- `createdb #{enc_option} -U "#{abcs["test"]["username"]}" #{abcs["test"]["database"]}`
+ search_path = "--schema=#{search_path}" if search_path
+ `pg_dump -i -U "#{abcs["test"]["username"]}" -s -c -O #{search_path} #{abcs["test"]["database"]} | ruby -n -e 'if (/^DROP / =~ $_) && (/^DROP PROCEDURAL LANGUAGE/ !~ $_) && (/^DROP SCHEMA/ !~ $_) then $_.sub!(/;$/, " CASCADE;") if ($_ =~ /^DROP SEQUENCE/) || ($_ =~ /^DROP TABLE/); print; end' > db/test_structure_clean.sql`
+ `psql -U "#{abcs["test"]["username"]}" #{abcs["test"]["database"]} < db/test_structure_clean.sql`
         when "sqlite","sqlite3"

Kim