Watch to emit deletion by cascade?

[perojas3]

So I am using watch to create a stream that show the changes in the database.
Also used foreign keys with delete cascade, so every time I delete an entity, other ones are deleted too.
Can I do the same at once? Deleting with cascade and automatically shown in the stream?
In android's room that can be done, but with moor I tried some tests and no the stream didn't emitted the changed, even though the database made the change.
But the problem could be mine.
It can be done?

[simolus3]

If you define the foreign key constraint in a moor file, that should be reflected in the generated code. The generated database should override streamUpdateRules to indicate how deletes can have cascading effects.

[perojas3]

Well... I haven't found a streamUpdateRules in the generated code.
I am using the foreing key this way IntColumn get flexibleEventId => integer().customConstraint('REFERENCES flexible_events(id) ON DELETE CASCADE')();. Also activated the foreign key in migration of the datbase.

[simolus3]

Ah - right. The customConstraint isn't checked at compile time, so we don't use it to infer cascading updates. Advanced SQL features only really work in moor files since we can parse the full CREATE TABLE statement from there.

You could also add the override manually. So if you had a table Bars referencing a table Foos, you could write:

@UseMoor(...)
class Database extends _$Database {
  StreamQueryUpdateRules get streamUpdateRules {
    return StreamQueryUpdateRules([
      WritePropagation(
        on: TableUpdateQuery.onTable(foots, limitUpdateKind: UpdateKind.delete),
        result: [
          TableUpdate.onTable(bars, kind: UpdateKind.delete),
        ],
      ),
    ]);
  }
}

[angel1st]

@simolus3 - I have an identical case. To clarify your answer, I should use the above snippet, provided by you along with the customConstraint as set by @perojas3 or your snipped should suffice?

[perojas3]

@simolus3 Well it is working. Thank you very much.
I had to update to the last version.
And to note instead of doing one emit from existing to null, emits every deletion. In my case I have one entity with a relationship 1:n to other two, and one of those have a 1:n relationship with other 2 entities. So at the end 5 emit until complete deletion. Take in account that have to make 5 stream by watching these 5 entities and combining them with rxdart, like is shown in the documentation.
Now, let's solve @angel1st problem. Yes I put the IntColumn get flexibleEventId => integer().customConstraint('REFERENCES flexible_events(id) ON DELETE CASCADE')() and then used the snippet at the database definition. You will have to override the get streamUpdateRules. And make sure you are in a version of the library that supports StreamQueryUpdateRules.

[angel1st]

@perojas3 - thanks for jumping in and clarify!
However, since my moor knowledge is still limited, I would appreciate I bit further explanation.
Let me make an example below and I would appreciate your feedback if I miss something:
Let's have two tables - Persons and Activities, where the relation between them is 1:n (one person : many activities). These are the tables:

class Persons extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  DateTimeColumn get created => dateTime().withDefault(currentDateAndTime)();
}

@DataClassName("Activity")
class Activities extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get personId =>
      integer().customConstraint('REFERENCES persons(id) ON DELETE CASCADE')();
  TextColumn get name => text()();
}

Now, let's have a look at the AppDatabase class:

@UseMoor(tables: [Persons, Activities], daos: [PersonsDao, ActivitiesDao])
class AppDatabase extends _$AppDatabase {
  // we tell the database where to store the data with this constructor
  AppDatabase() : super(_openConnection());

  static Future<DatabaseConnection> createIsolateConnection() async {
    final isolate = await _createMoorIsolate();
    return await isolate.connect();
  }

  AppDatabase.connect(DatabaseConnection connection)
      : super.connect(connection);

  // you should bump this number whenever you change or add a table definition. Migrations
  // are covered later in this readme.
  @override
  int get schemaVersion => 1;

  StreamQueryUpdateRules get streamUpdateRules {
    return StreamQueryUpdateRules([
      WritePropagation(
        on: TableUpdateQuery.onTable(persons, limitUpdateKind: UpdateKind.delete),
        result: [
          TableUpdate.onTable(activities, kind: UpdateKind.delete),
        ],
      ),
    ]);
  }

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      beforeOpen: (details) async {
        await customStatement('PRAGMA temp_store = MEMORY;');
        await customStatement('PRAGMA foreign_keys = ON;');
       },
    );
  }
}

