Search code examples
androidkotlinsqldelight

SQLDelight - Emit on Parent Tables when Child Tables are Modified

I haven't come across an obvious way to emit notifications on parent tables when child tables are updated with SQLDelight.

Here is an E/R Diagram roughly outlining the structure I have in play:

enter image description here

Which translates roughly to the following database schema:

R(ID)
S(ID, parentId)
T(S_ID, attribute)

And these .sq files:

-- com/package/R.sq
CREATE TABLE R (
    ID Text PRIMARY KEY
);

-- com/package/S.sq
CREATE TABLE S (
    ID Text PRIMARY KEY,
    parentId TEXT REFERENCES R(ID) ON DELETE CASCADE
);

-- com/package/T.sq
CREATE TABLE T (
    S_ID TEXT REFERENCES S(ID) ON DELETE CASCADE
    attribute TEXT,
    PRIMARY KEY(S_ID, attribute)
);

And what I'd like to happen is that when attributes of T are updated, SELECT * queries on S (and thus R) are notified, and likewise when attributes of S are updated queries on R are notified.

Has anyone come across a way to do this with SQLDelight?

In SqlBrite, I could specify a set of trigger tables on a query such that when any of those trigger tables are modified the query is re-run and the result set is emitted downstream. So I could declare my trigger tables as R, S, and T on SELECT * FROM R and modifications to R, S, or T caused that to be re-queried.

Solution

  • this is a bug in SQLDelight. here is the github issue for it: https://github.com/cashapp/sqldelight/issues/1325