Every object in PowerDesigner actually has two labels, which we refer to as the Name and the Code. The Name is the 'business' or 'human' name for an object, and the Code is a technical name for the object. The Naming Conventions allow you to automatically convert the 'business' names into the 'technical' codes, like this set of LDM attributes:
As well as changing the case, and replacing spaces with underscores, we can replace words and phrases with their abbreviations, using a CSV file or the PowerDesigner glossary as the source. Here are the same attributes, after changing the standard for codes to UpperCamelCase, and applying abbreviations from a CSV file.
That's fine if you're forward-engineering, creating technical artefacts from your models, but what if you're reverse-engineering, and those technical names (the PowerDesigner codes) are your starting point?
When you reverse-engineer a database in PowerDesigner, the only names available are the technical names, so the PowerDesigner Names and Codes are the same. Synchronisation is automatically turned off, so you can manually edit names without accidentally changing the codes (the technical names). For example, here's part of the model created by reverse-engineering the Demo database that gets installed with SAP SQL Anywhere 17:
The table and column names are all in UpperCamelCase. If I want to create a Logical data Model, I will need to convert them into a more human-friendly format. Out of the box, I can easily convert some standards - for example, it's trivial to convert "SALES_REPRESENTATIVE" to "Sales Representative". However, converting Camel Case names is not trivial. After reading a Sandhill blog entry about how to do this in ERwin yesterday, I decided to finally figure out how to do it in PowerDesigner - I've thought about it on and off, but never took the time to work it out. I didn't want to do it outside PowerDesigner, as I wanted to make use of PowerDesigner's standard naming conventions as much as possible, especially the ability to replace abbreviations with the real thing. For example, here's part of a CSV file I was using today, which I'll use in the next example:
I also wanted to come up with a mechanism that was as easy to use as possible - the result is a simple model extension, that adds menu options to the model, to tables, and to columns, so you can reset the names of:
For example, I have a column called "BBCOrderLn", and want to set the Name to "BBC Order Line" - "Ln" is the abbreviation for "Line" in my CSV file. With my model extension, I just right-click the column on the diagram or in the browser, and select the option "set Proper Name",
and the result is
Here's a made-up example for a whole table:
How does it work? It's based on a single model extension that contains a number of GTL templates - GTL is PowerDesigner's Generation Template Language, which is great at turning metadata into text. One of the templates contains embedded VBScript, which is the part that does the real work. I shan't bore you with the whole model extension here, I'll just show you the part that converts a single column.
It includes a menu, which allows you to run the Method called "set Proper Name", which contains a little bit of VBScript.
The key part here is "evaluatetemplatefor", which runs a shared GTL template called "newName". Because it's shared, I only have to define it once, and then I can use it wherever I like.
This is a very simple template, containing a single line of GTL, which calls the standard template (.convert_code) that PowerDesigner uses to convert codes into names, changing the case and reversing abbreviations. Instead of supplying the object code to be converted, it passes the result of the other template, "ProperCase"
"ProperCase" does the real work here, with some embedded VBscript. If you find any problems with this code, please let me know.
Finally, you need to create a simple Global Script, to report name changes to the Output window:
As well as changing the case, and replacing spaces with underscores, we can replace words and phrases with their abbreviations, using a CSV file or the PowerDesigner glossary as the source. Here are the same attributes, after changing the standard for codes to UpperCamelCase, and applying abbreviations from a CSV file.That's fine if you're forward-engineering, creating technical artefacts from your models, but what if you're reverse-engineering, and those technical names (the PowerDesigner codes) are your starting point?
When you reverse-engineer a database in PowerDesigner, the only names available are the technical names, so the PowerDesigner Names and Codes are the same. Synchronisation is automatically turned off, so you can manually edit names without accidentally changing the codes (the technical names). For example, here's part of the model created by reverse-engineering the Demo database that gets installed with SAP SQL Anywhere 17:
The table and column names are all in UpperCamelCase. If I want to create a Logical data Model, I will need to convert them into a more human-friendly format. Out of the box, I can easily convert some standards - for example, it's trivial to convert "SALES_REPRESENTATIVE" to "Sales Representative". However, converting Camel Case names is not trivial. After reading a Sandhill blog entry about how to do this in ERwin yesterday, I decided to finally figure out how to do it in PowerDesigner - I've thought about it on and off, but never took the time to work it out. I didn't want to do it outside PowerDesigner, as I wanted to make use of PowerDesigner's standard naming conventions as much as possible, especially the ability to replace abbreviations with the real thing. For example, here's part of a CSV file I was using today, which I'll use in the next example:
I also wanted to come up with a mechanism that was as easy to use as possible - the result is a simple model extension, that adds menu options to the model, to tables, and to columns, so you can reset the names of:- all tables and columns in the models
- a selected table
- all the columns in a selected table
- a selected column
For example, I have a column called "BBCOrderLn", and want to set the Name to "BBC Order Line" - "Ln" is the abbreviation for "Line" in my CSV file. With my model extension, I just right-click the column on the diagram or in the browser, and select the option "set Proper Name",
and the result is
Here's a made-up example for a whole table:
How does it work? It's based on a single model extension that contains a number of GTL templates - GTL is PowerDesigner's Generation Template Language, which is great at turning metadata into text. One of the templates contains embedded VBScript, which is the part that does the real work. I shan't bore you with the whole model extension here, I'll just show you the part that converts a single column.
It includes a menu, which allows you to run the Method called "set Proper Name", which contains a little bit of VBScript.
Sub %Method%(obj)
' Implement your method on <obj> here
Dim candidate
candidate = obj.evaluatetemplatefor("newName","PDM-ProperCase")
if not candidate = obj.Name then
reportChange "Column", obj.Table.Name & "." & obj.name, obj.Table.Name & "." & candidate
obj.Name = candidate ' need to change it
end if
End SubThe key part here is "evaluatetemplatefor", which runs a shared GTL template called "newName". Because it's shared, I only have to define it once, and then I can use it wherever I like.
This is a very simple template, containing a single line of GTL, which calls the standard template (.convert_code) that PowerDesigner uses to convert codes into names, changing the case and reversing abbreviations. Instead of supplying the object code to be converted, it passes the result of the other template, "ProperCase".convert_code(%ProperCase%," ")"ProperCase" does the real work here, with some embedded VBscript. If you find any problems with this code, please let me know.
.vbscript(%1%)
Dim obj : set obj = activeselection.item(0)
Dim myString : myString = obj.Code
Dim ProperCase
' converts a string into Proper Case, one character at a time
' the first character is always upper case
' if an existing character is already upper case, it is not converted
' if an existing character is a space, it is output as is
' ignore underscores - convert_code will deal with them
' acronyms are left intact
' multi-word conversions only made if they're separated by a space
Dim i
Dim prevSpaceInd ' was previous character a space?
Dim prevUpperInd ' was previous character upper case?
Dim nextChar ' the next character in the string
Dim myStringLength ' the length of myString
myStringLength = len(myString)
Select Case myStringLength
' If there are 0 or 1 characters, just return the string.
Case 0
ProperCase = myString
Case 1
ProperCase = Ucase(myString)
Case else
' Start with the first character - this will always be upper case
ProperCase = Ucase(Left(myString,1))
prevUpperInd = true ' remember this for the next character
' Add the remaining characters
Dim myChar
For i = 2 To len(myString)
myChar = Mid(myString,i,1)
If myChar = " " then
prevSpaceInd = True ' remember this for the next character
myChar = " "
ElseIf myChar = "_" then ' ignore
myChar = myChar
prevSpaceInd = True ' force script to act as if it was a space
ElseIf myChar = Ucase(myChar) then
' the current character is upper case
If prevSpaceInd then ' previous character was a space
myChar = myChar
prevSpaceInd = False
ElseIf prevUpperInd then ' previous character was also Upper Case
nextChar = Mid(myString,i+1,1)
If i = myStringLength then ' this is the last character in the string
myChar = myChar ' don't insert a space
ElseIf nextChar = Lcase(nextChar) then ' next char is lower case
' If the next character is not upper case,
' assume the current letter is beginning of new word
myChar = " " & myChar ' make this 1st letter of new word
Else
myChar = myChar ' continue an acronym
End If
Else
myChar = " " & myChar
End If
prevUpperInd = true ' remember this for the next character
Else ' must be lower case or perhaps a number, leave it alone
prevUpperInd = False
myChar = myChar
End If
ProperCase = ProperCase & myChar
Next ' i
End Select
ScriptResult = ProperCase
.endvbscriptFinally, you need to create a simple Global Script, to report name changes to the Output window:
- SAP Managed Tags:
5 Comments
