SubObject Formatting using VBScript

A few weeks ago, there was a discussion board post on formatting sub-objects.  I’d tried to accomplish this a few times in the past, and always given up, but I was curious if either things had gotten better with 15.3 or I’d gotten better at scripting.  Turns out the answer is no and maybe.  A little trial and error and I’ve come up with a reasonable method of accomplishing my goals.  I don’t believe the solution I’ve got here is great (maybe not even good), but it’s an interesting topic and perhaps someone will refine it into something more useful.  As a learning exercise, it certainly taught me a lot.

So let’s talk about some of the things I’ve found and some of the things I tried before settling on the solution below.

First, there are no methods for formatting subjects directly (so scratch anything easy off the list).

There is a property on the symbol that controls the formatting of sub-objects (good) but unfortunately, it’s basically a big text string that you have to parse and manipulate.  My first attempt involved parsing that string and manipulating the formatting for particular objects.  That became a bit of a code nightmare and the results during early testing were a bit “erratic” if I didn’t insert my changes into the string perfectly.  Here’s a case where my vbscript skills might have let us down.

So, if there’s no easy methods for manipulating sub-object formatting, and modifying the format is difficult, what’s left?  Well, what I settled on was creating extended attributes for the formatting and then creating a routine to build a new sub-object format string from the ground up based on those properties.  My thought was that by ignoring any existing sub-object formatting, I lessen the chance that my string manipulation creates a garbage format command.  I can write a variety of different routines to set the formatting properties on objects in the model for different purposes … so one routine to make all items modified since the last checkin bold, another to highlight columns that don’t have a definition., etc.  What could possibly go wrong?

So, let’s start building…

Step 1:  Create Custom Attributes

So, it turns out he sub-objects that can be displayed include columns, indexes, keys, etc.  Since I’d rather not have to create the same sets of attributes on them all, we’re going to put them on the extensible object metaclass.  This is a great shortcut when you want to do the same operation on a lot of classes.  So, in case you haven’t done this before, let’s walk through it.

I’m assuming you know how to add an extended attribute, if you don’t you can get the official documentation here or follow Joel’s quick example here.  The only thing you’ll need to do differently is select the PdCommon tab and change the filter to “Show Abstract Modeling Metaclasses”.

Once you’ve done that, ExtensibleObject should appear in the metaclass list.  Check the box and hit ‘Okay’.

Allright, now let’s add attributes.  When you add an attribute, you’ll notice that Sybase has conveniently provided predefined types for Font, Font Size, and Font Name.  They all provide the font dialog box as a handy mechanism for selecting the attributes.  Less convenient , even though all of them show you a dialog that allows you to select the font, size, style and color, none of them return all of those values to the extended attribute.  Font will actually give you name, size and style.  Font Name returns only the name, Font Size returns the size (duh) as a number.  None of them return the color, even though it’s an option on all three dialog boxes.  Downright annoying, the syntax for storing the font for the predefined attributes doesn’t match the formatting used to set the subobject format.

So, if you create custom attributes for Font and Color – the smallest number of attributes necessary, and just concatenate them with a comma in the middle (customFont & “,” & customFontColor) you’d get something like this:

Arial Rounded MT Bold,Bold,8,255 0 0

The subobject string for specifying the same value is

Arial Rounded MT Bold,8,B,255,0,0

Notice a few dfferences.

1. The order of the values is different.  Font size and style are switched
2. The format of the style property is different.  the Font property stores “Bold” while the subobject format is “B”
3. The color property is space separated in the color attribute with spaces and with commas in the subobject format.

Probably a case of two programmers doing their tasks without recognizing there was reason to make them match (or even anything to match to).  Annoying but not the end of the world.  However, if anyone from Sybase reads this, a font property that returns the color value and a subobject format string that matches would be greatly appreciated.

More annoying is that if I use just font and color, I’ve gotta break up the font property (did I mention I dislike string parsing?) and reassemble it in a different order (and with some substitutions to turn Bold into B).

So, here’s what I came up with.  2 extended attributes:

• customFont which is a font data type and gives me the font name, style and size (specified incorrectly for my purposes)
• customFontColor which is a color data type and gives me the custom color
• customFontFormat which I’ve defined as a calculated attribute.  The calculation combines the other two attributes and formats them properly for the subobject format string.  A little divide and conquer that makes the rest of my scripts a little shorter.  You can do this as a subroutine as well, I prefer calculated attributes which makes them “visible” outside of a vbscript and makes debugging a breeze.

Step 2:  Code the Calculated Attribute

