An Introduction to CA ERwin Data Modeler API: How to Sync your Foreign Key Indexes and Constraint Names

Introduction

A common request made by CA ERwin Data Modeler users is for a way to easily synchronize their foreign key indexes with their constraints. The purpose of this document is to explain how to use the CA ERwin Data Modeler API to do that as well as to serve as an introduction to using the API. This document will use a sample that can also be used to sync Foreign Key (FK) indexes and constraint names.

Preparing to use the API

In our example, we will be using the Visual Basic editor that is built into the Microsoft Excel software, since most users have this software and will not require any new software installation.

Invoking the Visual Basic Editor in Microsoft Excel

First, create a new spreadsheet in Excel. Next, launch the Macro Editor from the Tools | Macros | Macros... menu option (the shortcut key is Alt + F8).

Launch the Macro Editor using the Tools | Macros | Macros... menu option.

Type "RelationshipSync" in the Macro name field and press the Create

NOTE: if you do not see the Immediate or Project windows, you can turn them on using the View menu items

The next thing that must be done is to include the API library so our code can reference it. Go to the menu Tools | References

That launches the References editor

The listed components are included, by default. Keep all of these selected. Now locate the AllFusion ERwin API in the list and select it. Then press OK:

Back in the VBA editor , we see that the function has been defined for us:

     Sub RelationshipSync() 

     End Sub 

Each function must follow this declaration and End syntax. The code will be entered between these two statements.

Initiating a new Session of the ERwin API

Users can declare variables with the syntax of,

     Dim <objectname> As <objecttype> 

You can declare any variable at any time before it is referenced by the code. Variables declared within a function exist only within the scope of the function. Those declared outside the function are global and can be referenced by all functions.

The first part of the code is as follows:

     Dim Filename As String 
          Filename = Application.GetOpenFilename("Erwin 7.1 models_ 
     (*.erwin),*.erwin", ,"Select Model") 
          Filename = "erwin://" & Filename 
     Set oPU = Nothing 

In the first line, we have defined a variable Filename as a string. Next, we assigned a value to this string using the = operator. The value that we are assigning is more complicated.

The Application object is a windows defined object. Using the GetOpenFilename function allows us to pick a file from your file directory using the File Open editor that is common with windows applications. GetOpenFilename accepts an argument of "Erwin 7.1 models (*.erwin),*.erwin" which is what will display in the 'Files of type' field in the editor.

The next argument is left blank with the ', ,' characters. We finish the line with a character of _. The '_' character simply means that the code continues in the next line. You can use this character to wrap code onto another line or simply write everything on one line. The final argument is "Select Mode". That simply means that the editor will allow selection by the user.

In the third line we concatenate on the selected filename with the string "erwin://" using the & character. That is required by the API to indicate that the model is an ERwin file rather than a Model Manager model.

The next piece of code is:

     Dim oPU As SCAPI.PersistenceUnit 
     Set oPU = Nothing 

The first statement declared the oPU variable which is a Persistence Unit from the ERwin API (SCAPI). We then set that object to Nothing. That is just to be sure that the Persistence Unit is empty before we begin.

The next piece of code is as follows:

     Dim oERwin As New SCAPI.Application 
     Dim oSess As SCAPI.Session 

     Set oPU = oERwin.PersistenceUnits.Add(Filename, "RDO=No") 
     Set oSess = oERwin.Sessions.Add 
     oSess.Open oPU 

The above code defines more SCAPI objects. One is of type Application and another is of type Session. We then assign oPU (the Persistence Unit) a value using the method oERwin add Persistence Unit and by specifying Filename (the file we previously selected) and the read only setting as No ("RDO = No").

NOTE: Be sure that your file is not set as Read Only and is not open in any other applications or this code will not execute.

Next, we use the Sessions.Add method and assign the added session it to the oSess object. Then, we open the session, oSess, using the Persistence Unit object oPU.

NOTE: The code, up to this point, is all overhead and is always required for any ERwin API project. That can be considered the template on which all future projects are defined.

Retrieving all SCAPI Objects of a Specific Type

Since we want to sync our index names with our constraint names, we first need to retrieve all of our Key Groups. Key Groups are the logical objects that define our Indexes. To retrieve all of the Key Groups, we do the following:

Dim oKeyGroups As SCAPI.ModelObjects 
Dim oKeyGroup As SCAPI.ModelObject 
Set oKeyGroups = oSess.ModelObjects.Collect(oSess.ModelObjects.Root, "Key Group") 

The first line defines an array of type Model Objects. Model Objects can contain multiple objects of type Model Object. The second line defines oKeyGroup as an object of type Model Object. A Model Object can hold any ERwin Model Object and all its properties. We will use this object to temporarily hold each of the Key Group objects. Then, we populate the oKeyGroups array with all of the Key Groups in the model. To do that, we use the Collect method of the Model Objects. By default that contains all objects in the session. However, we are filtering on objects of type "Key Group". The other argument, oSess.ModelObjects.Root, indicates the level we begin to look for the object (at the root).

Syncing the Values

Finally, we get to the heart of the project:

For Each oKeyGroup In oKeyGroups 
     If (oKeyGroup.Properties.HasProperty("Relationship Ref")) Then 
          nTId = oSess.BeginTransaction 
          Dim strKeyID As String 
          strKeyId = oKeyGroup.Properties("Relationship Ref").Value 
          Dim oRel As SCAPI.ModelObject 
          Set oRel = oSess.ModelObjects.Item(strKeyId) 
          Dim constrNm As String 
          constrNm = oRel.Properties("Physical Name").FormatAsString 
          oKeyGroup.Properties("Physical Name").Value = constrNm 
          oSess.CommitTransaction (nTId) 
     End If 
 Next 

The first line defines our loop. We will iterate through each of the Key Groups. Next, we want to sync these names only on Foreign Key indexes. To check for that, we use the If statement and we check that the particular key group contains the property "Relationship Ref" which is a reference to a relationship (a constraint). If the condition is met, we do what is included before the End If statement.

To make changes to a Session, we use the BeginTransaction method. The nTId name for the transaction is completely arbitrary. Next, we assign a string, strKeyID, the value of the "Relationship Ref". That returns the unique id of that relationship object. We retrieve this specific object by using the ModelObjects.Item(<ID>) method of the Session object. Next, we retrieve the "Physical Name" property of that relationship and assign it to the string constrNm. The last step is to assign the Physical Name property the same value as the Constraint Name.

(oKeyGroup.Properties("Physical Name").Value = constrNm)

Then, we commit the transaction to the session. That is required for any change to the model objects. Finish the loop by closing the If condition with the End If statement and closing the entire loop with the Next.

After the session is modified, we save this back to the physical model with the following statement:

     oPU.Save 

Tidying Up

We finish the entire project with the following:

     oSess.Close 
     oERwin.Sessions.Clear 
     Set oPU = Nothing 
     Set oERwin = Nothing 
     End Sub 

That closes the Session and clears the variables that we created. The End Sub statement is required for all functions.