Template Editing Example

This section provides an example of creating a template to demonstrate the mechanics of editing a template. This example creates a very simple version of a CREATE TABLE template and tests it against the EMPLOYEE table in the emovies.erwin sample model.

Note: This example assumes the target server of your model is SQL Server 2000. However, the process is the same for all supported target servers.

The template produced here will not cover the full syntax of a CREATE TABLE statement. Once you understand the principles here, you can examine the full templates shipped with CA ERwin DM to guide you in customizing your SQL.

Follow these steps to create the example template:

Basic Template - Version 1

The first pass demonstrates the steps to produce the following SQL:

create table EMPLOYEE
(
  employee_first_name varchar(20),
  employee_address varchar(20),
  employee_phone integer,
  employee_address_2 varchar(20),
  employee_number varchar(20),
  soc_sec_number integer
  hire_date datetime,
  salary integer,
  email varchar(20),
  store_number integer,
  supervisor varchar(20)

)

Getting Started

Template files are quite extensive in order to cover all of the SQL required for CA ERwin DM's processes. The easiest way to start is to make a copy of an existing template file and edit it to produce the custom SQL you want:

Go to the C:\ProgramData\CA\ERwin Data Modeler\9.0\Templates directory, make a copy of SqlServer.fet, and name it SqlServer2.fet. This prevents unwanted changes to the real template file.
Start CA ERwin DM and load emovies.erwin.
Click Forward Engineering Templates on the Tools menu to invoke the Template Editor.
Click the Open tool and load SqlServer2.fet.

Make sure the EMPLOYEE [Entity] table is selected in the Sample Context box. If you cannot find it, turn on Show All.

Entry Point Templates

The Forward Engineering component of CA ERwin DM looks for templates with certain names to use as the starting point for a particular SQL command. For a CREATE statement, the name of the entry point template is Create XXX where XXX is the class name for the object type. The entry points for other types of statements follow this pattern; DROP statements have entry points of Drop XXX, ALTER statements have entry points of Alter XXX, and so on.

Note: Consult the ERwin Metamodel Reference to locate the class names for various object and property types.

The class name for a table is Entity, so the Create Entity template is edited:

Select the Create Entity template (it is probably already selected) and press F2. Rename that template to Create Entity old.
Select the New Template tool and name the new template Create Entity.

At this point, the Template Editor attempts to expand the new template and displays a message that an undetermined parsing error occurred - this is expected.

The Template

This version of the template uses three macros: Property, ForEachOwnee, and ListSeparator.

Note: Consult the Template Language and Macro Reference document if you are uncertain of TLX syntax, or to see full descriptions of the macros used here.

Type the following code into the Template Source field of the editor:

"create table " Property("Physical_Name")
"\n("
ForEachOwnee("Attribute")
{
  ListSeparator(",")
  "\n\t" Property("Physical_Name") " " Property("Physical_Data_Type")
}
"\n)"

After a moment, the following text appears in the Expanded Text field:

create table EMPLOYEE
(
  employee_first_name varchar(20),
  employee_address varchar(20),
  employee_phone integer,
  employee_address_2 varchar(20),
  employee_number varchar(20),
  soc_sec_number integer
  hire_date datetime,
  salary integer,
  email varchar(20),
  store_number integer,
  supervisor varchar(20)

)

Splitting SQL Scripts - Version 2

The Forward Engineering component needs to be able to split up the various SQL statements that are generated to the script. Parsing the script during generation is too slow, so tokens are placed into the script to indicate the split points. These tokens are inserted by a macro called FE::EndOfStatement.

Tokens representing instructions to the Forward Engineering component are all delimited by double @ symbols (@@).

Put your cursor in the Template Source field after the last line.
Double-click the FE::EndOfStatement macro in the Macros tree.

After a moment, the following text appears in the Expanded Text field:

create table EMPLOYEE
(
  employee_first_name varchar(20),
  employee_address varchar(20),
  employee_phone integer,
  employee_address_2 varchar(20),
  employee_number varchar(20),
  soc_sec_number integer
  hire_date datetime,
  salary integer,
  email varchar(20),
  store_number integer,
  supervisor varchar(20)
)
go

@@*EOS*@@

Subsidiary Templates - Version 3

Describes subsidiary templates and the use of the Execute macro.

Execute Macro

One template can delegate some of the processing to another template using the Execute macro. Another template is created called Emit FK and it is delegated to produce the foreign key constraints for the table:

