mORMot Open Source

gelinp

Member

Registered: 2024-08-26

Posts: 1

How to reflect an existing database ?

Hi,
I'm a newbie with Momrmot :
1. If the database already exist do I need to write manualy in lazarus each class to map or do Mormot can reflect the database existing schema (and may be write somewhere this class for me) ?
2. I understand it's not the best way to get a lot of joins and it will  be preferable to update table with json collections but how to synchronize such collections if I delete a records without cascading with the database ?
Thank to your response.
Patrick

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,589

Website

Re: How to reflect an existing database ?

Hello Patrick,

1. The SynDBExplorer sample/tool (from mORMot 1) is able to generate some of the class.
But perhaps not the most complete.

2. Complex JOINs are not well supported by the ORM, because we support also NOSQL databases, some of which not supporting them.
But there is TOrmMany which allows most simple relationships.
The cascaded deletions are supported if you define your own "cascaded delete ID type" as such:

 type
   TOrmClientToBeDeletedID = type TID;
   TOrmOrder = class(TOrm)
   ...
   published OrderedBy: TOrmClientToBeDeletedID
     read fOrderedBy write fOrderedBy;

The mORMot 1 documentation is still the reference for this matter:
https://synopse.info/files/html/Synopse … l#TITLE_74
https://synopse.info/files/html/Synopse … l#TITLE_86

anouri

Member

Registered: 2024-02-11

Posts: 43

Re: How to reflect an existing database ?

I am using sql capabilties for generate DTO and classes. It is realy simple and flexible, maybe help some one (this is for mysql and can be implement for other dbms by changing sql):

select t.table_name,concat(group_concat(col order by ordinal_position separator ';\r\n'),';') table_create from (
select a.table_name,a.ordinal_position,concat(a.column_name,': ',
if(a.data_type in ('varchar','enum','text','char','time','varbinary','tinytext','set','mediumtext'),
if(a.is_nullable='YES','TNullableUtf8Text','RawUtf8'),
if(a.data_type in ('int','tinyint','mediumint','smallint'),
if(a.is_nullable='YES','TNullableInteger','integer'),
if(a.data_type in ('decimal'),
if(a.is_nullable='YES','TNullableCurrency','currency'),
if(a.data_type in ('bigint'),
if(a.is_nullable='YES','TNullableInteger','int64'),
if(a.data_type in ('timestamp','date','datetime'),
if(a.is_nullable='YES','TNullableDateTime','TDateTime'),
if(a.data_type in ('longblob','blob','mediumblob'),
if(a.is_nullable='YES','TNullableUtf8Text','RawUtf8'),
if(a.data_type in ('double','float'),'Double','unDefined')))))))
) col from information_schema.columns a
where table_schema='your_mysql_db_name') t
group by t.table_name;

Last edited by anouri (2024-10-03 10:26:47)