Consider moving unmanaged code out into separate package(s)

[ericsink]

Background

Calling SQLite from C#/F#/etc involves two parts:

1. a wrapper (managed code, a bunch of glue written in C#)
2. and, SQLite itself (unmanaged code, written in C)

Calling C from managed code is rather complicated, and even more so when dealing with lots of different underlying platforms. Most platforms use the pinvoke mechanism, but they do so in different ways. And the Silverlight-flavored versions of Windows Phone do not support pinvoke, so they have to use C++/CX instead.

The goal of SQLitePCL.raw is to hide all that complexity and present a portable API for calling SQLite.

But the previous sentence makes things sound simpler than they are. Exactly what do we mean by "SQLite"? Obviously, we are referring to some compiled form of the SQLite library. But where does that come from?

1. We obviously need to get the right CPU, which might be x86, x64, ARM, ARM7, or whatever.
2. And on iOS and its ilk, we now have LLVM bitcode to deal with, which is kind of like another CPU.
3. On Windows and its ilk, we care about which version of the C compiler was used, because that determines which version of the C runtime library will be needed. Visual C++ 2010, 2013, and 2015 are all still important, depending on what version(s) of Windows you want to be compatible with.
4. There are lots of options that can be chosen when SQLite is compiled. Features like full text search and JSON can be turned on or off. Newer versions of SQLite often arrive with more bug fixes or better performance.
5. Some platforms have SQLite built-in to the OS. We want to give the choice to use that library (to keep the app small, for example) or to use a different SQLite build (to get a more recent SQLite version, for example).
6. We might want encryption features, which are available as SQLCipher (SQLite with encryption support, maintained by Zetetic, a third party) or SEE (a proprietary encryption feature available from the SQLite authors). These are just different builds of SQLite with more features.
7. Some platforms (Windows) do not have SQLite built-in to the OS. It is worth providing a good experience for developers on these platforms.
8. Cross-platform apps might (and probably do) want to be sure that the same version of SQLite is being used on all their platforms.

Giving people choices can be a bad thing. Too many options is the way to paralysis. There is much to be said for something that Just Works without hassle.

So it is tempting to eliminate a bunch of these choices. For example, maybe we should not support the ability to use the SQLite built-in to iOS and just always bundle a newer version with all the optional features turned on?

Alas, my experience in maintaining this library and supporting the developers who use it has taught me that app developers need the ability to choose among these tradeoffs.

Two kinds of SQLite choices

It is worth highlighting at this point that all the complexity of choosing a specific SQLite build can be separated into two categories:

1. Choosing the right build of SQLite for a given platform, such as CPU, runtime, etc. In all likelihood, the developer wants this to be automatic and never think about it.
2. Choosing the build of SQLite based on which features are desired, such as full text search or encryption. Some developers don't want to think about this, but some do.

   Two cases of developers

It is also worth mentioning that the needs of library developers are a bit different than those of app developers.

If someone is building and deploying an app, they probably care a lot about exactly how SQLite is included and what features it is using.

But if someone is developing a library which calls SQLitePCL.raw, they may not care about much of the stuff above. They probably want to defer those choices to their own users.

And I should also admit that library developers are the primary audience of this library, since it intentionally provides a "raw" API that is low-level, intended to be the foundation for friendlier APIs to be built on top of it.

The Status Quo

As of SQLitePCL.raw version 0.8.6, we have one large NuGet package which tries to solve all these problems.

With the exception of support for Windows 10 Universal apps (the "uap10.0" TFM, added in 0.8.5), the package is based on a NuGet 2.x approach, using none of the new stuff from NuGet 3.x.

The package contains both SQLitePCL.raw.dll (in a bunch of different forms) and compiled SQLite libraries (in a bunch of different forms).

For Windows platforms, the job of copying the correct sqlite3.dll to the build directory is handled by using an msbuild "targets" file, a NuGet feature wherein msbuild code from the NuGet package can be invoked as part of the build process. This targets file chooses the correct sqlite3.dll based on the CPU. On most Windows platforms, SQLitePCL.raw.dll goes into the NuGet "lib" directory, and the "targets" file goes into the NuGet "build" directory.

For the Silverlight-flavored versions of Windows Phone, because pinvoke is not used, the targets file has to do more. Everything goes into the "build" directory.

For iOS and Android, the targets file is not necessary for CPU choice. On iOS, unmanaged code libraries are "fat", containing multiple CPU architectures in a single file. On both iOS and Android, native code gets included by the Xamarin tools by embedding it into a resource within a CLR assembly.

So if we did not care about supporting different builds of SQLite for features (like full text search or encryption), we wouldn't need the targets file for iOS/Android at all. But NuGet doesn't have any way of presenting the sort of config option we would need. So, the SQLitePCL.raw.dll file(s) go into the build directory anyway, and a targets file is used to choose based on the msbuild property "UseSQLiteFrom".

Ignoring the different forms of native code for a moment, the various versions of SQLitePCL.raw.dll itself are actually not that different from each other:

1. Well, to be honest, the non-pinvoke builds are very different. So let's try to avoid talking about them.
2. There are "bait" versions. SQLitePCL.raw is a "bait and switch" PCL. The bait versions are designed to be used when another PCL wants to reference SQLitePCL.raw. A bait assembly is just a stand-in that is used at build time. It doesn't contain any functionality.
3. Some are compiled for different .NET framework versions. For example, there is a net35 build. And there is a compile-time option to choose between the old way of doing reflection and the new way. Stuff like that.
4. There are little bits of platform-specific code which can be compiled in or out. For example, on iOS, callback functions that are round-tripped through unmanaged code have to decorated with an attribute so the Xamarin AOT compiler can do magic.
5. Finally, there is the question of what library name is provided to pinvoke. In other words, when the DllImport directive is used to specify a C function to be called by managed code, where does that C function get found? In general, this is the name of a shared library, but iOS doesn't support shared libraries, so things there a little weird.

Other tidbits about the status quo:

1. Xamarin.iOS classic (not the unified stuff) still has limited support. This toolset does not support targets files, so there is no ability to choose different SQLite builds.
2. The recently-added support for "uap10.0" is done with a SQLitePCL.raw.dll in "lib" and a targets file in "build". The targets file will add a sqlite3.dll which was built with the v140 toolset (Visual C++ 2015), linked against the Visual C++ 2015 C runtime. Testing so far indicates that this works on Windows 10 desktop as well as Windows 10 Mobile. The special dependencies section is also included in the nuspec, and it gets ignored by older NuGets. There is currently no "dotnet" TFM included. None of the new NuGet 3.x stuff for unmanaged code is being used yet.
3. There is not yet any support for .NET Core on non-Windows platforms.
4. The package includes no SQLite builds for Linux.
5. There is some support for Xamarin.Mac, but it has problems.
6. The use of the targets file has been fragile, a common source of difficulty for developers using the library. Xamarin.iOS classic doesn't support it. When developers have a problem, they look in the lib directory and find nothing there, so they conclude that the package is corrupt. Remarks from "People Who Know More Than Me" suggest that the targets file mechanism is out of favor and probably headed for deprecation someday.
7. A previous design attempted to use multiple SQLitePCL.raw packages for different feature options. The intent of this approach was, for example, to have "SQLitePCL.raw_Latest" which included the latest build of SQLite for all platforms, and "SQLitePCL.raw_Cipher", which included the SQLCipher builds of SQLite. No msbuild property (UseSQLiteFrom). Just reference the NuGet package containing the features you wanted. The problem with this approach was with library developers, like Akavache, for example. If you are building a library, you want to reference one SQLitePCL.raw NuGet package and defer the other choices to the users of your library. You certainly do NOT want to end up having multiple NuGet packages, like "Akavache_With_SQLite_Latest" and "Akavache_With_SQLCipher" and so on.
8. The SQLitePCL.raw NuGet package is around 30 MB, and every time I add support for another platform, it gets bigger. In many cases, developers are downloading a package that contains a bunch of stuff they are not using.
9. SQLCipher is currently supported only for Android and iOS, not for any of the Windows platforms. And for iOS, it uses the libcrypto from OpenSSL, not the stuff provided by iOS itself. I have a pending pull request from the Couchbase folks to address these issues.
10. One other edge case is currently supported. On Windows, one of the SQLitePCL.raw builds contains special code to dynamically load the appropriate "sqlite3.dll" after checking whether it is running in 32 bit or 64 bit mode. I implemented this because it was something that EF7 would need, but it turns out that other people have found it helpful.

    The change I am considering

I would like to do this differently.

The idea is get all the compiled SQLite libraries out of SQLitePCL.raw itself.

1. The SQLitePCL.raw NuGet package would contain nothing but the managed code (the wrapper).
2. There would still be several different builds of SQLitePCL.raw.dll, including bait assemblies and platform-specific builds.
3. All the SQLitePCL.raw.dll assemblies would go in the NuGet "lib" directory. No more msbuild targets files in the main package.

And the various builds of SQLite (or SQLCipher) itself would go into separate NuGet packages. In other words, if you are using the SQLitePCL.raw package, you would also need to reference something (like another NuGet package) which gives you SQLite itself.

An attractive thing about this idea is that the complexity ends up located very close to the thing that caused it. SQLitePCL.raw itself becomes simple, because it mostly is. And the plethora of SQLite builds will be, er, complicated, but that's just the way things are.

The trick is to design this so that all the flexibility above gets retained.

1. The SQLitePCL.raw package itself gives you no choices and requires no decisions.
2. Any decisions about what SQLite library you are using are implicit in which SQLite library package you choose to add.
3. Library developers reference SQLitePCL.raw only. App developers are the ones who need to somehow add SQLite itself.

The key question in all of this is: In SQLitePCL.raw.dll, what name is provided to DllImport (pinvoke)?

(Let's ignore the non-pinvoke platforms for now.)

The simple answer is to just use "sqlite3":

1. The pinvoke mechanism will look for a shared library called "sqlite3" in whatever fashion makes the most sense on the platform.
2. The nice thing about this approach is that it fits the convention of how the SQLite library is named in its various official forms. By referencing "sqlite3" in DllImport, you give the developer the ability to use the OS-provided SQLite or a custom SQLite build from almost any source. The SQLite provided by iOS or Android would Just Work. The SQLite vsix extenson SDK would Just Work.
3. The bad thing about this approach is that it fits the convention of how SQLite is named, so it risks referencing a SQLite library that you were not intending to use. If you want to use a custom SQLite build, you have to give it the same name as the one provided by the OS and trust that the library you want will get used.
4. On iOS, this approach will always end up referencing the SQLite dynamic library provided by the OS. The option to use a more recent build or a custom build or a SQLCipher build would not be available. iOS does not allow apps to provide shared libraries.
5. On Android, this approach will find the libsqlite.so library provided by the OS. How would we support the use of other SQLite builds? If the app provides a libsqlite.so of its own, which one will get chosen? I honestly don't know the answer to this. For the moment, let's suppose I ran some tests and found that the app's libsqlite.so is preferred over the one from the OS. Would I trust this to be true for every version of Android? (Do I trust anything to be true for every version of Android?) Would I trust that this behavior cannot be changed by fiddling with LD_LIBRARY_PATH?
6. On Windows, this approach will look for sqlite3.dll wherever it can be found. Some of the same concerns about ambiguity would apply here, except of course that most (or all?) Windows platforms do not have SQLite as part of the OS. (I heard rumors of this changing on Windows 10, or maybe just on Windows 10 Mobile?)
7. Some developers want to always use the OS-provided SQLite or a SQLite build that is reasonably "official". The previous sentence might even be correct if I replace "Some" with "Most". For those developers, passing "sqlite3" to DllImport is the preferred answer, because it is simple. But I don't want to eliminate "developers who need more control" from the audience of this package.

So, using "sqlite3" with DllImport is problematic. What are the alternatives?

1. For iOS, the only other option is to use "Internal". This is a special feature of Mono (upon which Xamarin.iOS is based). Quoting directly from mono-project.com: /The “Internal” library name will instruct Mono not to look this up in an external library, but to try to satisfy the symbol referenced (DoSomething) in the current executable image./ Basically, since iOS does not allow apps to use shared libraries, this is what we have to use.
2. For all the other platforms, the alternative is to reference a library name that we would have to provide. For example, instead of DllImport with "sqlite3", we could use "z_sqlite3".
3. This approach makes the use of a custom SQLite build possible. The bad news is that it also makes the use of a customer SQLite build necessary. SQLitePCL.raw.dll will always look for a shared library called "z_sqlite3", so that's what will be required.
4. So, all the SQLite and SQLCipher builds that are currently inside the SQLitePCL.raw NuGet package would instead be published in separate NuGet packages that provide them under the name "z_sqlite3".
5. For example, we could have a package called Z_SQLite_Latest, which includes the latest copy of the SQLite code, in a library called "z_sqlite3". Another one might be called "Z_SQLite_SQLCipher".
6. These packages would not contain any managed code.
7. These packages would not, in general, be useful for anyone who is not using SQLitePCL.raw.
8. For the use case where people want to just use the SQLite that is installed with iOS or Android, we would need to publish a bridge package that includes a "z_sqlite3" that passes everything through to "sqlite3". We might call this Z_SQLite_Shim.
9. Some platforms will continue to need msbuild targets files in the Z_SQLite package. For NuGet 3.x cases, we can use its new features for native code.
10. It should be possible (at least for NuGet 3.x) to separate these builds into separate packages. For example, instead of having one big NuGet package which contains builds for every platform, we could have separate NuGet packages for each platform, and perhaps also a package which uses platform-specific dependencies to route to the proper one.
11. The SQLitePCL.raw NuGet package would require one of the z_sqlite3 packages, but it would not contain a dependency on any of the z_sqlite3 packages, because any such dependency would require a decision to be made about which one, and the goal is to not make that decision.
12. All the complexity of multiple SQLite builds would stay in the z_sqlite3 side of things, without the need to affect SQLitePCL.raw itself.
13. It is unfortunate that as a project, SQLitePCL.raw will go from publishing one or two NuGet packages to publishing many more. Flooding nuget.org with packages causes confusion. OTOH, looking at the quantity of packages now being provided by Microsoft itself, it would seem that this ship has already sailed.
14. FWIW, I have verified that this model will work for iOS. In other words, I was concerned that having one assembly that does a DllImport from "__Internal" might not work if the actual bundled native library resources were coming from a separate assembly. But it does. I still need to verify this with Xamarin.Android, but I am less concerned about that one.

So, DllImport("sqlite3") is simple but inflexible. And DllImport("custom name") is flexible but complicated.

I have made various attempts to find a way to allow this decision to be made at runtime, without success. If such a solution were to exist, I would set things up such that "sqlite3" would be used by default and provide a way to override.

Moving forward

I have not yet decided how to proceed. I am publishing this (lengthy and tedious) writeup to solicit feedback. If you have made it all the way to the end, please accept my thanks for your attention. All comments would be welcomed.

[ericsink]

The novel I have written above is long and boring. Its characters are shallow and its plot is weak. I can't imagine anybody will enjoy reading this.

That said, I am looking for comments on it.

I would specifically like feedback from @borrrden or @zgramana, because your use case is directly related to the subject at hand.

And I would value feedback from @onovotny, because you have much knowledge about NuGet 3.x.

And, even though I know this will especially tax the patience of @praeclarum, I would value your feedback because so many users of SQLitePCL.raw actually get there through SQLite-net-PCL.

And @paulcbetts, because Akavache uses SQLitePCL.raw as well, and because you know lots of stuff, your comments would be welcomed.

And @bordoley, because you have made lots of contributions to SQLitePCL.raw over time, I invite your remarks on this.

I'll probably think of others I want to nag, but that'll do for now.

[clairernovotny]

@ericsink In the spirit of fostering a good review/dialog, can we get this on a OneDrive word doc/onenote or shared google doc? It'd probably be easier to review and discuss/notate inline for this.

[ericsink]

@onovotny Probably. ;-)

Lemme look into that. I don't regularly use any of the stuff you mentioned, but I suspect they won't be difficult.

[ericsink]

All right, let's see if I did this right.

https://docs.google.com/document/d/1wdGctdH_JiG3O15a-SFhEfTQo9iYQt5TYXJHcLjNUjM/edit?usp=sharing

I think I've configured this to allow comments by anyone. For anybody who prefers the shared Google document as a way of providing feedback, feel free.

If anybody wants the ability to not just add comments but also edit the document there, let me know and I'll figure out how to add you.

And for anybody who prefers to just comment here, that is welcomed as well.

[mhutch]

I only skimmed this, but one minor correction - Xamarin.iOS Classic does use MSBuild now. It was optional in Cycle 5, and is always used in Cycle 6.

[mhutch]

Also, for iOS Unified on iOS 8.0+ you could consider using an embedded framework?

[ericsink]

@mhutch What would the benefit of an embedded framework be? I'm not up-to-speed on the tradeoffs there.

[clairernovotny]

I put a bunch of thoughts/comments in the doc.

[mhutch]

@ericsink you could ship a custom libsqlite build as as a shared library on iOS

[ericsink]

Ah. Way cool. I didn't know that iOS 8 allowed a way to use dynamic libs. Thanks.

[dsplaisted]

The key question in all of this is: In SQLitePCL.raw.dll, what name is provided to DllImport (pinvoke)?

None! All problems in computer science can be solved with another layer of indirection!

IE, don't put the DllImports in SQLitePCL.raw.dll. Put them in a (bait and switch) managed library that goes in the package for each native version of SQLite, and includes only the DllImports. Instead of a "shim" package with a native z_sqlite library that redirects calls to the version included in the OS, you can simply have another "native" SQLite package that just has the DllImports pointing at the version in the OS and no actual native library.

I think this is a bit of an improvement on what you describe, but I think I didn't follow all of it.

This still leaves the issue that you would like to have a default version of SQLite (that would be used for example if you install Akavache), but application authors should be able to override that default. It might be nice if NuGet could support this type of default/replaceable dependency. (OTOH it might just end up adding way too much complexity to NuGet.) If you can't find a way to detect at runtime which version of SQLite to use, then I think the next best option is to let the app configure via code whether the default or one of the custom versions should be used. IE by default SQLitePCL.raw.dll would end up calling into a PInvoke into "sqlite", for example, but if a flag was set then it would use a "z_Sqlite" PInvoke instead, and you would need to reference one of the native NuGet packages that provided the native library and corresponding PInvoke.

[ericsink]

The idea from @dsplaisted is thought-provoking and deserves exploration. But my initial worry is: The indirection added by the DllImport-only package is still a NuGet-based operation, and that flavor of indirection still only supports the ability to choose implementations based on platform-like factors, not config options. (Correct me if I am misunderstanding something.)

For the moment, as a followup to comments from @onovotny over in the shared Google doc, I am making another attempt to find a way to move the choice of the DllImport library to a runtime decision.

[dsplaisted]

The indirection added by the DllImport-only package is still a NuGet-based operation

Consider the following option:

There is an SQLitePCL.raw NuGet package. It does not contain any of the DllImports, or any package dependencies on packages that do. It is compiled against a "bait"/reference assembly version of a library that provides the DllImports.

There is an SQLite-default package. It contains the "switch" / implementation assembly version of the DllImports library, as well as when necessary the actual implementation of SQLite. It can do this on a per-platform basis based on the factors that NuGet supports.

For non-default config options, there are corresponding NuGet packages. For example, SQLite-Crypto and SQLite-Latest. These would also each contain the implementation of the DllImports, as well as the SQLite implementation with the corresponding options. They would also use NuGet to select the right version of SQLite and the DllImports for the OS/bitness/etc selected.

So developers would need to choose which "config" options of SQLite to get by choosing which implementation package of SQLite they installed. NuGet would automatically pick the right version for the OS/platform.

The main drawback of this design is that you wouldn't automatically get a default implementation of SQLite. So you install Akavache, and run your app, and get a TypeNotFoundException or something because you didn't reference an SQLite implementation package and didn't get the DLL with the DllImports (as well as an SQLite implementation, if necessary).

To improve this experience, runtime detection would be ideal, so the SQLitePCL.raw NuGet package would work out of the box but if you installed one of the non-default config option implementation packages it would automatically use that instead. If runtime detection isn't possible, my suggestion was to have a config option in SQLitePCL.raw that could be set by the application code to tell it whether to look for a non-default SQLite implementation.

Also note that in this design if you want to reduce package size, you can split the SQLite implementation package for each custom config option into multiple packages, and use runtime.json (as @onovotny mentions in the doc) to select the right one for the current target.

[ericsink]

"or any package dependencies on packages that do"

The phrase above is the issue that was getting me stuck.

Let me explain what I think you are saying:

The main assembly (SQLitePCL.raw.dll) now expects the static class containing the DllImports to be provided by another assembly, which for now I will call MyImports.dll. When I build SQLitePCL.raw.dll, I provide it with a reference copy of MyImports.dll. But when I package SQLitePCL.raw.dll into its NuGet package, I do not include MyImports.dll, nor do I include a NuGet dependency.

(Several different implementations of MyImports.dll would exist. Folks need to pick the one that has the features they want.)

I think this would mean that adding SQLitePCL.raw to a project will not even build until one of the various MyImports dlls is added as well.

Does this match your thoughts? Or were you thinking of something where SQLitePCL.raw.dll used reflection?

Your comments about "runtime detection would be ideal" seem suggestive of a more like a reflection-based solution. But for SQLitePCL.raw to work out of the box whenever a MyImports.dll is not present, that would mean that the main assembly actually does have its own set of DllImports.

Either way, I think you've suggested something interesting that was in my blind spot.

[praeclarum]

@ericsink https://github.com/ericsink you could ship a custom libsqlite build as as a shared library on iOS

Oh please don’t.

[ericsink]

@praeclarum I'm curious for more detail about the feeling of dread you apparently have about shared libs on iOS.

That said, I wasn't leaning in that direction anyway. Just curious.

[praeclarum]

Nothing against shared libs - everything against popping in our own custom sqlite. The system one is tested and stable. It’s updated automatically. I guess I dread maintaining that custom build.

That said, there are pros: adding full text indexing requires a custom built sqlite, some of that crazy encryption stuff also requires it. So I guess there is some utility to be gained with a pluggable back end.

On Jan 13, 2016, at 10:33 AM, Eric Sink notifications@github.com wrote:

@praeclarum https://github.com/praeclarum I'm curious for more detail about the feeling of dread you apparently have about shared libs on iOS.

That said, I wasn't leaning in that direction anyway. Just curious.

— Reply to this email directly or view it on GitHub https://github.com/ericsink/SQLitePCL.raw/issues/66#issuecomment-171390251.

[ericsink]

Ah. Well, FWIW, that ship has already sailed. :-)

