Upsert conflict with id + uuid

[frankvollebregt]

I am facing the following problem: I have some different tables which have both a regular ID as well as a UUID. For example:

CREATE TABLE cars(
  car_id INT NULL UNIQUE,
  car_uuid TEXT NOT NULL PRIMARY KEY,
  wheels INT NULL
);

CREATE TABLE boats(
  boat_id INT NULL UNIQUE,
  boat_uuid TEXT NOT NULL PRIMARY KEY,
  is_sailboat INT NULL
);

On the server backend, the normal IDs are used for identification (much better performance with large datasets), whereas in my Flutter app, the UUIDs are used. This ensures the client app can also add cars and boats without worrying about consistency on the server (which assigns them a 'normal' ID once pushed). When I receive a Boat or Car object from the server (as JSON), I set the UUID and insert it using

Car.fromJson(jsonEntry).toCompanion(true);
Boat.fromJson(jsonEntry).toCompanion(true);

Now, I want to also handle upserts. In this case, when there is a conflict on the car_id or boat_id (the uniqueness constraint fails), I want to instead update, keeping the local UUID and overwriting the rest.

In reality, I have a number of tables that are identified this way, so I want to dynamically handle these operations (and write as little code per type as possible). As a result, I have a function which handles server data that looks a bit like:

handleUpdate({required VehicleDatabase db, required String tableName, required Map<String, dynamic> entry}) {
  Insertable<dynamic>? companion;
  switch(tableName) {
    case 'cars':
      companion = Car.fromJson(entry);
      break;
    case 'boats':
      companion = Boat.fromJson(entry);
      break;
   default:
    throw new Exception(...);
  }

  TableInfo table = db.allTables.firstWhere((element) => element.actualTableName == tableName);
  
  // this works fine for insertions
  db.into(table).insertOnConflictUpdate(companion);
}

I was trying to use the onConflict parameter of insert instead, but did not get it to work the way I want.

db.into(table).insert(companion, onConflict: DoUpdate((Table? old) {
  // here, I want to return an inserable with all values from the new entry, but the UUID of the old entry
  // no UUID would also work, but since it's the PK it's required
}));

Does anyone have some insights on how I could achieve this? Thank you in advance!

More concretely: On an insertion conflict, I would like to access the entry in the database that caused the conflict, and construct a new companion which uses its UUID and the other values from the companion which was to be inserted, all while dynamically determining which table I'm working on.

[simolus3]

I haven't tried it, but something like this could work:

db.into(table).insert(companion, onConflict: DoUpdate((Table? old) {
  final oldId = (old as TableInfo).columnsByName['${tableName}_uuid'];
  return RawValuesInsertable({
   ...companion.toColumns(false),
    '${tableName}_uuid': oldId,
  });
}));

Essentially, the idea is to use the uuid column from the old table, but all other values from the companion.

[frankvollebregt]

Makes a lot of sense! However, when I try to implement this, I cannot assign oldId to the uuid key

The element type 'GeneratedColumn<dynamic>?' can't be assigned to the map value type 'Expression<dynamic>'.

[frankvollebregt]

It compiles when I do the following

return RawValuesInsertable({
  ...companion.toColumns(false),
  '${tableName}_uuid': old.primaryKey!.first,
});

But it warns me to only use primaryKey for overriding, so I assume this is not the way to go.

When I run it, however, I receive the following message

[ERROR:flutter/lib/ui/ui_dart_state.cc(209)] Unhandled Exception: type 'RawValuesInsertable<dynamic>' is not a subtype of type 'Insertable<Car>' of 'instance'

The above does not work after all: It still assigns a GeneratedColumn<String?> to the uuid. The error is then thrown, but I'm not sure what might change when a valid object is set as the uuid.

Wrapping it inside a Variable resolves the error in the IDE (as it is now an expression), but causes the above error during runtime.

[simolus3]

I think the best way to fix the error about the <dynamic> type is to make handleUpdate generic too:

Future<void> handleUpdate<T extends Table, D>({
  required VehicleDatabase db,
  required TableInfo<T, D> table,
  required Map<String, dynamic> entry,
}) {
  Insertable<D> companion;
  final tableName = table.entityName;

  switch (tableName) {
    case 'cars':
      companion = Car.fromJson(entry);
      break;
    case 'boats':
      companion = Boat.fromJson(entry);
      break;
    default:
      throw new Exception('...');
  }

  return db.into(table).insert(companion, onConflict: DoUpdate((T? old) {
    final oldId = (old as TableInfo<T, D>).columnsByName['${tableName}_uuid']!;
    return RawValuesInsertable<D>({
      ...companion.toColumns(false),
      '${tableName}_uuid': oldId,
    });
  }));
}

