When I was implementing my ADO.NET 2 code, I came across some serious issues regarding calling conventions.
As you are probably aware, sqlite3's API calls are all CDecl by default. However, .NET by default expects all DllImported functions to be declared StdCall. The .NET Framework allows you to change the calling conventions in the attribute, but the Compact Framework does not support the CDecl enum type and expects all imported functions to be in StdCall.
Furthermore, all callbacks are also expected to be StdCall, further complicating the use of delegates to perform callback operations.
In the source code I posted on my site, I wrote StdCall wrappers for almost all the sqlite3_xxx functions in order to maintain the highest level of compatibility. They are all declared using the cdecl(dllexport) attribute, so it isn't necessary to modify the default .DEF file that comes with the standard sqlite3 source distribution. Have a look at my SQLite3.cs file for how I imported them. Basically I just added the word "_interop" to the end of the function name.
Ya, the existing code does work as-is, but only because .NET is automatically detecting the stack mismatch and realigning it on the fly. This is definitely not the way to continue and it's probably slowing things down some as well.
Here's a little bit more information on it:
How to setup managed debugging assistants:
Seems like it might be worth pushing for some form of calling convention mechanism to be worked into the SQLite codebase.
Something along the lines of (in sqlite3.h)...
int SQLITE_API sqlite3_xxx (...);
Then, in a Windows DLL build, you could define SQLITE_API as __stdcall.
I thought about that, but then it would break all existing binaries written for the DLL when you dropped a StdCall version into your System32 directory. Not only that, but all the routines that accept callback function pointers would also have to change to include the stdcall definition, further complicating the whole thing.
The interop wrappers I wrote do a couple things specifically for .NET:
- Provide stdcall wrappers for the existing API's
- Provide callback wrappers so sqlite3 can callback into .NET functions.
- Added length output parameters to the UTF-8 versions of all the sqlite3 interop functions to make the conversion from 8-bit to unicode strings easier. (This is in my latest build which I haven't posted yet).
- The modified sqlite3.dll can still be dropped into the system32 directory without breaking anyone else.
One of the ways in which my build differs greatly from the one here is that I did away with the idea of using a MarshalStr class thingie which allocs and frees memory. I much prefer to use as few interop calls as possible, and if I can get away with only using DllImport on the sqlite3 DLL, then that's the preferred option.
Of course the problem with that is many of the sqlite3_xxx functions return char *'s, which in .NET kindof sucks because you don't know what their length is going to be until you get the pointer back. I originally tied into lstrlenA in kernel32.dll, but I hate using it, so I decided to make a sqlite3_strlen() interop function -- since I was making interop functions anyway, I might as well make one more.
So the sequence of getting a char * to a string then became (M = Managed ,U = Unmanaged):
M <-> U : sqlite3_xxx() returning an IntPtr
M <-> U : sqlite3_strlen() to get the length of the IntPtr
M : Marshal.Copy(IntPtr to byte)
M : UTF8.ToString(byte)
Last night I decided to take those interop functions and modify them a little more so they automatically returned the length of the char * as an output parameter, which eliminates the 2nd managed to unmanaged interop call in the above sequence. Probably not a huge performance gain, but when you're potentially dealing with the Compact Framework, anything you can do to eliminate interop calls is a boost in some form.
BTW: I'm not going to make any effort to make this code any more Compact Framework friendly than it already ias at this time -- there are way too many missing pieces of the CF 2.0 to even make it worthwhile. There are literally hundreds of methods and properties that the beta documentation says are present in CF 2.0 that are simply missing from the current beta.
While not breaking anyone else by dropping your modified version of sqlite3.dll into system32 seems like a decent goal at first, I'm not sure it really is. If someone else drops an updated version of sqlite3.dll after yours, then your provider is broken. Delivering and relying on any kind of "non-standard" sqlite3.dll as a shared DLL is bad news. Better to keep any non-standard sqlite3.dll as a private DLL. Better yet, if you must provide some unmanaged interop layer, then put that in a separate DLL.
I wholeheartedly agree. Unfortunately nobody at sqlite.org has cared to respond to my plea for a method of retrieving base column and table info for a given select clause (ala real_column_names pragma)
Maybe I could just break out the parser pieces of sqlite and throw those in an interop DLL ...
Perhaps you can submit your pragma patch to the sqlite.org bug tracker. I think there is also some kind of public domain release form which you must submit in order to contribute code changes, although I can't find mention of it on the site anymore. You would need ask drh.
I'm definitely going to keep pushing for the pragma. The idea of parsing the SQL myself is unappealing and very error-prone. The only way to do it properly is to have SQLite's parser do it.
In the meantime, I'm continuing to tune the ADO.NET 2.0 provider I wrote. The new 2.0 base classes are all but undocumented, so it's been somewhat of a nightmare.
On the updside, tracing the code has resulted in me fixing bugs in the SQLiteCommandBuilder I wrote, which in turn boosted the InsertMany test's performance significantly.
My 10,000 row insert test went from 1500ms execution time to 500ms.