mORMot Open Source

fabio_thz

Member

From: Vila Valerio-ES, Brasil

Registered: 2014-08-14

Posts: 4

Foreign Key "0" and not "null"

Hello,
   
   I am a Brazilian, I'm starting my studies in this Framework, but had a doubt:

How do I record with foreign key values "null"?

Whenever save an object that contains another, which is not instantiated, writes the value "0" instead of "null".

Sample Code:
https://mormot-br.googlecode.com/svn/trunk/

procedure TFMainForm.CreateConnection;
var
  LModel: TSQLModel;
  LModelServer: TSQLModel;
begin
  LModel := CreatePersonModel;
  LModelServer := CreatePersonModel;
  FConnection := TSQLRestClientDB.Create(LModel, LModelServer,
    ChangeFileExt(Application.ExeName,'.db3'), TSQLRestServerDB);
  TSQLRestClientDB(FConnection).Server.CreateMissingTables(0);
end;

procedure TFMainForm.FormCreate(Sender: TObject);
begin
  lblStatus.Caption := '';
  CreateConnection;
end;

procedure TFMainForm.FormDestroy(Sender: TObject);
begin
  FreeAndNil(FConnection);
end;

procedure TFMainForm.Button1Click(Sender: TObject);
var
  LPerson: TSQLPerson;
  LCity: TSQLCity;
  LId: Integer;
begin
  if chkIncCity.Checked then
  begin
    LCity := TSQLCity.CreateAndFillPrepare(FConnection,'');
    if not LCity.FillOne then
    begin
      LCity := TSQLCity.Create;
      LCity.Name := 'Vila Valerio';
      LCity.State := 'ES';
      FConnection.Add(LCity,True);
    end;
  end;

  LPerson := TSQLPerson.Create;
  LPerson.FirstName := edtFirstName.Text;
  LPerson.Surname := edtSurname.Text;

  if chkIncCity.Checked then
    LPerson.City := LCity.AsTSQLRecord
  else
    LPerson.City := nil;

  LId := FConnection.Add(LPerson,True);

  if LId > 0 then
  begin
    lblStatus.Caption := 'Included successfully - Id: '+IntToStr(LId);
    lblStatus.Font.Color := clGreen;
  end
  else
  begin
    lblStatus.Caption := 'Error: '+sLineBreak+FConnection.LastErrorMessage;
    lblStatus.Font.Color := clRed;
  end;
end;

See the result:

Thank you.

Last edited by fabio_thz (2014-08-19 02:11:21)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,649

Website

Re: Foreign Key "0" and not "null"

As stated by the SAD 1.18 PDF, it saves the ID of the field, and 0 if none.
There is a whole paragraph about NULL in the doc.

fabio_thz

Member

From: Vila Valerio-ES, Brasil

Registered: 2014-08-14

Posts: 4

Re: Foreign Key "0" and not "null"

The paragraph is "8.2.7. Handling NULL"?

Okay that is not nullable in property fields in Delphi, but the above example, we have a field nil, in case the property "City". Would not it be more correct that property with nil instance would not be inserted in the SQL clause?

LPerson.City := nil;

What about the other cases, it would not be interesting to implement a new type?

example:
http://cc.embarcadero.com/item/26916
http://www.tmssoftware.com/business/aur … e_type.htm
http://dade2000.altervista.org/index.ph … _for_Win32

Last edited by fabio_thz (2014-08-19 11:52:05)

fabio_thz

Member

From: Vila Valerio-ES, Brasil

Registered: 2014-08-14

Posts: 4

Re: Foreign Key "0" and not "null"

My biggest problem with this is the foreign key constraint violation. In this example I use SQLite, but in other tests using Firebird with relationships does not work.

How to solve this case? Do not use foreign keys?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,649

Website

Re: Foreign Key "0" and not "null"

We do not explicitly support foreign keys in our ORM yet.
See http://synopse.info/fossil/tktview?name=babfb39a8a

This is by design: we did not want to be tied to a SQL model (since we support MongoDB, or even an optimized TObjectList storage).
So data integrity is handled at the ORM level, not at the DB level.

See for instance:

function TSQLRestServer.Delete(Table: TSQLRecordClass; ID: integer): boolean;
begin
  result := inherited Delete(Table,ID); // call EngineDelete
  if result then
    // force relational database coherency (i.e. our FOREIGN KEY implementation)
    AfterDeleteForceCoherency(Table,ID);
