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

Matthias Fehring buschmann at huessenbergnetz.de
Wed Aug 24 15:27:11 UTC 2016


Hello Thomas,

I never used LocalStorage directly from QML/JS. I access the database from the 
C++ part. Your code looks like if you are using database transaction/commit.

But in C++ for me it did not work to set the PRAGMA if it is part of a 
database transaction. I have to execute it on its own, without transaction/
commit.

Best greetings
Matthias

Am Mittwoch, 24. August 2016, 14:45:13 CEST schrieb 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


-- 
Das Gesetz hat zum Schneckengang verdorben, was Adlerflug geworden wäre.
(Friedrich Schiller - Die Räuber)

Und der Buschfunk spielt gerade "The Legend of Pai Mei" von "David Carradine 
and Uma Thurma".

www.buschmann23.de
GPG-Key: 0x614C3258
GPG Fingerprint: B770 E0D0 69CF BFC1 5FE1 D78E 3A70 A936 614C 3258
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: This is a digitally signed message part.
URL: <https://lists.sailfishos.org/pipermail/devel/attachments/20160824/cad775d5/attachment.pgp>


More information about the Devel mailing list