mORMot Open Source

xotox

Member

Registered: 2015-02-07

Posts: 18

Forcing textfield size validation

Hi all!

I'm using a SQLite3 backed database and define a TSQLRecord text field as:

property FirstName: RawUTF8 index 80 read fFirstName write fFirstName;

I can set FirstName to e.g. 100 characters, and there is no automatic size check. The value will be stored as given with 100 characters.
Is it possible to force TSQLRecord to automatically check the field size, even if using SQLite as database?
Or do I really have to add Validators for all fields?
Background:
In future it might be possible to switch the database to another DB Server, e.g. MSSQL.
It would cause a lot of trouble if there are records not fitting into the MSSQL database.

Thanks in advance!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Forcing textfield size validation

There is no size limitation in SQLite3, by design.
If you want to validate the length, you have to add a TSynValidateText, then call it before sending to the REST instance.

Another option, at SQLite3 level, may be to add a CHECK constraint to the table.
See https://www.sqlite.org/lang_createtable.html#ckconst
If you succeed into it, please send your feedback: we may be able to add it to the framework source.

xotox

Member

Registered: 2015-02-07

Posts: 18

Re: Forcing textfield size validation

Hi!

I've implemented it using RTTI. Works downto Delphi 7 which is the oldest Delphi Version I still have installed. Feel free to reuse my code. Maybe it will help someone, or maybe there's a way to integrate it directly into the Framework.
The code will add {"MinLenght":0,"MaxLength":[field_size]} Validators for all RawUTF8 index [field_size] published properties. Well, to all TypInfo.tkLString TypeKinds better said.

procedure AddMaxLengthValidators(const SQLRecordClasses: array of TSQLRecordClass; const TypeKinds: TTypeKinds = [TypInfo.tkLString]);
var
  iClasses: integer;
  cProps: integer;
  iProps: integer;
  list: PPropList;
begin
  for iClasses := Low(SQLRecordClasses) to High(SQLRecordClasses) do
  begin
    cProps := GetPropList(SQLRecordClasses[iClasses].ClassInfo, TypeKinds, nil);
    GetMem(list, cProps * SizeOf(PPropInfo));
    try
      GetPropList(SQLRecordClasses[iClasses].ClassInfo, TypeKinds, List);
      for iProps := 0 to Pred(cProps) do
        if list^[iProps]^.Index > 0 then
          SQLRecordClasses[iClasses].AddFilterOrValidate(List^[iProps]^.Name,
            TSynValidateText.Create(Format('{"MinLength":0,"MaxLength":%d}', [list^[iProps]^.Index])));
    finally
      FreeMem(list, cProps * SizeOf(PPropInfo));
    end;
  end;
end;

Example Usage:

...

var
  mySQLRecords: array[0..1] of TSQLRecordClass = (TSQLmyRecord1, TSQLmyRecord2);

function CreateModel: TSQLModel;
begin
  result := TSQLModel.Create(mySQLRecords);
  AddMaxLengthValidators(mySQLRecords);
end;

...

Last edited by xotox (2015-02-21 19:04:18)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,269

Website

Re: Forcing textfield size validation

I've introduced TSQLRecordProperties.SetMaxLengthValidatorForTextFields() and SetMaxLengthFilterForTextFields() methods, and also corresponding TSQLModel.SetMaxLength[Validator/Filter]ForAllTextFields() methods so that text column lengths may be checked or truncated before sending to an external database expecting a maximum length.
See http://synopse.info/fossil/info/add517c844

If you want to perform some text field length validation or filter at ORM level, you may use TSQLRecordProperties's SetMaxLengthValidatorForTextFields() or SetMaxLengthFilterForTextFields() method, or at model level:

function CreateModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLMyRecord1,TSQLMyRecord2]);
  result.SetMaxLengthValidatorForAllTextFields(true); // "index n" is in UTF-8 bytes
end;

Hope it helps.