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

[David Greaves]

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
>