Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #7665 (reopened enhancement)

Opened 3 years ago

Last modified 2 years ago

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

Reported by: saserr Assigned to: core
Priority: normal Milestone: 2.x
Component: ActiveRecord Version: edge
Severity: normal Keywords: postgresql test activerecord dropdb createdb
Cc:

Description

Using dropdb and createdb in db:test:purge makes a huge dependency that user must have a privilege to create a databases. My solution would be:
1. DROP SCHEMA public CASCADE;
2. CREATE SCHEMA public;
Only dependency is that you are a database owner to be able to recreate schema. I did made a diff of rails/lib/tasks/databases.rake to show how that would work in rails. Sorry for not doing tests because I am still new to rails, but i did check it out in script/console and it did work.

Attachments

databases.rake.diff (0.8 kB) - added by saserr on 02/27/07 19:09:02.
diff of databases.rake
databases.diff (0.8 kB) - added by saserr on 02/27/07 19:19:00.
diff of databases.rake
cleanpg.rake (1.5 kB) - added by meadow on 02/28/07 11:15:38.
rake tasks to clean postgresql db schema without drop/create db, need review and cleanup
mah_databases.rake.diff (2.1 kB) - added by flowdelic on 03/01/07 00:24:26.

Change History

02/27/07 19:09:02 changed by saserr

  • attachment databases.rake.diff added.

diff of databases.rake

02/27/07 19:19:00 changed by saserr

  • attachment databases.diff added.

diff of databases.rake

02/27/07 19:20:34 changed by saserr

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

02/28/07 00:57:12 changed by meadow

1. what about multiple schemas in database?

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

(follow-up: ↓ 4 ) 02/28/07 09:57:43 changed by saserr

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

02/28/07 11:15:38 changed by meadow

  • attachment cleanpg.rake added.

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

(in reply to: ↑ 3 ; follow-ups: ↓ 6 ↓ 7 ) 02/28/07 11:16:22 changed by 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 :)

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

02/28/07 12:22:27 changed by saserr

  • status changed from new to closed.
  • resolution set to fixed.

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.

(in reply to: ↑ 4 ) 03/01/07 00:22:30 changed by flowdelic

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. ;-)

03/01/07 00:24:26 changed by flowdelic

  • attachment mah_databases.rake.diff added.

(in reply to: ↑ 4 ) 03/01/07 00:37:01 changed by flowdelic

  • status changed from closed to reopened.
  • resolution deleted.

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.

05/27/07 18:34:27 changed by josh

  • summary changed from postgresql dropdb and createdb in db:test:purge to [PATCH] postgresql dropdb and createdb in db:test:purge.

12/20/07 17:07:23 changed by jean.helou

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