Added configuration for using specific SQLite versions.

[laymonage]

Fixes #11, built on top of #49.

There are a few caveats as noted in the README. Will add more details in PR comments.

Marking as draft for now, as ideally the SpatiaLite service should also pick up the SQLite version, and possibly allow for a specific SpatiaLite version. I spent hours digging into it but I encountered either segfaults, uninitialized DB errors, or missing library errors.

Here's my WIP patch for SpatiaLite if you want to look into it, but please note that it contains commands that are redundant as I'm still experimenting different combinations.

Details

diff --git a/.env b/.env
index c1c4223..c396b59 100644
--- a/.env
+++ b/.env
@@ -6,6 +6,9 @@ ORACLE_VERSION=23.5.0.0
 POSTGRESQL_VERSION=14
 POSTGIS_VERSION=3.1
 SQLITE_VERSION=
+SPATIALITE_VERSION=
 SQLITE_CFLAGS="-DSQLITE_ENABLE_DESERIALIZE \
                -DSQLITE_ENABLE_JSON1 \
+               -DSQLITE_ENABLE_RTREE \
+               -DSQLITE_ENABLE_COLUMN_METADATA=1 \
                -DSQLITE_MAX_VARIABLE_NUMBER=32766"
diff --git a/compose.yml b/compose.yml
index 301fb3e..1c5f83c 100644
--- a/compose.yml
+++ b/compose.yml
@@ -287,7 +287,7 @@ services:
       - DATABASE_ENGINE=django.db.backends.postgresql
       - DATABASE_HOST=postgresql-db

-  sqlite:
+  sqlite: &sqlite
     <<: *base
     image: django-docker-box:${PYTHON_IMPLEMENTATION}-${PYTHON_VERSION}-sqlite${SQLITE_VERSION}
     pull_policy: never
@@ -310,7 +310,6 @@ services:
             else
               cp .libs/libsqlite3.so /tmp/
             fi
-            rm -rf /tmp/sqlite
           fi
         EOF
         SHELL ["/bin/bash", "-c"]
@@ -369,9 +368,54 @@ services:
       - DATABASE_HOST=postgresql-gis-db

   sqlite-gis:
-    <<: *base
-    depends_on:
-      <<: *depends-on-caches
+    <<: *sqlite
+    image: django-docker-box:${PYTHON_IMPLEMENTATION}-${PYTHON_VERSION}-sqlite${SQLITE_VERSION}-spatialite${SPATIALITE_VERSION}
+    pull_policy: never
+    build:
+      context: .
+      dockerfile_inline: |
+        FROM django-docker-box:${PYTHON_IMPLEMENTATION}-${PYTHON_VERSION}-sqlite${SQLITE_VERSION}
+        SHELL ["/bin/bash", "-o", "errexit", "-o", "nounset", "-o", "pipefail", "-o", "xtrace", "-c"]
+        # Only compile SpatiaLite if a version is specified.
+        USER root
+        RUN apt-get update --quiet --yes && apt-get install --no-install-recommends --yes libfreexl-dev libproj-dev
+        RUN <<EOF
+          if [[ "${SQLITE_VERSION}" && "${SPATIALITE_VERSION}" ]]; then
+            cd /tmp/sqlite
+            make install
+            cd /tmp
+            curl -Lo libspatialite.tar.gz https://www.gaia-gis.it/gaia-sins/libspatialite-sources/libspatialite-${SPATIALITE_VERSION}.tar.gz
+            tar xzf libspatialite.tar.gz
+            cd libspatialite-${SPATIALITE_VERSION}
+            ls -al
+            curl -Lo config.guess https://git.savannah.gnu.org/cgit/config.git/plain/config.guess
+            curl -Lo config.sub https://git.savannah.gnu.org/cgit/config.git/plain/config.sub
+            ls -al
+            chmod 755 config.guess
+            chmod 755 config.sub
+            ./configure
+            make
+            ls -al
+          fi
+        EOF
+        RUN <<EOF
+          cd /tmp/libspatialite-${SPATIALITE_VERSION}/
+          ls -al src/.libs
+          apt-get remove -y libsqlite3-mod-spatialite
+          make install
+        EOF
+        RUN cp /tmp/libspatialite-${SPATIALITE_VERSION}/src/.libs/mod_spatialite.so /tmp/
+        RUN ls -al /tmp
+        USER django:django
+        SHELL ["/bin/bash", "-c"]
+        ENV LD_PRELOAD="$$LD_PRELOAD ${SPATIALITE_VERSION:+/tmp/mod_spatialite.so}"
+      args:
+        - PYTHON_IMPLEMENTATION=${PYTHON_IMPLEMENTATION}
+        - PYTHON_VERSION=${PYTHON_VERSION}
+        - SQLITE_VERSION=${SQLITE_VERSION}
+        - SQLITE_CFLAGS=${SQLITE_CFLAGS}
+      additional_contexts: *additional-contexts
+
     environment:
       - DATABASE_ENGINE=django.contrib.gis.db.backends.spatialite

diff --git a/packages.txt b/packages.txt
index 86f8505..763c65d 100644
--- a/packages.txt
+++ b/packages.txt
@@ -1,5 +1,7 @@
+autoconf
 binutils
 build-essential
+curl
 default-libmysqlclient-dev
 default-mysql-client
 gdal-bin

I'm not a Docker expert, so any suggestions to improve the setup would be very much appreciated.

[charettes]

Thanks so much for your work on this @laymonage!

From reviewing your changes I think the tradeoff of sticking SQLITE_CFLAGS to flags that work with the currently bundled version of Python in the image makes sense particularly considering the amount of work that would be required to compile a compatible version of Python otherwise.

This is way better than what we ever had when I was pulling my hairs compiling multiple versions of SQLite and managing them to pinpoint the exact version that regressed and filed https://github.com/orf/django-docker-box/issues/25.

I'll give it a shot tomorrow trying to spike a solution to the the SQLite max query parameters issue but from a my relative exposure to Docker everything seemed right.

[ngnpope]

Amazing! I'll try to have a play with this next week.

[charettes]

Thanks @laymonage, I was able to play with it over the past week working on features such as UPDATE FROM which is SQLite 3.33.0 and I never ran into issues so unless there are strong objection I'd be happy to proceed with a merge here as we can always go back and make adjustments if needed.