[SailfishDevel] SQLite: ON DELETE CASCADE with Foreign Keys not working
TE
yurumi at gmx.de
Wed Aug 24 13:56:00 UTC 2016
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
More information about the Devel
mailing list