Provide a convenient method of adding SQL comments

[gsrohde]

Currently, updates to the database schema is effected primarily through the use of Rails migrations. This helps to ensure that different BETYdb database instances conform to the same database schema.

It is, however, desirable to have a less cumbersome method of updating database comments.

Tasks

• [x] Implement comment-editing feature.
• [x] Release new feature.
• [ ] Update documentation, including:
  • [ ] General procedure for updating comments.
  • [ ] Add to instructions for issuing a new release for the case where comments change and the comments should be propagated to other deployments.

[dlebauer]

Currently a lot of the documentation is in the schema metadata for the ebi_production (BETYdb.org) database. Ideally this could be propagated / syncd across instances, or stored in a single canonical location that doesn't require a migration for each edit.

[gsrohde]

Pull request #543 implements this feature.

Here's how it works.

Edit the file db/data/database_comments.sql to add, change, or delete comments. (Delete a comment by changing it to the empty string.)

The format of this file is roughly

table1_name:
  table_comment: comment for table 1
  column1_name: comment for column 1 of table 1
  column2_name: comment for column 2 of table 1
  ...
table2_name:
  table_comment: comment for table 2
  column1_name: comment for column 1 of table 2
  column2_name: comment for column 2 of table 2
  ...
...

(The order of the tables and the order of the key-value pairs under each table is immaterial.)

Then run the Rake task

rake bety:db:update_comments

If you want to make this the canonical set of comments for the database, check the updated version of db/data/database_comments.yaml into the repository. Then, in the next database release, users will be advised to run the Rake task to update their database comments (just as they are advised to run rake db:migrate when the database schema needs to be updated).

The Rake task

rake bety:db:dump_comments

can be used to produce a YAML file from existing comments. (Run

rake -D bety:db:dump_comments

for more information on this task and various run options.)