Weng Yan
PowerDesigner is the famous product of Sybase. I started from 16-bit's Windows, I should have a deep feelings. PowerDesigner is a cutlery designed by DBA and software architects. With the continuous upgrade of the version, PowerDesigner provides more powerful features. In order to start with this article, introduce you to the use skills in PowerDesinger to help you use this Case tool more efficiently.
statement of problem
I used to see a friend asking in many online PHYSICAL Model Design, in addition to Oracle, selecting other database models, and finally generating a database script when generating a database script, the database script of the record table and column comment information is not automatically generated. Code.
Please explain the concept of this problem first, we design the Oracle database through PowerDesigner, and finally get the script can be like this, where the Comment statement is the scripting statement that I have indicated in Oracle in Oracle, execute it in Oracle Once, you can get this information by querying the Oracle data dictionary. The advantage of this is that the table structure is relatively convenient, and it is not necessary to find it from the database documentation. For the team development, there is a meaning of application.
/ * ======================================================== * / / * Table: EMP * / / * ========================================= ====== * / Create Table EMP ("Empid" Number, "Empname" varchar2 (50), SEX Char (1)) / Comment On Table EMP IS 'Staff Information' / Comment On Column EMP. "Empid" IS 'staff number' / Comment on column EMP. "Empname" IS 'staff name' / comment on column Emp.sex is 'Gender F: Female M: Male' /
SQL Server has such a statement, however, everyone often discovers that the database model is SQL Server, when you automatically generate a library script, even if you choose the option to generate the comment statement, PowerDesigner will not help you generate it. PowerDesigner does not support SQL Server, the answer is obviously negative.
Problem PowerDesigner This tool can support the model design of any relational database, because all database models in PowerDesigner are stored in an XDB file, we can be in [PowerDesigner_Install_Home] / Resource Files / DBMS Can be found under. Of course, we can also create a new XDB yourself, define the characteristics of the database model we want to support. Understand this, the answer to this question is obvious.
Below I will simply explain, I am using the PowerDesinger9.5 Enterprise Edition. Sybase Some simplified versions (such as SQL Moduler) cannot customize XDB, please pay attention.
1 From the menu Tools-> Resouces-> DBMS, enter the custom data model, see Figure 1.
figure 1
2 PowerDesigner pops up a dialog, listing the current PowerDesigner supported in the list box
Database model (Figure 2). We choose to create a new one. Enter the name SQL Server 2000 (Extended), select replication from existing SQL Server 2000, so SQL Server 2000 (Extended) contains all the features owned by the SQL Server2000 model (Figure 3).
figure 2
image 3
3 Next, we need to add support to SQL Server 2000 (Extended) to automatically generate the characteristics of the table and column release code. PowerDesinger organizes the defined characteristics to organize a tree structure (Figure 4), this article is to do to define the Script code characteristics. We need to modify script feature codes in script / objects / table / TableComment and Script / Objects / Column / ColumnComment.
4 SQL Server 2000 Save Table and Collections in the Database are done by calling the system stored procedure sp_addextendedProperty. Then we only need, write separately
Table-class comment code is: execute sp_addextendedProperty N'MS_DEXTENDEDPROPERTY N'MS_DESBRIPTION ', N'% Comment% ', N'User', N ', N', N '% Table%', NULL, NULL level The comment code is EXECUTE SP_ADDEXTENDEDPROPERTY N'MS_DEXTENDEDPROPERTY N'MS_DESCRIPTION ', N'% Comment% ', N'User', N '% Owner', N'TABLE ', N'% Table% ', N'Column', N '% COLUMN% '
Let's take a look at these codes and immediately understand what is going on (Figure 5). % Comment% is equivalent to replacement variables, and PowerDesigner automatically replaces the value of replacing variables in the design model when generating scripts.
Figure 4
Figure 5
5 Save SQL Server 2000 (extended) into an XDB file.
6 Now we can create a new Physical Data Model, the database model selection SQL Server 2000 (Extended). Building a table, requiring reminders, be sure to specify a database user, generally using DBO (Figure 6). We can add comments to tables and columns.
Figure 6
7 Generate a library script, menu Databases-> Generate Database. In the dialog shown in Figure 7, pay attention to select the Comment option. Click OK to get the final settlement script. Open a look, we saw PowerDesinger to generate a corresponding code according to the rules that we told it ...
Execute sp_addextendedproperty n'ms_description ', N' Employees', N'User ', N'dbo', N'table ', N'Employee', Null, NULL
Go
Execute sp_addextendedproperty n'ms_description ', n' primary key ID, automatically add ', n'user', n'dbo ', n'tuffle', n'employee ', n'column', n'employeeid '
Go
.
8 We can perform this angle in SQL Server 2000 and then see these comments by Enterprise Manager.
Figure 7
Figure 8
summary
With this practice, we can understand how PowerDesigner handles SQL syntax differences in different databases, which is basically the same as the ideas of the parameter file in our development app. At the same time, we should also recognize that PowerDesinger is more and more complicated, only familiar with the functionality of this tool, can really improve production efficiency.
Author Blog:
http://blog.9cbs.neet/enhydraboy/
