Move to SQLitePCL.raw

[bricelam]

To get out of the business of handling the different ways of invoking native code on various platforms, we should consider building on top of Portable Class Library for SQLite or ericsink/SQLitePCL.raw.

[bricelam]

I looked into this a little, and I was surprised at how little the Portable Class Library for SQLite actually supports. Only a handful of the methods we use in NativeMethods are exposed by the API.

[ErikEJ]

Yeah, thats why you should be using Eric Sinks version! See https://github.com/ericsink/SQLitePCL.raw#so-how-is-sqlitepclraw-different-from-what-you-started-with in particular the section: So how is SQLitePCL.raw different from what you started with?

[ericsink]

SQLitePCL.raw covers nearly the entire sqlite3 API.

If there is interest in using SQLitePCL.raw here, I would be happy to help.

E

[bricelam]

@ericsink I just took a peek at SQLitePCL.raw, and I love it. It's just the low-level framework I'm looking for. I think I'll prototype porting Microsoft.Data.SQLite to it this weekend. If all goes well, I'll start the approval process to see if we can take a dependency on it. If it doesn't go well, I'll be sure to follow up with my questions and feedback. 😉

[ericsink]

Very good. I'll be available.

E

[bricelam]

Porting the code was trivial since it mirrors the native API exactly. However, there are a few issues we'll need to dig into before we can take a dependency on it.

1. The package isn't compatible with Project K. It uses MSBuild targets files to add references on some platforms.
2. The architecture needs to be known at build-time. "Any CPU" assemblies aren't supported. This is unlike our current implementation which determines it at runtime and loads the appropriate dll (See NativeLibraryLoader).
3. Two native APIs that we use aren't exposed.
   • sqlite3_db_filename
   • sqlite3_vfs_find

[davidfowl]

I took a look at sqlliteraw as well since I was investigating aspnet/dnx#402 and 1 and 2 was the were troublesome. It also doesn't take other os's into consideration.

[ericsink]

I would like to clear these obstacles. Looks like my first step is to learn
more about your project environment. I'll do some homework and try not to
ask questions until I can ask smart ones. :-)

E
On Jul 5, 2014 6:21 PM, "Brice Lambson" [email protected] wrote:

Porting the code
https://github.com/bricelam/DataCommon.SQLite/compare/raw was trivial
since it mirrors the native API exactly. However, there are a few issues
we'll need to dig into before we can take a dependency on it.

1. The package isn't compatible with Project K. It uses MSBuild
   targets files to add references on some platforms.
2. The architecture needs to be known at build-time. "Any CPU"
   assemblies are not supported. This is unlike our current implementation
   which determines it at runtime and loads the appropriate dll (See
   NativeLibraryLoader
   https://github.com/aspnet/DataCommon.SQLite/blob/dev/src/Microsoft.Data.SQLite/Utilities/NativeLibraryLoader.cs
   ).
3. Two native APIs that we use aren't exposed.
   • sqlite3_db_filename
   • sqlite3_vfs_find

—
Reply to this email directly or view it on GitHub
#21 (comment)
.

[davidfowl]

Hey @ericsink, take a look at the summary in aspnet/dnx#402. We're looking at ways to enable support for native libraries packaged with nuget projects from both a runtime and package time stand point. We're also very interested in how this works when running on os's like mac osx and linux (where it isn't feasible to package native binaries for each variation).

Another concern is being able to change bitness (which is possible in server environments). Today your package copies the correct binary to the output folder based on target platform. This doesn't work well if you need to run on either 32 bit or 64 bit environments as you're baking that knowledge at install time instead of deployment time. You can change the bitness on the iis app pool arbitrarily for e.g.

Our new project system doesn't depend on msbuild (although it doesn't preclude the use of it) so the targets files work fine for certain platforms but for the web platforms we're looking for something more dynamic (basically moving the algorithm you have at build time to runtime). That means there'll be no need to copy anything to the output folder since the runtime will (either by convention or by metadata) be able to figure out which native binary to select.

[ericsink]

OK, forget what I said about not asking dumb questions. :-)

Some stuff here is easy. I can add the missing sqlite3 APIs, for example.

And I think I can set things up to better handle the server-ish use cases (changing bitness, etc).

(In general, the bait-and-switch PCL style tries to defer the choice of the platform assembly until late, so that portable code can simply reference the bait, whereas building an actual executable of some kind needs to refer the proper platform-specific code for its target. What I hear you saying is that this decision, in some cases, needs to be deferred even later, from build time to run time. Makes sense.)

I don't understand the K project system yet, but I want to.

And now for the potentially dumb questions:

What are your target platforms for DataCommon.SQLite? It looks like EF7 wants to be on Windows Phone? Is compatibility with Xamarin desired as well? (I hope so!)

Dealing with native code on mobile platforms is obviously a different can of worms, generally somewhat worse than the issues with Kestrel, for example.

Right now I don't see anything in DataCommon.SQLite that supports Windows Phone. Am I missing it?

As I understand, DllImport/pinvoke simply doesn't work on Windows Phone. So the only way to call native code there is using a C++/CX wrapper. And although C# can be compiled as "AnyCPU", C++/CX cannot.

So: I don't understand how "determining the architecture and loading the appropriate dll at runtime" is going to work for Windows Phone. If I'm wrong, I'd be happy to learn. :-)

And even if it does somehow work on Windows Phone, I am quite certain that this approach will not work for Xamarin.iOS.

Bottom line:

For server-side cases, SQLitePCL.raw needs to learn how to defer some platform-specific decisions later (at runtime).

But for mobile device cases, doesn't K need to learn how to move some platform-specific decisions earlier (at build time)?

E

[davidfowl]

