Some minor issues compiling for 64-bit Windows with MSVC

[phrrngtn]

First off, thanks for putting this collection of extensions together. It is really useful to have them in one repo with consistent naming schemes and builds.

I noticed a problem earlier today when building the define extension with MSVC. I build some of the extensions from source with MSVC because I want them to be usable by a custom pysqlite3 (from https://github.com/coleifer/pysqlite3) which will run on an embedded Python client (PyXLL, a Python interpreter hosted within Excel) on Windows 64-bit. I have not done anything particularly special and built the extensions by hand and I tried the same today. It turns out that I had been using the 32-bit compiler that targets 64-bit code and everything seems to work fine for the other extensions except for define. cl src\sqlite3-define.c -I%AMALGAMATION% -link -dll -out:dist\define.dll

The error I got (from testing loading the DLL in Python via ctypes) was interesting OSError: [WinError 193] %1 is not a valid Win32 application

I was able to get it to work by using the 64-bit compiler (I am not a Windows C developer in general so I tend to use the most simple command-line rather than using projects and solution files in Visual Studio)

In digging into compilation a bit more, I found that there are some compilation issues with soundex (MSVC is not c99 compliant so some variable sized arrays had to be changed to malloc/free) and fileio (linker complained about undefined _symlink because -- presumably -- the compiler was not removing the unreachable code after the return 0)

Let me know if you are interested in having MSVC as a supported compiler and if so, how you would like it to be supported (I have a batch file that consists of a bunch of cl invocations similar to the one above).

regards, pjjH

[nalgeon]

Thank you for the detailed explanation! I'm completely fine with GCC though (it works on Windows too) and don't want to support MSVC (which I find terrible).

I build some of the extensions from source with MSVC because I want them to be usable by a custom pysqlite3 which will run on an embedded Python client on Windows 64-bit.

Why not then use Windows 64-bit builds from sqlean release?

[phrrngtn]

Why not then use Windows 64-bit builds from sqlean release?

I use the builds from the sqlean release for experimentation via the sqlite shell on Windows and Linux (via WSL) and Mac. They work fine and are very convenient.

My production environment is 64-bit Excel on Windows 10 so the call hierarchy will look something like Excel -> PyXLL (a dynamically loadable Excel extension that hosts a Python interpreter) -> pysqlite3 (a Python dynamically loadable extension which contains a statically linked SQLite) -> dynamically loaded SQLite extensions written in C. There is so much potential for things to go wrong due to a) one does not know what else Excel may have loaded including dynamically linked SQLite DLL and b) the stressful ways in which ones Python code can be called due to Excel's recalculation engine.

Compiling my own pysqlite3 with a statically linked SQLite where I have precise control over the compilation options has really improved the reliability of the system. I am constrained to use MSVC for building (Python) extensions because that is the ABI of our Python environment. To avoid potential conflict with other dll names, I sometimes prefix an extension with a prefix to disambiguate it from other dlls (i.e. perhaps someone else has a 'define.dll' already loaded) so it is very useful to control the compilation. At some point, I will do the development work to compile in all the extensions into the SQLite I link with pysqlite3 and thus the MSVC requirement for Python will transitively apply to the extension code.

I don't blame you for not wanting to support MSVC!

[nalgeon]

Whoa, that's quite a process you have there! Thanks for the explanation. Sorry for not supporting MSVC - hope you'll be able to merge future changes into your fork without too much trouble.

[phrrngtn]

I think I will be fine. It is very likely that I will combine this with my local pysqlite3 build for reasons outlined below.

The 'sugar rush' of the high performance is addictive! Trust me, unless I was enjoying the payoff, I would not go to such lengths with such a multi-component architecture with so many points of potential failure.

I see single-digit millisecond runtimes for a bunch of the SQLite queries e.g. a user-defined function that takes a search term and queries against a SQLite FTS index. The VSV extension allows me to preview/sample contents of files and restrict it to rows that have keys contained in a local Excel table.

For a production environment, I would disable extension loading in SQLite (to make the security attack surface much smaller) and compile in all the extensions into pysqlite3 (and the distribution of that can be managed within the corporate environment)

[nalgeon]

Great plan!

[phrrngtn]

FYI, I was able to achieve a working setup by modifying the pysqlite3 setup.py so that it compiled the files (for the various C extensions that either work directly under MSVC or I could trivially get to work by rewriting anything C99-specific with older equivalent idioms. It was then a case of putting in an extern declaration for each of the extension entry points and then adding the function pointers to the array initializer of builtin extensions.

xref: https://www.sqlite.org/loadext.html

To statically link your extension, simply add the -DSQLITE_CORE compile-time option.