New bundle like SQLitePCLRaw.bundle_green but use native sqlite library on Linux

[d79ima]

@ericsink you've done a really good job maintaining various SQLitePCLRaw bundles for the community. Thanks!

Would it be possible and not too much trouble for you to add another SQLitePCLRaw.bundle_ package which uses e_sqlite provider for all platforms except for Linux where it would use the native system sqlite provider instead. So, very similar to how you did the SQLitePCLRaw.bundle_green where it uses the native system sqlite provider on iOS.

Reason for my request is that I've recently been plagued with the incompatibility between the default sqlite provider used by Microsoft.EntityFramework.Core.Sqlite package and the libspatialite native library on Linux which is depended upon by Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite. The libspatialite apparently links in PROJ library which links in the native system sqlite library on linux. This results in a segmentation fault in the client app that is pulling both of these dependencies in. The issue is detailed here: dotnet/efcore#16667

My app is targeting Windows, Linux and Android and the current workaround suggested by Microsoft of explicitly using SQLitePCLRaw.bundle_sqlite3 breaks on Android as you detailed in your post: https://ericsink.com/entries/sqlite_android_n.html

I think if we had a new SQLitePCLRaw.bundle package such as the one i am proposing, it would help the community get around the spatialite segmentation fault issue in a fairly elegant way and still target all popular platforms in an easy way.

Or perhaps you could suggest a simpler way to achieve this?

[ericsink]

Short answer: I won't rule it out, but I am not inclined to add another bundle.

Longer answer:

My bundle packages are one of the leakiest abstractions ever. They have the effect of telling developers they do not need to think about the various issues involved of having multiple choices of native SQLite library, but those developers often DO need to think about such things.

The green package, in particular, is something I consider a bug. I wish I had never done it. Or maybe it wouldn't be so bad if I had given it a scarier name. But lots of people seem to think it's the best bundle to choose as a default, and I would like to have avoided that.

So as you can imagine, I am not eager to add another package that plays a similar role to bundle_green. :-)

That said, I said I won't "rule it out" because I need to consider the possibility that a bundle specifically for libspatialite use cases might be helpful. Like maybe if it had "spatialite" in the name, people would only use it for those use cases.

@bricelam -- Ignoring the issue of bundle packages, in terms of the lower level technical details, what is the right solution here? I think we've encountered this issue (or similar) before. Is the core problem here that libspatialite on Linux ends up using the system sqlite so we just need a way to deal with that?

[ericsink]

Well, bundle_sqlite3 is also unusable on Windows.

I think of bundle_sqlite3 as "use the library named sqlite3, which probably means the system sqlite, whatever that means". I think it would be a mistake to change the definition of that bundle.

[d79ima]

yeah that's a good point. On Windows it is painful as well, have to ship sqlite3.dll with application.

I guess what I am struggling with is a sqlite bundle that can effectively target all popular platforms. Which would have been the e_sqlite3 bundle if it wasn't for this libspatialite mismatch.

[bricelam]

The SpatiaLite issues are point-in-time. The version we took a dependency on (4.3.0a) has become a bit stale since its release in 2015. Meanwhile, its dependencies continued to evolve, and PROJ v6 finally broke something. Specifically, it took a dependency directly on SQLite which is something SQLite extensions should avoid doing if they want to be loaded by different versions of SQLite.

Luckily, activity on SpatiaLite has picked back up, and a solution has been found. At this point, we're just waiting for the next release of SpatiaLite, then we can update EF Core's dependency.

Having said all that, I still think there may be long-term value in having a bundle that uses the system SQLite library whenever possible--whatever that may be. (e.g. winsqlite3 on Windows, libsqlite3 on macOS and Linux) The reason being is that this version is supported (to some extent) by the OS. Microsoft will update it through Windows Update for security vulnerabilities, Canonical will do the same for Ubuntu (including in official Docker images), etc. I think this model will appeal a lot more to some people rather than having to manually update the nuget package and re-deploy.

[d79ima]

@bricelam thanks for the update on SpatiaLite. looks like there is some light at the end of that tunnel :)

[d79ima]

best workaround so far that i could come up is to conditionally reference package sqlite3 when targetting linux-arm.
otherwise use the e_sqlite3 package

        <When Condition="'$(RuntimeIdentifier)'=='linux-arm'">
            <ItemGroup>
                <PackageReference Include="SQLitePCLRaw.bundle_sqlite3" Version="2.0.4" />
            </ItemGroup>
        </When>
        <Otherwise>
            <ItemGroup>
                <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.0.4" />
            </ItemGroup>
        </Otherwise>
    </Choose>