[policy] Decide whether to require json support for all database drivers for Drupal 10

Interesting point about official PHP docker images, they based on debian:buster and alpine, so both should be fine for sqlite

The question that I have is: How easy is it for Windows and Mac users to get SQLite on their systems with the json1 extension? (and also with the DSQLITE_MAX_VARIABLE_NUMBER setting being higher then/equal 250000?)
Does anybody have any idea how many people on Windows and Mac use SQLite as a database for Drupal to test it out?

I have asked the same question on #3107155-11: Raise SQLite version requirement to 3.26 in Drupal 9 and @andypost answered with

I bet windows will use WSL in d9 era, so some Linux-distro, on MacOS this limit is higher iirc

I'd love to have JSON support in all database drivers, but I'd say a lot of my day to day involves Drupal on SQLite for local development and contrib development. It sounds like #7 shows it is (most likely) a non problem on macOS?

We're already planning to increase requirements for MySQL, postgres and sqlite to version that support JSON, and the required versions for contrib drivers don't seem onerous.

The only question for me is whether it's worth officially requiring support in the database drivers before we have support for it in Drupal. i.e. we'd need to add JSON support to the database layer, and then we'd need an implementation in core. We only really need to require JSON once we have a non-optional core implementation. I guess there's an argument that it's better to add the requirement prior to a major version rather than during if we expect to have an implementation added prior to 10.x though.

https://www.drupal.org/docs/9/how-drupal-9-is-made-and-what-is-included/... is the requirements page, it could be a footnote there maybe?

Both a CR and release not seems sensible for this one.

A slightly sad thing is that postgres doesn't support generated columns until version 12 (only just released) and SQLite in 3.31.0 (only just released too). MySQL supports this since 5.7. This is important because it we want to have indexes on anything in the json this would make that possible. Considering configuration - that'd allow us to remove the awfulness of storing configuration lookup keys in state. Here's an article that explains what generated columns are and why they are important... https://www.percona.com/blog/2016/03/07/json-document-fast-lookup-with-m...

Btw sqlite 3.31 adds uuid function, so follow up for uuid fields needs to be filed

@effulgentsia that does look interesting - that'd be great for config. Shame MySQL doesn't offer an equivalent - apart from the generated columns thing. Will be fun to add sort for json indexes in an db agnostic way... the things you point to make it look easier for SQLite and Postgres.

We should add #19 to whatever the 10.x database requirements issue is (I think there already is one, or at least a meta with a placeholder).

Looks #21-22 are more about related

So we still need a release note and change record here, but also this seems a bit late now we're already close to RC - we should really have documented it when the decision was made.

As it goes to 10.0 it can bet on php-sqlite will linked to system library

Updating the issue summary. We should try to reach a decision here sooner than later so we can bump Drupal 10's database requirements as soon as it opens.

+1

For >99% of the Drupal sites there will be a free performance increasement. I am not sure how much that increasement will be. For the <1% of the sites that run Microsoft SQL Server lower then version 16 or SQLite with PHP 7.3, they will not get support for Drupal 10. That will only be a handful of sites. Also AFAIK PHP 7.3 will not be supported on Drupal 10. For me this is an easy choose. I fully support having json support as a requirement for all databases in Drupal 10.

Tried a bit of an issue summary update.

Just a quick note while I have some time and thinking about this.

We don't need any abstraction for simply storing JSON since it's text. However we will need an abstraction layer for querying or parsing JSON data structures in SQL. A mechanism for accessing nested properties in Condition or Select fields needs to be worked on. I don't think that should block requiring drivers to implement a json (or extended json) data type. Giving the ability to store JSON is a big DX win in my opinion.

+1 from me as well. Whats left before RTBC?

As per the tags a release note snippet for the issue summary and a change record for people to have time to prepare would be needed :)

BTW looking at the proposed resolution to write up the change record, it seems like the proposed MySQL/MariaDB and PostgreSQL requirements are already in place in Drupal 9. So which database requirements are actually affected by this proposal?

The main problem would be SQLite. Anything else are in contrib drivers

From the issue summary:

For SQLite, version isn't an issue, but we'd have to require that it be compiled with the json1 extension. This isn't the default, but (some? all?) Linux distributions include it in their build of it. For example, Ubuntu does. From PHP 7.4 sqlite uses the library on the system. Drupal 10 will require at least PHP 8.0

https://www.sqlite.org/json1.html

Updating the issue summary to reflect the MySQL and PostgreSQL status as done in Drupal 9 already.

Updated the issue summary based on #37.

Nitpick IS update.

+1 from me for adding the json1 extension as required for SQLite in D10.

Created the followup issue for adding the json1 extension requirements test in Drupal 10 for SQLite. See: #3192487: Warn about upcoming JSON database support requirement in Drupal 9.
Created the CR.
The issue is now for me RTBC.

Added a release notes snippet.

I added versions for Oracle and SQL Server to the change record.

I think eventually this would impact usages of Redis and Memcache. Does it make sense to mention that in this change record?

@mradcliffe if we implement/require JSON support in storage that has memcache/redis implementations we can add a change record in those issues with what needs to be done (if something does), I don't think we need to mention it yet when it's still theoretical.

I've moved #3192487: Warn about upcoming JSON database support requirement in Drupal 9 back to 9.2.x since I think we can do most of that issue in a minor release and then just tweak it for Drupal 10.

Since that follow-up is open, I think we can mark this fixed.