Events – Validate Basics

Part TWO!

I’ve decided to split part two into two parts (maybe part 3 will be split in thirds).  First, a general discussion of validation events, pros, cons and things you should know.  I’ll follow up in another week (or two, or three – my record here isn’t great) with some additional samples of checks and automation done via validation events.

First, the basics.  A validation event fires whenever you modify an object and then hit ‘apply’, ‘ok’, or navigate off the object you’re modifying.  Pretty straightforward.  So, when would we use a validation event?  I see two potential uses for them (please let me know if you’ve got any others).

1. As a real-time substitute for a custom check.  Why wait for someone to request a model check if you can validate it as soon as the object has changed and let them know on the spot.
2. As a way to automate a common task by performing a repetitive action when an object is modified.

This is very appealing but does have a few potential problems.  Why wait for someone to hit the check model (or rely on them actually doing it) if you can ensure they’ll hit the same validation as soon as they make the mistake?  Why let the modeler potentially make the same error 200 times on a model before they bother to run a check and discover they’ve made the same error over and over again?  If you let them know immediately, they’ll learn to do it right immediately.

Well, it turns out there is a downside.  First, validation events allow you to raise an error, and potentially correct it (similar to auto-correct on an event) but makes it much harder to produce a nice concise list of errors.  In addition, if your event forces something to be fixed, the modeler may get annoyed if you’re enforcing rigor they might not be ready for yet.  For example, if they’re trying to lay down a quick model during a working session, being forced to enter a comment on every column could get in the way.  You could have the event write a warning to the output window without forcing the user to correct the issue but that’s easy to overlook and you’ll still need a custom check.  Second, you only get one validation event on each object class.  That can lead to a LOT of code in one big chunk if you’re trying to do multiple checks.  You can split it out into subroutines but there could still be an awful lot going on.  The last problem, related to the second is that there can be a fair amount of overhead involved.  You could be firing a lot more events than you think you are.

For demonstration purposes, I’m going to modify the XEM file I started for initialize events and walk through a little example.  We’ll create two events and see what happens.

First, let’s open our XEM file and edit the global script.  I’m going to add these lines:

dim eventCounter
eventCounter = 0

We’ll use event counter to track how many times we fire a validation event when we test.  Now we’ll create a couple of events.  First, we’ll create a column level validation event.  Here’s the code:

Function %Validate%(obj, ByRef message)

   eventCounter = eventCounter + 1

   output eventCounter & ":  Column Level Event Handler on " & obj.Name

   %Validate% = True
End Function

Next we’ll create a table level validation event

Function %Validate%(obj, ByRef message)

   eventCounter = eventCounter + 1

   output eventCounter & ":  Table Level Event Handler on " & obj.Name

   %Validate% = True

End Function

Before we go ahead and test, there’s one more topic we have to cover.  In the general options for the tool, there’s an option called “Auto Commit”.  Having autocommit on basically pushes the apply button after every change.  I typically have autocommit on, because I’m lazy and don’t make mistakes (at work… people have pointed out plenty on this blog…  you should take my word on this instead of asking my co-workers).

Here’s where the options is.

Now, let’s create a table and see what happens in the output window.  I’ve extended the XEM file I used for the initialization event demo.  This will create a set of audit columns on ever table automatically.  Notice that when we create two columns via script, the event doesn’t fire.

I’ll create a table with the audit columns and add two more on.  I’ll do it once with autocommit enabled and once with it off, resetting the counter in between.

Here’s the results.

Autocommit On:

1:  Table Level Event Handler on Table_1
2:  Column Level Event Handler on Column_3
3:  Table Level Event Handler on Table_1
4:  Table Level Event Handler on Table_1
5:  Table Level Event Handler on Table_1
6:  Table Level Event Handler on Table_1
7:  Table Level Event Handler on Table_1
8:  Table Level Event Handler on Table_1
9:  Column Level Event Handler on Column_4
10:  Table Level Event Handler on Table_1
11:  Table Level Event Handler on Table_1
12:  Table Level Event Handler on Table_1
13:  Table Level Event Handler on Table_1
14:  Table Level Event Handler on Table_1
15:  Table Level Event Handler on Table_1
16:  Table Level Event Handler on Table_1
17:  Table Level Event Handler on Table_1
18:  Table Level Event Handler on Table_1
19:  Table Level Event Handler on Table_1
20:  Table Level Event Handler on Table_1
21:  Table Level Event Handler on Table_1
22:  Table Level Event Handler on Table_1
23:  Table Level Event Handler on Table_1
24:  Table Level Event Handler on Table_1
25:  Table Level Event Handler on Table_1
26:  Table Level Event Handler on Table_1
27:  Table Level Event Handler on Table_1

