//
archives

PowerDesigner

This category contains 18 posts

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.

Scripting Parameters

Now that the holidays are over, time to get some work done again.

I’ve been rewriting some older scripts recently (again), and a lot of them are prompting the modelers for values to be used in the scripts.  I used to do this with a dialog box but now that PowerDesigner has forms, I’ve moved almost exclusively to extended attributes and forms.  This gives me a lot more control over the form, and allows me to gather multiple parameters at the same time.  It gives me greater control over lists of values, and it allows a script to “remember” the values chosen last time the script was run.

Let’s say I want to control access to the tables in my model through views.   Within a model, there are three parameters I want to control for my view generation:

  1. Who owns the view
  2. Do I use a view suffix (and what is it)
  3. Do I want them placed on the diagram after they’ve been created

I could prompt the user three times, but that’s not terribly user friendly.  I’m also very likely to regenerate or add new views over time, and I’ll likely want the same settings each time.

So, instead of prompting for each option individually, I’m going to create an extended attribute for each and a popup form that the user can use to set the values.

Creating the Extended Attributes

First, we create the three extended attributes we’ll use to control our script. Notice that I’ve created a category to keep them organized.  I’ll create the same category for forms, templates etc. so it’s easy to see what’s related.

I’ve created “Place Access View On Diagram” as a boolean, and “Access View Suffix” as a string.  “Access View Owner” needs to be a user defined in the model, so I’ve created it as a text attribute with a list of values.  You can do this by clicking on the “Create” button next to the List of values: box on the attribute.  When the template definition box pops up, enter this code in the editor:

.foreach_item (Model.Users)
%Name%
.next (\n)

This creates a new template called “access View Owner List”.  I recommend you immediately go in, create your category, and then move the template into it for organizational purposes.

Adding the Form

Now that we have our attributes, we need a way to set them (duh).  So let’s make a form.  Create our category, create our blank form, set it as a dialog box, and use the “Add Attribute” button to add our new extended attributes.  Our extended model definition now looks like this:

Setting Parameters

Now we’ve got our extended attributes, and our form.  We need a script with a few extra lines.  Since the point of this post is parameters, not views, I’ve condensed everything but the dialog down to  a couple of dots.

So here’s our script:

Dim dlg

Set dlg = obj.CreateCustomDialog(“Access Control Views”)
If not dlg is Nothing Then
dlg.ShowDialog()
End If

And here’s the result:

Now that we’ve set the values, we need to get them for the script:

owner = obj.getExtendedAttributeText(“Access View Owner”)

suffix = obj.getExtendedAttributeText(“Access View Suffix”)

display = obj.getExtendedAttributeText(“Place Access View on Diagram”)

Done.  Now the user only has to set the values the first time the execute the script or if they change the values and you can see what values were chosen by looking at the extended attributes.

Next time, we’ll expand on this a bit with some additional options to make this more flexible.

Selection Goodness

Today’s post is on selections.  Selections are a great way to script changes to multiple objects and a great way to make your custom methods more flexible.
Selections are just what they sound like, a group of objects, placed into a collection for processing.  Let’s jump right in with a simple example:

Dim mySel
Set mySel = ActiveModel.CreateSelection
mySel.AddObjects ActiveModel, cls_table, false, true

The first two lines declare and create your selection.  The third line adds all objects of the given class, in this case tables, to the selection.  The last two parameters (false, true) tell the script to skip shortcuts and to recurse through sub-packages.­­  Now that we have our selection, how do we use it.  The following lines will loop through the collection.  For my examples, I’m using SetNameAndCode to rebuild the codes on all the objects in the selection, just to demonstrate the concept.

Dim myObj
Dim mySel
Set mySel = ActiveModel.CreateSelection
mySel.AddObjects ActiveModel, cls_table, false, true
For each myObj in mySel.Objects
 With myObj
 Output .Name
 .SetNameAndCode .Name, "", True
 end with
Next

So far, so good.  It gets better though.  You’re not restricted to a single class in a selection.  So if you’d like to rebuild the codes for both tables and views, you can add both classes.  Our revised code looks like:

