[Mono-dev] SQLite & Cascading Deletions

[Matthias Schäfer]

Dear List,
I just want to know whether cascading deletions are supported by C# and 
Mono.Data.Sqlite. I tried a few thing but I didn't get it started. Ok, 
here are some informations:

The simple DB was set up by this schema:

CREATE TABLE Person (Email TEXT PRIMARY KEY NOT NULL, FirstName TEXT NOT 
NULL, LastName TEXT NOT NULL);
CREATE TABLE Lecture (ID INTEGER PRIMARY KEY NOT NULL, Lecturer TEXT NOT 
NULL REFERENCES Person(Email) ON DELETE CASCADE ON UPDATE CASCADE);

To test the schema, i used the sqlite-commandline-tool to insert a few 
persons and lectures:

PRAGMA foreign_keys=ON;
INSERT INTO Person VALUES ('j.wayne at domain.com', 'John', 'Wayne');
INSERT INTO Lecture (Lecturer) VALUES ('j.wayne at domain.com');

Everything went well and when I delete the Person-entry, the 
Lecture-entry becomes deleted too. So cascading deletes are obviously 
supported by my SQLite.

Then I created the DataContex by using sqlmetal. Everything works fine 
so far and I can access the DB and manipulate the data as desired. But 
when I delete an entry from the Persons-table, the Lecture-entries which 
point to the Person remain in the table. So cascading deletions don't 
work. Here's my C#-SQLite setup:

SqliteConnection dbconn = new 
SqliteConnection("DbLinqProvider=Sqlite;Data Source=TestDB.db3;");
TestDB db = new TestDB(dbconn); // this DataContext-class is generated 
by sqlmetal
db.Log = Console.Out;
db.ExecuteCommand("PRAGMA foreign_keys=ON");

I also looked into the generated DataContext-class and figured out, that 
sqlmetal didn't include the cascading delete-property into the 
association-entries. So I added the DeleteRule="CASCADE" to each 
association, but sadly without any effect.

What am I doing wrong? Do I have to implement the cascading deletion by 
myself?

Thanks for help,
Matthias