SQLitePCL.raw already supports the ability to use either the OS-provided SQLite (by default) or a custom build. And yes, this is for the reasons you mentioned (full text indexing, crypto, etc).

It could be said that a major goal of this project is to provide that flexibility without letting it cause too much hassle. I will consider it a small point of success that you maintain a popular wrapper but did not even know of the support for custom builds, since that apparently means the flexibility has not caused you enough pain to notice. :-)

[mhutch]

FWIW, I wasn't suggesting shipping sqlite as a shared lib, just pointing out that it was possible :)

[dsplaisted]

I think this would mean that adding SQLitePCL.raw to a project will not even build until one of the various MyImports dlls is added as well.

Does this match your thoughts?

Yes, this is what I was describing in the first part. I thought it would be easier to understand if I first described a simpler system.

However, the simple system has the unfortunate property that it's not a very good developer experience. So I think in addition you would want some other way of handling a default SQLite implementation (eg runtime detection or configuration).

[borrrden]

Over here we are also having lengthy discussions about this very topic! I've skimmed over what you've written but I'm a bit too tired at the moment to last through the whole thing. I'll give it another look soon!

[ericsink]

OK, just an update:

I've made significant progress, and I think I know where this is going to end up. But let's say that all of the following is tentative until it's actually done.

The main SQLitePCL.raw package is very clean. It no longer contains native code. It pinvokes with the library name "sqlite3", which means it should work with the default SQLite3 library on each platform. If you don't need a custom SQLite build, that's all you need. Just add the SQLitePCL.raw package and it should just work.

