System.Data.SQLite

Hi, may I know when is there going to be a new release for System.Data.SQLite with native SQLite 3.6.23.1 embedded?

I downloaded the source code, but the compilation don't seem to work when I put in new source file pulled from latest native sqlite. Any help?

I finally gave up on getting a new version, and have been building my own versions since december. Every part of the project will build with the latest engine code, but it takes some time to figure out. It's a difficult build project to understand -- but you can do it. I have decades of software building experience however so I was able to muddle through.

You would have to list every compile error in detail if you expect any help. Your errors are probably different from mine so I wouldn't know how to fix anyway.

He sort of said volunteers were welcome on the project but he has not answered his email.

 It's not that hard to do yourself. Download the source, then add in the new sqlite3 files for the latest sqlite engine. On one version of the engine I had a couple of compile errors that I fixed by commenting out some code I didn't understand (bad), but in later versions of the engine I didn't have any errors. I only added one property to the connection string to enable foreign keys, and fixed one property that had a bug in it. I verified that it will throw an exception if foreign key contraints are violated.

 If I give you what I have there will be issues. I didn't deal with the visual studio designer at all, but I don't think I broke anything. I didn't deal with vs2010 at all and I don't know what will happen with this. I made up my own version number (1.0.66.1), but didn't rename the dll, and if the dll is released into the wild I think it could create confusion. I am including it with my application install, and so far no problems.

 Hi,

The interop.c in SQLite since version 3.6.18 and latest 3.6.23.1 new vdbeCursor structure no longer contains iKey and pseudoTable property. Paul, I suppose you commented the following code to compile:

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

However, this will cause problems to SQLite logic itself and I've tested with that modification, querying data will have problem.I suppose the effort required to modify interop.c is quite huge that's why until now the author of System.Data.SQLite .NET did not release any new version. I really hope the author can do something to fix the interop.c. It's not written by me, so it will take considerable amount of effort to go through and fix the code for me. I had to stick with 1.0.65.0 (3.6.16).

The reason I stick with 3.6.16 is due to performance improvement although there could be serious bugs compared to older version like 3.6.13. My system uses basic features of SQLite only, so I guess I won't be hitting any of thoes critical bugs =) At www.sqlite.org, they recommended to stick with 3.6.12, 3.6.13 or 3.6.22 or 3.6.23 if not using the latest. If you guys want to get a build for SQLite .NET, just grab the amalgamation source from sqlite.org website:

http://www.sqlite.org/sqlite-amalgamation-3_6_13.zip

Replace those source files inside the SQLite-1.0.65.0-source\SQLite.Interop\src folder to compile. You might encounter compile error when switching to Release mode, go to the test project and remove System.Data.SQLite reference then readd from the project tab to let it reference to the correct new build.

Also, if you look at http://www.sqlite.org/changes.html, there is a huge amount of changes stated at the last point "Countless minor bug fixes". Sometimes in software development, too much changes may not be a good thing. It might introduce other new bugs. This is why I suppose why www.sqlite.org website recommend us to stick with 3.6.12, 3.6.13 or 3.6.22 if not using the latest version.

2009 May 7 (3.6.14)

• Added the optional asynchronous VFS module.
• Enhanced the query optimizer so that virtual tables are able to make use of OR and IN operators in the WHERE clause.
• Speed improvements in the btree and pager layers.
• Added the SQLITE_HAVE_ISNAN compile-time option which will cause the isnan() function from the standard math library to be used instead of SQLite's own home-brew NaN checker.
• Countless minor bug fixes, documentation improvements, new and improved test cases, and code simplifications and cleanups.

Paul, I suppose you commented the following code to compile:

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

Yes, that's what I had to do due to their massive code changes. The sqlite tests pass, and my code works, so it was good enough for me for the time being (I never use triggers). I have the impression this would be very easy to fix. However, the sqlite coders did not document the new structure, they just left the old comments. This might be a good question for the sqlite mailing lists and I think I will ask. So far all I have is:

    else if(pC->pseudoTableReg>0)
    {
       //*prowid = ???
    }

 and after comparing the old and new code for an hour, I have no idea what the rowid should be now. Perhaps you can figure this out.

Yeah, I had the same problem. Spending few hours on that little piece of bits and looking at all the references to that property. I downloaded several versions of native sqlite source to study, still no clue due to a sudden massive changes in one of the revision. Will be nice to see if anyone can help out at the sqlite mailing lists