1. Story background

I was working on a project recently, so I got off work a little late. Sitting on the subway, I habitually took out my Ipad and brushed graduate courses for a while. Suddenly the mobile phone rang. It turned out that it was organizing a voice conference to discuss the er diagram of the customer's need for the system. More than 400 required to be completed in the evening and sent out tomorrow. Because we do not set foreign key constraints, we cannot show the association relationship between tables with powerdesigner. Due to the large number of watches and urgent time, it was discussed that it might be too late in the evening. It is suggested to postpone the communication with the user.

2. Calm analysis

Then I was silent for a while. Suddenly, my inspiration flashed and said to give me all the table structure sql and have a look at it when I got home (I didn't give an answer on the spot, after all, plan and move!)

2.1 difficulties

• Urgent time and heavy workload;
• The relationship between paintings is complex and cumbersome;

2.2 divergent thinking

• Can I find some rules? Can it be implemented by program? After all, don't let anyone do a job that can be done with a computer.

Analyze sql and extract features
The sql demo is given below

create table `term_relationships`
(
   `object_id`            bigint(20) unsigned not null default 0 comment 'Corresponding article ID/link ID',
   `term_taxonomy_id`     bigint(20) unsigned not null default 0 comment 'Corresponding classification method ID',
   `term_order`           int(11) not null default 0 comment 'sort',
   primary key (`object_id`, `term_taxonomy_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

alter table term_relationships comment 'Article attribute relation table';

/*==============================================================*/
/* Table: term_taxonomy                                         */
/*==============================================================*/
create table `term_taxonomy`
(
   `term_taxonomy_id`     bigint(20) unsigned not null auto_increment comment 'ID',
   `description`          longtext comment 'explain',
   `parent`               bigint(20) unsigned not null default 0 comment 'Generic parent classification method ID',
   `count`                bigint(20) not null default 0 comment 'Article number statistics',
   `site_id`              bigint(20) comment 'site id',
   primary key (`term_taxonomy_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

alter table term_taxonomy comment 'column';

After analysis, most table foreign keys are named as follows: table name + underscore + id of the referenced table

2.3 solutions

Since it is regular and the leader means that the accuracy can not be so high, I will write a text parsing tool in Python to automatically generate foreign key sql, and then reverse generate er graph.

3. Scheme design

3.1 data structure design

Traverse all rows and store the table name and key name in the dictionary. The data design is as follows:

The green one is key

3.2. Traverse all sql text and put the table name and primary key in dic

The processing flow is as follows

3.3. Traverse the sql text again to generate foreign key sql

The processing flow is as follows

4,talk is cheap,show me the code

Upper code

import math
import re

primayKeyTableMap = {}


class Stack(object):

    def __init__(self):
        self.stack = []

    def push(self, data):
        """
        Stack function
        """
        self.stack.append(data)

    def pop(self):
        """
        Out of stack function,
        """
        return self.stack.pop()

    def gettop(self):
        """
        Take stack top
        """
        return self.stack[-1]
    def __len__(self):
        return len(self.stack)


stack1 =  Stack()
list1 = []


def findTableAndPrimayKey():
    p1 = re.compile(r'[`](.*?)[`]', re.S)
    with open('D:/1.sql','r', encoding='utf-8') as infile:
        for line in infile:
           if 'CREATE TABLE' in line :
                tableName = re.findall(p1, line)[0]
                if len(stack1) != 0 :
                    tempTableKey = stack1.pop()
                    tempTableName = stack1.pop()
                    if len(tempTableKey) > 2:
                        # print(tempTableKey)
                        primayKeyTableMap[tempTableKey] = [tempTableName,tempTableKey]
                    else:
                        # print(tempTableName+'_'+tempTableKey)
                        primayKeyTableMap[removePre(tempTableName)+'_'+tempTableKey] = [tempTableName,tempTableKey]
                stack1.push(tableName)

           if 'PRIMARY KEY' in line :
               keyName = re.findall(p1, line)
               stack1.push(keyName[0])

def addForeignKey():
    tableName = ''
    keyName = ''
    p1 = re.compile(r'[`](.*?)[`]', re.S)
    with open('D:/1.sql','r', encoding='utf-8') as infile:
        for line in infile:
           if 'CREATE TABLE' in line :
                tableName = re.findall(p1, line)[0]
                # stack1.push(tableName)
           elif '_USER_ID' in line or '_user_id' in line:
               foreignCloumn = re.findall(p1, line)
               sql = 'alter table '+tableName+' add foreign key('+foreignCloumn[0]+') references  Z_IS_USER(USER_ID)  on delete cascade on update cascade; '
               print(sql)     
           else :
                foreignCloumn = re.findall(p1, line)
                if len(foreignCloumn) > 0 and  foreignCloumn[0] in primayKeyTableMap and  primayKeyTableMap[foreignCloumn[0]][0]!=tableName :
                    sql = 'alter table '+tableName+' add foreign key('+foreignCloumn[0]+') references '+primayKeyTableMap[foreignCloumn[0]][0]+'('+primayKeyTableMap[foreignCloumn[0]][1]+') on delete cascade on update cascade; '
                    print(sql)
               


def removePre(tableName):
    return tableName.replace("IS_", "").replace("is_", "").replace("P_", '').replace('QRTZ_','').replace('Z_IS_','').replace('MS_','').replace('acl_','')

def main():
    findTableAndPrimayKey()    
    # print(primayKeyTableMap)
    addForeignKey()

main()

5. Summary

• This article uses the dic of python, the corresponding data structure is map, and uses stack to store temporary values.
• Regular is used to match text
• python is used

6. Digression

• Dare to break the rules and try.

Using the program, I finished my work in 2 hours, avoiding cumbersome drawing and overtime, ha ha!
​

Finally, I wish you all happiness!