Default values for columns ?

jbroussia · 2016-08-11 10:40:10

Hi,

Is it possible to set a default value when defining properties in TSQLRecord descendants (meaning that if I omit a property in a query, the default value should be used) ?
Or should I use the RDBMS to do it ?

Thanks.

ab · 2016-08-11 10:45:48

If you just override the object constructor and set the fields with the default values, it should work as expected.
No need to tweak the RDBMS.

ORM is always starting from object, not from relational model.
And mORMot ORM is expected to work on non RDBMS systems, which do not have such "default values".

jbroussia · 2016-08-11 11:42:35

OK thanks, I still have to convert my way of thinking to the ORM way !

edwinsn · 2021-11-27 10:23:02

Setting default field values in the constructor only works for **new rows**, but not an existing row with missing field(s).
For the latter, you might need to take the "override TSQLRecord.InitializeTable then run TSQLRest.UpdateField() to put some default value when the field is detected as missing." approach, which is not very convenient, as described in https://synopse.info/forum/viewtopic.php?id=5469

Last edited by edwinsn (2021-11-27 10:55:49)

edwinsn · 2021-11-27 11:26:09

@ab,

Maybe it's worth to add new property called TSQLPropInfo.DefaultValue and use that proper when constructing the SQL for adding the column in the db?

SQLite itself supports the `default` clause for the `alter table` statement, something like:

alter table t1 add column status integer default 100

edwinsn · 2021-11-27 12:04:29

ok, quite easy if you know how to do it, here is a working example might might be helpful for others:

class procedure TSQLMyTable.InitializeTable(Server: TSQLRestServer; const FieldName: RawUTF8; Options:
    TSQLInitializeTableOptions);
begin
  inherited;
  // the engine is adding the missing MyField1 field, we need to update the db table for this field to
  // have the default value, because the mORMot engine has no direct support for 'default field value'
  // see: https://synopse.info/forum/viewtopic.php?id=5469
  if (FieldName = 'MyField1') then
    UpdateDefaultValueForCardTextSourceProp(Server);
end;

class procedure TSQLMyTable.UpdateDefaultValueForCardTextSourceProp(const aServer: TSQLRestServer);
var
  sql: RawUTF8;
begin
  sql := FormatUtf8('UPDATE % SET MyField1 = % WHERE MyField1 IS NULL',
    [self.SQLTableName, cDefaultValueForMyField1]);
  aServer.Execute(sql);
end;

mORMot Open Source

#1 2016-08-11 10:40:10

Default values for columns ?

#2 2016-08-11 10:45:48

Re: Default values for columns ?

#3 2016-08-11 11:42:35

Re: Default values for columns ?

#4 2021-11-27 10:23:02

Re: Default values for columns ?

#5 2021-11-27 11:26:09

Re: Default values for columns ?

#6 2021-11-27 12:04:29

Re: Default values for columns ?

Board footer