powerdesigner反向示例（sql to power）

本文介绍

导出数据库建表语句
导出数据库表备注（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