search

jdhitsolutions / MySQLite

A small set of PowerShell commands for working with SQLite database files.
MIT License
41 stars 4 forks source link

Help Needed: Non-Windows Platform Support #21

Open jdhitsolutions opened 10 months ago

jdhitsolutions commented 10 months ago

I would love to be able to support non-Windows platforms using PowerShell 7. Unfortunately, I have not had any luck finding compatible libraries, i.e., System.Data.SqLite.dll and SQLite.Interop.dll. I am more than happy to accept PRs or even pointers on where to look.

God-damnit-all commented 9 months ago

@jdhitsolutions

GetSQL apparently includes SQLite binaries that support Linux:

https://github.com/jhoneill/GetSQL/tree/master/linux-x64

God-damnit-all commented 9 months ago

Worth noting that it doesn't support ARM architecture, though.

Also, I pointed jhoneill over to this issue in the hopes he can provide more context on how he acquired/compiled the non-Windows binaries.

jdhitsolutions commented 9 months ago

I will take a look when I get home from vacation.

jhochwald commented 9 months ago

Hi there,

I Stumbled across this thread... you might want to look at the following Nuget packages: https://www.nuget.org/packages/SQLite https://www.nuget.org/packages/System.Data.SQLite https://www.nuget.org/packages/System.Data.SQLite.Core

You can extract the assemblies from there. And with a few switches (Assembly load logic), this could do the trick for you.

Or you use the sqlite-netFx-source-1.0.XXX.0.zip file to build the libSQLite.Interop.so / SQLite.Interop.dll yourself ;-)

Let me know if you need support/help.

jhoneill commented 9 months ago

Worth noting that it doesn't support ARM architecture, though.

Also, I pointed jhoneill over to this issue in the hopes he can provide more context on how he acquired/compiled the non-Windows binaries.

I Stumbled across this thread... you might want to look at the following Nuget packages https://www.nuget.org/packages/System.Data.SQLite

That's the one I'm using. I include a script in GetSQL to download and set them up :-)

As @ImportTaste says though, that one doesn't have ARM support. Really the ideal would be for .NET to support SQLite directly on all platforms, but I'm not holding out a lot of hope of that happening soon.

jhochwald commented 9 months ago

Sorry @jhoneill I don't get the point!

You can create the required SQLite.Interop.dll for yourself, everything is open source!

**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.8.0-pre.5.0
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64_arm64'
        mkdir obj\arm64
        rc.exe  /r /nologo /dINTEROP_RC_VERSION=1,0,118,0 /foobj\arm64\SQLite.Interop.res src\SQLite.Interop\src\win\SQLite.Interop.rc                                                                                                                  cl.exe /MT /nologo /c /Foobj\arm64\interop.obj  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  src\SQLite.Interop\src\generic\interop.c
interop.c
        mkdir bin\arm64
        cl.exe /MT /nologo /LD /Febin\arm64\SQLite.Interop.dll  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  obj\arm64\interop.obj  /link  /INCREMENTAL:NO  /NOLOGO  obj\arm64\SQLite.Interop.res  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteCommand.bmp,System.Data.SQLite.SQLiteCommand.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteConnection.bmp,System.Data.SQLite.SQLiteConnection.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteDataAdapter.bmp,System.Data.SQLite.SQLiteDataAdapter.bmp  /VERSION:1.0
   Creating library bin\arm64\SQLite.Interop.lib and object bin\arm64\SQLite.Interop.exp
Build complete

This was created on a Microsoft Surface Pro 9 5G (SQ3 based ARM64)! In my case: I use the latest preview on this device! But it will work fine with the regular Visual Studio 2022 community edition. And the compiler is free, so why not using it?

And a regular cross compile will work fine as well:

C:\Temp\SQLite.Interop-win>CALL "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars32.bat"
**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.8.0-pre.5.0
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x86'
        mkdir obj\x86
        rc.exe  /r /nologo /dINTEROP_RC_VERSION=1,0,118,0 /foobj\x86\SQLite.Interop.res src\SQLite.Interop\src\win\SQLite.Interop.rc
        cl.exe /MT /nologo /c /Foobj\x86\interop.obj  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  src\SQLite.Interop\src\generic\interop.c
