SQLite load_extension syntax doesn't work Ubuntu 18.04

[ianthetechie]

The current syntax for loading the spatialite module does not appear to work on Ubuntu 18.04. It appears to be looking for a filename, not the base object library name. See a transcript of the session below run on my build server for an illustration of the syntax currently employed in the codebase vs the working syntax.

sqlite> select load_extension('mod_spatialite');
Error: mod_spatialite: cannot open shared object file: No such file or directory
sqlite> select load_extension('mod_spatialite.so');

sqlite> 

[danpat]

The sqlite docs at https://www.sqlite.org/c3ref/load_extension.html say:

The sqlite3_load_extension() interface attempts to load an SQLite extension library contained in the file zFile. If the file cannot be loaded directly, attempts are made to load with various operating-system specific extensions added. So for example, if "samplelib" cannot be loaded, then names like "samplelib.so" or "samplelib.dylib" or "samplelib.dll" might be tried also.

The module loading code lives here:

Ubuntu 16.04: sqlite 3.11.0 https://github.com/mackyle/sqlite/blob/version-3.11.0/src/loadext.c#L432
Ubuntu 18.04: sqlite 3.22.0 https://github.com/mackyle/sqlite/blob/version-3.22.0/src/loadext.c#L500-L509
sqlite latest https://github.com/mackyle/sqlite/blob/master/src/loadext.c#L515-L522

There was a change visible in 3.22.0 around module loading that has been reverted in the latest sqlite3 code. Maybe it's the source of the problem here.

Honestly, I think this is a bug in the version of sqlite that's bundled with Ubuntu 18.04. The recipe @ianthetechie supplied works just fine with Ubuntu 16.04, which is a big red flag in my book that the Ubuntu packages are messed up.

I don't think Valhalla should be in the business of trying operating-system specific extensions. sqlite documents that it takes care of this, and it used to work just fine (and currently works fine on macOS and Ubuntu 16.04 that I've tested). This needs to be upstreamed to either the Ubuntu or sqlite authors I think.

[ianthetechie]

That makes sense. Good catch. I knew it was expecting a file name (more or less), but overlooked that line in the docs.

[missinglink]

This has been fixed in 18.10, for release 18.04 you can create a symlink:

ln -s /usr/lib/x86_64-linux-gnu/mod_spatialite.so /usr/lib/x86_64-linux-gnu/mod_spatialite

[missinglink]

See pelias/docker#28 (comment) for more info

[ianthetechie]

Awesome. And yes, the symlink is what we have been doing. Thanks for the update.

[kevinkreiser]

Reopening this. We have code for doing this in a bunch of places throughout mjolnir. It looks appx like:

    // loading SpatiaLite as an extension
    sqlite3_enable_load_extension(db_handle, 1);
#if SQLITE_VERSION_NUMBER > 3008007
    sql = "SELECT load_extension('mod_spatialite')";
#else
    sql = "SELECT load_extension('libspatialite')";
#endif
    ret = sqlite3_exec(db_handle, sql.c_str(), nullptr, nullptr, &err_msg);
    if (ret != SQLITE_OK) {
      LOG_ERROR("load_extension() error: " + std::string(err_msg));
      sqlite3_free(err_msg);
      sqlite3_close(db_handle);
      return;
    }

Imho we should pull this out into a utility function that takes the db_handle. If we hit the != SQLITE_OK block we can just retry the query with the .so extension. We could possibly even do triple fail over and not need the #if version check above.

[missinglink]

@kevinkreiser I've seen the same failover logic in this lib: https://github.com/shaxbee/go-spatialite/blob/master/spatialite.go#L29