How to correctly model and use one-to-one joins with null in ORM?

wloochacz · 2021-03-25 21:41:36

Model: Database first.
Database: MSSQL 2014
Connection Properties: TOleDBMSSQL2012ConnectionProperties

I have the following data model:

  TUser = class(TSQLRecord)
  strict private
    FLogin : RawUTF8;
    FName  : RawUTF8;
  public
    property IdUser : TID                      read FID    write FID;
  published
    property Login  : RawUTF8 index cudtNAME_M read FLogin write FLogin;
    property Name   : RawUTF8 index cudtNAME_M read FName  write FName;
  end;

  TEmployee = class(TSQLRecord)
  strict private
    FBirthDate   : TNullableDateTime;
    FFirstName   : RawUTF8;
    FGender      : Integer;
    FHireDate    : TNullableDateTime;
    FIdEmployee  : Int64;
    FIdUser      : TUser;
    FIsEnabled   : Boolean;
    FLastName    : RawUTF8;
    FReleaseDate : TNullableDateTime;
  public
    property IdEmployee : TID                      read fID          write fID;
  published
    property FirstName  : RawUTF8 index cudtNAME_M read FFirstName   write FFirstName;
    property LastName   : RawUTF8 index cudtNAME_M read FLastName    write FLastName;
    property BirthDate  : TNullableDateTime        read FBirthDate   write FBirthDate;
    property Gender     : Integer                  read FGender      write FGender;
    property HireDate   : TNullableDateTime        read FHireDate    write FHireDate;
    property ReleaseDate: TNullableDateTime        read FReleaseDate write FReleaseDate;
    property IsEnabled  : Boolean                  read FIsEnabled   write FIsEnabled;
    property IdUser     : TUser                    read FIdUser      write FIdUser;
  end;

There is a little trick in there with mapping the ID field to another name, but I don't think it matters in this case. I tested it on single entities and it is OK.
I just want to have a property in an entity with a name just like in the database.

Everything works fine for data reads, but I have a problem with updates.

I have code like this:

var
  lID       :       Int64;
  lEmployee : TEmployee;
begin
  lEmployee := TEmployee.CreateAndFillPrepare(ORM.SQLRestServerDB, [100]);
  try
    lEmployee.IsEnabled := False;

    if not ORM.SQLRestServerDB.Update(lEmployee) then
    begin
      // How to check UPDATE error?
    end;
  finally
    lEmployee.Free;
  end;
end;

An employee with ID = 100 has a value in the IdUser = null field.
As you can see, I am not touching this value at all.
However, update tries to put a value of 0 (null) in the IdUser field, which is obviously not allowed because the foreign key checks for this value.
Allowed values are NULL or an existing ID from the User table, but certainly not zero.

1. How do I implement this correctly?
2. How to add NULL values to such related fields?
3. And how to check for error on save?

Of course, if I declare TEmployee.IdUser as TNullableInteger then everything is OK.
It's just that that's not quite the point.

ab · 2021-03-26 08:08:44

I guess this is not supported by the JOIN feature of the ORM yet.

wloochacz · 2021-03-26 09:08:15

ab wrote:

I guess this is not supported by the JOIN feature of the ORM yet.

Understood.
So it is also impossible in this case to assign NULL value for IdUser field?

And is there any workaround for the problem?
OnUpdate event or something similar?

And please, tell me how to check if there was any problem while updating the database?
Right now simply the Update method returns False, but I don't know why this command failed....

ab · 2021-03-26 09:46:42

The logs should give you more information.

But the problem with NULL is that
- SQLite3 accepts to put a NULL or an 0 everywhere IIRC;
- there is no "null" support in the Delphi language - we have the variant nullable types, but it is for standard values, not joined tables.

wloochacz · 2021-03-26 11:15:35

ab wrote:

The logs should give you more information.

Does this mean that I can't find out in the runtime about an exception that occurred while the ORM was updating the data?

But the problem with NULL is that
- SQLite3 accepts to put a NULL or an 0 everywhere IIRC;

That doesn't seem like the right assumption to me, take a look please:
https://sqlite.org/nulls.html
and
https://stackoverflow.com/questions/251 … ger-fields

You can clearly see that SQLite does not treat null and 0 values as the same.
And very well, because it would be a disaster.

Even for foreign keys defined in SQLite as on cascade set null everything is OK.
Simply put, a null value is null, and a 0 value is 0 and thus null != 0.
Period.

- there is no "null" support in the Delphi language - we have the variant nullable types, but it is for standard values, not joined tables.

OK, I understand.
I also understand that performing a single query in this case for a one-to-one relationship requires left join, not inner join.

I guess this is due to the mechanism of SQLite virtual tables, which admittedly (supposedly) support lef join, but their performance in this case is tragic.
It looks like mORMot only supports inner join - is this correct and is that what this is about?

ab · 2021-03-26 12:33:50

You are correct about all the points I guess.

The JOIN feature of our ORM is quite limited, and there only for backward compatibility with existing code. It does not support null there, and expects always a relationship.
Don't try to make it do more than what it can... poor little thing!

For complex queries, you need to bypass the ORM and run the proper SELECT statements directly.
The ORM is able to fill some objects from the JSON result, if the columns are properly named.

wloochacz · 2021-03-26 13:11:58

If I may AB, I would still like to ask you to answer a question:
How to check if there was any problem while updating the database for REST/ORM layer?

As for the rest, well - it looks like my conclusions are correct....
But that's a medium problem, I can handle it, but I need to understand exactly how it all works.

Well, I have powerful experience with relational databases and hence some background in my understanding.
I need to switch over ;-)
I've already dug through the forum and all the luck that you introduced nullable types

ORM in mORMot is a new thing for me (I still think that relation handling in mORMot ORM is... questionable), and since we are rewriting Server, so I want to do it properly and according to mORMot philosophy.

Well, I'm still researching, testing and learning.
Just learned that for the Cache to work on the server side is not as transparent as you write about it in the manual.
I can't do CreateAndFillXXX for example, and by using Retrieve methods.

There are really a lot of such cases, so thank you very much for your answers.

Let's keep going!

mORMot Open Source

#1 2021-03-25 21:41:36

How to correctly model and use one-to-one joins with null in ORM?

#2 2021-03-26 08:08:44

Re: How to correctly model and use one-to-one joins with null in ORM?

#3 2021-03-26 09:08:15

Re: How to correctly model and use one-to-one joins with null in ORM?

#4 2021-03-26 09:46:42

Re: How to correctly model and use one-to-one joins with null in ORM?

#5 2021-03-26 11:15:35

Re: How to correctly model and use one-to-one joins with null in ORM?

#6 2021-03-26 12:33:50

Re: How to correctly model and use one-to-one joins with null in ORM?

#7 2021-03-26 13:11:58

Re: How to correctly model and use one-to-one joins with null in ORM?

Board footer