sqlite statement with two "UPDATE" works in web but not in android

[folsze]

I created a minimal reproducible example, I ket it very simple for you to understand it quickly:
https://github.com/folsze/ionic-capacitor-sqlite-update-bug

Describe the bug
I have a sqlite statement with two relevant "UPDATE" statements inside it. It works in web but not in android.

      UPDATE map -- THIS IS THE FIRST UPDATE STATEMENT THAT I AM TALKING ABOUT
      SET progress = 0.3
      WHERE mapId = ${location.mapId};

      UPDATE mode -- THIS IS THE SECOND UPDATE STATEMENT THAT I AM TALKING ABOUT
      SET progress = 0.3
      WHERE mapId = ${location.mapId} AND modeId = ${location.modeId};

      UPDATE location
      SET progress = 0
      WHERE mapId = ${location.mapId} AND modeId = ${location.modeId} AND locationId = ${location.locationId};

In android only one of the update statements gets executed, the other one is not.

To Reproduce
Click this:

Now this looks good, the progress of the first map & the first mode gets updated from 0 -> 0.3:

BUT ON ANDROID:
Click this:

Here it does not update the mode (it stays 0), only the map 0 -> 0.3:

Desktop (please complete the following information):
Windows Google Chrome

Smartphone (please complete the following information):
PIXEL XL 33 API emulator, ran through Android Studio

Additional things:

I would like to know how the two databases (android sqlite database, web sqlite-wasm database) roughly differ. Is there any explanation or any reason why they are different? I have experienced the two behaving slightly different on a lot of occasions. See my Q&A question:
#389 (comment)

[jepiqueau]

@folsze clone your app try to run it and get

map-mode-location.service.ts:75 Assertion failed: progress should only be undefined onCreate.
(anonymous) @ map-mode-location.service.ts:75
4map-mode-location.service.ts:56 Assertion failed: progress should only be undefined onCreate.
(anonymous) @ map-mode-location.service.ts:56
core.mjs:8405 ERROR Error: Query failed: SelectSQL: queryAll: no such table: location
    at web.js:157:19
    at Generator.throw (<anonymous>)
    at asyncGeneratorStep (asyncToGenerator.js:3:1)
    at _throw (asyncToGenerator.js:25:1)
    at _ZoneDelegate.invoke (zone.js:372:26)
    at Object.onInvoke (core.mjs:24178:33)
    at _ZoneDelegate.invoke (zone.js:371:52)
    at Zone.run (zone.js:134:43)
    at zone.js:1275:36
    at _ZoneDelegate.invokeTask (zone.js:406:31)
handleError @ core.mjs:8405
test.page.ts:34 ionViewDidEnter after 107 ms.
2DevTools failed to load source map: Could not load content for chrome-extension://gekdekpbfehejjiecgonmgmepbdnaggp/adguard/browser-polyfill.js.map: System error: net::ERR_BLOCKED_BY_CLIENT

So if you do not me wasting my time please make sure that when you give a app which reproduces the bug, this app works.

I am not sure that the schema of your database is correct in the way you define the link between your tables

[jepiqueau]

@folsze i also do not see the ON UPDATE CASCADE and ON DELETE CASCADEin the FOREIGN KEY references

[folsze]

Ok sorry my bad I definitely should have checked and downloaded it myself before posting here. I will update it now right away.

[folsze]

@folsze i also do not see the ON UPDATE CASCADE and ON DELETE CASCADEin the FOREIGN KEY references

Thank you for your comments. I will review and try all things you said. I will see if those things fixed the bug and close this issue if they do, if you agree that's the right thing to do. Also let me know if I can/should provide any more info in some way or do anything.

UPDATE:
I cloned the project and I still do not the error that you do (Not in web nor in emulator). However I am using Windows and not MacOS like I assume you are. And I can only use an emulator started from Android Studio (Google Pixel 33 API) as I have Windows+IOS unfortunately.

I will still try to fix the error myself by trying the things you suggested with ON UPDATE CASCADE and will review the database schema.

[folsze]

Hey @jepiqueau I just went to my dad today, he has MacOS too. I set the project up on his laptop (XCode emulator AND on my iPhone) and the project was able to start without the "Assertion Failed" or "table not found" that you mentioned being triggered.

This assertion error probably means that something failed with inserting initial data. And that probably because the creation of the database probably fails.

I pushed the ios folder that it worked with for me to the repo. Maybe you could try running it with this one? Let me know if it still doesn't work.

---

About
"i also do not see the ON UPDATE CASCADE and ON DELETE CASCADEin the FOREIGN KEY references"

DELETE:
deletion will not ever be possible in this app. The only thing that can change is the progress property. I think I don't need it.

UPDATE:
same as above, only progress will update. And progress is not a foreign key reference, it is a field not related to other tables. I think I don't need it.

I appreciate you taking your time to look into this.

Let me know if I can be of any help

[folsze]

I managed to fix it.

It works after I split the statement containing multiple update statement into multiple single UPDATE statements. And await each statement to finish first.

I don't see any reason to investigate this further but maybe it would be good to tell people through the documentation that there might be issues if they pack multiple statements into one statement and just run them all in one statement. But maybe I just did things wrong 🤷

[jepiqueau]

@folsze may be you should make a PR to the API.md documentation to warn on this.

[jepiqueau]

@folsze send me the API.md modified on jepi.queau@free.fr and i will replace it in the next releasethanks for this

[folsze]

Didn't figure out right away how to do it right away and then I was busy but I now created a PR:
#394