search

capacitor-community / sqlite

Community plugin for native & electron SQLite databases
MIT License
426 stars 104 forks source link

foreign key ON DELETE CASCADE #540

Open MonicaExplan opened 1 month ago

MonicaExplan commented 1 month ago

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:

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":"consumerplangei","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 commented 3 weeks ago

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