1. Background
Recently I was working on a project, so I got off work a little late. I used to take out my Ipad and brush up my postgraduate courses while sitting on the subway. Suddenly the mobile phone rang, a look at the original organization voice meeting, discussing the customer needs the system er chart, there are more than 400 requests to finish the evening and send tomorrow. Because we did not set the foreign key constraint, powerDesigner cannot display the associations between tables. Due to the large number of watches and urgent time, we discussed that there might not be enough time in the evening, so we suggested to communicate with the user to postpone.
2. Calm analysis
Then I was silent for a while, and suddenly I had a flash of inspiration and said give me all the table structure SQL and look at it when I get home.
2.1, the difficulty
- Urgent time and heavy workload;
- Drawing correlation is more complex and tedious;
2.2. Divergent thinking
- Can I find some pattern? Can it be implemented programmatically? After all, don’t let people do what a computer can do.
Analyze the FEATURES of SQL extraction The following SQL demo is presented
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 'order'.primary key (`object_id`, `term_taxonomy_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
alter table term_relationships comment 'Article Property Relation Table';
/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
/* Table: term_taxonomy */
/ * = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = * /
create table `term_taxonomy`
(
`term_taxonomy_id` bigint(20) unsigned not null auto_increment comment 'ID',
`description` longtext comment 'that',
`parent` bigint(20) unsigned not null default 0 comment 'Parent classification method ID',
`count` bigint(20) not null default 0 comment 'Article 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 'section';
Copy the codeAfter analysis, most foreign keys are named as follows: table name + underscore + ID of the referenced table
2.3. Solutions
Since there is a pattern, and the leader is saying the accuracy can be less high, I will write a text parsing tool in Python, automatically generate foreign key SQL and then reverse generate ER graph.
3. Scheme design
3.1 data structure design
Iterate over all rows and store the table and key names in the dictionary. The data design is as follows:
The green one is key
3.2. Iterate over all SQL text and place table names and primary keys in DIC
The processing process is as follows
3.3. Iterate through the SQL text again to generate the foreign key SQL.
The processing process is as follows
4, Talk is cheap,show me the code
In the 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) :
""" "Stack function, """
return self.stack.pop()
def gettop(self) :
""" Take top of stack ""
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()
Copy the code5, summary
- This article utilizes Python’s DIC, the corresponding data structure is map, and uses stack to store temporary values.
- Re is used to match text
- Using the python
6. Digress
- Dare to break the rules and experiment.
Using the program, I finished the work in 2 hours, avoiding tedious drawing and overtime, haha!
Finally, I wish you all a happy 1024!