On delete cascade

[aleripe]

Hi Alex, as always thanks for your great job! I have an Android/iOS app which uses Sqflite and has a fairly complex database structure. Now I want to make it web compatible and started working with Sembast, which seems very good too. I have created a store for each table and an autoincrement integer primary key, so it is the more similar to the Sqlite database as possible. Everything works properly, except for the record deletion where obviously there is no cascade and that leaves a lot of orphaned records into other stores. In your opinion, what is the best way to tackle this problem that does not include removing all the related data by hand (as I said the structure is really complex and changes often)?

Thank you!!

Alessandro

[alextekartik]

Good question! Unfortunately, currently sembast does not include any solution for that. I would personally create an additional layer that would take care of “cascade deleting” in a transaction, but well, it is indeed by hand…

That is said, I could think of a solution in the future for that. I guess some hook/trigger that could be called in a transaction when some data is modified/deleted could be a maintainable solution.

store.onDelete(db).listen((txn, snapshot) {
  otherStore.record(snapshot[‘otherRecordKey’]).delete(txn);
}

store.onAdd(db).listen((txn, snapshot) {
  ...
}

store.onUpdate(db).listen((txn, oldSnapshot, newSnapshot) {
  ...   
}

So far the data remains schemaless so there is no plan at this point to parse data and find/handle “reference” to other record.

Well...suggestion are welcome but in the mean time you are on your own!

[aleripe]

Hi Alex, your solution for the future would really be a game changer! I was hoping for something like that, but it seems it's only an idea. So by hand is the only way at the moment... 😢 I think no other NoSQL DB handles this case, so not really your fault! 😄

Thanks!

[alextekartik]

So I ended up implementing it. It is available in version 3.1.0. More information here: https://github.com/tekartik/sembast.dart/blob/master/sembast/doc/triggers.md

You still have to create the proper listener/trigger manually though (i.e. no auto delete reference exists).

[aleripe]

Wow Alex, I didn't expect this change to come so quickly while I was still struggling to find an alternative solution! It seems very promising and I look forward to use it in my project. Is this available for sembast_web, too? Thank you so much!!

[alextekartik]

Since I was dealing with similar issues and needs, sometimes you just need someone else needs to tackle it. Yes is is available in sembast_web too, you just need to enforce at least version 3.1.0 on sembast (but pub upgrade will work).

[aleripe]

Hi Alex, sorry to bother you again, but I can't get it to work and don't know where I'm wrong. Please tell me if this is the correct approach.

I'm doing something similar to the Sqflite executor pattern using the DatabaseClient interface, because I have many repositories than can execute their methods in transaction and want to enlist them all inside the same one.

This is how I get the executor:

Future<DatabaseClient> get executor async {
   if (_transaction != null) {
      return _transaction;
   } else {
      return await _database;
   }
}

And this is how my executeInTransaction method looks like:

Future<T> executeInTransaction<T>(Future<T> Function() action) async {
   return await _lock.synchronized<T>(() async {
      if (_transaction != null) {
         return await action();
      }
      final db = await _database;
      return await db.transaction<T>((transaction) async {
        _transaction = transaction;
        final result = await action();
        _transaction = null;
        return result;
      });
    });
  }

So my base delete method:

Future<void> delete(String storeName, int id) async {
    intMapStoreFactory.store('otherStoreName').addOnChangesListener(await _database,
        (transaction, changes) async {
      for (var change in changes) {
        if (change.isDelete) {
           ....
        }
      }
    });

    final store = intMapStoreFactory.store(storeName);
    final ex = await executor;
    return await store.record(id).delete(ex);
}

However I can't use the executor on addOnChangesListener because it only accepts a Database object, so I don't know if it is using the same transaction or not.

Am I missing something?

Thanks!

[alextekartik]

I guess I was not clear on its usage. It is in fact similar to triggers in SQLite, so it is something you set once on a database (it is global to the database) after opening it and that you typically never remove. In your code above you are adding a listener everytime delete is called so you might get called multiple times.

Your database should be considered as 'ready' when the triggers are set. Pseudo code:

Future<Database> _database
Future<Database> get database async => _database ??= {
  var db = openDatabase();
  // Add change listeners
  await setupTriggers();
  // Database is ready!
  return db;
} ();

In my case, I have used it to track changes in a store to save data in another store.

When your listener is called, the transaction object in the callback will be your current transaction (even if you call it on a database object, a transaction is always created at some point) and you have to use this transaction object for you other operation in the listener if any (not the global executor object).

I guess I'm not clear. Unfortunately I don't have a project to share. As a side note, I would create the store reference globally once (my stores are typically global variables).

Maybe the unit tests on it could help (or not...):

• https://github.com/tekartik/sembast.dart/blob/master/sembast/test/changes_listener_test.dart
• https://github.com/tekartik/sembast.dart/blob/70c9a0169c5eec58070598e2c6ae4afb6685321c/sembast_test/lib/doc_test.dart#L610-L651

[aleripe]

I have adapted my code to your example but still it's not working... listener is added once, but it is called twice with the same old snapshot and the related entity is not deleted. I'm sure I'm still doing something wrong. I'll have another look at it tomorrow.

Thanks for your help!