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.
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.
Hi Rick, A very interesting post on audit columns. This will handle automatically adding audit columns to a physical model. I was wondering how you would advise the addition of audit columns between the merge of the logical model to the physical model (i.e logical model does not have audit columns, but the physical model does). Would you advise a post generation script?
By script, I assume you mean transform? That would be my preferred solution. I’ve used both pre and post generation transforms. I have multiple target platforms and pre-generation allows me to take care of some of the data type conversions that vary by RDBMS (e.g. varchar2 vs. varchar or date vs. timestamp). If you’re using database specific features like defaults, triggers, etc. or only supporting a single platform then post-generation probably makes more sense.
Hi Rich, thanks for the reply – I think I will leave it as a post generation transform.
Your blog is really interesting, keep up the good work!
Hi Rich, a further question for you. When adding audit columns to the physical model only .. the merge dialog box will always report a difference between a logical entity and a physical table (physical has audit columns). Is there any way to disable that so the real differences are apparent in a merge?
This might work (but probably not)…
1) create an extended collection “AuditColumns” on the table
2) have your transform add the columns you add to that collection
3) change the merge comparison options to ignore that collection
I think this will cause your audit columns to not get generated, but that’s the only thing I can think of that might work. No time to test tonight, but if you try it out, please post your results.