mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,651

Website

TSQLRecord filtering and validation

I've added auto-filtering in the framework.

See http://synopse.info/fossil/info/85dfa822ed
- introducing direct content filtering and validation using TSQLFilterOrValidator dedicated classes
-filtering is handled directly in the new TSQLRecord.Filter virtual method, or via some TSQLFilter classes - TSQLFilterUpperCase, TSQLFilterUpperCaseU, TSQLFilterLowerCase, TSQLFilterLowerCaseU and TSQLFilterTrim e.g.

I've added validation in the SQLite3Commons unit.
It's now totally independent from the UI part of the framework.

See http://synopse.info/fossil/info/5c03626961
and mostly http://synopse.info/fossil/info/14785e263f

TSQLRecord field content validation is handled in the new TSQLRecord.Validate virtual method, or via some TSQLValidate classes - see TSQLValidateRest, TSQLValidateIPAddress, TSQLValidateEmail, TSQLValidatePattern, TSQLValidatePatternI, TSQLValidateText, TSQLValidatePassWord e.g.

You have powerful validation classes for IP Address, Email (with TLD+domain name), simple regex pattern, textual validation, strong password validation...

SQLite3UIEdit unit now handles TSQLRecord automated filtering (using TSQLFilter classes) and validation (using TSQLValidate classes).
The unique field validation is now in TSQLRecord.Validate and not in SQLite3UIEdit itself (better multi-tier architecture).

To initialize it, you can add some filters/validators to your TSQLModel creation function:

function CreateFileModel(Owner: TSQLRest): TSQLModel;
var Classes: array[0..high(FileTabs)] of TSQLRecordClass;
    i: integer;
begin
  for i := 0 to high(FileTabs) do
    Classes[i] := FileTabs[i].Table;
  result := TSQLModel.Create(Classes,'synfile');
  result.Owner := Owner;
  result.SetActions(TypeInfo(TFileAction));
  result.SetEvents(TypeInfo(TFileEvent));
  TSQLFile.AddFilterOrValidate('Name',TSQLFilterLowerCase);
  TSQLUser.AddFilterOrValidate('Email',TSQLValidateEmail);
end;

Feedback is welcome!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,651

Website

Re: TSQLRecord filtering and validation

See http://synopse.info/forum/viewtopic.php?id=231 for the discussion about filtering and validation, which was the starting point of this new feature.

Thanks migajek for his proposals!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,651

Website

Re: TSQLRecord filtering and validation

It's interesting comparing our class-based implementation to e.g. http://www.remobjects.com/tv/da.aspx?vi … -scripting

A pure Delphi class-based filtering and validation will allow a lot more code reuse, on both Client and Server sides, with no need of an external/diverse language, in a pure procedural/event based model.
It will be also much faster (it's Delphi compiled), and much more powerful, since you may embed whatever local variable you want to the class instance, and you'll be able to call any high-level ORM methods in order to process the data.

This implementation just sounds great to me!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,651

Website

Re: TSQLRecord filtering and validation

For instance, here is the only method to defined to handle a generic field content unicity validation (from SQLite3Commons.pas unit):

function TSynValidateUniqueField.Process(aFieldIndex: integer; const Value: RawUTF8;
  var ErrorMsg: string): boolean;
var aID: integer;
begin
  result := false;
  if Value='' then
    ErrorMsg := sValidationFieldVoid else
  if (fProcessRest=nil) or (fProcessRec=nil) then
    result := true else
  with fProcessRec.RecordProps do
    if cardinal(aFieldIndex)>=cardinal(length(Fields)) then
      result := true else begin
      aID := GetInteger(pointer(fProcessRest.OneFieldValue(Table,'ID',
        FieldsName[aFieldIndex]+'=:('+QuotedStr(Value,'''')+'):')));
      if (aID>0) and (aID<>fProcessRec.fID) then
        ErrorMsg := sValidationFieldDuplicate else
        result := true;
    end;
end;

And here is how UNIQUE columns (i.e. published properties marked as stored false) will automaticaly register a trim filtering for textual values (using a TSynFilterTrim class), and a value unicity validation rule (using the TSynValidateUniqueField class as defined above).

// 2. handle unique fields, i.e. if marked as "stored false"
  Unique := not P^.IsStored;
  if Unique then begin
    include(IsUniqueFieldsBits,f);
    // must trim() text value before storage, and validate for unicity
    if FieldType[f] in [sftUTF8Text,sftAnsiText] then
      AddFilterOrValidate(f,TSynFilterTrim.Create);
    AddFilterOrValidate(f,TSynValidateUniqueField.Create);
  end;

All filtering and validation is handled at the TSQLRecord level. That is they are defined in a generic manner, outside the Server or the Client instance it runs on.