使用脚本维护数据库模型表列属性
0条评论设计数据库表时需要为每列加上备注，通常列的name就是备注，但是在生成SQL时PowerDesigner不会将name作为列备注，使用vb脚本可实现将表的列的name和comment互转
菜单Tools->Execute Commands->Edit/Run Scripts...->打开对应vbs脚本文件即可执行
对脚本进行一定修改，可以实现字段code转小写，增加预定义字段，设置字段属性等
name2comment.vbs
'******************************************************************************
'* File: name2comment.vbs
'* Purpose: Database generation cannot use object names anymore
' in version 7 and above.
' It always uses the object codes.
'
' In case the object codes are not aligned with your
' object names in your model, this script will copy
' the object Name onto the object Comment for
' the Tables and Columns.
'
'* Title:
'* Version: 1.0
'* Company: Sybase Inc.
'******************************************************************************
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
' This routine copy name into comment for each table, each column and each view
' of the current folder
Private Sub ProcessFolder(folder)
Dim Tab 'running table
For Each Tab In folder.tables
If Not tab.isShortcut Then
' 表备注
Tab.comment = tab.name
Dim col ' running column
' 判断是否含必须字段列，如果没有则添加
Dim idAdd
Dim createdTimeAdd
Dim modifiedTimeAdd
Dim remarkAdd
Dim ynAdd
idAdd = False
createdTimeAdd = False
modifiedTimeAdd = False
remarkAdd = False
ynAdd = False
For Each col In tab.columns
Dim code
col.code = LCase(col.code)
code = col.code
' 如果备注包含'，1，' 则认为是枚举类型，字段类型设置为tinyint
if(InStr(col.comment, "，1，")) then
col.datatype = "tinyint"
end if
If(code = "id") Then
idAdd = True
ElseIf(code = "created_time") Then
createdTimeAdd = True
ElseIf(code = "modified_time") Then
modifiedTimeAdd = True
ElseIf(code = "remark") Then
remarkAdd = True
ElseIf(code = "yn") Then
ynAdd = True
End If
Next
Dim colToAdd
If(idAdd = False) Then
'http://nntp-archive.sybase.com/nntp-archive/action/article/%3C44db862f.601e.1681692777%40sybase.com%3E
Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
colToAdd.code = "id"
End If
If(createdTimeAdd = False) Then
Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
colToAdd.code = "created_time"
End If
If(modifiedTimeAdd = False) Then
Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
colToAdd.code = "modified_time"
End If
If(remarkAdd = False) Then
Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
colToAdd.code = "remark"
End If
If(ynAdd = False) Then
Set colToAdd = Tab.CreateObject(PdPDM.cls_Column,,-1)
colToAdd.code = "yn"
End If
For Each col In tab.columns
code = col.code
If(code = "id") Then
col.name = "主键"
col.datatype = "bigint(20)"
col.length = 20
col.identity = True
col.primary = True
'col.mandatory = True '设置了主键就不能设置mandatory值，因为主键会默认设置非空
ElseIf(code = "remark") Then
col.name = "备注"
col.datatype = "varchar(255)"
col.length = 255
col.defaultvalue = "NULL"
ElseIf(code = "created_time") Then
col.name = "创建时间"
col.datatype = "datetime"
col.defaultvalue = "CURRENT_TIMESTAMP"
ElseIf(code = "modified_time") Then
col.name = "修改时间"
col.datatype = "timestamp"
col.defaultvalue = "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
ElseIf(code = "yn") Then
col.name = "是否有效"
col.datatype = "int(11)"
col.length = 11
col.comment = "是否有效，1，有效，0，无效"
End If
'decimal默认decimal(18,4)
If(col.datatype = "decimal") Then
col.datatype="decimal(18,4)"
col.length = 18
col.precision = 4
End If
'默认int(11)
If(col.datatype = "int") Then
col.datatype="int(11)"
col.length = 11
End If
'数据库备注字段，如果为空则设置为name值
If(col.comment = "") Then
col.comment= col.name
End If
'默认值
If(col.defaultvalue = "" And col.code <> "id" And col.primary <> True And col.identity <> True) Then
col.defaultvalue = "NULL"
End If
'数据类型
If(col.datatype = "") Then
col.datatype = "varchar(255)"
col.length = 255
End If
Next
End If
Next
Dim view 'running view
For Each view In folder.Views
If Not view.isShortcut Then
view.comment = view.name
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
comment2name.vbs（常用于逆向生成）
'******************************************************************************
'* File: comment2name.vbs
'* Purpose: Database generation cannot use object names anymore
' in version 7 and above.
' It always uses the object codes.
'
' In case the object codes are not aligned with your
' object names in your model, this script will copy
' the object Comment onto the object Name for
' the Tables and Columns.
'
'* Title:
'* Version: 1.0
'* Company: Sybase Inc.
'******************************************************************************
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
' This routine copy name into comment for each table, each column and each view
' of the current folder
Private Sub ProcessFolder(folder)
Dim Tab 'running table
For Each Tab In folder.tables
If Not tab.isShortcut Then
'表备注
If(tab.comment <> "") Then
Tab.name = tab.comment
End If
Dim col ' running column
For Each col In tab.columns
If(col.comment <> "") Then
'数据库备注字段
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.comment = view.name
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
发表新评论