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

TE yurumi at gmx.de
Wed Aug 24 14:11:40 UTC 2016


Oh, maximum sorry! I missed your proposed changes, which btw result in
the following (non?-)error:

Error: not an error Unable to execute multiple statements at a time


Cheers,
Thomas


On 24.08.2016 16:03, David Greaves wrote:
> 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
>>
>
> _______________________________________________
> SailfishOS.org Devel mailing list
> To unsubscribe, please send a mail to
> devel-unsubscribe at lists.sailfishos.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.sailfishos.org/pipermail/devel/attachments/20160824/c7ca7698/attachment.html>


More information about the Devel mailing list