Page MenuHomePhabricator

Support SQLite's upcoming strict mode
Open, Stalled, LowPublicFeature

Description

SQLite 3.37.0, scheduled for release on 2021-10-20, will introduce a new feature: strict table mode. It will make it possible to create tables for which column types will be strictly enforced, bar a bit of type coercion:

sqlite> CREATE TABLE test(x INTEGER) STRICT;
sqlite> INSERT INTO foo VALUES('123');
sqlite> INSERT INTO foo VALUES('text');
Error: cannot store TEXT value in INT column foo.x

Since it resolves the long-standing problem with SQLite's types being mostly suggestions, it's something worth exploring. I imagine we could include it as an experimental option in installer before this feature becomes ubiquitous on every thinkable system configuration.

Event Timeline

MaxSem changed the task status from Open to Stalled.Aug 23 2021, 6:35 PM

Stalling until it's actually released.

Krinkle changed the task status from Stalled to Open.Aug 23 2021, 6:40 PM
Krinkle triaged this task as Low priority.
Krinkle moved this task from Untriaged to Rdbms: Non-MySQL support on the MediaWiki-libs-Rdbms board.
Krinkle changed the task status from Open to Stalled.Aug 23 2021, 6:41 PM
Krinkle moved this task from Limbo to Watching on the Performance-Team (Radar) board.

Looking at https://www.sqlite.org/draft/stricttables.html , this looks fine. I'd be careful about:
a) queries that would now require manual type casting (e.g. INSERT SELECT has this annoyance in postgres)
b) if such type casting would effect query planning (mysql has problems with this kind of thing)

SQLite 3.37.0 has since been released and it has this option as expected. I would very much support investigating it and possibly adopting it in MW, to make SQLite behave more like MySQL/MariaDB and hopefully avoid issues like T253199. I'm guessing this task no longer has to be stalled?

We can start by enabling it in CI, if the sqlite library is new enough.