--load-extension=spatialite not working with Windows

[hcarter333]

Using each of
python -m datasette counties.db -m metadata.yml --load-extension=SpatiaLite

and

python -m datasette counties.db --load-extension="C:\Windows\System32\mod_spatialite.dll"

and

python -m datasette counties.db --load-extension=C:\Windows\System32\mod_spatialite.dll

I got the error:

  File "C:\Users\m3n7es\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\datasette\database.py", line 209, in in_thread
    self.ds._prepare_connection(conn, self.name)
  File "C:\Users\m3n7es\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\local-packages\Python311\site-packages\datasette\app.py", line 596, in _prepare_connection
    conn.execute("SELECT load_extension(?, ?)", [path, entrypoint])
sqlite3.OperationalError: The specified module could not be found.

I finally tried modifying the code in app.py to read:

    def _prepare_connection(self, conn, database):
        conn.row_factory = sqlite3.Row
        conn.text_factory = lambda x: str(x, "utf-8", "replace")
        if self.sqlite_extensions:
            conn.enable_load_extension(True)
            for extension in self.sqlite_extensions:
                # "extension" is either a string path to the extension
                # or a 2-item tuple that specifies which entrypoint to load.
                #if isinstance(extension, tuple):
                #    path, entrypoint = extension
                #    conn.execute("SELECT load_extension(?, ?)", [path, entrypoint])
                #else:
                conn.execute("SELECT load_extension('C:\Windows\System32\mod_spatialite.dll')")

At which point the counties example worked.

Is there a correct way to install/use the extension on Windows? My method will cause issues if there's a second extension to be used.

On an unrelated note, my next step is to figure out how to write a query across the two loaded databases supplied from the command line:
python -m datasette rm_toucans_23_10_07.db counties.db -m metadata.yml --load-extension=SpatiaLite

[hcarter333]

The issue is still present, and the fix above still works in datasette version 1.0a3

[hcarter333]

Looking into coming up with a fix instead of a patch, (see above.)
When I run with the released code, but add a print statement to see what the value of [extension] is I get back:
['/usr/lib/x86_64-linux-gnu/mod_spatialite.so']

Which matches the input argurment to the datasette command:

c

but I'm on a Windows box, so let's go wtih:

python3 -m datasette rm_toucans.db --metadata qso_loc.yml --load-extension=C:\Windows\System32\mod_spatialite.dll --plugins-dir=plugins --template-dir plugins/templates --root

That resulted in no output whatsoever. I'm going to try wrapping the path in double quotes since I'm on Windows.

Nope, still no output. Let's try a Linux style path with double quotes.

With a Linux style path, I get the same error.
I get the same error without the double quotes.

Here's part of the issue:
`# The --load-extension parameter can optionally include a specific entrypoint.

This is done by appending ":entrypoint_name" after supplying the path to the extension

class LoadExtension(click.ParamType):
name = "path:entrypoint?"
def convert(self, value, param, ctx):
print("loadex " + value)
if ":" not in value:
return value
path, entrypoint = value.split(":", 1)
return path, entrypoint
`

Notice the dependency on ':' in that block of code from /utils/Init.py.
Since Windows paths have 'C:' routinely, that's an issue.

No fix yet.

[hcarter333]

And here's the proposed fix. I still need to for and do a pull request and all that good stuff, but the fix in /utils/init.py should be:

The following in the LoadExtension class:

        #:\ indicates we're on a Windows machine study the argument a bit more
        if ":\\" in r"%r" % value:
            path_entry = value.split(":", 2)
            if len(path_entry) < 3:
                return value
            #argument contains a Windows/DOS path and an entry point
            path = path_entry[0] + ":" + path_entry[1]
            entrypoint = path_entry[-1]
            return path, entrypoint
        if ":" not in value:
            return value
        path, entrypoint = value.split(":", 1)
        return path, entrypoint

To make things even spiffier, add the (somewhat standard?) landing path for the spatialite dll on Windows:

ala

SPATIALITE_PATHS = (
    "/usr/lib/x86_64-linux-gnu/mod_spatialite.so",
    "/usr/local/lib/mod_spatialite.dylib",
    "/usr/local/lib/mod_spatialite.so",
    "/opt/homebrew/lib/mod_spatialite.dylib",
    "C:\Windows\System32\mod_spatialite.dll",
)