Steps to change primary key and preserve data in migration

[realth000]

Actually I'm not sure if it's the right way to do migration.

Assume here is a table:

@DataClassName('UserEntity')
class Users extends Table {
  TextColumn get username => text()();
  IntColumn get someId => int()();

  @override
  Set<Column<Object>> get primaryKey => {username};
}

When in v2, the primary key of the table changes from username to someId:

@DataClassName('UserEntity')
class Users extends Table {
  TextColumn get username => text()();
  IntColumn get someId => int()();

  @override
  Set<Column<Object>> get primaryKey => {someId};
}

So to finish the migration to v2, after searching in doc and sqlite related question, I found that I need to

1. save all data in memory
2. drop the v1 table
3. create a v2 one
4. insert data into the new table.

I achived these steps somehow:

from1To2: (m, schema) async {
  final userData = await m.database.select(schema.user).get();
  final userEntities =
      userData.map((e) => UserEntity.fromJson(e.data)).toList();
  await m.drop(schema.user);
  await m.create(schema.user);
  await transaction(() async {
    for (final user in userEntities) {
      await m.database.into(user).insertOnConflictUpdate(user);
    }
  });
},

But the line deserializing UserEntity: UserEntity.fromJson(e.data) throws an exception:

[ERROR:flutter/runtime/dart_vm_initializer.cc(41)] Unhandled Exception: type 'Null' is not a subtype of type 'int' in type cast
package:drift/src/runtime/data_class.dart 230:19                                                          _DefaultValueSerializer.fromJson

After printing data, figured out someId in json map is snake_case "some_id", but the field expected in deserializer is camelCase "someId" so it failed to found the element in json map.

I have another table already went through these steps, it only have "one section" name columns and didn't hit the case issue.

Seems there is no example or API in TableMigration to do this stuff, a similar one is changing column type.

Is this way correct to "change the primary key of table while reserving most data"?

Thanks in advance.

[simolus3]

Is this way correct to "change the primary key of table while reserving most data"?

Using UserEntity.fromJson is not a reliable tool here: That class will always reflect the current columns of your schema, so you'll get these null cast errors after adding a new non-nullable column to the table in any subsequent migration.
The good news is that drift's TableMigration API can take care of all of these steps, while also avoiding some common pitfalls by doing the transformation between the two tables in SQL.

Under the assumption that no two users in the old table have the same value in someId, you can just do this:

await m.alterTable(TableMigration(users));

That's it! If you can't guarantee that the ids are unique before the migration, you'd unfortunately have to delete duplicate rows or at least assign unique ids to them.

[realth000]

Thanks for the quick and clear response!

I always struggle with SQL so I do a lazy stuff:

await customUpdate(
  'DELETE FROM personal_message '
  'WHERE (uid, peer_uid, timestamp) NOT IN ( '
  '    SELECT uid, peer_uid, max(timestamp) '
  '    FROM personal_message '
  '    GROUP BY uid, peer_uid '
  ');',
  updates: {schema.personalMessage},
  updateKind: UpdateKind.delete,
);
await m.alterTable(TableMigration(schema.personalMessage));

For whom also looking for a final solution, above is the real code I run in migration, a table personal_message recording chat messages between current user uid and another user peer_uid, CustomUpdate reserves only the latest single message in each pair of chat (specified by uid and peer_uid), then I can change the primary key from {uid, timestamp} to {uid, peer_uid}.

I think it's safer to use SubQuery and Aggregate functions but it's easier for me, with raw and not-handwrite sql.