interop.c
        mkdir bin\x86
        cl.exe /MT /nologo /LD /Febin\x86\SQLite.Interop.dll  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  obj\x86\interop.obj  /link  /INCREMENTAL:NO  /NOLOGO  obj\x86\SQLite.Interop.res  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteCommand.bmp,System.Data.SQLite.SQLiteCommand.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteConnection.bmp,System.Data.SQLite.SQLiteConnection.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteDataAdapter.bmp,System.Data.SQLite.SQLiteDataAdapter.bmp  /VERSION:1.0
   Creating library bin\x86\SQLite.Interop.lib and object bin\x86\SQLite.Interop.exp

C:\Temp\SQLite.Interop-win>pushd C:\Temp\SQLite.Interop-win

C:\Temp\SQLite.Interop-win>CALL "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars64.bat"
**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.8.0-pre.5.0
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64'
        mkdir obj\x64
        rc.exe  /r /nologo /dINTEROP_RC_VERSION=1,0,118,0 /foobj\x64\SQLite.Interop.res src\SQLite.Interop\src\win\SQLite.Interop.rc
        cl.exe /MT /nologo /c /Foobj\x64\interop.obj  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  src\SQLite.Interop\src\generic\interop.c
interop.c
        mkdir bin\x64
        cl.exe /MT /nologo /LD /Febin\x64\SQLite.Interop.dll  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  obj\x64\interop.obj  /link  /INCREMENTAL:NO  /NOLOGO  obj\x64\SQLite.Interop.res  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteCommand.bmp,System.Data.SQLite.SQLiteCommand.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteConnection.bmp,System.Data.SQLite.SQLiteConnection.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteDataAdapter.bmp,System.Data.SQLite.SQLiteDataAdapter.bmp  /VERSION:1.0
   Creating library bin\x64\SQLite.Interop.lib and object bin\x64\SQLite.Interop.exp

C:\Temp\SQLite.Interop-win>pushd C:\Temp\SQLite.Interop-win

C:\Temp\SQLite.Interop-win>CALL "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvarsamd64_arm.bat"
**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.8.0-pre.5.0
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64_arm'
        mkdir obj\arm
        rc.exe  /r /nologo /dINTEROP_RC_VERSION=1,0,118,0 /foobj\arm\SQLite.Interop.res src\SQLite.Interop\src\win\SQLite.Interop.rc
        cl.exe /MT /nologo /c /Foobj\arm\interop.obj  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  src\SQLite.Interop\src\generic\interop.c
interop.c
        mkdir bin\arm
        cl.exe /MT /nologo /LD /Febin\arm\SQLite.Interop.dll  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  obj\arm\interop.obj  /link  /INCREMENTAL:NO  /NOLOGO  obj\arm\SQLite.Interop.res  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteCommand.bmp,System.Data.SQLite.SQLiteCommand.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteConnection.bmp,System.Data.SQLite.SQLiteConnection.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteDataAdapter.bmp,System.Data.SQLite.SQLiteDataAdapter.bmp  /VERSION:1.0
   Creating library bin\arm\SQLite.Interop.lib and object bin\arm\SQLite.Interop.exp

C:\Temp\SQLite.Interop-win>pushd C:\Temp\SQLite.Interop-win

C:\Temp\SQLite.Interop-win>CALL "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvarsamd64_arm64.bat"
**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.8.0-pre.5.0
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64_arm64'
        mkdir obj\arm64
        rc.exe  /r /nologo /dINTEROP_RC_VERSION=1,0,118,0 /foobj\arm64\SQLite.Interop.res src\SQLite.Interop\src\win\SQLite.Interop.rc                                                                                                                  cl.exe /MT /nologo /c /Foobj\arm64\interop.obj  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  src\SQLite.Interop\src\generic\interop.c
