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…
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.
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:
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
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.
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
Now we create a menu item again, and we’re good to go. If you’d like the complete code and a sample model, just download subObjectFormatting.pdm below.
SubObjectFormatting.pdm from box.net
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.
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
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.
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.
Again, not strictly necessary, but it’s only a few mouse clicks for a little extra versatility.
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
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?