XEM

Advanced Display Options and an End to Custom Shapes

Okay, so it’s been a LONG time since I’ve posted, but two small kids and a job (okay, mostly the kids) will do that.  Anyway …

We’re preparing to upgrade to PowerDesigner 15.3 (sadly from 15.0) and one of the new features I’m very excited to get my hands on is the new advanced display controls available.  Previously, you were fairly limited to the information you could display on a diagram.  Each symbol gave you the ability to select among the most common attributes.  You could add the Stereotype which gave you access to a bit of free-form text you could add to the display, but each object only had one stereotype and it lead to a lot of abuse of the stereotype concept.  If you wanted to show additional information, you were often stuck with color-coding, changing the borders or adding other bits of graphical “flair” to the diagram.  Not the most elegant solution and it requires the addition of a legend to all you diagrams in order to be meaningful.  Forget the legend, and you’ve lost the meaning.

Well, 15.3 fixes that for us.  Let’s look at a quick sample.

Let’s suppose we’d like to add some additional information to the model.  I’ll add two attributes, one is the standard attribute “Annotated” which I’ll add so that our modelers know when someone else on the team has left them some useful information.  The other is subject area, which we currently indicate using color coding (still handy for quickly visualizing a large model).  Let’s walk through how it’s done.  For this sample, I’ve already created a simple model with an extended attribute SubjectArea on the table metaclass.  If you want to play along at home, I’ll leave it to you to create that.  Using a criteria and custom format, I have color coded them according to standards.  If I want to indicate whether there’s an annotation, I could change the width or style of the border or add a graphical tag somewhere on the object (and hope it doesn’t cover something else).

Here’s the “before” picture with the red and blue colors indicating the subject areas.

Now, let’s look at how we can do it better.

First, right click on the desktop and choose “Display Preferences…”

Now, select table on the left of the dialog and then “Advanced…” on the right.

From this dialog, you can add ANY attribute available in the model, including extended attributes you’ve added and any available in the model from another source such as the XDB file.  To add SubjectArea, choose “Add Attribute” from the toolbar at the top of the dialog.  Scroll down until you see “Annotated”.  Since annotated is a boolean, you’ll have the ability to set a few options.  Label, will actually be the label you’ll see next to the attribute on the Display Preferences dialog, NOT what you’ll see next to the value on the diagram.  If you leave it blank, you’ll get the attribute name.  For a boolean you have to provide a value to display when true, and a value for false.  In my example, I’ve left the value for False blank, which means you won’t see anything on the diagram if the Annotated value is false.

Now, let’s add SubjectArea.  Go back to “Add Attribute” and choose the “Extended Attributes” tab this time.  Select SubjectArea and click “OK”.  Subject area is a text string, so you’ll have a slightly different set of options.  Since I created a label on the extended attribute, I can leave the label property blank here, or I can change it.  Prefix and Suffix are the values that will appear before and after the actual subject area (think “<<” and “>>” for a Stereotype).

In Prefix, type a nice label such as “Subject Area:  “.  Don’t forget the colon and a space or two, it won’t add them in for you.  Click “OK”.  Now you’ll be back at the display preferences dialog and you’ll see two new options you can choose.

Make sure they’re both selected and click “OK” again.  Apply your new format to all symbols and here’s the new diagram (I’ve removed the color coding, although it might be useful to keep it for a larger diagram).

Now, on any diagram, I can easily see if there’s an annotation that I might want to read and the subject area.  If I print a subset of the diagram or forget to include the legend, I can still interpret it.  Most importantly, I can present a LOT more information on the diagram in a format that’s much more easily understood.  If you want to get really information dense, you can create extended attributes that compute a result and add that.  Here’s an example.  Suppose you want all your tables to have a subject area and definition set.  Create a custom computed attribute.

Function %Get%(obj)  
  %Get% = true      
  if obj.Comment = "" then
    %Get% = false
  elseif obj.GetExtendedAttribute("Local Extensions.SubjectArea") = "" then
    %Get% = false  
  end if   
End Function

Now add that extended attribute to the table.  In this case, we’ll add it to the top of the table and set the value for False to “Model Incomplete!”.  Here’s what our sample model looks like now.  While this won’t replace custom checks, it’s a way to provide  immediate visual feedback to all our modelers as they’re completing the models.

So, if you haven’t upgraded to 15.3 yet, here’s another great reason to keep current.

Creating Extended Dependencies in VBScript