interop.c
        mkdir bin\arm64
        cl.exe /MT /nologo /LD /Febin\arm64\SQLite.Interop.dll  /DWINVER=0x600  /Isrc\SQLite.Interop\src\core  /DINTEROP_PLACEHOLDER=1  /DINTEROP_EXTENSION_FUNCTIONS=1  /DINTEROP_VIRTUAL_TABLE=1  /DINTEROP_FTS5_EXTENSION=1  /DINTEROP_PERCENTILE_EXTENSION=1  /DINTEROP_TOTYPE_EXTENSION=1  /DINTEROP_REGEXP_EXTENSION=1  /DINTEROP_JSON1_EXTENSION=1  /DINTEROP_SHA1_EXTENSION=1  /DINTEROP_SESSION_EXTENSION=1  /D_CRT_SECURE_NO_DEPRECATE  /D_CRT_SECURE_NO_WARNINGS  /D_CRT_NONSTDC_NO_DEPRECATE  /D_CRT_NONSTDC_NO_WARNINGS  /DSQLITE_THREADSAFE=1  /DSQLITE_USE_URI=1  /DSQLITE_ENABLE_COLUMN_METADATA=1  /DSQLITE_ENABLE_STAT4=1  /DSQLITE_ENABLE_FTS3=1  /DSQLITE_ENABLE_LOAD_EXTENSION=1  /DSQLITE_ENABLE_RTREE=1  /DSQLITE_SOUNDEX=1  /DSQLITE_ENABLE_MEMORY_MANAGEMENT=1  /DSQLITE_ENABLE_API_ARMOR=1  /DSQLITE_ENABLE_DBSTAT_VTAB=1  /DSQLITE_ENABLE_STMTVTAB=1  /DSQLITE_WIN32_MALLOC=1  /DSQLITE_HAS_CODEC=1  /DSQLITE_OS_WIN=1  /DNDEBUG=1  /DUNICODE=1  obj\arm64\interop.obj  /link  /INCREMENTAL:NO  /NOLOGO  obj\arm64\SQLite.Interop.res  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteCommand.bmp,System.Data.SQLite.SQLiteCommand.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteConnection.bmp,System.Data.SQLite.SQLiteConnection.bmp  /ASSEMBLYRESOURCE:src\System.Data.SQLite\Resources\System.Data.SQLite\Resources\SQLiteDataAdapter.bmp,System.Data.SQLite.SQLiteDataAdapter.bmp  /VERSION:1.0
   Creating library bin\arm64\SQLite.Interop.lib and object bin\arm64\SQLite.Interop.exp
Build complete

And I did some tests (again) today with 1.0.118.0, like this:

$Architecture = 'ARM64'
$InteropPath = 'C:\DEV\Modules\PSYNCSQLite\Source\Assembly\ARM64\SQLite.Interop.dll'
$SystemDataPath = 'C:\DEV\Modules\PSYNCSQLite\Source\Assembly\System.Data.SQLite.dll'
$Architecture = $null

$code = @'
[DllImport("kernel32.dll")] public static extern IntPtr LoadLibrary(string dllToLoad);
[DllImport("kernel32.dll")] public static extern bool FreeLibrary(IntPtr hModule);
'@

If ('Internal.Helper' -as [type])
{
   Write-Verbose -Message 'The internal helper is already loaded, that might cause issues!'
   Write-Verbose -Message 'We will not add it again, but we try to use the existing internal helper instead'
}
else
{
   $null = (Add-Type -MemberDefinition $code -Namespace Internal -Name Helper)
}

# Pre-load the platform specific DLL version
$null = ([Internal.Helper]::LoadLibrary($InteropPath))

# Next, load the .NET assembly. Since the Interop DLL is already pre-loaded, all is good
$null = (Add-Type -Path $SystemDataPath)

The rest is up to your usage, in my case it's a highly customized implementation (with parts a reuse from the MySQLite module).

My Build script generates a SQLite.Interop.dll for all my platforms, like Windows on AMD64 and ARM, MacOS ARM64 (x64 is no longer my scope), or Linux on AMD64 and ARM64. Nothing fancy, but it works fine.

