Database Synchronization only Reading

[apoleo88]

I would like to be able to synchronize 2 databases. The second database is read-only, so there is no problem with conflicts.

What I want to achieve can be seen as a differential backup.

Right now the easier method I have is to copy and sync the entire database.
Implement a database's recorder that stores the changes and updates only those seem pretty time-consuming.

Any suggestion is appreciated! : )

Useful discussion: #136

[simolus3]

Did you run into problems with the copy-and-sync files approach?

You could open two databases at once using ATTACH and then copy the data over using nothing but sql. It's unlikely that moor will have builtin support for that, but you can use moor to iterate over tables and build the sql. So you could open a transaction on the copy, delete everything from all tables and then use INSERT INTO SELECT to copy data over. If you want incremental changes, you could only delete rows that don't exist in the original database and then use INSERT OR REPLACE.

[apoleo88]

Thank you for the discussion, I think I need to give you more information:

• The Database run on the Flutter App, it is updated regularly.

• The first copy of the Database is encrypted by the App and stored on a storage only Server.

• The second copy runs on another Flutter app with Moor, is read-only, and it downloads the encrypted copy and decrypts it.

So, I can encrypt and upload the file every time, it shouldn't even be so heavy, but I would run it only once a day or so.
What I would like to accomplish is to create a differential backup, so it encrypt/upload/download only that part and can be run every several minutes.
I would like to avoid to compare 2 copies of the database every time that I want to update it.

What I thought to do was to create an empty copy of the DB, catch every event of the main DB, and insert into it the copy of the updated row after every change.
So, every time I want to sync I upload only this DB and then flush it. Then I use INSERT OR REPLACE on the other DB. To delete rows it needs an extra step like I could set everything to NULL in the row to let it know to delete it or create a table that store just two info like table_name and id_to_delete...

If this is the best way, there is a method to catch every change, after executing it, so I can get the table_name and id and create the entry in the second DB?

[simolus3]

Your approach sounds like it could work. Moor doesn't have a builtin hook to get notified about statements before they are sent.
However, you could write a custom method to do this. This should work for all tables:

// Inside your database or dao
Future<int> insertForBackup<T extends Table, D extends DataClass>(
    TableInfo<T, D> table, Insertable<D> row) async {
  await super.into(table).insert(row);

  // Add to backup
}

And then use insertForBackup(table, row) insert of into(table).insert(row). You can use something similar for updates and deletes.

[apoleo88]

You could open two databases at once using ATTACH and then copy the data over using nothing but sql. It's unlikely that moor will have builtin support for that, but you can use moor to iterate over tables and build the sql. So you could open a transaction on the copy, delete everything from all tables and then use INSERT INTO SELECT to copy data over. If you want incremental changes, you could only delete rows that don't exist in the original database and then use INSERT OR REPLACE.

How can I open a second database, with the same tables, but empty?

[simolus3]

Do you write data in your onCreate function? If not, you could just create another instance of your database class that uses a different database file. You can just call customQuery('SELECT 1;') on the second db to open it, which will automatically create all tables but leaves them empty.

[apoleo88]

Do you write data in your onCreate function? If not, you could just create another instance of your database class that uses a different database file. You can just call customQuery('SELECT 1;') on the second db to open it, which will automatically create all tables but leaves them empty.

It is not clear to me how to create multiple instances of the database.

I am using the same class, but passing now the name of the file in the constructor:
MyDatabase db = MyDatabase(databaseFileName)

But when I call it with the second fileName I get:

WARNING (moor): It looks like you've created the database classMyDatabase multiple times. When these two databases use the same QueryExecutor, race conditions will ocur and might corrupt the database. 
Try to follow the advice at https://moor.simonbinder.eu/faq/#using-the-database or, if you know what you're doing, set moorRuntimeOptions.dontWarnAboutMultipleDatabases = true

There is not much information on the link and I don't know what I am doing if I set moorRuntimeOptions.dontWarnAboutMultipleDatabases = true

---

Your approach sounds like it could work. Moor doesn't have a builtin hook to get notified about statements before they are sent.
However, you could write a custom method to do this. This should work for all tables:

// Inside your database or dao
Future<int> insertForBackup<T extends Table, D extends DataClass>(
    TableInfo<T, D> table, Insertable<D> row) async {
  await super.into(table).insert(row);

  // Add to backup
}

And then use insertForBackup(table, row) insert of into(table).insert(row). You can use something similar for updates and deletes.

Not knowing better, I did:

  Future insertAllForBackup<T extends Table, D extends DataClass>(TableInfo<T, D> table, List<Insertable<D>> rows) async {
    for (final row in rows) {
      super.into(table).insert(row);
    }
    // Add to backup
  }
  Future deleteForBackup<T extends Table, D extends DataClass>(TableInfo<T, D> table, Insertable<D> row) {
    super.delete(table).delete(row);

    // Add to backup
  }

But I am not sure how to do a generic update with the Companion, there is a way to pass only fieldToChange: newValue?
Also, an easy way to retrieve a column from an Insertable<D> row (I need the id)?

[simolus3]

and I don't know what I am doing if I set moorRuntimeOptions.dontWarnAboutMultipleDatabases = true

In earlier moor versions, I got many reports from users seeing that their tables didn't get created. It turned out that this problem happens when creating multiple database classes (on the same file), as that can break the migration. I added that warning to inform users that they probably want to only have one database class.

Having two database classes that point to different files is perfectly safe, so you can disable ignore that warning.

But I am not sure how to do a generic update with the Companion, there is a way to pass only fieldToChange: newValue?

You can use SomeTableCompanion(fieldToChange: Value(newValue)). This will update that column, all the others are left as they were before.

column from an Insertable row (I need the id)?

You can use table.mapFromCompanion() if the id is set on the companion. But if you want to delete a row by its id, you can just use

Future<void> deleteById<T extends Table, D extends DataClass>(
    TableInfo<T, D> table, int id) async {
  final idColumn = table.columnsByName['id'] as GeneratedIntColumn;
  await (delete(table)..where((tbl) => idColumn.equals(id))).go();
  
  // Add to backup
}

[iampopal]

Hello @simolus3,
We are using Drift for a locale network database, which does not needs the internet.
For better security
At the end of the the day we backup database a new copy of database file to cloud.

Our database size is getting huge. like after a year database size is arround 10MB Now.

We need an approach to upload a new database file with

• New Create Rows in tables
• New Updated row
• New Delete deleted row

So we can send couples of KB of the new database file to the server and marge it with the new database

can you please tell us how we can achieve this and which approach we can use?

[simolus3]

There are a number of possible solutions that come to mind. For new and updated rows, you could have a column to describe their state (e.g. whether the row was created or modified since the last synchronization). For a synchronization, you could go through all tables and check those flags to see whether the row needs to be uploaded.
For deletes, you could have a new table where rows store the original table name and the primary key of deleted rows.

Alternatively, you could have a single "backlog" table storing all pending writes (so each row would be a tuple of the table, the primary key and a flag of what needs to be uploaded). Then you could define triggers to automatically insert into that table for each other write to the database perhaps.