We use scripts to automate repetitive tasks, and that usually means things like creating tables, columns, views, etc.  So, I’ve generally gotten pretty fast at putting together those types of scripts.  For my last script, I wanted to create a view on top of a set of tables, and create an extended dependency to link them together.  By putting a stereotype on the extended dependency, I would always be able to identify the particular view created by these scripts, no matter what changes were made to them.

Typically we create a new object by doing something like:

dim extDep
set extDep = obj.ExtendedDependencies.CreateNew()

No luck.  I tried a few variations, and then hit the groups.  I found several examples for creating extended dependencies:

ExDepColl=ActiveModel.GetChildCollPdCommon.cls_extendedDependency)
Set NewExtendedDependency =ExDepColl.CreateNew(0)
NewExtendedDependency.InfluentObject = BaseTable
NewExtendedDependency.DependentObject = HistoryTable
ActiveDiagram.AttachLinkObject(NewExtendedDependency)

and

Set EDep = prc.ExtendedDependencies.CreateNew() 
Set EDep.InfluentObject = IObj
EDep.Stereotype = ed.Stereotype

Unfortunately, I could not get either to work, and while I’m certainly willing to admit the problem may lie between the keyboard and chair, what I eventually found that did work was:

Set ExtDep = ActiveModel.CreateObject(cls_ExtendedDependency)
obj.ExtendedDependencies.Add(ExtDep)

So, the key is to first create an object of type cls_ExtendedDependency THEN use the Add method to put it in the ExtendedDependencies collection.

Adding Seed Data to the After Tab

Another interesting post on the PowerDesigner group today.  Mark wanted to add seed data to the after script on a table, which is fairly common (we do it all the time); but with a brilliant twist.  The goal is to store the seed data script in an external file and have it end up in the DDL when we generate the database.

I thought this would be pretty easy by tweaking pieces of code I already, but there were a few gotchas.

1. You can’t directly read the contents of an external file within the PowerDesigner VBScript methods (as far as I know).  It may be possible to write your own routine to process the file, but I found an easier (at least for me workaround).

2. My workaround for getting a the file contents ended up converting the file contents to hex (sort of).  In a rather bizarre conversion, what I ended up with was a big string showing me the hex values representing the original file, but with a carriage return/line feed inserted periodically in a weird word wrap.  Since VBScript doesn’t have a convenient way of converting hex back to ASCII, a Google search got me most of the answer, but I ended up having to strip out the carriage returns first.

Anyway, here’s the solution… suggestions for improvements are always welcome (I plan on adding a toggle so I can include seed data when I’m generating an empty database and omit it when I’m altering an existing database which already has the seed data present).  As always, I’m doing this with an extended model file rather than stand alone scripts so I can get a little more control.  If you need information on how to create and work with extended model definition files, see Sybase’s documentation here.

Step 1: Create a Table Method

Here’s my method:

Sub %Method%(obj)

 dim exDep, seedFile, fileLoc

 ' loop through all the extended dependencies on the table, looking for one with the stereotype of SEEDDATA
 for each exDep in obj.ExtendedInfluences
  if exDep.Stereotype = "SEEDDATA" then

   ' if we've found an extended dependency with the right type, get the target file
   set seedFile = exDep.DependentObject
     ' write out some handy diagnostic information
     output "Loading seed data for " & obj.name & " from " & seedFile.LocationOrName

     ' convert to an embedded file - can't find another way to get at the contents
     ' this does a crazy hex conversion
     fileLoc = seedFile.LocationOrName
     seedFile.LoadFileAsEmbedded(fileLoc)

     'The file contents get converted to hex (not really sure why), when they're loaded
     'there may be a better way to do this but I haven't found it yet.
     with obj
       .EndScript = seedFile.Content
       'The hex contents get some lovely CrLf characters in them which cause the hex to ascii
       'conversion to fail - strip them out.
       .EndScript = replace(.EndScript, vbCrLf, "")
       ' now run them through this handy hex2ascii function (thanks google)
       .EndScript = hex2ascii(.EndScript)
     end with

     ' now convert it back to an external reference
     seedFile.ChangeToExternalFile(fileLoc)

    end if
 next
End Sub

Public Function hex2ascii(hextext) 

dim y, num, char, value

For y = 1 To Len(hextext) step 2
 num = Mid(hextext, y, 2)
 char = chr(cint("&h" & num))
 Value = Value & char
Next

hex2ascii = Value
End Function

Step 2: Make the Method Accessible

Okay, this isn’t technically necessary, but it sure makes testing easier. So, I created a menu on the table meta-class and put the method above on it. Now I can add seed data to an individual table with a right mouse click.

Step 3: Create a Model Method

Again, I could have skipped the table, but we get a lot of versatility doing it this way. Here’s the method code.

