How to backup and restore certain tables only?

[mytc]

There is a discussion on how to restore and backup in flutter moor here..
It works only for backup and restore entire database.
But what I want is to export and restore specific tables only.

Hence I wonder is there any simple way to do this?
I am thinking of using csv, but it seem complicated when the tables are related with each other. Eg, User table, and User entry where user entry have foreign key of user.
Other option would be json file.
And what about the performance of the process, if the table has, say more than 10k of entries?

[simolus3]

External representations like json or csv should work. When there's a lot of data, you may have to write it in small chunks.

However, if you just need backup/restore, I think the easiest way is to just use sqlite3 to copy the data over. I haven't tried it, but I think something like this should work:

extension CreateBackup on GeneratedDatabase {
  Future<void> createBackup(File to, List<TableInfo> tables) async {
    await customStatement('ATTACH DATABASE ? AS backup_target', [to.path]);

    final migrator = Migrator(this);
    for (final table in tables) {
      final sourceName = table.actualTableName;
      final targetName = 'backup_target.${table.actualTableName}';

      final aliased = alias(table, targetName) as TableInfo;
      await migrator.createTable(aliased);

      await customStatement(
          'INSERT INTO $targetName SELECT * FROM $sourceName');
    }

    await customStatement('DETACH backup_target');
  }
}

To restore a backup, you could once again attach the additional database and copy values over in the other direction.

[mytc]

ok, I now understand the attach feature.
Problem now is how to open another database instance? And how to use the doWhenOpened ?
Right now I am doing this, but don't know if this is correct way to open the database.

extension CreateBackup on GeneratedDatabase {
   Future<void> myBackup(File to) async {
    try {
      var ndb = _openBackupDB(to);//correct way to open database instance?
      doWhenOpened((_) => Future.value());//how to create table here?
  
      ndb.close();
    } catch (e) {
      print(e);
    }
  }

  LazyDatabase _openBackupDB(File to) {
    return LazyDatabase(() async {
      return VmDatabase(to, logStatements: true);
    });
  }
}

[simolus3]

Sorry for being unclear, I think the easiest way is to open another instance of your database class. When you use that instance, it will open the connection and create all tables, which is what we want here.

So something like

Future<void> createBackup(File to, List<TableInfo> tables) async {
  moorRuntimeOptions.dontWarnAboutMultipleDatabases = true;
  final secondary = YourDatabaseClass(_openBackupDB(to));
  // Opening the database to ensure all tables are created in the `to` file!
  await secondary.doWhenOpened((_) => Future.value());
  await secondary.close();
  
  await customStatement('ATTACH ... AS backup_target');
  // If you're still getting exceptions you may want to run this in a transaction
  for (final table in tables) {
    final sourceName = table.actualTableName;
    final targetName = 'backup_target.${table.actualTableName}';

    await customStatement(
        'INSERT INTO $targetName SELECT * FROM $sourceName');
   }
  await customStatement('DETACH backup_target');
}

[mytc]

This is how I did:

class BackupIns {
  static String bc_records =
      'CREATE TABLE IF NOT EXISTS bc_records (sys INTEGER, dias INTEGER, pul INTEGER,time INTEGER, uid INTEGER)';

  openBackup(File to) async {
    moorRuntimeOptions.dontWarnAboutMultipleDatabases = true;
    final secondary = AppDatabase(_openBackupDB(to));
    await secondary.doWhenOpened((_) async {
      try {
        await secondary.customStatement(bc_records);
      } catch (e) {
        print('create table error ' + e.toString());
      }
    });
    await secondary.close();
  }

  LazyDatabase _openBackupDB(File to) {
    return LazyDatabase(() async {
      return VmDatabase(to, logStatements: true);
    });
  }
}

But why I can't see the file in the to.path via file manger?
In the debug log, I can see this:
I/flutter (20018): Moor: Sent CREATE TABLE IF NOT EXISTS bc_records (sys INTEGER, dias INTEGER, pul INTEGER, time INTEGER, uid INTEGER) with args []

It should be successfully created right?

[simolus3]

Yes, it should be. Do you see the original database file in your file manager?

[mytc]

Yes, I can see the original file.
I also tried the original and secondary databases in same directory, but all i can see is the Original database file.

The only difference is that for the backup database file path was from flutter Filepicker package, where let user choose the backup path.
But I tried with file creation, by using the filepicker, it works. So, i don't think there is permission issue as I already handle by permission handler package.