powerdesigner反向示例（sql to power）
2020-02-27 本文已影响0人
tony_zhang
- 本文介绍
- 导出数据库建表语句
- 导出数据库表备注（alter table 语句），并和步骤1内容合并到sql文件
- 将合并后的sql文件导入powerdesigner
- powerdesigner中使用脚本运行设置entity字段列内容（字段中文名、字段英文名、字段类型、索引类型）
（设置列内容脚本见文末） - powerdesigner中使用脚本运行将entity模型导出生成数据库字典到Excel
（生成Excel脚本见文末）
- 概念
- msyql排序规则
utf-8有默认的排序规则，查看命令
show charset like 'utf8%';
utf8_general_ci
不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs
区分大小写，如果用户名和邮箱用这个 就会造成不良后果。
utf8_bin
字符串每个字符串用二进制数据编译存储，区分大小写，可以存二进制的内容。
概述：utf8_unicode_ci比较准确，utf8_general_ci速度比较快。通常情况下 utf8_general_ci的准确性就够我们用的，在我看过很多程序源码后，发现它们大多数也用的是utf8_general_ci，所以新建数据 库时一般选用utf8_general_ci就可以。
- 整理数据库表字典
- 导出表结构（示例使用Navicat），示例如下。
//树状菜单表
CREATE TABLE `demo_menu` (
`id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键' ,
`pid` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父级菜单id' ,
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单名称' ,
`code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单编码' ,
`pcode` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父级菜单编码' ,
`type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单类型' ,
`icon` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜单图标' ,
`priority` int(11) NULL DEFAULT NULL COMMENT '展示顺序' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='菜单表'
;
- 整理数据库表备注，示例如下。
//导出语句
select
concat(
'alter table ',
t.table_name,
' comment \'',
t.table_comment,
'\'',
';'
) as '表格备注'
from
information_schema.`tables` t
where
t.table_schema like '%demo_databases%'
and t.table_name like '%demo_menu%';
//导出结果
alter table demo_menu comment '菜单表';
alter table demo_user comment '用户表';
3.将第1步导出结果和第1步结果合并到demo.sql文件。
4.使用powerdesigner导入demo.sql，如下图。
-
调整entity字段中文。
6.导出数据库字典到Excel
- 上述代码。
- 代码示例，调整entity字段中文
Option Explicit
ValidationMode = True
InteractiveMode = im_Batch
Dim mdl ' the current model
' get the current active model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
MsgBox "There is no current Model "
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
MsgBox "The current model is not an Physical Data model. "
Else
ProcessFolder mdl
End If
Private sub ProcessFolder(folder)
On Error Resume Next
Dim Tab 'running table
for each Tab in folder.tables
if not tab.isShortcut then
tab.name = tab.comment
Dim col ' running column
for each col in tab.columns
if col.comment="" then
else
col.name= col.comment
end if
next
end if
next
Dim view 'running view
for each view in folder.Views
if not view.isShortcut then
view.name = view.comment
end if
next
' go into the sub-packages
Dim f ' running folder
For Each f In folder.Packages
if not f.IsShortcut then
ProcessFolder f
end if
Next
end sub
- 代码示例，数据库字段导出Excel
'******************************************************************************
Option Explicit
Dim rowsNum
rowsNum = 0
'-----------------------------------------------------------------------------
' Main function
'-----------------------------------------------------------------------------
' Get the current active model
Dim Model
Set Model = ActiveModel
If (Model Is Nothing) Or (Not Model.IsKindOf(PdPDM.cls_Model)) Then
MsgBox "The current model is not an PDM model."
Else
' Get the tables collection
'创建EXCEL APP
dim beginrow
DIM EXCEL, SHEET, SHEETLIST
set EXCEL = CREATEOBJECT("Excel.Application")
EXCEL.workbooks.add(-4167)'添加工作表
EXCEL.workbooks(1).sheets(1).name ="表结构"
set SHEET = EXCEL.workbooks(1).sheets("表结构")
EXCEL.workbooks(1).sheets.add
EXCEL.workbooks(1).sheets(1).name ="表目录"
set SHEETLIST = EXCEL.workbooks(1).sheets("表目录")
ShowTableList Model,SHEETLIST
ShowProperties Model, SHEET,SHEETLIST
EXCEL.workbooks(1).Sheets(2).Select
EXCEL.visible = true
'设置列宽和自动换行
sheet.Columns(1).ColumnWidth = 30
sheet.Columns(2).ColumnWidth = 20
sheet.Columns(3).ColumnWidth = 30
sheet.Columns(4).ColumnWidth = 20
sheet.Columns(5).ColumnWidth = 10
sheet.Columns(6).ColumnWidth = 10
sheet.Columns(1).WrapText =true
sheet.Columns(2).WrapText =true
sheet.Columns(4).WrapText =true
'不显示网格线
EXCEL.ActiveWindow.DisplayGridlines = True
End If
'-----------------------------------------------------------------------------
' Show properties of tables
'-----------------------------------------------------------------------------
Sub ShowProperties(mdl, sheet,SheetList)
' Show tables of the current model/package
rowsNum=0
beginrow = rowsNum+1
Dim rowIndex
rowIndex=3
' For each table
output "begin"
Dim tab
For Each tab In mdl.tables
ShowTable tab,sheet,rowIndex,sheetList
rowIndex = rowIndex +1
Next
if mdl.tables.count > 0 then
sheet.Range("A" & beginrow + 1 & ":A" & rowsNum).Rows.Group
end if
output "end"
End Sub
'-----------------------------------------------------------------------------
' Show table properties
'-----------------------------------------------------------------------------
Sub ShowTable(tab, sheet,rowIndex,sheetList)
If IsObject(tab) Then
Dim rangFlag
rowsNum = rowsNum + 1
' Show properties
Output "================================"
sheet.cells(rowsNum, 1) = tab.code
'sheet.cells(rowsNum, ).HorizontalAlignment=3
'sheet.cells(rowsNum, 3) = ""
'sheet.cells(rowsNum, 4) = "表说明"
sheet.cells(rowsNum, 2) = tab.comment
sheet.Range(sheet.cells(rowsNum, 2),sheet.cells(rowsNum, 4)).Merge
'设置超链接，从目录点击表名去查看表结构
'字段中文名 字段英文名 字段类型 注释 是否主键 是否非空 默认值
sheetList.Hyperlinks.Add sheetList.cells(rowIndex,2), "","表结构"&"!B"&rowsNum
rowsNum = rowsNum + 1
sheet.cells(rowsNum, 1) = "字段英文名"
sheet.cells(rowsNum, 2) = "字段类型"
sheet.cells(rowsNum, 3) = "字段注释"
sheet.cells(rowsNum, 4) = "是否非空"
'设置边框
sheet.Range(sheet.cells(rowsNum-1, 1),sheet.cells(rowsNum, 4)).Borders.LineStyle = "1"
'sheet.Range(sheet.cells(rowsNum-1, 4),sheet.cells(rowsNum, 4)).Borders.LineStyle = "1"
'字体为10号
sheet.Range(sheet.cells(rowsNum-1, 1),sheet.cells(rowsNum, 4)).Font.Size=10
Dim col ' running column
Dim colsNum
colsNum = 0
for each col in tab.columns
rowsNum = rowsNum + 1
colsNum = colsNum + 1
sheet.cells(rowsNum, 1) = col.code
'sheet.cells(rowsNum, 3) = ""
'sheet.cells(rowsNum, 4) = col.name
sheet.cells(rowsNum, 1) = col.code
sheet.cells(rowsNum, 2) = col.datatype
sheet.cells(rowsNum, 3) = col.comment
If col.Mandatory = true Then
sheet.cells(rowsNum, 4) = "是"
Else
sheet.cells(rowsNum, 4) = "否"
End If
next
sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,4)).Borders.LineStyle = "3"
'sheet.Range(sheet.cells(rowsNum-colsNum+1,4),sheet.cells(rowsNum,4)).Borders.LineStyle = "3"
sheet.Range(sheet.cells(rowsNum-colsNum+1,1),sheet.cells(rowsNum,4)).Font.Size = 10
rowsNum = rowsNum + 1
Output "FullDescription: " + tab.Name
End If
End Sub
'-----------------------------------------------------------------------------
' Show List Of Table
'-----------------------------------------------------------------------------
Sub ShowTableList(mdl, SheetList)
' Show tables of the current model/package
Dim rowsNo
rowsNo=1
' For each table
output "begin"
SheetList.cells(rowsNo, 1) = "数据库名称"
SheetList.cells(rowsNo, 2) = "表英文名"
SheetList.cells(rowsNo, 3) = "表注释"
rowsNo = rowsNo + 1
SheetList.cells(rowsNo, 1) = mdl.name
Dim tab
For Each tab In mdl.tables
If IsObject(tab) Then
rowsNo = rowsNo + 1
SheetList.cells(rowsNo, 1) = ""
SheetList.cells(rowsNo, 2) = tab.code
SheetList.cells(rowsNo, 3) = tab.comment
End If
Next
SheetList.Columns(1).ColumnWidth = 20
SheetList.Columns(2).ColumnWidth = 30
SheetList.Columns(3).ColumnWidth = 30
output "end"
End Sub