And again: I can provide the generic binaries described above and/or the plain source code (I might need to remove some internals only, but that will have no impact).

jhochwald commented 9 months ago

And by the way: The Build on Linux and macOS will not require Visual Studio. The GCC Tooling will be used in this case.

jhoneill commented 9 months ago

@jhochwald that's helpful., thanks.

There's a bit of a mindset of "I don't build binaries, I deliver PowerShell code", and "People should not need to trust binaries I've compiled" (Plus there are problems running tests on a platform I don't have). And in my case I've kept adding things to GetSql because I need them not as a community effort. So the world gets what I wanted rather than a product for the greatest number of people - that's how we got here, not a defence or saying that's how it should be.

Jeff and I can both look at building and distributing the binary (@jdhitsolutions it makes sense for only one of us to do the work), but I'd prefer to deliver it with instruction "Get the source from here, and follow these steps to build for your platform". These days because I can compile the few things I need with a DotNet.exe command and I do all my editing with VSCode I no longer have Visual Studio installed (I still have a licence for the full version just didn't put it on this laptop when I got it in Feb 2022).

jhochwald commented 9 months ago

@jdhitsolutions you might want to take a look at these repositories:

If you want to upgrade from sqlite-netFx 1.0.117.0 to 1.0.118.0, you need to tweak the SHA256 checksum. no further changes are required. I also tweaked the PowerShell Code (SQLite.Interop-win) a bit to avoid issues and make it a bit more robust. In short: Within package.ps1 the batch creation (staring in line 93 of the original) never really worked well for me.

On Windows I did a lot of tests with the Cross-Compile, and it seems to work fine (building the ARM/ARM64 binaries on an i7/X64). I also did many builds on native ARM64 just to ensure that it works the same way :) I do have (and use) both platforms, therefore I was able to check and test a lot on them.

For Linux, you can use a simple WSL or Docker image. On my Dev Box and the Surface Pro 9 5G I had to use the WSL, because docker never really worked on these two! To create a build on the Mac, the system needs a full blown XCode installations with the command line tools installed as well. This will bring GCC with it. Just run the package.sh and it will create a zip with all the files. Samples: SQLite.Interop-1.0.118.0-osx.ARM64.14.1.zip or SQLite.Interop-1.0.118.0-debian.AMD64.12.zip (Used Debian 12 on an ARM64). And I also tested the Linux ARM64 binaries I created on my Debian WSL on an Ampere based VM with Ubuntu, it works very well. On Windows (or Windows on ARM), the following archive will be created: SQLite.Interop-1.0.118.0-win.zip.

