id

71

publisher

goffi@goffi.org/d08fc2c8-30c5-4081-bff7-2f0f733436e8

title
Sqlite foreign keys "ON DELETE CASCADE" do not work
reporter
Adrien
created
24/03/2014, 12:25
updated
24/03/2014, 14:54
labels
Backend
type
bug
status
closed
priority
normal
milestone
0.7
severity
normal
body

The support of foreign keys do not seem be effective, despite "PRAGMA foreign_key = 1" is set after the initialization. Some ideas: - http://p-s.co.nz/wordpress/foreign-key-support-in-sqlite-and-python/ - if twisted adbapi opens several threads, the pragma is set only for one of them - ... ?

comments_uri
xmpp:pubsub.goffi.org?;node=urn%3Axmpp%3Amicroblog%3A0%3Acomments%2Forg.salut-a-toi.tickets%3A0_71

Click to reduce…

The reason seems to be twisted running the queries in different threads. One solution is to launch your query as an interaction and preceed it with the pragma setting. For example, replace: d = self.dbpool.runQuery("DELETE FROM profiles WHERE name = ?", (name, )) with: def delete(txn): txn.execute("PRAGMA foreign_keys = ON") txn.execute("DELETE FROM profiles WHERE name = ?", (name,)) return None d = self.dbpool.runInteraction(delete)

Click to expand… Click to reduce…

You are not logged. You need to log in to comment.