Migrate external table to enum column

[Andres-Parra2610]

Hello everyone, i have a two question with migrations. For example, i have these tables

class Products extends Table with Base {
  TextColumn get name => text().withLength(min: 3, max: 150)();

  TextColumn get measureUnitId =>
      text().references(MeasureUnits, #id, onUpdate: KeyAction.cascade, onDelete: KeyAction.cascade)();
}

class MeasureUnits extends Table with Compact {
  TextColumn get name => textEnum<EnumMeasurementUnits>().unique()();
}

I would like to simplify and remove MeasureUnits table and put directly the enum colum into Products, like this

class Products extends Table with Base {
  TextColumn get name => text().withLength(min: 3, max: 150)();

  TextColumn get name => textEnum<EnumMeasurementUnits>().unique()();
}

Also, i would like delete a custom constrain, for example

class Categories extends Table with Base {
  TextColumn get associationId =>
      text().references(Associations, #id, onUpdate: KeyAction.cascade, onDelete: KeyAction.cascade)();
  TextColumn get name => text().withLength(min: 3, max: 50)();

  @override
  List<String> get customConstraints => ['UNIQUE (association_id, name)'];
}

the result should be

class Categories extends Table with Base {
  TextColumn get name => text().withLength(min: 3, max: 50).unique()();
}

How could i execute this migration? thank you.

[simolus3]

I assume MeasureUnits currently has an id column as well, right? Assuming that Products also has an id column, a migration could look like this:

final innerProducts = products.createAlias('i');
// For each product we're about to select from in the TableMigration step, resolve the measurement name based on the current measurement id:
final unitNameFromId = subqueryExpression(selectOnly(measureUnits)
  ..addColumns([measureUnits.name])
  ..join([
    innerJoin(innerProducts,
        innerProducts.measureUnitId.equalsExp(measureUnits.id))
  ])
  ..where(innerProducts.id.equalsExp(products.id)));

await m.alterTable(TableMigration(
  products,
  newColumns: {products.unitName},
  columnTransformer: {
    products.unitName: unitNameFromId
  }
));

After the migration has completed, you can delete the products table. With these complex migrations, I strongly recommend exporting the schemas so that you can write reliable unit tests to make sure no data is lost.

For the second migration that alters table constraints without altering table semantics, you can run a simple alterTable call. alterTable will recreate the table under a temporary name, copy data over, and then drop the old table to make the temporary table final. Running this without any args implicitly refreshes all table constraints, but you can use things like a columnTransformer for complex migrations where some of the new columns need to be computed from old rows.