Implement the change column type operation. A change type migration looks like this:

{
  "name": "18_change_column_type",
  "operations": [
    {
      "change_type": {
        "table": "reviews",
        "column": "rating",
        "type": "integer",
        "up": "CAST(rating AS integer)",
        "down": "CAST(rating AS text)"
      }
    }
  ]
}

This migration changes the type of the rating column from TEXT to INTEGER.

The implementation is very similar to the set NOT NULL operation (#63):

• On Start:
  • Create a new column having the new type
  • Backfill the new column with values from the existing column, converting the types using the up SQL.
  • Create a trigger to populate the new column when values are written to the old column, converting types with up.
  • Create a trigger to populate the old column when values are written to the new column, converting types with down.
• On Complete
  • Remove triggers
  • Drop the old column
  • Rename the new column to the old column name.
• On Rollback
  • Remove the new column and both triggers.

The migration can fail in at least 2 ways:

• The initial backfill of existing rows on Start fails due to the type conversion not being possible on one or more rows. In the above example, any existing rows with rating values not representable as an INTEGER will cause a failure on Start.
  • In this case, the failure is reported and the migration rolled back (Rollback operation on Start failure #73)
• During the rollout period, unconvertible values are written to the old version schema. The up trigger will fail to convert the values and the INSERT/UPDATE will fail.
  • Some form of data quarantine needs to be implemented here, copying the invalid rows elsewhere and blocking completion of the migration until those rows are handled in some way).

The PR also adds example migrations to the /examples directory.