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:
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:
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.