PowerDesigner 设计数据库中常用脚本

PowerDesigner 设计数据库中常用脚本

数据库设计

物理模型设置

Name转Comment脚本

	'******************************************************************************
'* File:     name2comment.vbs
'* Title:    Name to Comment Conversion
'* Model:    Physical Data Model
'* Objects: Table, Column, View
'* Author:   steveguoshao
'* Created: 2013-11-29
'* Mod By:   
'* Modified: 
'* Version: 1.0
'* Memo:     Modify from name2code.vbs
'******************************************************************************

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  trim(tab.comment)="" then'如果有表的注释,则不改变它.如果没有表注释.则把name添加到注释里面.
			tab.comment   =   tab.name 
			Dim   col   '   running   column 
			for   each   col   in   tab.columns 
			if trim(col.comment)="" then '如果col的comment为空,则填入name,如果已有注释,则不添加;这样可以避
				col.comment=   col.name 
			next 
		end   if 
	next
	
	Dim   view   'running   view 
	for   each   view   in   folder.Views 
		if   not   view.isShortcut and trim(view.comment)=""  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

注释转name脚本

	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 code 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 len(tab.comment) <> 0 then
         tab.name = tab.comment
    end if
    On Error Resume Next
    Dim col 'running column
    for each col in tab.columns
        if len(col.comment) <>0 then
            col.name =col.comment
         end if
        On Error Resume Next
   next
end if
next
end sub

able加前缀脚本

	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   //如果为概念模型 为PdCDM.cls_Model
MsgBox "The current model is not an Physical Data model."  
Else   
ProcessFolder mdl   
End If   
  
'This routine copy name into code 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 InStr(tab.code,"tb_") = 0 then
      tab.code="tb_"+tab.code
   end if
end if
next 
end sub

对应数据类型

类型	数据库类型	内容	长度
Integer int / INTEGER	32-bit integer	—
Short Integer	int / INTEGER	32-bit integer	—
Byte	tinyint / SMALLINT	256 values	—
Number	numeric / NUMBER	Numbers with a fixed decimal point	Fixed
Decimal	decimal / NUMBER	Numbers with a fixed decimal point	Fixed
Float	float / FLOAT	32-bit floating point numbers	Fixed
Short Float	real / FLOAT	Less than 32-bit point decimal number	—
Long Float	double precision / BINARY DOUBLE	64-bit floating point numbers	—
Money	money / NUMBER	Numbers with a fixed decimal point	Fixed
Serial	numeric / NUMBER	Automatically incremented numbers	Fixed
Boolean	bit / SMALLINT	Two opposing values (true/false; yes/no; 1/0)	—
			—
Characters	char / CHAR	Character strings	Fixed
Variable Characters	varchar / VARCHAR2	Character strings	Maximum
Long Characters	varchar / CLOB	Character strings	Maximum
Long Var Characters	text / CLOB	Character strings	Maximum
Text	text / CLOB	Character strings	Maximum
Multibyte	nchar / NCHAR	Multibyte character strings	Fixed
Variable Multibyte	nvarchar / NVARCHAR2	Multibyte character strings	Maximum
			—
Date	date / DATE	Day, month, year	—
Time	time / DATE	Hour, minute, and second	—
Date & Time	datetime / DATE	Date and time	—
Timestamp	timestamp / TIMESTAMP	System date and time	—
			—
Binary	binary / RAW	Binary strings	Maximum
Long Binary	image / BLOB	Binary strings	Maximum
Bitmap	image / BLOB	Images in bitmap format (BMP)	Maximum
Image	image / BLOB	Images	Maximum
OLE	image / BLOB	OLE links	Maximum
Other	—	User-defined data type	—

概念模型

Name转Comment脚本

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(PdCDM.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   Entity   'running     table 
	for   each   Entity   in   folder.entities 
		if   not   Entity.isShortcut   then 
		   if  trim(Entity.comment)="" then'如果有表的注释,则不改变它.如果没有表注释.则把name添加到注释里面.
			   Entity.comment   =   Entity.name 
         end if
			Dim   Attr   '   running   column 
   			for   each   Attr   in   Entity.Attributes 
   			   if trim(Attr.comment)="" then '如果col的comment为空,则填入name,如果已有注释,则不添加;这样可以避
   				   Attr.comment=   Attr.name 	 
   		      end   if 
            Next
        end if
	Next
	
	'   go   into   the   sub-packages 
	Dim   f   '   running   folder 
	For   Each   f   In   folder.Packages 
      MsgBox folder.Packages 
		if   not   f.IsShortcut   then 
			ProcessFolder   f 
		end   if 
	Next 
end   sub