Moor: Method to Clear Database

[codekeyz]

I want to be able to clear the entire database record when user sign's out of my application.

[simolus3]

There are two options here: You could either close the database, delete the file from the device and then re-open it. Or you can delete everything from all tables, without closing the database:

Future<void> deleteEverything() {
  return transaction(() async {
    // you only need this if you've manually enabled foreign keys
    // await customStatement('PRAGMA foreign_keys = OFF');
    for (final table in allTables) {
      await delete(table).go();
    }
  });
}

[Holofox]

@simolus3, I encounter a problem when tables are not cleared when this method is called (most often this happens on real devices), maybe there is another way to clear all tables without closing the database? I use moor_flutter 3.0.0.

class Database extends _$Database {
  final LazyDatabase database;

  Database(this.database) : super(database);

  @override
  int get schemaVersion => 1;

  Future<void> deleteEverything() {
    return transaction(() async {
      for (final table in allTables) {
        await delete(table).go();
      }
    });
  }
}

I can’t understand the reason why this sometimes happens.

[simolus3]

Do you get an error when that happens? Or does it just not clear data?

[kuhnroyal]

Do FKs need to be disabled for this to work?

[Holofox]

Do you get an error when that happens? Or does it just not clear data?

There are no errors. This happens only on specific real devices that I don’t have, by the way, on Android and iOS emulators there is no such problem.

[simolus3]

Are those devices known to ship with a buggy sqlite? I'd love to take a look at workarounds if there are some. But it's very weird that those statements don't do anything without causing an error. Even dropping the target table of a FK constraint seems to work.

To reduce device-specific behavior, you could try switching to moor_ffi, which ships a precompiled sqlite3 library along with your app.

[Holofox]

@simolus3, in fact, the problem appears on most devices, but using the version with moor_ffi, it seems that users no longer have problems clearing tables.