Moor: Add InsertMode InsertOrUpdate

[kuhnroyal]

Since sqlite 3.24.0 there is a new INSERT ON CONFLICT DO UPDATE syntax: https://sqlite.org/lang_UPSERT.html

I am wondering if this can be added since at least moor_ffi can guarantee this version.

As an alternative I could write the statements myself in moor files but listing all the fields/values there is cumbersome.
Is something like this possible ? Automatically generating the fields and values blocks and have the method expect an entity of this table type.

insertOrUpdate: INSERT INTO myTable($fields) VALUES($values) ON CONFLICT($pk) UPDATE SET $fields = $values

[simolus3]

I think we should support upsert clauses in both moor files and the query builder at runtime, with an appropriate warning that it might not be supported on all platforms.

I'm not sure about extending $placeholders to support a list of columns, primary keys or set-clauses. It causes all kind of ambiguities in the parser that are hard to resolve, makes the query much harder to analyze and it'd also require changes in the runtime api.
Something like UPDATE SET $fields or INSERT INTO VALUES $fields that would generate the appropriate companion could work, but it's still a lot of work to handle.
I assume the use case for the query you posted would be resolved if we handled upsert clauses properly?

[kuhnroyal]

I assume the use case for the query you posted would be resolved if we handled upsert clauses properly?

Yes this would be sufficient, I would still prefer some way to have CRUD queries generated without using the query builder.

Just throwing this out here based on this, maybe a lot of CRUD cases could be handled much simpler with something like this:

@UseDao({
   queries: [
      Query(name: 'myFancyInsert', table: 'foo', type: QueryType.InsertOrReplace),
      Query(name: 'insertOrUpdateFoo', table: 'foo', type: QueryType.InsertOrUpdate),
      Query(name: 'countAllFoos', table: 'foo', type: QueryType.Count),
      Query(name: 'existsById', table: 'foo', type: QueryType.ExistsByPk),
      ...
   ]
})

Should be no parsing involved, just generated statements based on existing query builder syntax but would save a lot of work in CRUD heavy applications.

[wilkomanger]

When will this be added to the query builder? Would love to see this!

[simolus3]

I definitely want to support this. The problem is that generated companions can only hold values, not arbitrary sql expressions.

Taking the example from sqlite docs:

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;

The moor api to write that insert should look something like

into(vocabulary)
 .insert(
    VocabularyCompanion.insert(word: 'jovial')
    onConflict: DoUpdate((old) => VocabularyCompanion(value: old + 1)),
  )

Where old is an instance of the table, similar to the callback for say where .

However, so far I didn't find a good way to generalize companions from holding values to holding expressions without being too breaking.

[wilkomanger]

Ah, I see. My use case is a bit simpler, all I need is to insert some values, and if there's conflict replace those values, but leave the values of the row that were not specified unchanged. I suppose that could be implemented simpler? Then all you'd need is a insertOrUpdate InsertMode.

But I understand you'd want to do it right and allow for expressions of course. Maybe both the 'expression version' and the simpler one can coexist? Then the simpler one could be implemented first?

[simolus3]

I have a first version working on develop. It uses the more general syntax, allowing custom updates.

insert some values, and if there's conflict replace those values, but leave the values of the row that were not specified unchanged

With the new api, this should work by using

final companion = MyCompanion(...);

into(table).insert(companion, onConflict: DoUpdate((_) => companion));

Which would generate

INSERT INTO table (c1, ..., cN) VALUES (v1, ..., vN) ON CONFLICT DO UPDATE SET c1 = v1, ..., cN = vN

That's what you need, right? As you said, this use case is probably the most common for upsert clauses, so it's equivalent to this shorthand I added:

into(table).insertOnConflictUpdate(MyCompanion(...))

[wilkomanger]

Yes exactly, that's great! Thank you!

[kuhnroyal]

Could insertOnConflictUpdate also return an int for autoincrement tables the same way that insert does?

  /// If the table contains an auto-increment column, the generated value will
  /// be returned. If there is no auto-increment column, you can't rely on the
  /// return value, but the future will complete with an error if the insert
  /// fails.

[simolus3]

Good point, done (721d25ff3c57b9fa91f10f855c887dec710ca327)

[davidmartos96]

@simolus3 I have just found out about that notice in the documentation of insert.

If there is no auto-increment column, you can't rely on the return value

Does that apply to tables which don't use an autoincrement column but instead an INTEGER PRIMARY KEY NOT NULL column?

[simolus3]

That's just bad wording - insert returns the value of sqlite3_last_insert_rowid, so any alias for the rowid will work as well. I'll update the documentation to be more accurate here.