[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