Support migration of the database schema and data

[kwk]

We could add a version table which helps in figuring out what migration code needs to be executed.

[sbose78]

Is this the flow you would be proposing during an upgrade ?

1. Update version details in the database via a SQL.
2. Deploy latest version of the ALM app.
3. The app code reads the version details from the database and runs the relevant migrations.

[kwk]

@sbose78 no, so far we have the Perfrom() function from migration.go.

Here's a possible layout for a migration/schema-version table:

ID	version	updated_at
1	1	2016-09-06 10:00
2	2	2016-12-07 14:00
3	3	2017-01-12 09:00

The ID column might not be needed in the end.

The migration.go file might be a place to start doing the migration in a way similar to this:

1. Check if version table exists
   1. Version table doesn't exist.
      1. We must be in the original schema, so create version table and enter 1, then proceed as if the version table existed all the time.
   2. Version table exists
      1. Check the version column for the highest (in SQL: max()) number and call it OLD_VERSION.
      2. Jump to case in which to update from version OLD_VERSION to OLD_VERSION + 1.
         1. Insert OLD_VERSION+1 with the updated_at into the SQL table.
      3. Fall through other cases in the swtich to perform all required version updates until we've reached the current desired schema.

Does that sound logical?

I've implemented the above logic in SQL once and it worked okay.

@tsmaeder any ideas?

[baijum]

Can we check this project?
https://github.com/rubenv/sql-migrate

[kwk]

Taken from @aslakknutsen 's gist:

var order [][]func()

order = append(order, []func(db) {ExecSQLFile("add_customer.sql"), MigrateCustomerFieldName})
order = append(order, []func(db) {ExecSQLFile("add_some_other")})

// db.tx.begin // TODO: Make sure currentVersion is read inside TX
for verison := currentVersion() && version < size(order) {
    for migrationFun := order[version] {
        migrationFunc(db)
    }
    updateVersion()
}
// db.tx.comit

[kwk]

Idea from @aslakknutsen: turn on postgres driver output during migration for better mid-way error detection .

[kwk]

Group idea: Think about how to handle great version deltas: If delta is bigger than 2, require a downtime.

[kwk]

Can we check this project?
https://github.com/rubenv/sql-migrate

Hi @baijum . Just to recap yesterday's meeting:

We decided that we ( @aslakknutsen , @tsmaeder , @sbose78 , and @pranavgore09 ) won't use this library for a few reasons. It is mostly related to the features not being pretty relevant to us.

1. We only have to support one database (PostgreSQL) and not the many others supported by the project.
2. We want to have pre- and post-migration scripts (probably in Go) that need to run smart migration steps, that would otherwise be too cumbersome to write in pure SQL files.
3. We don't want to support down-migrations.

[kwk]

We should embed the migration in the code by using bindata: https://github.com/rubenv/sql-migrate#embedding-migrations-with-bindata

[aslakknutsen]

@kwk jut fyi, we're already using bindata; https://github.com/almighty/almighty-core/blob/master/Makefile#L160

[kwk]

@kwk jut fyi, we're already using bindata; https://github.com/almighty/almighty-core/blob/master/Makefile#L160

Thanks @aslakknutsen for pointing out.

[tsmaeder]

Two points I'd like to make:

1. We need to replace our use of "db.Automigrate()" with sql code
2. We have multiple db setup locations now (migration pkg, but also remotrworkitem/schedulertest.go). We should offer a SPI for "plugins" and always use the same migration code (also in tests)

[baijum]

@kwk I found a project with similar scope and removed unwanted features. Here it is: https://github.com/baijum/pgmigration