System.Data.SQLite

For my application, there are some cases where it would be useful (I think) to be able to use the incremental blob I/O in SQLite.  Are there any plans to extend SQLiteDataReader.GetBytes so that it will do this?

As a side note, I noticed when looking to see if it already did this that the GetBytes implementation assumes a 32bit architecture (it uses IntPtr.ToInt32 to do arithmetic on a pointer value returned from SQLite), which will probably bite someone eventually.  I realize that System.Data.SQLite only ships with the 32bit native SQLite right now, so that's not an issue yet, unless someone tries to build their own 64bit version.

SQLite.NET supports 64-bit architectures. In fact, we use it like that and the Win64 binaries are part of the standard distribution. What you pointed out is a critical bug that has to be fixed. I'll submit a special report for just that.

Now on the topic of incremental blob I/O. I've played with that idea and you can get the result at http://emclient.com/temp/sqlite_blob.diff.txt. It's totally untested, the interface needs to be refined (probably move the stream creation to DataReader, if possible) and tests have to be written for it. I'd be happy to hear any feedback about it.

Unfortunately I believe the ToInt32() code is correct -- and that while SQLite itself supports large files over 2gb, you can't actually put more than 2gb of data into a single column.  I'll do some research and confirm.

 

It's not correct, because you do pointer arithmetics with it... see http://blogs.msdn.com/jaredpar/archive/2008/10/28/is-intptr-long-truncating.aspx. Fix is here: http://www.emclient.com/temp/sqlite_ptrsize.diff.txt. SQLite indeed has size limit on blobs which is no more than 2Gb.

Ok now I see what you're referring to.

 

I've tested SQLite with BLOB fields of 700 MB. In my experience - the sqlite DLL code has a major flaw that causes it to consume hundreds of megabytes of RAM when you try to use the zeroblob API to prepare the BLOB for writing. I know this to be true since I'm working on a diff-merge application for sqlite and one of the stickiest things I needed to tackle is to handle BLOB fields correctly. So maybe SQLite can theoretically handle 2GB BLOB fields but in its current state it is not practical to push files that are larger than 100-200 MB as BLOB fields. My application was tuned to issue a warning if the user tries to do this in order to prepare him/her for the major disk thrashing that comes next... Liron