mORMot Open Source

imperyal

Member

Registered: 2018-10-11

Posts: 51

Prevent key violation on foreign keys

Hello!

Another simple question... How do I prevent the insertion of an invalid ID on a table that has a reference to another one (foreign key)?

My test tables:

  TSQLClienteRecord = class(TSQLRecord)
  private
    fNome:       RawUTF8;
    fIdade:      Int64;
    fNotas:      RawUTF8;
  published
    property Nome:  RawUTF8 index 100        read fNome  write fNome stored AS_UNIQUE;
    property Idade: Int64                    read fIdade write fIdade;
    property Notas: RawUTF8                  read fNotas write fNotas;
  end;


  TSQLEncomendaRecord = class(TSQLRecord)
  private
    fCliente: TSQLClienteRecord;
    fNif:     Int64;
    fMorada:  RawUTF8;
  published
    property fkCli:  TSQLClienteRecord read fCliente  write fCliente;
    property Nif:    Int64             read fNif      write fNif;
    property Morada: RawUTF8           read fMorada   write fMorada;
  end;

When I insert a record on EncomendaRecord using Postman for example, I can send any value to fkCli and no error occurs, and the record is inserted on the table. I expected an error to be automatically raised, preventing the insertion of that invalid foreign key.

And how do I do other validation that raise errors to the client AJAX app, like type differences, or string length, etc...

Thank you!

Last edited by imperyal (2018-10-23 16:02:01)

imperyal

Member

Registered: 2018-10-11

Posts: 51

Re: Prevent key violation on foreign keys

I did find the way to do validation on my services (newbie stuff):

class procedure TSQLClienteRecord.InternalDefineModel(Props: TSQLRecordProperties);
begin
  AddFilterNotVoidText(['Nome','Email']);

  AddFilterOrValidate('Email', TSynValidateEmail.Create);
end;

And then call .Validate to do the actual validation.

But I still don't know how to do validation on the server side for my TSQLRecord's CRUD operations... And prevent invalid foreign key violation at the record level, when inserting a record with Postman for example.

Would appreciate some help, thank you.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Prevent key violation on foreign keys

Validation is indeed just a client-side feature.

Since the framework need to work on all kind of databases, even some NoSQL engine with constraint feature, and since we favor data sharding (document oriented data) instead of relational model (foreign keys and pivot tables), there is no built-in ORM feature for complex constraints.
The ORM stores and retrieve the data, and no logic is expected to be in the database.
It is up to the business layer (i.e. your business code) to validate the data before storage - this is typically what a DDD system does.

So in practice, you may need to switch from a client-server ORM design to a SOA design, then put the data logic inside a server-side persistence service, which will call the filters before calling the ORM for actual storage.
See e.g. on the DDD sub-folder handle users data - https://github.com/synopse/mORMot/tree/ … QLite3/DDD

imperyal

Member

Registered: 2018-10-11

Posts: 51

Re: Prevent key violation on foreign keys

That's what I thought. Today I started testing that, doing some validation on a interface class, SOA style like you said.

procedure TServiceOutroTeste.alterarCliente(var cli: TSQLClienteRecord);
var
  FieldIndex: integer;
  strError:   string;
begin
  FieldIndex := -1;
  strError   := cli.Validate(internalClient, ['Email'], @FieldIndex);

  if strError <> '' then raise Exception.Create(strError)
                    else internalClient.Update(cli);
end;

Very simple example. I don't know if this is the right way of doing this but it works as expected.