mORMot Open Source

DonAlfredo

Member

Registered: 2023-10-31

Posts: 32

Generated columns

https://sqlite.org/gencol.html

As a frequent user of collections to store data as a [nested] json array , I need a better (faster) method of searching and retrieving data from inside a json field.
Generated columns can be used for faster retrieval (also by indexing).

Might there be a way to use generated columns through the mORMot ?

Example pure sql:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    data TEXT   -- contains JSON like {"type": "login", "user": {"id": 123}, "answer": 42, ...}
);
ALTER TABLE events 
ADD COLUMN event_type TEXT 
GENERATED ALWAYS AS (json_extract(data, '$.type')) VIRTUAL;

ALTER TABLE events 
ADD COLUMN user_id INTEGER 
GENERATED ALWAYS AS (json_extract(data, '$.user.id')) VIRTUAL;

CREATE INDEX idx_events_type ON events(event_type);
CREATE INDEX idx_events_user_id ON events(user_id);

SELECT * FROM events 
WHERE user_id = 123 
  AND event_type = 'login';

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,434

Website

Re: Generated columns

Yes, generated columns are very powerful.

Do you have any idea, how we could define it at the TOrm properties level?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,434

Website

Re: Generated columns

You can already use it if you generate the SQL ALTER TABLE ADD COLUMN GENERATED by hand, and don't include the calculated fields in the fields bitmap.

I guess we would need to add a method to change TOrmPropertiesAbstract.SimpleFieldsBits/SimpleFieldsIndex/SimpleFieldsCount[] for ooUpdate/ooInsert, and exclude the calculated fields from UPDATE/INSERT statements.

DonAlfredo

Member

Registered: 2023-10-31

Posts: 32

Re: Generated columns

As this feature request might be a corner case, changes into the mORMot should be minimal I guess.
What might be needed (and this also follows your proposal): limit the mORMot automation to allow raw SQL for these generated columns.
Followed by an evaluation of (speed) improvements and perhaps some dedicated inclusion into TORM.