System.Data.SQLite

Any possibility of having a System.Data.SQLite that uses the newer version of SQLite this year? I have held off using SQLite and this provider since I really want foreign key support. I'm not complaining or trying to rush you, just wondering if you could give any indication?

I really need to get going on the foriegn key support so I put the 3 new 3.6.20 SQLite source files into the SQLite.Interop project. Rebuilt, installed System.Data.SQLite using the existing installer, then copied my new versions of the dlls to the bin folder. Seems to work, and I now seem to have foreign keys working - an error occurs if a constraint is violated. All the System.Data.SQLite project tests pass. The only change to get it to compile is at about line 444 of interop.c. The VdbeCursor structure changed, and I have no idea what I'm breaking. I will of course use the official version when it's ready.

   //else if(pC->pseudoTable)
    //{
    //  *prowid = pC->iKey;
    //}
 

 I also added a 'Foreign Keys' property to ConnectionString in SQLiteConnection class.

Foreign Keys=ON
Foreign Keys=OFF

 which issues

 PRAGMA foreign_keys=ON

 PRAGMA foreign_keys=OFF

 This sounds really good. Does Foreign Key support mean that triggers are no longer needed for enforcing RI?

Can you post the updated bits somewhere along with a sample showing how FKs are enforced?

This sounds really, really good!

 Yes, foreign key triggers don't need to be explicity written anymore. I think they are written internally on the fly inside sqlite engine now. RI is enforced, almost annoyingly so.

My code is working, but his build is somewhat hard to learn without docs, and the install is a separate project. System.Data.SQLite includes sqlite engine using a linker trick. The system.data.sqlite .net project is linked into a win32 dll which is output as System.Data.SQLite.dll. I didn't know you could do that. 

I could put my code someplace, let me think about that. There aren't that many changes as far as I know, and I have not dealt with the designer at all and don't know what changes he will make there. I wish this project was on github and we could all pound on the code and submit changes.

The new foreign key feature is documented pretty accurately on the main sqlite site. You have to be careful that foreign keys must match the primary key of the parent table exactly or else you get a 'foreign key mismatch' error. I have found that cascading deletes are working automatically now if you use "ON DELETE CASCADE".

Hi, 

Is this new version of SQLite.dll released? I added the FOREIGN KEYS=ON; to the connection string. As well as all my foreign key constraints have the ON DELETE CASCADE clause. However, deleting a parent record does not delete the child records. I am not able to get it to work unless I use triggers.

The current version of SQLite.dll I am using is 1.0.65.0

I think I have confused you. My posts refer to my own home grown version.

We must all wait until the official version is out, so keep watching the home page. The connection string setting for foreign keys will be different from mine.