1、故事背景

最近在搞一个项目所以下班有点晚,坐在地铁上习惯性的拿出Ipad刷一会研究生课程。突然手机响了,一看原来是组织语音会议, 讨论客户需要系统的er图,有400多张要求晚上搞定明天发出去。由于我们没设置外键约束,因此用powerdesigner无法把表之间的关联关系展示出来。由于表数量多、时间紧急,大家讨论说晚上可能时间来不及,建议和用户沟通延后一下。

2、冷静分析

然后我沉默了一会,突然灵感闪现,说把所有的表结构sql给我,到家后看看(没有当场给出答复,毕竟谋定而后动!)

2.1、难点

  • 时间紧急、工作量大;
  • 画关联关系比较复杂,繁琐;

2.2、思维发散

  • 我能找到一些规律吗?是否可以用程序实现?毕竟用计算机能干的工作,就别让人做。

分析sql提取特征

下面给出sql demo

create table `term_relationships`
(
`object_id` bigint(20) unsigned not null default 0 comment '对应文章ID/链接ID',
`term_taxonomy_id` bigint(20) unsigned not null default 0 comment '对应分类方法ID',
`term_order` int(11) not null default 0 comment '排序',
primary key (`object_id`, `term_taxonomy_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

alter table term_relationships comment '文章属性关系表';

/*==============================================================*/
/* Table: term_taxonomy */
/*==============================================================*/
create table `term_taxonomy`
(
`term_taxonomy_id` bigint(20) unsigned not null auto_increment comment 'ID',
`description` longtext comment '说明',
`parent` bigint(20) unsigned not null default 0 comment '属父分类方法ID',
`count` bigint(20) not null default 0 comment '文章数统计',
`site_id` bigint(20) comment '站点id',
primary key (`term_taxonomy_id`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

alter table term_taxonomy comment '栏目';

经过分析,大部分表外键的命名方式是:被引用表的表名+下划线+id

2.3、解决办法

既然有规律,而且领导意思是准确度可以没那么高,那我就用Python写一个文本解析工具,自动生成外键sql然后再逆向生成er图。

3、方案设计

3.1、数据结构设计

遍历所有行,把表名和键名存储在字典中,数据设计如下:

晚上8点在地铁上收到boss需求:把400多张表的关系画出来明早客户要!_外键

其中绿色的是key

3.2、遍历全部sql文本,把表名和主键放在dic里面

处理流程如下

晚上8点在地铁上收到boss需求:把400多张表的关系画出来明早客户要!_表名_02

3.3、再次遍历sql文本,生成外键sql.

处理流程如下

晚上8点在地铁上收到boss需求:把400多张表的关系画出来明早客户要!_1024程序员节_03

4、talk is cheap,show me the code

上代码

import math
import re

primayKeyTableMap = {}


class Stack(object):

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

def push(self, data):
"""
进栈函数
"""
self.stack.append(data)

def pop(self):
"""
出栈函数,
"""
return self.stack.pop()

def gettop(self):
"""
取栈顶
"""
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、总结
  • 本文章利用了python的dic,对应数据结构是map,同时利用了stack存储临时值。
  • 利用了正则来匹配文本
  • 使用了python
6、题外话
  • 敢于打破常规、敢于尝试。

利用程序,2小时我完成了工作,避免了繁琐的画图和加班,哈哈!

最后祝大家1024快乐!