There will be two optional mechanisms for using a custom SQLite build:

(1) Actually, the main SQLitePCL.raw package will first try to pinvoke from a different name at runtime. Right now that name is "esqlite3" (note the e). If it finds a dynamic library with that name, it will use that. If it does not, it just falls back to the default pinvoke of "sqlite3" (without the e). (This was done by embedding two copies of the DllImports.)

So, if you want to use a custom SQLite build on Windows for example, just build it as esqlite3.dll and put it in the right place.

(2) The other way to use a custom SQLite build is more explicit and involves calling a function. That function's parameter must be an object which implements the ISQLite3Provider interface defined in SQLitePCL.raw. (This allows for a separate assembly to have a copy of those DllImports.)

At this point it looks like method (1) above will work everywhere except iOS. It should work on iOS Unified by using an embedded framework, but so far it doesn't, probably because I'm doing something wrong. Anyway, method (2) above allows the use of iOS static linking, and works just fine.

I intend to provide SQLite3 and SQLCipher NuGet packages for most platforms, probably using method (2) for iOS and method (1) for everything else. FWIW, method (2) should work pretty much anywhere, although I will (probably) only provide that mechanism for platforms where it is needed (iOS), because it requires an extra step on the developer's part.

Finally, it is worth noting that the Windows Phone 8.0 and "Windows Phone 8.1 Silverlight" will be excluded from all of the above, since they do not support pinvoke. I plan to continue shipping basic support for those two platforms, but not to support the use of custom SQLite builds there.