K is a server runtime and doesn't really have anything to do with mobile platforms. The nuget package can do what it does best of those platforms that have special needs. We'll likely have different target framework folder names for the server platforms so building a nuget package becomes easier for these kinds of things.

[bricelam]

Our current hope is to have EF7 run on Desktop (net451), Universal Apps (win81+wpa81), K, and Xamarin.

Windows Phone Silverlight is currently a non-goal, but Windows Phone Application (the WinRT-based framework that is supported in Universal Apps) is a goal. Since P/Invoke is supported there, our interop code was able to be simplified (no mixed-mode assemblies). The packaging and runtime takes care of loading the right native dll for the architecture. (See Extension SDKs.)

I'm not sure if Xamarin supports P/Invoke or not. I haven't looked into it yet.

Like @davidfowl said, K is for server-side scenarios. At the end of the day, it's all just NuGet packages. You should be able to consume the EF7 NuGet packages from a K, Desktop, Windows, Windows Phone (WinRT), or Xamarin project. Already, we're using an MSBuild targets file to copy the native dlls to the target directory for Desktop projects.

[ericsink]

Very helpful. For one thing, I had never realized that pinvoke works on the RT flavor of WP81.

(Xamarin definitely supports pinvoke. But Xamarin.iOS has other limitations because it has to compile everything Ahead Of Time.)

Anyway, I've got some stuff here I can move forward with. Thanks.

E

[ericsink]

I've recently published a new form of the SQLitePCL.raw nupkg.

https://www.nuget.org/packages/SQLitePCL.raw_needy/

The original form of the package, SQLitePCL.raw_basic, is unchanged. This new package is called SQLitePCL.raw_needy. The "needy" form of the package has several differences:

(1) It does not contain any of the actual sqlite DLLs. It "needs" SQLite to be provided in some other way, such as an extension SDK.

(2) It uses pinvoke only, not C++/CX, so it is happy building as AnyCPU. (This also means it doesn't support the silverlight form(s) of Windows Phone. It's PCL profile 111.)

(3) It doesn't need any msbuild tricks to deal with installing the native dlls, so it can simply reside in the lib directory of the nupkg.

(4) This package also includes the recent changes I made to support sqlite3_db_filename(), plus sqlite3__vfs__delete(), which is basically just a call to sqlite3_vfs_find() followed by a call to its xDelete() member.

This packaging should hopefully move us closer to supporting what DataCommon.SQLite needs, but it may not be all the way there. Specifically, I have not yet done anything along the lines of the NativeLibraryLoader.cs code. I considered integrating that code in, but it looks somewhat specific to the K server environment, so I'm wondering if it can stay on that side of the boundary.

(edit: typo)

E

[ericsink]

Update:

If I git clone DataCommon.SQLite, and then build initialize, it builds for me.

Then I made the following changes, which are roughly equivalent to what bricelam did:

1. Add a reference to SQLitePCL.raw_needy.
2. Add these four lines in each file where they are needed:

using NativeMethods = SQLitePCL.raw;
using StatementHandle = SQLitePCL.sqlite3_stmt;
using DatabaseHandle = SQLitePCL.sqlite3;
using Constants = SQLitePCL.raw;
3. Remove these five files from the build:

Interop\Constants.cs
Interop\DatabaseHandle.cs
Interop\NativeMethods.cs
Interop\sqlite3_vfs.cs
Interop\StatementHandle.cs
4. Remove several calls to Handle.IsInvalid
5. Replace the implementation of DeleteDatabase:

public static void DeleteDatabase(string filename)
{
    // TODO: Consider passing in vfs name
    SQLitePCL.raw.sqlite3__vfs__delete(null, filename, 1);
}

With these changes, the code builds under AnyCPU. I am currently trying to resolve some difficulties with my setup of xUnit 2.0 so I can run the tests.

E

[ErikEJ]

@ericsink Great news! You can use TestDiven instead to run the tests!

[divega]

@ericsink Thanks for the update. Sounds great! @bricelam is OOF a few days but we are looking forward to your PR. BTW, @ErikEJ is right, we are having difficulties with the latest xUnit bits (see EntityFramework issue #473) but TestDriven.NET seems to be working fine.

[ericsink]

Yep, installing TestDriven.NET was the answer: "276 passed, 0 failed, 0 skipped".

[mstyura]

@ericsink, @bricelam, @rowanmiller I just want to vote for adding support of WP and Windows 8/10 portable profiles. As I understand from above discussion it is currently not possible to provide it for WP Silverlight execution environment. As I understand so far there are two libs for SQLite which provides ADO.NET like API. The official System.Data.Sqlite and Mono.Data.Sqlite. First one is for full featured .NET and is not suitable for mobile environment unfortunately :( . Second one is intended for mobiles, but it's not obvious how well this library is supported. So it would be nice to have Microsoft.Data.Sqlite which is suitable for mobile and universal apps also. Please, consider making it as much portable as possible.

Thanks in advance.

[bricelam]

We're primarily limited by the places System.Data or System.Data.Common are available. Today, that means:

• .NET Framework/Mono
• .NET Core
  • Universal Windows Platform/.NET Native
  • DNX Core/Core CLR
• Xamarin
  • Xamarin.Android
  • Xamarin.iOS
  • Xamarin.iOS (Classic)

[mstyura]

@bricelam is the source code of System.Data.Common available in public? BTW, because the lack of System.Data in portable profiles, Mono.Data.SQlite even introduces System.Data.Portable and System.Transactions.Portable :|

[bricelam]

It's on the list to be open sourced as part of .NET Core.

[ericsink]

FWIW, SQLitePCL.raw 1.1.0 has been pushed to nuget. This is the version that contains some fixes I did as a result of testing with Microsoft.Data.Sqlite and UWP/.NET Native.