PostgreSQL, templates, and test databases

Rails
1

Hi everyone.

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?

Best,

Marnen Laibow-Koser
Web developer / Composer
Albany, NY, US
http://www.marnen.org
marnen AT marnen DOT org

2

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

3

Philip H. wrote:

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…

Definitely not ideal, but I had been considering this as well. However,
I’d rather be able to specify which template the test DB should be
created from. I may try installing this patch:
http://dev.rubyonrails.org/ticket/9195
but I would rather not mess around with the Rails core if I don’t have
to.

-philip

Best,
Marnen Laibow-Koser

4

Kim S. wrote:

A different approach is to modify the purge task that is in
railties/lib/tasks/databases.rake.
[…]

Thanks. I will investigate this option, perhaps changing it to read the
-T switch from database.yml (or maybe just apply that patch after all).
I’m new enough to Rails that I don’t yet really know how things work
under the hood, so this is very helpful.

Best,

Marnen Laibow-Koser
[email protected]

5

On Oct 5, 2007, at 9:48 AM, Marnen Laibow-Koser wrote:

http://dev.rubyonrails.org/ticket/9195
but I would rather not mess around with the Rails core if I don’t have
to.

-philip

Best,
Marnen Laibow-Koser

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

Kim S. - principal, Shrier and Deihl - mailto:[email protected]
Remote Unix Network Admin, Security, Internet Software Development
Tinker Internet Services - Superior FreeBSD-based Web Hosting
http://www.tinker.com/

6

Marnen Laibow-Koser wrote:

Thanks. I will investigate this option, perhaps changing it to read the
-T switch from database.yml (or maybe just apply that patch after all).
[…]

Update: I did indeed do this. It works quite well.

Best,

Marnen Laibow-Koser
[email protected]