[ericsink]

Update:

I am waffling on one issue and would welcome feedback:

I am starting to prefer that the use of a custom SQLite build should require an explicit Init() call, rather than having the main package look for a DLL and try to load it automatically. It just feels cleaner to me to have an explicit dependency injection step.

If (like most users) you want to use the default SQLite library, just use the SQLitePCL.raw package, and it should Just Work.

If you want to use a custom SQLite library (like, for example, one with fulltext search or encryption), you also add the corresponding NuGet package (which will contain or depend on platform-specific unmanaged code) and call its Init() method (which will cause the package to register itself with SQLitePCL.raw as the implementation to be used).

Any objections?

[borrrden]

I'm unfamiliar with what that Init() process would entail, but assuming it is possible this would be great. This would give us the ability to customize our SQLite build however we want and just put our own nuget package out there.

[borrrden]

Having read into this a little more, I assume this means that you would signal SQLitePCL.raw to switch from the "regular" DLL name to the "custom" dll name manually. It might make sense to allow a custom ISQLite3Provider implementation to be injected as well in case the user wants to add some P/Invoke functions that are not in the default implementation. Perhaps an abstract class that implements the base functionality?

[ericsink]

@borrrden Actually, the mechanism I've been implementing is much like the second thing you described, a way to inject a custom ISQLite3Provider implementation.

