Looking for a reflective Insert/Update example

[CyMathew]

Thank you for adding the ability and the excellent doument to do reflective table operations (runtime schema inspection)!

https://drift.simonbinder.eu/docs/dart-api/schema_inspection/

Thanks to your great example code for select, I was able to easily create my own selectable statement for reflective use. But I'm struggling to do the same for Insert/Update.

Could I get an example of how to do an upsert with runtime schema inspection? I feel like I'm not getting an idea of how to pull off an insert/upsert without access to the companion classes.

[simolus3]

I've posted some notes on that here (they will be published to the main website with the next drift release). Is that what you were looking for?

Basically, the trick to avoid companion classes is using RawValuesCompanion.

[CyMathew]

Yes! The RawValuesInsertable is probably what I was missing. Does RawValuesInsertable need all the column values or can we ignore unchanged columns (similar to a Companion) during an update?

[simolus3]

Unchanged columns should not be mentioned in a RawValuesInsertable, it behaves similar to companions in that regard.

[CyMathew]

Also, there may be an error in the new documentation:
I tried following the example given, as stated in the documentation:

This example builds on the previous one to update the title column of a generic table based on a filter of the id column:

added updateTitle<T extends TableInfo<Table, Row>, Row> function to the FindById<Table extends HasResultSet, Row> on ResultSetImplementation<Table, Row> extension above but I get an error because update() function is not available in ResultSetImplementation. As far as I know insert(), and update() are only available via extension on TableInfo (TableStatements extension) right?

Maybe you've shuffled a few functions around and I'm working with the older release API so I may be incorrect.
I was able to get the new example working by creating a new extension on TableInfo instead.

[simolus3]

That method was meant to be part of a database class since it uses the update method from that. I'll make the snippet clearer to point that out, thanks for the feedback!

[CyMathew]

Thank you!

Having used many packages/libraries/frameworks across many languages, I have to say Drift is one of THE best for its excellent API, documentation and code comments. It is easy to get started with and just as easy to gain mastery in using it skillfully. Bravo!

Highly appreciate your work.

[CyMathew]

So update and insert work fine reflectively. An insertAll with a DoUpdate on conflict is probably not possible (since the conflicting row is not known) as we don't have access to the old/excluded values (DoUpdate's update function only provides a Table object old instead of a Insertable<D> object).

Is this just a limitation of Drift being a layer above SQLite (so it doesn't get access to old/excluded values/row reference to conflicting row and its values mid-flight)? If I wanted to replace all old values with excluded's values with a where clause, I'm not sure how I would do that without having access to Insertable<D>/Row object causing the conflict.

[simolus3]

It's kind of hacky, but I think something like this should work since all instances of Table at runtime will be TableInfos:

await batch((b) {
  b.insertAll<Table, Row>(
    table,
    ...,
    onConflict: DoUpdate(
      (old) {
        final oldTable = (old as TableInfo);

        // or whichever columns you need
        return RawValuesInsertable({
          'foo': oldTable.columnsByName['foo']!,
        });
      },
    ),
  );
});

so it doesn't get access to old/excluded values/row reference to conflicting row and its values mid-flight

I'm not sure I understand this question -- regular upserts also don't really see anything mid-flight. We create the illusion with the typed table callback, but that's just to generate SQL. Only sqlite3 will see the actual values of the conflicting row while executing the insert statement.

If I wanted to replace all old values with excluded's values with a where clause

Casting to TableInfo in the where callback and using columnsByName in there to build a where clause should work.

[CyMathew]

For some reason, I didn't realize that columnsByName actually give the value when everything is translated to raw SQL.

That makes sense. Thank you