Hopefully, so far both - tables and AppDatabase classes are correctly updated - let me know if they are.
The next part however is not clear to me, let me quote - "You will have to override the get streamUpdateRules" - where should I find and override this one?

[simolus3]

tables and AppDatabase classes are correctly updated - let me know if they are

The snippet you posted is correct, yes. It basically tells moor that deleting a person might delete an activity as well. Might want to add an @override on the streamUpdateRules though.

where should I find and override this one?

You already did in your snippet 😉 If your IDE complains about streamUpdateRules not overriding anything, you probably need to upgrade your moor dependencies.

[angel1st]

@simolus3 - I see, basically, the snippet added to the AppDatabase class is the discussed override part. Then all should be set, shouldn't be?

[angel1st]

@simolus3 - and while we are still on the topic, what should streamUpdateRules look like in case we have a longer chain, e.g. person > activity > item?

[simolus3]

Then all should be set, shouldn't be?

Yes. Let me know if that doesn't work for you.

in case we have a longer chain

You only need to include the direct effects, moor will crawl the rules to respect transitive effects as well. So it could look like

    return StreamQueryUpdateRules([
      WritePropagation(
        on: TableUpdateQuery.onTable(persons, limitUpdateKind: UpdateKind.delete),
        result: [
          TableUpdate.onTable(activities, kind: UpdateKind.delete),
        ],
      ),
      WritePropagation(
        on: TableUpdateQuery.onTable(activities, limitUpdateKind: UpdateKind.delete),
        result: [
          TableUpdate.onTable(item, kind: UpdateKind.delete),
        ],
      ),
    ]);

[karlopenguini]

How is this resolved in Drift 2.1.0? This issue still persists.

[simolus3]

@karlopenguini Does the generated file drift gives you have a StreamQueryUpdateRules in it? If not, how are you declaring foreign keys or triggers? You might be running into a separate bug then.

[Amr-Elgazar]

@perojas3 - thanks for jumping in and clarify! However, since my moor knowledge is still limited, I would appreciate I bit further explanation. Let me make an example below and I would appreciate your feedback if I miss something: Let's have two tables - Persons and Activities, where the relation between them is 1:n (one person : many activities). These are the tables:

class Persons extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  DateTimeColumn get created => dateTime().withDefault(currentDateAndTime)();
}

@DataClassName("Activity")
class Activities extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get personId =>
      integer().customConstraint('REFERENCES persons(id) ON DELETE CASCADE')();
  TextColumn get name => text()();
}

Now, let's have a look at the AppDatabase class:

@UseMoor(tables: [Persons, Activities], daos: [PersonsDao, ActivitiesDao])
class AppDatabase extends _$AppDatabase {
  // we tell the database where to store the data with this constructor
  AppDatabase() : super(_openConnection());

  static Future<DatabaseConnection> createIsolateConnection() async {
    final isolate = await _createMoorIsolate();
    return await isolate.connect();
  }

  AppDatabase.connect(DatabaseConnection connection)
      : super.connect(connection);

  // you should bump this number whenever you change or add a table definition. Migrations
  // are covered later in this readme.
  @override
  int get schemaVersion => 1;

  StreamQueryUpdateRules get streamUpdateRules {
    return StreamQueryUpdateRules([
      WritePropagation(
        on: TableUpdateQuery.onTable(persons, limitUpdateKind: UpdateKind.delete),
        result: [
          TableUpdate.onTable(activities, kind: UpdateKind.delete),
        ],
      ),
    ]);
  }

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      beforeOpen: (details) async {
        await customStatement('PRAGMA temp_store = MEMORY;');
        await customStatement('PRAGMA foreign_keys = ON;');
       },
    );
  }
}

Hopefully, so far both - tables and AppDatabase classes are correctly updated - let me know if they are. The next part however is not clear to me, let me quote - "You will have to override the get streamUpdateRules" - where should I find and override this one?
[WARNING] drift_dev on lib/db/subscription_dbs/subscription_sections/subscriptions_sections_db.dart: line 18, column 7 of package:thepump/db/subscription_dbs/subscription_sections/subscriptions_sections_db.dart: subscriptionsSectionsTable could not be found in any import.