Foreign Key "0" and not "null"

fabio_thz · 2014-08-19 02:09:55

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:
FluxBB bbcode test

Thank you.

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

ab · 2014-08-19 06:20:33

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 · 2014-08-19 11:43:05

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 · 2014-08-19 11:48:07

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 · 2014-08-19 14:48:24

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 · 2014-08-19 17:54:02

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 · 2014-08-19 21:38:40

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

mORMot Open Source

#1 2014-08-19 02:09:55

Foreign Key "0" and not "null"

#2 2014-08-19 06:20:33

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

#3 2014-08-19 11:43:05

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

#4 2014-08-19 11:48:07

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

#5 2014-08-19 14:48:24

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

#6 2014-08-19 17:54:02

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

#7 2014-08-19 21:38:40

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

Board footer