creating index through mormot's orm with part of field's content

dcoun · 2025-05-05 14:12:40

I have an Torm object and I am MariaDB as external database
When Torm.InitializeTable runs for this table, it checks and create the index if it does not exist.

The SQL that should be run for mysql is: CREATE INDEX IF NOT EXISTS NDXExNams_del_cat_nam ON exnams(del,cat,nam(30));

This can not be done through Mormot and I am getting the error: "TOrmPropInfoList.IndexByNameOrExcept(nam(30)): unkwnown field in TOrmExNams"

The db field nam is about 2048 bytes and it is not allowed to have all content in the index. With the above sql only the first 30 characters are used in the index.

The same query in Postgresql can be: CREATE INDEX IF NOT EXISTS "NDXExNams_del_cat_nam" ON exnams(del, cat, substring(nam, 1, 30));

Is it possible to bypass the check for the field names from mormot or give up and create them manually?

Thank you in advance

ab · 2025-05-05 14:31:06

What is the ExtFieldNames[] content in TRestStorageExternal.CreateSqlMultiIndex ?

Where does this nam(30) come from?

I am really confused.
For such complex indexes, the idea is to create the index by hand in pure SQL (bypassing the ORM), then disable the automatic creation of indexes with the proper TOrmInitializeTableOptions.

dcoun · 2025-05-05 14:37:19

('del', 'cat', '')

ab · 2025-05-05 14:48:21

Yes, because you forced 'nam(30)' in your call, right?

This is incorrect and unsupported for sure.

I am not able to reproduce this here.

dcoun · 2025-05-05 14:58:56

My stack during the error:
- r77204424 KERNELBASE RaiseException + 0x64
- mormot.core.text.ESynException.RaiseUtf8('%:IndexByNameOrExcept(%):unknown field in %', (...))
- mormot.orm.base.TOrmPropInfoList.IndexByNameOrExcept('nam(30'))
- mormot.orm.core.TOrmMapping.InternalToExternalDynArray(???, ('del', 'cat', "), $IEDOF870)
- mormot.orm.sql.TRestStorageExternal.CreateSqlMultifindex(???, (...).False, 'NDXExNams_del_cat_nam')
- mormot.orm.server.TRestOrmServer.CreateSqlMultifindex(TOrmExNams, (...).False, 'NDXExNams_del_cat_nam')

I found out that defining nam(30) as field in CreateSqlMultiIndex does not work.
The question is if is going to be supported or not.
I have no problem to create manually such indexes if mormot can not support them.

ab · 2025-05-05 17:05:46

As I wrote above, it is not supported for sure.

dcoun · 2025-05-05 17:25:55

It is ok, @ab.
Thank you a lot for the amazing mormot2.

mORMot Open Source

#1 2025-05-05 14:12:40

creating index through mormot's orm with part of field's content

#2 2025-05-05 14:31:06

Re: creating index through mormot's orm with part of field's content

#3 2025-05-05 14:37:19

Re: creating index through mormot's orm with part of field's content

#4 2025-05-05 14:48:21

Re: creating index through mormot's orm with part of field's content

#5 2025-05-05 14:58:56

Re: creating index through mormot's orm with part of field's content

#6 2025-05-05 17:05:46

Re: creating index through mormot's orm with part of field's content

#7 2025-05-05 17:25:55

Re: creating index through mormot's orm with part of field's content

Board footer