Feature request: Transaction object

[Fredolx]

It would be greatly appreciated if we could get a Transaction object that could be obtained by doing db.writeTransaction() so we could gradually build a transaction and do transaction.commit when done.

Example:

var db = SqliteDatabase(path: "mydb");
var tx = await db.writeTransaction();
var id = await tx.execute("INSERT ...", params);
var id2 = await tx.execute("INSERT ..." params);
// ...
await tx.commit();

Thank you

[simolus3]

What's your use-case for this? Just a preference / not wanting to throw exceptions to roll back transactions? After all, you could gradually build the transaction inside the callback too.

Since SQLite only supports a single concurrent writer, we have to block all other writes while a write transaction is active. An API where you'd obtain a transaction object with manual lifecycle management wouldn't change that. So there's probably not much of a difference between the two styles in practice.

[Fredolx]

I was starting to think of a solution to build the callback gradually, but I realized I can't get the last insert rowid after a statement

  static commitWrite(List<Future<void> Function(SqliteWriteContext)> commits) async {
    var db = await Sql.db;
    db.writeTransaction((tx) async {
      for (var commit in commits) {
        await commit(tx);
      }
    });
  }

Is there an other way you would build the transaction gradually that would allow to get the id of what's inserted?

[simolus3]

but I realized I can't get the last insert rowid after a statement

If I'm not missing anything that should be unrelated to transactions? The execute interface is the same.

Is there an other way you would build the transaction gradually that would allow to get the id of what's inserted?

If it's possible for you to rewrite your queries, you could add a RETURNING rowid at the end of insert / update / delete statements. execute should give you a single row back that contains the rowid as a column.

[Fredolx]

I started doing something along those lines and it works so far. I made a function that takes a List<Future<void> Function(SqliteWriteContext, Map<String, String>)> and executes all of them using a writeTransaction. The map is used to relay data from one sql function to the next (like the last inserted row id). I'll contribute an example so that other people won't have the same questions. Thank you