Dim mySel
Dim myObj
Set mySel = ActiveModel.CreateSelection
mySel.AddObjects ActiveModel, cls_table, false, true
mySel.AddObjects ActiveModel, cls_view, false, true
For each myObj in mySel.Objects
 With myObj
 Output .Name
 .SetNameAndCode .Name, "", True
 end with
Next

Without a selection, we’d be stuck doing something like:

For each tab in ActiveModel.tables
 Output tab.Name
 Tab.SetNameAndCode Tab.Name, "", True
Next
For each view in ActiveModel.views
 output view.name
 view.SetNameAndCode view.Name, "", True
Next

That’s not too bad, but notice our SetNameAndCode sample is very short. Also, we’re only dealing with two classes and we’re only dealing with objects in the root of the model.  If we want to recurse through packages, there’s a lot of additional work yet to be done.
Now, suppose we don’t want to include all objects of a particular class, but we’d like to be a bit more choosey.  We have a couple of options:

  • AddActiveSelectionObjects
  • ShowObjMultiSelection
  • ShowObjectPicker

The first, AddActiveSelectionObjects method, does exactly what it says it does.  Whatever objects you currently have selected, either in the browser or the active diagram are added to your current selection.  I’ll focus on the other two.
The second two provide dialog boxes which you can use to define your selection manually.
Here’s an example:

Dim mySel
Set mySel = ActiveModel.CreateSelection
mySel.ShowObjMultiSelection ActiveModel, "Tables"

The first parameter is the name of the parent object.  In this case, the active model.  The second parameter, this case “Tables” is the name of the collection you want to select from.  You’ll get a dialog that looks like this:

Multi-Select Dialog
Choose the objects you want and processing using the same select loop.  Instead of the active model, the first parameter can be changed a particular table, view, etc.  This allows you to choose from objects that apply only to that object.  This is very handy in a custom method.  For example, this method would allow you to select references on a particular table.  Keep in mind that the selection itself is still a property of the model, not Obj.

Sub %Method%(obj)
   dim mySel
   dim myObj
   Set mySel = ActiveModel.CreateSelection
   mySel.ShowObjMultiSelection Obj, "InReferences"
   for each myObj in mySel.Objects
      With myObj
         Output .Name
         .SetNameAndCode .Name, "", True
      end with
   next
End Sub

If you’ve looked at the VBScript help file, you probably know there’s a great example of how to build your own custom selection list for the multi-selection method.  I’m going to change it just a bit to show you how to get a single selection dialog with both inReferences and outReferences.  This also demonstrates the Add method available to any collection.

Sub %Method%(obj)
   dim myObj
   dim ref'add all of the objects we’re interested in to the collection
   Set mySel = ActiveModel.CreateSelection
   For each ref in Obj.inReferences
      mySel.Objects.Add(ref)
   next
   For each ref in Obj.outReferences
      mySel.Objects.Add(ref)
   next
   'now show the objects in the selection in your dialog
   mySel.ShowObjMultiSelection mySel, "Objects"
   for each myObj in mySel.Objects
      With myObj
         Output .Name
         .SetNameAndCode .Name, "", True
      end with
   next
End

The other method of adding objects to a selection, SubShowObjectPicker, behaves quite a bit differently.  I don’t use this method nearly as often, but we’ll cover it just for completeness.
Unlike the mult-selection dialog, ShowObjectPicker always (as far as I can tell) starts from the active model.  You can choose which class of object the user gets to choose from (e.g. tables or columns) but you can’t choose to show only columns belonging to a specific table.  You can filter by stereotype but unless you’re going to change the stereotype for an object at runtime (and set it back when you’re done), this isn’t an approach I find particularly useful.  Anyway, here’s a code snippet and the result.

Dim mySel
Set mySel = ActiveModel.CreateSelection
mySel.ShowObjectPicker "Table:Column"

This gives you the following dialog:
singleselectWell, that’s enough for now.  More to come.