System.Data.SQLite

When using System.Data.SQLite with F# SQLProvider, you need to manually copy the x64 and x86 interop subdirectories from 
<project root>\packages\System.Data.SQLite.Core.<version>\build\net46
to 
<project root>\packages\System.Data.SQLite.Core.<version>\lib\net46
(see [http://fsprojects.github.io/SQLProvider/core/sqlite.html#ResolutionPath])

If possible, it would make things easier for F# use, if the NuGet package already included those extra copies of the x64/x86 subdirectories under "lib".

I realise that this is a minority use case, but with type providers (like SQLProvider) being so well suited for data access, it would be a most welcome feature.

Generally, people use the included MSBuild file to make sure the interop DLLs
get copied into the project output directory.  Will that not work for F#?

That works when the SQLite database is accessed only during runtime, which is the case when System.Data.SQLite is used directly.

However, when it's used via the SQLProvider type provider, the database is accessed already during editing and compilation. Example:

    type MyDB = SqlDataProvider< 
                    ConnectionString = "Data Source=MyDB.s3db;Version=3",
                    DatabaseVendor = Common.DatabaseProviderTypes.SQLITE >

Already during editing, the above declaration causes the type provider to access the database and extract information about the column names and their types. On-the-fly, it uses this information to generate statically typed and named members that can be used in queries. It makes database access as convenient as in a dynamic language, but it's still static and type safe.

That's why SQLProvider needs System.Data.SQLite and the interop DLLs already before the project has built.

I'm open to making changes in order to make this scenario work for you, given
the following conditions:

1. It does not increase the size of the NuGet package(s).
2. It is somehow opt-in, as to avoid unnecessarily impacting others.

Is it possible for you to set an environment variable for the process that will
be loading System.Data.SQLite?

Alternatively, can you write (create?) a "System.Data.SQLite.dll.config" file
that sits right beside the DLL itself?

This would allow you to make use of the configuration options listed here:

[/artifact?ci=trunk&filename=System.Data.SQLite/Configurations/System.Data.SQLite.dll.config]

Maybe I should add the (comments-only) configuration file to the NuGet package?

If people customize it in-place, would that be bad from a project package
management perspective?

On trunk, it should now be possible to write (or create) an XML configuration
file named "System.Data.SQLite.dll.config" right alongside the managed assembly
"System.Data.SQLite.dll" with something like the following contents:

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <add key="PreLoadSQLite_BaseDirectory"
         value="%PreLoadSQLite_AssemblyDirectory%\..\..\build\%PreLoadSQLite_TargetFramework%" />
  </appSettings>
</configuration>

The above construct uses the new "replacement tokens" available for use with
configuration settings.  As long as this configuration file does not get copied
to the build output directory, things should continue to work as normal at
runtime.  Meanwhile, at design-time, the above configuration block should allow
the managed assembly to locate the native interop assemblies.

Please note that while there are now tests to verify these replacement tokens
actually work, I have NOT tested it from within a NuGet package deployment.

Are you able to compile the binaries and NuGet package from the source code
locally in order to verify these changes in your environment?

I've followed the build instructions under "Automated Build" on [https://system.data.sqlite.org/index.html/doc/trunk/www/build.wiki], but the nuspec file refers to .../x64 and .../Win32/ReleaseNativeOnlyStatic directories which aren't created during build. Should the contents of those directories be the same as for the (x64 or Win32)/ReleaseNativeOnly directories?

-John

Normally, the NuGet packages are only built during the release process,
documented here: [/doc/trunk/www/release.wiki].

Yes, the contents are basically the same.  In theory, you should be able
to manually rename "ReleaseNativeOnly" directories to "ReleaseNativeOnlyStatic"
and have things work.

The only difference between the two sets of native binaries is that one statically
links to the MSVC runtime and one does not.

I took the quick'n dirty route and renamed ReleaseNativeOnly to ReleaseNativeOnlyStatic. Then I

* built a NuGet package
* added it to local NuGet source (to be able to simulate actual use)
* installed the NuGet package in a test project
* created the "System.Data.SQLite.dll.config" according to your instructions

Works perfectly with F# SQLProvider! 

Do you think this "System.Data.SQLite.dll.config" could be included in future System.Data.SQLite NuGet packages, or would you rather see a modification of SQLProvider that creates the config file when needed?

-John

We could include it in the NuGet pacakges; however, we would have to include it
multiple times, one per directory containing the "System.Data.SQLite.dll" file.

Also, we would need to make sure that it does NOT get copied into the build output
directory.

I think check-in [6e8e58e534aee864] should accomplish the goals.  And I'm pretty
sure it won't be copied to the build output directory.

When I manually placed it in the lib directory and rebuilt the project using it, it was not copied to output, so I'm pretty sure too.

-John

Cool.  I guess we'll ship this change in 1.0.104.0 then.