Uncategorized

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

Questionable Quotations

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