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

[TE]

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

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