[SailfishDevel] SQLite: ON DELETE CASCADE with Foreign Keys not working

David Greaves david.greaves at jolla.com
Wed Aug 24 14:03:32 UTC 2016


Did you try?

I combined both the
PRAGMA foreign_keys = ON;
and
PRAGMA foreign_keys;

into one transaction.

David

On 24/08/16 14:56, TE wrote:
> Sorry for being ambiguous. It returns: {"foreign_keys":0}
>
>
> On 24.08.2016 15:53, David Greaves wrote:
>> What does
>>
>>  function queryForeignKeysEnabled()
>>  {
>>      instance().transaction(function(tx) {
>>          var rs = tx.executeSql("PRAGMA foreign_keys = ON; PRAGMA
>> foreign_keys;");
>>          console.log(JSON.stringify(rs.rows.item(0)))
>>      });
>>  }
>>
>> return?
>>
>> David
>>
>> On 24/08/16 13:45, TE wrote:
>>> Hello Matthias,
>>>
>>> thanks for the quick reply! I access the database from the QML/JS
>>> domain. To open a connection I use the following function:
>>>
>>> function instance()
>>> {
>>>     var db = Storage.LocalStorage.openDatabaseSync("Db", "", "Nodes",
>>> 5000000);  /* DB Size: 5MB */
>>>     db.transaction(function(tx) {
>>>         tx.executeSql("PRAGMA foreign_keys = ON;");
>>>     });
>>>     return db
>>> }
>>>
>>> The following test function
>>>
>>> function queryForeignKeysEnabled()
>>> {
>>>     instance().transaction(function(tx) {
>>>         var rs = tx.executeSql("PRAGMA foreign_keys;");
>>>         console.log(JSON.stringify(rs.rows.item(0)))
>>>     });
>>> }
>>>
>>> returns
>>>
>>> {"foreign_keys":0}.
>>>
>>> Can you map this implementation to your C++ code and tell the
>>> difference?
>>>
>>> Cheers,
>>> Thomas
>>>
>>>
>>>
>>>
>>> On 24.08.2016 13:54, Matthias Fehring wrote:
>>>> Hello Thomas,
>>>>
>>>> for me foreign key support is working. Directly after establishing the
>>>> connection to the SQLite database (QSqlDatabase::open()) I use
>>>> QSqlQuery to
>>>> execute "PRAGMA foreign_keys = ON".
>>>>
>>>> I think you have to do it every time you open a closed database, so I
>>>> implemented it in my database connection methods that it is executed
>>>> if the
>>>> database connection was closed before.
>>>>
>>>> Best greetings
>>>> Matthias
>>>>
>>>>
>>>> Am Mittwoch, 24. August 2016, 12:39:04 CEST schrieb TE:
>>>>> Hello fellow devs,
>>>>>
>>>>> I have two SQLite tables. The second table references a key from the
>>>>> first table as foreign key. Now when I delete a row in the first
>>>>> table I
>>>>> want all rows from the second table referencing the deleted key to be
>>>>> deleted as well. For this the action ON DELETE CASCADE seems to be
>>>>> appropriate. Unfortunately this doesn't work and the corresponding
>>>>> rows
>>>>> in the second table are *not* deleted.
>>>>>
>>>>> Here are the table definitions:
>>>>>
>>>>> First table:
>>>>> CREATE TABLE IF NOT EXISTS NodeMetaData (id INTEGER PRIMARY KEY,
>>>>> parentId INTEGER);
>>>>>
>>>>> Second table:
>>>>> CREATE TABLE IF NOT EXISTS NodeConcreteData (refId INTEGER NOT NULL,
>>>>> FOREIGN KEY(refId) REFERENCES NodeMetaData(id) ON DELETE CASCADE);
>>>>>
>>>>> In the sqlite docs, it is mentioned that foreign key support has to be
>>>>> acitvated. So I tried tx.executeSql("PRAGMA foreign_keys = ON;"). But
>>>>> the query tx.executeSql("PRAGMA foreign_keys;") returns 0.
>>>>>
>>>>> When I recreate the scenario in an interactive SQLite session, the
>>>>> above
>>>>> works (PRAGMA foreign_keys = ON has to be executed).
>>>>>
>>>>> Any ideas?
>>>>> Is the SFOS Qt lib compiled with foreign key support?
>>>>> What am I missing?
>>>>>
>>>>> Many thanks in advance!
>>>>> Thomas
>>>>> _______________________________________________
>>>>> SailfishOS.org Devel mailing list
>>>>> To unsubscribe, please send a mail to
>>>>> devel-unsubscribe at lists.sailfishos.org
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> SailfishOS.org Devel mailing list
>>>> To unsubscribe, please send a mail to
>>>> devel-unsubscribe at lists.sailfishos.org
>>>
>>>
>>>
>>> _______________________________________________
>>> SailfishOS.org Devel mailing list
>>> To unsubscribe, please send a mail to
>>> devel-unsubscribe at lists.sailfishos.org
>>>
>>
>> _______________________________________________
>> SailfishOS.org Devel mailing list
>> To unsubscribe, please send a mail to
>> devel-unsubscribe at lists.sailfishos.org
>
> _______________________________________________
> SailfishOS.org Devel mailing list
> To unsubscribe, please send a mail to devel-unsubscribe at lists.sailfishos.org
>



More information about the Devel mailing list