Function %Get%(obj)
 ' Implement your getter method on <obj> here
 ' and return the value

 dim customFont, fontName, fontStyle, fontSize, fontColor, strPos

 customFont = obj.getExtendedAttributeText("SubObjDemo.CustomFont")

 strPos = instr(customFont,",")

 'get the name
 fontName = left(customFont,strPos-1)

 'get the style and convert to the allowable values
 customFont = mid(customFont,strPos+1)

 strPos = instr(customFont,",")
 fontStyle = left(left(customFont, strPos-1),1)
 if fontStyle = "" then
 fontStyle = "N"
 end if

 customFont = mid(customFont,strPos+1)

 'now the font size
 fontSize = customFont

 if fontSize = "" then
 fontSize = 10
 end if

 fontColor = obj.getExtendedAttributeText("SubObjDemo.CustomFontColor")

 if fontColor = "" then
 fontColor = "255,255,255"
 else
 fontColor = replace(fontColor," ",",")
 end if

 %Get% = fontName & "," & fontStyle & "," & fontSize & "," & fontColor

End Function

Step 3:  Some Assembly Required

So, now we have all the individual pieces, we just need to assemble them.  I’ve gotten as far as columns and keys.  Obviously there are other properties available, but we’ll leave that as an exercise for the reader.  If anyone would care to finish this, I’d be more than happy to publish the finished product.

So, first create a custom method.  Here’s the logic.  For each table symbol in the active diagram find the object it represents.  Then loop through the columns and keys collection and get the customFontFormat property.  Use these to build a new format string for the symbol.

Here’s my code:

Sub %Method%(obj)
   ' Implement your method on <obj> here

   dim sym
   dim symObj ' object the symbol represents
   dim subObj ' the sub-objects in the objects (columns and keys in this example)
   dim formatStr ' the new sub-object format string we'll assemble
   dim workStr ' a working string that we'll build to replace the format string

   ' now, start by looping through the symbols on the active diagram

   for each sym in activeDiagram.symbols

     if sym.ClassName = "Table Symbol" then'
         'get the object (in this case table)
         set symObj = sym.Object         

         'We're going to build the subobjects string using the formatting values stored in the objects.
         'first the table level entities

         'columns
         for each subObj in symObj.Columns
            if subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat") <> "" then
               if workStr <> "" then
                  workStr = workStr & vbLf
               end if
               workStr = workStr & subObj.ObjectId & " " & subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat")
            end if
         next

         if workStr <> "" then
            if formatStr <> "" then
               formatStr = formatStr & vbLf
            end if
            formatStr = formatStr & "Column 0" & vbLf & workStr
         end if

         'now keys
         for each subObj in symObj.Keys
            if subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat") <> "" then
               if workStr <> "" then
                  workStr = workStr & vbLf
               end if
               workStr = workStr & subObj.ObjectId & " " & subObj.GetExtendedAttribute("SubObjDemo.CustomFontFormat")
            end if
         next

         if workStr <> "" then
            if formatStr <> "" then
               formatStr = formatStr & vbLf
            end if

            formatStr = formatStr & "Key 0" & vbLf & workStr
         end if

         sym.subObjects = formatStr

     end if

   Next

   activeDiagram.RedrawAllViews

End Sub

Create a menu item on the menu item to call or new method and this part is done.

Step 4:  A Method to Set Formats

So now we can format subobjects for a table if there’s a custom format specified – and you can set them manually by editing the custom properties we defined.  Obviously that’s now something we want to do.  What’s left?  A method or methods to set the sub-object format properties on objects based upon some condition.  While I think columns changed or added since the last checkin is the obvious candidate for this, I don’t know if you have a repository and that’s a little hard to test.  So I’m going create my script to identify columns which don’t have a comment.  The thing I like about this particular approach is I can re-use the apply formatting method and create many different methods to set the formatting properties on sub-objects.  So here’s my method:

Sub %Method%(obj)
   ' Implement your method on <obj> here

   Dim mySel
   Dim myObj

   Set mySel = ActiveModel.CreateSelection

   mySel.AddObjects ActiveModel, cls_column, false, true

   For each myObj in mySel.Objects
      With myObj

         if myObj.comment = "" then
            myObj.setExtendedAttribute "CustomFont","Arial,Bold,9"
            myObj.setExtendedAttribute "CustomFontColor","255 0 0"
         else
            myObj.setExtendedAttribute "CustomFont","Arial,Normal,9"
            myObj.setExtendedAttribute "CustomFontColor","0 0 0"
         end if

      end with

   Next

   obj.ExecuteCustomMethod "SubObjDemo.ApplyFormat"End Sub