feature request: execute multiple statments in one call

[stevenctl]

All methods in this package only support executing a single statement. While obviously
you can work around this by manually splitting out SQL or a potentially sketchy .split(';'),
those are inconvieint and error-prone.

I'd like to do something like:

final migration = await File("./assets/ddl/$fname").readAsString();
await db.execute(script); // only supports 1 statement!

Currently:

• execute calls select under the hood
• and executeBatch does prepare then statement.execute().

We need something that uses prepareMultiple.

For web: https://github.com/simolus3/sqlite3.dart/blob/v2/lib/src/worker.dart#L345

API Options

1. New method with no parameters

Future<void> executeMultiple(String sql)
// or
Future<void> executeRaw(String sql)

Why didn't I include a List<List<Object?>> parameterSets or even a flat List<Object?>?

It's pretty ambigious how that should be handled with multiple statements:

• Do we apply every parameter set to every statement?
• Do we apply parameterSets[n] to statements[n]?
• If parameterSets.length != statements.length, is that an error?

IIUC adding optional parameters is not a breaking change, so that could be added later.

2. Modify executeBatch behavior

Current implementation uses .prepare, could switch to .prepareMultiple and execute statements sequentially (stop on first error).

Breaking change risk: Users unknowingly passing multiple statements who rely on only the first executing.

Could make explicit:

Future<void> executeBatch(
  String sql,
  List<List<Object?>> parameterSets, {
  bool allowMultipleStatements = true,
})

[simolus3]

It's pretty ambigious how that should be handled with multiple statements:

I agree that not supporting parameters here is the right move.

In package:sqlite3, the behavior is that if you call CommonDatabase.execute without parameters, it starts supporting multiple statements. This is behavior is unfortunately undocumented (sorry for the work you put into the prepareMultiple approach!), but I think relying on that might be an easier approach for these wrapper packages.

Users unknowingly passing multiple statements who rely on only the first executing.

This is a good point, I think we should consistently pass checkNoTail: true when preparing statements. That would technically also be a breaking change, but I think going from "silently ignoring half of the statement" to an error would obviously be an improvement. cc @stevensJourney for concerns, otherwise I can add that next week.

[stevenctl]

Hmm, when I tried using the SqliteMigrations utility from this package, I did not get that behind (and I didn't pass any parameters). I see, this package still needs to be updated with something like if (paramters.isEmpty) { db.execute } else { db.select }

[stevenctl]

Thanks for the links to the underlying implementation on the PR. I will do some more testing

[stevenctl]

Thinking about this some more, and extending the behavior of execute in this package seems like a breaking API change. The underlying sqlite3 execute returns void which is probably why this package uses .select..

Consider a test like the one at the bottom of this comment, and the quickest (and maybe naive) impl of what I think you're suggesting:

if (params.isNotEmpty) {
  return db.select(...) // old behavior
}
await db.execute(...)
return ResultSet([], [], []);

With this, the following tests fail:

• RETURNING without params test
• Basic Tests should error on dangling transactions (existing test)
• Basic Tests Basic Setup (another existing test that wants to see [{'journal_mode': 'wal'}] as a result.

But without this, the execute handles multiple statements I added below would fail.

I don't there is a good way to evolve the current execute semantics here.

---

    test('execute single statement with RETURNING populates ResultSet',
        () async {
      final db = await testUtils.setupDatabase(path: path);
      await createTables(db);
      final result = await db.execute(
          'INSERT INTO test_data(description) VALUES(?) RETURNING id, description',
          ['test returning with params']);

      expect(result.columnNames, equals(['id', 'description']));
      expect(result.rows.length, equals(1));
      expect(result.rows[0][0], isA<int>());
      expect(result.rows[0][1], equals('test returning with params'));
    });

    test(
        'execute single statment with RETURNING populates ResultSet without params',
        () async {
      final db = await testUtils.setupDatabase(path: path);
      await createTables(db);
      final result = await db.execute(
          'INSERT INTO test_data(description) VALUES("test returning without params") RETURNING id, description');

      expect(result.columnNames, equals(['id', 'description']));
      expect(result.rows.length, equals(1));
      expect(result.rows[0][0], isA<int>());
      expect(result.rows[0][1], equals('test returning without params'));
    });

    test('execute handles multiple statements', () async {
      final db = await testUtils.setupDatabase(path: path);
      await createTables(db);

      await db.execute('''
        INSERT INTO test_data(description) VALUES('row1');
        INSERT INTO test_data(description) VALUES('row2');
      ''');

      final results =
          await db.getAll('SELECT description FROM test_data ORDER BY id');
      expect(results.length, equals(2));
      expect(results.rows[0], equals(['row1']));
      expect(results.rows[1], equals(['row2']));

      await db.close();
    });

[stevensJourney]

but I think going from "silently ignoring half of the statement" to an error would obviously be an improvement. cc @stevensJourney for concerns, otherwise I can add that next week.

I agree, reporting this as an error feels like a good idea.

[simolus3]

This package passes checkNoTail: true when preparing statements already. I've fixed select() silently dropping statements upstream in simolus3/sqlite3.dart@3e56933 (for 3.x releases only, but we'll get that eventually).