JSON database columns
|Reported by:||anonymous||Owned by:|
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.