(You could wrap this generic method in a non-generic method that only takes the table name and calls the generic method with the appropriate types in a switch statement).

[frankvollebregt]

Thanks for the help, I'll try it out soon and approve the answer if it works for me

[frankvollebregt]

Unfortunately, it still doesn't work...

When I log companion's runtimeType, it correctly shows as Car (I also tried CarCompanion, same result).

During the insertion, though, it still errors, saying

type 'RawValuesInsertable<dynamic>' is not a subtype of type 'Insertable<Car>' of 'instance'
Cars.validateIntegrity

[frankvollebregt]

I've now tried to just remove the uuid key from the map that is inserted in the rawValuesInsertable, but that doesn't seem to work either. I see in the error above that it says

ON CONFLICT(car_uuid)

Does this mean it only checks for conflicts on the primary key, and still errors on conflicts caused by (in this case) the UNIQUE constraint on the car_id?

[frankvollebregt]

I've just written a version that constructs the query based on the column names/table name for a more manual approach. This works as expected for me.

The ON CONFLICT point I made above appears to be true, the onConflict never triggers because the conflict is not on the primary key.

Is there a specific reason Drift only looks for conflicts on the PK of a table?

[frankvollebregt]

I expanded and refined the function I mentioned in my previous comment. I think I'll stick with this solution for now, as it requires essentially no table-specific dart code. Thank you again for all the help and quick responses. The only thing remaining now is the question in my previous reply about the PK-only onConflict.

/// Use a customStatement to upsert the entry in the table
Future<void> upsertEntry({
  required db,
  required String tableName,
  required Map<String, dynamic> entry,
}) async {
  // set uuid if it isn't set
  entry['${tableName}_uuid'] = entry['${tableName}_uuid'] ?? Uuid().v4();

  // remove columns that are not present in the local database
  // note that for the tables that do not have a uuid, it will be removed again in this step
  Iterable<String> allColumnNames = db.allTables.firstWhere((element) => element.entityName == tableName).columnsByName.keys;
  entry.removeWhere((key, value) => !allColumnNames.contains(key));
  
  // remove empty columns (in an attempt to 'patch')
  entry.removeWhere((key, value) => value == null);

  // Values for inside the query
  String columnNames = entry.keys.reduce((value, element) => value + ', '+element);
  String questionMarks = entry.keys.reduce((value, element) => value + ', ?').replaceFirst(entry.keys.first, '?');
  String reducedColumnNamesToQuestionMarks = [...entry.keys].where((element) => element != '${tableName}_uuid').reduce((value, element) => value + ' = ?, '+element) + ' = ?';

  // Query variables
  List<dynamic> allValues = [...entry.values];
  List<dynamic> reducedValues = [...entry.entries.where((el) => el.key != '${tableName}_uuid').map((e) => e.value)];

  // The query
  final String upsert = 'INSERT INTO $tableName ($columnNames) VALUES ($questionMarks) ON CONFLICT DO UPDATE SET $reducedColumnNamesToQuestionMarks';

  await db.customStatement(upsert, [...allValues, ...reducedValues]);
}

[simolus3]

Yes it does, thank you! The downside is that I now essentially have two switch statements where I select based on which table I'm handling. It looks a bit like

That's true, but you could change the "inner" handleUpdate method to take an Insertable<D> instead of a map and do the fromJson in the outer method (since you're switching over tables you know which data class to use there). That way you would eliminate one switch statement.

Does this mean it only checks for conflicts on the primary key, and still errors on conflicts caused by (in this case) the UNIQUE constraint on the car_id?

Yes, by default that's what we do since that's the only information we have available at runtime at the moment. We have to provide an explicit list of columns where sqlite3 detects conflicts, and we default to the primary key. I think that's a sane default, but one that should be made more visible in the docs (done with 528b576). You can set a custom target with DoUpdate(target: listOfColumns).

It looks like you don't provide those columns in your ON CONFLICT clause, does that actually work? Quoting the sqlite3 docs, I always assumed that it shouldn't:

The syntax that occurs in between the "ON CONFLICT" and "DO" keywords is called the "conflict target". The conflict target specifies a specific uniqueness constraint that will trigger the upsert. The conflict target is required for DO UPDATE upserts, but is optional for DO NOTHING.

If it works and I'm just misunderstanding thinks, doing the same in drift would obviously be much better.

[frankvollebregt]

I agree defaulting to the primary key to detect conflicts is indeed a sane rationale.

Reading that portion, I'd indeed assume that shouldn't work, but it does for me. Looking at the next sentence in the docs however, it does say

When the conflict target is omitted, the upsert behavior is triggered by a violation of any uniqueness constraint on the table of the INSERT

Which I assume is what's happening for me. I suppose this is just poorly worded in the SQLite documentation (maybe this was changed with some update?).