How to migrate column type to update value based on old column value?

[iamiota]

Thanks for providing such a great library.

If there is a column of type text and name "age", now I want to update the type of this column to int and update the column value: one -> 1, seven -> 7, sixteen -> 16 etc.

How should I write the stepByStep logic in onUpgrade?

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    onCreate: (m) async {
      await m.createAll();
    },
    beforeOpen: (details) async {
      // Make sure that foreign keys are enabled
      await customStatement('PRAGMA foreign_keys = ON');
    },
    onUpgrade: stepByStep(
      from1To2: (m, schema) async {
        // Write your migrations here
      },
      from2To3: (Migrator m, Schema3 schema) async {},
      from3To4: (Migrator m, Schema4 schema) async {
        await m.alterTable(TableMigration(
          schema.tableUser,
          columnTransformer: {
            schema.tableUser.age:
              Variable(schema.tableUser.age == 'sixteen' ? 16 : 0),
          },
        ));
      }
    ),
  );
}

Thanks!

[simolus3]

I think there are two reasonable options here:

1. If you know that age will only take a few possible options (e.g. you know that it's between 0 and 120), you could use a very long caseMatch expression to map all of the values:

var oldTextColumn = schema.tableUser.age.dartCast<String>();

columnTransformer: {
  schema.tableUser.age: oldTextColumn.caseMatch<int>({
    Constant('zero'): Constant(0),
    Constant('one'): Constant(1),
     // ...
  })
}

2.If you have a generic parser that goes from the string representation to the number, you could register that as a user-defined function to invoke in the migration. For instance, if you open your database like this:

NativeDatabase.createInBackground(
  File(yourDatabasePath),
  setup: (database) {
    database.createFunction(
      functionName: 'text_to_number',
      function: (args) {
        final text = args[0] as String;
        // todo: Parse number string
        return 0;
      },
    );
  },
)

Then you could write

columnTransformer: {
  schema.tableUser.age: CustomExpression('text_to_number(age)')
}

[iamiota]

Thank you so much, this is awesome!