Select the New Template tool and name the new template Emit FK.
Add the following as temporary code for the new template. This code will be replaced later in the example.
```
"Got here"
```
Double-click the Create Entity template again to go back to editing it and add the following text at the end. The use of the Equal macro causes nothing to emit if the current Key_Group is not a foreign key.
```
ForEachOwnee("Key_Group")
{
  Equal( Left( Property( "Key_Group_Type" ), 2 ), "IF" )
  Execute("Emit FK")
  FE::EndOfStatement
}
```

After a moment, the following text appears in the Expanded Text field:

create table EMPLOYEE
(
  employee_first_name varchar(20),
  employee_address varchar(20),
  employee_phone integer,
  employee_address_2 varchar(20),
  employee_number varchar(20),
  soc_sec_number integer
  hire_date datetime,
  salary integer,
  email varchar(20),
  store_number integer,
  supervisor varchar(20)
)
go
@@*EOS*@@

Got here
go
@@*EOS*@@

Got here
go
@@*EOS*@@

Controlling the Context Stack

The Got here statement can now be replaced in the Emit FK template with code to emit an ALTER statement that adds the foreign key. Since such a statement requires information from the Entity object, the Attribute objects owned by it, the Relationship object, and the Key_Group_Member objects, the template code will have to control the context stack to make sure that properties are read from the correct object.

Note: This is not the most efficient way to produce the intended output, but it accomplishes the task without complicating the example with a lot of new macros.

Double-click on the Emit FK template to edit it.

Replace the existing text with the following code:

"alter table " PushOwner Property("Physical_Name") Pop
"\nadd foreign key ("
ForEachOwnee("Key_Group_Member")
{
  ListSeparator(",") Property("Physical_Name")
}
") references "
PushReference("Relationship_Ref") 
PushReference("Parent_Entity_Ref")
Property("Physical_Name")
Pop
Pop
" ("
ForEachMigratingColumn
{
  ListSeparator(",") Property("Physical_Name")
}
")"
PushReference("Relationship_Ref")
"\n\ton delete " 
UpperCase( Property("Relationship_Parent_Delete_Rule") ) 
Pop

Ignore the parsing error that appears in the Expanded Text field (because the template was not entered from the correct starting point) and double-click the Create Entity template to get the entry point template to evaluate.

After a moment, the following text appears in the Expanded Text field:

create table EMPLOYEE
(
  employee_first_name varchar(20),
  employee_address varchar(20),
  employee_phone integer,
  employee_address_2 varchar(20),
  employee_number varchar(20),
  soc_sec_number integer
  hire_date datetime,
  salary integer,
  email varchar(20),
  store_number integer,
  supervisor varchar(20)
)
go
@@*EOS*@@

alter table EMPLOYEE
add foreign key (store_number) references STORE (store_number)
on delete NO ACTION
go
@@*EOS*@@

alter table EMPLOYEE
add foreign key (supervisor) references EMPLOYEE (employee_number)
on delete NO ACTION
go
@@*EOS*@@

Sorting the Output - Version 4

One of the foreign keys in the EMPLOYEE table is self-referential. However, the other is not; it references the STORE table. If the order of processing the Entity objects results in the STORE table emitting after the EMPLOYEE table, the ALTER statement creating the foreign key will fail.

To avoid this, tokens can be emitted that instruct the Forward Engineering component to sort types of statements into groups. These groups are called buckets. Any statement placed in Bucket #1 is emitted before any statement placed in Bucket #2, which is before any statement in Bucket #3, and so on. There can be an arbitrary number of buckets. The macro FE::Bucket inserts the bucket tokens.

Insert the following code as the first line of the Create Entity template:
```
FE::Bucket("10")
```
Insert the following code right after the first occurrence of the FE::EndOfStatement macro:
```
FE::Bucket("20")
```

After a moment, the following text appears in the Expanded Text field:

@@*B=10*@@
create table EMPLOYEE
(
  employee_first_name varchar(20),
  employee_address varchar(20),
  employee_phone integer,
  employee_address_2 varchar(20),
  employee_number varchar(20),
  soc_sec_number integer
  hire_date datetime,
  salary integer,
  email varchar(20),
  store_number integer,
  supervisor varchar(20)
)
go
@@*EOS*@@

@@*B=20*@@
alter table EMPLOYEE
add foreign key (store_number) references STORE (store_number)
on delete NO ACTION
go
@@*EOS*@@

When the templates are executed against the entire model, all of the CREATE TABLE statements (Bucket #10) will emit before any of the ALTER statements (Bucket #20).