Script generation

Chapter 3 DBMS Reference Guide

Script generation

Script generation statements are available in the Script category, under the different object categories.

For example, in Sybase ASA 8, the Create statement in the Table category is the following:

create table [%QUALIFIER%]%TABLE% 
(
    %TABLDEFN%
)
[%OPTIONS%]

This statement contains the parameters for creating the table together with its owner and physical options.

Extension mechanism

You can extend script generation statements to complement generation using them extension statements. The extension mechanism allows you to generate statements immediately before or after Create, Drop, and Modify statements, and to retrieve these statements during reverse engineering.

For more information on reverse engineering additional statements see section "Script reverse engineering".

Generation Template Language

Extension statements are defined using the PowerDesigner Generation Template Language (GTL) mechanism.

An extension statement can contain:

Reference to other statements that will be evaluated during generation. These entries are text items that must be defined in the object category of the extension statements
Variables used to evaluate object properties and extended attributes. Variables are enclosed between % characters
Macros such as ".if", provide generic programming structures for testing variables. Note: we recommend that you avoid using GTL macros in generation scripts, as they cannot be reconstituted when reverse engineering by script. Generating and reverse engineering via an ODBC connection are not subject to this limitation.

For more information on the PowerDesigner Generation Template Language (GTL), see chapter "Generation Reference Guide".

During generation, the statements and variables are evaluated and the result is added to the global script.

Example 1

The extension statement AfterCreate is defined in the table category to complement the table Create statement by adding partitions to the table if the value of the partition extended attribute requires it.

AfterCreate is defined in GTL syntax as follows:

.if (%ExtTablePartition% > 1)
%CreatePartition%
go
.endif

The .if macro is used to evaluate variable %ExtTablePartition%. This variable is an extended attribute that contains the number of table partitions. If the value of %ExtTablePartition% is higher than 1, then %CreatePartition% will be generated followed by "go". %CreatePartition% is a statement defined in the table category as follows:

alter table [%QUALIFIER%]%TABLE%
   partition %ExtTablePartition%

%CreatePartition% generates the statement for creating the number of table partitions specified in %ExtTablePartition%.

Example 2

You create in Sybase ASE an extended statement to automatically create the login of a user before the Create user statement is executed. The BeforeCreate statement is the following:

sp_addlogin %Name% %Password%
go

The automatically generated login will have the same name as the user and its password. You can preview the statement in the user property sheet, the BeforeCreate statement appears before the user creation statement:

Modify statements

You can also add BeforeModify and AfterModify statements to standard modify statements.

Modify statements are executed to synchronize the database with the schema created in the PDM. By default, the modify database feature does not take into account extended attributes when it compares changes performed in the model from the last generation. You can bypass this rule by adding extended attributes in the ModifiableAttributes list item. Extended attributes defined in this list will be taken into account in the merge dialog box during database synchronization.

To detect that an extended attribute value has been modified you can use the following variables:

%OLDOBJECT% to access an old value of the object
%NEWOBJECT% to access a new value of the object

For example, you can verify that the value of the extended attribute ExtTablePartition has been modified using the following GTL syntax:

.if (%OLDOBJECT.ExtTablePartition% != %NEWOBJECT.ExtTablePartition%)

If the extended attribute value was changed, an extended statement will be generated to update the database. In the Sybase ASE syntax, the ModifyPartition extended statement is the following because in case of partition change you need to delete the previous partition and then recreate it:

.if (%OLDOBJECT.ExtTablePartition% != %NEWOBJECT.ExtTablePartition%)
   .if (%NEWOBJECT.ExtTablePartition% > 1)
      .if (%OLDOBJECT.ExtTablePartition% > 1)
%DropPartition%
      .endif
%CreatePartition%
   .else
%DropPartition%
   .endif
.endif

For more information on the PowerDesigner Generation Template Language (GTL), see chapter "Generation Reference Guide".