The class raw has:

public static void SetProvider(ISQLite3Provider imp);

which can be used to inject whatever implementation you want.

The NuGet packages I plan to provide will contain something like:

public static void Init()
{
    var imp = new SQLite3Provider_with_special_stuff();
    raw.SetProvider(imp);
}

None of this is set in stone yet.

[bordoley]

I don't have much skin in the game these days, but I'll chime in that I like the init approach. I'm almost incline to say that it should be required even when using the built in SQLite version, so that developers are aware that they are choosing to use the platform provided version versus an explicit version. This is especially relevant for platforms like Android where their is wide distribution of different operating system versions with different SQLite versions, which will of course have different capabilities. It might also be a good idea to update the default SQLiteProvider to explicitly throw an exception when using SQLite functions that are newer and not commonly supported across all prepackaged SQLite versions.

[ericsink]

@bordoley I do find myself tempted by the idea of forcing the developer to be explicit even when they want the default SQLite build. But I doubt I will convince myself to be that cruel. :-)

[ericsink]

FWIW, we're getting close to publishing a pre-release of 0.9.0.

The main SQLitePCL.raw package should just work for most people, and it has slimmed down to about 447 KB:

     447536 SQLitePCL.raw.0.9.0-pre1.nupkg
      49888 SQLitePCL.ugly.0.9.0-pre1.nupkg

