Schema does not match when using a custom unique constraint

[Sobotkama]

I need to have a custom unique constraint - I'm trying to treat all nulls as identical in a column. Apparently, this can't be done in SQLite while creating a table, it has to be done after that, so I've written it as a custom statement like so:

return MigrationStrategy(
  beforeOpen: (details) async {
    if (kDebugMode) {
      await validateDatabaseSchema();
    }
    await customStatement('PRAGMA foreign_keys = ON');
  },
  onCreate: (Migrator m) async {
    await m.createAll();
    await customStatement(
      'CREATE UNIQUE INDEX IF NOT EXISTS UQ_PhysicalDevice ON "physical_devices" ("identifier", COALESCE("manufacturer", \'\'), "model_name");',
    );
  },
  onUpgrade: upgradeStrategy,
);

However, when running validateDatabaseSchema, I (somewhat predicatbly) get the following error:

Schema does not match
additional:
 Contains the following unexpected entries: UQ_PhysicalDevice

For now, I've commented out the validation. Is there a way to tell the dart analyzer to expect the constraint? Or a more drift-friendly way to create it?

[simolus3]

You could add an index to the list of schema entities drift expects in the database by overriding this getter in the database class:

  @override
  List<DatabaseSchemaEntity> get allSchemaEntities => [
        ...super.allSchemaEntities,
        Index('UQ_PhysicalDevice',
            'CREATE UNIQUE INDEX IF NOT EXISTS UQ_PhysicalDevice ON "physical_devices" ("identifier", COALESCE("manufacturer", \'\'), "model_name");')
      ];

This will also make the migrator create the index with createAll(), so you need no additional custom statements.

[Sobotkama]

Thanks, this did solve my initial problem quite elegantly.

I did come upon another issue however - I can't actually use that unique index in an ON CONFLICT target - I've tried the following insert statements, with the goal of overwriting existing rows:

await db.into(db.physicalDevices).insert(testDevice);
await db.into(db.physicalDevices).insertOnConflictUpdate(testDevice);
await db.into(db.physicalDevices).insert(testDevice, onConflict: DoUpdate((old) => testDevice));
await db.into(db.physicalDevices).insert(testDevice, onConflict: DoUpdate((old) => testDevice, target: [db.physicalDevices.identifier, db.physicalDevices.manufacturer, db.physicalDevices.modelName]));

The first one predictably fails. I'd expect the second and third option to generate an ON CONFLICT without any target, which should work in SQLite (More on this below), but it targets the table's primary key.
The last option doesn't work, probably because the unique index doesn't contain the manufacturer column directly? (IDK, I'm not that familiar with SQLite)

So my best option AFAIK is to use the following SQL statement (note that there's no conflict target):

INSERT INTO "physical_devices" ("identifier", "model_name") VALUES ('Hello there', 'General Kenobi') ON CONFLICT DO UPDATE SET "identifier" = 'Hello there', "model_name" = 'General Kenobi';

I've tried to run this manually on a copy of the app database and it behaves correctly. Is there any way to get drift to generate this? I've tried:

await db.into(db.physicalDevices).insert(testDevice, onConflict: DoUpdate((old) => secondDevice, target:[]));

But I get an ArgumentError from drift: 'Table has no primary key, so a conflict target is needed.' (Which isn't true)

[simolus3]

Right, I don't remember why I've defaulted to the table's primary key. I think there have been some cases where we had to provide a target and so that seemed like a reasonable default. Changing it now is a breaking change, but at least we can allow an empty list to skip generating a conflict target. I've fixed that in 044a0f3.

[Sobotkama]

Awesome, thanks.