From: Jeremy Kemper <jeremy@...>
Subject: Re: a remote database issue
Newsgroups: gmane.comp.lang.ruby.rails
Date: 2005-03-01 17:23:33 GMT (3 years, 17 weeks, 6 days, 10 hours and 14 minutes ago)

Dick Davies wrote:
> Personally I'd like to see us get away from pg_dump and find another way to
> pull the schema from development and drop everything from the test db
> (rather than dropdb/createdb).
> I'm beginning to think this should be done in the adapter..

Rails assumes a "live db" approach: work with the development db using
some kind of tool then version-control the schema dump.  The development
database is the authoritative source.

Instead, I write out the schema SQL in a text file and generate a master
db from it.  Then createdb can use the master as a template to create
the development and test databases.  Here's a sample config/database.yml
and Rakefile:

- database.yml ---
# Don't need database, etc.  Specify username and rely on .pgpass
superuser:
  username: foobar

# Contains the "master" schema and fixtures data.  Used as a template
# to create development and test database.  We don't need a username
# and other details since we won't ever connect from Rails.
fixtures:
  database: fixtures

development:
  adapter:  postgresql
  database: development
  username: foodev
  password: abc

test:
  adapter:  postgresql
  database: test
  username: footest
  password: def

- Rakefile ---
SCHEMA_SQL   = "#{RAILS_ROOT}/db/schema.pgsql"
FIXTURES_SQL = "#{RAILS_ROOT}/db/fixtures.pgsql"

desc 'Regenerate everything the master schema.'
task :regen => [:regenerate_fixtures, :fresh_dev_db, :yaml_fixtures]

desc 'Regenerate fixtures database from the master schema.'
task :regenerate_fixtures => :environment do
  conf = ActiveRecord::Base.configurations
  superuser = conf['superuser']['username']
  fixtures = conf['fixtures']['database']

  `dropdb -U #{superuser} #{fixtures}`
  `createdb -U #{superuser} #{fixtures}` or abort 'createdb failed'
  `psql -U #{superuser} -f #{SCHEMA_SQL} #{fixtures}` or abort 'schema
load failed'
  `psql -U #{superuser} -f #{FIXTURES_SQL} #{fixtures}` or abort
'fixtures load failed'
end

task(:fresh_test_db => :environment) { clone_fixtures_to :test }
task(:fresh_dev_db  => :environment) { clone_fixtures_to :development }

def clone_fixtures_to(target_label)
  conf = ActiveRecord::Base.configurations
  superuser = conf['superuser']['username']
  target, fixtures = conf[target_label.to_s]['database'],
conf['fixtures']['database']

  `dropdb -U #{superuser} #{target}`
  `createdb -U #{superuser} -T #{fixtures} #{target}`
end

desc 'Dump the fixtures database to yaml fixtures.'
task :yaml_fixtures => :environment do
  path = "#{RAILS_ROOT}/test/fixtures"
  sql = 'SELECT * FROM %s'

  ActiveRecord::Base.establish_connection(:fixtures)
  ActiveRecord::Base.connection.table_names.each do |table_name|
    i = '000'
    File.open("#{path}/#{table_name}.yml", 'wb') do |file|
      file.write ActiveRecord::Base.connection.select_all(sql %
table_name).inject({}) { |hash, record|
        hash["#{table_name}_#{i.succ!}"] = record
        hash
      }.to_yaml
    end
  end
end

Then make your test tasks depend on the :fresh_test_db task instead of
:clone_structure_to_test.  All fixtures will be loaded, so you don't
have to declare them in your test.  Instead, isolate your changes to the
test database by starting a transaction in setup and rolling back in
teardown.  This makes managing foreign key constraints simple and is
much faster than deleting and inserting all fixtures for every test.

Ramble off,
jeremy