mORMot Open Source

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 108

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: 108

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,935

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: 51

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: 108

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: 457

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: 108

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.

Kabiri

Member

Registered: 2024-06-22

Posts: 51

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.

I know what you're saying, but I still prefer to control the table dependencies myself. When I delete a value in one table and know it's used in another table (like master/detail tables, etc.), I use a select to make sure the record isn't being used in the related tables. The benefit of this method is that my API tells the user they cannot delete this record because it is being used in the (table name) table.

JD

Member

Registered: 2015-08-20

Posts: 123

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.

@Kabiri

Sorry to ask, but are you not reinventing the wheel here? I used to do that too long ago, but it becomes unmanageable in a large database with lots of tables, data and table references. With the proper use of foreign keys, you can always try to delete a row and the error code (if any) will tell you why delete failed. Foreign keys are invaluable if you want your data to be consistent.

Kabiri

Member

Registered: 2024-06-22

Posts: 51

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

@JD
It’s true that my work increases. 
Managing tables and dependencies becomes difficult and time-consuming, and development must be done carefully. 
However, in return, I can show more understandable messages to the end user. 

Imagine a software user wants to delete a product. 
I check the inventory table and inform them that the product cannot be deleted because it still exists in the inventory. 
Or I let them know that the product is part of a sales invoice and therefore cannot be deleted.

JD

Member

Registered: 2015-08-20

Posts: 123

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

@JD
It’s true that my work increases. 
Managing tables and dependencies becomes difficult and time-consuming, and development must be done carefully. 
However, in return, I can show more understandable messages to the end user. 

Imagine a software user wants to delete a product. 
I check the inventory table and inform them that the product cannot be deleted because it still exists in the inventory. 
Or I let them know that the product is part of a sales invoice and therefore cannot be deleted.

I don't know what database you use, but it is a simple ON DELETE RESTRICT in PostgreSQL. It throws up a 23503 foreign key violation error code. See https://www.postgresql.org/docs/current … aints.html. And the example you cited is even discussed on the page.

JD

Last edited by JD (2025-03-15 21:28:29)

Kabiri

Member

Registered: 2024-06-22

Posts: 51

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

I don't know what database you use, but it is a simple ON DELETE RESTRICT in PostgreSQL. It throws up a 23503 foreign key violation error code. See https://www.postgresql.org/docs/current … aints.html. And the example you cited is even discussed on the page.

JD

All relational databases operate this way and do not allow deletion. 
I said the reason for this is to provide a more appropriate message to the user. 
When the primary key of my table is registered in several other tables, I give the user more accurate and clearer information. 
Of course, everyone uses their own approach.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 108

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

It´s more easy parse the message error to show more understandable messages to the end use that verify all locations that my pk is fk! This is my opinion!

Neither all programmer will have the caution that we have! And problems can happen.

anouri

Member

Registered: 2024-02-11

Posts: 85

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

I am using FK allways but for show message to end user I use information schema table to find all fk's and then create my own message. It is so easy and it is one function for checking refrential integrity.

this is for mysql:

      select REFERENCED_TABLE_NAME,CONSTRAINT_NAME,group_concat(COLUMN_NAME) COLUMN_NAME,
      group_concat(REFERENCED_COLUMN_NAME) REFERENCED_COLUMN_NAME,
      group_concat(COLUMN_NAME,'=',REFERENCED_COLUMN_NAME) col_ref
      from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
      where TABLE_SCHEMA = :TABLE_SCHEMA
      and TABLE_NAME = :TABLE_NAME
      and referenced_column_name is not NULL
      group by REFERENCED_TABLE_NAME,CONSTRAINT_NAME

parsing error message can not create understandable message. when user want to delete product number 1  I show this message:

product 1 used in invoice 123 and can not deleted.

invoice 123 is first invoice that used product number 1.

Last edited by anouri (2025-03-27 10:27:57)