UM-USElab/gradecraft-development — pull request #639 by allspiritseve (2013-11-14 18:39:36+00:00)
Title: [Feedback needed] Proposed solution for reloading views inside migrations

After a lot of iterations, I think I have arrived at the simplest solution that works for reloading views inside migrations. (See #574 for discussion).

Goals:

1. Reload views inside a transaction. After each migration, the database should be left in a complete state.
2. Manually included module. Everyone should have the ability to override the default functionality or remove it altogether from their migrations, in case some special code is required.
3. Migrations should always drop / reload the same view as when they are written, whether or not newer versions of the views exist.

Overview:

Instead of a single db/etc.sql, we now have versions of that file in db/etc, e.g. db/etc/1.sql. This allows migrations to reference a particular version of views that never changes (Goal 3). I chose an incremental number scheme, because unlike migrations it is not desirable to have changes "interleaved" by the date they were written. db/etc/3.sql comes after db/etc/2.sql. If two developers create the same version number, someone will either need to merge them or rename one to come after the other.

Like before, we have rake tasks to drop / load / reload views. By default, these tasks now use the latest version in db/etc, but the version can be specified with an environmental variable:

```shell
rake db:etc:reload VERSION=2
```

In our git `post-receive` hook, `rake db:etc:load` will be run after `rake db:migrate`, which will replace all views (since they are specified with `CREATE OR REPLACE VIEW` in the db/etc file) allowing columns to be appended on the end without first dropping the view.

Additionally, I wrote a module `EtcMigration` which can be included into any migration and adds the following methods:

```ruby
load_etc(version: 1)
drop_etc(version: 1)
reload_etc(version: 1)
migrate_etc(from: 1, to: 2)
```

These can be used in the following use cases:

## Altering a table that a view depends on

```ruby
def up
# Drops db/etc/1.sql, adds the column, loads db/etc/1.sql
reload_etc(version: 1) do
add_column :events, :genre, :string
end
end

def down
# Drops db/etc/1.sql, removes the column, loads db/etc/1.sql
reload_etc(version: 1) do
remove_column :events, :genre, :string
end
end
```

## Changing a view to incorporate a new column
```ruby
def up
# Drops db/etc/1.sql, adds the column, loads db/etc/2.sql
migrate_etc(from: 1, to: 2) do
add_column :events, :genre, :string
end
end

def down
# Drops db/etc/2.sql, adds the column, loads db/etc/1.sql
migrate_etc(from: 2, to: 1) do
remove_column :events, :genre, :string
end
end
```
Note the above requires creating a new version of the views and editing the new version to reference the new column
```shell
cp db/etc/1.sql db/etc/2.sql
vim db/etc/2.sql
```

## Changing a view in a way that fails when `CREATE OR REPLACE VIEW` is run
```ruby
def up
# Drops db/etc/2.sql, loads db/etc/2.sql
migrate_etc(from: 2, to: 3)
end

def down
# Drops db/etc/3.sql, adds the column, loads db/etc/2.sql
migrate_etc(from: 3, to: 2)
end
```
```shell
cp db/etc/2.sql db/etc/3.sql
vim db/etc/3.sql
```

## Changing a view in a way that does not fail when running `CREATE OR REPLACE VIEW`
```shell
cp db/etc/2.sql db/etc/3.sql
vim db/etc/3.sql
```
Note that this does not require a migration, as the git `post-receive` hook runs `rake db:views:load` after all migrations are run.

# Feedback needed

Please read through the above. Feel free to check out the branch and try playing around with the code. If this is the right approach, then next I'd like to look into creating a Rails generator that will copy the latest db/etc file to a new version and generate a migration with EtcMigration included and the appropriate versions specified, to cut down the number of steps involved in creating a migration that reloads views.

/cc @chcholman @bigeasy @JangoSteve