Raise SQLite version requirement to 3.26 in Drupal 9

Problem/Motivation

Background context for Drupal 8

• Drupal 8.0 was released in Nov. 2015. At that time, for people who wanted to install it on SQLite, the stated minimum required SQLite version was 3.6.8. If this were true, then this would have meant that all still-supported versions at that time of Debian (6, 7, 8), Ubuntu (12.04, 14.04), and RHEL/CentOS (6, 7), shipped with a compatible SQLite version. Debian 6 shipped with 3.7.3. Ubuntu 12.04 shipped with 3.7.9. And RHEL/CentOS 6 shipped with 3.6.20.
• However, DrupalCI did not test on SQLite 3.6.8. It only tested on 3.8.2. So after releasing Drupal 8.0, we learned that Drupal could not in fact install on anything less than 3.7.11 → , at which point we raised the stated minimum → to that, and have not raised it since.
• Per above, this raised the minimum above what shipped with Debian 6, Ubuntu 12.04, and RHEL 6.
• In other words, in reality, instead of Drupal 8.0 working with the SQLite version on all supported versions of the major Linux distros, it only worked on the latest versions of Ubuntu and RHEL/CentOS and with the latest and latest-1 of Debian.
• This was mitigated by the fact that by default PHP was compiled with its own private copy of SQLite, whose version was above 3.7.11 since PHP 5.5.11. In other words, the only people who would have encountered a problem on Debian 6, Ubuntu 12.04, or RHEL/CentOS 6, were people who custom compiled PHP to link against their system's SQLite instead of PHP's bundled copy. This might explain why we didn't get many reports of the problem other than #2605594: Change minimum required sqlite version to 3.7.11 → .

Considerations for Drupal 9

• PHP 7.3 continues to be compiled by default with its own bundled copy of SQLite (presently at version 3.28 since PHP 7.3.8).
• However, PHP 7.4 removed its bundled copy and now compiles against whatever SQLite version is on the operating system.
• If we want Drupal 9.0 to work on SQLite with all still-supported versions of Debian, Ubuntu, and RHEL/CentOS, then that would include RHEL/CentOS 7, which would only let us raise our minimum to 3.7.17.
• However, then we'd lose out on some very helpful features, such as json support (since 3.9) and UPSERT support (since 3.25).
• Alternatively, if we're willing to restrict SQLite support to the version that's shipped with the latest version of Debian, Ubuntu, RHEL/CentOS, and MacOS then we can raise the minimum to 3.26 and benefit from UPSERT support, because RHEL/CentOS 8 ships with SQLite 3.26, Debian 10 ships with SQLite 3.27, Mac Catalina ships with 3.28, and Ubuntu 20.04 will ship with 3.30 or higher.

Proposed resolution

• For Drupal 9, raise the SQLite minimum to 3.26.
• PHP 7.3 users will be fine, since they'll be using PHP's bundled copy that's at 3.28.
• PHP 7.4 users will only be able to use SQLite if they're on the latest version of their respective operating system, or if they install SQLite from a different package repository than what's on their operating system. This includes Mac Mojave users who will need to either upgrade to Catalina or use PHP 7.3 or find some other way to install a higher SQLite version.
• Ubuntu 18.04 users (including users of downstream distros like Mint and ElementaryOS) will be particularly impacted during the next few months, because Ubuntu 20.04 won't be released until April and the downstream distros will lag another few months after that. However, they too can use PHP 7.3 until they're able to upgrade their OS.
• People with Drupal codebases (modules, distributions, or sites) on GitHub that they test with TravisCI will be affected, since TravisCI provides Ubuntu 18.04 but not yet 20.04. However, as above, they can run those tests on PHP 7.3. Or, if they must test with PHP 7.4, they can add the following to their .travis.yml:
  

  dist: bionic
  
  before_install:
    - sudo echo "deb http://archive.ubuntu.com/ubuntu focal main restricted universe multiverse" >> /etc/apt/sources.list
    - sudo apt-get update
    - sudo apt-get -y install sqlite3/focal
  

  I tested the above on TravisCI and it works (it successfully installs Ubuntu 20.04 (focal)'s SQLite version on Ubuntu 18.04 (bionic)), but I don't know if it has any unwanted side effects.

• Note that all of the above only affects people wanting to use SQLite. People affected by the above can also simply choose to use MySQL or PostgreSQL. However, SQLite is convenient for running tests and installing a site with Quick Start → .

Remaining tasks

• Decide on if raising to 3.26 is acceptable.
• Add a test environment that can test it: #3112396: DrupalCI environment needed with PHP 7.4 and SQLite >= 3.26 →
• Commit a patch that raises the requirement.

Release notes snippet

Raised the minimum SQLite version to 3.26 in Drupal 9.