Bring back the SQLitePCLRaw.provider.sqlite3 packages

[bricelam]

Using the system version of SQLite is a bit ugly in 2.x:

class NativeLibraryAdapter : IGetFunctionPointer
{
    readonly IntPtr _library;

    public NativeLibraryAdapter(string name)
        => _library = NativeLibrary.Load(name);

    public IntPtr GetFunctionPointer(string name)
        => NativeLibrary.TryGetExport(_library, name, out var address)
            ? address
            : IntPtr.Zero;
}

SQLite3Provider_dynamic_cdecl
    .Setup("sqlite3", new NativeLibraryAdapter("sqlite3"));
SQLitePCL.raw.SetProvider(new SQLite3Provider_dynamic_cdecl());

It gets even harder on .NET Framework where NativeLibrary isn't available.

Newer versions of PROJ--used by the SpatiaLite extension--are only compatible with the system-installed version of SQLite. (see dotnet/efcore#16667) So having an easier way to use the system version would be nice.

[ericsink]

This wouldn't be hard to do, and probably makes sense.

At the beginning of my dev cycle for 2.0, one of my goals was to reduce the number of packages and providers. The sheer number of them causes a lot of confusion. I made some progress, but in general, this goal seems to be unreachable. There are so many edge cases. SQLitePCLRaw is almost entirely about edge cases.

[ericsink]

v2.0.3 has been pushed to nuget. It includes SQLitePCLRaw.provider.sqlite3.nupkg. Please let me know of any problems found with it.

[bricelam]

Worth adding a bundle?

[ericsink]

:-)

If you think it's worth the trouble, I can do it. I default to no on such things because it seems like every additional package ID causes confusion.

[Gusi]

Hi,
I'm, stuck with a problem using this provider to load a custom sqlite3.dll. I can initialize the provider manually, but when trying to use a Microsoft.Data.Sqlite.SqliteConnection, the SqliteConnection static constructor forces a call to the SQLitePCL.Batteries_V2.Init method, and I can not find the SQLitePCL.batteries_v2.dll in the nuget package so the call fails,

Is this a bug or am I doing something wrong?

[ericsink]

Try this:

https://www.nuget.org/packages/Microsoft.Data.Sqlite.Core/

[Gusi]

Try this:

https://www.nuget.org/packages/Microsoft.Data.Sqlite.Core/

I think it won't work. The Sqlite.Core package still calls batteries_v2

https://github.com/aspnet/Microsoft.Data.Sqlite/blob/master/src/Microsoft.Data.Sqlite.Core/Utilities/BundleInitializer.cs

The difference with normal Sqlite package is that it doesn't include the bundle, but the initialization call is still there.

[ericsink]

Oh my, you are correct -- sorry about that.

So @bricelam , yeah, I guess maybe it is worth adding a bundle...

[Gusi]

Thanks for your help!

I patched it by now creating a fake SQLitePCL.batteries_v2.dll library with and empty Init method so the Microsoft.Data.Sqlite doesn't complain. It works, but of course this is not a good solution.

[bricelam]

You can manually set (and freeze if necessary) the provider before using SqliteConnection. It only tries to call Batteries_V2–it’s not an error if it can’t.

[bricelam]

But yes, a bundle definitely improves the experience

[Gusi]

Thanks for the reply.
Indeed I'm setting the provider befores calling SqliteConnection, but haven't tried the Freeze feature. About the call to Batteries_V2 I've not checked if it's inside a try/catch block, so I will test it again.

Regards.

[ericsink]

@bricelam I've added bundle_sqlite3.

I'm not sure if there are any edge cases that matter to you which I might be forgetting about, but the bundle right now is pretty darn simple. It only targets netstandard2.0, and it always just does DllImport of "sqlite3" (as opposed to NativeLibrary.Load or any other dynamic approach).

It has a test app which runs against netcoreapp2.1, netcoreapp3.1, and net461, and on my machine, it passes all my usual tests against all 3 targets, with the 64 bit sqlite3.dll obtained from sqlite.org. I haven't tried anything like using Linux against whatever sqlite package is part of Ubuntu, for example.

None of this is up on nuget yet, but I'll probably be doing a 2.0.4-pre at some point soon for testing purposes.

Feedback welcome.

[bricelam]

Nice, I'll run the Microsoft.Data.Sqlite and EF Core tests on it (like we do for winsqlite3 and e_sqlcipher) when the prerelease is available, so we can get coverage on Ubuntu and macOS.

[bricelam]

I wonder if this package would benefit Unity on iOS. Using the system version of SQLite instead of statically linking your own sounds a lot simpler.

[ericsink]

OTOH, if this package helped the Unity on iOS case, then it seems that bundle_green would too.

[Gusi]

I can't see this package listed in the nuget package directory, but it is commented in the 2.0.4 release notes. Is it still in beta? Should I compile it myself or will it be available soon? Thx!

[ericsink]

AFAICT, the packages are both there:

https://www.nuget.org/packages/SQLitePCLRaw.bundle_sqlite3

https://www.nuget.org/packages/SQLitePCLRaw.provider.sqlite3

[Gusi]

My fault, I was doing the wrong search. Thanks, everything working fine now!

[angelrishi]

Sir, built a custom SQlite. It is now lib/x86/libsqliteX.so in the Android.
The android is using SQLitePCLRaw.bundle_green. So, the custom built sqlite is completely ignored by Android.
Should we be using SQLitePCL.raw.SetProvider(new SQLite3Provider_dynamic_cdecl());
Would this work for both iOS and Android?

[ericsink]

@angelrishi This issue is closed. You need to open a new issue and post complete information about what trouble you are having.