Re:JSON and Virtual Columns in SQLite
by jitl on Monday May 16, 2022 @02:34PM
(#31401543)
There are some object properties the clients need to query, and other object properties the clients don’t care to query, but are needed to render the data in the UI, so it still needs to be stored locally.
For example, today we run queries on "parent page ID", so that is a normalized parent_id column in SQLite, instead of being stored in the JSON column.
We don't currently run queries on "text color" or "image width" object properties, but we still need to store that data in the cache to render a block. So, it's fine to leave those things in a JSON column. If we need to run a query like "find all images that are full-width" in the future, we can add a new image_width column/virtual column/index at that time.
For example, today we run queries on "parent page ID", so that is a normalized parent_id column in SQLite, instead of being stored in the JSON column.
We don't currently run queries on "text color" or "image width" object properties, but we still need to store that data in the cache to render a block. So, it's fine to leave those things in a JSON column. If we need to run a query like "find all images that are full-width" in the future, we can add a new image_width column/virtual column/index at that time.