#1 2022-05-26 17:07:01

Stemon63
Member
Registered: 2016-10-24
Posts: 49

TORM: Problem... too many fields...

Hi All

I have a big problem now on a TORM class: it have 400 fields, so I have a "too many fields" error on create. 256 is the max number.
But italian tax forms have a big number of "simple" fields (not tables) to manage for every Torm.
Any hint for avoid "sharding" of the table in "sub-tables "? This would increase the complexity of managing simple tables but with many fields...
Thanks in advance!

Offline

#2 2022-05-26 17:10:19

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,206
Website

Re: TORM: Problem... too many fields...

The 256 maximum comes from the compiler itself: it is the maximum number of items in a set.
And we use sets to map the fields everywhere within the ORM.

So, for your purpose, if you have a lot of fields, I would add some variant fields, and store it them a TDocVariantData with some JSON, or just some RawJson fields.
The you can use SQlite3 functions to retrieve some values from the fields.

Offline

#3 2022-05-26 17:43:55

Stemon63
Member
Registered: 2016-10-24
Posts: 49

Re: TORM: Problem... too many fields...

Uhm... thanks AB.
But, alternatively, if I want to manage a big class with 400 fields IN MEMORY (derived from Torm or another mormot) is possible in some way?
If I use a big Delphi class (in memory) it works without problem (collecting data from multiple tables), but  I would prefer a class derived from mormot to exploit its potential.
Thanks a lot

Offline

#4 2022-05-26 18:41:23

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,206
Website

Re: TORM: Problem... too many fields...

With 400 fields, I would use a "business" class with the 400 fields, but the TOrm using some JSON storage fields to gather most of them.

Offline

#5 2022-05-27 10:31:17

Stemon63
Member
Registered: 2016-10-24
Posts: 49

Re: TORM: Problem... too many fields...

Ok, AB,
we have no choice. :-) Thanks.
But I need those fields to be filterable or searchable on the database anyway. So I thought that using the json functions it is possible to query and extract the data contained in the variant in the "where" clause of the fillprepare or .Execute.
But the variant fields are created as "text", while instead, to be managed directly through Db they must be of type Json. I'm talking about PostgreSql.
Is it possible to force the creation of "Json" fields type instead of "Text" ones for the variant fields of "TOrm"?
Thank you so much!

Last edited by Stemon63 (2022-05-27 10:31:36)

Offline

#6 2022-05-27 10:43:10

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,206
Website

Re: TORM: Problem... too many fields...

Sadly, PostgreSQL JsonB is not yet supported by our ORM.
You could use SQLite3 JSON functions, but it would use virtual tables, therefore will fetch all data from the main PostgreSQL, so could be slow...

With an external table, what you could try also is to define several TOrm classes, mapping the same external PostgreSQL table.
Perhaps not all 400 columns are used at once, so you could define several TOrm classes depending on the business logic.

Offline

#7 2022-05-27 11:22:40

Stemon63
Member
Registered: 2016-10-24
Posts: 49

Re: TORM: Problem... too many fields...

Not JsonB, but standard Json "textual". I have changed fieldtype from "TEXT" to "JSON" into table, after createmissingtables, and it seems works on write and on read. JsonB is the best (for index and optimization)  but in the meantime, plain Json can be managed for now.
So, if I can force  map "json" instead "text" on table creation, I think that can use mormot and also DB direct Json features.

Instead, For the external table, how can I map several TOrm classes to the same external PostgreSQL table? There is a MapTable function?
Thanks a lot!

Offline

#8 2022-05-27 11:52:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,206
Website

Re: TORM: Problem... too many fields...

Just define several TOrm classes using the same external table name.

Offline

#9 2022-05-27 14:15:37

Stemon63
Member
Registered: 2016-10-24
Posts: 49

Re: TORM: Problem... too many fields...

Ok, I will try.
I'm only missing a "Map" function for change "text" into  "json" when field is variant on a table creation. Or a way for "force" it. Please tell me a direction.
Embedding some big tables, big only on number of fields, included within a master record, can be a better solution with "destructured" data forms, but I need to change "TEXT" to Json, so we are sure to use SQL also on embedded json.
Thank you so much for your patience!

Offline

#10 2022-05-27 16:02:56

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,206
Website

Re: TORM: Problem... too many fields...

No, there is no JSON mapping feature.

Offline

Board footer

Powered by FluxBB