For the additional packages, I'm almost settled on the terminology of a "plugin" instead of using the word "custom". They currently look like this:

    4449915 SQLitePCL.plugin.sqlcipher.android.0.9.0-pre1.nupkg
    8032667 SQLitePCL.plugin.sqlcipher.ios_classic.0.9.0-pre1.nupkg
    8032681 SQLitePCL.plugin.sqlcipher.ios_unified.0.9.0-pre1.nupkg
    1763512 SQLitePCL.plugin.sqlite3.android.0.9.0-pre1.nupkg
    2992878 SQLitePCL.plugin.sqlite3.ios_classic.0.9.0-pre1.nupkg
    2992881 SQLitePCL.plugin.sqlite3.ios_unified.0.9.0-pre1.nupkg
     972642 SQLitePCL.plugin.sqlite3.net35.v110_xp.0.9.0-pre1.nupkg
     972786 SQLitePCL.plugin.sqlite3.net40.v110_xp.0.9.0-pre1.nupkg
     972787 SQLitePCL.plugin.sqlite3.net45.v110_xp.0.9.0-pre1.nupkg
    1269105 SQLitePCL.plugin.sqlite3.uap10.0.v140.0.9.0-pre1.nupkg
    1269088 SQLitePCL.plugin.sqlite3.win8.v110.0.9.0-pre1.nupkg
     973032 SQLitePCL.plugin.sqlite3.win8.v110_xp.0.9.0-pre1.nupkg
     973045 SQLitePCL.plugin.sqlite3.win81.v110_xp.0.9.0-pre1.nupkg
    1265677 SQLitePCL.plugin.sqlite3.win81.v120.0.9.0-pre1.nupkg

To use a plugin, just add it to your app and call SQLite3Plugin.Init() from your equivalent of main().

For now, all the plugin packages still use targets files, not the NuGet 3.x stuff. That'll come.

For now, I have support for the same set of native code platforms I did before. More later.

I did add new native code packages built with the v110_xp toolset with the C runtime statically linked. Desktop apps can use these to avoid needing to add the C runtime lib.

I do not yet have a "switch" package. In other words, I don't have a package that figures out which plugin you need and then adds a dependency to that one.

I still need to update my sqlcipher builds to be more like the ones Couchbase Lite is using. Like using common crypto for the iOS version, for example.

I also want to do coordinate with @borrrden to make sure this new approach is going to work well with the plugin-ish approach you folks introduced with Couchbase Lite 1.2.

I'll push these packages to nugget as soon as I'm sure I am done changing the package IDs. If you hate the word "plugin", now is the time to say so. :-)

[borrrden]

Excellent I've been waiting for this. Nice bonus with the statically linked C runtime (this has come up several times). Also since 1.2 I've further refactored out our managed code sources as well as our unmanaged one so that they truly work like plugins. Your method will be a welcome addition because it means I can avoid what I'm doing at the moment which is compiling SQLitePCL sources directly into my DLL to avoid msbuild fighting over which version of SQLitePCL gets copied (the official one or "mine").

I don't currently have the equivalent for the Init method in my system and this is both a pro and con, but if SQLitePCL has one it won't be a problem unless it absolutely has to be called from the top level project (I assume it would be ok for a library project inside the project to call it). It's a pro because it means that installing the package is all the user needs to do, but its a con because there will be either errors or arbitrary decisions if the user installs more than one of the same family of plugins. Also iOS cannot include plugins unless they are AOT compiled which makes things weird and necessitates the need for an init method or ghost refererences in the core assembly.

I don't hate the word plugin, though an alternative could be "support package"

[ericsink]

@borrrden A coworker of mine is saying that I have too many packages. He wants me to have just three:

SQLitePCL.raw SQLitePCL.plugin.sqlite3 (which contains sqlite3 for all platforms) SQLitePCL.plugin.sqlcipher (which contains sqlcipher for all platforms)

I sympathize with the feeling of confusion people get when they bring up their IDE's nuget dialog, search for SQLitePCL, and see too many options. But more and more NuGet packages have this issue.

Anyway, do you have an opinion on this?

