[PATCH] postgresql dropdb and createdb in db:test:purge

diff of databases.rake

diff of databases.rake

databases.diff is diff of edge version and databases.rake.diff is diff of 1.2.2 version

1. what about multiple schemas in database?

2. can't your testers just have own local installation of postgresql server with required privileges?

regarding first question:
1. I am sure (from your own documentation) that in database.yml there is no option for postgresql's schema
2. If there is no option for schema that means that every query in your application must have attribute :from => "<schema_name>.<table_name>" because (when you don't give a schema name it defaults to public)
3. Your implementation of migration use "CREATE TABLE <table_name> (...)" which also defaults to public)
4. Lets look something hypothetically: if a database had one schema for testing and some other for something else that dropping a database would also drop that schema which isn't necessary.
So your whole implementation of postgresql adapter doesn't support schemas, and if it did than you could just drop that schema and recreate it.

regarding second question:
1. security (you are giving a createdb right to a program)
2. many people don't want to install postgresql (especially not on windows) because it uses resources (memory) and their computer might be an older one
3. it's just easier from my perspective

rake tasks to clean postgresql db schema without drop/create db, need review and cleanup

there is option :schema_search_path for postgresql connection spec, so it works with queries, but neither migrations nor schema dumps in ruby format don't support database with multiple schemas (anyway i just use their plain sql equivs :)

I can suggest another solution, see attached rake tasks which create dumps that drop objects first, so database can be cleaned wtithout rights to createdb

And why isn't this default behavior for db:text:prepare in case postgresql is used? It is better than mine and is definitely better than dropdb and createdb. Thanks for the rake file; I will certainly use it.

Replying to meadow:

there is option :schema_search_path for postgresql connection spec, so it works with queries, but neither migrations nor schema dumps in ruby format don't support database with multiple schemas (anyway i just use their plain sql equivs :)

Migrations and schema dumps will not create schemas, or allow database objects to be created in different schemas, but if you set a schema_search_path, they will be created in the first listed schema (instead of public).

I use a schema in postgres, and have all databases on a remote server. The db:purge task is broken for this setup, so patched the databases.rake task (see mah_databases.rake.diff to drop the first schema in the schema_search_path, if defined. This is a lot faster than dropping db objects individually. And does not require createdb and its associated privileges.

Just adding another potential solution to pile. Take it or leave it. ;-)

Replying to meadow:

I can suggest another solution, see attached rake tasks which create dumps that drop objects first, so database can be cleaned wtithout rights to createdb

This rake task requires the developer to have postgresql (or at least pg_dump and psql) to be installed locally. Is that a valid assumption?

I know on our dev team, we have two rails developers that only access Postgres remotely, there is no local pg_dump or psql command on their machines.

The attached diff mah_databases.rake.diff is what we use to make things work for our team. (We have a snapshot of rails checked into /vendor/rails in our code tree with this patch applied.)

If no schema_search_path is set, then dropping the public schema would make sense.

I just tried the patch on rel_2-0-2, it applies cleanly and works fine. +1