mORMot Open Source

TommyYommi

Member

Registered: 2013-01-18

Posts: 27

Foreign Key in SQlite and ORM

Hello...

How can I declare a TSQLRecord in a way to make the ORM create a Foreign Key into a SQlite database?

I have declare 2 classes:

  TEndereco = class(TSQLRecord)
  private
    FLogradouro: RawUTF8;
    FBairro: RawUTF8;
    FNumero: Integer;
    FCidade: RawUTF8;
  published
    property Logradouro: RawUTF8 read FLogradouro write FLogradouro;
    property Numero: Integer read FNumero write FNumero;
    property Bairro: RawUTF8 read FBairro write FBairro;
    property Cidade: RawUTF8 read FCidade write FCidade;
  end;

  TCliente = class(TSQLRecord)
  private
    FDivida: Currency;
    FDataAlteracao: TModTime;
    FIdade: Integer;
    FNome: RawUTF8;
    FDataNascimento: TDateTime;
    FCNPJ: RawUTF8;
    FEndereco: TEndereco;
  published
    property Nome: RawUTF8 read FNome write FNome;
    property Idade: Integer read FIdade write FIdade;
    property DataAlteracao: TModTime read FDataAlteracao write FDataAlteracao;
    property Divida: Currency read FDivida write FDivida;
    property DataNascimento: TDateTime read FDataNascimento
      write FDataNascimento;
    property CNPJ: RawUTF8 read FCNPJ write FCNPJ;
    property Endereco : TEndereco read FEndereco write FEndereco;
  end;

The ORM created 2 tables and a Endereco Integer field to make the relationship, but do not create a Foreign Key between the 2 tables.

Last edited by TommyYommi (2013-02-02 10:19:58)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Foreign Key in SQlite and ORM

Foreign Keys are internal to mORMot, not created yet at the database level.

But it is planned for the future, and always possible to create by hand, using a simple SQL statement at table initialization.
To let your own code launched when a table is created, override the class procedure TSQLRecord.InitializeTable() method - see its documentation.

Implements Foreign keys at ORM level, including support of several synchronization like cascade/restrict - issue is that it should work also with no SQL DB back-end, e.g. for pure in-memory tables;
source: http://synopse.info/fossil/wiki?name=RoadMap

TommyYommi

Member

Registered: 2013-01-18

Posts: 27

Re: Foreign Key in SQlite and ORM

Internal how? If I try to delete a record used by another table the ORM won't  let me?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Foreign Key in SQlite and ORM

The ORM will set Field=0 where Field references the deleted ID.
He will do that at ORM level, not at database level.
That's why there is a dedicated item in the roadmap to implement support of several synchronization like cascade/restrict.

See this method of TSQLRestServer class:

    /// this method is called internally after any successfull deletion to
    // ensure relational database coherency
    // - delete all available TRecordReference properties pointing to this record
    // in the database Model, for database coherency
    // - delete all available TSQLRecord properties pointing to this record
    // in the database Model, for database coherency
    // - important notice: we don't use FOREIGN KEY constraints in this framework,
    // and handle all integrity check within this method (it's therefore less
    // error-prone, and more cross-database engine compatible)
    function AfterDeleteForceCoherency(Table: TSQLRecordClass; aID: integer): boolean; virtual;

Purpose of this ORM-based synchronization is to work even if you do not have a SQL engine embedded.

If you want to change this behavior, e.g. to trigger an error when deletion occurs, you can override the following virtual method in your TSQLRestServer:

function TSQLRest.RecordCanBeUpdated(Table: TSQLRecordClass; ID: integer; Action: TSQLEvent;
  ErrorMsg: PRawUTF8 = nil): boolean;
begin
  result := true; // accept by default -> override this method to customize this
end;

TommyYommi

Member

Registered: 2013-01-18

Posts: 27

Re: Foreign Key in SQlite and ORM

I need to create the Foreign Keys by hand.

Is there a command on the ORM to create Foreign Keys?
If not, Wich Administration tool to SQlite you think is better to the Database created by the ORM, for me to do that?

Last edited by TommyYommi (2013-02-02 15:38:26)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Foreign Key in SQlite and ORM

I suspect just writing your SQL statement, and execute it in your own override of the class procedure TSQLRecord.InitializeTable() method, could be the best.
It will be better than any tool, run by hand.

Take a look at
http://www.sqlite.org/foreignkeys.html and
http://www.sqlite.org/syntaxdiagrams.ht … key-clause

To test the content, any SQL tool will work, but you can use our SynDBExplorer tool, which knows our collations.
It is free, and since it is base on our SynDB classes, it will work with SQLite3, Jet/MSAccess, Oracle, MS SQL, and any ODBC/OleDB providers.
See http://blog.synopse.info/tag/SynDBExplorer

TommyYommi

Member

Registered: 2013-01-18

Posts: 27

Re: Foreign Key in SQlite and ORM

Very well, thanks for all the answers.

I will use TSQLRecord.InitializeTable() method.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Foreign Key in SQlite and ORM

Any feedback is welcome, especially for ideas about how you want FOREIGN KEYS to be automatically implemented by the ORM (for the roadmap item to be implemented).

I planned to add some synchronization attributes, like cascade or restrict, then create the table with the proper REFERENCES clause.

What are you opinion/wishes about the needed features?

TommyYommi

Member

Registered: 2013-01-18

Posts: 27

Re: Foreign Key in SQlite and ORM

I believe that when you declare a class with a relationship (TSQLRecord kind of field), the should create the correspondent Foreign key, there would be a default option (cascade or restricted).

What do you think?