IronPython.SQLite not working on macOS/Linux

[BCSharp]

While testing some PyPI packages on a recent ipy 3.4 I have discovered that the sqlite3 package bundled with IronPython is not working correctly. After looking into the source code and some debugging I've discovered two major issues ans a few smaller ones.

Major issue #1 is that there is only one "platform compatibility" layer: os_win_c.cs, which, according to the embedded docs is Windows-specific. Nevertheless, it is being unconditionally compiled on all platforms. The compilation succeeds, but the code compatibility alternatives are limited to Windows 95, Windows CE, Windows RT, Windows Phone, Silverlight, and Windows NT (maybe I have missed some other Windows variant...). When run on macOS/Linux, the compatibility methods run out of all platform detection checks and return an error. I can't see how it ever was working on macOS/Linus in any serious way. When I hacked the code to treat macOS as WinNT, it went a little further, but still hit another snag.

Major issue #2: the code uses .NET API that is not supported on macOS. Warnings about that have been recently disabled (#1330). This pertains file locking, which is essential functionality, effectively making sqlite3 unable to open any file. An in-memory database should work though.

The warning itself mentions only problems on macOS, so presumably Linux should be OK (after some hacks). I haven't tested it on Linux yet, but if I find no more snags, I will submit the changes to get at least this platform supported. There is also a possibility to use Mono.Unix compatibility layer on macOS to get partial file locking, but I am not sure if this is fully implemented; the Mono documentation is lacking.

On a wider note, the SQLite implementation that IronPython uses looks very old, unmaintained, supporting obsolete platforms, and not supporting the platforms that IronPython and .NET currently target. So I wonder whether instead of spending time trying to get this code to work, it wouldn't be better to replace it with a modern implementation that is actively maintained (like SQLite.raw).

[slozier]

On a wider note, the SQLite implementation that IronPython uses looks very old, unmaintained, supporting obsolete platforms, and not supporting the platforms that IronPython and .NET currently target. So I wonder whether instead of spending time trying to get this code to work, it wouldn't be better to replace it with a modern implementation that is actively maintained (like SQLite.raw).

It's probably a good idea in the long run. (I've had similar thoughts with regards to compression modules). I sort of dislike having the extra dependency on the core, but maybe we could break off the SQLite module into its own optional NuGet package if it's an issue...

[BCSharp]

I sort of dislike having the extra dependency on the core, but maybe we could break off the SQLite module into its own optional NuGet package

Another possibility is the DLR approach: reference the SQLite project at the source code level (as a git submodule or subtree) and compile it with the rest to the codebase into a standalone assembly as it is now.