end;

This coherency will be ensured for both TSQLRecord and TRecordReference published fields, whatever database backend is used.
We allow even to implement "foreign keys" over tables on diverse databases, and even on diverse engines (e.g. one table on SQLite3, another on Oracle, and another on MongoDB).

fabio_thz

Member

From: Vila Valerio-ES, Brasil

Registered: 2014-08-14

Posts: 4

Re: Foreign Key "0" and not "null"

I was surprised by the possibilities presented ... This is great.

But some features of nullable fields would be nice.

I'm starting a new project with mORMot to get to know, use Aurelius currently, but I'm looking for a second option, and apparently, this might even be the main option, is way ahead of a simple ORM.

Working on commercial projects, sometimes we have some situations:
TEdit.Text connected on a nullable property type Integer, which can have any integer value or no value ('').

If I retrieve an integer value and this is a value "0", my edit will show a value that is not real, the record was left blank, but now has the value "0".

This may be more complex, but for foreign keys, independent of the database, it is interesting not save the default "0" value, but "null" if the relationship does not exist.

I am very grateful for the clarification!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,649

Website

Re: Foreign Key "0" and not "null"

About TEdit this is just a matter of UI coding IMHO.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 88

Re: Foreign Key "0" and not "null"

Hy AB,

to try to guarantee the integrity of the data, since there is no foreign key support yet, couldn't there be something that could be done before calling
result := inherited Delete(Table,ID); // call EngineDelete
to check if this id is being used in another TOrm? thus preventing deletion?

Something like BeforeDeleteForceCoherency(Table,ID);

Last edited by mrbar2000 (2024-07-23 13:04:32)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,649

Website

Re: Foreign Key "0" and not "null"

Foreign key is a powerful concept, but it is complex to implement.
https://www.sqlite.org/foreignkeys.html

AfterDeleteForceCoherency is currently doing some actual work on linked fields as TRecordReference or TOrm or T*ID.
We currently only support some kind of "ON DELETE SET 0" or "ON DELETE CASCADE" work at ORM.

So I guess you expect to refuse the deletion if the ID is used somewhere else?
Some kind of "ON DELETE RESTRICT" to raise an error when Delete() is called with some existing reference in other tables?

You can do this in your own persistence code, before calling Delete().
Adding a method to the ORM would make sense only if it is properly handled.
We would need to add TOrm*RestrictID field type name detection, and TRecordReferenceRestrict field, similar to TOrm*ToBeDeletedID and TRecordReferenceToBeDelete.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 88

Re: Foreign Key "0" and not "null"

So I guess you expect to refuse the deletion if the ID is used somewhere else?
Some kind of "ON DELETE RESTRICT" to raise an error when Delete() is called with some existing reference in other tables?

Yeah this is very necessary to many of us that work with relational databases.

We would need to add TOrm*RestrictID field type name detection, and TRecordReferenceRestrict field, similar to TOrm*ToBeDeletedID and TRecordReferenceToBeDelete.

Yeah xxxRestrictID would be great. and this field types could create automatically that foreign key if not exists.

Finally foreign key on mormot >:)

I think that maybe whould be necessary remove AfterDeleteForceCoherency and put a BeforeDeleteForceCoherency to treat XXXRestrictID XXXToBeDeletedID  XXXToBeDeletedIDSetNull or XXXToBeDeletedIDSetZero. just ideas!!!

Last edited by mrbar2000 (2024-07-24 04:30:14)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,649

Website

Re: Foreign Key "0" and not "null"

Putting to zero should still be done AFTER the main deletion, to detect any failure of it.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 88

Re: Foreign Key "0" and not "null"

Putting to zero should still be done AFTER the main deletion, to detect any failure of it.

Do you intend to make this change soon (without pressuring you)?

If I'm not going to do it now, I can try to implement it and pass it on to you. I don't know the mormot sources very well but I can follow the same steps you did in AfterDeleteForceCoherency

I was thinking here, unless you want to treat the restrict at the orm level, you would only need the RESTRICT field type to create the foreign key in the database.
If I create it at ORM level, I would still create the foreign key to avoid incorrect data in the database due to some statement that the programmer executes (INSERT OR UPDATE) outside the ORM.

Last edited by mrbar2000 (2024-07-24 14:32:02)