As I mentioned before: I never created the x64 binaries on a Mac! I should work the same way, but I never tested it (mostly because I don't need it anymore).

What do you think @jhoneill? Is this a kind of an "Get the source from here, and follow these steps to build for your platform" instruction you mentioned above? In general, the Visual Studio Community Edition is enough, therefore no licence is needed. I'm using the Community Edition only! Ok, to be honest: I'm using JetBrains Rider most of the time. I'm not using VSC much. In this case I had to.

jhochwald commented 9 months ago

And I published my changes here:

Hope this helps.

God-damnit-all commented 9 months ago

It would probably be best to come up with a Github Actions workflow to build all the different binaries, honestly.

jhochwald commented 9 months ago

It would probably be best to come up with a Github Actions workflow to build all the different binaries, honestly.

How would you do that for Windows? You need a full blown Visual Studio 2022 (at least the community edition), with some special workloads installed. But you can see what changes are needed, therefore give it try.

God-damnit-all commented 9 months ago

It would probably be best to come up with a Github Actions workflow to build all the different binaries, honestly.

How would you do that for Windows? You need a full blown Visual Studio 2022 (at least the community edition), with some special workloads installed. But you can see what changes are needed, therefore give it try.

Here's the runner they use for Windows, it has that and more: https://github.com/actions/runner-images/blob/main/images/win/Windows2022-Readme.md

jhochwald commented 9 months ago

Here's the runner they use for Windows, it has that and more: https://github.com/actions/runner-images/blob/main/images/win/Windows2022-Readme.md

Awesome! I never saw all the details (workloads), but I’m not really use GitHub for builds. Should be easy for you to automate the build as you mentioned before.

jhochwald commented 9 months ago

It would probably be best to come up with a Github Actions workflow to build all the different binaries, honestly.

How would you do that for Windows? You need a full blown Visual Studio 2022 (at least the community edition), with some special workloads installed. But you can see what changes are needed, therefore give it try.

Here's the runner they use for Windows, it has that and more: https://github.com/actions/runner-images/blob/main/images/win/Windows2022-Readme.md

And b the way: https://github.com/actions/runner-images/blob/main/images/macos/macos-13-arm64-Readme.md That image might be cool for an automated macOS build. Not the latest OS, but it’s enough to create the assembly and macOS ARM64.

rhubarb-geek-nz commented 8 months ago

I have updated my repositories to build 1.0.118.0.

SQLite.Interop and SQLite.Interop-win

SQLiteConnection is also published on the PowerShell Gallery. This demonstrates how to package all the native shared libraries in a PowerShell module.

sqlite-tools has also been updated to provide native ARM compiled tools on Windows.

I use a Windows Dev Kit 2023 to validate the Windows ARM64 builds. The macOS versions are built on a mini and the Linux builds in dockers.

I have also simplified the code to determine the Runtime Identifier by getting it directly from dotnet rather than predicting it.

jdhitsolutions commented 5 months ago

I think I finally got this working on Linux. I have not done anything with MacOS because I have no way of testing anything. I am open to PRs with MacOS support that have been thoroughly tested and verified.

rhubarb-geek-nz commented 5 months ago

Given that I have SQLiteConnection already published in the gallery with compiled native dlls I was wondering if it was worth refactoring to create the database connection with New-SQLiteConnection, then you could get rid of all of the native dll and interop code and your solution would be pure PowerShell functions and platform agnostic.

I do have a different one for PowerShell Desktop because that only has the Windows dlls and the native module loading is handled differently.

I have a separate one for Alpine but believe that I can put the alpine shared libraries under "linux-musl-x64" etc in the core module.

jdhitsolutions commented 5 months ago

@rhubarb-geek-nz In a perfect world with unlimited time and resources, that would probably make the most sense. But that is a bigger project that I can't tackle now. I have a working module that supports Windows and Linux. If someone wants to submit a PR with validated MacOS support that would be great. Even if I refactored, I still have no way to test MacOS code and I am leery of publishing something I can't run. Now, if someone wants to buy me a new Macbook Pro, I wouldn't say no. 😄

rhubarb-geek-nz commented 5 months ago

Glad you have something working.

On a source-code management front, rather than having the DLLs in the git repository it is better to have either the project, makefile or script to fetch it in the repository. Having the straight DLLs stops this being an open source project because you can't see the source used to create the dlls, you can't recreate them and you don't know their origin. The user can't see the version of the dll, the compiler or compiler options used or any patches applied to the code.

I suggest that in git you should have the scripts then attach the built binaries to the releases and of course publish in the gallery. For example in my releases for 1.0.118.0 the built binaries are attached along with a SHA256 hash to verify the integrity of the dll.

It would also allow somebody to then build an ARM or ARM64 version.

Is the DLL at assembly\SQLite.Interop.dll required given your mySQLite.psm1 module file calls Add-Type with the platform specific versions?

jdhitsolutions commented 5 months ago

I get your point about the open source question. At the very least I should include file hashes. I'll have to think about this. This module is a dependency on other projects and I don't want to force the user to have to manually build files before they can use the other module.

I suppose I could modify this module to include code to build the assemblies and ship the module with the most current build. But I have to find a balance between true open source and ease of use. I don't want to bury the user with requirements.

rhubarb-geek-nz commented 5 months ago

But I have to find a balance between true open source and ease of use.

Hopefully most users are just installing from the powershell gallery

jdhitsolutions commented 5 months ago

I am absolutely expecting that.