Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add --load-extension option to datasette for loading extra SQLite extensions #110

Closed
simonw opened this issue Nov 16, 2017 · 2 comments
Closed

Comments

@simonw
Copy link
Owner

simonw commented Nov 16, 2017

This would allow users with extra SQLite extensions installed (like spatialite) to load them at runtime.

Inspired by this comment: #46 (comment)

simonw pushed a commit that referenced this issue Nov 16, 2017
Allows loading of SQLite extensions. Refs #110.
@simonw
Copy link
Owner Author

simonw commented Nov 16, 2017

Here's how I tested this.

First I downloaded and started a docker container using https://hub.docker.com/r/prolocutor/python3-sqlite-ext - which includes the compiled spatialite extension. This downloads it, then starts a shell in that container.

docker run -it -p 8018:8018 prolocutor/python3-sqlite-ext:3.5.1-spatialite /bin/sh

Installed a pre-release build of datasette which includes the new --load-extension option.

pip install https://static.simonwillison.net/static/2017/datasette-0.13-py3-none-any.whl

Now grab a sample database from https://www.gaia-gis.it/spatialite-2.3.1/resources.html - and unzip and rename it (datasette doesn't yet like databases with dots in their filename):

wget http://www.gaia-gis.it/spatialite-2.3.1/test-2.3.sqlite.gz
gunzip test-2.3.sqlite.gz
mv test-2.3.sqlite test23.sqlite

Now start datasette on port 8018 (the port I exposed earlier) with the extension loaded:

datasette test23.sqlite  -p 8018 -h 0.0.0.0 --load-extension /usr/local/lib/mod_spatialite.so

Now I can confirm that it worked:

http://localhost:8018/test23-c88bc35?sql=select+ST_AsText%28Geometry%29+from+HighWays+limit+1

test23

If I run datasette without --load-extension I get this:

datasette test23.sqlite  -p 8018 -h 0.0.0.0

test23_and_turn_on_auto-escaping_in_jinja_ _simonw_datasette_82261a6

@simonw simonw closed this as completed Nov 16, 2017
@simonw simonw added this to the SQLite extensions edition milestone Nov 16, 2017
@simonw
Copy link
Owner Author

simonw commented Nov 16, 2017

To finish up, I committed the image I created in the above so I can run it again in the future:

docker commit $(docker ps -lq) datasette-sqlite

Now I can run it like this:

docker run -it -p 8018:8018 datasette-sqlite datasette /tmp/test23.sqlite  -p 8018 -h 0.0.0.0 --load-extension /usr/local/lib/mod_spatialite.so

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant