There is something we can do to timeout well on android?

[ivofernandes]

I tried this package but I have a problem that queries that take 3s in an sqlite in sqlite cipher take about 9s, and some queries never end...that would not be a problem if the sqlite connection still worked after that but looks like the only way of really close sqlite session is to restart the app after that.

Any idea about how to restart a sqlite after a never ending query? Or is some problem in the android sqlite driver?

[davidmartos96]

What device are you testing with?

[davidmartos96]

Also, you may need to optimize the query with indexes for example. 3s query for a mobile app sounds a bit heavy. But of course, it depends on the context

[davidmartos96]

You can also try running the following after opening the database:

db.rawQuery("PRAGMA cipher_memory_security = OFF");

It helps low end devices in terms of performance when using SQLCipher

[ivofernandes]

I'm testing in a Huawei p30 pro, but yeah I know the query is super heavy, but is not a problem of the phone itself, it's also slow when I run in my macbook with an intel i9, and probably is not even a problem of the encryption, I guess the problem is that sqlite gets locked trying to solve that query and never gets available again, even if I put a timeout, the timeout is only for the connection, and the sqlite will not accept any connection after it.

If it is on a server you can kill connections that are blocking the database, but something like that is impossible in a phone?

[davidmartos96]

As far as I know, the Android Sqlite/SQLCipher API does not support cancelling a query once sent. This library, same as sqflite is a wrapper of that API.

[davidmartos96]

@ivofernandes If you really need to achieve concurrency of queries, I don't think either this package or sqflite would serve your needs, as they are both based on Flutter Platform Channels and communication with the native platform.

You could however migrate to using the drift package which can connect to SQLite/SQLCipher with FFI in pure Dart. This allows creating multiple connections across Dart Isolates (Multi process), and achieve connection concurrency.

This is a small demo, with low level calls, drift is much more high level. The important bits from the example are the isolates and the MultiExecutor.withReadPool. You could adapt this pure Dart unit test demo into the regular way of using Drift on a Flutter app.

If you want to try it out, you may need to tweak the "LIMIT 30000000" into a lower number, so that it runs in a manageable amount of time. I've tweaked it on my machine so that it takes around 2 seconds.

import 'dart:io';
import 'package:drift/drift.dart';
import 'package:drift/isolate.dart';
import 'package:drift/native.dart';
import 'package:test/test.dart';
import 'package:path/path.dart' as p;

void main() {
  String fileName = 'drift_shared.db';
  final _file = File(p.join(Directory.systemTemp.path, fileName));

  QueryExecutor _createExecutor() => NativeDatabase(_file);

  DatabaseConnection _forBackgroundIsolate() {
    return DatabaseConnection.fromExecutor(_createExecutor());
  }

  test('drift multi executor', () async {
    if (_file.existsSync()) _file.deleteSync();

    final isolateW = await DriftIsolate.spawn(_forBackgroundIsolate);
    final isolateR1 = await DriftIsolate.spawn(_forBackgroundIsolate);
    final isolateR2 = await DriftIsolate.spawn(_forBackgroundIsolate);

    final writerConn = await isolateW.connect();
    final reader1Conn = await isolateR1.connect();
    final reader2Conn = await isolateR2.connect();

    final multiExec = writerConn.withExecutor(MultiExecutor.withReadPool(
      reads: [
        reader1Conn.executor,
        reader2Conn.executor,
      ],
      write: writerConn.executor,
    ));

    await multiExec.executor.ensureOpen(_FakeExecutorUser());

    final futures = <Future>[];
    final f1 = () async {
      print('Start slow query');
      await multiExec.executor.runSelect('''
WITH RECURSIVE r(i) AS (
  VALUES(0)
  UNION ALL
  SELECT i FROM r
  LIMIT 30000000
)
SELECT i FROM r WHERE i = 1;
''', []);
      print('Finish slow query');
    }();
    futures.add(f1);

    final f2 = () async {
      print('Start quick query');
      await multiExec.executor.runSelect('SELECT 1', []);
      print('Finish quick query');
    }();
    futures.add(f2);

    await Future.wait(futures);

    await multiExec.executor.close();

    await isolateW.shutdownAll();
    await isolateR1.shutdownAll();
    await isolateR2.shutdownAll();
  });
}

class _FakeExecutorUser extends QueryExecutorUser {
  @override
  Future<void> beforeOpen(QueryExecutor executor, OpeningDetails details) {
    return Future.value();
  }

  @override
  int get schemaVersion => 1;
}

[ivofernandes]

Amazing :) thank you