System.Data.SQLite

Hello everybody,


due to the limit of 32MB storage memory in the Device Emulator I was forced to store my apps main DB file  on the storage card (SC). This worked with SQLite 1.0.39 but not with later versions: I was able to read form the DB but not to write, so I had to provide two versions of my app: one for the emulator using V1.0.39 and one release version with the latest & greatest. This was last year during preparition of a trade fair, so I had no time to spare whatsoever.

Since I could afford to spare some time on this issue, and since I want to get rid of a special emulator version of my app (that contains all the bugs already fixed) I tried to get my app working with DB files on a SC with the latest release (1.0.63) but the problem remained: Reading is possible, but writing is not.

I want to share the results of my research doing this:

1.    GetFileAttributeW sets wrong LastError code for files stored on SC
Normally, GetFileAttributeW returns INVALID_FILE_ATTRIBUTES if a file is not found and GetLastError() is supposed to return ERROR_FILE_NOT_FOUND. This works for files that reside in the storage memory of a device.
I wrote some test code that shows, that no last error is set if the file resides on the SC. So with a small fix in the winDelete() functions I could get rid of that problem.
Although I posted a fix at 'sqlite.org' nobody seems to have taken any notice of it.
@Robert Simpson: maybe you have more luck than I do:
    http://www.sqlite.org/cvstrac/tktview?tn=3644

2.    Locking sometimes fails
The second problem that remaines / remained is a very strange issue that has to do with locking.
Looking a bit deeper into the code (like 3-4 days work), I found out, that winceLockFile screws up every now and then:
One needs to look a bit deeper into the code to understand what's going on there, but the very short version of it is, that every now and then, winceLockFile does not do what it is supposed to do.

Adding quite some debugging output to this function, I figured that some times winceLockFile performs a SHARED_LOCK while being requested a PENDING_LOCK. As a result, the values of pFile->local.nReaders is set wrongly and the following lock requests fail due to wrong values.

I cannot explain this problem what so ever, but explicetly disabling optimizations for this function seems to do the trick. It may be a bug in the emulator or some very weird side effect that keeps the ARM emulator from doing this right. From looking at the debug output, I haven't found a clue what really went wrong on this.

I hope this helps other from getting too frustrated doing DB files on the storage card on an emulator

Tilman

The short-term solution may be to set the last error value -- but in the long term Microsoft should really fix this bug.

I'll see what I can do.

The biggest problem with proposing this change to the pending byte is I can't give a clear and concise reason why this change affects anything.  In theory, changing it to const int should really do nothing compared to when it was a static int.

I doubt seriously I'll be able to get Dr. Hipp to make this change unless I can give him a compelling argument as to why it works.  He's also not keen on removing functionality from the codebase -- this change would require changes to the test suite -- and I suspect that the pending byte was made changeable because of a request from someone else ... who'd then complain about that functionality being removed.

In theory, there is no difference between theory and practice. In practice, theory and practice don't even relate.

Since the problem showed up only with the emulator, and since it showed up very rarely and in a very nondeterministic fashion, and since it didn't show up when disabling the code optimization for at least the "winceLockFile" function, it looks a lot like a very weird Device Emulator bug. I agree, that the reasoning for the change is rather weak.

Unfortunately, I most probably won't be able to spend more time to find and create a test case scenario that demonstrates the problem on the Device Emulator, since I already spent 3-4 days figuring out the problem in the first place. But with the Device Emulator being closed source, I'm stuck to the code that I have a chance to change in order to work around the problem.

But this would be exactly my reasoning: Why not add an #ifndef to disable the test code that can change the value of PENDING_BYTE. It could be enabled by default, but you could add another #define and we're done.
We would not ask to remove the functionality, instead we'd ask to make it optional.

On a desktop computer there is not too much need to do optimization anymore, but my expericence with Win Mobile devices speak a different language: Less code instructions => faster code. Replacing PENDING_BYTE by a const value will reduce memory access on the device. I was also thinking about whether we need locking on a WinCE based app at all, since usually there is only one thread accessing the DB, but that is another topic.
(There is quite a number of calls to the locking / unlocking functions)