mORMot Open Source

trx

Member

Registered: 2015-08-30

Posts: 30

Default values for new fields in external DB

Hi,

I have a TSQLRecord descendant (TMyRecord) which is stored in a an external database (MariaDB via ZEOS).
After table is created by the ORM (CreateMissingTables) and after I insert some rows, if I add another eg. RawUTF8 published property the field is created in the DB automatically.

My issue is that the default value for all existing rows will be NULL.
So if I then try to select an existing row (TMyRecord.CreateAndFillPrepare and FillOne) I get no results.

Currently the only way I have to make those rows available again is to manually connect to the database and change NULL to empty string for all rows.

I haven't been able to find a solution for this in the documentation and the sources.
Any suggestions?

Thank you!
Svetozar Belic.

Last edited by trx (2020-06-02 11:32:23)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Default values for new fields in external DB

There is no default value supported directly by the ORM.

What we usually do is override TSQLRecord.InitializeTable then run TSQLRest.UpdateField() to put some default value when the field is detected as missing.

trx

Member

Registered: 2015-08-30

Posts: 30

Re: Default values for new fields in external DB

I will do that as well then, thanks ab!

trx

Member

Registered: 2015-08-30

Posts: 30

Re: Default values for new fields in external DB

Ab,

When I override TSQLRecord.InitializeTable, it gets called when the table is created or the table is empty.
But it does not get called when the table already contains rows and I have added a new field.

Am I missing something?
Thank you.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Default values for new fields in external DB

It is also called when a new field is added IIRC.
Only the first time, just after ALTER TABLE is executed, with the field name in FieldName parameter.

trx

Member

Registered: 2015-08-30

Posts: 30

Re: Default values for new fields in external DB

That is currently not the case for external DB.
I have created a pull request on GitHub (317) with a potential fix.

EDIT:
It seems that my fix is not a proper solution. InitializeTable must be called somewhere after because for example TSQLRest.UpdateField() is not possible at that point:

Error SQLITE_LOCKED (6) [UPDATE CISInfo SET Test8=? WHERE Test8=?] using 3.31.0 - vtable constructor called recursively: CISInfo, extended_errcode=6
 In file '..\..\fpc\mORMot\SynSQLite3.pas' at line 5392

Last edited by trx (2020-06-02 21:01:11)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: Default values for new fields in external DB

Try to run ExecuteDirect() instead of UpdateField().