Edgewall Software

Opened 4 years ago

Closed 4 years ago

Last modified 7 months ago

#13118 closed enhancement (wontfix)

JSON database columns

Reported by: anonymous Owned by:
Priority: normal Milestone:
Component: database backend Version:
Severity: normal Keywords: generic tracobject
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:


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.

Attachments (0)

Change History (4)

comment:1 by Ryan J Ollos, 4 years ago

Milestone: undecided

comment:2 by Ryan J Ollos, 4 years ago

Milestone: undecided
Resolution: wontfix
Status: newclosed

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.

Last edited 4 years ago by Ryan J Ollos (previous) (diff)

comment:3 by anonymous, 7 months ago


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.

comment:4 by anonymous, 7 months ago

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

Modify Ticket

Change Properties
Set your email in Preferences
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.