Foreground with database

[zawadZwd]

Hi there, actually it is not an issue :)
I was trying to trigger action on database in foreground ( moor plugin to be more precise, but hive is even more fussy ), but I couldn't find any smart solution for this. I've tried with isolation but it is not working at all. Is it even possible to use db in this way?

[Dev-hwang]

Hello, @zawadZwd

It's possible. This is an example using sqflite plugin.

1. Depend on it.

dependencies:
  sqflite: ^2.0.0+3

2. Create UpdateCountLog model.

class UpdateCountLog {
  final int value;
  final String timestamp;

  const UpdateCountLog({
    required this.value,
    required this.timestamp,
  });

  factory UpdateCountLog.fromJson(Map<String, dynamic> json) {
    return UpdateCountLog(
      value: json['value'] ?? 0,
      timestamp: json['timestamp'] ?? ''
    );
  }

  Map<String, dynamic> toJson() {
    return {
      'value': value,
      'timestamp': timestamp,
    };
  }
}

3. Define UpdateCountLogTable.

const String _kTableName = 'updateCountLogTable';
const String _kColumnNameId = '_id';
const String _kColumnNameValue = 'value';
const String _kColumnNameTimestamp = 'timestamp';

class UpdateCountLogTable {
  String get name => _kTableName;

  String get createQuery =>
      'CREATE TABLE IF NOT EXISTS $_kTableName('
          '$_kColumnNameId INTEGER PRIMARY KEY AUTOINCREMENT,'
          '$_kColumnNameValue INTEGER,'
          '$_kColumnNameTimestamp TEXT)';

  String get dropQuery => 'DROP TABLE $_kTableName';

  Future<List<UpdateCountLog>> select() async {
    final result = <UpdateCountLog>[];

    final db = await SqliteProvider.instance.database;
    await db.query(_kTableName).then((logList) {
      for (var i=0; i<logList.length; i++)
        result.add(UpdateCountLog.fromJson(logList[i]));
    });

    return result;
  }

  Future<int> insert(UpdateCountLog log) async {
    final db = await SqliteProvider.instance.database;
    return await db.insert(_kTableName, log.toJson());
  }
}

3. Create SQLiteProvider.

const String _kDatabaseName = 'mobile.db';

class SQLiteProvider {
  SQLiteProvider._internal();
  static final instance = SQLiteProvider._internal();

  Database? _database;
  Future<Database> get database async {
    if (_database == null)
      _database = await _openDatabase();

    return _database!;
  }

  final updateCountLogTable = UpdateCountLogTable();

  Future<Database> _openDatabase() async {
    String path = join(await getDatabasesPath(), _kDatabaseName);
    return await openDatabase(path, version: 1, onCreate: (db, version) async {
      await db.execute(updateCountLogTable.createQuery);
    });
  }

  Future<void> closeDatabase() async => await _database?.close();
}

4. Finally, use the callback function below.

void callback() {
  SQLiteProvider? sqliteProvider;
  int updateCount = 0;

  FlutterForegroundTask.initDispatcher((timestamp) async {
    if (sqliteProvider == null) {
      sqliteProvider = SQLiteProvider.instance;

      // Get the last saved updateCount.
      final logList = await sqliteProvider?.updateCountLogTable.select();
      if (logList?.isNotEmpty == true) {
        logList?.sort((a, b) => a.timestamp.compareTo(b.timestamp));
        updateCount = logList?.last.value ?? 0;
      }
    }

    final log = UpdateCountLog(
        value: updateCount, timestamp: timestamp.toString());
    await sqliteProvider?.updateCountLogTable.insert(log).then((_) {
      print('saved UpdateCountLog: ${log.toJson()}');
      FlutterForegroundTask.update(notificationText: updateCount.toString());
      updateCount++;
    });
  }, onDestroy: (timestamp) async {
    print('callback() is dead.. x_x');
  });
}

[zawadZwd]

Man, thank you for your time!
Here is the thing:

I'm following your code above nad my db provider looks like this:

const String _databaseName = 'database.db';

class SQLiteProvider {
  SQLiteProvider._internal();

  static final instance = SQLiteProvider._internal();
  final executionTable = ExecutionTable();

  Database? _database;

  Future<Database> get database async => _database ??= await _openDatabase();

  Future<Database> _openDatabase() async {
    final path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(path, version: 1, onCreate: (db, version) async {
      await db.execute(executionTable.createQuery);
    });
  }

  Future<void> closeDatabase() async => await _database?.close();
}

table:

const String _executionTableName = 'executionTableName';
const String _executionColumnNameId = 'id';
const String _executionColumnNameDate = 'date';
const String _executionColumnNameLayer = 'layer';

class ExecutionTable {
  String get name => _executionTableName;

  String get createQuery => '''
    CREATE TABLE IF NOT EXISTS $_executionTableName(
    $_executionColumnNameId INTEGER PRIMARY KEY AUTOINCREMENT,
    $_executionColumnNameDate TEXT,
    $_executionColumnNameLayer TEXT)
  ''';

  Future<List<ExecutionModel>> select() async {
    final result = <ExecutionModel>[];
    final db = await SQLiteProvider.instance.database;
    await db.query(_executionTableName).then((executions) {
      for (var execution in executions) {
        result.add(ExecutionModel.fromJson(execution));
      }
    });

    return result;
  }

  Future<int> insert(ExecutionModel execution) async {
    final db = await SQLiteProvider.instance.database;
    return await db.insert(_executionTableName, execution.toJson());
  }
}

and callback:

void databaseCallback() {
  SQLiteProvider? sqliteProvider;
  FlutterForegroundTask.initDispatcher((timestamp) async {
    if (sqliteProvider == null) {
      sqliteProvider = SQLiteProvider.instance;
    }
    final strTimestamp = timestamp.toString();
    await sqliteProvider?.executionTable.insert(ExecutionModel(date: strTimestamp.substring(0, 19), layer: 'Foreground')).then((value) {
      log('Insert has been done');
      FlutterForegroundTask.update(notificationText: 'Insert date: ${strTimestamp.substring(0, 19)}');
    });

    log('timestamp: $strTimestamp');
  }, onDestroy: (timestamp) async {
    log('Deleted callback');
  });
}

Unfortunately insert in the databaseCallback is not invoking, only the log which shows the timestamp of callback trigger...

[log] 2021-07-28 11:59:13.672398	FlutterForegroundTask started.
[log] timestamp: 2021-07-28 11:59:15.336399
[log] timestamp: 2021-07-28 11:59:45.129345

Insert inside app itself works like a charm :/

[Dev-hwang]

@zawadZwd

There doesn't seem to be any problem with the code.

Could you try adding catchError and whenComplete like below?

void databaseCallback() {
  SQLiteProvider? sqliteProvider;
  FlutterForegroundTask.initDispatcher((timestamp) async {
    if (sqliteProvider == null) {
      sqliteProvider = SQLiteProvider.instance;
    }
    final strTimestamp = timestamp.toString();
    await sqliteProvider?.executionTable.insert(ExecutionModel(date: strTimestamp.substring(0, 19), layer: 'Foreground')).then((value) {
      log('Insert has been done');
      FlutterForegroundTask.update(notificationText: 'Insert date: ${strTimestamp.substring(0, 19)}');
    }).catchError((error) {        // here
      print('error: $error');
    }).whenComplete(() {
      print('complete');
    });

    log('timestamp: $strTimestamp');
  }, onDestroy: (timestamp) async {
    log('Deleted callback');
  });
}

And check that whenComplete works.

[zawadZwd]

Lol, it works now! Dude, I would buy you a coffee if I could :)
Thanks again for the help!