Sqlite triggers and 🍉 sync risks?

[nmeibergen]

In short

I have a couple of triggers defined in Sqlite. Initiated via the setup case of the unsafeSql part of the 🍉 schema. I noticed however that these triggers work exactly as expected during usage on a device. But, they seem to fail after 'large pull' synchronisations, e.g. when a user logs in a new device and all data is pulled.

My case

I have a column that is completely derived from other columns. It's a concatenation of note text (from a notes table) + tag names (via note_tags table and tags table). This is all build with triggers on these three tables and the resulting column is called search_text on the notes table. I experimentally apply FTS on this specific column to return suitable notes on search. This column itself is not synchronised, its merely used for text search on the device.

Complication

After synchronisation with a new device all data is pulled onto it and I noticed that the search_text column is not created correctly: in a lot of cases some tags are not concatenated, implying that the triggers are not called as expected. My hypothesis is therefore something along the following lines:

Adding the data on this first (large) synchronisation pull might happen for example in multiple transaction running in parallel*? Then the triggers might happen on incomplete data. But maybe something else is happening here. The truth is that my knowledge on the workings of the synchronisation and its resulting impact on Sqlite is not complete.

I thus wanted to verify with you whether there are some risk when implementing triggers in combination with 🍉 sync?

* I'm aware that multiple batches are only turned on when _unsafeBatchPerCollection is set to true, which is not in my case. I am just not sure if multiple batches means the same as multiple transactions here.

Remark

Your answer might also be of importance for this FTS pull request as it is also based on registering triggers in Sqlite.

[PEZO19]

@nmeibergen Do you have any update on this? (Asking because of TRIGGER use potentially.)

[nmeibergen]

@PEZO19 Sorry for not responding earlier, I thought I already did! The above complication was the result of me not understanding the full dynamics of the triggers. I thought, naively, that triggers where appended to the end of each 'transaction'. However, triggers are inserted directly after the trigger statement, even if this statement is followed by many other statements.

So in short: no trigger issues with Watermelon! Just important to have a full grasp of this how triggers are inserted, in particular if you are updating/creating/deleting in bulk.