mORMot Open Source

lele9

Member

Registered: 2011-10-28

Posts: 170

validate multi fields unique

hi,
how validate a TSQLRecord on multi fields unique values?
an example to explain:

TSQLSampleRecord = class (TSQLRecord)
property propA: RawUTF8;
property propB: RawUTF8;
property propC: RawUTF8;
end;

i would execute a validate to deny an insert of two TSQLSampleRecord with same PropA AND PropB.

maybe TSynValidateRest can help me?how?

thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,250

Website

Re: validate multi fields unique

Just create a new class, sibling to TSynValidateUniqueField.

lele9

Member

Registered: 2011-10-28

Posts: 170

Re: validate multi fields unique

thanks,
i write this code, maybe can help other users, quickly tested, but please check its ok.

TSynValidateUniqueFields = class(TSynValidateRest)
  private
    faFieldsNames: TRawUTF8DynArray;
  protected
    procedure SetParameters(Value: RawUTF8); override;
  public
    function Process(aFieldIndex: integer; const Value: RawUTF8; var ErrorMsg: string): boolean; override;
    property aFieldsNames: TRawUTF8DynArray read faFieldsNames write faFieldsNames;
  end;

function TSynValidateUniqueFields.Process(aFieldIndex: integer;
  const Value: RawUTF8; var ErrorMsg: string): boolean;
var aID: TID;
    aWhere: RawUTF8;
  I: Integer;
begin
   result := false;
   if (fProcessRest=nil) or (fProcessRec=nil) then
    result := true else
   begin
      aWhere := '(';
      aWhere := aWhere + '(1=1)';
      for I := 0 to Length(self.faFieldsNames)-1 do
         aWhere := aWhere + ' AND (' + self.faFieldsNames[i] + '=:('+
                   QuotedStr(fProcessRec.GetFieldValue(self.faFieldsNames[i]),'''')+'):)';
      aWhere := aWhere + ')';
      with fProcessRec.RecordProps do
         SetID(pointer(fProcessRest.OneFieldValue(Table, 'ID', aWhere)), aID);
      if (aID>0) and (aID<>fProcessRec.GetID) then
        ErrorMsg := sValidationFieldDuplicate
      else
        result := true;
   end;

end;

procedure TSynValidateUniqueFields.SetParameters(Value: RawUTF8);
var V: TPUtf8CharDynArray;
  I: Integer;
begin
  JSONDecode(Value, ['FieldNames'], V, True);
  SetLength(self.faFieldsNames, 0);
  I := 0;
  while (V[0]<>nil) do
  begin
     SetLength(self.faFieldsNames, Length(self.faFieldsNames)+1);
     self.faFieldsNames[i] := GetNextItem(V[0]);
     inc(I);
  end;
end;

that can be used as this

TSQLSampleRecord.AddFilterOrValidate('propA', TSynValidateUniqueFields.Create('{"FieldNames":"propA,propB"}'));

thanks,
Emanuele

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,250

Website

Re: validate multi fields unique

Great! Sounds just fine.

Has been included as http://synopse.info/fossil/info/53094ef2f5

Thanks for sharing!