foreign key ON DELETE CASCADE

[MonicaExplan]

Hi

To Reproduce
I have sqllite plugin 3.5.1 installed.

I upgrade to capacitor 4 and I upgrade sqlite plugin to 4.8.0 but an error occurs. So, I try other version of sqlite plugin:

• 4.6.0: the same error
• 4.0.1: the same error
• 3.7.0: the same error
• 3.5.2: the same error

Then, I open my previous project in capacitor 3, upgrade the sqlite plugin from 3.5.1 to 3.5.2: the same error

Describe the bug
The error happens both Android and iOS

The problem seems to occur when I do a delete on the linked table (with foreign key on the main tabel with ON DELETE CASCADE).

First, I create my DB schema by json and ImportFromJson API (below the two table and constraint involved). The import is ok, no errors.
Then, I load data keeping them from Rest serice on the server backend, my app load data into device DB using this plugin executeSet

--- My tables

1. the main table

   {
   "name": "tickets",
   "pattern": [
   { "column": "id", "value": "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" },
   { "column": "syncId", "value": "TEXT UNIQUE" },
   { "column": "num", "value": "TEXT UNIQUE" },
   { "column": "subject", "value": "TEXT NOT NULL" },
   { "column": "last_modified", "value":"INTEGER DEFAULT (strftime('%s', 'now'))" },
   { "column": "sql_deleted", "value": "BOOLEAN DEFAULT 0 CHECK (sql_deleted IN (0, 1))" }
   ]
   }

2. this is my linked table with foreignkey
   {
   "name": "ticketsAssets",
   "pattern": [
   { "column": "id", "value": "INTEGER PRIMARY KEY NOT NULL" },
   { "column": "ticketId", "value": "TEXT" },
   { "column": "ticketSyncId", "value": "TEXT" },
   { "column": "last_modified", "value": "INTEGER DEFAULT (strftime('%s', 'now'))" },
   { "column": "sql_deleted", "value": "BOOLEAN DEFAULT 0 CHECK (sql_deleted IN (0, 1))" },
   { "foreignkey": "ticketId", "value": "REFERENCES tickets(id) ON DELETE CASCADE" }
   ]
   }

----- The error in Android Studio log
2024-04-16 16:30:36.127 9131-9131 Capacitor/Plugin it.explan.plangei.consumer V To native (Capacitor plugin): callbackId: 85579387, pluginId: CapacitorSQLite, methodName: executeSet

2024-04-16 16:30:36.131 9131-9131 Capacitor it.explan.plangei.consumer V callback: 85579387, pluginId: CapacitorSQLite, methodName: executeSet, methodData: {"database":"consumer_plangei_","set":[ {"statement":"DELETE FROM ticketsAssets WHERE ticketSyncId=?;","values":["9400E607EAF78F4FC1258B01004C8624"]}],"transaction":false}

2024-04-16 16:30:36.132 9131-9287 Capacitor/Plugin it.explan.plangei.consumer W getArray calls without a default value will return null in Capacitor 5 instead of an empty array to match iOS behavior

2024-04-16 16:30:36.136 9131-9287 com.getcap...RetHandler it.explan.plangei.consumer V *** ERROR ExecuteSet: incomplete input: , while compiling: UPDATE tickets SET sql_deleted = 1 WHERE

2024-04-16 16:30:36.136 9131-9287 Capacitor en.explan.plangei.consumer D Sending plugin error: {"save":false,"callbackId":"85579379","pluginId":"CapacitorSQLite"," methodName":"executeSet","success":false,"error":{"message":"ExecuteSet: incomplete input: , while compiling: UPDATE tickets SET sql_deleted = 1 WHERE "}}

--- The xCode log error message
[error] - {"message":"ExecuteSet: getReferences(message: "Indices of FOREIGN KEY and ON DELETE not equal")","errorMessage":"ExecuteSet: getReferences(message: "Indices of FOREIGN KEY and ON DELETE not equal")"}

My test
since I don't have this instruction in my code: UPDATE tickets SET sql_deleted = 1 WHERE
I think this instructions is due to the plugin. I don't Know who launch this UPDATE instructions, and why?

If from the "ticketsAssets" table schema I remove this:
{ "foreignkey": "ticketId", "value": "REFERENCES tickets(id) ON DELETE CASCADE" }

the error does not occurs!

thanks a lot
Monica

[jepiqueau]

@MonicaExplan You are using far to old releases of the plugin. move to the latest release with Capacitor 5