Represent DocTypes as UML

Hi All,
i am wondering if there is any tool which can convert the .json files of DocTypes into a UML Diagram such smething as the below example.

Thanks.
Ahmed

We used below approach to generate ER diagram based on frappe metadata. Mysql information schema and docfield, customfield tables has all data that you need and mysql workbench to reverse engineer data model.

  1. Execute 2 queries provided below. It will output all the alter table statements to create constraints on the tables
  2. Get a schema dump of your erpnext database.
  3. Recreate schema on local MySql MariaDB Using MySql Workbench
  4. Execute all ‘alter table’ commands output by below 2 queries on local mariadb.
  5. Create diagram in MySql Workbench - reverse engineer option https://dev.mysql.com/doc/workbench/en/wb-reverse-engineer-live.html

Query 1 -

SELECT CONCAT('tab' ,parent) as 'Table Name' 
       ,fieldname as 'Column' 
       ,CONCAT('tab',options) as 'References Table' 
       ,CONCAT('ALTER TABLE `','tab',parent,'` ADD CONSTRAINT `fk_',parent,'_',fieldname, '` FOREIGN KEY (',fieldname,') REFERENCES `','tab',options,'` ( name ) ON DELETE CASCADE ON UPDATE RESTRICT;') as fk_constraint 
FROM `tabDocField` 
WHERE fieldtype = 'Link' 
AND CONCAT('tab' ,parent) in (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES) 
union 
SELECT CONCAT('tab' ,dt) as `Table Name`   
       ,fieldname as `Column` 
       ,CONCAT('tab',options) as `References Table` 
       ,CONCAT('ALTER TABLE `','tab',dt,'` ADD CONSTRAINT `fk_',dt,'_',fieldname, '_cust` FOREIGN KEY (',fieldname,') REFERENCES `','tab',options,'` ( name ) ON DELETE CASCADE ON UPDATE RESTRICT;') as fk_constraint 
FROM `tabCustom Field` 
WHERE fieldtype = 'Link' 
AND CONCAT('tab' ,dt) in (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES) 
ORDER BY `Table Name`;

Query 2 -

SELECT  
  CONCAT('tab', parent) as `Table Name`
        ,options as `References Table`
        ,CONCAT('ALTER TABLE', '`tab', options, '` ADD CONSTRAINT `fk_',options,'_',fieldname,
        '` FOREIGN KEY ( parent ) REFERENCES `','tab',parent,'` ( name ) ON DELETE CASCADE ON UPDATE RESTRICT;') as fk_constraint
FROM 
  `tabDocField`
WHERE
  fieldtype = 'Table'
   AND CONCAT('tab' ,parent) in (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)     
UNION
SELECT  
  CONCAT('tab', dt) as `Table Name`
        ,options as `References Table`
        ,CONCAT('ALTER TABLE', '`tab', options, '` ADD CONSTRAINT `fk_',options,'_',fieldname,
 '_cust` FOREIGN KEY ( parent ) REFERENCES `','tab',dt,'` ( name ) ON DELETE CASCADE ON UPDATE RESTRICT;') as fk_constraint
FROM 
  `tabCustom Field`
WHERE
  fieldtype = 'Table'
 AND CONCAT('tab' ,dt) in (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES);

2 Likes