Change active record management from ruby to sql

[dlebauer]

From the "Schema dumping and you" in the ruby manual:

There are two ways to dump the schema. This is set in config/application.rb by the config.active_record.schema_format setting, which may be either :sql or :ruby.

Since we are implementing database-level constraints, it appears that we need to change this setting from :ruby to :sql. @gsrohde, does this sound correct? If so, can you please assign this feature to @phenolphtalein?

I have updated the Constraints documentation to justify this move; in brief, Given that the Ruby web application is only one of the ways in which we use the database (e.g. we don't want to have to use the API with all of our R code ... or do we?), it seems reasonable to implement database-level constraints and features such as views.

[dlebauer]

Or ... to stay using schema.rb, there are also a few ruby gems that can help with constraints in active record. Here are a few I found searching through the ruby toolbox

• foreigner is widely used gem for foreign key constraints
• schema_plus also supports views
• default_value_for
• a few gems support enums in ActiveRecord

[gsrohde]

I was aware of foreigner but not the others. We would also need something to keep track of the triggers and trigger functions. Since the trigger functions are in PL/pgSQL and so aren't database-independent, it seemed like we might as well just dump the schema in SQL mode, though perhaps using the above-mentioned gems would make writing the migrations marginally easier.

[dlebauer]

based on conversation with @robkooper, @gsrohde

• dump development.sql on beta site, this is used for testing
• production.sql doesn't necessarily match most recent version
• for a particular tagged version, development.sql and production.sql should be the same

rake db test clone structure

looks at current dev database, makes a clone, loads to test database; if you have development.sql file it can start from there, otherwise goes back to production.sql

[gsrohde]

Here is our current practice:

1. The config.active_record.schema_format setting has been set to :sql
2. schema.rb has been removed from the repository.
3. Every time we change the master database on ebi-forecast, we generate and check in a new version of the production_structure.sql file as follows:

RAILS_ENV=production rake db:structure:dump
git add db/production_structure.sql
git commit
git push

Note that the rake db:structure:dump command doesn't depend on the config.active_record.schema_format setting.
4. The config.active_record.schema_format setting does affect what happens when rake db:test:prepare is run: It will no longer try to use schema.rb and will instead generate a fresh version of development_structure.sql by examining the development database; it then uses this structure file to (re-)generate the test database. Note that we don't keep development_structure.sql in the Git repository.

Unless you have remaining concerns that aren't addressed by this protocol, please close this issue.

[dlebauer]

please close after
1, adding development_structure.sql to .gitignore and
2. information about creating this file in the testing wiki
3. any other information from here that should go to the wiki (e.g. running migrations (or even just a link to this issue)