(I'll respond to your questions just above in a separate msg.)

[ericsink]

@borrrden I have been envisioning that in your case, the Init() method would be called from inside your library.

And yes, one of the reasons I made the Init() method mandatory is to deal with the constant problems with iOS and its AOT compiler.

[borrrden]

I would certainly prefer one package to contain all of the implementations for any given configuration. Otherwise Couchbase Lite will be taking deps on several different plugin packages. I'd definitely like to avoid the user having to add them (but that will still cause the next problem). The goal is to install Couchbase Lite and have everything work without additional packages being manually installed. To that end, I have chosen one plugin as the "default" (Couchbase.Lite.Storage.SystemSQLite) and made the Couchbase.Lite package have a dependency on it. In keeping with this goal, if everything were a separate package by platform we would have to add three additional packages as dependencies to make sure that we covered everything (one for each platform of a given implementation) and it would look very odd to a user if they had a "droid" package on their iOS project, etc.

So if you end up keeping them like this I imagine I would not make them nuget dependencies, but rather download them at compile time and package them all together in the Couchbase nuget package. Right now (in the master branch) the plugin packages look like this ->

SystemSQLite: dep on SQLitePCL.raw and SQLitePCL.ugly Couchbase.Lite.Storage.SystemSQLite.dll -> concrete implementation of our storage interface using the above two references (keeping them intact because they take care of the sqlite3.dll copying for me!)

SQLCipher: No deps (SQLitePCL.raw and ugly code is compiled into the below DLL) Couchbase.Lite.Storage.SQLCipher.dll -> concrete implementation of the storage interface using the internally compiled versions of raw and ugly that DllImport to sqlcipher A .targets file (made a lot of references to yours in the process of making them) that copies the correct sqlcipher native image, if applicable (Android and iOS both have these images compiled directly into the managed DLL)

ForestDB: No deps (cbforest-sharp.dll is compiled into the below DLL) Couchbase.Lite.Storage.ForestDB.dll -> concrete implementation of the storage interface using cbforest-sharp A .targets file that copies the correct CBForest-Interop native image, if applicable (Android and iOS both have these images compiled directly into the managed DLL)

Note: The concrete implementations get loaded at runtime via Type.GetType (indirectly calls Assembly.Load). The library is in control of which implementation will get used so even if the user decides to get cute and call Assembly.Load on another one, it won't have any effect other than taking up more memory.

This is what I would like to turn it into:

SystemSQLite dep on SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlite3 Couchbase.Lite.Storage.SystemSQLite.dll -> concrete implementation of our storage interface that registers the SQLitePCL.plugin.sqlite3 plugin

SQLCipher dep on SQLitePCL.raw, SQLitePCL.ugly Couchbase.Lite.Storage.SQLCipher.dll -> concrete implementation of our storage interface, and SQLite provider implementation using our compiled version of sqlcipher A .targets file as described above

CustomSQLite (new package) dep on SQLitePCL.raw, SQLitePCL.ugly Couchbase.Lite.Storage.CustomSQLite.dll -> concrete implementation of our storage interface, and SQLite provider implementation using our compiled version of sqlite3 A .targets file similar to SQLCipher

ForestDB (No change)

[borrrden]

P.S. Another angle would be to split the Couchbase.Lite.Storage packages into platforms, but this will infect everything up to the core package since I want the nuget dependency from Couchbase.Lite -> Couchbase.Lite.Storage.SystemSQLite. It would turn into Couchbase.Lite -> Couchbase.Lite.Storage.iOS.SystemSQLite, Couchbase.Lite.Storage.Android.SystemSQLite, Couchbase.Lite.Storage.Net45.SystemSQLite, Couchbase.Lite.Storage.Net35.SystemSQLite (I still split the last two because one needs the backport TPL and the other doesn't)

[ericsink]

That's very helpful. Thanks. More later...

[ericsink]

@onovotny A question:

I've had people gripe at me for assuming that "net45" (in NuGet) means Windows. They're using Mono on Linux and they want to consume net45 packages without getting Windows-specific stuff.

I've also had people tell me to ignore the people mentioned above, because net45 actually does imply Windows, and, well, cross-platform .NET stuff is what .NET Core is all about, so use "dotnet" and the new stuff in NuGet 3.x and look at .NET Standard.

What say you? Is it legit to assume that net45 (and net40, etc) always means Windows?

[borrrden]

so use "dotnet" and the new stuff in NuGet 3.x and look at .NET Standard.

This will require support from Mono which seems to be a ways off, at least for IDE integration.

I don't think anyone can make the argument that net45 means Windows only unless they are using Nuget 3.3+, and I definitely don't want to think that people will start using net35 as Windows only since there is no dotnet equivalent.

[clairernovotny]

Right now, Mono seems to choose the net45 stuff because it's essentially "desktop 4.5 compatible". There's really no way NuGet currently has to provide mono-specific assets. The closest it gets is for Xamarin iOS/Android/Mac platforms.

I think you should assume that net4/net45 is Windows but that this is "yet another thing that's borked on Mono" because there's no other way.

The only possible workaround would be to use msbuild targets to swap stuff out based on environment variables/IsUnix type of stuff.

In the end netstandard is the way to go but it still won't currently differentiate mono from net45/net46. Clearly that's the goal. What you may have in NuGet v3 is the runtime id's -- of which mac/Linux are there. That's how the CoreFX libs provide Linux specific functionality. That's all NuGet v3 stuff though.

The idea is that you'd have a netstandard1.0 version for the MSIL parts and then potentially have many different runtimes elements as that can pivot on Linux/OSX/x86/arm, etc. You shouldn't then care if it's .NET Core on Linux or Mono on Linux since they should be using the same assets.

[ericsink]

@borrrden wrote: "I don't think anyone can make the argument that net45 means Windows only"

And yet, I assume that you want this proposed SQLitePCL.plugin.sqlite3 package to provide a Windows DLL for net45, right? :-)

[borrrden]

Yes, and so I mean "Desktop .NET 4.5" (whether on Windows, OS X, or Linux, etc) as opposed to "Windows .NET 4.5". Your current solution doesn't copy Windows specific items when they are not needed. Or do you mean that people don't want them to even exist in the downloaded package at all?

EDIT I think my wording before might have been a little confusing so I added some emphasis

I don't think anyone can make the argument that net45 means Windows only unless...

I may have misunderstood the point you were trying to make though (I thought it was people saying that net45 should exclude platforms other than Windows)

[ericsink]

"Your current solution doesn't copy Windows specific items when they are not needed."

I think I understand what you mean by the above. Basically, for net45 (and similar), by default (without the UseSQLiteFrom msbuild property), SQLitePCL.raw (0.8.x) does not install any native code, and since it pinvokes from plain old "sqlite3", it probably Just Works for non-Windows platforms.

(For 0.9.0, the main SQLitePCL.raw package will work the same way.)

But the only purpose of these new "plugin" packages (however they get grouped) is to provide an implementation of the native code.

Although (as you have described) it would cause more hassle for the developer, the benefit of having the plugin packages all separate is that they produce a "layer" where I'm not making any assumptions that higher layers might regret. SQLitePCL.plugin.sqlite3.net45.v110_xp.0.9.0-pre1.nupkg is not a friendly name, but it's unambiguous. Once you know that "v110_xp" is a Visual Studio toolset, you know that this package is Windows-specific. Even if "net45" does not mean Windows, "net45.v110_xp" does.

So, we were talking earlier about a SQLitePCL.plugin.sqlite3.nupkg which contains multiple platforms. It (obviously) needs to know how to select the right one. In terms of the native code in this package, what do we want "net45" to mean?

From looking inside your cblite packages, it looks like the only things that matter for cblite right now are the Xamarin platforms and net35+net45. If you take a dep on the multi-platform SQLitePCL.plugin.sqlite3, when net45 gets selected for your package, I assume you want a Windows DLL (as opposed to any of the possible non-Windows connotations of net45).

That's what I mean above when I speak of "assuming that net45 means Windows".

Another example, but perhaps not currently applicable to cblite: For the multi-platform SQLitePCL.plugin.sqlite3, which build do I put in for win81? The one built against the most recent compatible C runtime library? Or the one built with v110_xp? The former will require the extra Visual C++ Runtime package to be added. The latter is built with the C runtime statically linked, so it requires no extra step. But are there cases where the end app developer needs to make sure they don't have two different copies of the C runtime in their app?

It's possible that I am being excessively pedantic. It wouldn't be the first time.

[ericsink]

(Popping the stack and responding to your note that began with "I would certainly prefer one package ")

So for your SystemSQLite package, since 1.2 you've gone back to having nuget deps on SQLitePCL.raw? (Just clarifying.)

In your "This is what I would like to turn it into:" section, you mention that SystemSQLite would have a dep on the multi-platform SQLitePCL.plugin.sqlite3. Is that for Windows stuff? For "system SQLite", on iOS and Android you wouldn't need it, right?

For SQLCipher, I still think I might switch over to using your builds, which may or may not affect how you structure the deps for your SQLCipher package.

For your CustomSQLite package, dropping in the right C# code from my tree is straightforward. I've thought about trying to make that use-case even easier, but I haven't come up with anything obvious.

I've never actually used the nuget ability to vary the package deps per-platform:

https://docs.nuget.org/create/nuspec-reference#specifying-dependencies-in-version-2.0-and-above

But I've been planning to experiment with this to build a "switching" package, one which contains nothing except deciding which of the plugin packages is needed.

So for example, suppose we don't have a big multi-platform plugin package. Would this varying-deps feature allow you to just add one plugin dependency for each of your platforms? Or am I not understanding correctly?

[borrrden]

NOTE Halfway through writing this I realized it sort of weakened my argument for preferring one package

Hmm well to put it in a non-abstract perspective, Couchbase Lite .NET has 11 supported platform combination targets, out of which 8 are in GA and 3 are beta:

GA .NET 4.5 Windows -> Uses net45 .NET 3.5 Windows -> Uses net35 .NET 4.5 Mono OS X -> Uses net45 .NET 3.5 Mono OS X -> Uses net35 .NET 4.5 Mono Linux -> Uses net45 .NET 3.5 Mono Linux -> Uses net35 Xamarin.iOS -> Uses Xamarin.iOS10 Xamarin.Android -> Uses MonoAndroid

Beta Unity3D Desktop -> N/A Unity3D iOS -> N/A Unity3D Android -> N/A

So for the net45 target, for example, I would want the Windows native DLLs included in whatever version I end up choosing. I think I may have jumped to conclusions that I would need to add the four different deps (iOS, net35, net45, droid) to EVERY project because I forgot about dependency target frameworks. So in the end if it can work as below, then you should use whatever configuration is easiest for you to maintain and work with.

Couchbase.Lite.Storage.SystemSQLite "mock" nuspec

Framework=net45 deps -> SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlite3.whatever (statically compiled runtime, including Windows DLLs)

Framework=net35 deps -> same as net45

Framework=Xamarin.iOS10 deps -> SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlite3.ios_unified (see note at bottom)

Framework=MonoAndroid deps -> SQLitePCL.raw, SQLitePCL.ugly, SQLitePCL.plugin.sqlite3.droid (see note at bottom)

Couchbase.Lite.Storage.SQLCipher (and other custom native builds) "mock" nuspec

Framework=all deps -> SQLitePCL.raw, SQLitePCL.ugly (plugin will come from inside package)

(note at bottom) This depends on whether the "default" ISQLite3Provider implementation will be part of the plugin packages, or part of the core package. If they are part of the core package, then iOS and Android plugins will not be needed, as you said.

[borrrden]

Add on comments: I am using the deps per platform target at the moment because some nuget deps are not available for .NET 3.5, so I cannot add them for .NET 3.5 and am forced to include them in the package.

For custom SQLite, we have a repo similar to our SQLCipher one for building our own SQLite3 with the compilation flags that we need, it may or may not differ from the one you offer.

[ericsink]

OK, so understanding that you support Mono/OSX and Mono/Linux was an "Aha moment".

But then this sentence confused me:

So for the net45 target, for example, I would want the Windows native DLLs included in whatever version I end up choosing.

To be clear:

So you want the native Windows DLLs in whichever SQLitePCL.plugin package includes net45, however:

If the targets file for that package always copies the native Windows DLLs to the project output directory, that's going to be a problem for you, right? You need that targets file to respect OSX and Linux issues.

(I assume for OSX and Linux you always rely on whatever SQLite is installed with the OS.)

[borrrden]

Yes, your summary is correct. If the targets file copies it every time it won't be a huge problem, but it looks weird. OS X and Linux will be using the system SQLite, unless they install the custom sqlite package (that I haven't created yet) or the SQLCipher package.

Basically, the way it works now with regards to x86/sqlite3.dll and x64/sqlite3.dll is the way I'd like it to continue working:

Included in the net45 package but: Windows -> Copy OS X -> Don't copy Linux -> Don't copy

[ps30]

Will you be updating the SQLite binary to 3.11.0 in the 0.9.0 release? http://sqlite.org/releaselog/3_11_0.html

[ericsink]

@ps30 Yes, I plan to do so.

[ericsink]

0.9.x has been released