Sub %Method%(obj) Dim myObj
 Dim mySel

 Set mySel = obj.CreateSelection
 mySel.AddObjects obj, cls_table, false, true

 For each myObj in mySel.Objects
   With myObj
     myObj.ExecuteCustomMethod("SeedData.LoadSeedData")
   end with
 Next
End Sub

If you’re not familiar with selections and ExecuteCustomMethod, then please see my earlier posts.  You won’t regret it.

Step 4:  Make this Method Accessible

Again, not strictly necessary, but it’s only a few mouse clicks for a little extra versatility.

Step 5: Add the Model Trigger

The BeforeDatabaseGenerate trigger fires before you generate your DDL and is the piece that makes it all work automatically.  Here’s the code:

Function %BeforeDatabaseGenerate%(model, fct)

   ActiveModel.ExecuteCustomMethod("SeedData.Load Seed Data")
   %BeforeDatabaseGenerate% = True

End Function

Done.

Sample

Now, if you’ve done all the work above, here’s a completed PDM file that has an extended model definition with all the code above, pre-made.  I know I could have put this at the top, but really, would you have learned as much?

Here’s the Sample File

Validation Events

I answered a great question on the PowerDesigner usenet group today.  The question was how to automatically add an “id_” prefix to a column name and code when the Primary Key attribute was set.  In the past, I would’ve had to do this with a script, and probably a custom model check, but with PD15 we have events, which allow us to perform scripts automatically when certain actions are taken.

I thought this would be a good topic to elaborate on since a) I can include pictures and bit more formatting control than I can on the usenet group and b) I’m actually home, the wife is out, and the kids are asleep.  Anyway, here’s the solution.

1.  Open or add an extended model definition file.  For information on how to add an XEM file see the Sybase documentation.

2.  Add the table metaclass to the file.  Even though we actually want to validate a column property, the column validation event seems to only fire when a column is created, so we’ll put our validation event on the table and loop through the columns when the table is changed.

3.  Add a validation event handler to the table.

4.  Put this code into the handler script

Function %Validate%(obj, ByRef message)
 ' Implement your object validation rule on <parent> here
 ' and return True in case of success, False otherwise with a message

 dim col

 for each col in obj.columns

    if col.Primary = true then
       ' check to see if the id_ is already there
       if left(col.name,3) = "id_" then
          %Validate% = True
       else
          ' if it is not there add it to the name and set the code using the current abbreviation file
          With col
             .name = "id_" & .name
             .SetNameAndCode .Name, "", True
          End With

          %Validate% = True
       end if
    else
       ' if the primary attribute is false, see if we need to remove an id_ prefix
       if left(col.name,3) = "id_" then
          with col
             .name = right(.name, len(.name)-3)
             .SetNameAndCode .Name, "", True
          end with
          %Validate% = True
       else
          %Validate% = True
       end if
    end if

 next

 %Validate% = True

End Function

All done.  Note that this could be combined with some other techniques I’ve posted before.  For example, much of this code could be placed in a method on the column itself, then called in both a custom model check and a model level script using execute custom method.  However, the key advantage of validations is that, as long as people have the correct XEM attached (it can also be done in the XDB file), you can make sure that validations take place as an object is edited, which saves you the trouble of constantly reminding people to execute all the custom model checks BEFORE they generate DDL.

Best of Luck.

Recursion and Extended Attributes

Another quick one today.  I’ve got some large models which are divided into packages.  I’m automating some tasks with scripts which I can configure using extended attributes (see earlier posts).

Let’s say I have a parameter for a script called “Audit” which can be true or false.  My model is divided into 3 packages, each containing a set of tables.  I want to set the default for the model to be True and have the script behave appropriately if I don’t do anything else.  I’d also like to be able to override it for a package and have the script work appropriately.  And since packages can be nested (although I haven’t seen it done much), the script needs to handle that as well.  Easy enough with a little recursion.  Create the same named extended attribute at each level (Model and Package) and use a global script to resolve it.

Here’s the script:

function getExtendedAttr(startObj, attrName)
  dim par
  dim attrVal

  set par = startObj.Parent

  if par is nothing then
     output "Error in getExtendedAttr"
  else
     attrVal = par.getExtendedAttributeText(attrName)

     if attrVal = "" then
        if par = activeModel then
           getExtendedAttr = ""
        else
           getExtendedAttr = getExtendedAttr(par, attrName)
        end if

     else
        getExtendedAttr = attrVal
     end if
  end if

end

Now, instead of using

obj.GetExtendedAttribute("Audit")

I use

getExtendedAttr(obj, "Audit")

Done.