JSON database columns

[anonymous]

Other Python ORM ​support JSON in SQL columns:

For storing JSON in the database Pony uses the following types:

• SQLite - TEXT
• ​PostgreSQL - JSONB (binary JSON)
• ​MySQL - JSON (binary JSON, although it doesn’t have ‘B’ in the name)

Starting with the version 3.9 SQLite provides the ​JSON1 extension module. This extension improves performance when working with JSON queries, although Pony can work with JSON in SQLite even without this module.

This can be useful for very flexible, dynamic schemas with user-defined or plugin-managed custom fields, many optional (sparse) fields; array fields that can be easily queried in reports (unlike e.g. Trac's keywords or ticket custom fields) and even nested and associative fields.

Trac could also offer such a JSON database column datatype. As a first step it could just provide this for plugins.

Maybe it would also be useful to add a custom JSON column to Trac objects like milestones to make it easier to add information in plugins. Eventually custom ticket fields could be migrated to be stored like that as well, to make SQL queries for custom fields a simple JSON field access.

Maybe this is not viable, but it may be worth looking into further.

[Ryan J Ollos]

We'd accept a good patch with test coverage, but otherwise we are unlikely to add this unless there is a need for it in Trac.

Feel free to add to TracDev/ScratchPad.

[anonymous]

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

The JSON functions and operators are built into SQLite by default, as of SQLite version 3.38.0 (2022-02-22). … the JSON functions went from being opt-in with SQLite version 3.37.2 and earlier to opt-out with SQLite version 3.38.0 and later.

Python 3.11 beta release ​already includes SQLite with JSON support.

[anonymous]

I think it would be a good first step to add:

• mysql_backend._type_map​: 'json': 'json'
• postgres_backend._type_map​: 'json': 'jsonb'
• sqlite_backend._type_map​: 'json': 'text'