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