Auto-renaming columns in PowerDesigner LDM/PDM

[gemisigo]

I want to rename columns in a PowerDesigner LDM/PDM according to the
state of the Primary checkbox. To be more specific, I want the column
renamed from "oldname" to "id_oldname" if Primary is checked (and
revert when it gets unchecked if it is possible).

I managed to alter "MSSQLSRV2008::Script\Objects\Column\Add" so that
it creates the script with "id_" prefix appended to columns that are
part of the primary key, but it works at script level only, it does
not change the column name/code. To make the script consistent I
should change "MSSQLSRV2008::Script\Objects\PKey\Add" too, I failed at
that.
Modifying "(%PKEYCOLUMNS%)" to "(id_%PKEYCOLUMNS%)" appends the prefix
to the first column only (which is more or less what I expected) and I
could not figure out how to process them one after the other.
".foreach_item(%PKEYCOLUMNS%)" does not seem to work, it reports error
in the generated script.

So I have to rename/recode the columns in the column tab.

Is there a way to achieve that in PowerDesigner 15?

[rkkier]

Easy enough with a little vbscript.

Create a new extended model definition file (or use an existing one).
Add the table metaclass (you'd think it would be column, but that
doesn't work), then put a validate event handler on it. Paste this
code into the event handler:

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

if left(col.name,3) = "id_" then
%Validate% = True
else

With col
.name = "id_" & .name
.SetNameAndCode .Name, "", True
End With

%Validate% = True
end if

else

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

If this doesn't make sense, just post a reply and I'll try and provide
more detail.

[gemisigo]

Thank you for your answer. At first it did not make sense, but I
figured out how to do it. The code seems to be quite simple even if I
have absolutely no experience with vbscript.

It summoned another issue, though.
According to my interpretation, a validate event handler should
validate the object after each and every event that could cause the
object to be invalid. Now, there is something that is a bit unclear.
Creating or deleting a column seems to be such an event, but changing
the column is not. And even that proves to be false sometimes.

I created a Table with a few columns. The first column I clicked to be
primary changed its name/code immediately (though it should not) but
none of the subsequent clicks triggered the event handler. It does not
update the object until I add or remove another column. The help
states that "It is triggered when change tabs or click OK or Apply in
the object property sheet.". I disabled autocommit to see if it
interferes but none of those events triggered the event handler. Am I
missing something or is this a bug?

[rkkier]

It's either a bug, or Sybase needs to redo their documentation to
reflect reality. I keep meaning to set aside time to figure out what
actions trigger what events but never seem to get around to that. It
does appear that the validation event on a column is only triggered
when the column is created, but the validation event on the table is
triggered by most changes to a collection in the table, that's why I
put the code there. I can't help straighten out the documentation,
but if you've got cases where the event isn't working properly, let me
know.

You can also take the same basic code and put it in a custom check on
the column. Just check the primary attribute and the first three
characters of the string. You can have it warn the user, provide an
automatic correction, and even have PD run the correction for the user
automatically. You'll accomplish the same thing as long as you always
run a check model.

I learned VBScript about 4 years ago just to make my modeling tasks
easier. Lot's of good resources on the web.

[gemisigo]

>  I can't help straighten out the documentation,
> but if you've got cases where the event isn't working properly, let me
> know.

So far the only case it should work (according to the documentation)
and it does not is when clicking on OK or Apply. It does not say
anything about auto-commit, but I think it ought to work there too.

> You can also take the same basic code and put it in a custom check on
> the column.  Just check the primary attribute and the first three
> characters of the string.  You can have it warn the user, provide an
> automatic correction, and even have PD run the correction for the user
> automatically.  You'll accomplish the same thing as long as you always
> run a check model.

I did, thanks. This was an excellent idea. And (unlike the column
validating) it works properly.

[rkkier]

I'm using this same basic logic for several tasks. If you wouldn't
mind sharing a sample, I'd be interested in trying to figure out what
the difference is. You can mail me directly.