BrainBakery

There is a lot of support to all my crazy projects given by Onoclea.com – a professional hosting service, crafted to fit you needs. Despite the great support I get from the team there are boundaries I cannot cross. One of them is no create/drop database permission for PostgreSQL. This restriction made a clash with Ruby On Rails test routine which is dropping test database without a warning. Luckily that was an easy fix to create. Now I want save your precious time sharing the tip. The only requirement is that you can have PL/pgSQL installed. (Ask your sysadmin if unsure)

Firstly you should install the following procedure in test database:

CREATE LANGUAGE 'plpgsql';
DROP FUNCTION IF EXISTS drop_all_tables();
CREATE FUNCTION drop_all_tables() RETURNS integer AS $$
DECLARE
    tname RECORD;
BEGIN
	FOR tname IN SELECT table_name FROM information_schema.TABLES WHERE table_schema='public' AND table_type != 'VIEW' AND table_name NOT LIKE 'pg_ts_%%' LOOP
		RAISE NOTICE 'DROP TABLE %;', tname.table_name;
		EXECUTE 'DROP TABLE ' || tname.table_name;
	END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

The best way would be to keep the procedure in sql file within rails application directory structure (e.g. db/sql/proc-drop_all_tables.sql) and use the following command to install PL/pgSQL.

$> psql db_name < db/sql/initial_data.sql

You want to execute the script only once, right after you’ve created test database.

Secondly you have to patch rake’s db:purge task, defined in lib/tasks/databases.rake file within rails gem. Open the file and search for lines resembling the following snippet (line numbers may vary in your case):

309
310
311
312
313
314
315
316
317
318
319

     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"
         ActiveRecord::Base.clear_active_connections!
         drop_database(abcs['test'])
         create_database(abcs['test'])

Replace drop/create calls (two last lines) with the following Ruby code:

318
319
320

         ActiveRecord::Base.establish_connection(:test)
         ActiveRecord::Base.connection.execute("select * from drop_all_tables();");
         ActiveRecord::Migrator.migrate("db/migrate/", 0)

This is it! From now on Rails will drop all tables instead of recreating database.

Tags: fix, plpgsql, postgresql, rails, ror, ruby, tip