mORMot Open Source

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 81

I want use ORM very much but MORMOT are some restrictions

Arnold, I practically read the entire forum and you always give the same answer regarding TID null, FKs etc...
I really want to work with ORM, just like many programmers who ventured before me in trying to use mormot, I contacted some of them and they told me that they stopped using mormot's ORM precisely for the same reasons that I didn't. I want to give up.
I know that you need to keep your ORM capable of working with both SQL and NoSQL databases.
But is there any way to implement things that the orm itself can handle internally instead of having to write it in my code? Example:

I have a table in my database that looks like this:

CREATE TABLE TABELA_AB (
    CD_AB ID_NN /* ID_NN = INTEGER NOT NULL */,
    CD_A ID_NN   /* ID_NN = INTEGER NOT NULL */,
    CD_B ID_NN   /* ID_NN = INTEGER NOT NULL */,
    CD_C ID         /* ID = INTEGER ACCEPT NULL VALUES */
);
ALTER TABLE TABELA_AB ADD CONSTRAINT UNQ1_TABELA_AB  UNIQUE (CD_A, CD_B, CD_C);
ALTER TABLE TABELA_AB ADD CONSTRAINT PK_TABELAAB PRIMARY KEY (CD_AB);
ALTER TABLE TABELA_AB ADD CONSTRAINT FK_TABELA_AB_2 FOREIGN KEY (CD_A) REFERENCES TABELA_A (CD_A );
ALTER TABLE TABELA_AB ADD CONSTRAINT FK_TABELA_AB_3 FOREIGN KEY (CD_B) REFERENCES TABELA_B (CD_B );
ALTER TABLE TABELA_AB ADD CONSTRAINT FK_TABELA_AB_1 FOREIGN KEY (CD_C ) REFERENCES TABELA_C (CD_C );

TC = class(TOrm);

TCNullable = class(TC);

TTableAB = class(TOrm)
published
  A: TA;
  B: TB;
  C: TCNullable;
end;

1) Being able to define field as not null. Currently mormot creates tables without defining any field as not null other than the primary key. Even so, using orm all fields will always be filled with a certain value. unless TNullableXXX is used. My idea:
  VirtualTableExternalMap(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal')
    .SetRequired('A', True)
    .SetRequired('B', True)
Although I think this should be automatic when using the TORM field type! You store ZERO, why dont create the field with not null? Is not it?

2) Prevent deletion of a record whose primary key is being used in another table as a foreign key.
I know you've said many times that this should be controlled via business rules and such, but the programmer can always forget to do this somewhere. being in the model and the model creating the FK we do not run this risk.
I liked you ide of TClassNameRestrictID = A kind of TID that:
   - can store value as null at database if field not is on Requerid list (via SetRequired)
   - create fk automatically on databased that support this feature
   - during the update, prevent it from placing a value that does not exist in the related table.
     during the delete, prevent it from remove the record if some field fk are using this key
     this would help for nosql banks for sql banks the foreign key was created so an exception would already happen anyway.

Maybe this is difficult to implement or not, I don't know. but you don't think these implementations would help the adoption of MORMOT. If we do a poll about which database most Delphi programmers use, I'm sure there will be more people using SQL than NoSQL.
     
MapField('FieldName','FieldNameAlternative').

Last edited by mrbar2000 (2024-07-31 18:47:21)

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 81

Re: I want use ORM very much but MORMOT are some restrictions

Some answer to this topic Arnold?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,506

Website

Re: I want use ORM very much but MORMOT are some restrictions

This means several days of work to implement, test and validate, I am afraid.

mormoter

Member

Registered: 2024-06-14

Posts: 20

Re: I want use ORM very much but MORMOT are some restrictions

Take care ! Many mormot`s fans  are counting on you

Kabiri

Member

Registered: 2024-06-22

Posts: 33

Re: I want use ORM very much but MORMOT are some restrictions

I use SQL but I never use foreign keys. I always manage table relationships myself. I think foreign keys are more of a hindrance.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 81

Re: I want use ORM very much but MORMOT are some restrictions

Kabiri, I undesrstand you but FKs is a great way to protect yout database against inconsistence. If mormot could grant this integrity will be great. this will remove of programmer many responsability. We know that are programmers and programmers.

dcoun

Member

From: Crete, Greece

Registered: 2020-02-18

Posts: 412

Re: I want use ORM very much but MORMOT are some restrictions

Kabiri, I undesrstand you but FKs is a great way to protect yout database against inconsistence. If mormot could grant this integrity will be great. this will remove of programmer many responsability. We know that are programmers and programmers.

Do you use zeos with mormot to connect to the database?

For me, every sql error with zeos and mariadb can result to unpredictable situations, that zeos framework will not resolve.
So, I prefer not to use database's "protections"

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 81

Re: I want use ORM very much but MORMOT are some restrictions

I think you don't understand what is being proposed in this thread. The proposal is for mormot to prevent a record from being removed from the database if the record is being used by some property of another object that references this table. this will to avoid inconsistency in the database or the table that references the deleted record being set to ZERO as mormot does today.

The issue of creating the FK is just something additional to prevent the programmer from using a delete outside the ORM from also leaving the database inconsistent.