PowerDesigner is a very powerful database design software. Skilled use of PowerDesigner can make database design efficient and concise. The specific operations of PowerDesign are described in detail in the help file (press F1), here is just a list of commonly used operations. PowerDesigner16.5 cracked Chinese package: https://download.csdn.net/download/z645817/11221804
1. If you want to learn more about some commands and operations that are customized in PowerDesign for writing VBS scripts, specific You can refer to the script examples in the $PWD\PowerDesigner 9\VB Scripts directory.
Run VB scripts in Tools->Execute Commands. The operating instructions are clearly written in the help document F1. The location of the help document is Customizing and Extending PowerDesigner> Scripting PowerDesigner> Running Scripts in PowerDesign.
Configure unified generation rules
2, modify the rules for creating the table script. If each table has the same fields, you can modify it as follows:
Database -> Edit Current DBMS expand Script -> Object -> Table -> Create See the Value value at the bottom right, you can directly Amend as follows:
/* tablename: %TNAME% */
create table [%QUALIFIER%]%TABLE% (
%TABLDEFN%
ts char(19) null default convert(char(< span style="color: #800080;">19),getdate(),20),
dr smallint null default 0
)
[%OPTIONS%]
The ts and dr two columns will be automatically inserted into each table when the SQL script is generated. The %TNAME The% variable is to add a name value comment of the table to the SQL of each table.
3. Modify the field generation rules. To add a comment to each field, expand Script -> Object -> Column -> Add in Database -> Edit Current DBMS and modify the Value to:
% 20:COLUMN% [%COMPUTE%?AS (%COMPUTE%):%20 :DATATYPE% [%IDENTITY%?%IDENTITY%:[%NULL%][%NOTNULL%]][ default %DEFAULT%]
[[constraint %CONSTNAME%] check (%CONSTRAINT%)]]/*%COLNNAME%*/
where %COLNNAME% is the column Name value ( It can be Chinese).
4. Modify the naming rules of foreign keys. Select Database —> Edit Current DBMS Select Scripts -> Objects -> Reference -> ConstName
You can find the Value on the right is:
FK_%.U8:CHILD %_%.U9:REFR%_%.U8:PARENT%
The naming method is:’FK_’+8-digit child table name+9-digit reference name+8-digit parent table name , Can be customized according to this model as:
FK_%.U7:CHILD%_RELATIONS_%.U8:PARENT%
You can change the name of FK to FK_TABLE_1_RELATIONS_PARENT_1. After mastering this method, you can modify it according to your own ideas.
5, Generate the header comments in the SQL file of the database creation script is very annoying, you can use Databse -> Generate Database (Ctrl+G ) In the window, select the Format card and remove the Title hook option of Script conventions.
6. Add foreign key: Model -> References After creating a new foreign key, double-click to enter the foreign key properties. In the “Joins” card, you can select the foreign key field of the sub-table.
7, do not include the drop statement when generating the code
in the menu Database->Generate Database, select in the pop-up window On the Options tab, remove all the Drop options on the right side of the window.
8. The field default value in the generated code does not need to be enclosed in single quotation marks. For example, default’to_char(sysdate,’yyyymmdd’)’ should be changed to default to_char(sysdate,’yyyymmdd’)
In the menu Database->Edit Current DBMS…, select the General tab, select Script->Quote on the left side of the window, and then remove the single quotes after Value on the right side of the window.
9.去掉生成的建表脚本中对象的双引号
打开PD选择Tools－Model Options－ Naming Convention, set the Charter case option of the label of Name and Code on the right to Uppercase or Lowercase, as long as it is not Mixed Case.
10、When creating a table, when modifying the field, modify the content of the name, and the code will also change. How to make the code not change with the name
There is a button “=” on the right side of the Name and Code. If you need to not synchronize, just pop this button up and click “=” directly to synchronize.
Modification method: modify in the option menu in PowerDesign, in Tool -> General Options->Dialog->Operating modes->Name to Code mirroring, here the default is to synchronize the name and code, uncheck the checkbox.
11. The problem that the mysql version cannot generate views
After opening the PDM using MySQL5.0, select Database->Edit Current DBMS in the menu..->Confirm that the DBMS uses MySQL5.0
Open the tree structure Script->Objects->View -> Create on the left end, and write in the Value on the right end
create VIEW [%R%?[ < span style="color: #0000ff;">if not exists]] %VIEW%
as
%SQL%
Select Drop and write in Value on the right end
drop table if exists %VIEW%
Select Enable, select Yes in the Value on the right, and confirm to save.
Note that this modification only modifies the settings of the PowerDesigner program, and only needs to be configured once; but it does not modify the PDM file, and PowerDesigner that has not been modified cannot generate SQL for the view.
12. Why does the Existence of index warning appear after creating a table?
A table should contain at least one column, one index, one key, and one reference.
It is not necessary to check the Existence of index item, this warning does not affect the creation of the table. The warning means that the table is not indexed, and a table generally has at least one column, one index, and one primary key.
13. When PDM generates the table creation script, an error occurs when the field exceeds 15 characters
The solution is to open PDM, The Database menu bar appears, go to Database -> Edit Current DBMS ->script->objects->column->maxlen, and increase the value (originally 30), for example, change it to 60. If the length of the table or other objects also has this kind of error, you can choose the corresponding objects to change it in this way, or use the following method:
● The Database generation prompt will pop up when the table building script is generated Box: Remove the small tick of options-check model, that is, do not check (not recommended)
14. How to prevent one-to-one relationship from generating two references (foreign keys)
must be defined The dominant direction of the relationship, the dominant entity (marked with D) becomes the parent table. Double-click the one-to-one relationship in cdm ->Detail->Dominant role to select the dominance relationship
15. Modify the type of mysql table, such as changing the default myisam to innodb
modify a single The table is in the properties window of the table, select the Physcial Options tab, double-click type=(ISAM) on the left side of the window on the right side of the window, then select sql at the lower right side of the window, and then modify it to type=(innodb)
If you modify The database type is innodb type, open the properties window of the model, select the MySQL tab, and enter InnoDB in the edit box after Database type. Then select the menu database->default physical option, and then double-click on the left side of the pop-up window to select type=[ISAM], type=[ISAM] appears on the right side of the window, click it with the mouse, and select innodb after type at the bottom of the window , And finally click the “Apply to…” button to select all the tables.
16. There is no notes attachedwith comments
PowerDesigner can support any kind of relationship when generating MSSQL database tables directly from the model The model design of the type database, because the characteristic definition of each database model in PowerDesigner is stored in an xdb file, we can find it under [PowerDesigner_Install_Home]/Resource Files/DBMS. Of course, we can also create a new xdb ourselves and define the characteristics of the database model we want to support.
Let’s briefly explain that PowerDesinger16 Enterprise Edition is used. Some simplified versions of Sybase (such as SQL Moduler) cannot customize xdb. Please pay attention.
●From the menu TOOLS->RESOUCES->DBMS, enter the custom data model
●PowerDesigner pops up a dialog box, and the list box lists the database models currently supported by PowerDesigner. Choose to create a new one. Enter the name SQL SERVER 2010 (EXTENDED) and choose to copy from the existing SQL SERVER 2010, so that SQL SERVER 2010 (EXTENDED) contains all the features of the original SQL SERVER 2010 model.
●Next, we need to add features that support automatic generation of table and column comment codes for SQL Server 2010 (Extended). PowerDesinger organizes the defined characteristics in a tree structure. The work to be done in this article is to define the Script code characteristics. We need to modify the script feature codes in Script\Objects\Table\TableComment and Script\Objects\Column\ColumnComment.
●SQL Server 2010 saves table and column comments in the database by calling the system stored procedure sp_addextendedproperty. Then we only need to write
table-level comment code:
EXECUTE sp_addextendedproperty N'< /span>MS_Description', N'%COMMENT%', N'user', N'%OWNER%' , N'table'< /span>, N'%TABLE%', NULL, NULL
column-level comment code is:
EXECUTE sp_addextendedproperty N' MS_Description', N'%COMMENT%', N'user', N'< span style="color: #800000;">%OWNER%', N' table', N'%TABLE%', N'column', N'%COLUMN%'
%COMMENT% is equivalent to replacing variables. PowerDesigner automatically replaces the value of the replacement variable in the design model when generating the script.
●Save SQL Server 2010 (Extended) as an xdb file.
●Now, you can create a new Physical Data Model, select SQL Server 2010 (Extended) for the database model. To create a table, it should be noted that a database user must be designated, generally using dbo. We can add comments to both the table and the column.
● To generate a database script, menu Databases->Generate Database. Note that the comment option must be selected. Click OK to get the final library building script. Open it and see, we see that PowerDesinger generates the corresponding code according to the rules we told it
EXECUTE sp_addextendedproperty N' MS_Description', N'Employee Information', N'user', N'dbo', N 'table', N'Employee' , NULL, NULL
go
EXECUTE sp_addextendedproperty N'MS_Description', N'Primary key ID, automatically increase span>', N' user', N'dbo', N'table', N'Employee', N' column', N'< /span>EmployeeID‘
go
●We execute this script in SQL Server 2010, and then these comments can be seen through Enterprise Manager.
17, display the NAME list in the scrip
modify the field generation rules. To add a comment to each field, expand the Value of Script -> Object -> Column -> Add in the same window and modify it to:
%20:COLUMN% [%COMPUTE%?AS (%COMPUTE%):%20:DATATYPE% [ %IDENTITY%?%IDENTITY%:[%NULL%][%NOTNULL%]][ default %DEFAULT%]
[[constraint %CONSTNAME%] check (%CONSTRAINT%)]]/*%COLNNAME%*/
where %COLNNAME% is the column Name value ( Can be Chinese)
18、Identity
View the properties of the table in PDM, Columns tab, select the entire column, view the column properties, click the properties icon (the one with the hand-shaped pattern) on the upper left, this When opening a setting window, set in the General tab, there is an Identity check box in the General tab of the column properties, just check it.
19. Generate comments for sql, the operation is as follows, using PowerDesigner16.5, select Tools -> Excute commands->Edit/Run Script to open the window to add the following information, execute run
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. span>"
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
'Use the indication as a table comment, but you don’t need to do this
tab.comment = tab.name
Dim col ‘ running column
for each col in tab.columns
‘Combine column name and comment into comment
col.comment= col.name
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
20, replace the database sql statement
select Database->change current DBMS, select the corresponding database in the DBMS, For MySQL, click OK, then select Database—“Generate Database option, select the save path of the generated sql file in the pop-up window, click OK, you can see that the generated sql language is completely in accordance with the MySQL standard.
21、New conceptual model(conceptual Data Model)
File–>New Model–>Conceptual Data Mode or click Work area, right click–>New Model–>Conceptual Data Mode
22. When opening the model, there will be a grid line, remove the grid line : Tools->Dispaly Preferences->Diagram to check Show page delimiter.
23, generate annotated sql, Database–>Generate DataBase… into the pop-up box. On the Options tab, you can select the content of the script to survive (Generate name in enpty comment), you can choose whether to create a database script, select the table to generate the script, and the file encoding method in the selection, select the module you need to generate SQL, and specify the output Catalog etc.
/* tablename: %TNAME% < /span>*/
create table [%QUALIFIER%]%TABLE% (
%TABLDEFN%
ts char(19) null default convert(char(< span style="color: #800080;">19),getdate(),20),
dr smallint null default 0
)
[%OPTIONS%]
%20:COLUMN% [% COMPUTE%?AS (%COMPUTE%):%20:DATATYPE% [%IDENTITY%?%IDENTITY%:[%NULL%][%NOTNULL%] ][ default %DEFAULT%]
[[constraint %CONSTNAME%] check (%CONSTRAINT%)]]/*%COLNNAME%*/
FK_%.U8:CHILD%_ %.U9:REFR%_%.U8:PARENT%
FK_%.U7:CHILD%_RELATIONS_%.U8:PARENT%
< p>
create VIEW [%R%?[ if not exists]] %VIEW%
as
%SQL%
drop table if exists %VIEW%
EXECUTE sp_addextendedproperty N'MS_Description', N'%COMMENT% ', N'user< /span>', N'%OWNER%', N'table', N'%TABLE%', NULL, NULL
EXECUTE sp_addextendedproperty N'MS_Description', N'%COMMENT%', N'< /span>user', N'%OWNER%', N'table', N'%TABLE%' , N'column'< /span>, N'%COLUMN%'
EXECUTE sp_addextendedproperty N'MS_Description', N'Employee Information', N 'user', N'dbo' , N'table', N'Employee', NULL, NULL
go
EXECUTE sp_addextendedproperty N'MS_Description', N'Primary key ID, automatically increase span>', N' user', N'dbo', N'table', N'Employee', N' column', N'< /span>EmployeeID‘
go
%20:COLUMN% [%COMPUTE%?AS (%COMPUTE%):%20:DATATYPE% [%IDENTITY%?%IDENTITY%:[%NULL%][%NOTNULL%]][ default %DEFAULT%]
[[constraint %CONSTNAME%] check (%CONSTRAINT%)]]/*%COLNNAME%*/
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. span>"
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
'Use the indication as a table comment, but you don’t need to do this
tab.comment = tab.name
Dim col ‘ running column
for each col in tab.columns
‘Combine column name and comment into comment
col.comment= col.name
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