Autocommit Off:

1:  Table Level Event Handler on Table_2
2:  Column Level Event Handler on Column_3
3:  Column Level Event Handler on Column_4

So, with autocommit on, we get 27 events with autocommit on.  With autocommit off, we get 3.  The model I’m working with has two tables table with 4 columns each.  The validation events are only writing messages to the output window.  If your validation events are complex and you have a lot of functions, you can start incurring a lot of overhead.  If you add validation events to a lot of metaclasses, you could impact performance.  The takeaway –

1. don’t overuse validation events
2. autocommit OFF – if you’re going to use validation events, make sure your modelers know that autocommit may be a problem.
3. write code that will not cause problems if it executes multiple times if you’re going to insist on leaving autocommit enabled (see rule #2).

I’d stick with validation events for items that are difficult to correct after that fact and you’re concerned that people will make repeatedly if they aren’t made aware of the issue immediately.  If your point is to let people know there’s a problem early and “teach” them what they’ve done wrong, you might also consider having the event notify the user of the error along with corrective actions without actually “fixing” the problem.

That’s enough of the basics.  Check back in a week or so for more on validation events.

Events – Initialize

This will be the first of several posts on events.  As many of my friends and colleagues have pointed out, I don’t have a great track record putting out new posts on a regular basis.  So, right now, I’m committing to at least one more post on this topic.  The Sybase documentation lists almost a dozen different events, and I’m fairly certain their list is either incomplete or there’s another list somewhere I haven’t found.  I’m not giving myself and end date (baby steps), but it’s a start.  One of the reasons I started this blog was to force myself to learn a bit more about PowerDesigner and vbScript so this should be interesting.  Some of these events, I haven’t used yet, so honestly I’m not quite sure what I’ll write about but I’m sure I’ll figure something out.

Overview

All metaclasses support 4 events:  Initialize, Can Create, Validate, and On Target Model Reconnection.  There are a few additional methods that apply to specific objects which I may or may not cover.

You add events, just like you add anything else.  By extending a model through an XEM or XDB file.  I’ll use an XEM for my examples.  I’ll assume you know how to get that far (as usual).  For my demonstration, I’m going to create an initialization even on the table metaclass that adds a pair of columns (create date, update date).  I’ve tried to do as much of this as possible to save myself time.  Originally I automated these types of tasks with custom checks and auto-corrects, but initialization events are a much better method.  Why create code to fix something you’ve already done wrong or forgotten to do when you can create code to do it automatically as you create the object.  Faster, better, harder to forget, and usually less code.

Adding our Event

So, you’ve created or opened an XEM file and added the table metaclass.  If you’re not sure how to do this, see several of my prior posts.

Step 1:  Add an event

Right click on the table metaclass, you’ll see a list of 5 events (table has an extra).  Check the box next to Initialize and click ‘OK’
Now, you can open the event and edit the initialize event.  However, as usual, I’ll make things more complex than they have to be (occasionally this pays off in the long run) and put my code in a custom method.  Why?  Because I have some models that need these columns added to existing tables and doing it this way makes it easy to add the columns to existing tables and automatically to new tables with a single piece of code.

Step 2:  Create our Custom Method

Right click on the table metaclass again and create a new method.  Here’s the code I’ll use.  I’m keeping it simple for demonstration purposes.  I called my method addAuditColumns.

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

   dim newCol

   'Create Date
   set newCol = obj.Columns.CreateNew()

   with newCol
      .Name = "Create Date"
      .SetNameAndCode .Name, "", true
      .DataType = "DATE"
      .Mandatory = TRUE

   end with

   'Update Date
   set newCol = obj.Columns.CreateNew()

   with newCol
      .Name = "Update Date"
      .SetNameAndCode .Name, "", true
      .DataType = "DATE"
      .Mandatory = TRUE
   end with

End Sub

I’ll add a menu item to the table metaclass as well so I can call this method with a couple of clicks on the context menu.  I’ve included it in the demo file.

Step 3:  Call the Custom Method

Now that we’ve done all the hard work, in our method, the event becomes easy.

Here’s the code:

Function %Initialize%(obj)
   ' Implement your initialization on <obj> here
   ' and return True in case of success

   obj.ExecuteCustomMethod("Events.addAuditColumns")   %Initialize% = True
End Function

And we’re done.  Now, anytime you create a table, you’ll automatically start with your two audit columns.

Step 4:  Create Some Tables

Best of luck.

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

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.