Added configuration for using specific SQLite versions. #50

laymonage · 2025-01-29T00:41:58Z

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.

laymonage · 2025-01-29T00:56:58Z

README.md

+  included in the `sqlite` module. A mismatch in the module and the dynamically
+  loaded library may result in Python failing to load, which may happen if we
+  use an SQLite version that is older than the system version.
+- Debian and Ubuntu use a custom `CFLAGS` variable to compile their distributed


https://sources.debian.org/src/sqlite3/3.40.1-2%2Bdeb12u1/debian/rules/#L43-L61

https://git.launchpad.net/ubuntu/+source/sqlite3/tree/debian/rules?h=applied/ubuntu/focal#n39

laymonage · 2025-01-29T00:57:42Z

README.md

+
+SQLite is normally bundled in the Python installation using the version
+available on the system where Python is compiled. We use the Python Docker image
+based on Debian `bookworm`, which has SQLite 3.40.1.


I've updated https://code.djangoproject.com/wiki/SupportedDatabaseVersions to better reflect our policy for SQLite.

laymonage · 2025-01-29T00:59:43Z

.env

+SQLITE_CFLAGS="-DSQLITE_ENABLE_DESERIALIZE \
+               -DSQLITE_ENABLE_JSON1 \
+               -DSQLITE_MAX_VARIABLE_NUMBER=32766"


I explained this before in more detail in django/django#18899 (comment), but here's a breakdown of each flag:

SQLITE_ENABLE_DESERIALIZE

This flag was not enabled by default until SQLite 3.36. Without this flag, you'll encounter an error like pyenv/pyenv#2625.

This is because Python checks for the availability of the deserialize functions at compile time.

If we don't want to add this flag, we can work around this by using an older version of Python that didn't have support for serialize() (< 3.11), or by using bullseye (which ships with 3.34.1 and thus Python 3.12 wasn't built to have SQLite deserialize functions). Or, of course, compiling Python from source ourselves.

SQLITE_ENABLE_JSON1

This flag was not enabled by default until SQLite 3.38.

We have the following test that fails if you run it with a database backend that does not support JSONField, because it's missing the @skipUnlessDBFeature decorator:

https://github.com/django/django/blob/8a6b4175d790424312965ec77e4e9b072fba188b/tests/schema/tests.py#L2425-L2443

You can verify this by setting SQLITE_VERSION to a version < 3.38 and removing -DSQLITE_ENABLE_JSON1 from SQLITE_CFLAGS, or by using a version >= 3.38 and adding -DSQLITE_OMIT_JSON.

Happy to raise a ticket and PR for that.

Edit: I've raised a ticket https://code.djangoproject.com/ticket/36156#ticket

SQLITE_MAX_VARIABLE_NUMBER

There are a few tests that would fail when it does ContentType.objects.all().delete() due to a row count query exceeding the variable limit. This only happens when the whole test suite is run, but not when the failing tests are run in isolation (likely because other tests would create more ContentType instances).

This flag defaulted to 999 in < 3.32 (and 32766 in >= 3.32.0).

This is something that Debian/Ubuntu has customized, even in 3.31.

Related:

Fixed #36118 -- Accounted for multiple primary keys in bulk_update max_batch_size. django#19088 (comment)

https://code.djangoproject.com/ticket/36143

laymonage · 2025-01-29T01:02:09Z

compose.yml

+            if [ -f libsqlite3.so ]; then
+              cp libsqlite3.so /tmp/
+            else
+              cp .libs/libsqlite3.so /tmp/
+            fi
+            rm -rf /tmp/sqlite


The "configure" script underwent major refactoring in 3.48.0: https://www.sqlite.org/releaselog/3_48_0.html

As a result, the compiled library is placed directly in the source root. In older versions, it's placed inside a .libs directory.

The rm -rf is just a cleanup step to keep the image size small.

laymonage · 2025-01-29T01:22:21Z

compose.yml

@@ -285,6 +289,38 @@ services:

  sqlite:
    <<: *base
+    image: django-docker-box:${PYTHON_IMPLEMENTATION}-${PYTHON_VERSION}-sqlite${SQLITE_VERSION}
+    pull_policy: never


We don't push the image to Docker Hub. This ensures doing FROM django-docker-box:${PYTHON_IMPLEMENTATION}-${PYTHON_VERSION} won't make Docker try to pull it from Docker Hub if the image isn't available locally.

Could also try build instead, but if I recall correctly it doesn't work in this context unless you already built the base image. Using build is even worse as it will run the build process even if the image has already been built. It does use the cache, but it also adds a few seconds to the run, compared to never that would just skip it.

charettes · 2025-01-29T05:31:47Z

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 orf#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.

laymonage added 4 commits January 27, 2025 05:03

Bump default Python version to 3.12

3bd0e30

Added sqlite-lib service to test against specific SQLite versions.

d584e91

Consolidated sqlite-lib service into sqlite.

975b9d3

Fixed libsqlite3.so output path on SQLite 3.48.0+.

9b59095

laymonage commented Jan 29, 2025

View reviewed changes

laymonage marked this pull request as draft January 29, 2025 01:02

laymonage mentioned this pull request Jan 29, 2025

Fixed #36118 -- Accounted for multiple primary keys in bulk_update max_batch_size. django/django#19088

Merged

laymonage commented Jan 29, 2025

View reviewed changes

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Added configuration for using specific SQLite versions. #50

Added configuration for using specific SQLite versions. #50

laymonage commented Jan 29, 2025 •

edited

Loading

laymonage Jan 29, 2025

laymonage Jan 29, 2025

laymonage Jan 29, 2025 •

edited

Loading

laymonage Jan 29, 2025

laymonage Jan 29, 2025

charettes commented Jan 29, 2025

Added configuration for using specific SQLite versions. #50

Are you sure you want to change the base?

Added configuration for using specific SQLite versions. #50

Conversation

laymonage commented Jan 29, 2025 • edited Loading

laymonage Jan 29, 2025

Choose a reason for hiding this comment

laymonage Jan 29, 2025

Choose a reason for hiding this comment

laymonage Jan 29, 2025 • edited Loading

Choose a reason for hiding this comment

SQLITE_ENABLE_DESERIALIZE

SQLITE_ENABLE_JSON1

SQLITE_MAX_VARIABLE_NUMBER

laymonage Jan 29, 2025

Choose a reason for hiding this comment

laymonage Jan 29, 2025

Choose a reason for hiding this comment

charettes commented Jan 29, 2025

laymonage commented Jan 29, 2025 •

edited

Loading

laymonage Jan 29, 2025 •

edited

Loading

`SQLITE_ENABLE_DESERIALIZE`

`SQLITE_ENABLE_JSON1`

`SQLITE_MAX_VARIABLE_NUMBER`